Skip to main content

Branches

{% if %} / {% elif %} / {% else %} blocks: where the tags land, and how each branch — taken or not — is laid out.

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

A branch body led by and keeps the and​

Before

SELECT
    a
FROM t
WHERE data <= DATE("x")

{% if flag %}
{% if other %}
{% set d = "x" %}
{% else %}
{% set d = "y" %}
{% endif %}
AND
    data >= DATE("{{ d }}")
{% endif %}

After

SELECT a
FROM t
WHERE data <= DATE("x")
{% if flag %}
    {% if other %}
        {% set d = "x" %}
    {% else %}
        {% set d = "y" %}
    {% endif %}
    AND data >= DATE("{{ d }}")
{% endif %}

A branch inside a dollar quoted body ships once​

dialect snowflake

Before

execute immediate $$
begin
    call system$send_snowflake_notification(
        snowflake.notification.text_plain(:v_message),
        {% if database_name == 'SYNAPSE_DATA_WAREHOUSE' %}
        snowflake.notification.integration('SLACK_INGEST_UPDATES')
        {% else %}
        snowflake.notification.integration('DEV_SLACK_INGEST_UPDATES')
        {% endif %}
    );
    return :v_message;
end;
$$;

After

execute immediate
    $$
begin
    call system$send_snowflake_notification(
        snowflake.notification.text_plain(:v_message),
        {% if database_name == 'SYNAPSE_DATA_WAREHOUSE' %}
        snowflake.notification.integration('SLACK_INGEST_UPDATES')
        {% else %}
        snowflake.notification.integration('DEV_SLACK_INGEST_UPDATES')
        {% endif %}
    );
    return :v_message;
end;
$$;

A branch that is the whole statement​

Before

{% set top = 1 %}
{% if flag %}
{{var(
        'query_a',
        'select 1 as a'
    )}}
{% else %}
{{var(
        'query_b',
        'select 2 as b'
    )}}
{% endif %}

After

{% set top = 1 %}

{% if flag %}
    {{ var('query_a', 'select 1 as a') }}
{% else %}
    {{ var('query_b', 'select 2 as b') }}
{% endif %}

A comment before a point guard closing an untaken branch keeps its order​

Before

select
    a
    {%- if var('include_extensions', false) %}
    , b
    {# the trailing note #}
    {% if var('with_c', false) %}, c{% endif %}
    {%- endif %}
from t

After

select
    a
    {%- if var('include_extensions', false) %}
        , b
        {# the trailing note #}
        {% if var('with_c', false) %}, c{% endif %}
    {%- endif %}
from t

A comment ending an untaken branch survives​

Before

select
    id
    {%- if var('include_extensions', false) %}
    , person_id as x_temp_record_origin
    {# , last_name as zzz_temp_last_name #}
    {%- endif %}
from t

After

select
    id
    {%- if var('include_extensions', false) %}
        , person_id as x_temp_record_origin
        {# , last_name as zzz_temp_last_name #}
    {%- endif %}
from t

A comment inside a forced else branch stays​

Before

WITH base_table AS (

    SELECT
        block_timestamp,
        tx_id,
        program_id,
        purchaser,
        _inserted_timestamp
    FROM
        {{ ref('silver__events') }}
        e
    WHERE
        program_id = 'HYPERfwdTjyJ2SCaKHmpF2MtrXqWxrsotYDsTrshHWq8'
        AND (purchaser = '5pdaXth4ijgDCeYDKgSx3jAbN7m8h4gy1LRCErAAN1LM'
        OR seller = '5pdaXth4ijgDCeYDKgSx3jAbN7m8h4gy1LRCErAAN1LM')

{% if is_incremental() %}
AND _inserted_timestamp >= (
    SELECT
        MAX(_inserted_timestamp)
    FROM
        {{ this }}
)
{% else %}
    AND block_timestamp >= '2022-09-22' -- no Hyperspace sales before this DATE
{% endif %}
),
sale_amt AS (
    SELECT
        b.tx_id,
        SUM(i.lamports) / pow(10, 9) AS sales_amount
    FROM
        base_table b
    GROUP BY
        b.tx_id
)
SELECT
    b.block_timestamp,
    e.sales_amount
FROM
    sale_amt e
    INNER JOIN base_table b
    ON e.tx_id = b.tx_id

After

WITH
    base_table AS (

        SELECT block_timestamp, tx_id, program_id, purchaser, _inserted_timestamp
        FROM {{ ref('silver__events') }} e
        WHERE
            program_id = 'HYPERfwdTjyJ2SCaKHmpF2MtrXqWxrsotYDsTrshHWq8'
            AND (
                purchaser = '5pdaXth4ijgDCeYDKgSx3jAbN7m8h4gy1LRCErAAN1LM'
                OR seller = '5pdaXth4ijgDCeYDKgSx3jAbN7m8h4gy1LRCErAAN1LM'
            )

            {% if is_incremental() %}
                AND _inserted_timestamp >= (SELECT MAX(_inserted_timestamp) FROM {{ this }})
            {% else %}
                AND block_timestamp >= '2022-09-22' -- no Hyperspace sales before this DATE
            {% endif %}
    ),

    sale_amt AS (SELECT b.tx_id, SUM(i.lamports) / pow(10, 9) AS sales_amount FROM base_table b GROUP BY b.tx_id)
SELECT b.block_timestamp, e.sales_amount
FROM sale_amt e
INNER JOIN base_table b ON e.tx_id = b.tx_id

A comment inside an untaken branch stays​

Before

with
    base as (
        select a, b
        from {{ ref('events') }}
        {% if not is_incremental() %}
        where evt_block_time >= date '2022-06-01'  -- seaport first txn
        {% endif %}
    )
select *
from base

After

with
    base as (
        select a, b
        from {{ ref('events') }}
        {% if not is_incremental() %}
            where evt_block_time >= date '2022-06-01' -- seaport first txn
        {% endif %}
    )
select *
from base

A construct in a nested taken branch stays inside the inner block​

Before

{% if var('use_sources', true) %}
{% if var('warehouse_ready', true) %}

{{
    config(
      alias='x',
      materialized='table'
    )
}}

with a as (
  select  id
  from t
)
select * from a

{% endif %}
{% endif %}

After

{% if var('use_sources', true) %}
    {% if var('warehouse_ready', true) %}

        {{ config(alias = 'x', materialized = 'table') }}

        with a as (select id from t)
        select *
        from a

    {% endif %}

{% endif %}

A guard carrying the next clause stays with the items​

Before

with
    repository_teams as (
        select
        {% if var('flag', true) %}
            *
        from {{ ref('t1') }}
        {% else %}
            a, b
        from {{ ref('t2') }}
        {% endif %}
    )
select *
from repository_teams

After

with
    repository_teams as (
        select
        {% if var('flag', true) %}
            * from {{ ref('t1') }}
        {% else %}
            a, b from {{ ref('t2') }}
        {% endif %}
    )
select *
from repository_teams

A guard glued inside a token ships verbatim​

Before

select a
from t
where b = 0
    {%- if flag %}
        and c = 1
    {%- endif -%}
order by a

After

select a
from t
where
    b = 0{%- if flag %}
        and c = 1
    {%- endif -%}order by a

A guard holding the whole where clause glued to the paren​

Before

with
    hours as (
        select hour from silver_hours
    ),

    token_addresses as (
        select token_address from ta
    ),

    hourly_token_prices as (
  SELECT
    hour,
    token_address
  FROM
    hours cross join token_addresses
{% if is_incremental() %}
WHERE
  HOUR BETWEEN timestamp_sub(current_timestamp(), interval 3 day)
  AND timestamp_trunc(current_timestamp(), hour)
{% else %}
WHERE
  HOUR BETWEEN timestamp('2020-05-05')
  AND timestamp_trunc(current_timestamp(), hour) -- first date with valid prices data
{% endif %})
select *
from hourly_token_prices

After

with
    hours as (select hour from silver_hours),

    token_addresses as (select token_address from ta),

    hourly_token_prices as (
        SELECT hour, token_address
        FROM hours
        cross join token_addresses
        {% if is_incremental() %}
            WHERE
                HOUR BETWEEN timestamp_sub(current_timestamp(), interval 3 day)
                    AND timestamp_trunc(current_timestamp(), hour)
        {% else %}
            WHERE HOUR BETWEEN timestamp('2020-05-05') AND timestamp_trunc(current_timestamp(), hour) -- first date with valid prices data
        {% endif %})
select *
from hourly_token_prices

A guard nested in an else gets its branch sampled​

Before

select action_id, block_id
from t
where
    receiver_id = 'contract.main.burrow.near'
    and receipt_succeeded = true

        {% if var("MANUAL_FIX") %}
        AND {{ partition_load_manual('no_buffer') }}
        {% else %}

            {% if var("INCREMENTAL_RUN") %}

            AND _modified_timestamp >= (
                SELECT
                    MAX(_modified_timestamp)
                FROM
                    {{ this }}
            )
        {% endif %}
        {% endif %}

After

select action_id, block_id
from t
where
    receiver_id = 'contract.main.burrow.near'
    and receipt_succeeded = true

    {% if var("MANUAL_FIX") %}
        AND {{ partition_load_manual('no_buffer') }}
    {% else %}

        {% if var("INCREMENTAL_RUN") %}

            AND _modified_timestamp >= (SELECT MAX(_modified_timestamp) FROM {{ this }})
        {% endif %}
    {% endif %}

A jinja comment in an untaken branch pairs with its node​

Before

select
    a
    {%- if var('cols', []) -%}
      {%- set names = [] -%}
      {# add the aggregated columns #}
      {%- for c in var('cols', []) %}
        , {{ c }}
      {%- endfor -%}
    {%- endif %}
from t

After

select
    a
    {%- if var('cols', []) -%}
        {%- set names = [] -%}
        {# add the aggregated columns #}
        {%- for c in var('cols', []) %}
            , {{ c }}
        {%- endfor -%}
    {%- endif %}
from t

A kept set between laid sets opening an untaken branch keeps its order​

Before

{% if var('claims_enabled', false) == true -%}
{%- set claims_source_columns -%}
    , elig.data_source
{%- endset -%}
{%- set claims_unused_columns -%}
    , elig.file_name
{%- endset -%}
{%- set claims_metadata_columns -%}
    , elig.ingest_datetime
{%- endset -%}

select
    elig.person_id
    {{ claims_source_columns }}
    {{ claims_metadata_columns }}
from t as elig

{%- endif %}

After

{% if var('claims_enabled', false) == true -%}
    {%- set claims_source_columns -%}
        , elig.data_source
    {%- endset -%}
    {%- set claims_unused_columns -%}
        , elig.file_name
    {%- endset -%}
    {%- set claims_metadata_columns -%}
        , elig.ingest_datetime
    {%- endset -%}

    select elig.person_id {{ claims_source_columns }} {{ claims_metadata_columns }}
    from t as elig
{%- endif %}

A lone construct in an untaken branch frames under its tag​

Before

{% if target.name == 'prod' %}
    {{
  config(
    materialized='table',
  )
}}
{% endif %}

select a
from t

After

{% if target.name == 'prod' %}
    {{
        config(
          materialized='table',
        )
    }}
{% endif %}

select a
from t

A nested guard beside a set block keeps its tags​

Before

{% if is_incremental() and execute %}
    {% set start_date_query %}
    SELECT COALESCE(MAX(d), 1) AS s
    FROM {{ this }}
    {% endset %}
    {% if execute %}
        {% set start_date = "a" %}
    {% else %} {% set start_date = "2023-01-01" %}  -- valor default
    {% endif %}
{% endif %}

select    a
from   t
where d > '{{ start_date }}'

After

{% if is_incremental() and execute %}
    {% set start_date_query %}
    SELECT COALESCE(MAX(d), 1) AS s
    FROM {{ this }}
    {% endset %}
    {% if execute %}
        {% set start_date = "a" %}
    {% else %}
        {% set start_date = "2023-01-01" %}  -- valor default
    {% endif %}
{% endif %}

select a
from t
where d > '{{ start_date }}'

A nested guard inside a wrapping blocks taken body keeps its place​

Before

{{ config(materialized='table') }}

{% if var('run_date', 'DATA') <= var('DATA_V6', 'V6') %}

-- period one: the planned window
with trips as (
    select data, trip_id
    from {{ ref('subsidio_trips_desaninhada') }}
    {% if is_incremental() %}
    where data = "2024-01-01"
    {% endif %}
)
select * from trips

{% else %}

select 1 as x

{% endif %}

After

{{ config(materialized = 'table') }}

{% if var('run_date', 'DATA') <= var('DATA_V6', 'V6') %}

    -- period one: the planned window
    with
        trips as (
            select data, trip_id
            from {{ ref('subsidio_trips_desaninhada') }}
            {% if is_incremental() %}
                where data = "2024-01-01"
            {% endif %}
        )
    select *
    from trips

{% else %}

    select 1 as x
{% endif %}

A set in an untaken branch survives its nested loop​

Before

select
    a
    {%- if var('cols', []) -%}
      {%- set names = [] -%}
      {%- for c in var('cols', []) %}
        , {{ c }}
      {%- endfor -%}
    {%- endif %}
from t

After

select
    a
    {%- if var('cols', []) -%}
        {%- set names = [] -%}
        {%- for c in var('cols', []) %}
            , {{ c }}
        {%- endfor -%}
    {%- endif %}
from t

A statement tag opening a taken branch stays inside it​

Before

{{ config(schema=var('target_schema')) }}

{% set source_table = 'accesspolicy' %}

{% if source_table is not none %}
    {% do log("Using source_table: " ~ source_table, info=True) %}
    select * from {{ source_table }}
{% else %}
    {% do log("Warning source_table: " ~ source_table, info=True) %}
{% endif %}

After

{{ config(schema = var('target_schema')) }}

{% set source_table = 'accesspolicy' %}

{% if source_table is not none %}
    {% do log("Using source_table: " ~ source_table, info=True) %}
    select *
    from {{ source_table }}
{% else %}
    {% do log("Warning source_table: " ~ source_table, info=True) %}
{% endif %}

A taken guard ending in a comment​

Before

select  a
from t
where a > 0
{% if flag %}
    and b > 0 -- for initial query optimisation
{% endif %}

After

select a
from t
where
    a > 0
    {% if flag %}
        and b > 0 -- for initial query optimisation
    {% endif %}

A trimmed guard nested in an untaken guard survives​

Left unchanged — already in dfmt's shape:

with
    complete_partitions as (
        select id, tipo
        from {{ ref('staging') }}
        {% if var('flag', false) -%}
            where data between "{{ var('start', '2025-01-01') }}" and "{{ var('stop', '2025-12-31') }}"

            {% if var('parts', '') | length > 0 -%}
                union all

                select id, tipo
                from {{ ref('older') }}
                where id in ({{ var('parts', '') }})
            {%- endif %}
        {%- endif %}
    )
select *
from complete_partitions

A vanished construct at a branch bodys end stays inside it​

Before

select
    id,

    {% if var('using_invoices', true) %}
    invoice_id,
    {{ var('invoice_metadata', '') }}
    {% endif %}

    {% if var('using_subscriptions', true) %}
    subscription_id,
    {{ var('subscription_metadata', '') }}
    {% endif %}

    amount
from t

After

select
    id,

    {% if var('using_invoices', true) %}
        invoice_id,
        {{ var('invoice_metadata', '') }}
    {% endif %}

    {% if var('using_subscriptions', true) %}
        subscription_id,
        {{ var('subscription_metadata', '') }}
    {% endif %}

    amount
from t

A zero width construct ending a taken branch stays inside​

Before

SELECT
    toDate(block_timestamp)  AS date,
    count(*)                 AS n_events
FROM t1
{% if start_month and end_month %}
WHERE toStartOfMonth(block_timestamp) >= toDate('{{ start_month }}')
{% else %}
WHERE 1=1 {{ maybe_filter }}
{% endif %}
GROUP BY toDate(block_timestamp)

After

SELECT toDate(block_timestamp) AS date, count(*) AS n_events
FROM t1
{% if start_month and end_month %}
    WHERE toStartOfMonth(block_timestamp) >= toDate('{{ start_month }}')
{% else %}
    WHERE 1 = 1 {{ maybe_filter }}
{% endif %}
GROUP BY toDate(block_timestamp)

An empty construct does not stop its forced branch laying out​

Before

select
    struct(
        {% for metric_name in ["a", "b"] %}
            {% if metric_name in ["a"] -%}
                p_{{ metric_name }}   as   {{ metric_name }} {{ "," if not loop.last }}
            {% else -%}
                q_{{ metric_name }}   as   {{ metric_name }} {{ "," if not loop.last }}
            {% endif -%}
        {% endfor %}
    ) as metrics
from t

After

select
    struct(
        {% for metric_name in ["a", "b"] %}
            {% if metric_name in ["a"] -%}
                p_{{ metric_name }} as {{ metric_name }} {{ "," if not loop.last }}
            {% else -%}
                q_{{ metric_name }} as {{ metric_name }} {{ "," if not loop.last }}
            {% endif -%}
        {% endfor %}
    ) as metrics
from t

An operator after an untaken guard survives the seam​

dialect clickhouse

Before

SELECT
    a
FROM t
WHERE 1=1
{% if start_month and end_month %}
    AND toStartOfMonth(d) >= toDate('{{ start_month }}')
{% else %}
    {{ maybe_filter }}
{% endif %}
    AND b >= 1
GROUP BY a

After

SELECT a
FROM t
WHERE
    1 = 1
    {% if start_month and end_month %}
        AND toStartOfMonth(d) >= toDate('{{ start_month }}')
    {% else %}
        {{ maybe_filter }}
    {% endif %}
    AND b >= 1
GROUP BY a

An untaken branch comment does not swallow the blocks after it​

Before

select
    id

    {% if var('stitch', false) %}
      -- updated with mapping as part of post hook on the derived page views table
      , cast(user_identifier as varchar) as stitched_user_id
    {% else %}
      , cast(null as varchar) as stitched
    {% endif %}

    {% if var('iab', false) %}
      , iab_field
    {% endif %}

    {% if var('yauaa', false) %}
      , yauaa_field
    {% endif %}

    {% if var('ua', false) %}
      , ua_field
    {% endif %}
from t

After

select
    id

    {% if var('stitch', false) %}
        -- updated with mapping as part of post hook on the derived page views table
        , cast(user_identifier as varchar) as stitched_user_id
    {% else %}
        , cast(null as varchar) as stitched
    {% endif %}

    {% if var('iab', false) %}
        , iab_field
    {% endif %}

    {% if var('yauaa', false) %}
        , yauaa_field
    {% endif %}

    {% if var('ua', false) %}
        , ua_field
    {% endif %}
from t

An untaken guard ending in comments keeps them​

Before

select *
from t
{% if flag -%}
  where
    a > 1
-- dedupliquem 2 dies enrera
-- i per tant ho descartem aqui
{% endif %}

After

select *
from t
{% if flag -%}
    where a > 1
    -- dedupliquem 2 dies enrera
    -- i per tant ho descartem aqui
{% endif %}

Block around the whole statement​

Before

{% if flag %}
select a
from t
{% endif %}

After

{% if flag %}
    select a
    from t
{% endif %}

Both branches get the same treatment​

Before

{% if target_type == 'fabric' %}
SELECT A FROM T WHERE X = 1
{% else %}
select b from t where x = 1
{% endif %}

After

{% if target_type == 'fabric' %}
    SELECT A
    FROM T
    WHERE X = 1
{% else %}
    select b
    from t
    where x = 1
{% endif %}

Consecutive guards stay siblings in template order​

Before

with data as (
{% for x in [1] %}
    select a
    from t
    {%- if true %}
    left join p on t.id = p.id
    {%- endif %}
    {%- if true %}
    left join q on t.id = q.id
    {%- endif %}
{% endfor %}
)
select * from data

After

with
    data as (
        {% for x in [1] %}
            select a
            from t
            {%- if true %}
                left join p on t.id = p.id
            {%- endif %}
            {%- if true %}
                left join q on t.id = q.id
            {%- endif %}
        {% endfor %}
    )
select *
from data

Each branch lays out like the same sql item​

width 80

Before

{% set columns = {'boarded_at': 'DATETIME', 'card_id': 'ID', 'fare': 'NUMERIC'} %}

select
    event_date,
    {% for column, type in columns.items() %}
        {% if type == 'DATETIME' %}
            datetime(parse_timestamp('%Y-%m-%dT%H:%M:%E*S%Ez', json_value(content, '$.{{ column }}')), 'UTC') as {{ column }},
        {% elif type == 'ID' %}
            replace(json_value(content, '$.{{ column }}'), '.0', '') as {{ column }},
        {% else %}
            safe_cast(json_value(content, '$.{{ column }}') as {{ type }}) as {{ column }},
        {% endif %}
    {% endfor %}
    total
from t

After

{%
    set columns = {'boarded_at': 'DATETIME', 'card_id': 'ID', 'fare': 'NUMERIC'}
%}

select
    event_date,
    {% for column, type in columns.items() %}
        {% if type == 'DATETIME' %}
            datetime(
                parse_timestamp(
                    '%Y-%m-%dT%H:%M:%E*S%Ez',
                    json_value(content, '$.{{ column }}')
                ),
                'UTC'
            ) as {{ column }},
        {% elif type == 'ID' %}
            replace(json_value(content, '$.{{ column }}'), '.0', '')
                as {{ column }},
        {% else %}
            safe_cast(
                json_value(content, '$.{{ column }}') as {{ type }}
            ) as {{ column }},
        {% endif %}
    {% endfor %}
    total
from t

Else branch macro supplies the filter​

dialect clickhouse

Before

{% macro monthly_filter(col) -%}
    and {{ col }} >= toStartOfMonth(now() - interval 2 day)
{%- endmacro %}
select count(*) as n
from {{ ref('events') }}
where 1 = 1
{% if start_month and end_month %}
    and toStartOfMonth(date) >= toDate('{{ start_month }}')
{% else %}
    {{ monthly_filter('date') }}
{% endif %}
group by 1

After

{% macro monthly_filter(col) -%}
    and {{ col }} >= toStartOfMonth(now() - interval 2 day)
{%- endmacro %}

select count(*) as n
from {{ ref('events') }}
where
    1 = 1
    {% if start_month and end_month %}
        and toStartOfMonth(date) >= toDate('{{ start_month }}')
    {% else %}
        {{ monthly_filter('date') }}
    {% endif %}
group by 1

Guard in a with list takes the cte column​

Before

with
{% if full_reload %}
    lookback as (select max(year) as min_ys from {{ ref('lookback_src') }}),
{% endif %}
    max_perim_year as (select max(year) as y from {{ ref('perimeters_agg') }})
select *
from max_perim_year

After

with
    {% if full_reload %}
        lookback as (select max(year) as min_ys from {{ ref('lookback_src') }}),
    {% endif %}

    max_perim_year as (select max(year) as y from {{ ref('perimeters_agg') }})
select *
from max_perim_year

Taken execute block then vanishing expression​

Before

{% if execute %}
    {% set min_day = '2024-01-01' %}
{% endif %}
{{ '' }}
select a from t where d >= '{{ min_day }}'

After

{% if execute %}
    {% set min_day = '2024-01-01' %}
{% endif %}

{{ '' }}

select a
from t
where d >= '{{ min_day }}'