if/else logic in models in dbt - Mini Project: Build & Apply

Using if/else Logic in dbt Models

📖 Scenario: You work as a data analyst for an online store. You want to create a dbt model that classifies orders as 'High Value' or 'Regular' based on the order amount.

🎯 Goal: Build a dbt model using if/else logic to label orders as 'High Value' if the amount is greater than 100, otherwise label them as 'Regular'.

📋 What You'll Learn

Create a dbt model named order_classification.sql with sample order data.

Add a configuration variable high_value_threshold set to 100.

Use if/else logic in a CASE WHEN statement to classify orders.

Output the order ID, amount, and classification label.

💡 Why This Matters

🌍 Real World

Classifying orders by value helps businesses target customers with special offers or alerts.

💼 Career

Data analysts and engineers often use conditional logic in dbt models to prepare data for reporting and decision-making.

1

Create the initial orders data in the dbt model

Create a dbt model named order_classification.sql with a WITH clause called orders containing these exact rows: (1, 50), (2, 150), (3, 75), and (4, 200). The columns should be order_id and amount.

dbt

-- Create the orders CTE with the exact rows and columns
-- Your code here

Need a hint?

Use a WITH clause and UNION ALL to create the sample data.

2

Add a configuration variable for the high value threshold

Add a variable called high_value_threshold and set it to 100 at the top of the model.

dbt

high_value_threshold = 100

WITH orders AS (
    SELECT 1 AS order_id, 50 AS amount UNION ALL
    SELECT 2, 150 UNION ALL
    SELECT 3, 75 UNION ALL
    SELECT 4, 200
)
SELECT * FROM orders
-- Your code here

Need a hint?

Use dbt Jinja syntax to set the variable: {% set high_value_threshold = 100 %}.

3

Use if/else logic to classify orders

Modify the SELECT statement to include a new column called classification. Use a CASE WHEN statement to label orders as 'High Value' if amount > high_value_threshold, otherwise label them as 'Regular'.

dbt

{% set high_value_threshold = 100 %}

WITH orders AS (
    SELECT 1 AS order_id, 50 AS amount UNION ALL
    SELECT 2, 150 UNION ALL
    SELECT 3, 75 UNION ALL
    SELECT 4, 200
)
SELECT order_id, amount,
    CASE WHEN amount > high_value_threshold THEN 'High Value' ELSE 'Regular' END AS classification
FROM orders
-- Your code here

Need a hint?

Use CASE WHEN amount > high_value_threshold THEN 'High Value' ELSE 'Regular' END to classify.

4

Display the final classified orders

Add a SELECT statement that outputs order_id, amount, and classification from the orders CTE with the classification logic applied. Then print the results.

dbt

{% set high_value_threshold = 100 %}

WITH orders AS (
    SELECT 1 AS order_id, 50 AS amount UNION ALL
    SELECT 2, 150 UNION ALL
    SELECT 3, 75 UNION ALL
    SELECT 4, 200
)
SELECT order_id, amount,
    CASE WHEN amount > high_value_threshold THEN 'High Value' ELSE 'Regular' END AS classification
FROM orders
-- Your code here

Need a hint?

Run the model to see the classification output with order IDs, amounts, and labels.