dbt-utils в глубину: production patterns

dbt-utils — самый используемый dbt package. К моменту middle-уровня вы видели его базовые macros: dbt_utils.surrogate_key, dbt_utils.unique_combination_of_columns, generic tests. Но реальная production-ценность — в advanced patterns: union_relations, get_column_values для dynamic SQL, deduplicate с правильной стратегией, grant_select_to для governance.

Этот урок — про эти patterns. Каждый я разбираю с production gotchas, edge cases, и реальными примерами кода. Версия dbt-utils 1.3+ (2026).

generate_surrogate_key — основа всех PK

-- Создание stable PK из нескольких колонок
{{ dbt_utils.generate_surrogate_key(['customer_id', 'order_date', 'product_id']) }}

Возвращает MD5 hash от concatenated values. Это deterministic — те же inputs дают тот же hash.

Use cases:

1. Composite PK для fact-таблиц без natural single-column key.
2. Surrogate key для dimensions, где natural key может меняться (например, email клиента — может быть отозван, переиздан).
3. Idempotent merge для incremental models — surrogate как unique_key.

-- models/marts/fct_orders.sql
SELECT
    {{ dbt_utils.generate_surrogate_key(['order_id', 'line_item_id']) }} AS pk,
    order_id,
    line_item_id,
    customer_id,
    product_id,
    quantity,
    unit_price
FROM {{ ref('stg_order_items') }}

Gotchas:

1. NULL handling. dbt_utils 1.0+ заменяет NULL на sentinel-строку (‘dbt_utils_surrogate_key_null’) перед hashing. Coллизии возможны если в данных есть точно такая строка — крайне маловероятно, но defensive design в чувствительных случаях:

{{ dbt_utils.generate_surrogate_key([
    'COALESCE(customer_id::text, \'NO_CUSTOMER\')',
    'COALESCE(order_date::text, \'NO_DATE\')'
]) }}

Явные COALESCE дают более явный intent для readers.

2. Type ambiguity. Без явных cast’ов: 42 (int) и '42' (string) могут давать разные hashes в зависимости от warehouse. Стандартизируйте через cast:

{{ dbt_utils.generate_surrogate_key([
    'customer_id::text',
    'order_date::date::text'
]) }}

3. Order matters. ['a', 'b'] ≠ ['b', 'a']. Фиксируйте order через convention в проекте.

4. Не используйте для security. MD5 — не cryptographic hash для модели угроз. Surrogate keys — для identity, не integrity.

unique_combination_of_columns — composite uniqueness test

Generic test, проверяет уникальность комбинации колонок.

# models/marts/fct_orders.yml
models:
  - name: fct_orders
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
            - line_item_id

Это должно быть на всех fact-таблицах где PK составной. unique-тест dbt-core тестирует одну колонку.

Production pattern: использовать в snapshot tests:

snapshots:
  - name: customers_snapshot
    data_tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - customer_id
            - dbt_valid_from

Гарантирует что не появляются дубли версий с одинаковым timestamp.

Gotchas:

• Performance: на больших таблицах — GROUP BY все combination_of_columns + COUNT > 1 — expensive. Используйте where config для subset:

data_tests:
  - dbt_utils.unique_combination_of_columns:
      combination_of_columns: [order_id, line_item_id]
      config:
        where: "order_date >= current_date - 30"

Тест проверяет только последние 30 дней — быстрее. Полный run раз в неделю.

• NULL handling: NULL в одной из колонок может скрыть дубль (NULL != NULL в SQL). Если есть NULL — COALESCE с sentinel или отдельный not_null тест.

deduplicate — выбираем правильную стратегию

dbt_utils.deduplicate(relation, partition_by, order_by) — самый частый use case в staging models.

-- models/staging/stg_customers.sql
{{ dbt_utils.deduplicate(
    relation=source('app', 'customers'),
    partition_by='customer_id',
    order_by='updated_at DESC, _loaded_at DESC'
) }}

dbt компилит в:

SELECT * FROM (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, _loaded_at DESC) AS rn
    FROM raw.app.customers
)
WHERE rn = 1

На DuckDB можно сделать через QUALIFY (быстрее, без subquery):

-- DuckDB-specific
SELECT * FROM raw.app.customers
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC, _loaded_at DESC) = 1

Через dispatch override (см. модуль 05) можно сделать dbt_utils.deduplicate использовать QUALIFY на DuckDB.

Production gotchas:

1. order_by стабильность. Если у duplicate rows одинаковый updated_at, ROW_NUMBER может выбирать произвольную row при разных runs. Добавляйте tie-breaker:

order_by='updated_at DESC, _loaded_at DESC, customer_id'  -- 3-уровневый ORDER BY

2. partition_by для composite. Один column обычно: partition_by='customer_id'. Многие: partition_by='customer_id, region_id' (строка, не список).

3. Deduplicate vs distinct. dbt_utils.deduplicate — на основе window function, возвращает full row first occurrence. SQL DISTINCT — на основе всех колонок, может вернуть unexpected results если есть NULL или type differences.

get_column_values — dynamic SQL

dbt_utils.get_column_values(table, column) — возвращает список уникальных значений колонки. Используется для PIVOT, dynamic columns, conditional logic.

-- models/marts/sales_by_country.sql
{% set countries = dbt_utils.get_column_values(
    table=ref('dim_countries'),
    column='country_code',
    order_by='country_code'
) %}

SELECT
    product_id,
    {% for c in countries %}
      SUM(CASE WHEN country_code = '{{ c }}' THEN revenue ELSE 0 END) AS revenue_{{ c }}
      {%- if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ ref('fct_sales') }}
GROUP BY product_id

5 стран в dim_countries -> 5 колонок revenue_*. Новая страна добавлена в next dbt run — schema extended automatically.

Production gotchas:

1. execute flag: dbt_utils handle это правильно, но если вызывать в parse-time loop — может возвращать empty (placeholder). Compile output надо проверять.

2. Performance в parse-time: каждый dbt compile отправляет SELECT DISTINCT в warehouse. Для маленьких dim — fine, для huge — overhead. Cache через var() / target conditional.

3. Limit max для безопасности:

{% set countries = dbt_utils.get_column_values(
    table=ref('dim_countries'),
    column='country_code',
    order_by='country_code',
    max_records=100  -- защита: если больше 100 уникальных, ошибка
) %}

Без max_records если в dim вдруг появилось 10000 стран (corrupt data) — будет 10000 SUM CASE WHEN — model compile упадёт или будет невероятно медленным.

4. Default value на случай empty:

{% set countries = dbt_utils.get_column_values(
    table=ref('dim_countries'),
    column='country_code',
    default=['US']  -- если 0 строк или parse-time
) %}

Не критично, но safer для edge cases.

union_relations — объединение source-таблиц

dbt_utils.union_relations — UNION ALL множества relations с автоматическим matching колонок.

-- models/staging/stg_all_orders.sql
{{ dbt_utils.union_relations(
    relations=[
        source('app_us', 'orders'),
        source('app_uk', 'orders'),
        source('app_eu', 'orders')
    ],
    source_column_name='_source'  -- добавит колонку с именем relation
) }}

Что делает:

1. Берёт schema каждого relation.
2. Находит union колонок (по имени).
3. Делает SELECT с COALESCE / CAST на каждую относительную колонку, NULL для missing.
4. UNION ALL результатов.
5. Опционально добавляет колонку _source с именем relation.

Compile output:

SELECT
    customer_id,
    order_id,
    amount,
    NULL AS region_specific_field,
    'app_us.orders' AS _source
FROM raw.app_us.orders

UNION ALL

SELECT
    customer_id,
    order_id,
    amount,
    region_specific_field,
    'app_uk.orders' AS _source
FROM raw.app_uk.orders

Production patterns:

1. Multi-region source consolidation. Каждая региональная app пишет в свою schema, dbt union-ит в central staging.

2. Schema evolution support. Если в US появилась new колонка, а UK ещё нет — union_relations corrupted без явных types. Использовать include config:

{{ dbt_utils.union_relations(
    relations=[
        source('app_us', 'orders'),
        source('app_uk', 'orders')
    ],
    include=['order_id', 'customer_id', 'amount']  -- only these columns, ignore others
) }}

Это explicit contract: ‘мы union-им только эти 3 колонки, новые в US/UK игнорируются until added to include list’.

3. Exclude config: симметрично, можно skip определённые колонки:

{{ dbt_utils.union_relations(
    relations=[...],
    exclude=['_loaded_at', '_etl_run_id']
) }}

Skip technical metadata columns.

Gotchas:

• Type coercion. Если amount в US это NUMERIC(10,2), в UK — INTEGER, union_relations попытается cast. Может lose precision. Лучше align types в staging upstream.
• Column ordering: relations с одинаковыми именами но разным order — union обрабатывает по names. Если разные names — добавляется NULL для missing.

grant_select_to — production governance

dbt_utils.grant_select_to — даёт SELECT permission на model после CREATE. Critical для production governance.

# dbt_project.yml
models:
  myproject:
    +post-hook: "{{ dbt_utils.grant_select_to(grantees=['analyst_role', 'bi_service']) }}"

После каждого dbt run — модель получает GRANT SELECT TO listed roles. Без это автоматики:

• Аналитики не могут читать новые модели (нужен manual GRANT каждый раз).
• При recreate (table materialization) permissions могут сбрасываться.

-- Compile result для DuckDB / Postgres / Snowflake (через dispatch)
GRANT SELECT ON {{ this }} TO analyst_role;
GRANT SELECT ON {{ this }} TO bi_service;

Production patterns:

1. Per-environment grants:

models:
  myproject:
    prod:
      +post-hook: "{{ dbt_utils.grant_select_to(grantees=['prod_analyst', 'bi_prod']) }}"
    dev:
      +post-hook: "{{ dbt_utils.grant_select_to(grantees=['developer']) }}"

Dev и prod имеют разные grant lists.

2. Per-model grants:

-- models/marts/restricted_finance_data.sql
{{ config(
    materialized='table',
    post_hook=\"{{ dbt_utils.grant_select_to(grantees=['finance_team_only']) }}\"
) }}

Sensitive models имеют restrictive grants — overrides project-level.

3. dbt 1.5+ grants config (alternative):

+grants:
  select: ['analyst_role', 'bi_service']

Это native dbt feature, без dbt_utils. Часто preferred для new projects (proper grant lifecycle: incrementally added vs revoked at recreate).

Gotchas:

• DuckDB: GRANT не имеет смысла (single-user). grant_select_to silently no-op или error в зависимости от version.
• BigQuery: используется IAM, не SQL GRANT. dbt_utils версия для BigQuery адаптирована.
• Snowflake: проверьте что role exists перед GRANT. dbt не валидирует — GRANT на non-existent role упадёт.

Прочие dbt-utils gem’ы

dbt_utils.star — SELECT * без указанных колонок:

SELECT
    {{ dbt_utils.star(
        from=ref('stg_orders'),
        except=['_loaded_at', '_source_file']
    ) }}
FROM {{ ref('stg_orders') }}

Полезно когда хотите все колонки кроме technical metadata.

dbt_utils.pivot — table pivot:

SELECT
    customer_id,
    {{ dbt_utils.pivot(
        column='order_status',
        values=dbt_utils.get_column_values(ref('fct_orders'), 'order_status'),
        agg='sum',
        then_value='order_total'
    ) }}
FROM {{ ref('fct_orders') }}
GROUP BY customer_id

Generates SUM(CASE WHEN order_status = 'X' THEN order_total END) AS X для каждого status.

dbt_utils.date_spine — generate range дат:

{{ dbt_utils.date_spine(
    datepart='day',
    start_date='cast(\\'2024-01-01\\' as date)',
    end_date='cast(\\'2026-12-31\\' as date)'
) }}

Использует на JOIN’ах для filling missing dates в trend analysis.

dbt_utils.expression_is_true — generic test для произвольного SQL boolean:

columns:
  - name: amount
    data_tests:
      - dbt_utils.expression_is_true:
          expression: "amount >= 0"

Альтернатива singular tests, но проще декларативно.

Production project layout

В большом проекте dbt-utils используется везде. Рекомендую:

1. packages.yml:

packages:
  - package: dbt-labs/dbt_utils
    version: [">=1.3.0", "<2.0.0"]

Pin major version. dbt-utils minor versions backwards compatible, major могут ломать.

2. dbt deps в CI:

dbt deps
dbt parse
dbt build

Без dbt deps — packages not installed, всё ломается.

3. Override registry: если override package macros (через search_order), документируйте в OVERRIDES.md:

| Macro | Original package | Override reason | Last reviewed |
|---|---|---|---|
| deduplicate | dbt_utils | DuckDB QUALIFY optimization | 2026-05-19 |
| grant_select_to | dbt_utils | Add custom role mapping | 2026-04-10 |

Попробуй сам

В labs:

1. Используйте deduplicate для staging customers с tie-breaker order_by.
2. Создайте dynamic PIVOT через get_column_values для sales by category.
3. Union 3 sources через union_relations с include list (только нужные колонки).
4. Generate surrogate key для fct_orders на (order_id, line_item_id), добавьте unique_combination_of_columns тест.
5. Setup grant_select_to в dbt_project.yml на ‘analyst_role’. На DuckDB будет no-op — проверьте что не падает.
6. Override deduplicate для DuckDB через search_order (из модуля 05), используйте QUALIFY.

Это набор production patterns, которые применяются ежедневно.

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

1. generate_surrogate_key — composite PK через MD5. Cast и order matter.
2. unique_combination_of_columns — composite uniqueness test. Используйте where для performance на больших tables.
3. deduplicate — staging dedup. Tie-breaker order_by обязателен для стабильности.
4. get_column_values — dynamic SQL для PIVOT. max_records для safety, default для edge cases.
5. union_relations — multi-source consolidation. include для explicit contract, schema evolution support.
6. grant_select_to — production governance. Per-env grants через target-conditional. dbt 1.5+ имеет native +grants config.
7. Other gems: star (SELECT except), pivot, date_spine, expression_is_true.
8. Pin major version в packages.yml. Document overrides в registry.