Introduction to Jinja

What is Jinja?

Jinja is a templating language for Python that lets us write dynamic, reusable code. In dbt, we can leverage Jinja to add logic, variables, and functions to our SQL, turning static queries into flexible, maintainable transformations.

Why Use Jinja in dbt?

1. DRY Principle (Don't Repeat Yourself)

Instead of copying the same SQL across multiple models:

-- Without Jinja: Repeating the same logic everywhere
SELECT 
    order_id,
    CASE 
        WHEN status = 'completed' THEN 'finished'
        WHEN status = 'pending' THEN 'in_progress'
        ELSE 'unknown'
    END as order_status
FROM orders

With Jinja, you can create a macro and reuse it:

-- With Jinja: Define once, use everywhere
{% macro standardize_status(column_name) %}
    CASE 
        WHEN {{ column_name }} = 'completed' THEN 'finished'
        WHEN {{ column_name }} = 'pending' THEN 'in_progress'
        ELSE 'unknown'
    END
{% endmacro %}

-- Use it in any model
SELECT 
    order_id,
    {{ standardize_status('status') }} as order_status
FROM orders

2. Dynamic SQL Generation

Pivoting data in SQL can often be tedious, especially when having to write out multiple column names. We can leverage Jinja to write code for us.

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

SELECT 
    customer_id,
    {% for method in payment_methods %}
    SUM(CASE WHEN payment_method = '{{ method }}' THEN amount ELSE 0 END) as {{ method }}_total
    {{ "," if not loop.last }}
    {% endfor %}
FROM payments
GROUP BY customer_id

This generates clean SQL without manual copying and pasting.

Real-World Example: Union Multiple Tables

One of the most powerful use cases is dynamically unioning tables:

{% set tables = ['events_2023', 'events_2024', 'events_2025'] %}

{% for table in tables %}
    SELECT 
        *,
        '{{ table }}' as source_table
    FROM {{ source('raw', table) }}
    {% if not loop.last %}
    UNION ALL
    {% endif %}
{% endfor %}

This compiles to:

SELECT *, 'events_2023' as source_table FROM raw.events_2023
UNION ALL
SELECT *, 'events_2024' as source_table FROM raw.events_2024
UNION ALL
SELECT *, 'events_2025' as source_table FROM raw.events_2025

Not only does this save you precious time, but it also simplifies updating the code in the future. If, for example, we obtain another table in 2026, we can simply append the new table name to our table array that we set at the start of the Jinja script.

3. Environment-Specific Logic

Run different logic in dev vs. production:

SELECT *
FROM {{ ref('orders') }}
{% if target.name == 'prod' %}
WHERE created_at >= DATEADD(year, -2, CURRENT_DATE)
{% else %}
-- In dev, just use last 30 days for faster queries
WHERE created_at >= DATEADD(day, -30, CURRENT_DATE)
{% endif %}

Essential Jinja Syntax for dbt

1. Variables: {{ }}

Double curly braces output values:

SELECT * FROM {{ ref('customers') }}
-- Compiles to: SELECT * FROM analytics.customers

2. Logic: {% %}

Percentage signs contain control structures:

{% if var('include_deleted', false) %}
    SELECT * FROM customers  -- includes deleted records
{% else %}
    SELECT * FROM customers WHERE deleted_at IS NULL
{% endif %}

3. Comments: {# #}

Jinja comments won't appear in compiled SQL:

{# TODO: Add customer segmentation logic here #}
SELECT * FROM customers

dbt-Specific Jinja Features

dbt extends Jinja with special functions:

ref() - Reference Other Models

SELECT * FROM {{ ref('stg_customers') }}

Creates model dependency and enables us to visualise the lineage in our DAG.

source() - Reference Raw Tables

SELECT * FROM {{ source('postgres', 'users') }}

Documents data lineage from source systems.

var() - Use Project Variables

-- In dbt_project.yml
vars:
  start_date: '2024-01-01'

-- In your model
WHERE created_at >= '{{ var("start_date") }}'

Best Practices

1. Keep It Simple: Jinja should make SQL clearer to understand. If a macro is too complex, consider breaking it down.
2. Use Macros for Repeated Logic: If you write the same SQL transformation several times, consider building a macro.
3. Test Your Compiled SQL: Run dbt compile to see what SQL code your Jinja actually generates. This is a very useful means of debugging.