Skip to main content

Alignment

The align option in templates: rows inside guards and loops join the surrounding column, and what ends a group.

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

A blank line before a guard ends the group​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  created_at as created,

  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  count(*) as cnt
from orders

After

select
    customer_id as cust,
    created_at  as created,

    {% if include_totals %}
        sum(total) as total_spent,
    {% endif %}
    count(*)       as cnt
from orders

A blank line inside a guard ends the group​

dialect postgres · config align = aliases, maxLineWidth = 60

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,

  count(distinct order_identifier) as order_count,
  {% endif %}
  created_at as created
from orders

After

select
    customer_id    as cust,
    {% if include_totals %}
        sum(total) as total_spent,

        count(distinct order_identifier) as order_count,
    {% endif %}
    created_at                           as created
from orders

A call with a moustache argument​

dialect snowflake · config maxLineWidth = 40, align = named_arguments

Before

select *
from table(
  generator(
    rowcount => {{ var('rows') }},
    timelimit => 10
  )
)

After

select *
from table(
    generator(
        rowcount  => {{ var('rows') }},
        timelimit => 10
    )
)

A case in a loop generated item​

dialect postgres · config maxLineWidth = 80, align = case_arms

Before

select
  {% for c in ['a', 'b'] %}
  case when {{ c }} > 0 then 'positive' when {{ c }} < 0 then 'neg' else 'zero' end as sign_{{ c }},
  {% endfor %}
  id
from t

After

select
    {% for c in ['a', 'b'] %}
        case
            when {{ c }} > 0 then 'positive'
            when {{ c }} < 0 then 'neg'
            else                  'zero'
        end as sign_{{ c }},
    {% endfor %}
    id
from t

A comment row ends the group​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  name as n,
  -- totals
  sum(total) as total_spent,
  count(*) as cnt
from orders
group by 1, 2

After

select
    customer_id as cust,
    name        as n,
    -- totals
    sum(total) as total_spent,
    count(*)   as cnt
from orders
group by 1, 2

A guard at the end of the list​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  created_at as created
  {% if include_totals %}
  , sum(total) as total_spent
  {% endif %}
from orders

After

select
    customer_id      as cust,
    created_at       as created
    {% if include_totals %}
        , sum(total) as total_spent
    {% endif %}
from orders

A guard at the start of the list​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  customer_id as cust,
  created_at as created
from orders

After

select
    {% if include_totals %}
        sum(total) as total_spent,
    {% endif %}
    customer_id    as cust,
    created_at     as created
from orders

A guard in a cte select list​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

with base as (
  select
    customer_id as cust,
    {% if is_incremental() %}
    max(updated_at) as last_seen,
    {% endif %}
    count(*) as cnt
  from orders
  group by 1
)
select * from base

After

with
    base as (
        select
            customer_id         as cust,
            {% if is_incremental() %}
                max(updated_at) as last_seen,
            {% endif %}
            count(*)            as cnt
        from orders
        group by 1
    )
select *
from base

A guard of only a comment is no row​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  {% if include_notes %}
  -- notes are not modelled yet
  {% endif %}
  created_at as created
from orders

After

select
    customer_id as cust,
    {% if include_notes %}
        -- notes are not modelled yet
    {% endif %}
    created_at  as created
from orders

A guard under the river​

dialect postgres · config maxLineWidth = 60, align = aliases, clauseAlignment = right

Before

select customer_id as cust,
       {% if include_totals %}
       sum(total) as total_spent,
       {% endif %}
       created_at as created
  from orders

After

select customer_id    as cust,
       {% if include_totals %}
           sum(total) as total_spent,
       {% endif %}
       created_at     as created
  from orders

A guard with an empty else​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  {% else %}
  {% endif %}
  created_at as created
from orders

After

select
    customer_id    as cust,
    {% if include_totals %}
        sum(total) as total_spent,
    {% else %}
    {% endif %}
    created_at     as created
from orders

A guarded column​

dialect postgres · config maxLineWidth = 80, align = column_definitions

Before

create table {{ this }} (
  id bigint not null,
  {% if with_region %}
  customer_region varchar(10),
  {% endif %}
  amount numeric(12, 2)
)

After

create table {{ this }}(
    id                  bigint not null,
    {% if with_region %}
        customer_region varchar(10),
    {% endif %}
    amount              numeric(12, 2)
)

A guarded else​

dialect postgres · config maxLineWidth = 80, align = case_arms

Before

select
  case
    when status = 'a' then 'active'
    when status = 'pp' then 'paused'
    {% if with_default %}
    else 'other'
    {% endif %}
  end as status_label
from orders

After

select
    case
        when status = 'a'  then 'active'
        when status = 'pp' then 'paused'
        {% if with_default %}
            else                'other'
        {% endif %}
    end as status_label
from orders

A jinja comment ends the group​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  name as n,
  {# totals #}
  sum(total) as total_spent,
  count(*) as cnt
from orders
group by 1, 2

After

select
    customer_id as cust,
    name        as n,
    {# totals #}
    sum(total) as total_spent,
    count(*)   as cnt
from orders
group by 1, 2

A loop emits rows of the column​

dialect postgres · config maxLineWidth = 80, align = aliases

Before

select
  id as order_id,
  {% for col in ['a', 'bb', 'ccc'] %}
  sum({{ col }}) as total_{{ col }},
  {% endfor %}
  created_at as created
from orders

After

select
    id                 as order_id,
    {% for col in ['a', 'bb', 'ccc'] %}
        sum({{ col }}) as total_{{ col }},
    {% endfor %}
    created_at         as created
from orders

A loop with a comma guard​

dialect postgres · config maxLineWidth = 80, align = aliases

Before

select
  id as order_id,
  {% for col in ['a', 'bb', 'ccc'] %}
  sum({{ col }}) as total_{{ col }}{% if not loop.last %},{% endif %}
  {% endfor %}
from orders

After

select
    id                 as order_id,
    {% for col in ['a', 'bb', 'ccc'] %}
        sum({{ col }}) as total_{{ col }}{% if not loop.last %},{% endif %}
    {% endfor %}
from orders

A moustache aliased joins the column​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  {{ dbt_utils.generate_surrogate_key(['a', 'b']) }} as surrogate,
  created_at as created
from orders

After

select
    customer_id as cust,
    {{ dbt_utils.generate_surrogate_key(['a', 'b']) }}
                as surrogate,
    created_at  as created
from orders

A moustache on the right​

dialect postgres · config maxLineWidth = 80, align = comparisons

Before

select *
from orders
where customer_region = '{{ var("region") }}'
  and amount >= {{ var('floor') }}
  and id = 1

After

select *
from orders
where
    customer_region = '{{ var("region") }}'
    and amount      >= {{ var('floor') }}
    and id          = 1

A moustache that breaks carries as to the column​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  {{ dbt_utils.generate_surrogate_key(['customer_id', 'order_id', 'created_at']) }} as surrogate,
  created_at as created
from orders

After

select
    customer_id as cust,
    {{
        dbt_utils.generate_surrogate_key([
            'customer_id',
            'order_id',
            'created_at'
        ])
    }}          as surrogate,
    created_at  as created
from orders

A moustache value in an arm​

dialect postgres · config maxLineWidth = 80, align = case_arms

Before

select
  case
    when amount > 1000 then {{ var('big_label') }}
    when amount > 10 then 'medium'
    else 'small'
  end as size
from orders

After

select
    case
        when amount > 1000 then {{ var('big_label') }}
        when amount > 10   then 'medium'
        else                    'small'
    end as size
from orders

A nested guard joins the column​

dialect postgres · config maxLineWidth = 80, align = aliases

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  {% if include_counts %}
  count(order_id) as order_count,
  {% endif %}
  {% endif %}
  created_at as created
from orders

After

select
    customer_id             as cust,
    {% if include_totals %}
        sum(total)          as total_spent,
        {% if include_counts %}
            count(order_id) as order_count,
        {% endif %}
    {% endif %}
    created_at              as created
from orders

A set list with a guard​

dialect postgres · config maxLineWidth = 80, align = assignments

Before

update {{ this }}
set
  status = 'closed',
  {% if track_time %}
  closed_at = current_timestamp,
  {% endif %}
  reason_code = 3
where id = 1

After

update {{ this }}
set
    status        = 'closed',
    {% if track_time %}
        closed_at = current_timestamp,
    {% endif %}
    reason_code   = 3
where id = 1

A set value from a var​

dialect postgres · config maxLineWidth = 40, align = assignments

Before

update orders
set
  status = '{{ var("status") }}',
  amount_cents = amount * 100,
  id = 1
where id = 1

After

update orders
set
    status = '{{ var("status") }}',
    amount_cents = amount * 100,
    id     = 1
where id = 1

A single guarded row joins the column​

dialect postgres · config align = aliases, maxLineWidth = 60

Before

select
  customer_region as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  id as created
from orders

After

select
    customer_region as cust,
    {% if include_totals %}
        sum(total)  as total_spent,
    {% endif %}
    id              as created
from orders

A tag inside a case of an aligned row​

dialect bigquery · config align = aliases

Before

select
    1 as a,
    case
        {%- for p in ["x", "y"] %}
            when starts_with(b, '{{ p }}') then '{{ p }}'
        {%- endfor %}
    end as c
from t

After

select
    1   as a,
    case
        {%- for p in ["x", "y"] %}
            when starts_with(b, '{{ p }}') then '{{ p }}'
        {%- endfor %}
    end as c
from t

A trailing comment rides past the alias​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust, -- the customer
  sum(total) as total_spent,
  created_at as created -- when
from orders
group by 1, 3

After

select
    customer_id as cust, -- the customer
    sum(total)  as total_spent,
    created_at  as created -- when
from orders
group by 1, 3

A var as an alias value​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  '{{ var("region") }}' as region,
  customer_id as cust,
  amount as amt
from orders

After

select
    '{{ var("region") }}' as region,
    customer_id           as cust,
    amount                as amt
from orders

A where with a guarded condition​

dialect postgres · config maxLineWidth = 80, align = comparisons

Before

select *
from orders
where customer_region = 'EU'
  {% if is_incremental() %}
  and updated_at > (select max(updated_at) from {{ this }})
  {% endif %}
  and amount >= 10

After

select *
from orders
where
    customer_region    = 'EU'
    {% if is_incremental() %}
        and updated_at > (select max(updated_at) from {{ this }})
    {% endif %}
    and amount         >= 10

All kinds aligned in one template​

dialect postgres · config maxLineWidth = 80, align = all

Before

select
  customer_id as cust,
  case when status = 'a' then 'active' when status = 'pp' then 'paused' else 'other' end as status_label,
  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  created_at as created
from {{ ref('orders') }}
where customer_region = 'EU'
  and status_code = 1
group by 1, 2, 4

After

select
    customer_id    as cust,
    case
        when status = 'a'  then 'active'
        when status = 'pp' then 'paused'
        else                    'other'
    end            as status_label,
    {% if include_totals %}
        sum(total) as total_spent,
    {% endif %}
    created_at     as created
from {{ ref('orders') }}
where customer_region = 'EU' and status_code = 1
group by 1, 2, 4

An arm guarded by a block​

dialect postgres · config maxLineWidth = 80, align = case_arms

Before

select
  case
    when status = 'a' then 'active'
    {% if include_paused %}
    when status = 'pausedd' then 'paused'
    {% endif %}
    else 'other'
  end as status_label
from orders

After

select
    case
        when status = 'a'           then 'active'
        {% if include_paused %}
            when status = 'pausedd' then 'paused'
        {% endif %}
        else                             'other'
    end as status_label
from orders

An author aligned guard is kept aligned​

dialect postgres · config maxLineWidth = 60, preserveBreaks = breaks_and_alignment

Before

select
  customer_id     as cust,
  {% if include_totals %}
  sum(total)      as total_spent,
  {% endif %}
  created_at      as created
from orders

After

select
    customer_id    as cust,
    {% if include_totals %}
        sum(total) as total_spent,
    {% endif %}
    created_at     as created
from orders

Arms from a loop​

dialect postgres · config maxLineWidth = 80, align = case_arms

Before

select
  case
    {% for k, v in {'a': 'active', 'pp': 'paused'}.items() %}
    when status = '{{ k }}' then '{{ v }}'
    {% endfor %}
    else 'other'
  end as status_label
from orders

After

select
    case
        {% for k, v in {'a': 'active', 'pp': 'paused'}.items() %}
            when status = '{{ k }}' then '{{ v }}'
        {% endfor %}
        else 'other'
    end as status_label
from orders

Arms that span lines in a template​

dialect postgres · config maxLineWidth = 60

Before

select
  case
    when customer_region = 'EU' and order_total > {{ var('eu_floor') }} then 'european high value'
    when customer_region = 'US' and order_total > {{ var('us_floor') }} then 'american high value'
    else 'standard'
  end as segment
from {{ ref('orders') }}

After

select
    case
        when customer_region = 'EU'
            and order_total > {{ var('eu_floor') }}
        then 'european high value'

        when customer_region = 'US'
            and order_total > {{ var('us_floor') }}
        then 'american high value'

        else 'standard'
    end as segment
from {{ ref('orders') }}

Assignments from a loop​

dialect postgres · config maxLineWidth = 80, align = assignments

Before

update orders
set
  status = 'closed',
  {% for c in ['a', 'bb'] %}
  total_{{ c }} = 0,
  {% endfor %}
  id = 1
where id = 1

After

update orders
set
    status            = 'closed',
    {% for c in ['a', 'bb'] %}
        total_{{ c }} = 0,
    {% endfor %}
    id                = 1
where id = 1

Columns from a loop​

dialect postgres · config maxLineWidth = 80, align = column_definitions

Before

create table orders (
  id bigint not null,
  {% for c in ['region', 'country_code'] %}
  {{ c }} varchar(10),
  {% endfor %}
  amount numeric(12, 2)
)

After

create table orders(
    id          bigint not null,
    {% for c in ['region', 'country_code'] %}
        {{ c }} varchar(10),
    {% endfor %}
    amount      numeric(12, 2)
)

Equalities around a guard join​

dialect postgres · config maxLineWidth = 80, align = comparisons

Before

select *
from orders
where customer_region = 'EU'
  {% if filter_status %}
  and status = 'active'
  {% endif %}
  and amount_in_cents = 10

After

select *
from orders
where
    customer_region     = 'EU'
    {% if filter_status %}
        and status      = 'active'
    {% endif %}
    and amount_in_cents = 10

Implicit alias beside a moustache​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id cust,
  amount * {{ var('fx_rate') }} as amount_usd,
  created_at created
from orders

After

select
    customer_id                      cust,
    amount * {{ var('fx_rate') }} as amount_usd,
    created_at                       created
from orders

Leading commas around a guard​

dialect postgres · config align = aliases, commaStyle = leading, maxLineWidth = 60

Before

select
  customer_id as cust
  {% if include_totals %}
  , sum(total) as total_spent
  {% endif %}
  , created_at as created
from orders

After

select
      customer_id    as cust
    {% if include_totals %}
        , sum(total) as total_spent
    {% endif %}
    , created_at     as created
from orders

Rows in both branches join the column​

dialect postgres · config align = aliases, maxLineWidth = 60

Before

select
  customer_id as cust,
  {% if target.name == 'prod' %}
  sum(total) as total_spent,
  {% else %}
  count(order_id) as order_count,
  {% endif %}
  created_at as created
from orders

After

select
    customer_id         as cust,
    {% if target.name == 'prod' %}
        sum(total)      as total_spent,
    {% else %}
        count(order_id) as order_count,
    {% endif %}
    created_at          as created
from orders

Rows inside a guard join the surrounding column​

dialect postgres · config align = aliases, maxLineWidth = 60

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  count(order_id) as order_count,
  {% endif %}
  created_at as created
from orders

After

select
    customer_id         as cust,
    {% if include_totals %}
        sum(total)      as total_spent,
        count(order_id) as order_count,
    {% endif %}
    created_at          as created
from orders

Table aliases in from are not select rows​

dialect postgres · config maxLineWidth = 40, align = aliases

Before

select
  o.id as order_id,
  c.name as customer_name
from {{ ref('orders') }} as o
join {{ ref('customers') }} as c on c.id = o.customer_id

After

select
    o.id   as order_id,
    c.name as customer_name
from {{ ref('orders') }} as o
join {{ ref('customers') }} as c
    on c.id = o.customer_id

Two guards in a row​

dialect postgres · config maxLineWidth = 80, align = aliases

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  {% if include_counts %}
  count(order_id) as order_count,
  {% endif %}
  created_at as created
from orders

After

select
    customer_id         as cust,
    {% if include_totals %}
        sum(total)      as total_spent,
    {% endif %}
    {% if include_counts %}
        count(order_id) as order_count,
    {% endif %}
    created_at          as created
from orders

Whitespace control on the guard​

dialect postgres · config maxLineWidth = 60, align = aliases

Before

select
  customer_id as cust,
  {%- if include_totals %}
  sum(total) as total_spent,
  {%- endif %}
  created_at as created
from orders

After

select
    customer_id    as cust,
    {%- if include_totals %}
        sum(total) as total_spent,
    {%- endif %}
    created_at     as created
from orders

Without align a guard pads nothing​

dialect postgres · config maxLineWidth = 60

Before

select
  customer_id as cust,
  {% if include_totals %}
  sum(total) as total_spent,
  {% endif %}
  created_at as created
from orders

After

select
    customer_id as cust,
    {% if include_totals %}
        sum(total) as total_spent,
    {% endif %}
    created_at as created
from orders