Jinja and Macros

Every dbt model is a Jinja template that compiles to SQL. You have been using it since lesson 1 — ref, source, config and is_incremental are all Jinja. This lesson is about writing your own.

Two delimiters

{{ ... }}    -- expression: output the result into the SQL
{% ... %}    -- statement: control flow, no output
{# ... #}    -- comment: disappears entirely

-- models/marts/order_flags.sql
{% set thresholds = [10, 25, 50] %}

select
    order_id,
    amount,
    {% for t in thresholds %}
    amount >= {{ t }} as over_{{ t }}{% if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_orders') }}

dbt compile --select order_flags

14:02:31  Running with dbt=1.9.1
14:02:31  Found 7 models, 2 seeds, 1 source, 431 macros
14:02:31  Concurrency: 4 threads (target='dev')
14:02:31
14:02:31  Compiled node 'order_flags' is at target/compiled/bookshop/models/marts/order_flags.sql

cat target/compiled/bookshop/models/marts/order_flags.sql

select
    order_id,
    amount,

    amount >= 10 as over_10,

    amount >= 25 as over_25,

    amount >= 50 as over_50

from "bookshop"."main"."stg_orders"

Three columns from one loop. loop.last suppressed the trailing comma — the single most common Jinja bug in dbt projects, and the reason to compile before you run.

Whitespace

The blank lines above come from the newlines around {% %} blocks. Add a hyphen to strip them:

    {%- for t in thresholds %}
    amount >= {{ t }} as over_{{ t }}{{ "," if not loop.last }}
    {%- endfor %}

select
    order_id,
    amount,
    amount >= 10 as over_10,
    amount >= 25 as over_25,
    amount >= 50 as over_50
from "bookshop"."main"."stg_orders"

Cosmetic for the warehouse, which does not care, and worth doing anyway — compiled SQL is what you paste into a query editor when a model is wrong, and unreadable output slows that down.

Writing a macro

Anything in macros/ is available everywhere in the project.

-- macros/cents_to_pounds.sql
{% macro cents_to_pounds(column_name, decimals=2) %}
    round( ({{ column_name }} / 100.0)::numeric, {{ decimals }})
{% endmacro %}

select
    payment_id,
    {{ cents_to_pounds('amount_cents') }} as amount_gbp,
    {{ cents_to_pounds('fee_cents', 4) }} as fee_gbp
from {{ ref('stg_payments') }}

select
    payment_id,
    round( (amount_cents / 100.0)::numeric, 2) as amount_gbp,
    round( (fee_cents / 100.0)::numeric, 4) as fee_gbp
from "bookshop"."main"."stg_payments"

The value is not saving keystrokes — it is that the rounding rule is defined once. When finance says fees round to six places, one file changes.

Generating columns from data

Pivoting a status column by hand means editing the model every time a new status appears. dbt_utils.get_column_values reads the actual values at compile time:

-- models/marts/orders_by_status.sql
{% set statuses = dbt_utils.get_column_values(ref('stg_orders'), 'status') %}

select
    ordered_at,
    {%- for status in statuses %}
    sum(case when status = '{{ status }}' then amount else 0 end) as {{ status }}_amount
        {{- "," if not loop.last }}
    {%- endfor %}
from {{ ref('stg_orders') }}
group by 1

select
    ordered_at,
    sum(case when status = 'completed' then amount else 0 end) as completed_amount,
    sum(case when status = 'returned' then amount else 0 end) as returned_amount
from "bookshop"."main"."stg_orders"
group by 1

This runs a query during compilation — which has a consequence worth internalising: the model cannot compile if stg_orders does not exist yet. On a clean warehouse, dbt compile fails:

14:14:52  Encountered an error:
Compilation Error in model orders_by_status (models/marts/orders_by_status.sql)
  Database Error
    Catalog Error: Table with name stg_orders does not exist!

Guard it so a first-time build still parses:

{% set statuses = dbt_utils.get_column_values(ref('stg_orders'), 'status')
   if execute else [] %}

execute is false during the parse pass and true during the run pass. dbt reads every file twice, and forgetting that is behind most “works on my machine, fails in CI” macro bugs.

Querying the warehouse yourself

-- macros/log_row_count.sql
{% macro log_row_count(relation) %}
  {% if execute %}
    {% set result = run_query('select count(*) as n from ' ~ relation) %}
    {% do log("row count for " ~ relation ~ ": " ~ result.columns[0].values()[0], info=True) %}
  {% endif %}
{% endmacro %}

-- at the top of a model
{{ log_row_count(ref('stg_orders')) }}

14:19:07  row count for "bookshop"."main"."stg_orders": 8
14:19:07  1 of 1 START sql table model main.daily_revenue ................ [RUN]
14:19:07  1 of 1 OK created sql table model main.daily_revenue ........... [OK in 0.07s]

log(..., info=True) prints to the console; without info it only reaches the log file. {% do %} runs an expression without outputting it — use it whenever a macro call would otherwise dump None into your SQL.

Variables

# dbt_project.yml
vars:
  lookback_days: 3
  start_date: '2026-01-01'

where ordered_at >= '{{ var("start_date") }}'::date
  and ordered_at >= current_date - {{ var("lookback_days", 7) }}

dbt run --select order_events --vars '{lookback_days: 14}'

14:23:40  1 of 1 START sql incremental model main.order_events ........... [RUN]
14:23:40  1 of 1 OK created sql incremental model main.order_events ...... [OK in 0.08s]

where ordered_at >= '2026-01-01'::date
  and ordered_at >= current_date - 14

The second argument to var() is a default. Without one, a missing variable is a hard error — which is usually what you want for something like start_date.

For secrets and per-environment values, use the environment instead:

{{ env_var('DBT_WAREHOUSE_SIZE', 'xsmall') }}

Overriding dbt’s own macros

dbt’s internals are macros, so you can replace them. The one most projects override is schema naming, because the default appends the custom schema to the target schema:

-- macros/generate_schema_name.sql
{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = target.schema -%}
    {%- if target.name == 'prod' and custom_schema_name is not none -%}
        {{ custom_schema_name | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}
{%- endmacro %}

# target=prod
14:28:15  1 of 1 OK created sql table model reporting.revenue_by_day ..... [OK in 0.08s]
# target=dev
14:28:44  1 of 1 OK created sql table model dev_alice.revenue_by_day ..... [OK in 0.07s]

Clean schema names in production, everything in one personal sandbox in development. This is the standard override and worth adding to any project with more than one developer.

Knowing when to stop

Jinja can generate anything, and a model that is 80% template and 20% SQL is unreadable and undebuggable. Two questions before adding a loop:

1. Would the plain SQL be shorter? Three case statements beat a loop that produces three case statements.
2. Does the generated SQL change shape based on data? If yes, a schema change now depends on warehouse contents, and a stale value silently changes your table.

dbt compile is the check. If the compiled output surprises you, the template is too clever.

Practice

-- macros/country_name.sql
{% macro country_name(column_name) %}
    case {{ column_name }}
        when 'GB' then 'United Kingdom'
        when 'US' then 'United States'
        when 'NL' then 'Netherlands'
        else 'Unknown'
    end
{% endmacro %}

select
    customer_id,
    case country_code
        when 'GB' then 'United Kingdom'
        when 'US' then 'United States'
        when 'NL' then 'Netherlands'
        else 'Unknown'
    end as country
from "bookshop"."main"."stg_customers"

{%- set metrics = ['amount', 'discount', 'shipping'] %}
select
    ordered_at,
    {%- for m in metrics %}
    sum({{ m }}) as total_{{ m }}{{ "," if not loop.last }}
    {%- endfor %}
from {{ ref('stg_orders') }}
group by 1

select
    ordered_at,
    sum(amount) as total_amount,
    sum(discount) as total_discount,
    sum(shipping) as total_shipping
from "bookshop"."main"."stg_orders"
group by 1

select
    ordered_at,
    sum(amount) as total_amount,
    sum(discount) as total_discount,
    sum(shipping) as total_shipping,
from "bookshop"."main"."stg_orders"

14:38:02  Runtime Error in model order_totals (models/marts/order_totals.sql)
  Parser Error: syntax error at or near "from"

dbt run --select daily_revenue --vars '{start_date: "2026-01-08"}'

14:41:33  1 of 1 OK created sql table model main.daily_revenue ........... [OK in 0.07s]

┌────────────┬────────┬─────────┐
│ ordered_at │ orders │ revenue │
├────────────┼────────┼─────────┤
│ 2026-01-09 │      1 │    8.75 │
│ 2026-01-12 │      1 │   19.99 │
└────────────┴────────┴─────────┘

Next: snapshots — capturing how a row looked before it changed.