Variables and control flow in dbt - Mini Project: Build & Apply

Using Variables and Control Flow in dbt Models

📖 Scenario: You work as a data analyst using dbt to transform sales data. You want to create a model that filters sales records based on a minimum sales amount set by a variable. This helps you focus on important sales only.

🎯 Goal: Build a dbt model that uses a variable to set a sales threshold and applies control flow to filter sales records with amounts greater than or equal to that threshold.

📋 What You'll Learn

Create a variable called min_sales with the value 1000 in the dbt model.

Use a config block to set the model materialization to table.

Write a SQL query that selects order_id and sales_amount from sales_data where sales_amount is greater than or equal to the min_sales variable.

Use Jinja control flow to apply the filter condition based on the variable.

💡 Why This Matters

🌍 Real World

In real projects, analysts use variables and control flow in dbt to create dynamic models that adapt to different business needs without rewriting SQL.

💼 Career

Understanding variables and control flow in dbt is essential for data engineers and analysts to build maintainable and flexible data transformation pipelines.

1

Set up the sales data source

Create a dbt model SQL file with a select statement that selects order_id and sales_amount from the table sales_data. This is the base query before filtering.

dbt

select order_id, sales_amount from sales_data
-- Your code here

Need a hint?

This is the starting point. Just select the columns from the table.

2

Add a variable for minimum sales threshold

Add a Jinja variable called min_sales and set it to 1000 at the top of the model file.

dbt

{% set min_sales = 1000 %}
select order_id, sales_amount from sales_data
-- Your code here

Need a hint?

Use Jinja syntax to create the variable before the SQL query.

3

Apply control flow to filter sales by the variable

Use a Jinja if statement to add a where clause that filters sales_amount to be greater than or equal to min_sales. The where clause should only appear if min_sales is set.

dbt

{% set min_sales = 1000 %}
select order_id, sales_amount from sales_data
{% if min_sales %}
    where sales_amount >= {{ min_sales }}
{% endif %}
-- Your code here

Need a hint?

Use Jinja control flow to conditionally add the filter based on the variable.

4

Add model configuration and print the final query

Add a config block at the top to set materialized='table'. Then print the full SQL query as the model output.

dbt

{% config(materialized='table') %}
{% set min_sales = 1000 %}
select order_id, sales_amount from sales_data
{% if min_sales %}
    where sales_amount >= {{ min_sales }}
{% endif %}
-- Your code here

Need a hint?

Use the config Jinja block to set the model materialization.