For loops for dynamic SQL in dbt - Mini Project: Build & Apply

For loops for dynamic SQL

📖 Scenario: You work as a data analyst using dbt to build SQL models. You want to create a dynamic SQL query that selects sales data for multiple regions without repeating code.

🎯 Goal: Build a dbt model using a for loop in Jinja to dynamically generate SQL code for multiple regions.

📋 What You'll Learn

Create a list variable called regions with the values 'north', 'south', and 'east'.

Create a variable called sales_threshold and set it to 1000.

Use a for loop with the variable region to iterate over regions and generate a SQL SELECT statement for each region.

Print the final SQL query string that combines all region queries with UNION ALL.

💡 Why This Matters

🌍 Real World

Data analysts often need to write SQL queries that repeat similar logic for different categories or regions. Using loops helps avoid repetitive code and makes maintenance easier.

💼 Career

Knowing how to generate dynamic SQL with loops is useful for dbt developers and data engineers who build scalable and maintainable data transformation pipelines.

1

Create the list of regions

Create a list variable called regions with the exact values 'north', 'south', and 'east'.

dbt

# Create the list variable regions with 'north', 'south', and 'east'
# Your code here

Need a hint?

Use square brackets [] to create a list and separate values with commas.

2

Set the sales threshold

Create a variable called sales_threshold and set it to the integer 1000.

dbt

regions = ['north', 'south', 'east']
# Create the variable sales_threshold and set it to 1000
# Your code here

Need a hint?

Assign the number 1000 directly to the variable sales_threshold.

3

Generate SQL queries with a for loop

Use a for loop with the variable region to iterate over regions. Inside the loop, create a list called queries that contains a SQL SELECT statement for each region selecting all columns from sales_data where region equals the current region and sales is greater than sales_threshold. Use string formatting with f-strings.

dbt

regions = ['north', 'south', 'east']
sales_threshold = 1000
# Use a for loop to create a list called queries with SQL SELECT statements for each region
# Your code here

Need a hint?

Start with an empty list queries = []. Use a for loop to add formatted SQL strings to this list.

4

Print the combined SQL query

Use print to display the final SQL query string that joins all queries in queries with UNION ALL between them.

dbt

regions = ['north', 'south', 'east']
sales_threshold = 1000
queries = []
for region in regions:
    query = f"SELECT * FROM sales_data WHERE region = '{region}' AND sales > {sales_threshold}" 
    queries.append(query)
# Print the combined SQL query string joined by ' UNION ALL '
# Your code here

Need a hint?

Use the join method on the string ' UNION ALL ' to combine the list queries.