Après des années de travail sur la science et l'ingénierie des données, la qualité des données est le fantôme suspendu qui apparaît dans presque tous les projets, décimant la réussite des entreprises.

SQL est le langage de facto des données. Une façon d'améliorer la qualité des données consiste à améliorer la base de code SQL avec des tests unitaires et des tests de données. Cet article est principalement inspiré du post de Mme Gao .

Dans cet article, la logique fondamentale du test unitaire sur SQL avec dbt sera illustrée avec un jeu de données simple.

Idée basique

L'idée de base d'effectuer des tests unitaires sur SQL est exactement la même que de faire des tests unitaires sur du code Python :

1. se moquer d'une entrée contrôlable D
2. il y a un module/fonction/algorithme testable , appelez-le A
3. calculer le résultat attendu en utilisant D comme entrée, obtenir O_should
4. comparer le résultat attendu (O_should) avec la sortie réelle de A (O_is)

Parfois, il n'est pas nécessaire que ce soit une correspondance exacte, c'est-à-dire qu'il ne corresponde qu'à 2 chiffres après la virgule.

Un exemple

Construisons un exemple naïf, pour illustrer l'idée ci-dessus. Nous avons besoin de la structure de dossier de projet dbt suivante

-- dbt_project.yml

-- data/
------ iris.csv
------ selected_iris_expected.csv

-- models/
------ iris/
---------- selected_iris.sql
---------- schema.yml

# example: dbt_project.yml

# take things under data/ as seeds
data-paths: ["data"]

# configure seed, all going into unittesting schema
seeds:
    schema: unittesting

-- selected_iris.sql
{{ config(
        materialized='table',
        schema='unittesting',
        tags=['iris']
    )
}}

-- count the number of special iris id (above average in all aspects)
-- not a very meaningful logic, just for exemplare purpose
SELECT
distinct count(distinct id)
FROM "public"."iris";
where sepallengthcm > 5.9 and sepalwidthcm > 3.1 and petallengthcm > 3.8 and petalwidthcm > 1.2
-- selected_iris_expected.csv
count
150

# schema.yml 

version: 2

# table model selected_iris should be equal to iris
models:
  - name: selected_iris

    tests:
      - dbt_utils.equality:
          compare_model: ref('selected_iris_expected')
          tags: ['unit_testing']

Étape 1 : charger les données de test dans la base de données

# here we use
# iris.csv will be loaded into unittesting.iris table
# selected_iris_expected.csv will be loaded into unittesting.selected_iris_expected table
dbt seed
# build selected_iris model into unittesting.selected_iris
dbt run

Étape 2 : comparer

# here we use
# all tests within folder model/iris/ with be executed
# of course, we can restrict to only unittesting using tags
dbt test --model iris

Technicités

Les tests unitaires SQL ont l'air simples, n'est-ce pas ? mais en réalité la chose pourrait être plus complexe :

• vous avez probablement besoin d'une structure de dossiers plus sophistiquée (utilisant des sous-dossiers imbriqués) pour séparer et organiser les tests en fonction des projets
• vous devrez peut-être activer/désactiver les tests unitaires en fonction de l'environnement dev/prod actuel
• vous pouvez avoir une grande table d'entrée qui est difficile à simuler
• vous pouvez avoir un modèle complexe, sur lequel il est difficile de calculer le résultat attendu à l'avance (modèle non testable)
• le résultat attendu peut ne pas correspondre à 100 % à la sortie réelle (même s'ils sont pratiquement identiques) en raison de la précision des nombres flottants, etc.
• ou vous n'avez peut-être tout simplement pas de budget temps dans ce projet, ce qui n'est pas rare, les gens ne testent pas beaucoup sql en 2022, les instructions SQL sont considérées comme correctes après avoir été écrites

Un conseil : modularisez les instructions SQL

Malgré tous les défis énumérés ci-dessus, une chose aide les tests unitaires dans le cas de SQL ainsi que de tout autre langage de programmation plus général comme Python.

C'est la modularisation. Un bon modèle/fonction/algorithme modularisé garantit la testabilité et la lisibilité, même pour sql.

Il existe de nombreuses façons de réaliser cela avec sql et dbt :

• macro dbt (https://docs.getdbt.com/docs/build/jinja-macros)
• avec déclaration (https://learnsql.com/blog/what-is-with-clause-sql/)

Conclusion

SQL est le langage natif des données, dans cet article, nous avons démontré un moyen de faire des tests unitaires SQL avec dbt.

De même, nous pourrions également faire des tests d'intégration de données ou d'autres astuces comme déléguer la création de sql à un langage plus puissant comme python en utilisant rasgoQ L.