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

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

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

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

Hint

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

Practice

1. In dbt models, which SQL construct is commonly used to add if/else logic for creating new columns?

easy

A. WHILE LOOP

B. FOR LOOP

C. CASE WHEN ... THEN ... ELSE ... END

D. FUNCTION CALL

Solution

1. Step 1: Understand conditional logic in SQL

   SQL uses CASE statements to check conditions and return values accordingly.

2. Step 2: Identify the correct syntax for if/else logic

   CASE WHEN ... THEN ... ELSE ... END is the standard way to implement if/else logic in SQL and dbt models.

3. Final Answer:

   CASE WHEN ... THEN ... ELSE ... END -> Option C

4. Quick Check:

   CASE statement = if/else logic [OK]

Hint: Remember: CASE is SQL's if/else [OK]

Common Mistakes:

• Confusing loops with conditional logic
• Using function calls instead of CASE
• Trying to use procedural code in SQL

2. Which of the following is the correct syntax for a CASE statement in a dbt model to check if a column score is greater than 50?

easy

A. CASE WHEN score THEN 'Pass' ELSE 'Fail' END

B. CASE score > 50 THEN 'Pass' ELSE 'Fail'

C. IF score > 50 THEN 'Pass' ELSE 'Fail' END

D. CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END

Solution

1. Step 1: Review correct CASE syntax

   The correct syntax starts with CASE WHEN condition THEN result ELSE other_result END.

2. Step 2: Check each option for syntax errors

   CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END correctly uses CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END. Others miss WHEN, END, or have wrong structure.

3. Final Answer:

   CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END -> Option D

4. Quick Check:

   CASE WHEN + condition + THEN + result + ELSE + result + END [OK]

Hint: CASE must have WHEN and END keywords [OK]

Common Mistakes:

• Omitting WHEN keyword
• Missing END at the end
• Using IF instead of CASE in SQL

3. Given the following dbt model SQL snippet, what will be the value of status for a row where score = 75?

SELECT
  score,
  CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 70 THEN 'Good'
    ELSE 'Average'
  END AS status
FROM scores_table

medium

A. 'Good'

B. NULL

C. 'Average'

D. 'Excellent'

Solution

1. Step 1: Check conditions in order for score = 75

   First condition: score >= 90? No (75 < 90). Second condition: score >= 70? Yes (75 >= 70).

2. Step 2: Determine which condition applies first

   The first true condition is WHEN score >= 70 THEN 'Good', so status = 'Good'.

3. Final Answer:

   'Good' -> Option A

4. Quick Check:

   75 >= 70 = 'Good' [OK]

Hint: Check CASE conditions top to bottom [OK]

Common Mistakes:

• Ignoring order of CASE conditions
• Choosing 'Excellent' incorrectly
• Assuming ELSE applies before WHEN

4. Identify the error in this dbt model snippet:

SELECT
  user_id,
  CASE
    WHEN age > 18 THEN 'Adult'
    WHEN age <= 18 THEN 'Minor'
    ELSE 'Unknown'
FROM users

medium

A. Missing END keyword for CASE statement

B. Incorrect comparison operator in WHEN clauses

C. ELSE clause cannot be used in CASE

D. Missing comma after user_id

Solution

1. Step 1: Check CASE statement syntax

   CASE statements must end with END keyword to close the block.

2. Step 2: Verify presence of END keyword

   The snippet lacks END after ELSE 'Unknown', causing syntax error.

3. Final Answer:

   Missing END keyword for CASE statement -> Option A

4. Quick Check:

   CASE must end with END [OK]

Hint: Always close CASE with END [OK]

Common Mistakes:

• Forgetting END keyword
• Misplacing ELSE clause
• Assuming ELSE is optional

5. You want to create a new column category in your dbt model based on sales amount: if sales > 1000, category is 'High'; if sales between 500 and 1000 inclusive, category is 'Medium'; otherwise, 'Low'. Which CASE statement correctly implements this logic?

hard

A. CASE WHEN sales > 1000 THEN 'High' WHEN sales > 500 THEN 'Medium' ELSE 'Low' END

B. CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END

C. CASE WHEN sales > 500 AND sales <= 1000 THEN 'Medium' WHEN sales > 1000 THEN 'High' ELSE 'Low' END

D. CASE WHEN sales > 1000 THEN 'High' ELSE WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END

Solution

1. Step 1: Analyze the conditions and their order

   Check sales > 1000 first for 'High', then sales between 500 and 1000 inclusive for 'Medium', else 'Low'.

2. Step 2: Verify correct syntax and condition order

   CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END correctly orders conditions (highest first) and uses proper inclusive range with AND. CASE WHEN sales > 500 AND sales <= 1000 THEN 'Medium' WHEN sales > 1000 THEN 'High' ELSE 'Low' END misses inclusive lower bound (sales=500 fails) and suboptimal order. CASE WHEN sales > 1000 THEN 'High' WHEN sales > 500 THEN 'Medium' ELSE 'Low' END misses inclusive lower bound. CASE WHEN sales > 1000 THEN 'High' ELSE WHEN sales >= 500 THEN 'Medium' ELSE 'Low' END has syntax error with ELSE WHEN.

3. Final Answer:

   CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END -> Option B

4. Quick Check:

   Order and range checks correct in CASE WHEN sales > 1000 THEN 'High' WHEN sales >= 500 AND sales <= 1000 THEN 'Medium' ELSE 'Low' END [OK]

Hint: Check ranges carefully and order conditions top-down [OK]

Common Mistakes:

• Wrong order of conditions causing misclassification
• Syntax error using ELSE WHEN instead of WHEN
• Missing inclusive range checks