Why Jinja makes SQL dynamic in dbt - Visual Breakdown

Concept Flow - Why Jinja makes SQL dynamic

Start: Static SQL Template

↓

Insert Jinja Tags {{ }} or {% %}

↓

dbt Runs Jinja Engine

↓

Jinja Processes Variables & Logic

↓

Generate Final SQL with Values

↓

Execute Dynamic SQL in Database

Jinja lets you add variables and logic inside SQL templates. When dbt runs, Jinja replaces these with real values, creating dynamic SQL that changes based on inputs.

Execution Sample

dbt

SELECT * FROM {{ source_table }}
WHERE date >= '{{ start_date }}'
{% if filter_active %}
AND status = 'active'
{% endif %}

This SQL template uses Jinja to insert table name, start date, and optionally filter by active status.

Execution Table

Step	Jinja Tag	Input Value	Action	Output SQL Snippet
1	{{ source_table }}	users	Replace variable with 'users'	SELECT * FROM users
2	{{ start_date }}	2024-01-01	Replace variable with '2024-01-01'	WHERE date >= '2024-01-01'
3	{% if filter_active %}	true	Condition true, include block	AND status = 'active'
4	End if	-	Close conditional block

💡 All Jinja tags processed, final SQL ready for execution

Variable Tracker

Variable	Initial	After Step 1	After Step 2	After Step 3	Final
source_table	undefined	users	users	users	users
start_date	undefined	undefined	2024-01-01	2024-01-01	2024-01-01
filter_active	undefined	undefined	undefined	true	true

Key Moments - 2 Insights

Why does the SQL change after running Jinja?

Because Jinja replaces variables and evaluates conditions (see execution_table steps 1-3), turning placeholders into actual values.

What happens if filter_active is false?

The conditional block (step 3) is skipped, so the 'AND status = 'active'' line is not added to the SQL.

Visual Quiz - 3 Questions

Look at the execution table, what is the output SQL snippet after step 2?

A AND status = 'active'

B SELECT * FROM users

C WHERE date >= '2024-01-01'

D WHERE date >= 'users'

💡 Hint

Check the 'Output SQL Snippet' column for step 2 in the execution_table.

At which step does Jinja decide whether to include the 'AND status = 'active'' line?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Action' column in execution_table for the conditional block.

If filter_active was false, how would the final SQL change?

A The 'AND status = 'active'' line would be missing

B The source_table would change

C The start_date would be removed

D Nothing would change

💡 Hint

Refer to key_moments explanation about conditional blocks and execution_table step 3.

Concept Snapshot

Jinja lets you write SQL templates with placeholders and logic.
When dbt runs, Jinja replaces these with real values.
This makes SQL dynamic and adaptable.
Use {{ variable }} for values and {% if %} for conditions.
Final SQL is generated before running in the database.

Full Transcript

Jinja is a tool that helps make SQL dynamic by allowing you to put variables and logic inside your SQL code. When you run dbt, it processes these Jinja tags, replacing variables like {{ source_table }} with actual names like 'users'. It also checks conditions like {% if filter_active %} to decide if parts of the SQL should be included. This way, the SQL changes based on inputs, making it flexible and reusable. The execution table shows each step where Jinja replaces or evaluates parts of the SQL template, resulting in the final SQL that runs in the database.