Expressions

{{ … }} output expressions embedded in SQL — refs, filters, splits, and the width they declare.

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

A construct after distinct stays with it

width 30

Before

select count(distinct {{ the_column }}) from t

After

select count(
    distinct {{ the_column }}
)
from t

A construct inside a string literal never breaks

Before

{%- set the_first_application_settings_snapshot_identifier_we_need = "442ab0695cfd8a3fa7ffbddb959903ad" -%}
select
    application_settings_snapshot_id,
    iff(application_settings_snapshot_id_from_the_snapshot_base_table = '{{  the_first_application_settings_snapshot_identifier_we_need  }}',
        '{{  the_first_application_settings_snapshot_identifier_we_need  }}', valid_from)      as valid_from
from app_settings

After

{%- set the_first_application_settings_snapshot_identifier_we_need = "442ab0695cfd8a3fa7ffbddb959903ad" -%}
select
    application_settings_snapshot_id,
    iff(
        application_settings_snapshot_id_from_the_snapshot_base_table
            = '{{  the_first_application_settings_snapshot_identifier_we_need  }}',
        '{{  the_first_application_settings_snapshot_identifier_we_need  }}',
        valid_from
    ) as valid_from
from app_settings

A dotted ref of three expressions stays dotted

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

Before

SELECT * FROM {{ db }}.{{ schema }}.{{ table }}

After

SELECT *
FROM {{ db }}.{{ schema }}.{{ table }}

A dotted ref of two expressions stays dotted

Before

SELECT * FROM {{ schema }}.{{ table }}

After

SELECT *
FROM {{ schema }}.{{ table }}

A hole narrower than its compiled text stays flat

Before

select case when event_type = '{{ c }}' then 1 else 0 end as conv from events

After

SELECT CASE WHEN EVENT_TYPE = '{{ c }}' THEN 1 ELSE 0 END AS CONV
FROM EVENTS

A hole opening with a newline still declares width

Before

select case when kind in ('charge', 'payment') then {{ first_sale_timestamp_in_reporting_timezone }} else null end as first_sale from t

After

SELECT
  CASE
    WHEN KIND IN ('charge', 'payment')
    THEN {{ first_sale_timestamp_in_reporting_timezone }}
    ELSE NULL
  END AS FIRST_SALE
FROM T

A hole wider than its compiled text breaks the case

Before

select case when event_type = '{{ conversion_event_type_variable }}' then 1 else 0 end as count_order_conversions from events

After

SELECT
  CASE
    WHEN EVENT_TYPE = '{{ conversion_event_type_variable }}' THEN 1
    ELSE 0
  END AS COUNT_ORDER_CONVERSIONS
FROM EVENTS

A join construct inside a literal stays tight

Before

select
    x
from t
where c in ('{{ mylist | join("', '") }}')

After

select x from t where c in ('{{ mylist | join("', '") }}')

A quote inside a construct does not end the literal

Before

select
    '{{ "a" | replace("'", "''") }}' as description
from t

After

select '{{ "a" | replace("'", "''") }}' as description from t

A template expression stays closed while the call after it opens

width 100

Before

select {{ a_schema + b_suffix }}.dedup_array(array_agg(struct(session_start_at as timestamp, fpc_id as id))) as fpc_id, x from t

After

select
    {{ a_schema + b_suffix }}.dedup_array(
        array_agg(struct(session_start_at as timestamp, fpc_id as id))
    ) as fpc_id,
    x
from t

A vanished item does not swallow the next item

Left unchanged — already in dfmt's shape:

select
    case when a then false else b end as is_account_leftside
    -- the below allows for pass through columns
    {{ passthrough }},
    case when lower(account_type) = 'bank' then 1 else 0 end as account_order
from accounts

An empty hole still declares its splice width

Before

select case when currency_id = '{{ home_currency_default }}' then amount else 0 end as amt from transfers

After

SELECT
  CASE
    WHEN CURRENCY_ID = '{{ home_currency_default }}' THEN AMOUNT
    ELSE 0
  END AS AMT
FROM TRANSFERS

An expression as the from table survives

Before

SELECT a FROM {{ table_name }}

After

SELECT A
FROM {{ table_name }}

An expression in a case condition survives

Before

SELECT CASE WHEN {{ col }} > 0 THEN 'yes' ELSE 'no' END FROM t

After

SELECT CASE WHEN {{ col }} > 0 THEN 'yes' ELSE 'no' END
FROM T

An expression in a join condition survives

Before

SELECT a FROM t1 JOIN t2 ON t1.{{ col }} = t2.{{ col }}

After

SELECT A
FROM T1
JOIN T2 ON T1.{{ col }} = T2.{{ col }}

An expression in order by survives

Before

SELECT * FROM t ORDER BY {{ col }}

After

SELECT *
FROM T
ORDER BY {{ col }}

An expression in the select list survives

Before

SELECT {{ col }}, id FROM t

After

SELECT {{ col }}, ID
FROM T

An expression in the where clause survives

Before

SELECT a FROM t WHERE {{ col }} > 1

After

SELECT A
FROM T
WHERE {{ col }} > 1

An expression repeated in group by survives twice

Before

SELECT {{ col }}, count(*) FROM t GROUP BY {{ col }}

After

SELECT {{ col }}, COUNT(*)
FROM T
GROUP BY {{ col }}

Continuations do not drift

Before

with spine as (
    {{ dbt_utils.date_spine(
        datepart="day",
        start_date="a",
        end_date="b"
    ) }}
)
select * from spine

After

with
    spine as (
        {{ dbt_utils.date_spine(
            datepart="day",
            start_date="a",
            end_date="b"
        ) }}
    )
select *
from spine

Multiline construct reanchors inside a block

Before

with
{% if is_incremental() %}
snapchat_ads as (
    select
        {{ "account" ~
            "_id_" ~
            "mapped" }} as account_identifier_for_campaign_report,
        spend_in_usd_at_daily_conversion_rates,
        impressions_counted_by_platform
    from snapchat_campaign_report
),
{% endif %}
base as (select id from t)
select *
from base

After

with
    {% if is_incremental() %}
        snapchat_ads as (
            select
                {{ "account" ~ "_id_" ~ "mapped" }} as account_identifier_for_campaign_report,
                spend_in_usd_at_daily_conversion_rates,
                impressions_counted_by_platform
            from snapchat_campaign_report
        ),
    {% endif %}

    base as (select id from t)
select *
from base

Statement construct stays at the margin

Before

with
    final as (
        select
            person_identifier,
            marketing_qualification_timestamp,
            product_qualification_timestamp,
            sales_acceptance_timestamp,
            first_order_conversion_timestamp
        from prep_crm_person_source
    )
{{
    final_select
}}

After

with
    final as (
        select
            person_identifier,
            marketing_qualification_timestamp,
            product_qualification_timestamp,
            sales_acceptance_timestamp,
            first_order_conversion_timestamp
        from prep_crm_person_source
    )
{{ final_select }}

Three expressions across clauses all survive

Before

SELECT {{ col1 }}, {{ col2 }} FROM {{ tbl }}

After

SELECT {{ col1 }}, {{ col2 }}
FROM {{ tbl }}

Vanished item extension keeps the item column

Before

with aggregated as (
  select
    source_relation,
    account_id,
    account_name,
    sum(clicks) as clicks,
    sum(impressions) as impressions,
    sum(spend) as spend,
    sum(conversions_value) as conversions_value
    {{ passthrough_metrics }}
  from base
)
select * from aggregated

After

with
    aggregated as (
        select
            source_relation,
            account_id,
            account_name,
            sum(clicks) as clicks,
            sum(impressions) as impressions,
            sum(spend) as spend,
            sum(conversions_value) as conversions_value
            {{ passthrough_metrics }}
        from base
    )
select *
from aggregated

A construct after distinct stays with it​

A construct inside a string literal never breaks​

A dotted ref of three expressions stays dotted​

A dotted ref of two expressions stays dotted​

A hole narrower than its compiled text stays flat​

A hole opening with a newline still declares width​

A hole wider than its compiled text breaks the case​

A join construct inside a literal stays tight​

A quote inside a construct does not end the literal​

A template expression stays closed while the call after it opens​

A vanished item does not swallow the next item​

An empty hole still declares its splice width​

An expression as the from table survives​

An expression in a case condition survives​

An expression in a join condition survives​

An expression in order by survives​

An expression in the select list survives​

An expression in the where clause survives​

An expression repeated in group by survives twice​

Continuations do not drift​

Multiline construct reanchors inside a block​

Statement construct stays at the margin​

Three expressions across clauses all survive​

Vanished item extension keeps the item column​

A construct after distinct stays with it

A construct inside a string literal never breaks

A dotted ref of three expressions stays dotted

A dotted ref of two expressions stays dotted

A hole narrower than its compiled text stays flat

A hole opening with a newline still declares width

A hole wider than its compiled text breaks the case

A join construct inside a literal stays tight

A quote inside a construct does not end the literal

A template expression stays closed while the call after it opens

A vanished item does not swallow the next item

An empty hole still declares its splice width

An expression as the from table survives

An expression in a case condition survives

An expression in a join condition survives

An expression in order by survives

An expression in the select list survives

An expression in the where clause survives

An expression repeated in group by survives twice

Continuations do not drift

Multiline construct reanchors inside a block

Statement construct stays at the margin

Three expressions across clauses all survive

Vanished item extension keeps the item column