dbt Macros

Macros are reusable Jinja SQL functions that you define once and call from anywhere in your project. They reduce repetition, enforce consistency, and make complex logic easy to test and maintain. Macros live in the macros/ folder and use the {% macro %}...{% endmacro %} syntax.

Why Macros Exist

Without macros, repeated logic appears copied across many models:

-- In stg_orders.sql
(amount_cents / 100.0)::numeric(10,2) as amount_dollars

-- In stg_refunds.sql
(refund_cents / 100.0)::numeric(10,2) as refund_dollars

-- In stg_adjustments.sql
(adjustment_cents / 100.0)::numeric(10,2) as adjustment_dollars

If the conversion logic changes, you update every file. With a macro, you update one file.

Writing Your First Macro

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, precision=2) %}
    ({{ column_name }} / 100.0)::numeric(10, {{ precision }})
{% endmacro %}

The macro takes the column name as a required argument and precision as an optional argument with a default of 2.

Calling a Macro

-- models/staging/stg_orders.sql
select
    order_id,
    customer_id,
    order_date,
    {{ cents_to_dollars('amount_cents') }}          as amount_dollars,
    {{ cents_to_dollars('shipping_cents') }}        as shipping_dollars,
    {{ cents_to_dollars('tax_cents', precision=4) }} as tax_dollars
from {{ source('ecommerce', 'orders') }}

After compilation:

select
    order_id,
    customer_id,
    order_date,
    (amount_cents / 100.0)::numeric(10, 2)   as amount_dollars,
    (shipping_cents / 100.0)::numeric(10, 2) as shipping_dollars,
    (tax_cents / 100.0)::numeric(10, 4)      as tax_dollars
from raw_data.shopify.orders

Macro with Multiple Arguments

-- macros/date_spine.sql
{% macro safe_divide(numerator, denominator) %}
    case
        when {{ denominator }} = 0 then null
        else {{ numerator }} / {{ denominator }}::float
    end
{% endmacro %}

-- Usage in a model
select
    order_id,
    {{ safe_divide('revenue', 'quantity') }} as revenue_per_unit
from {{ ref('fct_orders') }}

Generate SQL Dynamically with Loops

Macros shine when you need to generate repetitive SQL from a list:

-- macros/union_relations.sql
{% macro unpivot_metrics(column_list) %}
{% for col in column_list %}
    select '{{ col }}' as metric_name, {{ col }} as metric_value
    from {{ ref('fct_orders') }}
    {% if not loop.last %}union all{% endif %}
{% endfor %}
{% endmacro %}

-- Usage
{{ unpivot_metrics(['revenue', 'orders', 'refunds']) }}

Compiles to:

select 'revenue' as metric_name, revenue as metric_value
from dbt_dev.fct_orders
union all
select 'orders' as metric_name, orders as metric_value
from dbt_dev.fct_orders
union all
select 'refunds' as metric_name, refunds as metric_value
from dbt_dev.fct_orders

Adapter-Specific Macros

Different warehouses use different SQL syntax. Macros handle these differences so models work across adapters:

-- macros/current_timestamp.sql
{% macro current_timestamp() %}
    {% if target.type == 'snowflake' %}
        convert_timezone('UTC', current_timestamp())
    {% elif target.type == 'bigquery' %}
        current_timestamp()
    {% elif target.type == 'postgres' %}
        now() at time zone 'UTC'
    {% else %}
        current_timestamp
    {% endif %}
{% endmacro %}

-- Usage in any model (works across all warehouses)
select
    order_id,
    {{ current_timestamp() }} as loaded_at
from {{ ref('stg_orders') }}

Calling a Macro from a run-operation

Run a macro directly from the command line without running models:

dbt run-operation cents_to_dollars --args '{"column_name": "amount_cents"}'

Use this for utility macros like creating warehouse schemas, granting permissions, or running maintenance tasks.

Organizing Macros

macros/
  finance/
    cents_to_dollars.sql
    safe_divide.sql
    calculate_tax.sql
  utils/
    current_timestamp.sql
    safe_cast.sql
    null_coalesce.sql
  data_quality/
    not_negative.sql
    between_inclusive.sql

When to Write a Macro

• The same SQL pattern appears in three or more models
• Logic differs by warehouse adapter but should behave identically
• You want to generate SQL from a list rather than write it repeatedly
• A utility operation needs to run outside of the normal model pipeline