Skip to main content

Loops

{% for … %} loops, including inline generators and loop-controlled separators like {% if not loop.last %}.

Each pair below is a real, passing case from dfmt's test corpus (suites/dfmt-jinja/cases/loops/): the template as written, then the same template after dfmt — every Jinja tag reproduced exactly, the SQL laid out around it. 27 examples.

A fully trimmed separator guard beside a chain slices whole​

Left unchanged — already in dfmt's shape:

select
    {% for col in ['session_identifier', 'app_id'] %}
        {% if col.lower() == 'session_identifier' -%}
            session_identifier as domain_sessionid
        {%- else -%}
            {{ col.lower() }}
        {%- endif -%}
        {%- if not loop.last -%},{%- endif %}
    {% endfor %}
from t

A loops first iteration own line tail comment survives​

dialect snowflake

Before

with calls as (
    {% for t in ['a', 'b'] %}
        select tx from {{ t }}
        -- one source per iteration
        {% if not loop.last %}
        union
        {% endif %}
    {% endfor %}
)
select * from calls

After

with
    calls as (
        {% for t in ['a', 'b'] %}
            select tx
            from {{ t }}
            -- one source per iteration
            {% if not loop.last %}
                union
            {% endif %}
        {% endfor %}
    )
select *
from calls

A nested loop whose body opens with a comment ships once​

Left unchanged — already in dfmt's shape:

select
    x,
    {% for m in metrics -%}
        {% for v in variants -%}
            -- a note
            f({{ m }}__{{ v }}) as {{ m }}__{{ v }},
        {% endfor -%}
    {% endfor -%}
from t

A per iteration comment in a loop body​

Before

select
    case
        {%- for r in rs %}  -- noqa: LT05
        when x = '{{ r }}'
        then 'Algorithm: {{ r }}'  -- noqa: LT05
        {%- endfor %}
        else 'none'
    end as v
from t

After

select
    case
    {%- for r in rs %}
        -- noqa: LT05
        when x = '{{ r }}' then 'Algorithm: {{ r }}' -- noqa: LT05
    {%- endfor %}
    else 'none'
    end as v
from t

A separator guard whose body ends in a comment stays inside the loop​

dialect snowflake

Before

with calls as (
    {% for t in ['a', 'b'] %}
        select tx from {{ t }}
        {% if not loop.last %}
        union -- there may be multiple calls in same tx
        {% endif %}
    {% endfor %}
)
select * from calls

After

with
    calls as (
        {% for t in ['a', 'b'] %}
            select tx
            from {{ t }}
            {% if not loop.last %}
                union -- there may be multiple calls in same tx
            {% endif %}
        {% endfor %}
    )
select *
from calls

A zero iteration extender keeps the list split​

Left unchanged — already in dfmt's shape:

select
    session_key,
    session_partition_key,
    min(event_date_dt) as session_partition_date -- the partition date
    {% for ce in [] %}
        , countif(event_name = '{{ ce }}') as {{ ce }}_count
    {% endfor %}
from t

A zero iteration for keeps its template placement​

Before

select
    a,
    b
{% for sp in [] %}
    , {{ sp }}
{% endfor %}
from t

After

select
    a,
    b
    {% for sp in [] %}
        , {{ sp }}
    {% endfor %}
from t

A zero iteration item through a macro lays out​

Before

{% macro extract_date(input_str) -%}
    regexp_extract({{ input_str }}, r'[0-9]{4}')
{%- endmacro %}
{% for test_case in [] %}
select   '{{ test_case.name }}'  as name,{{ extract_date(test_case.value) }}   as result
from   t   where  x=1
{% endfor %}

After

{% macro extract_date(input_str) -%}
    regexp_extract({{ input_str }}, r'[0-9]{4}')
{%- endmacro %}

{% for test_case in [] %}
    select '{{ test_case.name }}' as name, {{ extract_date(test_case.value) }} as result
    from t
    where x = 1
{% endfor %}

A zero iteration items methods are callable​

Before

{% for test_case in [] %}
{% set is_marts = test_case.node.fqn[1].endswith('marts') %}
select   {{ test_case.name }}   as n,  '{{ is_marts }}'   as m from   t
{% endfor %}

After

{% for test_case in [] %}
    {% set is_marts = test_case.node.fqn[1].endswith('marts') %}
    select {{ test_case.name }} as n, '{{ is_marts }}' as m
    from t
{% endfor %}

A zero iteration loop between two sets keeps template order​

Before

with described as (
    {% set numeric_cols = [] %}
    {% for col in numeric_cols %}
        {% do numeric_cols.append(col) %}
    {% endfor %}
    {% set stats = {'stddev': 'stddev'} %}
    select {{ stats['stddev'] }} as m
    from t
)
select * from described

After

with
    described as (
        {% set numeric_cols = [] %}
        {% for col in numeric_cols %}
            {% do numeric_cols.append(col) %}
        {% endfor %}
        {% set stats = {'stddev': 'stddev'} %}
        select {{ stats['stddev'] }} as m
        from t
    )
select *
from described

A zero iteration loop glued to the last token stays glued​

dialect clickhouse · width 64

Before

select 1 from t where a_rather_long_column = b_rather_long_column and c = ''{% for d in xs %} and k.{{ d }} = ''{% endfor %}

After

select 1
from t
where
    a_rather_long_column = b_rather_long_column
    and c = ''{% for d in xs %} and k.{{ d }} = ''{% endfor %}

A zero iteration loop with no separator ships one statement​

Before

{% for c in [] %}
select   {{ c.name }}   as only_col from   t
{% endfor %}

After

{% for c in [] %}
    select {{ c.name }} as only_col
    from t
{% endfor %}

An aligned case keeps its end home​

config align = case_arms

Before

select
    case
        {%- for r in rs %}  -- noqa: LT05
        when x = '{{ r }}'
        then 'Algorithm: {{ r }}'  -- noqa: LT05
        {%- endfor %}
        else 'none'
    end as v
from t

After

select
    case
    {%- for r in rs %}
        -- noqa: LT05
        when x = '{{ r }}' then 'Algorithm: {{ r }}' -- noqa: LT05
    {%- endfor %}
    else 'none'
    end as v
from t

An author inline separator guard stays inline on every pass​

Left unchanged — already in dfmt's shape:

select
    {% for m in ['a', 'b'] %}
        {%- if not loop.first %} , {% endif -%}
        count({{ m }}) as {{ m }}_count
    {% endfor %}
from t

An inline for in a select list stays verbatim​

dialect ansi · config keywordCase = upper, identifierCase = upper, functionCase = upper, dataTypeCase = upper, indentWidth = 2, maxLineWidth = 80, blankLineBetweenCtes = false, collapseShortStatements = false

Before

SELECT {% for col in cols %}{{ col }}, {% endfor %}id FROM t

After

SELECT
  {% for col in cols %}{{ col }}, {% endfor %}
  ID
FROM T

An inner in list ladder keeps its level on every pass​

Before

{{ config(materialized='table', schema='PGRNseq_data') }}

{%- set consent_groups = [
    {'description': 'Health/Medical/Biomedical', 'consents': ['1']},
    {'description': 'Disease', 'consents': ['2']}
] -%}

    {%- for group in consent_groups -%}
    (
    select DISTINCT
        CASE consent
            WHEN '2' THEN 'Dementia'
            ELSE NULL 
        END as disease_limitation,
        '{{ group.description }}' as description,
        NULL as website,
        cast({{ var('gid', 'md5(consent)') }} as string) as id
    from {{ ref('stg_subjectconsent') }} as subjectconsent
      where subjectconsent.consent in (
        {%- for c in group.consents -%}
            '{{ c }}'{% if not loop.last %}, 
          {% endif %}
        {%- endfor -%}
          )
        )
    {% if not loop.last %}union all{% endif %}
    {%- endfor -%}

After

{{ config(materialized = 'table', schema = 'PGRNseq_data') }}

{%-
    set consent_groups = [
        {'description': 'Health/Medical/Biomedical', 'consents': ['1']},
        {'description': 'Disease', 'consents': ['2']}
    ]
-%}

{%- for group in consent_groups -%}
    (
        select DISTINCT
            CASE consent WHEN '2' THEN 'Dementia' ELSE NULL END as disease_limitation,
            '{{ group.description }}' as description,
            NULL as website,
            cast({{ var('gid', 'md5(consent)') }} as string) as id
        from {{ ref('stg_subjectconsent') }} as subjectconsent
        where subjectconsent.consent in (
            {%- for c in group.consents -%}
                '{{ c }}'
                {% if not loop.last %}
                    ,
                {% endif %}
            {%- endfor -%}
        )
    )
    {% if not loop.last %}union all{% endif %}
{%- endfor -%}

Body materialises across lines​

Before

select
  a_reasonably_long_identifier_one,
  a_reasonably_long_identifier_two,
  {% for col in cols -%} offer.{{ col }}, {% endfor -%}
  a_reasonably_long_identifier_three
from some_table_with_a_name

After

select
    a_reasonably_long_identifier_one,
    a_reasonably_long_identifier_two,
    {% for col in cols -%}
        offer.{{ col }},
    {% endfor -%}
    a_reasonably_long_identifier_three
from some_table_with_a_name

Glued in list loop comes apart​

Before

select
    case
        when project_id in (
            {% for p in ['a', 'b'] %}'{{ p }}'{% if not loop.last %},
            {% endif %}{% endfor %}
        ) then 1
        else 0
    end as flag
from t

After

select
    case
        when project_id in (
            {% for p in ['a', 'b'] %}
                '{{ p }}'
                {% if not loop.last %}
                    ,
                {% endif %}
            {% endfor %}
        )
        then 1
        else 0
    end as flag
from t

Guard after an item opens its own line​

Before

{% set metrics = ["revenue", "cost", "margin"] %}
select
    stock_code,
    end_date,
    {% for metric in metrics %}
        {{ metric }} {% if not loop.last %},{% endif %}
    {%- endfor %}
from q1

After

{% set metrics = ["revenue", "cost", "margin"] %}

select
    stock_code,
    end_date,
    {% for metric in metrics %}
        {{ metric }}
        {% if not loop.last %}
            ,
        {% endif %}
    {%- endfor %}
from q1

Hoisted separator keeps the clause column​

Left unchanged — already in dfmt's shape:

select
    a as first_col,
    b as second_col
    -- take the rest
    {% set except_cols = ['a', 'b'] %}
    {% for col in cols %}
        {% if col not in except_cols %}
            , max({{ col }}) as {{ col }}
        {% endif %}
    {% endfor %}
from t

Inline loop inside a bracket​

Before

select
    case
        when project_id in (
        {% for p in projects %}'{{ p }}'{% if not loop.last %}, {% endif %}{% endfor %}
        )
        then 'ours'
    end as owner
from t

After

select
    case
        when project_id in (
            {% for p in projects %}'{{ p }}'{% if not loop.last %}, {% endif %}{% endfor %}
        )
        then 'ours'
    end as owner
from t

Inline separator across three iterations​

Before

select fix_version like any (
{%- for month in months %}
'v%({{ month }}%)'{%- if not loop.last %},{% endif -%}
{% endfor %}
) as is_release from t

After

select
    fix_version like any(
        {%- for month in months %}
            'v%({{ month }}%)'{%- if not loop.last %},{% endif -%}
        {% endfor %}
    ) as is_release
from t

Leaning guards keep template order​

Before

select
    a,
    b
{% if flag_one %}
    , c
{% endif %}
{% if flag_two %}
    , d
{% endif %}
from t

After

select
    a,
    b
    {% if flag_one %}
        , c
    {% endif %}
    {% if flag_two %}
        , d
    {% endif %}
from t

One liner loop in an in list​

Before

select a
from t
where x in ({% for c in cols %}'{{ c }}',{% endfor %} 'z')

After

select a from t where x in ({% for c in cols %}'{{ c }}',{% endfor %} 'z')

Separator between iterations​

Before

select
{% for c in cols %}
{{ c }}
{% if not loop.last %}
,
{% endif %}
{% endfor %}
from t

After

select
    {% for c in cols %}
        {{ c }}
        {% if not loop.last %}
            ,
        {% endif %}
    {% endfor %}
from t

Untaken guard with block shape opens​

Before

with
    stg_task_instance as (
        {% for src in ["airflow"] -%}
        select distinct
            task_id
            , dag_id
            , '{{ src }}' as source_system
        from stg_task_instance_{{ src }}
        {% if not loop.last -%} union {% endif -%}
        {% endfor -%}
    )
select *
from stg_task_instance

After

with
    stg_task_instance as (
        {% for src in ["airflow"] -%}
            select distinct task_id, dag_id, '{{ src }}' as source_system
            from stg_task_instance_{{ src }}
            {% if not loop.last -%}
                union
            {% endif -%}
        {% endfor -%}
    )
select *
from stg_task_instance

Zero iteration loop keeps its tags nested​

Before

select
    client_key,
    min(session_start_timestamp) as first_seen_timestamp,
    sum(sum_event_value_in_usd) as sum_event_value_in_usd,
    count(distinct session_key) as count_sessions
    {% if events %}
        {% for ce in events %}
          {% set clean = ce %}
            , sum(count_{{ clean }}) as count_{{ clean }}
        {% endfor %}
    {% endif %}
from sessions

After

select
    client_key,
    min(session_start_timestamp) as first_seen_timestamp,
    sum(sum_event_value_in_usd) as sum_event_value_in_usd,
    count(distinct session_key) as count_sessions
    {% if events %}
        {% for ce in events %}
            {% set clean = ce %}
            , sum(count_{{ clean }}) as count_{{ clean }}
        {% endfor %}
    {% endif %}
from sessions