if/else logic in models in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of conditional logic in a dbt model

Consider this dbt model SQL snippet using a CASE statement. What will be the value of category for a row where sales_amount is 150?

dbt

SELECT
  sales_amount,
  CASE
    WHEN sales_amount > 200 THEN 'High'
    WHEN sales_amount > 100 THEN 'Medium'
    ELSE 'Low'
  END AS category
FROM sales_data

A NULL

B 'High'

C 'Low'

D 'Medium'

💡 Hint

Think about the order of conditions in the CASE statement and which condition matches 150.

The CASE statement checks conditions in order. Since 150 is not greater than 200, the first WHEN is false. The second WHEN checks if 150 is greater than 100, which is true, so the category is 'Medium'.

❓ data_output

intermediate

Resulting rows after applying if/else logic in a dbt model

Given this dbt model SQL, how many rows will have status = 'Active'?

dbt

SELECT
  user_id,
  CASE
    WHEN last_login > CURRENT_DATE - INTERVAL '30 days' THEN 'Active'
    ELSE 'Inactive'
  END AS status
FROM user_logins

A No users, because of syntax error

B All users who logged in within the last 30 days

C All users regardless of login date

D All users who logged in more than 30 days ago

💡 Hint

Look at the condition comparing last_login date with current date minus 30 days.

The CASE checks if last_login is within the last 30 days. Those users get 'Active'. Others get 'Inactive'. So only users with recent logins are 'Active'.

🔧 Debug

advanced

Identify the error in if/else logic in a dbt model

This dbt model SQL snippet is intended to assign a label based on score. What error will it raise?

dbt

SELECT
  user_id,
  CASE
    WHEN score > 90 THEN 'Excellent'
    WHEN score > 75 THEN 'Good'
    ELSE 'Average'
  END AS performance
FROM user_scores
WHERE score >= 0

A No error, runs correctly

B SyntaxError due to missing END keyword

C Runtime error because score can be NULL

D TypeError because score is compared to string

💡 Hint

Check if the CASE statement syntax is complete and if the WHERE clause handles NULLs.

The CASE statement is correctly formed with END. The WHERE clause filters out negative scores but does not exclude NULLs, which may or may not cause issues depending on the database. However, no syntax or type errors exist here.

🚀 Application

advanced

Applying if/else logic to create a new column in a dbt model

You want to create a new column risk_level in your dbt model based on credit_score: scores above 700 are 'Low', between 500 and 700 are 'Medium', and below 500 are 'High'. Which SQL snippet correctly implements this?

A CASE WHEN credit_score > 700 THEN 'Low' WHEN credit_score >= 500 THEN 'Medium' ELSE 'High' END AS risk_level

B CASE WHEN credit_score >= 700 THEN 'Low' WHEN credit_score > 500 THEN 'Medium' ELSE 'High' END AS risk_level

C CASE WHEN credit_score > 700 THEN 'Low' WHEN credit_score > 500 THEN 'Medium' ELSE 'High' END AS risk_level

D CASE WHEN credit_score >= 700 THEN 'Low' WHEN credit_score >= 500 THEN 'Medium' ELSE 'High' END AS risk_level

💡 Hint

Pay attention to the boundary conditions and whether to use > or >=.

Option A correctly assigns 'Low' for scores strictly greater than 700, 'Medium' for scores from 500 up to and including 700, and 'High' otherwise. This matches the requirement.

🧠 Conceptual

expert

Understanding nested if/else logic in dbt models

In dbt models, what is the main difference between using nested CASE statements versus multiple WHEN conditions in a single CASE statement?

A Multiple WHEN conditions execute faster than nested CASE statements in all databases.

B Nested CASE statements are invalid syntax in dbt models.

C Nested CASE statements allow more complex, multi-level decision trees, while multiple WHEN conditions in one CASE are flat and evaluated in order.

D Multiple WHEN conditions can only handle numeric comparisons, nested CASE can handle strings.

💡 Hint

Think about how CASE statements evaluate conditions and how nesting affects logic structure.

Nested CASE statements let you build multi-level decisions, like if/else inside if/else, while multiple WHENs in one CASE are checked sequentially in a flat structure.

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