Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 41 ยท Jinja and Macros

Control Flow and Loops in Jinja

Use Jinja if statements, for loops, the loop variable, dictionaries and namespaces to generate repetitive SQL in dbt, and run the rendered SQL on sample data.

The reason to learn Jinja is to stop copy-pasting SQL. A pivot with ten near-identical CASE columns, a union of twelve monthly tables, a condition that differs between development and production: control flow handles all of them. This lesson covers if, for, the loop helper variable and the patterns you will use most.

if, elif and else

JINJA
{% if target.name == 'prod' %}
    from {{ source('shop', 'orders') }}
{% else %}
    from {{ source('shop', 'orders') }} limit 1000
{% endif %}

A classic dbt use: in development, process a sample so runs are fast; in production, process everything. Conditions can use ==, !=, <, >, and, or, not, in, and tests such as is defined, is none, is string, is number.

Python if / elif / else

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Here target is a plain variable we pass in; in real dbt it is the built-in target object (target.name), described in Context Functions. The same template compiles to three different queries.

for loops

A for loop repeats a block for each item. Its main job in SQL is generating repeated columns:

JINJA
{% for status in ['pending', 'shipped', 'delivered', 'cancelled'] %}
    sum(case when status = '{{ status }}' then 1 else 0 end) as {{ status }}_orders
    {%- if not loop.last %},{% endif %}
{% endfor %}

The loop variable

Inside a loop, Jinja gives you a loop object:

Expression Meaning
loop.index Counter starting at 1
loop.index0 Counter starting at 0
loop.first / loop.last True on the first / last pass
loop.length Total number of items
loop.revindex Counter from the end

loop.last solves the eternal SQL problem of the trailing comma: add a comma after every item except the last.

Run this one to see the whole pipeline: the template renders to SQL, which you can then execute.

Python Generating a pivot with a loop

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Now run the SQL it generated against the sample shop data:

SQL The generated pivot, executed ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

If tomorrow a fifth status returned appears, you add one word to the list instead of one more hand-written line, and every CASE stays consistent.

Looping over dictionaries

Use .items() to loop over key and value pairs:

Python Dictionary loop: price bands

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Because Python dictionaries keep insertion order, the WHEN branches come out in the order you wrote them, which matters for CASE.

Loops that build a UNION

A dbt favorite: the same query over several tables.

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

{% for region in regions %}
select '{{ region }}' as region, * from {{ ref('stg_orders_' ~ region) }}
{% if not loop.last %}union all{% endif %}
{% endfor %}

The ~ operator joins strings, so ref('stg_orders_' ~ region) becomes a different ref on each pass. dbt detects all three dependencies even though they are generated in a loop, because it records every ref call during rendering. (Keep the list defined in the file or in a var, so the dependency set is predictable.)

Scope and namespace

A variable set inside a loop disappears after the loop in Jinja, which surprises people who expect Python behavior:

Python The loop scope trap and namespace

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The first version prints 0, because the set inside the loop created a new local variable. The namespace object is the Jinja way to carry state out of a loop. In dbt, collecting values this way is rare; usually you build a list with {% do my_list.append(x) %} instead (the do statement is available in dbt's Jinja).

Useful tests and checks

JINJA
{% if var('start_date', none) is not none %}
  where ordered_at >= '{{ var("start_date") }}'
{% endif %}

{% if columns | length > 0 %} ... {% endif %}
{% if my_value is defined %} ... {% endif %}

Combined with var(), an optional filter appears in the SQL only when someone passes the variable. We use this in the context lesson.

Readability rules

  • Prefer a short loop over 20 copies, but not a loop so clever nobody can follow it. If Jinja makes a model unreadable, check the compiled output and consider a simpler design.
  • Put lists and mappings in a {% set %} at the top of the model (or in vars in dbt_project.yml).
  • Always compile and inspect: dbt compile --select my_model.
  • Keep Jinja out of simple models. Staging should read like SQL.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which expression is true only on the last pass of a for loop?

    loop.last is true on the final iteration, which is how you avoid a trailing comma.
  2. 2. What is printed by {% set n = 0 %}{% for i in [1,2] %}{% set n = n + i %}{% endfor %}{{ n }}?

    Variables set inside a loop are local to it. Use a namespace object to keep state across iterations.
  3. 3. How do you loop over key and value pairs of a dictionary d?

    d.items() yields (key, value) pairs in insertion order.
  4. 4. Why is a ref() inside a for loop still tracked by dbt?

    Dependencies come from the ref() calls that actually execute during rendering, including ones inside loops.

Summary

  • if/elif/else choose SQL by target, variable or condition; for repeats a block.
  • loop.last, loop.first and loop.index solve separators and numbering.
  • Loop over lists and dict.items(); use namespace for state that must survive a loop.
  • Keep Jinja readable and inspect results with dbt compile.

Next: Writing Macros