Unit Testing CASE WHEN Boundary Logic in dbt

Business logic often lives in CASE WHEN statements. Customer segmentation, pricing tiers, status calculations — these multi-branch conditionals are deceptively simple to write and surprisingly easy to get wrong.

The most common bugs:

• Overlapping conditions: Two branches that can both match (evaluation order matters in SQL)
• Missing boundary values: Off-by-one errors at tier thresholds (> vs >=)
• Null handling: Forgetting that NULL >= 1000 returns NULL, not FALSE
• Implicit ELSE: Relying on the default ELSE NULL when you meant to handle all cases explicitly

The solution is systematic boundary testing — create test rows that hit every threshold, not just typical values.

The Boundary Testing Pattern

-- models/marts/core/mrt__core__customer_segments.sql
select
    customer_id,
    total_spend,
    case
        when total_spend >= 10000 then 'platinum'
        when total_spend >= 5000 then 'gold'
        when total_spend >= 1000 then 'silver'
        when total_spend > 0 then 'bronze'
        else 'inactive'
    end as segment
from {{ ref('int__customers_summary') }}

unit_tests:
  - name: test_mrt_core_customer_segments_all_tiers
    model: mrt__core__customer_segments
    description: "Each spend threshold should map to correct segment"
    given:
      - input: ref('int__customers_summary')
        rows:
          - {customer_id: 1, total_spend: 15000}   # Platinum (well above)
          - {customer_id: 2, total_spend: 10000}   # Platinum (exact boundary)
          - {customer_id: 3, total_spend: 9999}    # Gold (just under platinum)
          - {customer_id: 4, total_spend: 5000}    # Gold (exact boundary)
          - {customer_id: 5, total_spend: 1000}    # Silver (exact boundary)
          - {customer_id: 6, total_spend: 1}       # Bronze (smallest positive)
          - {customer_id: 7, total_spend: 0}       # Inactive (zero)
          - {customer_id: 8, total_spend: null}    # Inactive (null case)
    expect:
      rows:
        - {customer_id: 1, segment: "platinum"}
        - {customer_id: 2, segment: "platinum"}
        - {customer_id: 3, segment: "gold"}
        - {customer_id: 4, segment: "gold"}
        - {customer_id: 5, segment: "silver"}
        - {customer_id: 6, segment: "bronze"}
        - {customer_id: 7, segment: "inactive"}
        - {customer_id: 8, segment: "inactive"}

Why Each Test Row Matters

The test data isn’t random. Each row serves a specific purpose:

Exact boundary values (customers 2, 4, 5): These test exact threshold matches — 10000, 5000, 1000. If someone changes >= to >, these rows will fail. Boundary values are where off-by-one errors live.

Just-under values (customer 3): Testing 9999 proves that gold, not platinum, kicks in below 10000. Without this row, a > 9999 condition would look identical to >= 10000 for all other test values.

Smallest positive value (customer 6): Tests the total_spend > 0 condition with the minimum possible positive value.

Zero (customer 7): Tests whether zero maps to “inactive” or “bronze.” The > 0 condition in the bronze branch means zero falls through to the ELSE. If someone changes this to >= 0, the test catches it.

Null (customer 8): This is the most commonly missed case. NULL >= 1000 evaluates to NULL, which is falsy — it falls through every branch to the ELSE clause. If someone adds COALESCE(total_spend, 0) at the top of the CASE WHEN, the null behavior changes. The test documents whichever behavior you’ve chosen.

The Pattern Applied

This boundary testing approach works for any CASE WHEN logic:

1. List every branch including the implicit ELSE
2. For each threshold, create a row at the exact boundary
3. For each boundary, create a row just below it
4. Add a null row to verify null handling explicitly
5. Add one “typical” row per tier if helpful for readability

The test documents expected behavior per branch. When someone modifies the CASE WHEN to add a sixth category, or adjusts a priority order, they get immediate feedback on whether existing behavior changed.

When Boundary Testing Earns Its Keep

This pattern is particularly valuable on:

• Customer segmentation models where the business rules change regularly
• Lead scoring where overlapping conditions determine routing
• Order classification where the tier determines pricing or fulfillment logic
• Status derivation where downstream models branch on the computed status

In each case, the CASE WHEN logic directly drives business decisions. A misclassification in customer segments might mean someone gets the wrong discount. A lead scoring error might route a high-value lead to the wrong team. The unit test cost is small compared to the cost of getting these wrong.

For simple two-branch CASE WHEN statements (basically an IF/ELSE), the overhead of writing a unit test often isn’t worth it. Reserve this pattern for multi-branch logic with three or more conditions, especially when the conditions involve numeric thresholds.