Jinja is a powerful templating engine that allows you to write dynamic, reusable code. Originally developed for Python web applications, Jinja has found its way into the data engineering world through dbt (data build tool), where it enables analysts and data engineers to create dynamic data transformation pipelines.

This article focuses on the basics of Jinja and provides examples of its usage in dbt models:

This is the first post in a series about Jinja practical applications in dbt. This series will give you a comprehensive understanding of how to leverage Jinja to take your dbt models to the next level.

The origins of Jinja

Jinja was initially developed as a template engine for the Python web frameworks like Django or Flask. Its name derives from Japanese Shinto shrines (神社, “jinja”), reflecting its purpose as a “template” or structure. The engine was designed to separate presentation from application logic, allowing developers to dynamically generate content for web pages. In the data world, dbt adopted Jinja as its templating engine because of its readability, flexibility, and Python compatibility—dbt is implemented in Python.

Why use Jinja in dbt?

Jinja transforms static SQL into dynamic code. Without Jinja, you’d need to manually update SQL queries every time you want to change a date range, add new columns, or modify logic. With Jinja, you can:

  • Create reusable logic: Define variables once and use them across multiple models, reducing duplication and maintenance overhead

  • Build flexible models: Write models that adapt based on configuration, environment, or input parameters without code changes

  • Automate repetitive tasks: Generate SQL code programmatically instead of writing repetitive column lists or union statements

  • Improve maintainability and consistency: Centralise business logic, filter conditions, and defaults in one place, making updates easier and reducing errors

Understanding Jinja syntax

Jinja uses a simple syntax that distinguishes between static content and dynamic template code. In dbt, you’ll primarily work with three types of Jinja delimiters:

  • Expressions: Used to output values or call functions, and written between {{ }}
  • Statements: Used for control structures like loops and conditionals, and written between {% %}
  • Comments: Used for documentation that won’t appear in compiled SQL, and written between {# #}

Here’s a basic example of Jinja used in a dbt model:


select
    customer_id,
    order_date,
    COALESCE(report_status, '{{ var("default_status") }}') as report_status
from {{ ref('raw_orders') }}
where order_date >= '{{ var("start_date") }}'

When dbt compiles this model (assuming deafult_status is ‘open’ and start_date is ‘2025-01-01’), the Jinja expressions are evaluated and replaced with the actual values:

select
    customer_id,
    order_date,
    'open' as report_status
from analytics.dbt_staging.raw_orders
where order_date >= '2025-01-01'

Common use cases in dbt

Environment-specific configuration

You can dynamically switch database schemas or tables based on the environment where a model is run (development, staging, production).

dbt model:


select 
  * 
from {{ var('env_prefix') }}_customers
where created_date >= '{{ var('cutoff_date') }}'

Compiled SQL (assuming the model is run in production environment, where env_prefix is ‘prod’ and cutoff_date is ‘2025-01-01’):

select 
  * 
from prod_customers
where created_date >= '2025-01-01'

Dynamic column selection

Jinja enables you to automatically include or exclude columns based on a variable value.

dbt model:


select
    customer_id,
    {% if var('include_pii') %}
    email,
    phone_number,
    {% endif %}
    order_count
from {{ ref('customer_summary') }}

Compiled SQL when include_pii is True:

select
    customer_id,
    email,
    phone_number,
    order_count
from analytics.dbt_staging.customer_summary

Compiled SQL when include_pii is False:

select
    customer_id,
    order_count
from analytics.dbt_staging.customer_summary

Conditional logic

You can use Jinja variables to apply different business rules based on parameters.

dbt model:


select
    *,
    case
        when region = 'US' then amount * {{ var('usd_conversion_rate') }}
        when region = 'EU' then amount * {{ var('eur_conversion_rate') }}
        else amount
    end as normalised_amount
from {{ ref('sales_data') }}

Compiled SQL (assuming usd_conversion_rate is 1.0 and eur_conversion_rate is 1.08):

select
    *,
    case
        when region = 'US' then amount * 1.0
        when region = 'EU' then amount * 1.08
        else amount
    end as normalised_amount
from analytics.dbt_staging.sales_data

Best practices for using Jinja in dbt

Start simple

Begin by replacing hard-coded values with variables, then gradually incorporate more advanced features like loops and conditionals, if needed.

Remember the compile-time context

Always remember that Jinja runs at compile time, not query time. This means Jinja is perfect for generating SQL queries but not for performing runtime calculations. When a dbt model is compiled, all Jinja expressions in it will be evaluated and replaced with static values before your SQL even reaches the database.

Maintain readability

Keep your Jinja code readable by using:

  • Consistent indentation
  • Meaningful variable names
  • Whitespace to separate logical blocks
  • Comments for complex logic

Document your code

Use Jinja comments enclosed within {# #} to document business logic used in the model, for example:

{# 
  Filter for active customers only - defined as having
  placed an order in the last 90 days or having
  an active subscription
#}

{% set active_customer_filter %}
  (last_order_date >= dateadd('day', -90, current_date) or
   subscription_status = 'active')
{% endset %}

select * from {{ ref('customers') }}
where {{ active_customer_filter }}

Test compiled output

Before executing your models, test your compiled SQL output by running dbt compile to ensure your Jinja code generates the expected SQL. These compiled files are created in the target/ directory of your dbt project, and are updated every time you compile your code. Testing compiled queries before executing helps you to catch syntax errors or unexpected behaviour early.

Use macros for repeating or complex logic

When you find yourself repeating the same Jinja patterns across multiple models, consider creating a macro. Macros are reusable blocks of Jinja code that can significantly improve maintainability and unify the transformation logic.

Conclusion

Jinja in dbt opens up a world of possibilities for creating more dynamic, maintainable data transformations.

In future posts of this “Jinja in dbt” series, I’ll explore:

Illustration by Storyset

dbt data_engineering jinja