Зробити SQL величним знову, або Як DBT допоможе побудувати сховище даних

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті.

Привіт, мене звати Андрій, я Data Engineer у компанії Holy Water з екосистеми Genesis. Я маю досвід роботи у Data Analysis, тож сьогодні хочу поговорити про технологію на стику професій.

Цей матеріал може бути цікавий аналітикам та інженерам даних, які виконують перетворення та агрегації даних засобами SQL.

«Болі», з якими стикається спеціаліст з даних

Нижче перерахую болючі питання, знайомі тим, хто працює з даними:

1. У вас бувало таке, що ви знаходите якусь табличку у своєму аналітичному сховищі й не знаєте, з яких сирих даних вона збирається, який процес за це відповідає?
2. Треба збирати агрегати (вітрини даних) за графіком, а вони часто спираються на інші таблички, тож доводиться ставити їх на оновлення на 30 хвилин пізніше. Але буває, що залежності збираються довше, ніж зазвичай, і тоді — журбинка.
3. А як щодо інкрементального оновлення таблиць? Ви свої вітрини повністю оновлюєте чи дозаписуєте? А буває, що треба оновити n останніх днів або тільки окремі рядочки.
4. Документацію ведете у Confluence (Google Sheets, коментах у БД)? Зручно підтримувати, шукати інформацію?
5. А було таке, що якийсь звіт не працює, бо посилається на колонку в таблиці/ представленні, якої вже немає, і невідомо, як відкотити назад?
6. А коли пишете агрегат з LTV 0, 1, 3, 7, 30... 180, 365 днів і копіюєте колонки майже однакового змісту (DRY principle)?
7. Стикалися з тим, що додали/ видалили рядочок зі словника, і важко проконтролювати або відновити його стан?
8. А дублікати траплялися вам у повністю готових табличках?
9. Було таке, що менеджер просить переглянути, чи все добре з даними, бо графіки спрямовані донизу, а ви перевіряєте і помічаєте, що щось не оновилося?

Чого бракує під час перетворення даних, щоб позбутися вищезгаданих проблем:

1. Системи контролю версій.
2. Генерації SQL.
3. Data Lineage (граф залежності).
4. Інкрементального оновлення.
5. Тестів на якість даних.
6. Документації.

Так-так, знаю, ви вже придумали, як зробити (або вже навіть зробили) свій внутрішній інструмент, який буде розв’язувати деякі перелічені проблеми. А можливо, вам достатньо Scheduling queries або ви взагалі використовуєте Windows Task Scheduler на віддаленому робочому столі.

Рішення:

Альтернативне рішення: Насправді вже є опенсорсний фреймворк, який розв’язує ці питання елегантним чином. І навіть не один, але ми розглянемо саме DBT.

DBT (data build tool) — це інструмент, покликаний додати елементи програмної інженерії в роботу з даними (SQL).

Як вирішує проблему DBT (data build tool)

Розглянемо докладніше реалізацію DBT у розрізі вищенаведених пунктів

1. Система контролю версій. DBT зберігає проєкт у репозиторії, де ви описуєте моделі даних (скажімо, таблички). Те, що проєкт перебуває в репозиторії, дає можливість контролювати зміни, проводити код-рев’ю та не «викочувати» в п’ятницю інші корисні практики.
2. Генерація SQL. Моделі, описані SQL-кодом, в якому також може бути використана Jinja. Якщо коротко, то в нас з’являються змінні, цикли, галуження та функції.
3. Data Lineage (граф залежності). У моделях, залежних від інших, ви вказуєте назву моделі, а не «хардкодите» табличку/ представлення. Таким чином DBT знає граф залежностей і може збирати ваші таблички послідовно.
4. Інкрементальне оновлення. DBT вміє оновлювати таблички повністю і має два типи інкрементального оновлення: «видалити та замінити новим» (наприклад, партицію за певну дату) та «оновити за ключем» (наприклад, рядок для певного користувача).
5. Тести на якість даних. У DBT є вбудовані тести для певних колонок. Зокрема, unique, not_null, допустимі значення, referential integrity тощо. Крім того, можна написати SQL-тест, який буде перевіряти специфічні сценарії.
6. Документація. На базі моделей, їхніх залежностей, опису, тестів і структури DWH генерується гарна HTML-сторінка. А якість заповнення документації можна контролювати на етапі підтвердження merge request.
7. Бонусний пункт — Seed. Досить зручна функція, можна тримати CSV-файлик у репозиторії, який перетворюється на табличку в базі.

Приклад, як описувати модель:

data_mart__revenue_by_lifetime.sql

{% set days = [0, 1, 3, 7, 14, 30, 90, 180, 365] %}


SELECT
    users.user_id,
{%- for day in days %}
SUM(
    CASE WHEN TIMESTAMP_DIFF(orders.order_time, 
users.install_time, `DAY`) <= {{ day }}
         THEN orders.amount
          END
)   AS ltv_day_{{ day }}
        {%- if not loop.last %},{% endif %}
{%- endfor %}
FROM {{ source('product', 'users') }}             AS users
LEFT JOIN {{ ref('product__orders') }}            AS orders USING(user_id)
GROUP BY users.user_id

Поширена ситуація, коли нам треба порахувати метрику на n-й день лайфтайму.

Цей код під час компіляції згенерує такий скрипт:

SELECT
    users.user_id,
SUM(
    CASE WHEN TIMESTAMP_DIFF(orders.order_time, users.install_time, `DAY`) <= 0
         THEN orders.amount
          END
)   AS ltv_day_0,
...
SUM(
    CASE WHEN TIMESTAMP_DIFF(orders.order_time, users.install_time, `DAY`) <= 365
         THEN orders.amount
          END
)   AS ltv_day_365
FROM `my_db`.`product_schema`.`users`             AS users
LEFT JOIN `my_db`.`dbt_monetary`.`orders`         AS orders USING(user_id)
GROUP BY users.user_id

Розберемо докладніше:

{% set days = [0, 1, 3, 7, 14, 30, 90, 180, 365] %} задали список днів. Цю змінну ми могли вказати не в самому файлі моделі, а в конфіг-файлі, тоді вона могла б використовуватись різними моделями (як глобальна змінна);

{% for day in days %}...{% endfor %} ітерувалися по них, створюючи колонки;

{% if not loop.last %},{% endif %} використали галуження, щоб не ставити кому після останньої колонки;

{%- endfor %} мінуси біля знаку % потрібні для форматування, а саме — для видалення зайвих пробілів і переносів рядочка;

{{ source('product', 'users') }} документація передбачає, що треба вказувати спеціальну змінну (посилання на джерело) замість безпосереднього використання назви таблиці;

{{ ref('product__orders') }} посилання на іншу модель замість назви таблиці.

Посилання на моделі дають змогу зобразити Data Lineage:

Якими є конфігурації до моделей:

Properties.yml

models:
  - name: product__orders
    config:
      schema: monetary
      alias: orders
      materialized: table
      partition_by:
        field: order_time
        data_type: datetime
        granularity: day
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - user_id
      - dbt_utils.recency:
          field: order_time
          datepart: day
          interval: 1

Тут ми задаємо спосіб матеріалізації моделі, це може бути:

• Table (як у нашому випадку) — таблиця, що оновлюється повністю;
• View — власне представлення;
• Incremental — часткове оновлення має умовно дві стратегії:
  • оновлення рядочків за ключем;
  • оновлення партиції.
• Ephemeral — модель не має об’єкта в сховищі даних, а виступає як CTE (Common Table Expression) під час створення інших моделей.

Також ми вказуємо назву (alias) і схему (schema) матеріалізованого об’єкта, спосіб партиціонування (partition_by) і тести (tests).

Під час запуску тестів до цієї моделі ми перевіримо унікальність user_id і свіжість даних (схожі тести є і для джерел).

У файлі конфігурації могло б бути ще багато різновидів тестів, опис кожної колонки, теги, документація тощо.

Ще трохи бонусів

Тести теж можуть бути описані окремими SQL-скриптами, якщо потрібно виконати щось специфічне. Вони допоможуть не тільки гарантувати якість даних, а й перевірити свої моделі перед потраплянням у продакшн.

У DBT можна сконфігурувати різні змінні на тестовому та продовому середовищах розробки.

Виконуючи run (створення/ оновлення) моделей, можна використати теги, щоб не писати про запуск кожної моделі окремо. Також є синтаксис, який допоможе оновити модель і всі її залежності «+my_model», або нашу модель і всі моделі, що базуються на ній, «my_model+».

Водночас не треба перейматися залежностями моделей, адже DBT використає відомий йому lineage і виконає моделі в топологічній послідовності.

На основі ваших коментарів до табличок і колонок DBT створює сторінку документації і може навіть переносити їх у коментарі до ваших таблиць у сховище.

Крім того, DBT пропонує створювати таблички з CSV-файлів, що зберігаються у репозиторії, називаючи це словом seed. Це досить зручно для ведення словників, які заповнюються вручну, бо тоді вони розміщені під репозиторієм і є змога відстежувати зміни.

Висновок

Як і колеги, я теж «винаходив велосипед», створюючи рішення, які зі змінним успіхом виконували окремі функції DBT (або Dataform). Та нарешті знайшлася технологія, яка допоможе ефективно застосовувати практики програмної інженерії в розробці сховища даних.

Версіювання, середовище розробки, генерація/ перевикористання коду (функції, цикли, галуження, змінні), тестування, конфігурування, документація, модульність, інкрементальне оновлення, логування, сторонні бібліотеки та багато іншого тепер доступні й вам. Про все це докладніше розповідають розробники у своєму безплатному курсі, сертифікат якого можна додати у профіль на LinkedIn.

Здається, що технологію DBT сильно недооцінюють (або про неї не знають) наші колеги. На Djinni серед навичок її вказали всього 9% дата-інженерів і 2% аналітиків (на момент написання статті). А ви чули про DBT?