dbt_utils: must-have макросы

dbt_utils от dbt Labs — это базовая библиотека для большинства dbt-проектов. Установив её через packages.yml, вы получаете 30+ макросов: helpers для SQL, генерация ключей, work with dates, тесты. На момент 2026 актуальная версия — 1.3.x, совместимая с dbt-core 1.8+.

В этом уроке — обзор самых полезных функций. Не «все 30 макросов», а те, что junior использует на 80% задач.

SQL helpers: star, pivot, deduplicate

dbt_utils.star

Один из самых полезных макросов. Заменяет SELECT * на expanded column list, с возможностью исключить колонки:

-- Вместо:
SELECT * FROM {{ ref('stg_jaffle__customers') }}

-- Используем:
SELECT
    {{ dbt_utils.star(from=ref('stg_jaffle__customers'), except=["created_at", "_loaded_at"]) }}
FROM {{ ref('stg_jaffle__customers') }}

Что произойдёт после dbt compile:

SELECT
    customer_id, first_name, last_name, email, is_active
FROM main.stg_jaffle__customers

dbt сам сгенерирует список колонок (без created_at и _loaded_at), запросив INFORMATION_SCHEMA warehouse.

Зачем это нужно:

1. Гигиена. SELECT * тащит в downstream все колонки, включая _loaded_at, _fivetran_sync_id, и т.д. — мусор для аналитика.
2. Стабильность. Если в source добавится колонка internal_admin_notes, она автоматически потечёт в downstream через SELECT *. С star(except=...) — нет.
3. Performance в некоторых warehouse: explicit columns быстрее.

Альтернативные параметры:

-- Префикс/суффикс для всех колонок (для self-joins):
{{ dbt_utils.star(from=ref('stg_customers'), prefix='cust_') }}
-- -> cust_customer_id, cust_email, ...

{{ dbt_utils.star(from=ref('stg_customers'), suffix='_v1') }}
-- -> customer_id_v1, email_v1, ...

-- Только определённые колонки:
{{ dbt_utils.star(from=ref('stg_customers'), only=['customer_id', 'email']) }}

dbt_utils.pivot

Превращает long-формат в wide:

SELECT
    customer_id,
    {{ dbt_utils.pivot('item_type', dbt_utils.get_column_values(ref('stg_order_items'), 'item_type')) }}
FROM {{ ref('stg_order_items') }}
GROUP BY customer_id

Что генерируется (если item_type содержит ‘food’, ‘drink’, ‘dessert’):

SELECT
    customer_id,
    SUM(CASE WHEN item_type = 'food' THEN 1 ELSE 0 END) AS food,
    SUM(CASE WHEN item_type = 'drink' THEN 1 ELSE 0 END) AS drink,
    SUM(CASE WHEN item_type = 'dessert' THEN 1 ELSE 0 END) AS dessert
FROM stg_order_items
GROUP BY customer_id

Динамический pivot: dbt_utils.get_column_values() запрашивает уникальные значения, и pivot генерит CASE WHEN для каждого. Удобно, когда вы не знаете заранее, какие категории есть.

Параметры:

{{ dbt_utils.pivot(
    'status',                                                       -- column to pivot
    dbt_utils.get_column_values(ref('orders'), 'status'),           -- values to pivot to
    agg='sum',                                                       -- aggregation (default: sum)
    then_value='amount',                                             -- что суммировать (default: 1)
    else_value=0,                                                    -- значение при false
    quote_identifiers=False                                          -- кавычки вокруг имён
) }}

dbt_utils.deduplicate

Удаление дубликатов с keep latest semantic:

-- models/staging/stg_orders.sql
{{ dbt_utils.deduplicate(
    relation=source('jaffle', 'orders'),
    partition_by='order_id',
    order_by='_loaded_at DESC'
) }}

Что генерируется:

SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY _loaded_at DESC) AS rn
    FROM raw_jaffle.orders
)
WHERE rn = 1

Это стандарт для CDC-загруженных source: Fivetran пишет историю изменений, мы берём latest по _loaded_at. После deduplicate получается current state.

Хеши и ключи: generate_surrogate_key

SELECT
    {{ dbt_utils.generate_surrogate_key(['customer_id', 'product_id', 'order_date']) }} AS sk,
    customer_id,
    product_id,
    order_date,
    amount
FROM order_items

Что генерируется:

-- В DuckDB:
SELECT
    md5(coalesce(cast(customer_id as text), '_dbt_utils_surrogate_key_null_') || '-' ||
        coalesce(cast(product_id as text),  '_dbt_utils_surrogate_key_null_') || '-' ||
        coalesce(cast(order_date as text),  '_dbt_utils_surrogate_key_null_')) AS sk,
    ...

Это MD5-хеш конкатенации полей с разделителем и обработкой NULL.

Зачем:

1. Composite primary key как один column. Удобно для JOIN и для SCD2 tracking.
2. Stable. Один и тот же input -> один и тот же hash. Воспроизводимо.
3. NULL-safe. NULL не ломает хеш (заменяется sentinel-значением).

Когда не нужен:

• У вас уже есть natural PK. Не дублируйте.
• Хеш над одной колонкой = бессмысленно. generate_surrogate_key(['id']) ≈ просто MD5(id) — лучше используйте сам id.

Альтернатива в 1.10+: dbt поддерживает встроенный dbt_utils.generate_surrogate_key, но также есть dbt.hash() — встроенная функция dbt-core, более новая. Используйте dbt_utils для совместимости.

Date helpers: date_spine, last_day

dbt_utils.date_spine

Генерирует таблицу с каждой датой в диапазоне (день / неделя / месяц):

-- models/marts/dim_dates.sql
WITH dates AS (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2030-12-31' as date)"
    ) }}
)

SELECT
    date_day AS date,
    EXTRACT(year FROM date_day) AS year,
    EXTRACT(quarter FROM date_day) AS quarter,
    EXTRACT(month FROM date_day) AS month,
    EXTRACT(day FROM date_day) AS day_of_month,
    EXTRACT(dow FROM date_day) AS day_of_week,
    EXTRACT(dow FROM date_day) IN (0, 6) AS is_weekend
FROM dates

date_day — это auto-named column от макроса. Получаем 4000+ строк дат, на которых строим dim_dates.

Параметры:

• datepart: day, week, month, quarter, year.
• start_date: SQL expression (приведённый к date).
• end_date: SQL expression.

Это de facto стандарт генерации date dimension. На каждом адаптере dbt_utils даёт корректный SQL (recursive CTE на Postgres, generate_series на DuckDB и т.д.).

dbt_utils.last_day

Последний день периода:

SELECT
    {{ dbt_utils.last_day('order_date', 'month') }} AS month_end_date
FROM orders

В DuckDB:

SELECT
    (date_trunc('month', order_date) + interval '1 month' - interval '1 day')::date
    AS month_end_date
FROM orders

Удобно для месячных и квартальных отчётов.

Generic тесты в dbt_utils

dbt_utils включает несколько очень полезных тестов поверх стандартных not_null/unique/relationships:

unique_combination_of_columns

Тест, что комбинация колонок уникальна. Например, в fct_order_items должна быть уникальна пара (order_id, line_id):

models:
  - name: fct_order_items
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_id

Стандартный unique не работает на multi-column — поэтому это часто-используемый тест.

expression_is_true

Тест, что любое SQL-выражение возвращает TRUE для всех строк:

models:
  - name: fct_orders
    columns:
      - name: amount
        data_tests:
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: discount_pct
        data_tests:
          - dbt_utils.expression_is_true:
              expression: "BETWEEN 0 AND 1"

Эквивалентно: WHERE NOT (amount >= 0) — если строки есть, тест fail.

equal_rowcount

Тест, что две модели имеют одинаковое количество строк:

models:
  - name: stg_jaffle__orders
    data_tests:
      - dbt_utils.equal_rowcount:
          compare_model: source('jaffle', 'orders')

После staging-чистки rows should be the same. Если staging меньше — кто-то фильтруется неожиданно.

equality

Тест, что две модели идентичны:

models:
  - name: stg_v1
    data_tests:
      - dbt_utils.equality:
          compare_model: ref('stg_v2')
          compare_columns:
            - customer_id
            - email

Для миграции старых моделей на новые: проверить, что результат не меняется.

fewer_rows_than / cardinality_equality

models:
  - name: dim_active_customers
    data_tests:
      - dbt_utils.fewer_rows_than:
          compare_model: ref('dim_customers')

Тест, что active customers — это subset all customers.

Полный пример: dim_dates

Соберём date dimension с dbt_utils:

-- models/marts/core/dim_dates.sql
{{ config(materialized='table') }}

WITH dates AS (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2020-01-01' as date)",
        end_date="cast('2030-12-31' as date)"
    ) }}
)

SELECT
    date_day AS date,
    EXTRACT(year FROM date_day) AS year,
    EXTRACT(quarter FROM date_day) AS quarter,
    EXTRACT(month FROM date_day) AS month,
    EXTRACT(day FROM date_day) AS day_of_month,
    EXTRACT(week FROM date_day) AS week_of_year,
    STRFTIME(date_day, '%A') AS day_of_week_name,
    EXTRACT(dow FROM date_day) AS day_of_week_iso,
    EXTRACT(dow FROM date_day) IN (0, 6) AS is_weekend,
    date_day = {{ dbt_utils.last_day('date_day', 'month') }} AS is_last_day_of_month,
    date_day = date_trunc('quarter', date_day) AS is_first_day_of_quarter
FROM dates

Запустите dbt run --select dim_dates. В warehouse — таблица с 4017 строками (10 лет дат).

В YAML добавим тесты:

models:
  - name: dim_dates
    description: "Календарь дат 2020-2030. Generated via dbt_utils.date_spine."
    columns:
      - name: date
        description: "Дата (one row per day)"
        data_tests:
          - unique
          - not_null
      - name: year
        data_tests:
          - dbt_utils.expression_is_true:
              expression: "BETWEEN 2020 AND 2030"

dbt test --select dim_dates запустит тесты. Один из них — expression_is_true — это уже dbt_utils.

Quick reference table

И тесты:

Antipatterns

Попробуй сам

В вашем dbt-проекте:

1. Установите dbt_utils (если ещё нет):

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.0

dbt deps

2. Создайте dim_dates через date_spine:

-- models/marts/dim_dates.sql
{{ config(materialized='table') }}

WITH dates AS (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="cast('2024-01-01' as date)",
        end_date="cast('2027-01-01' as date)"
    ) }}
)

SELECT
    date_day AS date,
    EXTRACT(year FROM date_day) AS year,
    EXTRACT(month FROM date_day) AS month,
    EXTRACT(dow FROM date_day) IN (0, 6) AS is_weekend
FROM dates

dbt run --select dim_dates
dbt show --inline "SELECT * FROM {{ ref('dim_dates') }} ORDER BY date LIMIT 10"

3. Используйте star в staging:

-- models/staging/stg_clean_customers.sql
SELECT
    {{ dbt_utils.star(from=source('raw', 'customers'), except=['_loaded_at', '_synced_at']) }}
FROM {{ source('raw', 'customers') }}

4. Добавьте unique_combination test в YAML:

models:
  - name: fct_order_items
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_id

Запустите dbt test. Проверьте, что unique_combination работает.

Бонус: используйте dbt_utils.generate_surrogate_key для PK таблицы snapshot.

Ключевые выводы

1. dbt_utils — стандартный пакет, обязательный к установке в production-проектах.
2. star(from=, except=) — SELECT с исключением колонок. Гигиена staging-слоя.
3. pivot() — long -> wide. Динамический pivot через get_column_values().
4. deduplicate(rel, partition_by, order_by) — стандарт для CDC source чистки.
5. generate_surrogate_key([cols]) — MD5 composite key, NULL-safe. Для SCD2 и composite PK.
6. date_spine(part, start, end) — генерация календаря. Основа dim_dates.
7. Тесты: unique_combination_of_columns (multi-col uniqueness), expression_is_true (any SQL expr), equal_rowcount (two models same count), equality (two models identical).
8. Antipatterns: star везде, surrogate_key где не нужен, date_spine без bounds, get_column_values в parse phase на больших таблицах.