Dopo anni di lavoro sulla scienza e l'ingegneria dei dati, la qualità dei dati è il fantasma sospeso che appare in quasi tutti i progetti, decimando i risultati aziendali.

SQL è il linguaggio de facto di Data. Un modo per migliorare la qualità dei dati è migliorare la base di codice SQL con unit test e test dei dati. Questo articolo è in gran parte ispirato dal post della signora Gao .

In questo articolo verrà illustrata la logica fondamentale dello unit test su SQL con dbt con un semplice set di dati.

Idea base

L'idea di base di condurre unit test su SQL è esattamente la stessa di eseguire unit test su codice Python:

1. deridere un input controllabile D
2. c'è un modulo/funzione/algoritmo testabile , chiamalo A
3. calcola il risultato atteso usando D come input, ottieni O_should
4. confrontare il risultato atteso (O_should) con l'output effettivo di A (O_is)

A volte non deve essere una corrispondenza esatta, cioè solo fino a 2 cifre dopo la virgola.

Un esempio

Costruiamo un esempio ingenuo, per illustrare l'idea di cui sopra. Abbiamo bisogno della seguente struttura di cartelle del progetto dbt

-- 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']

Passaggio 1: caricare i dati del test nel database

# 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

Passaggio 2: confrontare

# 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

Tecnici

L'unità di test SQL sembra semplice, giusto? ma in realtà la cosa potrebbe essere più complessa:

• probabilmente hai bisogno di una struttura di cartelle più sofisticata (usando sottocartelle nidificate) per separare e organizzare i test in base ai progetti
• potrebbe essere necessario attivare/disattivare i test unitari in base all'ambiente dev/prod corrente
• potresti avere una grande tabella di input che è difficile da deridere
• potresti avere un modello complesso, sul quale è difficile calcolare in anticipo il risultato atteso (modello non testabile)
• il risultato atteso potrebbe non corrispondere al 100% con l'output effettivo (anche se sono praticamente gli stessi) a causa della precisione del numero mobile ecc.
• o potresti semplicemente non avere un budget di tempo in questo progetto, il che non è raro, le persone non testano molto sql nel 2022, le istruzioni SQL sono considerate corrette dopo essere state scritte

Un consiglio: modularizza le istruzioni SQL

Nonostante tutte le sfide sopra elencate, una cosa aiuta con i test unitari nel caso di SQL e di qualsiasi altro linguaggio di programmazione più generale come Python.

Questa è la modularizzazione. Un buon modello/funzione/algoritmo modularizzato garantisce la testabilità e la leggibilità, anche per sql.

Ci sono molti modi per realizzarlo con sql e dbt:

• macro dbt (https://docs.getdbt.com/docs/build/jinja-macros)
• con dichiarazione (https://learnsql.com/blog/what-is-with-clause-sql/)

Conclusione

SQL è il linguaggio nativo dei dati, in questo articolo abbiamo dimostrato un modo per eseguire test unitari SQL con dbt.

Allo stesso modo potremmo anche eseguire test di integrazione dei dati o altri trucchi come delegare la creazione di sql a un linguaggio più potente come Python usando rasgoQ L.