Lesson 3 of 3 in this module

3/14 in track

Jinja basics

sql14 min

Overview

{{ ref('stg_orders') }} turns into a table name when dbt compiles. {% if %} is template logic. Jinja does not run in this tab, so you write the compiled SQL.

On this page8 sections

What you will do

Open almost any dbt model file and you will see SQL mixed with curly braces, like FROM {{ ref('stg_orders') }}.

If you paste that directly into a database console, the database halts with an error. Those braces are Jinja templating, and today you learn how they compile into plain database code.

What happens when you mix Jinja templates into SQL?

By the end of this lab, you will look at any templated model and write down the exact SQL it produces.

That compiled SQL is what your warehouse executes, and it is what you type in this lesson editor.

Three boxes left to right: a model file containing from {{ ref('stg_orders') }}, then compiled SQL containing from analytics.stg_orders, then the warehouse running the SELECT.
What compile does to one model file.Source: LakeBench (original diagram)

The left box shows what you author with Jinja.

The middle box shows what dbt compiles with templates removed.

The right box shows the warehouse receiving only plain SQL.

The warehouse never sees Jinja; it receives only compiled SQL.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
JinjaA small templating language. It lets SQL files contain simple logic
TemplateA file with blanks that get filled in before it runs
MacroA reusable snippet, like a function for SQL
ref()Points to another model by name, so dbt works out the table for you
source()Points to a raw table by name
CompileTurn the template into plain SQL that the warehouse can run

Why this skill

Hardcoding schema names and table paths in SQL makes analytics projects fragile and difficult to test.

Why should you avoid hardcoding table names directly?

In production, stg_orders lives under analytics.stg_orders. In development, you need it in your private schema like dev_priya.stg_orders so you do not overwrite production tables.

If every query hardcodes physical schemas, switching environments requires editing dozens of files by hand.

Jinja ref() decouples queries from hardcoded physical schemas.

Query styleHow environment changes workLineage tracking
Hardcoded FROM analytics.ordersManual text replace across filesInvisible to dbt compiler
Dynamic FROM {{ ref('orders') }}Automatic schema resolutionBuilds DAG dependencies automatically

Jinja enables models to run cleanly across local development, staging, and production environments.

When a query fails in the warehouse, the error points to line numbers in the compiled SQL, not your source file.

ref() discovers DAG dependencies and switches schemas automatically across environments.

How the code works

Jinja provides three different delimiters, distinguished by their bracket syntax.

What are the 3 core Jinja markers?

Each marker serves a distinct syntactic purpose during template compilation.

The three Jinja markers.

MarkerNameWhat it doesExample
{{ ... }}ExpressionGets replaced by a value in the SQL text{{ ref('stg_orders') }}
{% ... %}StatementControls the template: if, for, set; leaves no text of its own{% if target.name == 'dev' %}
{# ... #}CommentRemoved completely during compilation{# note to self #}

Expressions are the most common marker. They evaluate Python-like functions and output raw text into SQL.

Statements manage template control flow like conditional branches and loops, leaving no text behind.

Comments wrapped in curly-hash pairs vanish entirely during the compilation phase.

Watch out{{ }} does not run in this editor

If you paste {{ ref('orders') }} into the SQL editor the query fails, because the editor is a plain SQL engine with no Jinja step. Compile it yourself: replace the whole {{ ... }} with the table name, so FROM orders.

What are dbt macros and how do they work?

Writing custom macros turns repetitive SQL expressions into reusable functions across your project.

In dbt, macros live in the macros/ directory and are defined using the macro statement tag.

SQLCustom dbt macro definition
-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
    ROUND(CAST({{ column_name }} AS DOUBLE) / 100.0, 2)
{% endmacro %}

You invoke macros inside model SELECT queries just like built-in database functions.

When dbt compiles the file, the macro expands into standard SQL expressions before running in the warehouse.

Worked examples

Let us examine four concrete examples of Jinja compilation in action.

What happens when compiling a single ref function?

Here is a minimal model file in models/marts/orders_summary.sql:

SQLmodels/marts/orders_summary.sql, as written (illustration, not run here)
SELECT
    order_id,
    order_total
FROM {{ ref('stg_orders') }};

During compilation, dbt substitutes the expression with the resolved warehouse relation name:

SQLWhat the warehouse receives in production (illustration, not run here)
SELECT
    order_id,
    order_total
FROM analytics.stg_orders;

The warehouse executes the compiled SQL without ever seeing the Jinja syntax.

What is the difference between source and ref?

Both functions resolve into physical table names, but dbt treats them differently in the dependency graph.

Jinja expressions and what is left after compile. Schema names depend on target profile settings.

In the model fileAfter compile (typical)It points at
{{ ref('stg_orders') }}analytics.stg_ordersAnother model in this project
{{ source('ecommerce', 'orders') }}raw.ordersA raw table you do not own
{{ config(materialized='table') }}(empty text)A setting for dbt, removed from SQL

ref points to models you author, while source points to external raw tables loaded by data engineers.

How does a Jinja loop generate repetitive SQL columns?

Consider a payments table where finance wants total revenue pivoted per payment method.

Instead of hand-coding repetitive CASE statements, write a Jinja loop:

SQLModel file with a for loop (illustration, not run here)
SELECT
    order_id,
    {% for method in ['card', 'upi', 'netbanking'] %}
    ROUND(SUM(CASE WHEN method = '{{ method }}' THEN amount ELSE 0 END), 2) AS {{ method }}_amount{% if not loop.last %},{% endif %}
    {% endfor %}
FROM {{ ref('stg_payments') }}
GROUP BY order_id;

The compiler loops through each item and produces clean standard SQL:

SQLCompiled SQL (illustration, not run here)
SELECT
    order_id,
    ROUND(SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END), 2) AS card_amount,
    ROUND(SUM(CASE WHEN method = 'upi' THEN amount ELSE 0 END), 2) AS upi_amount,
    ROUND(SUM(CASE WHEN method = 'netbanking' THEN amount ELSE 0 END), 2) AS netbanking_amount
FROM analytics.stg_payments
GROUP BY order_id;

Adding a fourth payment method requires updating one list item without rewriting repetitive SQL logic.

How do you compile a model query by hand in this editor?

The editor accepts only standard SQL, so you perform the compilation step manually.

Suppose the source model reads:

SQLModel file written with ref (illustration, not run here)
SELECT
    order_id,
    order_status,
    order_total
FROM {{ ref('orders') }}
WHERE order_status = 'paid'
LIMIT 5;

Replace the template with the known table name orders:

SQLCompiled by hand, runs in the editor
SELECT
    order_id,
    order_status,
    order_total
FROM orders
WHERE order_status = 'paid'
LIMIT 5;

Output on the seeded orders table: only paid orders are returned.

order_idorder_statusorder_total
ORD-0001paid612.07
ORD-0003paid1104.32
ORD-0005paid261.73
ORD-0006paid431.34
ORD-0007paid396.72
Watch outDo not forget to remove the braces

A very common slip is to change the table name but leave {{ and }} behind, like FROM {{ orders }}. The braces are part of the template syntax, so they must go too. What remains has to be plain SQL.

RememberWhen a model errors, read the compiled file

The warehouse only ever saw the compiled SQL, so its error line numbers point there. Open the copy under target/compiled/, not your source file, and the line numbers will match.

How do you conditionally limit query volume in development?

dbt exposes built-in environment context so you can adjust behavior between development and production runs.

SQLLimiting query volume during local development runs (illustration, not run here)
SELECT *
FROM {{ ref('stg_orders') }}
{% if target.name == 'dev' %}
LIMIT 500
{% endif %};

In development environments, the query processes only 500 rows to conserve compute and reduce build time.

In production, the condition evaluates to false and dbt compiles the query without the limit clause.

How do you debug compilation errors before warehouse execution?

When a Jinja tag is missing or misnamed, dbt halts with a compilation error before running SQL.

  1. Write modelSQL + Jinja tags
  2. dbt compileparses templates locally
  1. Inspect target/view compiled .sql
  2. dbt runexecutes against warehouse

Executing dbt compile verifies syntax and outputs rendered SQL into the target/ directory.

Inspecting target/compiled/ allows engineers to catch malformed SQL before running costly warehouse queries.

How does compilation transform a model from template to warehouse?

Tracing Jinja template compilation
  1. Template: write SQL models containing ref() calls and Jinja macros.

    1. Model .sql
    2. Jinja compiler
  2. Compile: dbt resolves relations, evaluates loops, and strips markers.

    1. Jinja compiler
    2. Compiled SQL
  3. Inspect: rendered SQL sits in target/compiled/ for debugging.

    1. Compiled SQL
    2. target/ directory
  4. Warehouse: database executes clean compiled SQL without templates.

    1. Compiled SQL
    2. Warehouse engine

4 rules to remember about Jinja in dbt:

  1. Expressions {{ }} output values, statements {% %} control logic, and comments {# #} vanish.
  2. ref() dynamically discovers dependencies and resolves target schemas across environments.
  3. source() points to raw landing tables declared in sources.yml.
  4. The warehouse only ever receives compiled SQL; inspect target/ to debug syntax issues.

Common beginner questions

Do I need to learn all of Jinja?

No. Most dbt work uses ref(), source(), config() and the occasional if condition or for loop. Learn those first. When you meet something unfamiliar, read the compiled output to see what it does.

Why not just write the table name directly?

Because the name changes between environments, and because ref() is how dbt discovers which model depends on which. Hard-coding the name hides dependency information from dbt.

Where can I see the compiled SQL in real dbt?

When you run dbt compile or dbt run, dbt writes the compiled version to a folder called target/compiled/ inside your project. Opening that file is the first thing to do when a model errors.

What comes next

You have seen that ref() and source() become table names.

In the next module, What dbt Is, you will explore why teams use dbt instead of manual scripts, and how ref() and source() build the dependency graph that orchestrates your pipeline.

Practice

The exercise gives you a model written with ref() and asks for the compiled SQL with an 8 row limit.

Remember the two parts: turn the whole expression into a table name, and make sure no curly braces are left in the editor.

Going further: let a loop write the repetitive SQL

Jinja is useful when you would otherwise copy and paste the same SQL line with one word changed. A loop over a list generates the lines, and the list becomes the one place to edit. Always read the compiled result: the SQL that runs is what Jinja produces, not what you wrote.

The example renders a template that makes 1 column per payment method, then runs the compiled SQL. Predict the compiled SQL before revealing the output.

PythonTemplate, compiled SQL, result
import duckdb
from jinja2 import Template

template = Template("""
SELECT order_id,
{%- for method in methods %}
       SUM(CASE WHEN method = '{{ method }}' THEN amount ELSE 0 END) AS {{ method }}_amount{{ "," if not loop.last }}
{%- endfor %}
FROM payments
GROUP BY order_id
ORDER BY order_id
""")
sql = template.render(methods=["card", "upi", "cash"]).strip()
print(sql)

con = duckdb.connect()
con.execute("CREATE TABLE payments AS SELECT * FROM (VALUES (1,'card',100),(1,'upi',50),(2,'cash',80)) t(order_id, method, amount)")
print()
for row in con.execute(sql).fetchall():
    print(row)
SELECT order_id,
       SUM(CASE WHEN method = 'card' THEN amount ELSE 0 END) AS card_amount,
       SUM(CASE WHEN method = 'upi' THEN amount ELSE 0 END) AS upi_amount,
       SUM(CASE WHEN method = 'cash' THEN amount ELSE 0 END) AS cash_amount
FROM payments
GROUP BY order_id
ORDER BY order_id

(1, 100, 50, 0)
(2, 0, 0, 80)

The loop wrote three CASE lines. loop.last stops the trailing comma after the final one, which would be a syntax error. Adding a new method means adding one word to the list, not editing SQL in three places.

The cost of this power is readability. When a model fails, open the compiled file (under target/) and debug the SQL, not the template. If a Jinja block takes a minute to understand, a few plain lines of SQL may be the better choice.

Try a new scenario

You must sum amount for each of the years 2024, 2025 and 2026 as separate columns. What goes in the list and what does the loop write?

Open the answer

The list is the 3 years and each pass writes one SUM(CASE ...) column: SUM(CASE WHEN year = 2024 THEN amount END) AS y2024, SUM(CASE WHEN year = 2025 THEN amount END) AS y2025, SUM(CASE WHEN year = 2026 THEN amount END) AS y2026. The separator logic matters: forgetting it produces a trailing comma and a syntax error that only appears in the compiled SQL.

Interview: what is the risk of too much Jinja in SQL models?

Models become hard to read and debug because the SQL that runs is not the SQL you see. Keep Jinja for repetition and configuration, read the compiled SQL when something fails, and prefer plain SQL when a reader would understand it faster.

Close the lesson and explain it

Without scrolling up, explain what a Jinja loop produces, why you read the compiled SQL, and when plain SQL is clearer.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?