Tests unitaires de la logique aux limites des CASE WHEN dans dbt

La logique métier vit souvent dans des expressions CASE WHEN. La segmentation clients, les paliers de tarification, les calculs de statut — ces conditionnelles multi-branches sont trompeusement simples à écrire et étonnamment faciles à rater.

Les bugs les plus courants :

• Conditions qui se chevauchent : deux branches qui peuvent toutes deux correspondre (l’ordre d’évaluation compte en SQL)
• Valeurs limites manquantes : erreurs d’un entier aux seuils de palier (> vs >=)
• Gestion des nulls : oublier que NULL >= 1000 retourne NULL, pas FALSE
• ELSE implicite : se fier au ELSE NULL par défaut quand vous vouliez gérer tous les cas explicitement

La solution est un test aux limites systématique — créez des lignes de test qui touchent chaque seuil, pas seulement des valeurs typiques.

Le pattern de test aux limites

-- 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: "Chaque seuil de dépenses devrait correspondre au bon segment"
    given:
      - input: ref('int__customers_summary')
        rows:
          - {customer_id: 1, total_spend: 15000}   # Platinum (bien au-dessus)
          - {customer_id: 2, total_spend: 10000}   # Platinum (limite exacte)
          - {customer_id: 3, total_spend: 9999}    # Gold (juste en dessous du platinum)
          - {customer_id: 4, total_spend: 5000}    # Gold (limite exacte)
          - {customer_id: 5, total_spend: 1000}    # Silver (limite exacte)
          - {customer_id: 6, total_spend: 1}       # Bronze (plus petit positif)
          - {customer_id: 7, total_spend: 0}       # Inactive (zéro)
          - {customer_id: 8, total_spend: null}    # Inactive (cas null)
    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"}

Pourquoi chaque ligne de test compte

Les données de test ne sont pas aléatoires. Chaque ligne a une fonction spécifique :

Valeurs aux limites exactes (clients 2, 4, 5) : ces lignes testent les correspondances exactes aux seuils — 10000, 5000, 1000. Si quelqu’un change >= en >, ces lignes échoueront. Les valeurs limites sont l’endroit où vivent les erreurs d’un entier.

Valeurs juste en dessous (client 3) : tester 9999 prouve que gold, et non platinum, s’applique en dessous de 10000. Sans cette ligne, une condition > 9999 serait identique à >= 10000 pour toutes les autres valeurs de test.

Plus petite valeur positive (client 6) : teste la condition total_spend > 0 avec la plus petite valeur positive possible.

Zéro (client 7) : teste si zéro correspond à “inactive” ou “bronze”. La condition > 0 dans la branche bronze signifie que zéro passe à ELSE. Si quelqu’un change cela en >= 0, le test le détecte.

Null (client 8) : c’est le cas le plus souvent manqué. NULL >= 1000 s’évalue à NULL, qui est faux — il passe toutes les branches jusqu’à la clause ELSE. Si quelqu’un ajoute COALESCE(total_spend, 0) en haut du CASE WHEN, le comportement avec les nulls change. Le test documente le comportement que vous avez choisi.

Le pattern appliqué

Cette approche de test aux limites fonctionne pour toute logique CASE WHEN :

1. Listez chaque branche y compris le ELSE implicite
2. Pour chaque seuil, créez une ligne à la limite exacte
3. Pour chaque limite, créez une ligne juste en dessous
4. Ajoutez une ligne null pour vérifier la gestion des nulls explicitement
5. Ajoutez une ligne “typique” par palier si cela aide à la lisibilité

Le test documente le comportement attendu par branche. Quand quelqu’un modifie le CASE WHEN pour ajouter une sixième catégorie, ou ajuste un ordre de priorité, il reçoit un retour immédiat sur si le comportement existant a changé.

Quand le test aux limites est vraiment utile

Ce pattern a une valeur particulière pour :

• Les modèles de segmentation clients où les règles métier changent régulièrement
• Le lead scoring où les conditions qui se chevauchent déterminent le routage
• La classification des commandes où le palier détermine la tarification ou la logique de traitement
• La dérivation de statut où les modèles en aval se ramifient selon le statut calculé

Dans chaque cas, la logique CASE WHEN pilote directement des décisions métier. Une mauvaise classification dans les segments clients peut signifier que quelqu’un obtient la mauvaise remise. Une erreur de lead scoring peut router un lead à forte valeur vers la mauvaise équipe. Le coût du test unitaire est faible comparé au coût de se tromper.

Pour les expressions CASE WHEN simples à deux branches (essentiellement un IF/ELSE), l’overhead d’écrire un test unitaire n’en vaut souvent pas la peine. Réservez ce pattern pour la logique multi-branches avec trois conditions ou plus, particulièrement quand les conditions impliquent des seuils numériques.