How to loop over a list of values in a dbt model (Jinja for)

Writing the same expression for every status, country or month is repetitive and easy to get wrong. A Jinja for loop writes it for you: you keep a list, dbt generates one line per value. The typical case is a pivot, one column per value of a category.

Copy and paste

A fixed list:

-- models/marts/orders_by_status.sql
{%- set statuses = ['placed', 'shipped', 'returned'] -%}

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

What dbt compiles:

select
    customer_id,
    sum(case when status = 'placed' then amount else 0 end) as placed_amount,
    sum(case when status = 'shipped' then amount else 0 end) as shipped_amount,
    sum(case when status = 'returned' then amount else 0 end) as returned_amount
from "shop"."main"."stg_orders"
group by customer_id

The values read from the data, with dbt_utils:

{%- set statuses = dbt_utils.get_column_values(ref('stg_orders'), 'status') -%}

or with your own macro, as in how to write your first macro:

{%- set statuses = get_statuses() -%}

The rest of the model stays the same.

How the loop works

• loop.last is true on the last value. {%- if not loop.last %},{% endif %} puts a comma after every column except the last one, so the select stays valid.
• The other loop variables: loop.first, loop.index (from 1) and loop.length.
• The dashes in {%- and -%} remove the line breaks Jinja would otherwise leave, so the compiled SQL has no empty lines.

A list from the data

A fixed list must be updated when a new status appears. A list read from the table follows the data, at a price:

• dbt runs an extra query each time it compiles the model;
• when a new value appears, the model gains a column, which matters for an incremental model and for anything downstream that selects specific columns;
• column names come from the data, so values with spaces or symbols need cleaning first.

A fixed list is often the better choice for values that rarely change. When in doubt, add an accepted_values test with the same list, so you hear about new values.

Other loops

• Over several lists at once, use a list of pairs: {% for code, label in [('IT', 'italy'), ('DE', 'germany')] %}.
• Over the columns of a table, adapter.get_columns_in_relation(ref('stg_orders')) returns them, and dbt_utils.star() selects all columns except the ones you exclude.
• A pivot without writing the loop: dbt_utils.pivot('status', statuses) writes the sum(case ...) lines for you. By default it sums 1 for every matching row, so it counts orders; add then_value='amount' and suffix='_amount' to get the amounts as above.

Tested with dbt Core 1.12 and dbt_utils 1.4.1.