Привет! Меня зовут Кирилл Львов, я fullstack-разработчик в компании СберАналитика. В этой статье хочу рассказать про мощный инструмент трансформации данных — DBT (Data Build Tool).
Сегодня любой средний и крупный бизнес хранит множество данных в разрозненных источниках (CRM, ERP, HRM, базы данных, файловые хранилища и т.д.). Каждая из этих систем самодостаточна и закрывает определённую боль бизнеса, но собрав данные из таких источников и стандартизировав их, нам открывается возможность анализировать данные, строить модели машинного обучения и принимать на основе этих данных управленческие решения. Для того чтобы реализовать такой подход строятся ELT (или ETL) процессы. ELT (Extract, Load, Transform) — это процесс, состоящий из трех этапов:
Извлечение (Extract): На этом этапе данные извлекаются из различных источников, таких как базы данных, файловые системы, API и т.д.
Загрузка (Load): Сырые данные загружаются в целевую систему.
Преобразование (Transform): После загрузки данные обрабатываются и преобразуются в нужный формат. Это может включать очистку данных, агрегирование, фильтрацию, изменение структуры данных и других преобразований, необходимых для анализируемых данных.
Для реализации этапа преобразования данных (Transform) нам на помощь приходит DBT. В этой статье мы рассмотрим основные концепции, функции и сущности DBT. Научившись работать с DBT и внедрив его в свои процессы, вы сможете улучшить качество своей аналитики и повысите эффективность работы с данными, сделав процессы более структурированными, быстрыми и понятными.
Данная статья состоит из 3-х частей. Структура DBT-проекта (рассмотрим структуру папок, назначение файлов и принцыпи их наименования), сущности DBT (рассмотрим 6 главных сущностей DBT) и дополнительный функционал (некая дополнительная информация, которая не относится к сущностям, но про неё стоит упомянуть).
Структура проекта DBT
DBT – представляет различный функционал для трансформации, тестирования данных. Сам DBT проект – это git репозиторий c определённой структурой папок. В каждой из папок храняться файлы .yml или .sql (в некоторых случаях могут присутствовать и другие форматы), которые описывают свойства DBT-логики. Логика DBT настраивается либо центролизованно (пишется конфиг файл (Config) на группу ресурсов), либо точечно (1 свойство (property) на 1 ресурс). Свойство может быть объявлено только в .yml файлах. Config может задаваться в dbt_project.yml, .yml или в блоке config .sql файла.
Так как конфиги могут быть заданы в разных местах, то они могут перезатирать друг друга. В DBT принята следующая цепочка приоритетности dbt_project.yml -> .yml файлы -> блок config в .sql . То есть если в .yml файле указан параметр который также задан в dbt_project.yml файле DBT не будет учитывать для этого ресурса параметр из dbt_project.yml.
Структура DBT проекта подразумевает разделение файлов на директории по назначению файлов:
models/ - файлы, которые описывают модели данных.
seeds/ - CSV-файлы (справочная информация с небольшим набором данных)
snapshots/ - файлы отвечающие за сохранение истории данных
tests/ - файлы тестов
macros/ - файлы макросов (переиспользуемые Jinja шаблоны)
analysis/ - файлы для Adhoc запросов
dbt_project.yml - основной файл конфигурации проекта.
profiles.yml - файл, в котором настраиваются подключения к базам данных (обычно хранится вне git-репозитория из-за чувствительных данных)
Слои данных
Для эффективного управления и обеспечения доступности, данные разделяют на слои. Команда DBT рекомендует разделять данные на 3 слоя:
Staging – слой сырых данных полученных из источников.
Intermidiate – слой расчетов.
Marts – слой витрин данных (готовые данные для BI систем или ML моделей).
DBT проект содержит .yml файл в котором указываются параметры сущностей
и .sql файл в котором прописывается SQL шаблон для трансформации данных. Каждое имя файла в проекте DBT должно быть уникально. Команда DBT рекомендует использовать определённый подход при наименовании файлов описанный в официальной документации.
Пример наименования для моделей данных:
Сущности DBT
Модели данных (Models)
Models — это основные единицы трансформации данных в DBT. Это SQL шаблоны, которые при выполнении DBT компилируют и выполняют SQL, а затем помещает результат в целевую базу данных как новые таблицы или представления. Они позволяют строить цепочку зависимостей, где модели могут ссылаться друг на друга. Для написания моделей используется SQL и шаблонизатор Jinja.
Структура шаблона выглядит следующим образом:
В примере наша модель ссылается на две таблицы с помощью ref(). Также может присутствовать ссылка на источник sorce(). Такой функционал ссылок позволяет строить цепочку преобразования данных и отслеживать происхождение итоговой таблицы.
В начале файла прописывается конфиг модели в котором указываются параметры логики DBT. Один из самых важных параметров – materialized (тип материализации модели). DBT предоставляет 5 видов материализации моделей:
Table: Создает постоянную таблицу в базе данных. Это подход, когда данные нужно сохранять для дальнейшего использования, и когда необходимо ускорить запросы к ним. Применяется для статичных или редко обновляемых данных.
View: Создает представление (view) в базе данных. Данные не сохраняются физически, и при каждом запросе к представлению выполняется SQL-запрос. Это удобно для динамических наборов данных, которые часто обновляются, но могут быть медленнее в обработке.
Incremental: Используется для добавления новых данных к существующей таблице с минимальными затратами ресурсов. Этот подход особенно используется на больших наборах данных, которые обновляются регулярно – вместо того, чтобы пересоздавать всю таблицу, dbt добавляет только изменения.
Ephemeral: Создает временные таблицы (CTE), которые существуют только во время выполнения текущего запроса. Ephemeral модели не сохраняются в базе данных и полезны для промежуточных расчетов, которые не нужно сохранять для дальнейших запросов.
Materialized View : Это тип материализации в DBT, который создает физическую таблицу в базе данных, как и постоянная таблица, но с возможностью автоматического обновления данных. Материализованные представления сохраняют результаты запроса, что позволяет ускорить доступ к данным, особенно для сложных и ресурсоемких запросов.
Seeds
Seeds - csv файлы которые хранятся в dbt проекте, а не в базе данных. Подходят для хранения небольших объёмов справочных данных. Эти файлы хранятся в основной структуре проекта DBT, поэтому не рекомендуется хранить в них конфидициальную информацию по типу данных авторизации.
Snapshot
Snapshot – функционал, который реализует алгоритм Slowly Changing Dimensions 2ого типа (scd2) Благодаря scd2 можно восстановить состояние таблицы, на момент любой предыдущей загрузке данных. Таким образом вы сможете видеть, как данные изменялись с течением времени.
Существует две стратегии определения изменений в таблице:
Timestamp - определяет изменения в оригинальной таблице на основе поля, в котором хранятся дата и время изменения строки
Check - полностью сравнивает содержимое орегинальной и целевой таблиц
Стратегия timestamp более предпочтительная, так как исполняется более эффективнее и быстрее, но применима не во всех случаях (не применима к таблицам без данных о дате и времени изменения). Стратегия check более медленная, но применима для любой таблицы.
snapshots:
- name: orders_snapshot
relation: source('jaffle_shop', 'orders')
config:
schema: snapshots
database: analytics
unique_key: id
strategy: timestamp
updated_at: updated_at
dbt_valid_to_current: "to_date('9999-12-31')"Adhoc запросы (Analyses)
Иногда аналитику может понадобиться сделать какой-то разовый запрос. Такие запросы называют Adhoc запросами. Для того, чтобы писать adhoc запросы используя dbt, существует сущность Analyses. Analyses никак не меняют структуру базы данных, но при компиляции выдают готовый SQL-запрос для пользователя.
Analyses хранятся в папке /analyses и представляют из себя SQL-шаблоны по структуре идентичные шаблонам моделей (Models).
-- analyses/running_total_by_account.sql
with journal_entries as (
select *
from {{ ref('quickbooks_adjusted_journal_entries') }}
), accounts as (
select *
from {{ ref('quickbooks_accounts_transformed') }}
)
select
txn_date,
account_id,
adjusted_amount,
description,
account_name,
sum(adjusted_amount) over (partition by account_id order by id rows unbounded preceding)
from journal_entries
order by account_id, idMacros
Macros – это аналог функций в языках программирования. То есть какой-то кусок логики который может быть переиспользован много раз, чтобы избежать дублирования кода. Макросы хранятся в папке /macros. Они принимают параметры на основе которых формируют кусок шаблона, который будет подставлен в место вызова макроса.
Предположим мы работаем с гео-данными и нам надо расчитать расстояние до от точки до точки зная широту и долготу. Для такой задачи мы можем написать следующий макрос:
{% macro haversine_distance(lat1, lon1, lat2, lon2) %}
6371 * acos(
cos(radians({{ lat1 }})) cos(radians({{ lat2 }}))
cos(radians({{ lon2 }}) - radians({{ lon1 }})) +
sin(radians({{ lat1 }})) * sin(radians({{ lat2 }}))
)
{% endmacro %}Данный макрос принимает имена колонок с широтой и долготой двух гео-объектов и возвращает расстояние между ними (на самом деле он возвращает sql-шаблон, который встанет на место вызова, но для простоты понимания можно считать что он вернёт готовое значение которое встанет в колонку distance_km)
Теперь мы можем использовать макрос в своей модели данных:
select
id as location_id,
{{ haversine_distance('lat1', 'lon1', 'lat2', 'lon2') }} as distance_km,
...
from app_data.locationsЗапрос без использования макроса выглядел бы так:
select
id as location_id,
6371 * acos(
cos(radians(lat1)) cos(radians(lat2))
cos(radians(lon2) - radians(lon1)) +
sin(radians(lat1)) * sin(radians(lat2))
) as distance_km,
...
from app_data.locationsСамая большая прелесть макросов заключается в том, что под большое количество стандартных задач аналитиков они уже написаны и собраны в пакеты, которые вы можете добавить в проект и использовать сэкономив себе много времени. Такие пакеты можно найти на сайте DbtHub.
Для того чтобы добавить пакет макросов в свой проект нужно создать в корне проекта файл packages.yml и добавить в него следующую структуру с перечислением имён и версий пакетов:
packages:
- package: dbt-labs/codegen
version: 0.13.1
- package: dbt-labs/dbt_utils
version: 1.1.1Tests
Tests позволяют проверять качество и целостность данных в моделях. Они помогают гарантировать, что данные в вашей модели соответствуют определенным критериям. Автоматическое тестирование выполняется во время выполнения DBT, чтобы выявить потенциальные проблемы с качеством данных.
В dbt присутствует 3 вида тестов:
Singular – самый простой вид тестов. Он выполняет запрос к таблице на поиск строк с ошибкой. Если запрос вернул результат (нашёл хотя бы одну неправильную строку), то DBT сообщит об ошибке в данных. Для создания singular теста нужно создать в папке test .sql файл с SELECT запросом на ошибочные данные.
Вот пример Singular теста который проверяет, что нету заказов с отрицательной стоимостью:
select
order_id,
amount,
from {{ ref('orders') }}
where amount < 0Generic – это уже готовых тесты. По умолчанию DBT предоставляет 4 вида тестов
unique – колонка должна содержать уникальные значения
not_null – колонка не должна содержать пустых значений (NULL)
accepted_values – колонка должна содержать только значения из заданного набора
relationships – значения в колонке должны полностью соответствуют значениям в другой колонке
Представим, что нам каждый месяц нужно предоставлять отчёт о 5 ведущих банках России. Предположим, что рейтинг уже заранее выведен и всегда включает одни и те же банки. Нам надо каждый раз проверять, что в отчёт не попала информация о не интересующих нас банках и что имена не дублируются. В таком случае мы можем воспользоваться встроенными Generic-тестами accepted_values и unique. Для этого в папке модели, которую вы хотите протестировать (В нашем случае banks) нужно создать .yml файл со следующей структурой:
version: 2
models:
- name: banks
description: "Таблица лидеров банковского сектора"
columns:
- name: bank_name
description: "Название банка"
tests:
- unique
- accepted_values:
values: ['Сбербанк', 'Альфа-банк', 'ВТБ', 'Т-банк', 'Газпромбанк']Также DBT позволяет писать собственные generic-тесты. предположим мы захотели написать собственный not_null тест. Для этого в папке macros нужно создать .sql файл со следующим синтексом.
{% test my_not_null(model, column_name) %}
select *
from {{ ref(model) }}
where {{ column_name }} is null
{% endtest %}После создания собственного теста мы можем его применить. Например к нашей модели банков:
version: 2
models:
- name: banks
description: "Таблица лидеров банковского сектора"
columns:
- name: bank_name
description: "Название банка"
tests:
- unique
- accepted_values:
values: ['Сбербанк', 'Альфа-банк', 'ВТБ', 'Т-банк', 'Газпромбанк']
- my_not_nullUnit-тесты – позволяют проверить, что наша логика трансформации написана верно. Используются если в ваших запросах присутствует какая-либо сложная логика и вы хотите убедиться что она работает верно. В unit тестах вам нужно задать пример исходных и конечных данных. DBT выполнит трансформацию на примере исходных данных и сравнит результат с примером конечных данных. Если результат выполнения совпал с примером, то тест считается пройденным. Unit-тесты добавляются в .yml файл вашей модели используя следующий синтаксис:
unit_tests:
- name: test_is_valid_email_address
model: dim_customers
given:
- input: ref('stg_customers')
rows:
- {email: cool@example.com, email_top_level_domain: example.com}
- {email: cool@unknown.com, email_top_level_domain: unknown.com}
- {email: badgmail.com, email_top_level_domain: gmail.com}
- {email: missingdot@gmailcom, email_top_level_domain: gmail.com}
- input: ref('top_level_email_domains')
rows:
- {tld: example.com}
- {tld: gmail.com}
expect:
rows:
- {email: cool@example.com, is_valid_email_address: true}
- {email: cool@unknown.com, is_valid_email_address: false}
- {email: badgmail.com, is_valid_email_address: false}
- {email: missingdot@gmailcom, is_valid_email_address: false}Мы рассмотрели все основные сущности DBT, теперь давайте познакомимся с дополнительным функционалом инструмента не привязанным к конкретным сущностям.
Свежесть данных (Data freshness)
Рассмотрим стандартный ELT процесс. У нас по расписанию забираются какие-то данные из источников, после чего происходит расчёт витрин данных на основе которых формируются отчёты. Предположим ELT процесс выполнился успешно, но через какое-то время к нам приходит пользователь отчётов и жалуется, что отчёт не обновился или обновился не полностью. Мы идём разбираться в чём проблема и оказывается, что в каком-то источнике не обновились данные. Для того, чтобы выявлять подобные случаи до того, как пользователь придёт к вам с подобной проблемой DBT разработал функционал Freshness.
Для того, чтобы добавить функционал Freshness нужно прописать в .yml файле параметры проверки свежести данных. Параметры можно указывать как для источников так и для таблиц.
version: 2
sources:
- name: jaffle_shop
database: raw
freshness: # настройки свежести по умолчанию
warn_after: {count: 12, period: hour} # предупреждение через 12 часов
error_after: {count: 24, period: hour} # ошибка через 24 часа
loaded_at_field: etlloaded_at # поле, указывающее на время загрузки
tables:
- name: customers # эта таблица будет использовать настройки свежести по умолчанию
- name: orders
freshness: # более строгие настройки свежести для этой таблицы
warn_after: {count: 6, period: hour}
error_after: {count: 12, period: hour}
# Применяем условие в запросе свежести
filter: datediff('day', etlloaded_at, current_timestamp) < 2 # требуется, чтобы данные были загружены менее 2 дней назад
- name: product_skus
freshness: # не проверять свежесть для этой таблицыМетаданные (Metadata) и генерация документации
Метаданные - это данные о данных. Такие данные позволяют разобраться аналитику или data-инженеру о чем эти данные и как их можно использовать. DBT позволяет задавать описание моделей и источников, описание атрибутов, тэги (для объединения моделей в группы), а также задавать кастомные метаданные по ключу meta. Например вы можете использовать кастомные метаданные для указания владельца данных.
Пример .yml файла с описанием модели обогащённой метаданными:
models:
- name: user_orders
description: "Агрегированные заказы пользователей"
tags: ["marketing", "core"]
meta:
owner: "marketing-team@sberbank.ru"
business_owner: "Руководитель отдела маркетинга"
columns:
- name: user_id
description: "Уникальный ID пользователя"
- name: first_order_date
description: "Дата первого заказа"
- name: total_orders
description: "Общее количество заказов"
- name: avg_order_value
description: "Средний чек (RUB)"
meta:
currency: "RUB"
rounding: 2На основе заданных метаданных можно сгенерировать документацию. Для этого требуется выполнить следующие команды:
dbt docs generate- создаёт статический сайт документацииdbt docs serve- запускает локальный веб-сервер и отображает сгенерированную документацию. По умолчанию сервер разворачивается на порту 8080, но его можно изменить используя флаг -- port .
Заключение
DBT — это инструмент, который способен упростить работу с аналитическими данными. Он предоставляет удобное решение для их трансформации, тестирования и документирования. DBT создает экосистему, где данные, их описание и логика обработки объединены в единое пространство. Это повышает прозрачность процессов и делает их более управляемыми и согласованными.
Надеюсь, эта статья вдохновила вас на то, чтобы интегрировать DBT в свои рабочие процессы. Попробуйте DBT в своём следующем проекте — и вы убедитесь, как легко превращать сырые данные в мощные аналитические решения!
