Basic Jinja Templating

33.1 Jinja in 40 Minutes (20 min)

Jinja is a templating language. dbt renders the Jinja in each model before sending the SQL to DuckDB. You've already used it: {{ ref(...) }}, {{ source(...) }}, {{ config(...) }}, {{ env_var(...) }}.

The three kinds of tags

A first template

Create analyses/jinja_playground.sql. Files in analyses/ are compiled but never run, which makes them perfect for experimenting.

{# A scratchpad for learning Jinja #}
{% set target_rate = 2.0 %}
{% set series = ['overnight_rate', 'fed_funds', 'yield_2yr'] %}

-- The inflation target is {{ target_rate }}%
-- We have {{ series | length }} series: {{ series | join(', ') }}
-- Upper-case first series: {{ series[0] | upper }}
select {{ target_rate }} + 1 as upper_band

cd macro_dbt
dbt compile --select jinja_playground
cat target/compiled/macro_dbt/analyses/jinja_playground.sql

Things to notice:

• Jinja variables look like Python: lists [...], indexing series[0], numbers and strings.
• Filters transform values with |: length, join(', '), upper, lower, trim, replace('a','b'), round(2).
• The {# #} comment and the {% set %} lines disappear from the output.

Jinja runs first, SQL runs second. Jinja can't see your data (it only knows what's written in the project, in variables, or in the target). It writes SQL text, and DuckDB executes that text afterwards. Keep this two-step model in mind and most confusion disappears.

33.2 Variables, Lists and for Loops: Generating Columns (25 min)

Yesterday's int_rates__monthly repeated the same line eight times. With a loop:

{% set rate_series = [
    'overnight_rate', 'fed_funds', 'yield_2yr', 'yield_10yr',
    'cad_usd', 'wti', 'oecd_cli', 'unemployment_rate'
] %}

select
    month,
    {% for s in rate_series %}
    max(value) filter (where series_name = '{{ s }}') as {{ s }}{{ ',' if not loop.last }}
    {% endfor %}
from {{ ref('int_rates__monthly_long') }}
group by month

• {% for s in rate_series %} … {% endfor %} repeats the block once per item.
• loop.last is true on the final iteration, so {{ ',' if not loop.last }} puts a comma after every line except the last. A trailing comma would be a SQL syntax error.
• '{{ s }}' (Jinja inside single quotes) produces a SQL string; {{ s }} alone produces a column name.

Compile and read the output. It's the eight lines you typed yesterday, generated.

Whitespace control

The compiled SQL contains blank lines where the {% %} tags were. A - inside a tag trims whitespace on that side:

    {%- for s in rate_series %}
    max(value) filter (where series_name = '{{ s }}') as {{ s }}{{ ',' if not loop.last }}
    {%- endfor %}

Tidy compiled SQL is easier to debug, but it's cosmetic. Don't lose time on perfect whitespace.

Useful loop properties

Try it 33.2

In analyses/jinja_playground.sql, write a loop that generates, for each lag in [1, 3, 6, 12], a line like lag(cpi, 3) over w as cpi_l3, with no comma after the last line. Compile and check.

{% set lags = [1, 3, 6, 12] %}
select
    {%- for l in lags %}
    lag(cpi, {{ l }}) over w as cpi_l{{ l }}{{ ',' if not loop.last }}
    {%- endfor %}
from cpi
window w as (order by month)

33.3 Conditionals and target: Lighter Dev Builds (23 min)

{% if %} … {% elif %} … {% else %} … {% endif %} works like Python's if (Day 2). Combined with target, the active environment, it lets one model behave differently in dev and prod.

What's in target?

-- analyses/jinja_playground.sql
-- target.name   = {{ target.name }}
-- target.schema = {{ target.schema }}
-- target.type   = {{ target.type }}

Faster dev builds on the big CPI table

The full CPI table has over a million rows. In dev you rarely need 1914–2005:

-- models/staging/stg_statcan__cpi.sql  (inside the `renamed` CTE, after `from source`)
    from source
    {% if target.name == 'dev' %}
    where ref_date >= '2000-01'          -- dev only: recent history is enough to develop against
    {% endif %}

dbt compile --select stg_statcan__cpi                 # dev: the WHERE clause is present
dbt compile --select stg_statcan__cpi --target prod   # prod: it disappears
diff target/compiled/macro_dbt/models/staging/stg_statcan__cpi.sql <(dbt compile --select stg_statcan__cpi --target prod --quiet; cat target/compiled/macro_dbt/models/staging/stg_statcan__cpi.sql) || true

(dbt compile --target prod only renders SQL. It doesn't create anything, so it's safe before the cut-over.)

Careful with filters and window functions. Filtering in staging means int_cpi__growth in dev has no data before 2000, so the first 12 months of YoY are NULL. That's acceptable in dev, but it's why the filter must never apply in prod. Also note that yesterday's regression check against Module 2 will now show differences in dev (older rows missing). Compare on month >= '2001-01-01' when you check dev builds.

Try it 33.3

Add a comment line at the very top of int_rates__monthly.sql that renders as -- built for target: dev (or prod). Then make the query include only months from 2015 when the target is dev.

-- built for target: {{ target.name }}
...
from {{ ref('int_rates__monthly_long') }}
{% if target.name == 'dev' %}
where month >= date '2015-01-01'
{% endif %}
group by month

33.4 Project Variables: var() and --vars (20 min)

Hard-coded values like '2000-01' or 2006-01-01 belong in one place. dbt's project variables play the role of config.toml (Day 23).

Add to dbt_project.yml:

vars:
  panel_start_date: "2006-01-01"     # first month in the macro panels
  dev_cpi_start: "2000-01"           # dev-only CPI filter (REF_DATE format)
  inflation_target: 2.0
  rate_series:
    - overnight_rate
    - fed_funds
    - yield_2yr
    - yield_10yr
    - cad_usd
    - wti
    - oecd_cli
    - unemployment_rate

Use them with var():

-- stg_statcan__cpi.sql
{% if target.name == 'dev' %}
where ref_date >= '{{ var("dev_cpi_start") }}'
{% endif %}

-- int_rates__monthly.sql
{%- for s in var('rate_series') %}
max(value) filter (where series_name = '{{ s }}') as {{ s }}{{ ',' if not loop.last }}
{%- endfor %}

Overriding from the command line

dbt compile --select stg_statcan__cpi --vars '{dev_cpi_start: "2020-01"}'

The --vars value is YAML inside single quotes. It overrides dbt_project.yml for that one invocation, which is handy for a quick narrow build or a backfill.

A known duplication: rate_series repeats information already in seeds/seed_series.csv. Jinja can't read seed data at compile time with what we've learned so far. On Days 34–37 you'll use run_query and packages to derive such lists from the seed itself, so the CSV becomes the single source of truth.

33.5 Looping over Structures: Gap-Safe Growth Rates in 15 Lines (20 min)

Loops aren't limited to strings. A list of dictionaries can describe a whole family of calculations. Here is Day 19's gap-safe growth logic, with each measure described once:

-- models/intermediate/int_cpi__growth.sql
-- Grain: month × geography × component. Gap-safe growth rates in %.
{% set horizons = [
    {"name": "mom",    "lag": 1,  "power": 1},
    {"name": "ann_3m", "lag": 3,  "power": 4},
    {"name": "ann_6m", "lag": 6,  "power": 2},
    {"name": "yoy",    "lag": 12, "power": 1},
] %}

with base as (

    select month, geo, geo_code, geo_level, component, cpi
    from {{ ref('stg_statcan__cpi') }}
    where not is_terminated

),

lagged as (

    select
        *,
        {%- for h in horizons %}
        lag(cpi, {{ h.lag }})   over w as cpi_l{{ h.lag }},
        lag(month, {{ h.lag }}) over w as month_l{{ h.lag }}{{ ',' if not loop.last }}
        {%- endfor %}
    from base
    window w as (partition by geo, component order by month)

)

select
    month, geo, geo_code, geo_level, component, cpi,
    {%- for h in horizons %}
    case when month_l{{ h.lag }} = cast(month - interval {{ h.lag }} month as date)
         then 100 * (power(cpi / cpi_l{{ h.lag }}, {{ h.power }}) - 1)
    end as {{ h.name }}{{ ',' if not loop.last }}
    {%- endfor %}
from lagged

Compile it and compare the output with yesterday's hand-written version: identical SQL, but the economics (which horizons, which annualisation power) now sits in one small, readable table at the top.

Adding a measure, say 24-month growth annualised (power 0.5), is now a one-line change:

    {"name": "ann_24m", "lag": 24, "power": 0.5},

Don't over-template. Jinja is excellent for repetition with a clear pattern. If a reader has to mentally execute three nested loops to understand a model, plain SQL would have been better. Rule of thumb: loop over lists of columns or measures, not over business logic.

Try it 33.5

Using the same pattern, write models/intermediate/int_gdp__growth_rates.sql for quarterly real GDP with horizons qoq (lag 1, power 1), qoq_ann (lag 1, power 4) and yoy (lag 4, power 1). Remember that one quarter is 3 months, so the date check must use interval {{ 3 * h.lag }} month.

{% set horizons = [
    {"name": "real_qoq",     "lag": 1, "power": 1},
    {"name": "real_qoq_ann", "lag": 1, "power": 4},
    {"name": "real_yoy",     "lag": 4, "power": 1},
] %}

with gdp as (

    select quarter, max(value) filter (where prices like 'Chained%') as real_gdp
    from {{ ref('stg_statcan__gdp') }}
    where estimate = 'Gross domestic product at market prices'
      and seasonal_adjustment = 'Seasonally adjusted at annual rates'
    group by quarter

),

lagged as (

    select
        *,
        {%- for l in horizons | map(attribute='lag') | unique %}
        lag(real_gdp, {{ l }}) over w as real_l{{ l }},
        lag(quarter, {{ l }})  over w as quarter_l{{ l }}{{ ',' if not loop.last }}
        {%- endfor %}
    from gdp
    window w as (order by quarter)

)

select
    quarter, real_gdp,
    {%- for h in horizons %}
    case when quarter_l{{ h.lag }} = cast(quarter - interval {{ 3 * h.lag }} month as date)
         then 100 * (power(real_gdp / real_l{{ h.lag }}, {{ h.power }}) - 1)
    end as {{ h.name }}{{ ',' if not loop.last }}
    {%- endfor %}
from lagged

33.6 Capstone: A Jinja-Powered Intermediate Layer + Regression Check (28 min)

Bring the dbt project up to parity with Module 2's staging and intermediate layers, using Jinja where it genuinely helps.

Requirements:

1. Staging (views): stg_statcan__cpi (dev filter via var), stg_statcan__gdp, stg_macro__observations: all reading source() and dbt seeds.
2. Intermediate:
   • int_rates__monthly_long (table), int_rates__monthly (view, loop over var('rate_series'), derived columns);
   • int_cpi__growth (table, horizons loop), int_cpi__smoothed (view);
   • int_gdp__growth (view): port Day 20's full model (real, nominal, deflator, MA4). You may generate its growth columns with a loop.
3. Every model has a grain comment at the top and a description in YAML.
4. dbt build runs cleanly from scratch: dbt build --full-refresh.
5. A regression analysis in analyses/regression_vs_module2.sql compares each dbt intermediate model with its Module 2 counterpart for month >= '2001-01-01' (to ignore the dev CPI filter), reporting the number of differing rows per model.

{# Compares dbt dev models with the Module 2 hand-built views.
   Compile, then run the compiled SQL in DuckDB (or via dbt show --inline). #}

{% set comparisons = [
    {"dbt": "int_cpi__growth",   "legacy": "intermediate.int_cpi__growth",
     "cols": "month, geo, component, round(cpi, 6), round(yoy, 6), round(mom, 6)",
     "filter": "month >= date '2001-01-01'"},
    {"dbt": "int_rates__monthly", "legacy": "intermediate.int_rates__monthly",
     "cols": "month, round(overnight_rate, 6), round(fed_funds, 6), round(spread_10_2_bp, 6)",
     "filter": "month >= date '2015-01-01'"},
] %}

{% for c in comparisons %}
select
    '{{ c.dbt }}' as model,
    (select count(*) from (
        select {{ c.cols }} from {{ ref(c.dbt) }} where {{ c.filter }}
        except
        select {{ c.cols }} from {{ c.legacy }} where {{ c.filter }}
    )) as only_in_dbt,
    (select count(*) from (
        select {{ c.cols }} from {{ c.legacy }} where {{ c.filter }}
        except
        select {{ c.cols }} from {{ ref(c.dbt) }} where {{ c.filter }}
    )) as only_in_legacy
{{ 'union all' if not loop.last }}
{% endfor %}

dbt compile --select regression_vs_module2
dbt show --inline "$(cat target/compiled/macro_dbt/analyses/regression_vs_module2.sql)"

Finally, look at the result as a graph:

dbt docs generate && dbt docs serve --port 8080

Your lineage graph now shows raw_dlt sources and seeds → staging → intermediate. Compare that picture with the numbered sql/ folder from Module 2: the same logic, but now self-documenting and dependency-aware.

Recap: Days 31–33

Looking ahead: Days 34–37

• Modular modelling: a disciplined staging → intermediate → marts split, and when to break a model apart.
• DRY principles and macros: turn today's horizons loop into a reusable macro ({{ growth_rates('cpi', horizons) }}) usable for CPI, GDP and any future series.
• run_query: derive rate_series from seed_series itself, with no duplication.
• Tests: unique, not_null, relationships, accepted_values, and custom tests replacing Module 2's sql/tests/.
• Incremental models and packages such as dbt_utils.

Day 33 checklist

• I can read and write Jinja expressions, statements, comments and filters
• I generate repetitive columns with for loops over lists and lists of dictionaries
• I use target to make dev builds lighter, without changing prod logic
• Constants and lists live in vars, and I can override them with --vars
• I know when templating helps readability, and when plain SQL is better
• My dbt dev layer reproduces the Module 2 intermediate layer, proven by a Jinja-generated regression analysis

cd ..
git add .
git commit -m "Day 33: Jinja templating, vars, growth-rate loops, regression analysis"
git push