Dynamic SQL Generation with Jinja Expressions

In this lesson, you will learn how to use Jinja expressions within your dbt models to inject variables and perform calculations, allowing you to write more flexible and maintainable SQL code.

Moving Beyond Static SQL

Up until now, you have been writing "static" SQL. This means your queries look the same every time they run. However, as your data pipelines grow, you often need to adapt your SQL based on environment variables, configurations, or simple logic. This is where Jinja—a powerful templating engine integrated into dbt—comes in.

In dbt, we use the {{ ... }} syntax to tell dbt to evaluate the content inside the double braces as code before the SQL is sent to your data warehouse.

Injecting Variables and Calculations

The simplest way to use Jinja is to inject values directly into your SQL statements. For example, if you have a threshold for a "high-value" transaction that changes periodically, you can store it in your dbt_project.yml file and reference it dynamically:

-- models/high_value_transactions.sql

SELECT
    order_id,
    amount,
    {{ var('high_value_threshold') }} as threshold
FROM {{ ref('stg_orders') }}
WHERE amount > {{ var('high_value_threshold') }}

You can also perform basic arithmetic or logic directly within your SQL files. For instance, if you want to calculate a discount percentage dynamically based on an existing variable:

SELECT
    product_name,
    price,
    price * {{ var('discount_rate') / 100 }} as discount_amount
FROM {{ ref('stg_products') }}

Why use this?

When you use Jinja in this way, dbt "compiles" the code before execution. The data warehouse never sees the Jinja; it receives clean, standard SQL. This allows you to centralize your business logic (like tax rates or thresholds) in one place while keeping your SQL models clean and DRY (Don't Repeat Yourself). By moving these hard-coded numbers out of your queries and into Jinja expressions, you make your pipeline resilient to changes in business requirements.

Key Takeaway: Use the {{ ... }} syntax to inject variables and perform calculations in your SQL files, turning rigid queries into dynamic code that adapts to changing business needs.