Skip to main content

Set blocks

{% set x %}…{% endset %} blocks whose body is SQL, laid out where the block lands.

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

A column list set body is formatted where it lands​

Before

{% set cols %}
a,   b,
c
{% endset %}

select {{ cols }} from t

After

{% set cols %}
    a, b, c
{% endset %}

select {{ cols }}
from t

A comment ending a set body survives where it lands​

Before

{%- set extensions -%}
    , person_id as x_temp_record_origin
    {# , last_name as zzz_temp_last_name #}
{%- endset -%}

select
    id
    {{ extensions }}
from t

After

{%- set extensions -%}
    , person_id as x_temp_record_origin
    {# , last_name as zzz_temp_last_name #}
{%- endset -%}

select id {{ extensions }}
from t

A set body embedded by a macro as sql is formatted​

Before

{% macro calculate_metric(inner_sql) %}
with a as (
    {{ inner_sql }}
)
select count(*) as n from a
{% endmacro %}

{%- set metric_sql -%}
    SELECT
      id,
      CAST(active AS STRING) AS active,
      COALESCE(name, first_name) AS name,
      birth_date
    FROM {{ ref('Practitioner') }}
    WHERE active IS NOT NULL
{%- endset %}

{{ calculate_metric(metric_sql) }}

After

{% macro calculate_metric(inner_sql) %}
    with a as (
        {{ inner_sql }}
    )
    select count(*) as n from a
{% endmacro %}

{%- set metric_sql -%}
    SELECT id, CAST(active AS STRING) AS active, COALESCE(name, first_name) AS name, birth_date
    FROM {{ ref('Practitioner') }}
    WHERE active IS NOT NULL
{%- endset %}

{{ calculate_metric(metric_sql) }}

A set body holding a glued loop reaches one layout​

dialect clickhouse

Before

{%- set retained_lump_key -%}
k.main_account = y.retained_account and k.partner_data_area_id = ''{% for d in dims %} and k.{{ d }} = ''{% endfor %}
{%- endset -%}

select 1 from t where {{ retained_lump_key }}

After

{%- set retained_lump_key -%}
    k.main_account = y.retained_account
    and k.partner_data_area_id = ''{% for d in dims %} and k.{{ d }} = ''{% endfor %}
{%- endset -%}

select 1
from t
where {{ retained_lump_key }}

A set body landing in a string literal stays as written​

dialect databricks

Before

{%- set schema_str -%}
cdc_flag STRING, cdc_dsn BIGINT,
event_dt DATE
{%- endset -%}

select * from read_files('/batch/Trade.txt', schema => '{{ schema_str }}')

After

{%- set schema_str -%}
cdc_flag STRING, cdc_dsn BIGINT,
event_dt DATE
{%- endset -%}

select *
from read_files('/batch/Trade.txt', schema => '{{ schema_str }}')

A set body quoted by a macro stays as written​

dialect databricks

Before

{% macro read_daily_csv(filename, schema_str) %}
read_files('/batch/{{ filename }}', format => 'csv', schema => '{{ schema_str }}')
{% endmacro %}

{%- set schema_str -%}
cdc_flag STRING, cdc_dsn BIGINT, tradeid BIGINT,
status STRING, t_tt_id STRING,
event_dt DATE
{%- endset -%}

select * from {{ read_daily_csv('Trade.txt', schema_str) }}

After

{% macro read_daily_csv(filename, schema_str) %}
    read_files('/batch/{{ filename }}', format => 'csv', schema => '{{ schema_str }}')
{% endmacro %}

{%- set schema_str -%}
cdc_flag STRING, cdc_dsn BIGINT, tradeid BIGINT,
status STRING, t_tt_id STRING,
event_dt DATE
{%- endset -%}

select *
from {{ read_daily_csv('Trade.txt', schema_str) }}

A set body reaching sql through another set is formatted​

Before

{% set first %}
select   a from t1
{% endset %}
{% set both %}
{{ first }}
union all
select   a from t2
{% endset %}

with base as (
    {{ both }}
)
select * from base

After

{% set first %}
    select a
    from t1
{% endset %}

{% set both %}
    {{ first }}
    union all
    select a
    from t2
{% endset %}

with base as ({{ both }})
select *
from base

A set body read through a filter stays as written​

Before

{% set q %}
select   a,b
from t
{% endset %}

with base as (
    {{ q | indent(4) }}
)
select * from base

After

{% set q %}
select   a,b
from t
{% endset %}

with base as ({{ q | indent(4) }})
select *
from base

A set body used as sql is formatted​

Before

{% set q %}
select   id,
  cast(active as string) as active,
  coalesce(name, first_name) as name, birth_date
from t
where   active is not null
{% endset %}

{{ q }}

After

{% set q %}
    select id, cast(active as string) as active, coalesce(name, first_name) as name, birth_date
    from t
    where active is not null
{% endset %}

{{ q }}

A set body with a guard inside is formatted where it lands​

Before

{%- set offset_sql -%}
    {% if is_incremental() %}
    select nvl(max(z.seq), 0) from {{ this }} z
    {% else %}
      select 0 from dual
    {% endif %}
{%- endset -%}

with base as (
    {{ offset_sql }}
)
select * from base

After

{%- set offset_sql -%}
    {% if is_incremental() %}
        select nvl(max(z.seq), 0)
        from {{ this }} z
    {% else %}
        select 0
        from dual
    {% endif %}
{%- endset -%}

with base as ({{ offset_sql }})
select *
from base

An unused set body stays as written​

Before

{% set q %}
select   a,b
from t
{% endset %}

select * from base

After

{% set q %}
select   a,b
from t
{% endset %}

select *
from base