От SQL-запроса до результата — в одном окне. Обзор на dbt-модуль в SubQuery

Привет! Меня зовут Маргарита, я BI-архитектор в Далее. Моя задача — чтобы клиенты получали подготовленные данные в удобном для них виде. В этом мне помогает dbt (Data Build Tool).

Раньше мы использовали локальную версию dbt, а с прошлого года перешли на внутреннюю веб-платформу SubQuery. Теперь все задачи я делаю в одном окне, и на ту же работу уходит заметно меньше времени: по моим ощущениям, в 2-3 раза.

Возможно, этот обзор будет полезен коллегам, которые тоже работают с данными. Первое, к чему я быстро привыкла, — результат SQL-запроса модели dbt можно посмотреть сразу в превью. Не нужно отдельно открывать СУБД  и проверять данные на корректность выполненного SQL. С этого, пожалуй, и начну…

Если коротко, SubQuery — платформа для работы с большими данными. Здесь уже развернут dbt, есть SQL-редактор, граф моделей, ветки, расписание и API, а еще — история изменений, логи запусков, ИИ-генерация документации и многое другое. 

В этом обзоре я говорю только о dbt-модуле, т. к. именно в нем провожу большую часть дня.

Сначала смотрю результат, потом сохраняю модель

Допустим, мне нужно собрать таблицу с заказами пользователей. Dbt-модель может выглядеть так:

{{
    config(
        enabled= true,
        tags = ['orders'],
        unlogged = true,
        meta= {'owner': 'mgrtmark'},
        materialized= 'table',
        description = 'заказы'
    )
}}

select
    order_id,
    order_number,
    status,
    shipment_date,
    delivering_date
from {{ ref('orders') }}

Запрос можно выполнить целиком или выделить нужный фрагмент. Результат появится в превью: значения, названия колонок и типы данных. Там же можно отсортировать строки, закрепить столбец или скопировать названия полей в следующий запрос.

Раньше я отлаживала и оптимизировала запросы SQL в консоли СУБД, а потом переносила в локальный dbt. Сейчас делаю все в одном окне: и отладку, и запуск, и фикс ошибок — это очень удобно.

Редактор в dbt проекте можно настроить под себя: от шрифтов до автодополнения и подсказок.

Есть также поиск по проекту и переход к модели из ref() — что удобно, когда тебе нужно быстро перейти в источник\родительскую модель.

В результат запроса базово можно вывести до 200 строк. Для проверки запроса этого хватает, но при необходимости количество строк можно увеличить до 1000 через настройки dbt.

Продакшн-данные и данные для тестов и отладки у меня лежат отдельно в разных базах. В dbt для этого есть targets. Сначала работаю с запросом\моделью на stage, проверяю, прогоняю тесты. Когда результат меня устраивает, переключаюсь на prod и запускаю уже там. 

В SubQuery нужный target выбирается в верхней панели. В локальном проекте те же подключения настраиваются через profiles.yml и выбором флага при запуске. Сама логика знакомая, поэтому переучиваться не пришлось. В SubQuery достаточно выбрать target в панели (это просто и безопасно), а не каждый раз думать о том, чтобы не забыть дописать параметр в команду в терминале.

Запросы мы меняем в отдельных ветках

Плюс в том, что мы с коллегой можем работать над одной моделью: каждый в своей рабочей области, над своим участком. Перед тем как опубликовать модель, я подтягиваю изменения в свою раб. область из master-ветки, проверяю, какие изменения были сделаны в модели, и потом публикую. 

Если возникают конфликты, их можно легко разрешить в одном окне, написать комментарий, проверить на компиляцию. В master-ветке можно посмотреть историю версий и понять: кто, как, когда и какие файлы менял.

Перед тем как данные попадают в прод, они проходят обязательное тестирование. Базовые тесты dbt уже встроены в SQ: их можно легко добавить прямо в документацию к модели. Есть и кастомные\специфичные тесты. Например: на наличие дублей в источнике, проверка источника на полноту данных, автогенерация запроса при изменении схемы таблицы и другие.

DAG (Directed Acyclic Graph) доступен в одном окне

Когда работаешь с финальной витриной, нужно понимать, какие модели готовят для нее данные (предки) и что зависит уже от нее (потомки).

В SubQuery граф открывается в панели вывода сразу под запросом: модели — узлы, связи — линии. По схеме понятны и состав цепочки, и направление зависимостей.

На графе можно выбрать конкретную модель, посмотреть ее предков и потомков, отфильтровать граф по названию или тегу. Для большого проекта это быстрее, чем разбирать зависимости по именам файлов.

Отсюда же запускается модель вместе с нужной частью графа. Например, можно пересчитать ее с предками или, наоборот, обновить все зависимые модели. Перед запуском видно, какая цепочка попадет в расчет.

В локальном dbt граф тоже был. Я смотрела его через dbt docs. Открывала отдельную страницу, находила модель и уже там разбиралась с зависимостями. В SubQuery код, результат запроса и граф находятся в одном окне.

Документацию к модели можно сгенерировать

Сервис подготовит описания модели и колонок, но их нужно проверить. Бизнес-смысл иногда определяется неточно . Я использую генерацию как черновик, затем правлю формулировки при необходимости. Также можно посмотреть документацию на отдельной странице, найти модели по поиску, овнеру, наличию тестов, макросов и источников.

Обновление моделей запускаю по расписанию или по событию

Обычные расчеты я ставлю на расписание. В нем выбираются команда dbt и selector. Cron можно ввести вручную или собрать из полей с днями и временем. 

Ошибки приходят на почту, а результаты запусков сохраняются в истории. Для пересчета прошлого периода есть бэкфил: задаешь диапазон и шаг, например неделю.

Но источник не всегда обновляется в одно и то же время. У нас данные из источников в базу загружаются с помощью скриптов в  Airflow. Сегодня скрипт закончит в 03:20, завтра — в 04:10. Если поставить dbt-модель на фиксированное время, придется оставлять запас или рисковать запуском до окончания загрузки.

В таком сценарии я использую API SubQuery. В скрипте добавляется запрос с токеном, который дергает конкретную модель или тег, после чего пересчитывается нужный DAG. Это позволяет не гадать со временем обновления, так как нагрузка на базу может быть разной и нет риска остаться с неполными данными или вовсе без них.

Пример запуска по событию:

import requests


  requests.post(
    'https://subquery.ru/api/dbt/run',
    headers={
        'Content-Type': 'application/json',
        'Authorization': 'Bearer ••••••••••••••••••••••••••••••••'  # Вместо точек вставляется токен
    },
    json={
        'projectId': '•••••••',   # Id проекта
        'branchId': '•••••••',    # Id ветки
        'command': 'RUN',         # Допустимые значения: RUN | RUN_OPERATION | TEST | SNAPSHOT | SEED | BUILD | COMPILE | SOURCE_FRESHNESS
        'model': 'my_first_dbt_model.sql'
    }
  )

Что еще есть для работы с данными

Я ограничилась dbt, чтобы обзор не превратился в справочник по всей платформе. Но у SubQuery есть еще несколько модулей, которые пригодятся аналитикам.

В файловом менеджере можно хранить данные на S3. Например, у меня там лежат словари, к которым я обращаюсь из запросов. Один такой справочник могут использовать несколько моделей, поэтому его не приходится дублировать внутри проекта.

В Python-ноутбуках доступны ячейки Python, SQL, DuckDB и Markdown. К ним можно подключить СУБД, обработать файл, проверить гипотезу и вывести результат таблицей или графиком. Библиотеки устанавливаются из доступного каталога. 

В Ноутбуках можно как читать, так и писать данные в базу, а еще — создавать запросы к API. Это очень помогает при необходимости забора внешних данных для проведения различных исследований. 

Еще есть Потоки для загрузки исходных данных с площадок. Модуль пока развивается, поэтому здесь не буду разбирать его без отдельного рабочего примера.

Лайфхак — как в SubQuery не тратить лимит на запуски

Для индивидуальной работы с данными платформу можно использовать бесплатно. На стартовом тарифе доступны 5 участников команды, один редактор, один проект, один target и 100 dbt-запусков в месяц. Запуск по расписанию тоже входит в лимит.

Чтобы не тратить лимит на отладку, я сначала проверяю запрос в SQL-консоли. Запуски из нее не расходуют лимиты. В файл модели переношу уже собранный вариант.

Одна модель с обновлением по расписанию два раза в день израсходует около 60 в месяц. Для небольшого проекта одного аналитика 100 запусков хватит. 

Еще из приятного — все пошагово расписано в документации, а регистрация не через заявки, а прямо на сайте subquery.ru. Можно для начала просто зайти и позапускать модельки в демо-проекте.

А что используете вы: локальный dbt, облачную платформу или свою связку из нескольких инструментов?