How to write your first macro in dbt

A macro is a function written in Jinja that returns SQL. When the same expression appears in ten models, such as a division that must not fail on zero, a macro keeps it in one place: fix it once and every model gets the fix on the next run.

Copy and paste

The macro, in any file under macros/:

-- macros/safe_divide.sql
{% macro safe_divide(numerator, denominator, decimals=2) -%}
    round({{ numerator }} / nullif({{ denominator }}, 0), {{ decimals }})
{%- endmacro %}

Use it in a model:

-- models/marts/avg_order_value.sql
select
    customer_id,
    {{ safe_divide('revenue', 'orders') }} as avg_order_value,
    {{ safe_divide('revenue', 'orders', decimals=0) }} as avg_order_value_rounded
from {{ ref('customer_orders') }}

What dbt compiles:

select
    customer_id,
    round(revenue / nullif(orders, 0), 2) as avg_order_value,
    round(revenue / nullif(orders, 0), 0) as avg_order_value_rounded
from "shop"."main"."customer_orders"

How it works

• Arguments are text. 'revenue' is passed as a string and pasted into the SQL, so the macro works with any column or expression: safe_divide('sum(amount)', 'count(*)').
• Defaults. decimals=2 makes the argument optional; pass it by name to change it.
• The output is SQL. dbt replaces the call with whatever the macro prints. Check it with dbt compile.

Clean compiled SQL

Jinja keeps the line breaks around its tags. Without the dashes in {%- and -%}, the compiled model has an empty line before and after every call. The dashes remove the whitespace on that side of the tag. They do not change the result, only how readable the compiled SQL is.

A macro that runs a query

A macro can query the warehouse and return the result to the model, for example the list of values in a column:

-- macros/get_statuses.sql
{% macro get_statuses() %}
    {% set query %}
        select distinct status from {{ ref('stg_orders') }} order by 1
    {% endset %}
    {% set result = run_query(query) %}
    {% if execute %}
        {{ return(result.columns[0].values()) }}
    {% else %}
        {{ return([]) }}
    {% endif %}
{% endmacro %}

execute is false while dbt parses the project, when queries are not run, so the macro returns an empty list in that phase. Looping over a list shows it in use.

A macro on its own: run-operation

Some macros do something instead of returning SQL, such as granting access or cleaning old schemas. dbt run-operation calls them directly:

-- macros/log_hello.sql
{% macro log_hello(name='world') %}
    {% do log('Hello ' ~ name, info=True) %}
{% endmacro %}

dbt run-operation log_hello --args '{name: Nicola}'

Hello Nicola

Things to know

• File names are free. dbt reads every .sql file in macros/, and one file can hold several macros. Name the file after the macro to find it again.
• Package macros have a prefix: dbt_utils.star(...), dbt_utils.get_column_values(...).
• Overriding dbt. A macro with the same name as one of dbt’s, such as generate_schema_name, replaces it; see how to change the target schema.

Tested with dbt Core 1.12.