Why Jinja makes SQL dynamic in dbt - See It in Action

Why Jinja Makes SQL Dynamic

📖 Scenario: Imagine you work with a sales database. You want to create SQL queries that change based on different conditions, like filtering sales by region or date. Using Jinja in dbt helps you write SQL that can change automatically without rewriting the whole query.

🎯 Goal: You will build a simple dynamic SQL query using Jinja templating in dbt. This query will select sales data filtered by a region that you set in a variable.

📋 What You'll Learn

Create a variable called region with the value 'West'

Write a Jinja if statement to filter sales by the region variable

Use a SQL SELECT statement to get order_id and amount from sales table

Print the final SQL query string

💡 Why This Matters

🌍 Real World

In real projects, you often need SQL queries that change based on user input or configuration. Jinja templating in dbt lets you write flexible SQL that adapts without rewriting code.

💼 Career

Data analysts and engineers use dynamic SQL to create reusable reports and models. Understanding how Jinja makes SQL dynamic is key for working efficiently with dbt and modern data tools.

1

Create a variable for the region filter

Create a variable called region and set it to the string 'West'.

dbt

# Create a variable called region and set it to 'West'
# Your code here

Need a hint?

Use region = 'West' to create the variable.

2

Write a Jinja if statement for filtering

Write a Jinja if statement that checks if region is set, and if so, adds a SQL WHERE clause to filter by region. Store this in a variable called filter_clause.

dbt

region = 'West'
# Write a Jinja if statement to create filter_clause
# Your code here

Need a hint?

Use a Python if expression to set filter_clause to WHERE region = 'West' if region is not empty.

3

Create the dynamic SQL query using the filter

Create a variable called sql_query that uses an f-string to build a SQL SELECT statement selecting order_id and amount from the sales table. Add the filter_clause variable to the query to filter by region.

dbt

region = 'West'
filter_clause = f"WHERE region = '{region}'" if region else ''
# Create sql_query using filter_clause
# Your code here

Need a hint?

Use an f-string to combine the SELECT statement with filter_clause.

4

Print the final SQL query

Print the variable sql_query to display the final dynamic SQL query.

dbt

region = 'West'
filter_clause = f"WHERE region = '{region}'" if region else ''
sql_query = f"SELECT order_id, amount FROM sales {filter_clause}"
# Print the sql_query
# Your code here

Need a hint?

Use print(sql_query) to show the query.