Writing a Macro

Concepts covered: Macros and packages

Six models in the project turn a cents column into dollars, and each one spells out the arithmetic for itself. Two of the 6 round to 4 places where the rest round to 2, so a report that joins them shows the same order with 2 different amounts. The fix is to write the expression once, in a place every model can reach, and call it by name.

select
    order_id,
    customer_id,
    round(amount_cents / 100.0, 2) as amount,
    round(tax_cents / 100.0, 2) as tax,
    round(shipping_cents / 100.0, 2) as shipping
from {{ source('raw', 'orders') }}

A macro is a named block of Jinja that dbt expands wherever the name is called. It lives in a .sql file under macros/, and dbt reads every file in that folder at parse time, before any model renders, so a model deep in models/staging/ calls a macro defined anywhere in the project without importing it.

{%- macro cents_to_dollars(column_name, places=2) -%}
    round({{ column_name }} / 100.0, {{ places }})
{%- endmacro %}

The {% macro %} tag names the macro and lists its arguments, with a default after = where one applies. The body between the tags renders with the arguments filled in, and the rendered text is pasted into the model at the point of the call. column_name arrives as the string 'amount_cents', so the body prints round(amount_cents / 100.0, 2), and nothing about the macro survives into the SQL the warehouse receives.

select
    order_id,
    customer_id,
    {{ cents_to_dollars('amount_cents') }} as amount,
    {{ cents_to_dollars('tax_cents') }} as tax,
    {{ cents_to_dollars('shipping_cents') }} as shipping
from {{ source('raw', 'orders') }}

select
    order_id,
    customer_id,
    round(amount_cents / 100.0, 2) as amount,
    round(tax_cents / 100.0, 2) as tax,
    round(shipping_cents / 100.0, 2) as shipping
from raw.orders

Arguments are passed by position or by name, and every call that leaves places out gets the default of 2, so the 3 columns now round alike, as does every other model that calls the macro. The dashes on the opening and closing tags strip the newline and indentation around the body, which is why each compiled expression sits flush with the other columns. Without the dashes the compiled SQL carries a line break and 4 spaces before each round, which the warehouse accepts and the next reader of the compiled file has to look past.

An override belongs where a column's precision really differs. A supplier feed prices small parts in fractions of a cent: a washer costs 0.4 cents. At the default, round(0.4 / 100.0, 2) is 0.00, and a cost model that multiplies by 50,000 washers reports 0 dollars where the true cost is 200. Four places keep 0.0040, which survives the multiplication.

select
    part_id,
    {{ cents_to_dollars('unit_cost_cents', places=4) }} as unit_cost
from {{ source('raw', 'supplier_costs') }}

The override is written at the call, in the one model whose column needs it, so a reader of stg_supplier_costs sees why its costs carry 4 places. A later change to the macro's default reaches every call that left places out, while this call keeps its 4, which is what the animation below plays out on a small orders table.

select
    order_id,
    customer_id,
    {{ cents_to_dollars('amount_cents') }} as amount,
    {{ cents_to_dollars('tax_cents', places=2) }} as tax
from {{ source('raw', 'orders') }}

A Macro Argument Is Text

The macro receives the characters amount_cents and nothing about the column behind them, so it renders the same text whether that column is an integer, a string or missing, and a misspelled column name passes through it untouched into the compiled SQL.

Macros call each other the same way models call them. A net amount that subtracts refunds from gross can lean on the macro already written, and the inner call renders first, so the outer body receives finished text.

{%- macro net_dollars(gross_column, refund_column) -%}
    {{ cents_to_dollars(gross_column) }} - {{ cents_to_dollars(refund_column) }}
{%- endmacro %}

select
    order_id,
    round(amount_cents / 100.0, 2) - round(refund_cents / 100.0, 2) as net
from raw.orders

About this lesson

This section is part of the Jinja and Macros: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.