ServicesÀ proposNotesContact Me contacter →
EN FR
Note

Tester les fonctions de fenêtrage avec les unit tests dbt

Comment concevoir des données de test qui valident le partitionnement, l'ordonnancement et le cadrage des fonctions de fenêtrage — avec des patterns pour ROW_NUMBER, FIRST_VALUE, les sommes cumulatives et les entrées délibérément hors ordre.

Planté
dbtbigquerytesting

Les fonctions de fenêtrage sont des candidates idéales pour les unit tests, car leur comportement dépend de trois facteurs en interaction : le partitionnement (quelles lignes appartiennent au même groupe), l’ordonnancement (la séquence au sein de chaque partition) et le cadrage (quelles lignes participent au calcul). Une erreur sur l’un de ces trois facteurs produit des résultats subtilement incorrects, difficiles à détecter lors d’une revue de code.

La clé pour tester efficacement les fonctions de fenêtrage est de concevoir des données de test qui produiraient des résultats différents si l’un des trois facteurs était erroné.

Les principes de conception

Trois principes pour les données de test des fonctions de fenêtrage :

  1. Utiliser des lignes source hors ordre. Si la fenêtre utilise ORDER BY event_timestamp, insérer les lignes source dans un ordre non chronologique. Cela prouve que la clause d’ordonnancement fonctionne réellement. Si quelqu’un modifie accidentellement l’ordre en event_id, le test échoue.

  2. Inclure plusieurs partitions. Si la fenêtre utilise PARTITION BY user_id, inclure au moins deux utilisateurs. Cela prouve l’isolation des partitions — les calculs de l’utilisateur 1 ne doivent pas contaminer ceux de l’utilisateur 2.

  3. Choisir des valeurs qui rendent les erreurs évidentes. Pour tester une somme cumulative, choisir des valeurs d’entrée qui produisent un total courant unique à chaque étape. Des valeurs comme 1, 1, 1 rendent difficile la distinction entre une somme correcte sur 3 lignes et une somme incorrecte sur 2 lignes.

Exemple complet : plusieurs fonctions de fenêtrage

Considérons ce modèle qui calcule des métriques au niveau de la session avec trois fonctions de fenêtrage différentes :

-- models/intermediate/int__users_sessions.sql
select
    user_id,
    event_timestamp,
    row_number() over (partition by user_id order by event_timestamp) as session_event_number,
    first_value(page_path) over (partition by user_id order by event_timestamp) as landing_page,
    sum(page_views) over (
        partition by user_id order by event_timestamp rows unbounded preceding
    ) as cumulative_page_views
from {{ ref('base__ga4__events') }}
unit_tests:
  - name: test_int_users_sessions_window_functions
    model: int__users_sessions
    description: "Window functions should correctly partition and order by user"
    given:
      - input: ref('base__ga4__events')
        rows:
          # Événements de l'utilisateur 1 (délibérément hors ordre)
          - {user_id: 1, event_timestamp: "2024-06-01 10:05:00", page_path: "/products", page_views: 2}
          - {user_id: 1, event_timestamp: "2024-06-01 10:00:00", page_path: "/home", page_views: 1}
          - {user_id: 1, event_timestamp: "2024-06-01 10:10:00", page_path: "/cart", page_views: 1}
          # Événements de l'utilisateur 2
          - {user_id: 2, event_timestamp: "2024-06-01 11:00:00", page_path: "/about", page_views: 3}
    expect:
      rows:
        # Utilisateur 1 - ordonné par timestamp, pas par ordre source
        - {user_id: 1, event_timestamp: "2024-06-01 10:00:00", session_event_number: 1, landing_page: "/home", cumulative_page_views: 1}
        - {user_id: 1, event_timestamp: "2024-06-01 10:05:00", session_event_number: 2, landing_page: "/home", cumulative_page_views: 3}
        - {user_id: 1, event_timestamp: "2024-06-01 10:10:00", session_event_number: 3, landing_page: "/home", cumulative_page_views: 4}
        # Utilisateur 2
        - {user_id: 2, event_timestamp: "2024-06-01 11:00:00", session_event_number: 1, landing_page: "/about", cumulative_page_views: 3}

Examinons pourquoi ces données de test sont conçues délibérément :

Lignes source hors ordre. Les événements de l’utilisateur 1 sont listés dans l’ordre 10:05, 10:00, 10:10 dans le bloc given. La sortie attendue les réordonne en 10:00, 10:05, 10:10. Si le modèle ordonnait par autre chose que event_timestamp, les valeurs de session_event_number et de landing_page seraient incorrectes.

Plusieurs utilisateurs avec isolation des partitions. L’utilisateur 2 n’a qu’un seul événement. Son session_event_number doit être 1, et non 4. Sa landing_page doit être “/about”, et non “/home”. Si la clause PARTITION BY user_id était absente, les valeurs de l’utilisateur 2 intégreraient les données de l’utilisateur 1.

Valeurs cumulatives distinctives. Les valeurs de page_views (1, 2, 1, 3) produisent des totaux courants uniques : 1, 3, 4. Si la clause de cadrage était ROWS BETWEEN 1 PRECEDING AND CURRENT ROW au lieu de ROWS UNBOUNDED PRECEDING, les valeurs seraient 1, 3, 3 — le test détecterait la différence.

Ce test unique vérifie ROW_NUMBER, FIRST_VALUE et SUM avec un cadre de fenêtre — en un seul passage. Si l’une des spécifications de fenêtre est incorrecte, au moins une valeur attendue sera fausse.

Ce que chaque type de fonction de fenêtrage requiert

ROW_NUMBER / RANK / DENSE_RANK : tester avec des ex aequo. Si deux lignes ont le même timestamp, est-ce que ROW_NUMBER produit des valeurs différentes (non déterministe sans critère de départage) ? Est-ce que RANK produit la même valeur ? Inclure des lignes ex aequo dans les données de test si le modèle utilise un classement.

FIRST_VALUE / LAST_VALUE : tester que l’ordonnancement est correct. LAST_VALUE est particulièrement délicat — son cadre par défaut est ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, ce qui signifie qu’elle retourne la ligne courante, et non la dernière ligne de la partition. Si le modèle utilise LAST_VALUE, le test doit vérifier s’il faut ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

LAG / LEAD : tester les première et dernière lignes de chaque partition. LAG sur la première ligne retourne NULL (ou la valeur par défaut). LEAD sur la dernière ligne retourne NULL. Ces cas limites sont là où les erreurs de décalage apparaissent.

SUM / AVG avec des clauses de cadrage : la spécification du cadre est la principale source de bugs. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW donne un total courant. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW donne une somme glissante sur deux lignes. La différence est significative, et difficile à détecter en revue de code. Choisir des valeurs de test qui produisent des résultats différents selon la largeur du cadre.

Pièges courants

Tester avec des données déjà triées. Si les lignes source sont déjà dans le bon ordre, le test passe même si la clause ORDER BY est absente. Toujours mettre délibérément les lignes source dans le mauvais ordre.

Partitions à une seule ligne uniquement. Si chaque partition contient exactement une ligne, les fonctions de fenêtrage produisent trivialement des résultats corrects. Il faut au moins une partition avec plusieurs lignes pour exercer la logique.

Valeurs symétriques. Si toutes les valeurs de page_views sont 1, alors cumulative_page_views à la ligne 2 vaut 2 quel que soit le cadre — 1 ou 2 lignes précédentes. Utiliser des valeurs asymétriques pour que chaque largeur de cadre possible produise un résultat différent.