Skip to main content

SQL interaction

Where Jinja meets SQL layout — tags straddling clauses, guards on select items, closers on CTEs.

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

A cast after a construct keeps its type​

dialect duckdb

Before

{%- macro gid(prefix='', descriptor=[], study_id='') -%}
{{ "'" ~ prefix ~ "' || '_' || md5('" ~ study_id ~ "')" }}
{%- endmacro -%}

select distinct
    {{ gid(prefix='ap', descriptor=['subjectconsent.consent'], study_id='phs000906_extended_cohort') }}::text as "accesspolicy_id",
    subjectconsent.consent
from subjectconsent

After

{%- macro gid(prefix='', descriptor=[], study_id='') -%}
    {{ "'" ~ prefix ~ "' || '_' || md5('" ~ study_id ~ "')" }}
{%- endmacro -%}

select distinct
    {{ gid(prefix = 'ap', descriptor = ['subjectconsent.consent'], study_id = 'phs000906_extended_cohort') }}::text
        as "accesspolicy_id",
    subjectconsent.consent
from subjectconsent

A clause body that reaches a construct shows its opener​

width 80

Before

with topics as (
    select
        *
    from
        {{ source("amedia_adp_sources__dlt_database", "mimisbrunnr__topic") }}
)
select * from topics

After

with
    topics as (
        select *
        from {{
            source("amedia_adp_sources__dlt_database", "mimisbrunnr__topic")
        }}
    )
select *
from topics

A clean template formats to its exact shape​

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

Before

SELECT {{ col }} FROM t

After

SELECT {{ col }}
FROM T

A comma after an endif trails the tag​

Before

select
    valid_starting_at

    --Only runs if the user passes the variable
    {% if var('updater_columns') %}
    ,
    {{ var('updater_columns') | join(", ") }}

    {% endif %}

    -- figure out which values are null
    ,case when value is null then 'is_null' else value end as value
from t

After

select
    valid_starting_at

    --Only runs if the user passes the variable
    {% if var('updater_columns') %}
        , {{ var('updater_columns') | join(", ") }}
    {% endif %},

    -- figure out which values are null
    case when value is null then 'is_null' else value end as value
from t

A construct glued into a string stays glued​

Before

{% set iplan = ['alpha', 'beta'] %}

select    x
from   t
where u in ('{{ iplan | join("', '") }}')

After

{% set iplan = ['alpha', 'beta'] %}

select x
from t
where u in ('{{ iplan | join("', '") }}')

A construct inside a statement stays inside it​

dialect ansi

Left unchanged — already in dfmt's shape:

select a, {# mid #} b from t

A construct that expands to two statements ships once​

Left unchanged — already in dfmt's shape:

{{ two_queries }}

A file with no jinja still formats as sql​

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

Before

SELECT a FROM t

After

SELECT A
FROM T

A leading config block does not indent a top level with​

config cteNamesOnNewLine = false, maxLineWidth = 80

Before

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

with recent as (select customer_id, order_total from orders), older as (select customer_id from archive)
select * from recent join older using (customer_id)

After

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

with recent as (select customer_id, order_total from orders),

older as (select customer_id from archive)
select *
from recent
join older using (customer_id)

A leading tag does not indent a top level with​

config cteNamesOnNewLine = false

Before

{% set x = 1 %}

with recent as (select customer_id, order_total, ordered_at from orders where ordered_at >= "2024-01-01")
select * from recent

After

{% set x = 1 %}

with recent as (select customer_id, order_total, ordered_at from orders where ordered_at >= "2024-01-01")
select *
from recent

A list tail construct reads the same on every pass​

Before

select
    row_number() over (partition by schedule_id, start_time_utc, schedule_valid_from, schedule_valid_until {{ var('src_rel', '') }} order by holiday_date, holiday_start_or_end) as valid_from_index
from join_holidays

After

select
    row_number() over (
        partition by schedule_id, start_time_utc, schedule_valid_from, schedule_valid_until {{ var('src_rel', '') }}
        order by holiday_date, holiday_start_or_end
    ) as valid_from_index
from join_holidays

A list with broken constructs stacks on its commas​

Before

{% macro padroniza_telefone(c) -%}
/* valor original com trim */ pt_{{ c }}
{%- endmacro %}

select
    nome_acompanhante,
    struct(
        telefone_acompanhante_original as telefone_original,
        {{ padroniza_telefone('telefone_acompanhante_original') }}.telefone_valido_whatsapp as telefone_valido_whatsapp,
        {{ padroniza_telefone('telefone_acompanhante_original') }}.motivo_invalidacao_telefone as motivo_invalidacao_telefone
    ) as telefone_acompanhante
from final

After

{% macro padroniza_telefone(c) -%}
    /* valor original com trim */ pt_{{ c }}
{%- endmacro %}

select
    nome_acompanhante,
    struct(
        telefone_acompanhante_original as telefone_original,
        {{ padroniza_telefone('telefone_acompanhante_original') }}.telefone_valido_whatsapp as telefone_valido_whatsapp,
        {{ padroniza_telefone('telefone_acompanhante_original') }}.motivo_invalidacao_telefone
            as motivo_invalidacao_telefone
    ) as telefone_acompanhante
from final

A multi token expansion keeps its qualified name together​

dialect tsql

Before

select a
from {{ db }}.t

After

select a from {{ db }}.t

A name part spelled like a statement keyword stays in the name​

dialect snowflake

Before

select *
from {{ relation }}

After

select * from {{ relation }}

A recovered fragment keeps space on both sides​

Before

select coalesce(c.n_contacts, 0) as n_customer
from {{ ref('persona') }} p
left  join   crm c on p.bsn = c.bsn and c.x > interactionen_30d 1 ;

After

select coalesce(c.n_contacts, 0) as n_customer
from {{ ref('persona') }} p
left join crm c on p.bsn = c.bsn and c.x > interactionen_30d 1 ;

A single token expansion keeps its qualified name together​

dialect tsql

Before

select a
from {{ db }}.t

After

select a from {{ db }}.t

A statement level expression keeps its construct​

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

Before

SELECT a FROM t1;
{{ sql_block }}

After

SELECT A
FROM T1;

{{ sql_block }}

A statement with a parse error stays verbatim​

Left unchanged — already in dfmt's shape:

select case when 1 = 1 then cast(x y as integer) else x y end as x y from t

A table named where is a table not an error​

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

Before

SELECT {{ col }} FROM WHERE

After

SELECT {{ col }}
FROM WHERE

Aligning case arms pads them into a column​

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

Before

select case when a then 1 when longer_condition then 22 else 333 end as v from t

After

SELECT
  CASE
    WHEN A                THEN 1
    WHEN LONGER_CONDITION THEN 22
    ELSE                       333
  END AS V
FROM T

An empty construct sharing its line stays inline​

Left unchanged — already in dfmt's shape:

select
    row_number() over (
        {{ '' }} order by schedule_created_at_timestamp, schedule_invalidated_at_timestamp
    ) = 1 as is_default_schedule_for_this_source_relation
from ticket_schedule_history

An expansion ending in a comment stays inside its statement​

Before

{{ config(materialized='table') }}
{%- set sql -%}
select x
-- trailing note
{%- endset -%}
with a as (
    select 1 as x
)

{{ sql }}

After

{{ config(materialized = 'table') }}
{%- set sql -%}
    select x
    -- trailing note
{%- endset -%}
with a as (select 1 as x)

{{ sql }}

An expansions trailing newline belongs to the construct​

dialect snowflake

Before

{% set d = "text\n" %}

select    '{{ d }}' as x
from t

After

{% set d = "text\n" %}

select '{{ d }}' as x
from t

Conditional cte​

Before

with
a as (select 1 as x),
{% if extra %}
b as (select 2 as x),
{% endif %}
c as (select 3 as x)
select * from c

After

with
    a as (select 1 as x),

    {% if extra %}
        b as (select 2 as x),
    {% endif %}

    c as (select 3 as x)
select *
from c

Conditional join​

Before

select a.id
from a
{% if with_b %}
left join b on b.id = a.id
{% endif %}
where a.x = 1

After

select a.id
from a
{% if with_b %}
    left join b on b.id = a.id
{% endif %}
where a.x = 1

Conditional set operand​

Before

select a from t1
{% if include_second %}
union all
select a from t2
{% endif %}

After

select a
from t1
{% if include_second %}
    union all
    select a
    from t2
{% endif %}

Construct glued to its field access​

Before

select
    array_agg(struct(
        telefone as telefone_original,
        origem,
        cast(prioridade as string) as prioridade, {{ phone_column }}.telefone_valido_whatsapp as telefone_valido_whatsapp, {{ phone_column }}.motivo_invalidacao_telefone as motivo_invalidacao_telefone
    ) order by prioridade) as telefones_gestante
from telefones_deduplicados

After

select
    array_agg(
        struct(
            telefone as telefone_original,
            origem,
            cast(prioridade as string) as prioridade,
            {{ phone_column }}.telefone_valido_whatsapp as telefone_valido_whatsapp,
            {{ phone_column }}.motivo_invalidacao_telefone as motivo_invalidacao_telefone
        )
        order by prioridade
    ) as telefones_gestante
from telefones_deduplicados

Functions whose bodies are read place no construct inside them​

dialect postgres

Before

CREATE OR REPLACE FUNCTION flag_classb(stringy varchar)
RETURNS integer AS $$
	SELECT (stringy ~* 
    'ASSISTED LIVING|CORRECTIONAL|NURSING| MENTAL|DORMITOR|MILITARY|GROUP HOME|BARRACK')::integer;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION flag_senior_housing(stringy varchar)
RETURNS integer AS $$
	SELECT (stringy ~* 
    'SENIOR|ELDERLY| AIRS |A.I.R.S|CONTINUING CARE| SARA |S.A.R.A')::integer;
$$ LANGUAGE sql;

After

CREATE OR REPLACE FUNCTION flag_classb(stringy varchar)
RETURNS integer
AS $$
	SELECT (stringy ~* 
    'ASSISTED LIVING|CORRECTIONAL|NURSING| MENTAL|DORMITOR|MILITARY|GROUP HOME|BARRACK')::integer;
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION flag_senior_housing(stringy varchar)
RETURNS integer
AS $$
	SELECT (stringy ~* 
    'SENIOR|ELDERLY| AIRS |A.I.R.S|CONTINUING CARE| SARA |S.A.R.A')::integer;
$$ LANGUAGE sql;

Guarded where breaks the cte​

Before

with
    source as (
        select * from file_manager_s3_object_source
        {% if not is_incremental() %}
            where cast(event_time as timestamp) > (select coalesce(max(load_dt), '1900-01-01') from watermark_state)
        {% endif %}
    )
select *
from source

After

with
    source as (
        select *
        from file_manager_s3_object_source
        {% if not is_incremental() %}
            where cast(event_time as timestamp) > (select coalesce(max(load_dt), '1900-01-01') from watermark_state)
        {% endif %}
    )
select *
from source

Statements around a multi statement construct keep their places​

Left unchanged — already in dfmt's shape:

select 0 as w;

{{ two_queries }}

select 3 as z

Tag inside a case expression​

Before

select
case
when a = 1 then 'one'
{% if extended %}
when a = 2 then 'two'
{% endif %}
else 'other'
end as label
from t

After

select
    case
        when a = 1 then 'one'
        {% if extended %}
            when a = 2 then 'two'
        {% endif %}
        else 'other'
    end as label
from t

Trailing comment after a construct​

Before

{% set n = 1 %}
select a
from t
where x = 1
    and n_dates = {{ n }} -- user has activity on 1 date
    and n_repos = {{ n }} -- user has activity on 1 repo

After

{% set n = 1 %}

select a
from t
where
    x = 1
    and n_dates = {{ n }} -- user has activity on 1 date
    and n_repos = {{ n }} -- user has activity on 1 repo

Upper config cases keywords and identifiers​

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

Before

SELECT a, b FROM t WHERE x > 1

After

SELECT A, B
FROM T
WHERE X > 1