8. Jinja and macros

Every dbt model is a Jinja template that happens to render SQL. Chapter 7 showed when the rendering happens; this chapter is about what you can do in it — and the discipline to do less.

The syntax

Three delimiters:

   {{ ... }}   expression — renders a value
   {% ... %}   statement — control flow, no output
   {# ... #}   comment — never rendered

{% set payment_methods = ['card', 'bank_transfer', 'paypal'] %}

select
    order_id,
    {% for method in payment_methods %}
    sum(case when payment_method = '{{ method }}'
             then amount else 0 end) as {{ method }}_amount
    {%- if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_payments') }}
group by 1

Renders to:

select
    order_id,
    sum(case when payment_method = 'card' then amount else 0 end) as card_amount,
    sum(case when payment_method = 'bank_transfer' then amount else 0 end) as bank_transfer_amount,
    sum(case when payment_method = 'paypal' then amount else 0 end) as paypal_amount
from analytics.analytics.stg_payments
group by 1

loop.last and the trailing comma is the pattern you will write most often — Jinja’s loop object also gives loop.first, loop.index (1-based) and loop.index0.

Whitespace control

The hyphen strips whitespace on that side:

   {%- if ... %}    strip before
   {% if ... -%}    strip after
   {%- if ... -%}   both

Without it, control-flow lines leave blank lines everywhere. It does not change behaviour, only the readability of compiled SQL — which matters more than it sounds when you are debugging a 500-line rendered model.

The dbt context

Jinja on its own knows nothing about dbt. dbt injects a context of functions and variables:


`ref('model')`	resolve another model
`source('src', 'tbl')`	resolve a source
`this`	the current model’s relation
`config(...)`	set config for this model
`var('name', default)`	project variable
`env_var('NAME', default)`	environment variable
`target`	the active target — `target.name`, `target.schema`, `target.database`
`is_incremental()`	incremental state
`run_query(sql)`	run SQL now, at compile time
`log(msg, info=True)`	print during the run
`adapter`	adapter methods
`modules`	`datetime`, `re`, `itertools`, `pytz`
`builtins`	the original versions of overridden functions
`graph`	the whole manifest, at parse time

target is how environment-aware logic is written:

select * from {{ ref('stg_events') }}
{% if target.name == 'dev' %}
where event_date >= dateadd(day, -7, current_date)
{% endif %}

Development runs a week of data; production runs everything. This one pattern saves more warehouse spend than most optimisation work, because it makes iterating on a large model cheap. Chapter 13 returns to it.

Variables

Declared in dbt_project.yml:

vars:
  start_date: '2020-01-01'
  payment_methods: ['card', 'bank_transfer']

Read with {{ var('start_date') }}, overridden at the command line:

dbt run --vars '{"start_date": "2024-01-01"}'

Always give var() a default — {{ var('lookback_days', 3) }} — unless a missing value genuinely should stop the run. Without one, dbt fails at parse time, and it will be someone else’s CI that discovers it.

Macros

A macro is a reusable function in macros/.

-- macros/cents_to_dollars.sql

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

select
    order_id,
    {{ cents_to_dollars('amount_cents') }} as amount_usd
from {{ ref('stg_orders') }}

A macro returns text. It does not run anything; it produces a string that becomes part of your SQL. Holding that thought prevents most macro confusion.

Macros can call other macros, take defaults, and use the whole dbt context. Package macros are namespaced: {{ dbt_utils.star(from=ref('orders')) }}.

What macros are actually good for

   ✓ WORTH A MACRO
     a formula repeated in many models
       cents_to_dollars, fiscal_quarter
     boilerplate with a shape
       grant statements, audit columns
     cross-database compatibility
       date_trunc across warehouses
     generating repetitive SQL
       a pivot over N known values
     operational tasks
       run-operation targets

   ✗ NOT WORTH A MACRO
     something used once
     abstracting a join "for reuse later"
     business logic that belongs in a model
     anything that makes the compiled SQL
       impossible to follow

The test is whether a new team member can read the compiled output and understand it. A model built from six nested custom macros is technically DRY and practically unmaintainable. Chapter 14 has this as a named failure mode.

Running queries at compile time

run_query executes SQL during compilation and returns an Agate table you can iterate over.

{% set payment_methods_query %}
    select distinct payment_method
    from {{ ref('stg_payments') }}
    order by 1
{% endset %}

{% set results = run_query(payment_methods_query) %}

{% if execute %}
    {% set methods = results.columns[0].values() %}
{% else %}
    {% set methods = [] %}
{% endif %}

select
    order_id,
    {% for method in methods %}
    sum(case when payment_method = '{{ method }}' then amount end)
        as {{ method }}_amount{% if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_payments') }}
group by 1

The {% if execute %} guard is mandatory. Recall chapter 7: dbt renders twice. During the parse pass execute is false and run_query returns nothing — so unguarded code that iterates the result crashes at parse time. The else branch gives parsing something valid to chew on.

Use this sparingly. A model whose columns depend on data has a schema that changes without a code change, which breaks contracts, breaks downstream select lists, and makes CI results depend on production data. For a genuinely dynamic pivot it is the only option; otherwise a hard-coded list in vars is better precisely because it is visible.

Cross-adapter macros with dispatch

Warehouses disagree about function names. A macro that must work on several uses adapter.dispatch:

{% macro date_trunc_month(column_name) %}
    {{ return(adapter.dispatch('date_trunc_month')(column_name)) }}
{% endmacro %}

{% macro default__date_trunc_month(column_name) %}
    date_trunc('month', {{ column_name }})
{% endmacro %}

{% macro bigquery__date_trunc_month(column_name) %}
    date_trunc({{ column_name }}, month)
{% endmacro %}

dbt looks for <adapter>__<name> and falls back to default__<name>. This is how dbt_utils works on every warehouse, and it is the mechanism to know about if you write a package or support more than one adapter.

Inside one project on one warehouse, you do not need it.

Hooks and operations

Hooks are SQL that runs around a model:

models:
  jaffle_shop:
    marts:
      +post-hook: "grant select on {{ this }} to role reporter"

pre-hook before the model, post-hook after. There are also project-level on-run-start and on-run-end.

Operations are standalone macros run by hand:

{% macro grant_all_to_reporter() %}
  {% set sql %}
    grant usage on schema {{ target.schema }} to role reporter;
    grant select on all tables in schema {{ target.schema }} to role reporter;
  {% endset %}
  {% do run_query(sql) %}
  {% do log("Grants applied to " ~ target.schema, info=True) %}
{% endmacro %}

dbt run-operation grant_all_to_reporter

Useful for warehouse maintenance that belongs with the project — grants, cloning schemas, vacuuming, one-off backfills. It keeps the SQL in git rather than in someone’s terminal history.

Debugging Jinja

The tools, in the order to reach for them:

1. Read the compiled SQL. target/compiled/<project>/models/.... Almost every Jinja problem is obvious there.

2. dbt compile --select my_model — compile without executing, then read.

3. log() to print during a run:

{{ log("methods = " ~ methods, info=True) }}

Without info=True it only goes to logs/dbt.log.

4. Check execute if something works in dbt run but fails on dbt parse — that asymmetry is nearly always a missing guard.

5. Delete partial_parse.msgpack if a macro edit does not seem to take.

Knowing when to stop

The honest closing note. Jinja is a templating language, not a programming language, and dbt projects that treat it as one become unreadable.

   THE PROGRESSION

   {{ ref() }}                   necessary
   a small formula macro         good
   a loop over a config list     fine
   dynamic columns via run_query rare, justified
   macros calling macros calling
     macros to build a model     stop
   business logic in Jinja       you have
                                 written a
                                 program in
                                 the wrong
                                 language

The signal is simple: when the SQL in the file no longer resembles the SQL that runs, you have gone too far. The value of dbt is that a model is a SELECT a data-literate person can read in a pull request. Heavy Jinja trades that away for abstraction nobody asked for.

What to take from this chapter

Three delimiters: {{ }} renders, {% %} controls, {# #} comments. Use - for whitespace control so compiled SQL stays readable.
The dbt context gives you ref, source, this, config, var, env_var, target, is_incremental, run_query, log, adapter, modules and graph.
target.name conditionals are the highest-value pattern here — limit dev runs to a slice of data and iteration gets cheap.
Always give var() a default unless a missing value should stop the run.
A macro returns text. Worth writing for repeated formulas, boilerplate and cross-database compatibility; not for one-off abstraction.
run_query runs at compile time and needs an {% if execute %} guard, because the parse pass returns nothing. Use it rarely — dynamic columns mean a schema that changes without a code change.
adapter.dispatch is how one macro supports many warehouses, via <adapter>__ and default__ implementations. You need it in packages, not in a single-warehouse project.
Hooks run SQL around models; run-operation runs a macro standalone — good for grants and maintenance, kept in git.
Debug by reading target/compiled/. It answers nearly every Jinja question.
When the file stops looking like the SQL that runs, you have gone too far.

Next: tests and contracts — generic tests, singular tests, unit tests, severity, and what “testing data” actually means.

Written by Sepuri Sai Krishna