dbt Macros & Jinja Explained (With Real Examples)

If you've worked in a dbt project for more than a few weeks, you've probably hit this moment: you need the same small piece of SQL logic — a unit conversion, a status mapping, a date bucket — in five or six different models. You copy it. You paste it. You move on.

Then someone finds a bug in that logic, and now you're grepping the whole repo for every place you pasted it, hoping you don't miss one.

This is exactly the problem dbt macros exist to solve. This post walks through what a macro actually is, how Jinja fits in, a working example you can copy into your own project, and the one gotcha that trips up almost every junior dbt dev the first time a macro breaks.

What a macro actually is

A macro is a reusable piece of SQL, written using Jinja — a templating language originally built for Python web frameworks, which dbt uses as its templating layer on top of plain SQL.

The important mental model: a macro is not a database function. Snowflake (or whatever warehouse you're on) never sees your macro definition, and it never evaluates any Jinja. Instead, dbt reads your .sql files, finds the Jinja templating in them (macro calls, ref(), source(), control-flow tags, etc.), and renders that Jinja into plain SQL before anything is sent to the warehouse. By the time your query reaches Snowflake, every {{ }} and {% %} tag is gone — it's just a SELECT statement.

That's the whole trick: dbt macros let you template SQL, not extend SQL.

Defining a macro

Macros live in your project's macros/ directory, in .sql files. A macro definition looks like this:

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ({{ column_name }} / 100)::float
{% endmacro %}

That's a macro called cents_to_dollars that takes one argument, column_name, and returns the SQL expression to convert a cents value to a float dollar amount. Nothing runs yet — this is just a definition, the same way defining a Python function doesn't execute its body.

Calling a macro

You call a macro from any model using the same double-curly-brace syntax you already use for ref():

-- models/marts/fct_orders.sql
select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_dollars
from {{ ref('stg_orders') }}

When dbt compiles this model, it swaps {{ cents_to_dollars('amount_cents') }} for the macro's rendered body. The compiled SQL that actually hits Snowflake looks like:

select
    order_id,
    (amount_cents / 100)::float as amount_dollars
from analytics.staging.stg_orders

Now imagine you need that same conversion in fct_refunds, fct_payouts, and rpt_revenue. Instead of pasting the three-line cast into each one, you call {{ cents_to_dollars('some_column') }} in each model. If the rounding logic ever needs to change — say, finance wants you to round instead of truncate — you change it in one file, and every model that calls the macro picks up the fix the next time it runs.

Jinja gives you real control flow

Because macros are Jinja, you're not limited to simple text substitution. You get {% if %} conditionals, {% for %} loops, and {% set %} for variables — none of which exist in plain SQL.

A common use case is generating repetitive CASE WHEN columns from a list:

-- macros/pivot_payment_methods.sql
{% macro payment_method_columns() %}
    {% for method in ['bank_transfer', 'credit_card', 'gift_card'] %}
    sum(case when payment_method = '{{ method }}' then amount end) as {{ method }}_amount{{ "," if not loop.last }}
    {% endfor %}
{% endmacro %}

Called inside a select, this loop generates one sum(case when...) column per payment method, so you're not hand-writing (and hand-maintaining) three nearly-identical lines every time a new payment method shows up.

You also don't need to write every macro yourself. The dbt-utils package — installed via packages.yml and dbt deps — ships a library of prebuilt macros for common jobs: dbt_utils.date_trunc(), dbt_utils.pivot(), dbt_utils.generate_surrogate_key(), and more. Before writing a macro from scratch, it's worth checking whether dbt-utils already has it.

The gotcha: macros fail at compile time, not run time

Here's the thing that catches almost everyone the first time: macros are resolved before any SQL reaches the warehouse. If there's a typo in a macro call, a missing argument, or a Jinja syntax error, you will not get a Snowflake error. You'll get a Jinja compilation error, and those read very differently — they're about rendering, not about SQL semantics, which can be confusing if you're expecting a database error message.

The fix is a command most juniors don't reach for soon enough: dbt compile.

dbt compile --select fct_orders

This renders all the Jinja in your project and writes the fully-resolved SQL to target/compiled/<project_name>/models/.../fct_orders.sql, without running anything against the warehouse. When a macro-heavy model breaks, open that compiled file first. You'll see exactly what SQL dbt generated — which immediately tells you whether the problem is in your macro logic (fix the Jinja) or in the resulting SQL itself (fix the query, and now you can debug it like normal SQL).

Making dbt compile a reflex before dbt run whenever a macro is involved will save you a lot of confused debugging sessions.

Quick recap

• A macro is reusable SQL written with Jinja templating, defined once in your macros/ folder
• You call it from any model with {{ macro_name(args) }} — the same pattern as ref()
• dbt renders all Jinja into plain SQL at compile time, before anything touches the warehouse
• Jinja gives you {% if %}, {% for %}, and {% set %} — real logic that plain SQL doesn't have
• dbt_utils ships prebuilt macros for common tasks — check there before writing your own
• When something breaks, run dbt compile first to see the generated SQL before you start debugging

Once this clicks, you'll start noticing copy-pasted SQL everywhere in your project — and that's usually your cue that it belongs in a macro.

---

Part of an ongoing series on practical SQL, Python, and Snowflake skills for junior data engineers. Follow along on Instagram @techqueen.codes for daily breakdowns like this one.

Comments

[Harun - solo dev]

neha, this is a crystal-clear breakdown of dbt macros and the compile-time vs run-time gotcha. the mental model that "dbt renders jinja into plain sql before anything touches the warehouse" is exactly what junior devs miss.

as someone obsessed with keeping my own codebase at exactly 142kb, the DRY (don't repeat yourself) principle isn't just a best practice for me; it's a physical constraint. copy-pasting logic is the exact same trap in sql as it is in javascript—it bloats the codebase and creates silent bugs when one instance gets updated and the others don't.

your tip to make dbt compile a reflex before dbt run is gold. inspecting the target/compiled/ output is the only way to truly debug templating logic without guessing.

quick question: when dealing with complex, nested macros in larger projects, do you find it better to keep them in a single macros/ file organized by domain, or split them into individual files per macro to make version control diffs cleaner?

fantastic, highly practical guide for data engineers! 📊

[Neha Christina]

Thank you @koda2026, this comment made my day 🙏 and yes — that's the exact unlock for debugging dbt once it clicks.
Good question! For bigger projects I'd go one file per macro, grouped into subfolders by domain (e.g. macros/finance/cents_to_dollars.sql). dbt honestly doesn't care how you organize macros/ — it just parses every .sql file in there. But small single-purpose files = way cleaner git diffs, and way easier to find the macro you're looking for once you've got more than a handful.
Only exception: a few tiny helpers that are always used together can share a file. Otherwise, split 'em up 👍

[Harun - solo dev]

spot on advice, neha! clean git diffs are an absolute lifesaver when debugging complex projects.

thanks for taking the time to answer and for writing such a clear, practical guide. it’s a massive help to the community! 🐯📊