When Not to Write a Macro

Concepts covered: Macros and packages

A macro is a jump. The reader of a model sees {{ net_revenue('orders') }}, and before knowing which columns it touches they have to find the file under macros/ and render its branches in their head. SQL has no jumps: the expression sits where it is used. Every macro starts in debt, and it pays the debt off only when the SQL it replaces would have cost more to keep correct across the project.

One model, 4 macros

select
    customer_id,
    round(subtotal_cents / 100.0, 2) as subtotal,
    round(tax_cents / 100.0, 2) as tax,
    100.0 * refunds_cents / nullif(subtotal_cents, 0) as refund_pct,
    date_trunc('day', ordered_at) as order_day
from stg_orders

3 of the 4 macros wrap one expression. money() saves 3 characters per call and costs a file lookup per reader; round(x / 100.0, 2) tells the reader the unit conversion and the precision without leaving the line. safe_pct() hides the nullif, which is the one thing a reader checking a division wants to see. day_bucket() is worse than either, because it compiles differently per target, so the SQL a reviewer reads in dev differs from the SQL production runs. customer_dims() is the only one worth keeping: 9 columns repeated across 12 marts that must stay identical.

One question for every macro

The question is whether a change to this expression would have to land in several places at once, and whether those places are models in the same project. Repetition on its own does not answer it. round(x / 100.0, 2) appears in 30 models and will never change, because cents are cents, so copying it costs nothing. A refund-rate definition that finance amended twice last year, used in 6 marts, is the opposite case, and a macro with the rule in a comment is cheaper than 6 inconsistent fixes. Shapes driven by data (the pivot in Querying from Jinja) and generic tests are macros by nature, because no fixed SQL can express them.

Do

• Write a macro when 3 or more models carry the same block and must change together.
• Write a macro when the SQL's shape depends on data or config, as the pivot did.
• Write a generic test as a macro; that is what a generic test is.
• Review a macro change by diffing target/compiled/, where its effect is visible.

Avoid

• Wrap one function call: nullif(b, 0) reads faster than safe_denominator(b).
• Pass 5 or more positional arguments; the call site stops saying which is which.
• Call a macro from a macro from a macro; each hop is another file to open.
• Rewrite a case expression as {% if %} branches; SQL already has control flow.

Costs the project pays past readability

A warehouse error points at a line in target/compiled/, and the reader maps it back to a macro call by hand. Searching the repository for refund_pct finds no model, because the column name was assembled inside a macro. The static parser from the first section gives up on any model with a macro call, so parse time grows with macro use. A change to a shared macro changes the compiled SQL of every model that calls it, so a one-line edit is a review of 40 models. Each cost is small, and a project of 400 models and 90 macros pays all 4 on every change.

When a macro does stay, its call site should read the way SQL reads. Name it for what it produces, refund_rate rather than calc, and use keyword arguments so {{ refund_rate(numerator='refunds_cents', denominator='subtotal_cents') }} says which is which. Put the business rule in a comment inside the macro, above the SQL, with the date it last changed. Keep it to one level: a macro that calls into dbt_utils is fine, and a macro that calls a macro that calls a macro is 3 files for one expression.

About this lesson

This section is part of the Jinja and Macros: Advanced 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.