dbtmacrosJinjaSQLdata engineering

dbt macros: 8 паттернов, которые экономят часы рутины

2026-06-02 11 мин

dbt macros — Jinja-функции, которые генерируют SQL. Маленькие, переиспользуемые куски. Большинство аналитиков останавливается на {{ ref() }} и {{ source() }} — но макросы могут заменить сотни строк копипасты одной строчкой.

В этом гайде — 8 практических паттернов, которые я использую в продакшен dbt-проектах: от dynamic SQL до audit-логирования.

Зачем макросы

Типичная боль: повторяющиеся куски SQL. Например, конвертация валют в USD есть в 12 моделях. При изменении курса — менять в 12 местах. С макросом — в одном.

-- macros/to_usd.sql
{% macro to_usd(amount_col, currency_col) %}
  CASE {{ currency_col }}
    WHEN 'USD' THEN {{ amount_col }}
    WHEN 'EUR' THEN {{ amount_col }} * 1.08
    WHEN 'RUB' THEN {{ amount_col }} / 90
    ELSE NULL
  END
{% endmacro %}
-- model: orders_normalized.sql
SELECT
  order_id,
  {{ to_usd('amount', 'currency') }} AS amount_usd
FROM {{ ref('orders_raw') }}

Паттерн 1: dynamic columns (pivot без копипаста)

Часто нужно сделать pivot по динамическому списку категорий. Например, выручка по месяцам.

{% macro pivot_months(amount_col, months) %}
  {% for m in months %}
    SUM(CASE WHEN month = '{{ m }}' THEN {{ amount_col }} END) AS revenue_{{ m }}
    {% if not loop.last %},{% endif %}
  {% endfor %}
{% endmacro %}
-- model
SELECT
  product_id,
  {{ pivot_months('amount', ['2026-01', '2026-02', '2026-03', '2026-04']) }}
FROM {{ ref('orders') }}
GROUP BY product_id

Сгенерится:

SELECT product_id,
  SUM(CASE WHEN month = '2026-01' THEN amount END) AS revenue_2026-01,
  SUM(CASE WHEN month = '2026-02' THEN amount END) AS revenue_2026-02,
  ...

Паттерн 2: generate_schema_name (multi-env)

По умолчанию dbt пишет в схему profiles.yml + schema: из модели. Это даёт схему типа analytics_dbt_dev. Уродливо.

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) %}
  {%- set default_schema = target.schema -%}
  {%- if target.name == 'prod' -%}
    {{ custom_schema_name | trim }}
  {%- else -%}
    {{ default_schema }}_{{ custom_schema_name | trim }}
  {%- endif -%}
{% endmacro %}

Теперь:

Паттерн 3: audit-логирование на каждый run

Хочешь знать сколько rows прошло через каждую модель? Добавь хук + макрос.

-- macros/log_model_audit.sql
{% macro log_model_audit() %}
  INSERT INTO {{ target.schema }}.dbt_audit_log (
    model_name, schema_name, run_started_at, rows_affected
  )
  VALUES (
    '{{ this.name }}',
    '{{ this.schema }}',
    '{{ run_started_at }}',
    (SELECT COUNT(*) FROM {{ this }})
  )
{% endmacro %}
# dbt_project.yml
on-run-end:
  - "{{ log_model_audit() }}"

Каждый dbt run теперь логирует кол-во строк в audit-таблицу. Можно увидеть аномалии (например резкое падение rows = baseline check).

Паттерн 4: parametrized тесты

dbt-tests из коробки знают unique, not_null, relationships, accepted_values. Но что если нужен свой?

-- macros/test_within_n_days.sql
{% test within_n_days(model, column_name, days=7) %}
SELECT *
FROM {{ model }}
WHERE {{ column_name }} < CURRENT_DATE - INTERVAL '{{ days }}' DAY
{% endtest %}
# schema.yml
models:
  - name: events
    columns:
      - name: event_ts
        tests:
          - within_n_days:
              days: 30  # данные не старше 30 дней

Паттерн 5: surrogate_key (через dbt_utils)

Создание стабильного surrogate-ключа из бизнес-полей.

-- model
SELECT
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_ts', 'event_type']) }} AS event_pk,
  user_id,
  event_ts,
  event_type
FROM {{ source('raw', 'events') }}

Под капотом — MD5 от concatenated значений. Стабильно, deterministic — идемпотентность гарантирована.

Паттерн 6: dbt_utils.pivot для динамики

dbt_utils пакет даёт готовый pivot:

{{ dbt_utils.pivot(
    'event_type',
    dbt_utils.get_column_values(ref('events'), 'event_type')
) }}

Это сам получит список значений из таблицы через query и сгенерит SUM(CASE WHEN event_type='X' THEN 1 END) AS X для каждого. Динамический pivot без копипаста.

Паттерн 7: incremental с lookback window

При incremental загрузке часто нужен «lookback» — захватить последние N дней (на случай late-arriving data).

{{
  config(
    materialized='incremental',
    unique_key='event_pk',
    on_schema_change='append_new_columns'
  )
}}

SELECT
  {{ dbt_utils.generate_surrogate_key(['user_id', 'event_ts']) }} AS event_pk,
  user_id, event_ts, event_type
FROM {{ source('raw', 'events') }}
{% if is_incremental() %}
  WHERE event_ts >= (SELECT MAX(event_ts) - INTERVAL '3 day' FROM {{ this }})
{% endif %}

3-дневный lookback ловит данные опоздавшие на 1-2 дня. Подробнее про incremental — в нашем гайде.

Паттерн 8: conditional select по env

В dev — sample 10000 строк, в prod — все. Без копипаста.

-- macros/limit_for_dev.sql
{% macro limit_for_dev(n=10000) %}
  {% if target.name == 'dev' %}
    ORDER BY RANDOM() LIMIT {{ n }}
  {% endif %}
{% endmacro %}
-- model
SELECT * FROM {{ source('raw', 'events') }}
{{ limit_for_dev(10000) }}

В dev — каждый run быстрый. В prod — без сэмпла.

Подводные камни

Грабли 1: Jinja vs SQL комментарии

В Jinja {# comment #} не попадает в финальный SQL. А -- comment попадает. Используй {# #} для метаинформации макроса (не «грязнит» итоговый SQL).

Грабли 2: trailing whitespace

Jinja сохраняет пробелы / переводы строк. Используй {%- -%} (минус-знаки) для trim'a:

{%- if target.name == 'prod' -%}...{% endif -%}

Грабли 3: тестировать макрос отдельно

dbt compile сгенерит SQL без выполнения. Запускай и смотри в target/compiled/.sql — это финальный SQL. Не угадывай по Jinja.

Грабли 4: macro vs SQL function

Макрос работает во время компиляции dbt. SQL-функция (в БД) — в runtime. Для логики, которая меняется per environment — макрос. Для логики, которая идёт в WHERE / JOIN — SQL-функция в БД.

Частые вопросы

Какие dbt-пакеты обязательны?

dbt_utils — must-have (surrogate_key, pivot, generate_series, get_column_values). Дальше — dbt_expectations (Great Expectations-стиль тесты), elementary (data observability), dbt_artifacts (run history).

Можно ли вызывать макрос из другого макроса?

Да: {{ my_other_macro() }} внутри {% macro X() %}. Главное — порядок (определения должны быть видны). dbt автоматически парсит все macros/ директории.

Где хранить макросы — в моделях или отдельно?

В отдельной директории macros/ корня проекта. Один файл = один макрос (по convention). dbt подхватит все.

Можно ли использовать макрос внутри YAML тестов?

Да, через {{ }}. Например, tests: - my_custom_test: param1: '{{ var("my_var") }}'.

Что делать, если макрос работает в dev, но падает в prod?

99% — разные диалекты SQL (Snowflake vs Postgres vs ClickHouse). Используй {% if target.type == 'postgres' %} для conditional SQL.

Что дальше

Если хочешь практику — попробуй SQL-тренажёр с автопроверкой (5 задач бесплатно). В обоих диалектах SQL (SQLite + PostgreSQL 16) — повторяй паттерны dbt без необходимости поднимать пайплайн.

Готов к собеседованиям? AI-интервью тренирует ответы на реальных вопросах из бесплатного пула. В Pro — безлимит мок-собесов с разбором + 491 SQL-задача + 612 тестовых заданий из реальных собесов 2026.

Смежные посты

Сравнить Free и Pro → (1999₽/мес, экономит часы рутины)

Источники

SQL-тренажёр
Настоящий PostgreSQL 16: повторяй паттерны dbt-моделей вживую. 491 SQL-задача с автопроверкой, первые 5 бесплатно.
Открыть тренажёр →

Похожие статьи