dbt Jinja Basics

Jinja is a templating language built into dbt. It lets you add programming logic — variables, loops, conditionals, and function calls — inside your SQL files. dbt processes Jinja before sending SQL to the warehouse. Understanding Jinja is essential for writing dynamic models and macros.

What Jinja Looks Like in SQL

Jinja uses three types of delimiters:

{{ ... }}    Expression block — evaluates to a value and inserts it into SQL
{% ... %}    Statement block — control logic (if, for, set) — outputs nothing
{# ... #}    Comment block — ignored completely, not sent to warehouse

-- Example with all three types

{# This is a Jinja comment. It does not appear in the compiled SQL. #}

{% set payment_method = 'credit_card' %}

select
    order_id,
    {{ payment_method }}   as payment_type    -- inserts 'credit_card'
from {{ ref('stg_orders') }}
where payment_type = '{{ payment_method }}'

Expression Blocks {{ }}

Expression blocks insert a value into the SQL output. The most common uses are function calls and variable references:

{{ ref('stg_orders') }}            -- inserts table name
{{ source('ecommerce', 'orders') }} -- inserts source table name
{{ this }}                          -- inserts current model's table name
{{ var('start_date') }}             -- inserts a project variable value
{{ env_var('DBT_SCHEMA') }}        -- inserts an environment variable

Statement Blocks {% %}

Statement blocks contain logic but produce no direct SQL output.

Setting Variables

{% set status_list = ['pending', 'completed', 'cancelled'] %}

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

This generates:

select *
from dbt_dev.stg_orders
where status in (
    'pending', 'completed', 'cancelled'
)

Conditional Logic

select
    order_id,
    {% if target.name == 'prod' %}
        amount_dollars
    {% else %}
        -- round to whole dollars in dev for easier reading
        round(amount_dollars) as amount_dollars
    {% endif %}
from {{ ref('stg_orders') }}

target.name is a built-in Jinja variable that tells you the current dbt target (dev, prod, ci). Use it to write SQL that behaves differently per environment.

Loops with for

Loops generate repetitive SQL dynamically. Instead of writing the same pattern twenty times, write it once in a loop:

{% set metrics = ['revenue', 'orders', 'customers', 'avg_order_value'] %}

select
    date_trunc('month', order_date) as month,
    {% for metric in metrics %}
        sum({{ metric }}) as total_{{ metric }}
        {% if not loop.last %},{% endif %}
    {% endfor %}
from {{ ref('fct_orders') }}
group by 1

Compiles to:

select
    date_trunc('month', order_date) as month,
    sum(revenue) as total_revenue,
    sum(orders) as total_orders,
    sum(customers) as total_customers,
    sum(avg_order_value) as total_avg_order_value
from dbt_dev.fct_orders
group by 1

Built-In Jinja Variables in dbt

Variable            Content
-----------------   -------
target.name         Current target name ('dev', 'prod', 'ci')
target.schema       Current target schema name
target.database     Current target database name
target.type         Warehouse type ('snowflake', 'bigquery', etc.)
this                Current model's fully qualified table name
model.name          Current model file name (without .sql)

The is_incremental() Function

A special Jinja function used inside incremental models to conditionally add WHERE clauses:

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

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

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

On the first run, is_incremental() returns False and the WHERE clause is skipped. On subsequent runs it returns True and only new rows are loaded.

Jinja Filters

Jinja filters transform values using the pipe (|) symbol:

{{ 'hello' | upper }}          -- outputs HELLO
{{ ['a', 'b', 'c'] | join(', ') }}  -- outputs a, b, c
{{ my_list | length }}         -- outputs the count of items
{{ value | default('N/A') }}   -- outputs value or 'N/A' if value is undefined

Practical Example: Dynamic WHERE Clause

{% set start_date = var('start_date', '2024-01-01') %}
{% set end_date   = var('end_date',   '2024-12-31') %}

select *
from {{ ref('stg_orders') }}
where order_date between '{{ start_date }}' and '{{ end_date }}'

Run with custom dates:

dbt run --vars '{"start_date": "2025-01-01", "end_date": "2025-03-31"}'

Key Rules for Jinja in dbt

• Use {{ }} to insert values, {% %} for logic, {# #} for comments
• Always use ref() and source() inside {{ }} — never hardcode table names
• Compiled SQL lives in target/compiled/ — inspect it to debug Jinja issues
• Jinja runs at compile time on your computer, not inside the warehouse