Jinja Is What Makes dbt Programmable SQL
dbt compiles every model through the Jinja templating engine before sending it to the warehouse, which means anything wrapped in {{ }} (expressions), {% %} (statements like loops and conditionals), or {# #} (comments) gets evaluated and replaced with plain SQL text prior to execution. This is what elevates dbt from 'just SQL files' to a real templating layer — you can write loops, conditionals, and variables directly inside your SQL, and the warehouse never even sees the Jinja syntax, only the final rendered output.
Cricket analogy: This is like a coach writing out a general tactical plan with placeholders such as 'opening bowler' that gets filled in with the actual player name only once the toss and pitch conditions are known — the players only ever see the final, specific instructions.
-- Jinja expression, statement, and comment
select
order_id,
{# this comment is stripped from compiled SQL #}
{{ "'completed'" if var('include_status', true) else "'pending'" }} as status_filter
from {{ ref('stg_orders') }}
{% if target.name == 'prod' %}
where order_date >= '2020-01-01'
{% endif %}Control Flow: Loops and Conditionals
The {% for %} loop is commonly used to generate repetitive SQL, such as pivoting a column's distinct values into separate CASE WHEN expressions or summing several columns without typing each one out by hand, while {% if %} lets you branch logic based on variables, the target environment, or Jinja functions. Because this all happens at compile time, you can inspect exactly what SQL gets generated by running dbt compile and reading the file dbt writes to the target/compiled directory — there's no runtime magic left once that step is done.
Cricket analogy: This is like a scoring analyst writing one formula that loops over every player in the squad to generate each one's strike rate automatically, rather than typing out twenty separate manual calculations by hand.
select
order_id,
{% for status in ['pending', 'shipped', 'completed', 'cancelled'] %}
sum(case when status = '{{ status }}' then 1 else 0 end) as {{ status }}_count{% if not loop.last %},{% endif %}
{% endfor %}
from {{ ref('stg_orders') }}
group by 1Run dbt compile and inspect target/compiled/<project>/models/... to see the exact raw SQL your Jinja produced — this is the single best debugging technique when a model's rendered SQL doesn't match your expectations.
Variables and Environment Awareness
The var() function reads values passed in from dbt_project.yml or the command line via --vars, letting a single model behave differently across runs without editing code, while built-in objects like target.name (dev, staging, prod) and this (the current model's own relation) give templates awareness of their execution context. This environment awareness is what lets teams write one model that safely limits data volume in dev for fast iteration while processing the full history in production, all from the same source file.
Cricket analogy: This is like a training facility that runs shorter, lower-intensity net sessions during pre-season practice but the full-intensity, full-duration format during an actual international match — same drills, different settings based on context.
Be careful with target.name-based branching — if you hardcode logic like {% if target.name == 'prod' %}, you must keep target names consistent across every environment's profiles.yml, or the condition will silently fail to match in a renamed or new environment.
- Jinja delimiters: {{ }} for expressions, {% %} for statements, {# #} for comments — all stripped before SQL execution.
- {% for %} loops generate repetitive SQL like column pivots without manual duplication.
- {% if %} enables conditional logic based on variables, target environment, or Jinja functions.
- dbt compile writes the fully rendered SQL to target/compiled/ for debugging.
- var() reads values from dbt_project.yml or --vars, enabling reusable, configurable models.
- target.name and this give templates awareness of the current environment and model.
Practice what you learned
1. Which Jinja delimiter is used for statements like loops and conditionals in dbt?
2. How can you inspect the exact raw SQL that dbt's Jinja produces for a model?
3. What does the var() function retrieve in a dbt model?
4. What does the built-in `target.name` variable typically represent?
Was this page helpful?
You May Also Like
Macros
Learn how dbt macros turn repeated Jinja + SQL patterns into reusable, function-like building blocks, and how community packages like dbt_utils extend them further.
SQL Models Basics
Learn how dbt models turn a plain SQL SELECT statement into a managed table or view, and how to organize them into a clean, layered project structure.
ref() and source() Functions
Learn how dbt's ref() and source() functions build the project's dependency graph and enable environment-safe, portable SQL.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics