SQL That Writes SQL: Jinja and Macros
The templating layer under every dbt model. Jinja expressions, control flow, macros you write once and reuse, and the dbt_utils package that has already written the ones you need.
You’ve been using it since the second post. Every {{ ref('stg_orders') }} is a Jinja expression — dbt compiles your models through the Jinja templating engine before handing the SQL to the database. Most of the time it stays out of the way. But when you need to stop repeating yourself, Jinja is the tool that lets your SQL write SQL.
Two kinds of braces
Jinja has two constructs, and telling them apart is most of the battle:
{{ ... }}is an expression — it evaluates to a value that gets dropped into the SQL.{{ ref('orders') }}becomes a table name.{{ this }}becomes the current model’s relation.{% ... %}is a statement — control flow that produces no output on its own.{% if ... %},{% for ... %},{% set ... %}.
You saw the statement form in the incremental post:
{% if is_incremental() %}
where order_date > (select max(order_date) from {{ this }})
{% endif %}The {% if %} decides whether the where clause exists; the {{ this }} inside it fills in a table name. Expressions produce SQL text; statements decide which text.
Macros: functions for SQL
A macro is a reusable snippet of templated SQL — a function you call from your models. You define one with {% macro %} in a file under macros/. Here’s a genuinely useful one: our payment amounts arrive as integer cents, and we’d rather store dollars. Instead of writing the same division in every model, we write it once:
-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, scale=2) %}
round(1.0 * {{ column_name }} / 100, {{ scale }})
{% endmacro %}Then call it in a model like a function:
-- models/staging/stg_payments.sql
select
id as payment_id,
order_id,
payment_method,
{{ cents_to_dollars('amount') }} as amount
from {{ ref('raw_payments') }}When dbt compiles that model, the macro call expands in place:
select
id as payment_id,
order_id,
payment_method,
round(1.0 * amount / 100, 2) as amount
from "bookshop"."main"."raw_payments"Now the cents-to-dollars logic lives in exactly one place. Change how you round, and every model that calls the macro updates at once. That scale=2 default is a real function default — call cents_to_dollars('amount', 0) somewhere and that call rounds to whole dollars.
Looping: a macro that writes many columns
The cents example is small. The pattern earns its keep the moment Jinja loops, generating SQL you’d never want to type by hand. Say you want one order-count column per status — placed, shipped, completed, returned. Written out, that’s four near-identical sum(case when …) expressions, and a fifth status means a fifth hand-edit. A macro with a {% for %} writes them all from a list:
-- macros/count_by_status.sql
{% macro count_by_status(status_column, statuses) %}
{%- for status in statuses %}
sum(case when {{ status_column }} = '{{ status }}' then 1 else 0 end) as {{ status }}_orders
{%- if not loop.last %},{% endif -%}
{%- endfor %}
{% endmacro %}Three Jinja things do the work. {% for status in statuses %} walks the list. loop.last is a flag dbt hands you inside every loop — here it suppresses the trailing comma after the final column, the fiddly bit that trips up hand-rolled generators. And the {%- / -%} dashes trim the surrounding whitespace so the compiled SQL comes out clean instead of ragged.
Call it with a list. {% set %} names one inline:
-- models/marts/orders_by_customer.sql
{% set order_statuses = ['placed', 'shipped', 'completed', 'returned'] %}
select
customer_id,
count(*) as total_orders,
{{ count_by_status('status', order_statuses) }}
from {{ ref('stg_orders') }}
group by customer_idAt compile time the loop unrolls into exactly the SQL you’d otherwise have typed by hand:
select
customer_id,
count(*) as total_orders,
sum(case when status = 'placed' then 1 else 0 end) as placed_orders,
sum(case when status = 'shipped' then 1 else 0 end) as shipped_orders,
sum(case when status = 'completed' then 1 else 0 end) as completed_orders,
sum(case when status = 'returned' then 1 else 0 end) as returned_orders
from "bookshop"."main"."stg_orders"
group by customer_idAdd a fifth status to the list and a fifth column appears — no new SQL, no missed comma. That’s the shape of every useful macro: describe the pattern once, let Jinja stamp out the repetition.
Where the list comes from
Hardcoding order_statuses is the simple version, and it has a catch — a brand-new status in the data won’t get a column until you edit the list by hand. When you want the columns to track the data itself, pull the values from the warehouse at compile time. dbt_utils wraps this in get_column_values:
{% set order_statuses = dbt_utils.get_column_values(ref('stg_orders'), 'status') %}Under the hood that runs a select distinct status against the built model — dbt lets a macro execute SQL during compilation via run_query / the statement block, and get_column_values is a thin wrapper over it — and hands you the list. Now the pivot adapts on its own. The cost is that the query runs while the project compiles, so stg_orders has to exist before this model can read it; that’s exactly why the hardcoded list is the right default until you actually need the dynamism.
Three mechanics behind every macro
Before we go deeper, it’s worth naming three pieces of syntax you’ll reach for constantly — they’re the difference between a macro that compiles clean and one that fights you.
Whitespace control. Jinja preserves the newlines and indentation around your {% %} tags, which is why a naive loop produces SQL pocked with blank lines. The dash operators fix it: {%- strips whitespace to the left of the tag, -%} strips it to the right. In count_by_status, {%- if not loop.last %},{% endif -%} trims both sides of the comma so the columns line up instead of drifting. When compiled SQL comes out ragged, missing-dash tags are almost always the reason — and it matters beyond looks, because a stray blank line inside a select list occasionally lands a comma somewhere the parser dislikes.
{% set %} vs {% do %}. {% set x = ... %} binds a value to a name. {% do ... %} evaluates an expression for its side effect and throws the output away — you use it when calling something that returns a value you don’t want printed into the SQL. Appending to a list is the canonical case:
{% set columns = [] %}
{% for col in adapter.get_columns_in_relation(ref('stg_orders')) %}
{% do columns.append(col.name) %}
{% endfor %}columns.append(...) returns None, but without {% do %} you’d need {% set _ = columns.append(...) %} to swallow it. {% do %} is the clean way to say “run this, ignore the result” — which is also why you’ll see {% do return(...) %} and {% do log(...) %} throughout mature macros.
Default arguments. Macro args can carry defaults, and none is a perfectly good one — it lets a macro branch on whether the caller supplied a value:
{% macro dollars(column_name, scale=2, label=none) %}
round(1.0 * {{ column_name }} / 100, {{ scale }})
{%- if label %} as {{ label }}{% endif %}
{% endmacro %}Call it {{ dollars('amount') }} and you get a bare expression; call it {{ dollars('amount', label='amount_usd') }} and it aliases the column. Keyword arguments at the call site keep long signatures readable — dollars('tax', scale=4) says exactly what the 4 is.
One macro, two warehouses: adapter.dispatch
The target.type if/else works for a quick fork:
{% macro current_ts() %}
{% if target.type == 'snowflake' %}current_timestamp()
{% else %}now(){% endif %}
{% endmacro %}But it doesn’t scale — a third warehouse means editing every such macro, and a package author can’t anticipate your database at all. The real mechanism dbt gives you is adapter.dispatch, which resolves a macro name to a warehouse-specific implementation at compile time. The convention: one thin dispatcher, then one <adapter>__<name> implementation per database.
Say the bookshop needs a portable “days between two dates” — DuckDB spells it date_diff('day', a, b), Snowflake spells it datediff(day, a, b):
-- macros/days_between.sql
{% macro days_between(start_date, end_date) %}
{{ return(adapter.dispatch('days_between', 'bookshop')(start_date, end_date)) }}
{% endmacro %}
{% macro default__days_between(start_date, end_date) %}
datediff(day, {{ start_date }}, {{ end_date }})
{% endmacro %}
{% macro duckdb__days_between(start_date, end_date) %}
date_diff('day', {{ start_date }}, {{ end_date }})
{% endmacro %}adapter.dispatch('days_between', 'bookshop') looks for a macro named <adapter>__days_between — duckdb__days_between when we’re on DuckDB in dev, snowflake__days_between when we’re on Snowflake in prod — and falls back to default__days_between when no adapter-specific version exists. The second argument, 'bookshop', is the dispatch namespace: the project (or packages) to search. Models just call {{ days_between('order_date', 'shipped_date') }} and never see the fork.
This is exactly how dbt_utils is built — every one of its macros is a dispatcher over default__ / postgres__ / snowflake__ implementations. That’s also why it runs unmodified on DuckDB: the default__ version is standard-enough SQL that DuckDB accepts it. And because dispatch is configurable, you can point a package’s search at your macros to override one of its implementations — a dispatch: block in dbt_project.yml — but that’s a packages concern, and the next chapter is where it lives.
Overriding dbt’s own macros: schema names
Some of the most useful macros aren’t ones you call — they’re ones dbt calls, and you override. The canonical example is generate_schema_name, which dbt invokes to decide what schema each model lands in. The built-in default does something that surprises everyone once: when a model sets a custom schema via +schema: or config(schema=...), dbt concatenates it onto the target schema. A mart configured with schema: reporting under a target.schema of analytics builds into analytics_reporting, not reporting.
That default is deliberately conservative — it keeps two developers from clobbering each other’s reporting schema. But most teams want the opposite in production: bare, predictable schema names in prod, prefixed sandboxes in dev. You get that by dropping a generate_schema_name macro into macros/, where it shadows dbt’s built-in:
-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- set default_schema = target.schema -%}
{%- if custom_schema_name is none -%}
{{ default_schema }}
{%- elif target.name == 'prod' -%}
{{ custom_schema_name | trim }}
{%- else -%}
{{ default_schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}Read the branches: a model with no custom schema builds into the target’s default schema, as always. On the prod target (target.name == 'prod'), a custom schema is used bare — reporting means reporting. Anywhere else, the custom name is prefixed with the developer’s default schema — analytics_reporting in dev, dbt_sathya_reporting in a personal sandbox — so nobody’s dev run overwrites a teammate’s. That target.name check is the whole trick, and it’s why this macro is inseparable from how you configure environments; the environments chapter tells the full dev/prod story, but the macro itself belongs here, because it’s the cleanest illustration of overriding a dbt macro by name.
Two siblings work identically. generate_database_name(custom_database_name, node) decides the database/catalog — override it to route prod into a PROD database and dev into DEV. generate_alias_name(custom_alias_name, node) decides the table name itself; the default uses the model’s filename, and teams override it to, say, strip a stg_ prefix or version a table. All three follow the same rule: define a macro with the reserved name in your project, and dbt calls yours instead of its own.
The dbt Jinja context
The macros above lean on names you didn’t define — target, adapter, return. These come from the dbt Jinja context: the variables and functions dbt injects into every template. Here’s the working set, each with a one-liner.
var('name', default)— read a project variable, with an optional fallback:{% set start = var('start_date', '2024-01-01') %}. Override at the CLI withdbt run --vars '{start_date: 2025-06-01}'.env_var('DBT_KEY', 'fallback')— read an environment variable (the standard way to keep secrets out of code):{{ env_var('DBT_SCHEMA', 'analytics') }}.target— the active connection profile.target.name(dev/prod),target.type(duckdb),target.schema,target.database, andtarget.threadsare the ones you’ll reach for.this— the current model’s relation, for self-reference in incrementals and hooks:select max(order_date) from {{ this }}. Its parts arethis.database,this.schema,this.identifier.builtins— the original context functions, so an override can still reach the real one:{% set rel = builtins.ref(model_name) %}inside a customref.modules— a slice of Python’s stdlib.{{ modules.datetime.date.today() }}stamps a compile-time date;modules.pytzandmodules.reare there too.run_query(sql)— execute SQL at compile time and get an Agate table back:{% set results = run_query('select distinct status from ' ~ ref('stg_orders')) %}.{% call statement('name') %}…{% endcall %}/load_result('name')— the lower-level formrun_querywraps; run a named statement, then fetch its result withload_result('name').log(msg, info=false)— write to the dbt log;info=truealso prints to the console:{{ log('building ' ~ this, info=true) }}.exceptions.raise_compiler_error(msg)— fail the compile loudly when an argument is invalid:{% if scale < 0 %}{{ exceptions.raise_compiler_error('scale must be >= 0') }}{% endif %}.exceptions.warn(msg)is the softer cousin.adapter.dispatch(name, namespace)— resolve a macro to its warehouse-specific version, as above.adapteralso exposes helpers likeadapter.get_columns_in_relation(this).return(value)— return a real Python value (list, dict) from a macro rather than a string:{% do return(statuses) %}.{% do expr %}— evaluate an expression for its side effect, discarding output (see the.appendexample above).
Compile time, run time, and the execute flag
dbt reads your project in two passes. The first pass parses every file to build the graph — it needs to see every ref() and config() to wire the DAG, but it does not run your models against the warehouse. The second pass executes: now the SQL actually runs. Most macros never notice the difference. The ones that run a query at compile time do, and it’s the source of a classic confusing bug.
Anything built on run_query — including the get_column_values from the last section — returns nothing during the parse pass, because there’s no warehouse connection yet. Write a macro that calls run_query and then loops over the result, and the parse pass hands it an empty result: the loop produces nothing, sometimes silently. The guard is the execute flag, which is false while parsing and true during the real run:
{% macro column_names(relation) %}
{% if execute %}
{% set results = run_query('select * from ' ~ relation ~ ' limit 0') %}
{% do return(results.columns | map(attribute='name') | list) %}
{% else %}
{% do return([]) %}
{% endif %}
{% endmacro %}The rule: any macro that queries the warehouse should wrap that work in {% if execute %} and return a harmless default otherwise. It keeps dbt parse and dbt compile from tripping over a query that can’t run yet.
Reading what a macro actually produced
A macro is code that writes code, which makes “why did it emit that?” the everyday question. Two tools answer it, and neither requires a debugger.
The first is dbt compile. It runs the parse-and-render pass without touching your data and writes the finished SQL — every macro expanded, every ref resolved — under target/compiled/. So after editing count_by_status, dbt compile --select orders_by_customer lets you open target/compiled/bookshop/models/marts/orders_by_customer.sql and read the exact SQL dbt would send. Nine debugging sessions in ten end here: the compiled file shows the doubled comma or the missing group by immediately.
The second is log(), for seeing values while the macro runs — the ones that never appear in the output, like a list you built or a branch you took:
{% macro count_by_status(status_column, statuses) %}
{{ log("count_by_status over " ~ statuses | length ~ " statuses: " ~ statuses, info=true) }}
{%- for status in statuses %}
...With info=true the message prints to your console on every run; without it, it goes only to logs/dbt.log. It’s the print-debugging of dbt, and it’s the fastest way to confirm that get_column_values actually returned the four statuses you expected and not an empty list from the parse pass. For anything hairier, dbt --debug run echoes every compiled statement as it’s issued.
Macros that do things: hooks and operations
Every macro so far returned SQL text that landed inside a model. Some macros instead run for their side effects — granting permissions, refreshing a warehouse, vacuuming a table — and dbt gives them two homes.
Hooks attach SQL to the build lifecycle. A post-hook runs right after a model builds; a pre-hook runs just before. Set them in config(), or more often by folder in dbt_project.yml:
models:
bookshop:
marts:
+post-hook: "grant select on {{ this }} to role reporter"Now every mart, the moment it’s (re)built, grants read access to the reporting role — no separate step to forget, no drift between “the table exists” and “the right people can read it.” A hook is templated like everything else, so it can call a macro: keep the grant logic in one place and reference it from the hook.
-- macros/grant_select.sql
{% macro grant_select(relation, role) %}
grant select on {{ relation }} to role {{ role }}
{% endmacro %}models:
bookshop:
marts:
+post-hook: "{{ grant_select(this, 'reporter') }}"Same SQL as before, but now the grant lives in one file. Change how the bookshop grants — add a second role, switch to a role hierarchy, guard it behind target.name == 'prod' — and you change it once, not once per folder.
The project-level cousins run once per invocation rather than per model: on-run-start fires before anything builds, on-run-end after everything does — the place for a run-audit row or a one-time setup statement.
# dbt_project.yml
on-run-end:
- "insert into audit.dbt_runs (run_id, finished_at) values ('{{ invocation_id }}', current_timestamp)"dbt run-operation is the other door: it invokes a macro directly from the command line, with no model involved. The macro it calls needs to actually execute SQL rather than return it — that’s what run_query is for, and the execute guard keeps it quiet during parsing:
-- macros/grant_reporter_access.sql
{% macro grant_reporter_access(role='reporter') %}
{% if execute %}
{% for node in graph.nodes.values()
if node.resource_type == 'model' and 'marts' in node.fqn %}
{% do run_query("grant select on " ~ node.relation_name
~ " to role " ~ role) %}
{% do log("granted select on " ~ node.relation_name, info=true) %}
{% endfor %}
{% endif %}
{% endmacro %}$ dbt run-operation grant_reporter_access --args '{role: reporter}'That’s how one-off or scheduled maintenance — a grants sweep, a warehouse resize, a cleanup — lives in the same version-controlled project as your models instead of in a drawer of loose SQL scripts. Both patterns lean on the same idea: a macro is just templated SQL, and sometimes the SQL you want to template is an operation, not a query.
Don’t write what’s already written
Before you write a macro, check whether dbt_utils already has it. It’s the near-universal utility package — dbt’s standard library in practice. Need a hash key across several columns for a surrogate primary key? generate_surrogate_key does it portably:
select
{{ dbt_utils.generate_surrogate_key(['order_id', 'payment_method']) }} as payment_key,
order_id
from {{ ref('stg_payments') }}which compiles to a warehouse-appropriate hash:
select
md5(cast(coalesce(cast(order_id as TEXT), '_dbt_utils_surrogate_key_null_')
|| '-' || coalesce(cast(payment_method as TEXT), '_dbt_utils_surrogate_key_null_') as TEXT)) as payment_key,
order_id
from "bookshop"."main"."stg_payments"That is a lot of fiddly, null-safe, cross-database SQL you didn’t have to get right — and, as the adapter.dispatch section showed, it’s dispatched under the hood so it renders correctly on whatever warehouse you’re on. dbt_utils also ships generic tests (accepted_range, equal_rowcount) and helpers (star, pivot, date_spine). Installing it — and how packages drop their macros and tests into your project — is the whole subject of the next chapter.
Final thoughts
Jinja is what makes dbt more than a query runner. {{ }} drops values into SQL, {% %} decides which SQL exists, and macros let you name a pattern and reuse it — turning copy-paste into a function call. Used lightly it removes real duplication; used heavily it becomes a maze, so keep your models mostly readable SQL with Jinja doing the parts that genuinely repeat. And when you reach for a macro, look in dbt_utils first — the odds are good someone already wrote and tested it.
Comments