Jinja & Macros | dbt Course

🤔 What is Jinja?

Imagine you're writing a birthday card to 100 friends. Instead of writing each card by hand — "Dear Alice, Happy Birthday!", "Dear Bob, Happy Birthday!", "Dear Charlie..." — you create a template:

"Dear {name}, Happy Birthday! You're turning {age}!"

Then you let a machine fill in each name and age automatically. That's Jinja — a templating language that lets you write dynamic SQL. Instead of writing the same SQL 20 times with tiny changes, you write it once and let Jinja do the repetitive work.

Why Does dbt Use Jinja?

Plain SQL is static — what you write is exactly what runs. But real-world data problems need dynamic behavior:

❌ Plain SQL Problems

Copy-paste the same logic 20 times. Change one thing? Update all 20 files. Want different behavior in dev vs prod? Maintain two separate queries.

✅ Jinja + SQL Solutions

Write logic once, reuse everywhere. One change updates everything. Dev vs prod? One if statement handles both automatically.

The Three Jinja Delimiters

Jinja uses three special bracket styles. Think of them as three different types of instructions:

Syntax	Name	What It Does	Analogy
`{{ }}`	Expressions	Outputs a value (prints something)	The blank in a Mad Libs: "I ate a ___"
`{% %}`	Statements	Logic — if/else, for loops, macros	The instructions: "If rainy, bring umbrella"
`{# #}`	Comments	Notes that don't appear in output	Sticky notes to yourself: "TODO: fix this"

How dbt Compiles Jinja

When you run dbt run, dbt takes your Jinja+SQL file and compiles it into pure SQL before sending it to your database. Your database never sees the Jinja — it only sees clean SQL.

📝

You Write
Jinja + SQL

→

⚙️

dbt
Compiles

→

📄

Pure SQL
Generated

→

🗄️

Database
Executes

What you write (Jinja + SQL)

SELECT
    customer_id,
    first_name,
    last_name
FROM
    {{ ref('stg_customers') }}
WHERE
    status = 'active'

What dbt compiles it to (pure SQL sent to database)

SELECT
    customer_id,
    first_name,
    last_name
FROM
    analytics.staging.stg_customers
WHERE
    status = 'active'

You can always see the compiled SQL by running dbt compile or checking the target/compiled/ folder. This is incredibly useful for debugging — if your Jinja isn't producing the SQL you expect, look at the compiled output!

🎯 Variables & Filters

Variables are like blanks in a Mad Libs game. You leave a blank: "The ___ jumped over the ___" and someone fills in the words. In dbt, you define variables once and use them everywhere.

Filters are like autocorrect — they automatically clean up or transform text. "bob" becomes "BOB" with the upper filter. No manual work needed!

Project Variables with `var()`

Define variables in your dbt_project.yml and use them across your entire project:

dbt_project.yml

name: 'my_project'
version: '1.0.0'

vars:
  start_date: '2023-01-01'
  currency: 'USD'
  enable_debug: false

models/marts/fct_orders.sql — Using variables

SELECT
    order_id,
    customer_id,
    amount,
    order_date
FROM
    {{ ref('stg_orders') }}
WHERE
    order_date >= '{{ var('start_date') }}'

{# Compiles to: WHERE order_date >= '2023-01-01' #}

You can also override variables from the command line:

Terminal — Override a variable at runtime

# Use a different start date without changing any files
$ dbt run --vars '{"start_date": "2024-06-01"}'

# Now the query uses 2024-06-01 instead of 2023-01-01

Jinja Filters

Filters transform values using the pipe | symbol. They're chained after a value to modify it:

Filter	What It Does	Example	Result
`upper`	UPPERCASE	`{{ "hello" \| upper }}`	`HELLO`
`lower`	lowercase	`{{ "HELLO" \| lower }}`	`hello`
`trim`	Remove whitespace	`{{ " hi " \| trim }}`	`hi`
`replace`	Swap text	`{{ "cat" \| replace("c","b") }}`	`bat`
`default`	Fallback value	`{{ undefined_var \| default("N/A") }}`	`N/A`
`list \| join`	Combine list items	`{{ ["a","b","c"] \| join(", ") }}`	`a, b, c`

Think of filters like a factory assembly line. A raw material (your value) goes in one end, passes through machines (filters) that shape, polish, and transform it, and a finished product comes out the other end. You can chain multiple filters: {{ " hello " | trim | upper }} → HELLO

🔀 if/else Logic

It's like a recipe that says: "If you're cooking for 2 people, use 1 cup of rice. If you're cooking for 10 people, use 5 cups." The recipe adapts based on the situation. Jinja's if/else lets your SQL adapt based on the environment, variables, or any condition you want.

The Basic Pattern

Jinja if/else syntax

{% if condition %}
    -- SQL when condition is true
{% else %}
    -- SQL when condition is false
{% endif %}

Use Case: Dev vs Prod

This is the #1 most common use of Jinja if/else in dbt. In development, you don't want to process millions of rows — it's slow and expensive. So you limit the data:

models/marts/fct_orders.sql

SELECT
    order_id,
    customer_id,
    amount,
    order_date
FROM
    {{ ref('stg_orders') }}

{% if target.name == 'dev' %}
WHERE order_date >= DATEADD('day', -30, CURRENT_DATE)
{# In dev: only last 30 days — fast! #}
{% endif %}

{# In prod: no WHERE clause — process ALL data #}

🛠️ In Dev (target.name = 'dev')

SELECT ... FROM stg_orders
WHERE order_date >= DATEADD('day', -30, CURRENT_DATE)

Only 30 days of data → runs in seconds

🚀 In Prod (target.name = 'prod')

SELECT ... FROM stg_orders

All data → complete and accurate

Use Case: Conditional Columns

models/marts/dim_customers.sql

SELECT
    customer_id,
    first_name,
    last_name,
    email

    {% if var('include_pii', false) %}
    , phone_number
    , home_address
    {# Only include sensitive data if explicitly enabled #}
    {% endif %}

FROM
    {{ ref('stg_customers') }}

The target object gives you information about the current environment. target.name is the profile target (dev, prod, staging). target.schema is the database schema. target.database is the database name. Use these to make your models environment-aware!

🔄 for Loops

Instead of writing SUM(jan_sales), SUM(feb_sales), SUM(mar_sales), ... SUM(dec_sales) by hand — all 12 lines — you write a loop that does it for all 12 months automatically. It's like telling a robot: "Stamp every envelope in this pile" instead of stamping each one yourself.

The Basic Pattern

Jinja for loop syntax

{% for item in list %}
    -- SQL that repeats for each item
    {{ item }}
{% endfor %}

Example: Generating Monthly Columns

models/marts/fct_monthly_revenue.sql — What you write

{% set months = [
    'jan', 'feb', 'mar', 'apr', 'may', 'jun',
    'jul', 'aug', 'sep', 'oct', 'nov', 'dec'
] %}

SELECT
    customer_id,
    {% for month in months %}
    SUM({{ month }}_sales) AS total_{{ month }}_sales{% if not loop.last %},{% endif %}
    {% endfor %}
FROM
    {{ ref('stg_sales') }}
GROUP BY customer_id

Compiled SQL — What the database sees

SELECT
    customer_id,
    SUM(jan_sales) AS total_jan_sales,
    SUM(feb_sales) AS total_feb_sales,
    SUM(mar_sales) AS total_mar_sales,
    SUM(apr_sales) AS total_apr_sales,
    SUM(may_sales) AS total_may_sales,
    SUM(jun_sales) AS total_jun_sales,
    SUM(jul_sales) AS total_jul_sales,
    SUM(aug_sales) AS total_aug_sales,
    SUM(sep_sales) AS total_sep_sales,
    SUM(oct_sales) AS total_oct_sales,
    SUM(nov_sales) AS total_nov_sales,
    SUM(dec_sales) AS total_dec_sales
FROM
    analytics.staging.stg_sales
GROUP BY customer_id

🔑 The loop Object

Inside a for loop, Jinja gives you a special loop variable:

loop.index — Current iteration (1, 2, 3...)

loop.first — True on the first iteration

loop.last — True on the last iteration (perfect for trailing commas!)

loop.length — Total number of items

Example: UNION ALL Multiple Sources

models/staging/stg_all_payments.sql

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

{% for method in payment_methods %}

SELECT
    order_id,
    amount,
    '{{ method }}' AS payment_method
FROM
    {{ ref('stg_' ~ method ~ '_payments') }}

{% if not loop.last %} UNION ALL {% endif %}

{% endfor %}

Adding a 5th payment method? Without Jinja, you'd copy-paste a whole SELECT block and manually add UNION ALL. With Jinja, you just add 'venmo' to the list. One word change instead of 6 lines. Now imagine doing this with 20 payment methods — Jinja saves you from writing 120+ lines of repetitive SQL!

🛠️ Writing Your Own Macros

A macro is like a cooking shortcut. Instead of writing "preheat oven to 350°F, grease the pan, line with parchment paper" every time you bake, you just say "prep_oven()" and it does all three steps automatically.

In dbt, a macro is a reusable Jinja function. You define it once, and call it from any model. It's the ultimate DRY (Don't Repeat Yourself) tool.

Where Do Macros Live?

📁 my_dbt_project/ 📁 models/ 📄 fct_orders.sql 📄 dim_customers.sql 📁 macros/ ← Your macros go here! 📄 cents_to_dollars.sql 📄 generate_schema_name.sql 📄 limit_data_in_dev.sql 📄 dbt_project.yml

Example 1: cents_to_dollars

Many payment systems store amounts in cents (e.g., $49.99 = 4999). You constantly need to convert:

macros/cents_to_dollars.sql — Define the macro

{% macro cents_to_dollars(column_name, decimal_places=2) %}
    ROUND( {{ column_name }} / 100.0, {{ decimal_places }} )
{% endmacro %}

models/marts/fct_orders.sql — Call the macro

SELECT
    order_id,
    customer_id,
    {{ cents_to_dollars('amount_cents') }} AS amount_usd,
    {{ cents_to_dollars('tax_cents') }} AS tax_usd,
    {{ cents_to_dollars('shipping_cents') }} AS shipping_usd
FROM
    {{ ref('stg_orders') }}

Compiled SQL — What the database sees

SELECT
    order_id,
    customer_id,
    ROUND( amount_cents / 100.0, 2 ) AS amount_usd,
    ROUND( tax_cents / 100.0, 2 ) AS tax_usd,
    ROUND( shipping_cents / 100.0, 2 ) AS shipping_usd
FROM
    analytics.staging.stg_orders

Example 2: limit_data_in_dev

Remember the if/else for dev vs prod? Let's turn that into a reusable macro:

macros/limit_data_in_dev.sql

{% macro limit_data_in_dev(column_name, dev_days=3) %}

{% if target.name == 'dev' %}
WHERE {{ column_name }} >= DATEADD('day', -{{ dev_days }}, CURRENT_DATE)
{% endif %}

{% endmacro %}

models/marts/fct_orders.sql — Now every model can use it!

SELECT
    order_id,
    customer_id,
    amount,
    order_date
FROM
    {{ ref('stg_orders') }}

{{ limit_data_in_dev('order_date') }}

Without macros vs. with macros:

❌ Copy-paste the same WHERE clause into 30 models → change the logic? Update 30 files

✅ One macro, called in 30 models → change the logic? Update 1 file

That's the power of DRY (Don't Repeat Yourself)!

Example 3: generate_schema_name (Override)

dbt has a built-in macro called generate_schema_name that decides which schema a model is built in. You can override it:

macros/generate_schema_name.sql

{% macro generate_schema_name(custom_schema_name, node) %}

    {% if custom_schema_name is none %}
        {{ target.schema }}
    {% elif target.name == 'prod' %}
        {{ custom_schema_name | trim }}
    {% else %}
        {{ target.schema }}_{{ custom_schema_name | trim }}
    {% endif %}

{% endmacro %}

{# In prod: models go to the exact schema name (e.g., "marts") #}
{# In dev: models go to "dev_john_marts" — isolated per developer #}

Start small! Your first macro should solve a real pain point — something you've copy-pasted more than 3 times. The cents_to_dollars and limit_data_in_dev macros are perfect first macros because they're simple, useful, and easy to understand.

📦 Built-in dbt Macros

dbt comes with a toolbox full of pre-built macros — like a kitchen that already has a blender, toaster, and microwave. You don't have to build these appliances yourself; they're ready to use on day one!

You've already been using some of these without realizing it. ref() and source() are macros!

The Most Useful Built-in Macros

Macro	What It Does	Example
`ref()`	References another model (builds the DAG)	`{{ ref('stg_orders') }}`
`source()`	References a raw source table	`{{ source('raw_shop', 'orders') }}`
`config()`	Sets model configuration (materialization, etc.)	`{{ config(materialized='table') }}`
`var()`	Reads a project variable	`{{ var('start_date') }}`
`run_query()`	Executes SQL and returns results at compile time	`{% set results = run_query(sql) %}`
`log()`	Prints a message to the dbt log	`{{ log("Building model...", info=True) }}`
`exceptions.raise_compiler_error()`	Stops compilation with a custom error	`{{ exceptions.raise_compiler_error("Bad config!") }}`
`adapter.dispatch()`	Calls different SQL per database (Snowflake vs Postgres)	`{{ adapter.dispatch('my_macro')() }}`

run_query() — SQL at Compile Time

This is a powerful macro that lets you query the database while dbt is compiling your models. Use it to dynamically generate SQL based on actual data:

models/marts/fct_pivoted_payments.sql

{# Get all distinct payment methods from the database #}
{% set payment_query %}
    SELECT DISTINCT payment_method
    FROM {{ ref('stg_payments') }}
    ORDER BY 1
{% endset %}

{% set results = run_query(payment_query) %}
{% set methods = results.columns[0].values() %}

SELECT
    order_id,
    {% for method in methods %}
    SUM(CASE WHEN payment_method = '{{ method }}' THEN amount ELSE 0 END) AS {{ method }}_amount{% if not loop.last %},{% endif %}
    {% endfor %}
FROM
    {{ ref('stg_payments') }}
GROUP BY order_id

Why is this amazing? If someone adds a new payment method (say "crypto"), you don't need to change any code. The next time dbt runs, it queries the database, finds "crypto" in the list, and automatically generates a crypto_amount column. The SQL writes itself!

🌍 Jinja + SQL: Real-World Examples

Let's see how Jinja transforms real, messy, copy-paste-heavy SQL into clean, maintainable code. These are patterns you'll use every single week as a dbt developer.

Pattern 1: DRY Column Transformations

❌ Before (Repetitive)

SELECT
  LOWER(TRIM(first_name)) AS first_name,
  LOWER(TRIM(last_name)) AS last_name,
  LOWER(TRIM(email)) AS email,
  LOWER(TRIM(city)) AS city,
  LOWER(TRIM(state)) AS state
FROM raw_customers

5 lines of nearly identical code

✅ After (DRY with Jinja)

{% set cols = ['first_name',
  'last_name', 'email',
  'city', 'state'] %}

SELECT
  {% for col in cols %}
  LOWER(TRIM({{ col }})) AS {{ col }}
  {% if not loop.last %},{% endif %}
  {% endfor %}
FROM raw_customers

Add a column? Just add to the list!

Pattern 2: Environment-Aware Queries

macros/get_database.sql

{% macro get_database() %}
    {% if target.name == 'prod' %}
        PRODUCTION_DB
    {% elif target.name == 'staging' %}
        STAGING_DB
    {% else %}
        DEV_DB
    {% endif %}
{% endmacro %}

Pattern 3: Grant Permissions Macro

macros/grant_select.sql

{% macro grant_select(schema, role) %}

    {% set grant_sql %}
        GRANT SELECT ON ALL TABLES IN SCHEMA {{ schema }}
        TO ROLE {{ role }};
    {% endset %}

    {{ log("Granting SELECT to " ~ role, info=True) }}
    {% do run_query(grant_sql) %}

{% endmacro %}

80%

Less Copy-Paste

Macros eliminate most repetitive SQL

1

Place to Update

Change logic once, it updates everywhere

∞

Reusability

Use the same macro in every model

⚠️ Common Jinja Mistakes

Even experienced dbt developers trip over these. Learning the common pitfalls now will save you hours of debugging later. Think of this as the "don't touch the hot stove" section — learn from others' burns!

🐛 Mistake #1: Forgetting {% endif %} or {% endfor %}

Every {% if %} needs an {% endif %}. Every {% for %} needs an {% endfor %}. Forget one and dbt gives you a cryptic error like "unexpected end of template".

❌ {% if target.name == 'dev' %}
WHERE date > '2024-01-01'
{# Missing {% endif %}! #}

🐛 Mistake #2: Whitespace Issues

Jinja blocks can add unwanted blank lines to your compiled SQL. Use the dash syntax {%- -%} to trim whitespace:

{% for col in columns %} ← Adds blank lines
{%- for col in columns -%} ← Clean output, no extra whitespace

🐛 Mistake #3: Trailing Commas in Loops

When generating comma-separated lists, the last item shouldn't have a trailing comma. Always use loop.last:

❌ {{ col }}, ← Last item gets a trailing comma → SQL error!
✅ {{ col }}{% if not loop.last %},{% endif %} ← No trailing comma

🐛 Mistake #4: Using Jinja in .yml Where It's Not Supported

Jinja works in .sql files and some parts of .yml files (like descriptions and test arguments). But it does not work in all YAML fields. If your Jinja isn't rendering, check whether that YAML field supports it.

✅ description: "{{ doc('my_doc') }}" ← Works!
✅ to: ref('stg_customers') ← Works in test args!
❌ name: "{{ var('model_name') }}" ← Does NOT work

Debugging tip: When your Jinja isn't working, run dbt compile and check the output in target/compiled/. This shows you the exact SQL that dbt generated. If the Jinja didn't render, you'll see the raw {{ }} tags still there — that's your clue!

🧠 Quick Quiz — Test Your Understanding!

Let's see if you've unlocked the Jinja superpower. Click the answer you think is correct:

Question 1: What does `{{ }}` do in Jinja?

A) Defines a for loop

B) Outputs (prints) a value into the compiled SQL

C) Creates a comment that won't appear in the output

D) Defines a macro function

Question 2: What is a dbt macro?

A) A type of database table that stores temporary data

B) A YAML configuration file for model settings

C) A reusable Jinja function defined in the macros/ folder

D) A built-in SQL function provided by your database

Question 3: How do you avoid a trailing comma in a Jinja for loop?

A) Use {% break %} on the last iteration

B) dbt automatically removes trailing commas

C) Use {% if not loop.last %},{% endif %} to conditionally add the comma

D) Put the comma before the column name instead of after

Can you explain to a colleague what Jinja is and why dbt uses it? Can you write a simple macro and call it from a model? Can you use a for loop to generate dynamic columns? If yes — you've unlocked the Jinja superpower!

Jinja basics — {{ }} for expressions, {% %} for logic, {# #} for comments
Variables — var('name') reads project variables from dbt_project.yml
if/else — Conditional SQL, especially for dev vs prod behavior
for loops — Generate repetitive SQL dynamically (columns, UNION ALL, etc.)
Macros — Reusable Jinja functions in the macros/ folder
Built-in macros — ref(), source(), config(), var()
dbt compile — See the compiled SQL to debug Jinja issues

Jinja filters — | upper, | lower, | trim, | replace, | default
run_query() — Execute SQL at compile time for dynamic model generation
adapter.dispatch() — Write database-agnostic macros
generate_schema_name — Override dbt's default schema naming
Whitespace control — {%- -%} for clean compiled output

🧙 Jinja & Macros — SQL That Writes SQL

🤔 What is Jinja?

Why Does dbt Use Jinja?

❌ Plain SQL Problems

✅ Jinja + SQL Solutions

The Three Jinja Delimiters

How dbt Compiles Jinja

🎯 Variables & Filters

Project Variables with var()

Jinja Filters

🔀 if/else Logic

The Basic Pattern

Use Case: Dev vs Prod

🛠️ In Dev (target.name = 'dev')

🚀 In Prod (target.name = 'prod')

Use Case: Conditional Columns

🔄 for Loops

The Basic Pattern

Example: Generating Monthly Columns

Example: UNION ALL Multiple Sources

🛠️ Writing Your Own Macros

Where Do Macros Live?

Example 1: cents_to_dollars

Example 2: limit_data_in_dev

Example 3: generate_schema_name (Override)

📦 Built-in dbt Macros

The Most Useful Built-in Macros

run_query() — SQL at Compile Time

🌍 Jinja + SQL: Real-World Examples

Pattern 1: DRY Column Transformations

❌ Before (Repetitive)

✅ After (DRY with Jinja)

Pattern 2: Environment-Aware Queries

Pattern 3: Grant Permissions Macro

⚠️ Common Jinja Mistakes

🧠 Quick Quiz — Test Your Understanding!

Question 1: What does {{ }} do in Jinja?

Question 2: What is a dbt macro?

Question 3: How do you avoid a trailing comma in a Jinja for loop?

Project Variables with `var()`

Question 1: What does `{{ }}` do in Jinja?