Custom utility macro: пишем свой helper

В уроках 1-2 мы изучили теорию: синтаксис macros и использование в моделях. Готовые решения из dbt_utils пройдём в модуле 16 (Packages). Теперь финальный практический урок — пишем свой macro с нуля, проходя весь путь: от «я повторяю SQL» до production-ready helper с тестами и документацией.

В качестве примера возьмём задачу из реальных dbt-проектов: конверсия центов в доллары (или копеек в рубли, или satoshi в bitcoin). Это типовой helper, который встречается во всех staging-моделях fintech и e-commerce проектах.

Этап 1: распознать паттерн

В проекте Jaffle Shop вы пишете staging:

-- models/staging/stg_jaffle__orders.sql
select
    order_id,
    customer_id,
    (total_amount_cents / 100.0)::numeric(16, 2) as total_amount,
    (tax_amount_cents / 100.0)::numeric(16, 2) as tax_amount,
    (shipping_cost_cents / 100.0)::numeric(16, 2) as shipping_cost
from {{ source('jaffle', 'raw_orders') }}

И аналогично:

-- models/staging/stg_jaffle__payments.sql
select
    payment_id,
    order_id,
    (amount_cents / 100.0)::numeric(16, 2) as amount,
    (refund_amount_cents / 100.0)::numeric(16, 2) as refund_amount
from {{ source('jaffle', 'raw_payments') }}

Если по этому пути идти, ещё 5 моделей, и (... / 100.0)::numeric(16, 2) встретится 30 раз. Любое изменение (precision, currency conversion logic) — 30 правок.

Знак паттерна: одна и та же логика повторяется 3+ раз в разных моделях. Время macro.

Этап 2: спроектировать сигнатуру

Перед тем как писать SQL, ответьте на 4 вопроса:

1. Имя. Что делает macro? cents_to_dollars — описывает результат, не процесс. Хорошо.
2. Входы. Один параметр — имя колонки. column_name. Если хотим precision настраиваемой — precision=2, default разумный.
3. Выход. SQL-фрагмент типа (col / 100.0)::numeric(16, 2). Implicit return.
4. Edge cases. Что если колонка NULL? CAST(NULL / 100) = NULL — OK, без изменений. Что если 0? Тоже OK.

Сигнатура:

{% macro cents_to_dollars(column_name, precision=2) %}
    ...
{% endmacro %}

Этап 3: написать

-- macros/finance/cents_to_dollars.sql

{% macro cents_to_dollars(column_name, precision=2) %}
{# 
  Конвертирует int-колонку с центами в numeric с долларами.
  
  Args:
    column_name (str): имя SQL-колонки (или выражения), содержащей int-центы.
    precision (int): количество знаков после запятой. Default 2.
  
  Returns:
    SQL-фрагмент типа (column_name / 100.0)::numeric(16, precision)
  
  Examples:
    {{ cents_to_dollars('total_amount_cents') }}
    -- -> (total_amount_cents / 100.0)::numeric(16, 2)
    
    {{ cents_to_dollars('amount_cents', 4) }}
    -- -> (amount_cents / 100.0)::numeric(16, 4)
#}
    ({{ column_name }} / 100.0)::numeric(16, {{ precision }})
{% endmacro %}

Что здесь сделано:

• Каталог macros/finance/ — группировка по теме (финансы). dbt сканирует рекурсивно.
• Docstring через {# #} с Args/Returns/Examples — стандарт Python-документации.
• Default precision=2 — разумный для долларов.
• Минимальное тело — одна строка с подстановкой.

Этап 4: использовать в моделях

Перепишем stg_jaffle__orders:

select
    order_id,
    customer_id,
    {{ cents_to_dollars('total_amount_cents') }} as total_amount,
    {{ cents_to_dollars('tax_amount_cents') }} as tax_amount,
    {{ cents_to_dollars('shipping_cost_cents') }} as shipping_cost
from {{ source('jaffle', 'raw_orders') }}

Запустим dbt compile:

select
    order_id,
    customer_id,
    (total_amount_cents / 100.0)::numeric(16, 2) as total_amount,
    (tax_amount_cents / 100.0)::numeric(16, 2) as tax_amount,
    (shipping_cost_cents / 100.0)::numeric(16, 2) as shipping_cost
from "jaffle_shop"."main"."raw_orders"

Идентично прежнему SQL, но теперь в одном месте.

Этап 5: расширить, если нужно

Через неделю выясняется, что в Японии нет центов — yen хранится целым числом. Нужен флаг «не конвертировать»:

{% macro cents_to_dollars(column_name, precision=2, divisor=100) %}
{# 
  ...
  Args:
    divisor (int): на сколько делить. Default 100 (центы). Для yen — 1.
#}
    ({{ column_name }} / {{ divisor }}.0)::numeric(16, {{ precision }})
{% endmacro %}

Использование:

{{ cents_to_dollars('amount_yen', divisor=1) }}
-- -> (amount_yen / 1.0)::numeric(16, 2)

Старые вызовы продолжают работать (default divisor=100). Backward-compatibility через defaults — фундаментальный принцип эволюции macros.

Этап 6: тестировать

Macro сам по себе не тестируется. Но модели, которые его используют, тестируются обычным dbt test. Если macro кривой, тесты упадут.

Добавим тест к staging:

# models/staging/_models.yml
models:
  - name: stg_jaffle__orders
    columns:
      - name: total_amount
        data_tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"

Если кто-то сломает macro (например, забудет * 100), total_amount окажется в центах (огромные числа). Тест >= 0 пройдёт, но dbt_utils.expression_is_true: "<= 1000000" — упадёт, если есть заказ на $5000 (500,000 центов).

Лучший подход — unit test для модели, использующей macro:

# models/staging/_unit_tests.yml
unit_tests:
  - name: test_stg_orders_converts_cents
    model: stg_jaffle__orders
    given:
      - input: source('jaffle', 'raw_orders')
        rows:
          - {order_id: 1, customer_id: 'c1', total_amount_cents: 12345, tax_amount_cents: 1000, shipping_cost_cents: 500}
    expect:
      rows:
        - {order_id: 1, customer_id: 'c1', total_amount: 123.45, tax_amount: 10.00, shipping_cost: 5.00}

Этот unit test ЯВНО проверяет, что macro правильно конвертирует. Если кто-то изменит macro, тест поймает.

Полный пример: macro safe_divide с тестом

Возьмём ещё один частый паттерн — безопасное деление, которое не падает на zero:

-- macros/math/safe_divide.sql

{% macro safe_divide(numerator, denominator, fallback='null') %}
{# 
  Безопасное деление с fallback при denominator=0.
  
  Args:
    numerator (str): имя колонки или выражение в числителе.
    denominator (str): имя колонки или выражение в знаменателе.
    fallback (str): SQL-выражение, возвращаемое при denominator=0. Default 'null'.
  
  Returns:
    SQL-фрагмент case-when.
  
  Examples:
    {{ safe_divide('total_revenue', 'order_count') }}
    -- -> case when order_count = 0 then null else total_revenue / order_count end
    
    {{ safe_divide('returns', 'orders', '0') }}
    -- -> case when orders = 0 then 0 else returns / orders end
#}
    case 
        when {{ denominator }} = 0 then {{ fallback }}
        else {{ numerator }} / {{ denominator }}
    end
{% endmacro %}

Использование:

select
    customer_id,
    {{ safe_divide('total_revenue', 'order_count') }} as avg_order_value,
    {{ safe_divide('returns', 'orders', '0') }} as return_rate,
    {{ safe_divide('refunded_amount', 'paid_amount', '0.0') }} as refund_pct
from {{ ref('marts__customer_summary') }}

Compile:

select
    customer_id,
    case when order_count = 0 then null else total_revenue / order_count end as avg_order_value,
    case when orders = 0 then 0 else returns / orders end as return_rate,
    case when paid_amount = 0 then 0.0 else refunded_amount / paid_amount end as refund_pct
from "jaffle_shop"."main"."marts__customer_summary"

Unit test для модели:

unit_tests:
  - name: test_marts_customer_avg_order_value
    model: marts__customer_summary_kpi
    given:
      - input: ref('marts__customer_summary')
        rows:
          - {customer_id: 'c1', total_revenue: 1000.00, order_count: 4}
          - {customer_id: 'c2', total_revenue: 0.00, order_count: 0}
    expect:
      rows:
        - {customer_id: 'c1', avg_order_value: 250.00}
        - {customer_id: 'c2', avg_order_value: null}

Test проверяет два кейса: обычный и edge (zero denominator).

Когда macro — это плохая идея

Macros — мощный инструмент, но легко переусердствовать. Не всё, что повторяется, заслуживает macro. Признаки over-engineering:

1. Macro используется один раз. Если повторений нет, inline SQL чище.

{# Bad #}
-- macros/get_active_orders.sql
{% macro get_active_orders() %}
    select * from {{ ref('stg_orders') }} where status = 'active'
{% endmacro %}

-- В единственной модели:
{{ get_active_orders() }}

{# Good — без macro #}
select * from {{ ref('stg_orders') }} where status = 'active'

2. Macro имеет 8+ параметров. Скорее всего, делает несколько вещей. Раздели.

3. Macro = тонкая обёртка без added value. my_md5(x) = {{ "md5(" ~ x ~ ")" }} — wrapper без логики. Просто пиши md5(x).

4. Macro вызывает себя рекурсивно с сложной логикой. Скорее всего, перепутали инструмент: для сложных трансформаций лучше готовый Python-скрипт или Spark.

5. Macro используется для чего-то, для чего есть adapter-функция. Многие задачи закрыты dbt-командой через adapter.X(). Не пишите свой list_tables().

Хорошие практики

1. Group by topic в подкаталогах. macros/finance/, macros/string_utils/, macros/date_helpers/. Поиск проще.

2. README в каталоге macros. Перечень macros с одной строкой описания. Команда новичков скажет спасибо.

3. Docstring обязателен. Args, Returns, Examples. Через год вы сами не вспомните.

4. Default — для частого случая. Если 90% вызовов с precision=2, делай default 2.

5. Backward-compat через defaults. Новые параметры — с default, чтобы не ломать существующие вызовы.

6. Не более 2 уровней вложенности macro->macro. Глубже — debug превращается в кошмар.

7. Unit tests для критичных macros. Через модели, которые их используют.

8. Версии в комментариях. «v1: добавил precision, 2026-05-10» — полезно при code review.

Структура macros/ в зрелом проекте

macros/
├── README.md
├── _global_helpers.sql          # часто используемые мелочи
├── finance/
│   ├── cents_to_dollars.sql
│   ├── currency_convert.sql
│   └── README.md
├── date_helpers/
│   ├── add_business_days.sql
│   ├── get_fiscal_quarter.sql
│   └── README.md
├── string_utils/
│   ├── clean_email.sql
│   ├── clean_phone.sql
│   └── README.md
└── tests/                       # generic tests (либо в tests/generic/)
    ├── test_is_positive.sql
    └── test_within_range.sql

Такая структура хорошо переживает рост проекта.

Попробуй сам

Напишите macro format_phone(column_name, country='RU'), который нормализует номер телефона:

• Для country=‘RU’: превращает +7 (123) 456-78-90 или 8(123)4567890 в +7 1234567890.
• Для country=‘US’: превращает (123) 456-7890 в +1 1234567890.

Сигнатура:

{% macro format_phone(column_name, country='RU') %}
{# 
  Нормализует phone number в формат +X XXXXXXXXXX.
#}
    case 
        {%- if country == 'RU' -%}
        when length(regexp_replace({{ column_name }}, '[^0-9]', '', 'g')) = 11 
        then '+7 ' || right(regexp_replace({{ column_name }}, '[^0-9]', '', 'g'), 10)
        else null
        {%- elif country == 'US' -%}
        when length(regexp_replace({{ column_name }}, '[^0-9]', '', 'g')) = 10
        then '+1 ' || regexp_replace({{ column_name }}, '[^0-9]', '', 'g')
        else null
        {%- else -%}
        else null
        {%- endif -%}
    end
{% endmacro %}

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

select
    customer_id,
    {{ format_phone('phone') }} as phone_clean,
    {{ format_phone('emergency_contact', 'US') }} as emergency_phone
from {{ source('jaffle', 'raw_customers') }}

Запустите dbt compile и проверьте output. Напишите unit test с тремя кейсами: правильный RU-номер, кривой формат (должен дать null), US-номер.

Итоги

• Macro оправдан, когда логика повторяется 3+ раз, общая (не уникальная), edge cases стоит инкапсулировать.
• Чек-лист написания: имя по результату, default для частого случая, docstring (Args/Returns/Examples), backward-compat через defaults.
• Структура: подкаталоги по теме (finance/, string_utils/), README в каждом, один macro в файле.
• Тестируется через unit tests модели, использующей macro.
• Антипаттерны: macro для одного использования, 8+ параметров, тонкая обёртка без логики, глубокая рекурсия.

Модуль 10 закрыт. Вы умеете писать macros, использовать их в моделях, понимаете dispatch, знаете must-know macros из dbt_utils и можете спроектировать собственный production-ready helper. В следующем модуле — variables и env_var: как параметризовать проект через var() и env_var(), делать dev vs prod пути.