Skip to main content

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.