Récemment, je discutais avec un collègue de la façon d'effectuer des tests unitaires en SQL et j'ai réalisé que, bien que je sois encore au début de ma carrière dans les données, j'ai déjà rencontré ce problème à plusieurs reprises et à chaque tour, j'avais une approche différente pour résoudre. J'ai pensé qu'il pourrait être utile de partager mes hacks afin que je puisse consolider ce que j'ai appris, et aussi pour apprendre de vous le lecteur ce que vous avez fait et comment cela s'est passé. On y va?

Mais pourquoi tester unitaire SQL ?

Il est important de préciser que je parle ici de tester la logique SQL et non de tester les données . Tester les données consiste à s'assurer que les suppositions pour les propriétés de schéma, comme la nullité et l'unicité, sont effectivement valables pour les valeurs disponibles. Un SGBDR traditionnel garantirait cela avec des contraintes de table, mais nous n'avons pas cette fonctionnalité dans les entrepôts de données, nous devons donc lire et vérifier les conditions préalables et postérieures sur l'ensemble de données.

Le SQL de test unitaire est identique au code d'application de test unitaire : assurez-vous qu'un élément de logique produit la sortie attendue pour un ensemble d'entrées donné. Alors que les meilleures pratiques dictent que nous testons unitairement presque tout le code de l'application, que ce soit C++ ou Python, nous voyons rarement cela tenté avec SQL. Pourquoi?

La réponse, pour moi, est que l'écriture de SQL n'est pas la partie la plus difficile . La plupart du temps, les transformations de données sont triviales et répétitives, et la difficulté réside davantage dans la modélisation appropriée des données que dans le déplacement de bits de A à B. Cependant, lorsque la logique métier est nécessaire dans les transformations, vous pouvez vous retrouver avec une complexité nécessaire qui est difficile pour se décomposer - comme une expression CASE-WHEN complexe pour générer une colonne, avec des entrées provenant d'un redoutable JOIN à sept voies, et une clause WHERE multiligne qui garantit une table de vérité pour comprendre ce qui se passe…

D'après mon expérience, s'assurer que la logique est solide ne reposait que sur des tests manuels et une approbation du client, ce qui est 1) fragile et 2) presque criminel compte tenu de la pertinence des tests unitaires SQL ! Alors que les applications nécessitent toutes sortes d'échafaudages pour restreindre l'état mutable et assurer la reproductibilité des tests, SQL est pur et explicite. Alors pourquoi ne pas utiliser la même infrastructure pour valider que la logique fonctionne comme prévu ?

Première tentative : analyse

Ma première tentative de test unitaire de scripts SQL a eu lieu lorsque je travaillais chez Google, en utilisant des technologies internes comme Dremel et Powerdrill (vous remarquerez que leurs projets avaient des noms liés au travail avec les journaux ).

Nous étions en train de construire un pipeline d'analyse pour une nouvelle application, en traitant les journaux des utilisateurs pour générer des métriques chères aux chefs de produit, comme les utilisateurs actifs par pays ou par cohorte. Une requête représentative serait :

CREATE TABLE _7dau_by_country_platform AS
SELECT
  day,
  country,
  platform,
  COUNT(DISTINCT user_id) AS active_users
FROM UNNEST(dates) day
JOIN app_logs ON DATE(ts) BETWEEN DATE_SUB(day, INTERVAL 7 DAY) AND day
GROUP BY 1, 2, 3

Hacker un framework de test

Nous voulions faire mieux et nous assurer que nos scripts seraient dignes de confiance, nous avons donc construit notre propre framework de test :

1. Chaque table source aurait un CSV synthétique, avec beaucoup d'utilisateurs et de colonnes remplies. Pour chaque nouveau scénario de test, nous ajoutons des lignes aux tables source appropriées.
2. Lorsqu'un test était exécuté, l'ensemble du jeu de données était chargé dans des tables temporaires de l'entrepôt de données.
3. Pour chaque script, les sources étaient textuellement remplacées par la table temporaire, et le script était exécuté.
4. Pour chaque script, nous avons créé un CSV de sortie attendu et l'avons comparé avec la sortie réelle, en signalant toute différence.

La création des fichiers CSV prenait beaucoup de temps, car nous souhaitions calculer le nombre d'utilisateurs actifs sur des fenêtres de 1 jour, 7 jours et 28 jours. Nous aurions donc besoin de créer de nombreuses lignes avec des événements de journal uniquement pour tester un script simple qui ajouté une nouvelle dimension. Nous avons réutilisé autant que possible, y compris en utilisant la sortie attendue écrite pour un test comme entrée pour un autre, si cela avait du sens.

Panne

Au bout d'un moment, l'ajout d'un nouveau cas de test devenait trop fragile , car vous pouviez potentiellement affecter un certain nombre d'autres tests ! Si le test A doit calculer une fréquence relative, et pour cela compté toutes les lignes avec COUNT (*), au moment où nous ajoutons une nouvelle ligne à l'ensemble de données "or" pour le test B, la sortie attendue du test A se cassera.

Après de longs soupirs, nous avons mordu la balle et divisé le jeu de données en or en un jeu de données par script . Ce n'était toujours pas parfait car nous avons ajouté plusieurs cas de test par script, mais cela est devenu plus gérable. Avec cela, nous avons appris:

• Isolez les tests autant que possible . Un test unitaire a de la valeur car il se brise lorsqu'une seule hypothèse de composant ne tient plus. Le faire casser parce qu'un test non lié a modifié l'environnement partagé n'est pas utile.
• CSV est un format de fichier terrible . Sérieusement. Si nous voulions tester le comportement de la logique avec une chaîne NULL par rapport à une chaîne vide, nous devions introduire une valeur spéciale et une logique de conversion à l'étape de téléchargement. Pas étonnant que pandas.read_csv dispose de 52 arguments pour gérer tous les cas que CSV ne parvient pas à normaliser.
• Bien que les tests bénéficient de simplicité et d'explicitation, les données synthétiques sont trop verbeuses et rendent difficile la détection de ce qui diffère d'un test à l'autre. Nous nous sommes fortement appuyés sur les commentaires pour préciser quelle était l'intention de chaque ligne et cas de test (encore une fois, pas quelque chose de standardisé dans CSV).

Deuxième tentative : signature du client avec JSON

Dans ma deuxième expérience, je travaillais en tant que consultant ingénieur de données chez Zup Innovation, affecté à une équipe à Itaú, la plus grande banque d'Amérique latine. Nous travaillions avec un déploiement Hadoop sur site.

Le type de travail ici était très différent de l'analyse, consistant principalement à aplatir et à dédupliquer les enregistrements. Le principal défi provenait des scripts étant incrémentiels , la sortie d'une transformation donnée dépendant de la sortie de la précédente. Nous le testons généralement en deux étapes : nous appelons T0 une exécution sans état précédent et T1 une exécution basée sur T0.

Comme auparavant, nous souffrions de ne trouver des bogues qu'après le déploiement et nous voulions nous assurer que tous les cas critiques étaient couverts pendant le développement. L'équipe d'analystes métier a préparé une longue liste de cas de test, pour chacun d'eux spécifiant si une colonne aurait une bonne ou une mauvaise valeur, et quel était le résultat attendu. Notez qu'il ne s'agissait que de descriptions sur un tableau, mais tout de même assez proche d'un test paramétré .

La principale difficulté était de traduire cette liste de cas de test en entrées Hadoop réelles ! Au début, nous avions le processus suivant :

1. Nous avons créé un fichier Excel partagé où chaque onglet était un tableau, soit à partir de la première (T0) ou de la deuxième exécution (T1). Chaque cas de test deviendrait 0 à 2 lignes dans chaque table.
2. Nous produisons chaque table T0 au format CSV, effectuons des transformations cosmétiques, les téléchargeons sur Hadoop et exécutons le script SQL. La sortie T0 est collectée.
3. Nous produisons chaque table T1 au format CSV, effectuons des transformations cosmétiques, les téléchargeons sur Hadoop et exécutons le script SQL. La sortie T1 est collectée.

Hacker un générateur de table

Frustré par les allers-retours causés par ces erreurs, j'ai construit un petit outil de synthèse, qui prendrait en entrée des fichiers JSON décrivant un cas de test , puis sortirait toutes les tables dont nous avions besoin à la fois . Au début, j'espérais qu'il serait utilisé par les équipes d'ingénierie et d'assurance qualité, mais l'un des analystes commerciaux était techniquement avisé et comprenait JSON, et a aidé à convertir ses propres cas de test dans ce format ! Un fichier avec un cas de test ressemblait à ceci :

{
  "companies_t0": [
    {"company_id": "001-3", "company_name": "ACME Inc."},
    {"company_id": "002-9", "company_name": "Umbrella LLC"}
  ],
  "companies_t1": [
    {"company_id": "001-3", "company_name": "ACME Inc."}
  ],
  "associates_t0": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"}
  ],
  "associates_t1": [
    {"company_id": "001", "person_id": "p01"},
    {"company_id": "001", "person_id": "p02"},
    {"company_id": "002", "person_id": "p02"} 
  ],
  "persons": [...]
}

L'outil a collecté toutes les lignes de chaque cas de test et créé des fichiers Companies_t0.csv, Companies_t1.csv, etc., qui ont ensuite été téléchargés sur Hadoop. Lorsqu'une erreur était découverte, nous pouvions passer au fichier JSON et vérifier s'il s'agissait d'une erreur dans le code du cas de test, ce qui était beaucoup plus facile à faire que de rechercher la ligne spécifique dans de nombreux onglets Excel qui auraient pu affecter un cas de test donné. .

Une autre caractéristique de cet outil permettait à chaque ligne « d'hériter » des colonnes d'un modèle donné. Cela rendait les cas de test agréables à lire, car ils n'incluaient que les colonnes qui les concernaient.

Nouvelles leçons

Je ne peux pas dire que cet outil a été un succès. Tout d'abord, peu de gens l'ont adopté, et j'aurais dû mieux communiquer avec les ingénieurs et l'assurance qualité pour savoir comment cela pouvait s'intégrer dans le processus global. Deuxièmement, il a introduit de nouvelles opportunités d'erreurs manuelles, comme la création de cas avec des identifiants en double, en laissant les colonnes inchangées lors du copier-coller ou en modifiant les fichiers CSV directement au lieu de leurs progéniteurs JSON.

J'en ai tiré les enseignements suivants :

• L'entreprise pense dans des cas qui s'étendent sur des tables, et le code pense dans des tables qui contiennent des cas de test . Il y a une transposition à faire entre eux lors de la création d'un harnais de test.
• Que le client écrive des cas de test pour vous, c'est de l'or . Personne ne sait mieux quelles sont les entrées pertinentes et les sorties attendues que l'expert du domaine ! Ils ne pensent peut-être pas à tous les cas extrêmes que le code doit gérer, mais ils peuvent fournir un aperçu avec une poignée de cas de test sur des transformations délicates. De plus, l'établissement d'un langage partagé d'entrées-sorties avec eux vous aide à communiquer ces cas extrêmes plus tard !
• L'exécution simultanée de tous les cas de test rend la vérification et le débogage plus difficiles . Nous devions conserver un mappage entre les cas de test de l'ID utilisateur pour mapper les résultats aux cas de test et les vérifier. Nous avons essayé d'ajouter le numéro de cas de test à l'ID utilisateur, en étiquetant les données "in-band", mais cela n'a fait qu'apporter plus de problèmes avec les ID invalides. Il aurait été préférable d'exécuter chaque cas séparément ou d'avoir un moyen de suivre à quel cas correspond une sortie donnée.
• JSON n'est pas un bon format pour les humains. Dans l'exemple ci-dessus, ce qui est testé n'est pas explicite et JSON n'autorise pas les commentaires pour l'expliquer. Oui, nous pouvons utiliser un "_comment": "blah"champ, mais c'est un hack qui doit être annulé lors de la conversion en CSV. De plus, garder une trace des virgules et citer les clés d'objet est légèrement exaspérant .
• Les modèles de lignes améliorent considérablement la lisibilité. Bien que nous aimerions que les tests soient très explicites et avec peu de logique cachée, l'utilisation d'un modèle pour remplir les colonnes inintéressantes aide beaucoup à repérer les différences entre les valeurs qui sont en effet importantes pour le cas de test en cours.

Cette troisième et dernière expérience s'est passée chez mon ancien employeur, Cherre, qui fournit la connexion de données pour les sociétés immobilières. Nous avons principalement utilisé dbt pour écrire des transformations dans Google BigQuery, l'offre commerciale de Dremel. Un client nous avait demandé de modifier considérablement la logique (heureusement, en la simplifiant !), et nous voulions nous assurer de ne pas introduire de changement involontaire.

Réutilisation des modèles dbt

Étant donné que la logique existante n'a pas été testée à l'unité, j'ai commencé par un test de fumée , qui consiste simplement à créer des entrées et à s'assurer que rien ne les rompt. Le graphique de dépendance du modèle testé était profond, se terminant par environ 20 sources, donc ce serait un casse-tête de se moquer source par source. Au lieu de cela, j'ai choisi un seuil DAG approprié et remplacé seulement ~7 références par des données synthétiques, créées avec SQL lui-même :

WITH
-- Synthetic sources
foo AS (
  -- Test case 1, active foo
  SELECT "i001" AS foo_id, 200_000 AS foo_value, TRUE AS is_active,
  UNION ALL
  -- Test case 2, inactive foo without any bar
  SELECT "i002" AS foo_id, 240_000 AS foo_value, FALSE AS is_active,
),
bar AS (
  -- Test case 1, active foo with two bars
  SELECT "b001" AS bar_id, 451.40 AS bar_value, "i001" AS foo_id
  UNION ALL
  SELECT "b002" AS bar_id, 123.98 AS bar_value, "i001" AS foo_id
),
-- From now on, everything should be left the same as the DBT model.
model_x AS (
  SELECT ...
  FROM foo
  JOIN bar USING (foo_id)
)

Pour mieux visualiser et partager les entrées et les sorties, j'ai créé une copie du script pour chacun des CTE (c'est-à-dire chacune des tables intermédiaires) et l'ajoute SELECT * FROM <cte>à la fin. J'ai ensuite exécuté chaque copie, obtenant un CSV pour chaque transformation intermédiaire - foo.csv, bar.csv, model_x.csv, etc. les gens quels seraient les résultats des changements logiques, compte tenu de certaines entrées synthétiques, au format CSV.

Gains limités

En fin de compte, ce test a réussi à garantir que nous n'ajouterions aucune erreur avec le changement de logique, puisque les mêmes entrées ont été utilisées pour la logique existante et la nouvelle. Cela a également aidé à déboguer un problème de production plus tard, en nous permettant de le reproduire avec des données synthétiques. Malheureusement, puisqu'il a été construit dans un but précis, nous n'avons pas tenté de le généraliser pour écrire des tests dans d'autres modèles.

Les principales leçons à ce jour ont été :

• SQL est l'option avec la moindre inadéquation de résistance pour déclarer des données synthétiques. En écrivant les données synthétiques en SQL pur, nous n'avons pas à nous soucier du casting, des NULL, des commentaires, de la lisibilité… puisque la syntaxe même du langage les fournit déjà !
• Il est important de se moquer des références. La partie la plus complexe de la logique SQL se situe à la fin d'un grand DAG, et devoir se moquer de chacune des sources aurait été contre-productif. En se moquant des sources, on pourrait tester tous les modèles intermédiaires… mais je ne voulais pas tous les tester, juste les plus intéressants !
• Il est préférable de regrouper les lignes par cas de test plutôt que par table. J'avais déjà appris cela dans l'expérience précédente, mais je voudrais simplement répéter que même lorsque des cas de test sont écrits en SQL avec des commentaires, un certain contexte est toujours perdu lors de la lecture des cas et du retour en arrière des sorties aux tests.

Je ne sais pas si nous pouvons trouver une solution parfaite pour les tests unitaires SQL, mais nous pouvons toujours en rêver :

• Les cas de test s'exécuteraient isolément ;
• Chaque sortie indiquerait de quel cas elle provient;
• Les colonnes sans intérêt peuvent être remplies automatiquement et/ou ignorées ;
• Toute la logique est réutilisée, aucune adaptation ne serait nécessaire pour les tests ;
• Le point de coupure du graphique de dépendance peut être arbitraire ;
• Les cas de test et leurs résultats seraient compréhensibles - et inscriptibles - par un expert du domaine/spécialiste métier ;

Épilogue

C'est tout, merci d'avoir lu ! Si vous êtes arrivé jusqu'ici, veuillez laisser 10 applaudissements pour aider cet article à être vu par d'autres personnes comme vous. Plus important encore, partagez vos expériences passées avec les tests unitaires en ingénierie de données, je ne fais que commencer et tout rapport est le bienvenu !

Merci à David Barer, Bruno Santos et Rodrigo Donizetti pour votre disponibilité et la révision des brouillons de cet article.