How to Use Jinja in dbt: Syntax, Examples, and Tips

In dbt, you use Jinja templating to write dynamic SQL by embedding expressions inside {{ }} and control structures inside {% %}. This lets you create reusable, flexible models and macros that adapt based on variables or logic.

Syntax

Jinja in dbt uses two main delimiters: {{ expression }} for outputting values and {% statement %} for control flow like loops and conditions.

• {{ expression }}: Inserts the result of the expression into the SQL.
• {% statement %}: Runs logic like if, for, or set without outputting directly.

You can use variables, filters, and macros inside these blocks.

jinja

{% set table_name = 'customers' %}

select * from {{ table_name }}

{% if execute %}
  -- This block runs only during compilation
{% endif %}

Example

This example shows how to use Jinja to dynamically select a table name and filter rows based on a variable.

sql

{% set source_table = 'raw_data.sales' %}

select *
from {{ source_table }}
where sale_date >= '{{ var('start_date', '2023-01-01') }}'
order by sale_date desc

Output

select *
from raw_data.sales
where sale_date >= '2023-01-01'
order by sale_date desc

Common Pitfalls

Common mistakes when using Jinja in dbt include:

• Forgetting to use {{ }} for expressions, causing no output.
• Using {% %} when you want to output a value.
• Not escaping quotes inside strings, leading to SQL errors.
• Trying to use Python code unsupported by Jinja.

Always test your compiled SQL with dbt compile to catch errors early.

jinja

{# Wrong: no output because of missing {{ }} #}
{% set table_name = 'customers' %}

select * from table_name

{# Right: use {{ }} to output variable #}
select * from {{ table_name }}

Quick Reference

Jinja Syntax	Purpose	Example
{{ expression }}	Output value or variable	{{ table_name }}
{% if condition %} ... {% endif %}	Conditional logic	{% if execute %} ... {% endif %}
{% for item in list %} ... {% endfor %}	Loop over items	{% for col in columns %}{{ col }}, {% endfor %}
{% set var = value %}	Assign variable	{% set limit = 10 %}
{{ var('name', 'default') }}	Get dbt variable with default	{{ var('start_date', '2023-01-01') }}

Key Takeaways

Use {{ }} to output values and {% %} for control flow in Jinja within dbt.

Set variables with {% set %} to reuse values dynamically in your SQL models.

Always test your Jinja code by running dbt compile to see the generated SQL.

Avoid mixing Python code unsupported by Jinja; stick to Jinja syntax.

Use dbt variables with var() to make your models configurable.