01. Jinja Basics
What is Jinja?
Jinja (commonly called Jinja2) is a templating engine for Python used to generate dynamic text files using variables, loops, conditions, and reusable templates.
This topic focuses only on Jinja syntax and dbt context functions such as ref(), source(), var(), and config().
Macros are covered separately in the next topic.
In Otherwords, we can say:
Jinja is a Python-based templating engine that helps generate dynamic SQL, HTML, or configuration files using reusable templates, variables, loops, and conditional logic. In data engineering, it is commonly used in dbt to make SQL code modular and reusable.
It is widely used in:
- Data Engineering
- dbt
- Flask applications
- SQL generation
- Configuration files
- HTML rendering
Why Jinja is Used in DBT
Without Jinja:
- repetitive SQL
- hardcoded values
- duplicate logic
With Jinja:
- reusable code
- dynamic SQL generation
- parameterization
- DRY (Don’t Repeat Yourself) approach
Simple Example
Normal SQL:
SELECT *
FROM employee
WHERE country = 'India';
Using Jinja:
{% set country = 'India' %}
SELECT *
FROM employee
WHERE country = '{{ country }}';
Jinja dynamically replaces variables.
Generated SQL:
SELECT *
FROM employee
WHERE country = 'India';
- Make SQL dynamic
- Enable logic in SQL (loops, conditions)
- Create reusable code snippets (macros)
Think of it as "SQL with superpowers".
Why Jinja in DBT?
- Avoid repetition (DRY principle)
- Build configurable models
- Implement complex logic dynamically
- Generate custom SQL for multiple tables
Jinja Syntax Basics
Jinja uses delimiters inside SQL:
{{ ... }}→ Expressions & variables{% ... %}→ Logic (loops, conditions)
Example:
SELECT *
FROM {{ ref('orders') }}
WHERE order_date >= '{{ var("start_date") }}'
Common Jinja Tags
1. Variables
{{ var('schema') }}
2. Loops
{% for column in ['id', 'name', 'email'] %}
SELECT {{ column }}
{% endfor %}
3. Conditions
{% if target.name == 'prod' %}
WHERE created_at > '2024-01-01'
{% endif %}
Jinja in DBT Projects
-
Used in:
models,macros,seeds,tests -
Combined with DBT context functions:
ref()source()var()config()
Example: Dynamic Schema Using Jinja
{{ config(schema=var('custom_schema', 'default_schema')) }}
SELECT *
FROM {{ source('sales', 'orders') }}
Best Practices
Keep Jinja simple & readable
Avoid over-using logic in SQL (move to macros)
Use comments to explain complex logic
Test Jinja snippets in dbt compile
Debugging Jinja
Use:
dbt compile
to see the rendered SQL before execution.