Set, If, and For

Concepts covered: Jinja in models

set names a value

{% set states = ['opened', 'merged', 'closed'] %} creates a variable that lives for the rest of the file. It prints nothing, because it's a statement. Writing {{ states[0] }} later prints opened without quotes, because Jinja inserts the raw text of the value. When the SQL needs a string literal, you type the quotes: '{{ states[0] }}' renders to 'opened'. A number set with {% set min_year = 2024 %} renders through {{ min_year }} as 2024, which the warehouse reads as a number because nothing quotes it.

for repeats a block

Everything between {% for s in states %} and {% endfor %} is rendered once per item, with s holding the current item. The body is text with expressions in it, so it can hold a single column or a whole UNION branch. The 3 CASE columns from the intro become 1 block with 1 copy to edit, and the 4th state is 1 more entry in the list.

{% set states = ['opened', 'merged', 'closed'] %}

select
    project_id,
    {% for s in states %}
    sum(case when state = '{{ s }}'
        then 1 else 0 end) as {{ s }}_mrs
    {%- if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_merge_requests') }}
group by project_id

The if on the comma line places a comma only when another state follows. The loop variable it tests and the - inside {%- are explained further down this section.

select
    project_id,
    
    sum(case when state = 'opened'
        then 1 else 0 end) as opened_mrs,
    
    sum(case when state = 'merged'
        then 1 else 0 end) as merged_mrs,
    
    sum(case when state = 'closed'
        then 1 else 0 end) as closed_mrs
    
from "analytics"."dbt_dev"."stg_merge_requests"
group by project_id

Each pass printed the same 2 lines with s substituted twice: once inside quotes as a literal the warehouse compares against, once bare as part of a column name. The for statement left a blank line where it stood, and if and endfor did the same, so blank lines separate the columns. The warehouse skips them.

if decides whether text appears

The comma after each column is the detail that breaks copied SQL most often, and {% if not loop.last %},{% endif %} places it only when another column follows. loop is a variable Jinja provides inside every for block, and it carries the position of the current pass.

An if also works outside any loop. A flag set at the top of the file can switch a column or a filter on and off, and the compiled SQL contains the branch that was taken and no sign of the other. The template below adds a 3rd column only when the flag is true.

{% set include_locked = false %}

select
    project_id,
    count(*) as mrs
    {% if include_locked %}
    , sum(case when state = 'locked'
        then 1 else 0 end) as locked_mrs
    {% endif %}
from {{ ref('stg_merge_requests') }}
group by project_id

With the flag false, the compiled file has 2 columns and a blank gap where the branch stood. Set it to true and compile again: 3 columns, and the warehouse never learns that a choice was made. Putting the comma at the start of the optional column, rather than at the end of the one before it, keeps the SQL valid in both branches.

{% set states = ['opened', 'merged', 'closed'] %}

select
    project_id,
    {% for s in states %}
    sum(case when state = '{{ s }}'
        then 1 else 0 end) as {{ s }}_mrs
    {%- if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('stg_merge_requests') }}
group by project_id

Switch between the 2 versions and watch the lineage stay the same while the table gains a column. The raw data holds a locked merge request in both versions; in the 3-state version it is counted in no column at all, which is the quiet failure a loop over a hand-typed list can hide.

Looping over UNION branches

A loop body can be a whole SELECT. Suppose each region's merge requests land in a raw table of its own, declared as 3 sources. The loop below writes 1 branch per region, and the same loop.last test that placed commas now places union all between branches. ~ is Jinja's string join, so 'merge_requests_' ~ r evaluates to merge_requests_us on the first pass.

{% set regions = ['us', 'eu', 'apac'] %}

{% for r in regions %}
select
    '{{ r }}' as region,
    id as merge_request_id,
    state
from {{ source('raw', 'merge_requests_' ~ r) }}
{% if not loop.last %}union all{% endif %}
{% endfor %}

select
    'us' as region,
    id as merge_request_id,
    state
from "analytics"."raw"."merge_requests_us"
union all

select
    'eu' as region,
    id as merge_request_id,
    state
from "analytics"."raw"."merge_requests_eu"
union all

select
    'apac' as region,
    id as merge_request_id,
    state
from "analytics"."raw"."merge_requests_apac"

The 3 blank lines at the top are the set line, the empty line under it and the for line. Each branch carries its region as a quoted literal, so the unioned rows still say where they came from. The keyword appears twice for 3 branches because the if fails only on the last pass. Add a 4th region to the list and the compiled file gains a 4th branch and a 3rd union all, with no other edit.

About this lesson

This section is part of the Jinja and Macros: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.