Jinja Templating in dbt: Writing SQL That Writes Itself

SQL is a wonderful language for describing what data you want and a genuinely bad one for describing repetitive structure. Writing the same case when block for twelve country codes, or the same aggregation for every month of the year, means either a lot of copy-pasting or accepting that your SQL file is going to be six hundred lines long. Jinja is what dbt uses to escape that tradeoff — it’s a templating language that runs before your SQL does, generating the final query text dynamically.

Two Kinds of Curly Braces

dbt’s Jinja syntax uses two distinct delimiter styles, and knowing which is which is the first thing to get comfortable with:

{{ this_is_an_expression }}
{% this_is_a_statement %}

Expressions ({{ }}) output a value directly into the compiled SQL — this is what ref() and source() use. Statements ({% %}) control flow — loops, conditionals, variable assignment — and don’t themselves produce output.

{% set status_list = ['completed', 'shipped', 'delivered'] %}

select *
from {{ ref('stg_orders') }}
where status in (
  {% for status in status_list %}
    '{{ status }}'{% if not loop.last %},{% endif %}
  {% endfor %}
)

This compiles to plain SQL before it ever touches the warehouse:

select *
from analytics.stg_orders
where status in (
    'completed',
    'shipped',
    'delivered'
)

The warehouse never sees a single curly brace — by the time your query runs, it’s ordinary SQL. Jinja is entirely a compile-time tool.

Conditionals: Building Environment-Aware SQL

{% if %} blocks let a single model behave differently depending on context — most commonly, whether it’s running incrementally or from scratch.

select
  order_id,
  order_date,
  amount
from {{ source('raw', 'orders') }}

{% if is_incremental() %}
  where order_date > (select max(order_date) from {{ this }})
{% endif %}

On a full refresh, the if block is skipped entirely and every row is processed. On an incremental run, the where clause is included, filtering to only new rows. This single model file handles both cases without any duplicated logic — the pattern that makes dbt’s incremental materializations practical.

Loops: Eliminating Repetitive SQL

A {% for %} loop is the most common way teams eliminate copy-pasted SQL blocks. A frequent real-world case: pivoting a column’s distinct values into separate output columns.

{% set payment_methods = ['credit_card', 'paypal', 'bank_transfer', 'crypto'] %}

select
  order_id,
  {% for method in payment_methods %}
    sum(case when payment_method = '{{ method }}' then amount else 0 end) as {{ method }}_total
    {% if not loop.last %},{% endif %}
  {% endfor %}
from {{ ref('stg_payments') }}
group by order_id

Adding a fifth payment method later means adding one string to the list, not hand-writing another case when line and remembering to update every place it’s used.

Whitespace Control

Jinja loops and conditionals can leave behind extra blank lines and awkward indentation in the compiled SQL. The - modifier trims whitespace around a tag:

select
  {%- for col in ['a', 'b', 'c'] %}
  {{ col }}{% if not loop.last %},{% endif %}
  {%- endfor %}
from my_table

This is purely cosmetic — the compiled SQL runs identically either way — but readable compiled SQL matters enormously when you’re debugging a model by looking at target/compiled/ output, which is the first place to look when a Jinja-heavy model produces unexpected results.

Variables Inside Jinja Blocks

Jinja’s {% set %} creates a variable scoped to the current model’s compilation, useful for anything you’d otherwise repeat multiple times in one file.

{% set start_date = "2024-01-01" %}

select *
from {{ ref('stg_orders') }}
where order_date >= '{{ start_date }}'

union all

select *
from {{ ref('stg_returns') }}
where return_date >= '{{ start_date }}'

For values that need to change across environments or runs — not just within a single model — dbt’s project-level var() function is the right tool instead, covered in Variables (var()).

Jinja Comments: Documenting Intent Without Affecting Output

Jinja supports its own comment syntax, distinct from SQL’s -- comments, specifically for annotating templating logic without it appearing anywhere in the compiled output.

{# This loop generates one summed column per payment method.
   Add new methods to the payment_methods list above, not here. #}
{% for method in payment_methods %}
  sum(case when payment_method = '{{ method }}' then amount else 0 end) as {{ method }}_total
{% endfor %}

This matters because a regular SQL -- comment placed inside a Jinja block can end up in an unexpected position once the loop renders multiple times, or can interact confusingly with whitespace control — a Jinja comment is always stripped entirely during compilation, regardless of where it sits relative to loops and conditionals, making it the safer choice for documenting templating logic specifically.

Inspecting What Jinja Actually Produces

Every model’s fully-rendered SQL — with all Jinja resolved — is written to the target/compiled/ directory after a run. This is the single most useful debugging habit for anyone writing non-trivial Jinja:

dbt compile --select customer_orders
cat target/compiled/my_project/models/core/customer_orders.sql

When a loop produces a trailing comma or a conditional doesn’t behave the way you expected, reading the compiled output tells you exactly what went wrong, without needing to run anything against the warehouse.

Where Jinja Logic Belongs vs. Where It Doesn’t

A model with three nested loops and five conditionals is usually a sign that logic belongs in a macro instead — reusable Jinja functions that can be called from multiple models, covered in dbt Macros. Jinja directly inline in a model is best kept to genuinely model-specific logic; anything reused across more than one or two models should graduate to a macro.

Summary

Jinja is what separates dbt from “SQL scripts with extra steps.” Once you’re comfortable generating SQL dynamically instead of writing every variation by hand, entire categories of copy-paste maintenance burden disappear from a project.