Recentemente stavo discutendo con un collega su come eseguire unit test in SQL e mi sono reso conto che, sebbene sia ancora all'inizio della mia carriera nel campo dei dati, ho già riscontrato questo problema alcune volte e in ogni turno ho avuto un approccio diverso a risolvilo. Ho pensato che potesse essere utile condividere i miei trucchi in modo da poter consolidare ciò che ho imparato, e anche per imparare da te lettore cosa hai fatto e come è andata. Lo facciamo?

Ma perché unit test SQL?

È importante chiarire che sto discutendo qui di testare la logica SQL , non di testare i dati . Testare i dati garantisce che le supposizioni per le proprietà dello schema, come la nullità e l'unicità, valgano effettivamente per i valori a portata di mano. Un RDBMS tradizionale lo garantirebbe con i vincoli di tabella, ma non abbiamo questa funzione nei data warehouse, quindi dobbiamo effettivamente leggere e controllare le pre e post condizioni sul set di dati.

L'SQL di test unitario è uguale al codice dell'applicazione di test unitario: assicurati che un pezzo di logica produca l'output previsto per un dato insieme di input. Mentre le migliori pratiche impongono di testare in unità quasi tutto il codice dell'applicazione, sia esso C++ o Python, raramente lo vediamo tentato con SQL. Perché?

La risposta, per me, è che scrivere SQL non è la parte più difficile . La maggior parte delle volte, le trasformazioni dei dati sono banali e ripetitive e la difficoltà sta più nel modellare i dati in modo appropriato che nello spostare i bit da A a B. Tuttavia, quando la logica aziendale è necessaria all'interno delle trasformazioni, potresti ritrovarti con la complessità necessaria che è difficile separare — come una complessa espressione CASE-WHEN per generare una colonna, con input provenienti da un scoraggiante JOIN a sette vie e una clausola WHERE su più righe che garantisce una tabella di verità per capire cosa sta succedendo...

Nella mia esperienza, garantire che la logica sia valida si basava solo sul test manuale e sull'approvazione del client, che è 1) fragile e 2) quasi criminale considerando quanto sia adatto ai test unitari SQL! Mentre le app richiedono tutti i tipi di impalcature per limitare lo stato mutabile e garantire la riproducibilità nei test, SQL è puro ed esplicito. Allora perché non utilizzare la stessa infrastruttura per verificare che la logica funzioni come previsto?

Primo tentativo: analisi

Il mio primo tentativo di unit test degli script SQL è stato quando lavoravo in Google, utilizzando tecnologie interne come Dremel e Powerdrill (noterai che i loro progetti avevano nomi relativi al lavoro con i log ).

Stavamo costruendo una pipeline di analisi per una nuova app, elaborando i log degli utenti per generare metriche care ai product manager, come utenti attivi per paese o coorte. Una query rappresentativa sarebbe:

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

Hacking di un framework di test

Volevamo fare di meglio e assicurarci che i nostri script fossero affidabili, quindi abbiamo creato il nostro framework di test:

1. Ogni tabella di origine avrebbe un CSV sintetico, con molti utenti e colonne compilate. Per ogni nuovo caso di test, aggiungeremo righe alle tabelle di origine appropriate.
2. Quando è stato eseguito un test, l'intero set di dati è stato caricato in tabelle temporanee nel data warehouse.
3. Per ogni script, le fonti venivano sostituite testualmente dalla tabella temporanea e lo script veniva eseguito.
4. Per ogni script, abbiamo creato un CSV di output previsto e lo abbiamo differenziato con l'output effettivo, segnalando eventuali differenze.

La creazione dei CSV richiedeva molto tempo perché eravamo interessati a calcolare il numero di utenti attivi in ​​finestre di 1 giorno, 7 giorni e 28 giorni, quindi avremmo dovuto creare molte righe con eventi di log solo per testare un semplice script che aggiunto una nuova dimensione. Abbiamo riutilizzato il più possibile, incluso l'utilizzo dell'output previsto scritto per un test come input per un altro, se aveva senso.

Guasto

Dopo un po', l'aggiunta di un nuovo caso di test è diventata troppo fragile , dal momento che potresti potenzialmente influire su un numero qualsiasi di altri test! Se il test A ha bisogno di calcolare una frequenza relativa, e per questo ha contato tutte le righe con COUNT(*), nel momento in cui abbiamo aggiunto una nuova riga al set di dati "d'oro" per il test B, l'output previsto dal test A si interromperebbe.

Dopo molti sospiri, mordiamo il proiettile e dividiamo il set di dati d'oro in un set di dati per script . Non era ancora perfetto perché abbiamo aggiunto più casi di test per script, ma è diventato più gestibile. Con ciò abbiamo appreso:

• Isolare i test il più possibile . Un test unitario ha valore perché si interrompe quando le ipotesi di un singolo componente non sono più valide. Non è utile interromperlo perché alcuni test non correlati hanno modificato l'ambiente condiviso.
• CSV è un formato di file terribile . Sul serio. Se volessimo testare come si è comportata la logica con una stringa NULL rispetto a una stringa vuota, dovremmo introdurre un valore speciale e una logica di conversione nella fase di caricamento. Non c'è da stupirsi che pandas.read_csv abbia 52 argomenti per gestire tutti i casi che CSV non riesce a standardizzare.
• Sebbene i test traggano vantaggio dalla semplicità e dall'esplicitezza, i dati sintetici sono troppo dettagliati e rendono difficile individuare le differenze tra un test e l'altro. Abbiamo fatto molto affidamento sui commenti per chiarire quale fosse l'intenzione di ogni riga e caso di test (di nuovo, non qualcosa di standardizzato in CSV).

Secondo tentativo: approvazione del client con JSON

Nella mia seconda esperienza, lavoravo come consulente di data engineer presso Zup Innovation, assegnato a un team di Itaú, la più grande banca dell'America Latina. Stavamo lavorando con una distribuzione Hadoop on-premise.

Il tipo di lavoro qui era molto diverso dall'analisi e consisteva principalmente nell'appiattimento e nella deduplicazione dei record. La sfida principale derivava dal fatto che gli script fossero incrementali , con l'output di una data trasformazione che dipendeva dall'output della precedente. Di solito lo testiamo in due fasi: chiamiamo T0 un'esecuzione senza stato precedente e T1 un'esecuzione basata su T0.

Come prima, soffrivamo per la ricerca di bug solo dopo l'implementazione e volevamo assicurarci che tutti i casi limite fossero coperti durante lo sviluppo. Il team di analisti aziendali ha preparato un ampio elenco di casi di test, specificando per ognuno di essi se una colonna avrebbe un valore buono o cattivo e qual era l'output previsto. Si noti che queste erano solo descrizioni su una tabella, ma comunque abbastanza vicine a un test parametrizzato .

La difficoltà principale è stata tradurre questo elenco di casi di test in effettivi input Hadoop! All'inizio abbiamo avuto il seguente processo:

1. Abbiamo creato un file Excel condiviso in cui ogni scheda era una tabella, dalla prima (T0) o dalla seconda esecuzione (T1). Ogni test case diventerebbe 0-2 righe in ogni tabella.
2. Produciamo ogni tabella T0 come CSV, eseguiamo trasformazioni cosmetiche, le carichiamo su Hadoop ed eseguiamo lo script SQL. L'output T0 viene raccolto.
3. Produciamo ogni tabella T1 come CSV, eseguiamo trasformazioni cosmetiche, le carichiamo su Hadoop ed eseguiamo lo script SQL. L'uscita T1 viene raccolta.

Hacking di un generatore di tabelle

Frustrato dall'andirivieni causato da questi errori, ho creato un piccolo strumento di sintesi, che avrebbe preso come input i file JSON che descrivevano un caso di test e quindi avrebbe prodotto tutte le tabelle di cui avevamo bisogno contemporaneamente . All'inizio speravo che sarebbe stato utilizzato dai team eng e QA, ma uno degli analisti aziendali era tecnicamente esperto e comprendeva JSON e ha contribuito a convertire i propri casi di test in questo formato! Un file con un test case si presentava così:

{
  "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": [...]
}

Lo strumento ha raccolto tutte le righe da ogni test case e ha creato i file companies_t0.csv, companies_t1.csv, ecc., che sono stati poi caricati su Hadoop. Quando veniva scoperto un errore, potevamo passare al file JSON e verificare se si trattava di un errore nel codice del test case, che era molto più facile da fare rispetto alla ricerca della riga specifica all'interno di molte schede di Excel che avrebbero potuto influire su un determinato test case .

Un'altra caratteristica di questo strumento era consentire a ciascuna riga di "ereditare" le colonne da un determinato modello. Ciò ha reso i casi di test piacevoli da leggere, in quanto includevano solo le colonne pertinenti.

Nuove lezioni

Non posso dire che questo strumento sia stato un successo. Innanzitutto, poche persone l'hanno adottato e avrei dovuto comunicare meglio con gli ingegneri e il QA come poteva adattarsi al processo generale. In secondo luogo, ha introdotto nuove opportunità di errore manuale, come la creazione di casi con ID duplicati, lasciando le colonne invariate al copia-incolla o modificando direttamente i file CSV invece dei loro progenitori JSON.

Ne ho ricavato le seguenti lezioni:

• Il business pensa in casi che si estendono su tabelle e il codice pensa in tabelle che contengono casi di test . C'è una trasposizione da fare tra di loro durante la creazione di un'imbracatura di prova.
• Avere il cliente che scrive casi di test per te è oro . Nessuno sa meglio quali sono gli input rilevanti e gli output attesi dell'esperto di dominio! Potrebbero non pensare a tutti i casi limite che il codice deve gestire, ma possono fornire informazioni con una manciata di casi di test su trasformazioni complicate. Inoltre, stabilire un linguaggio condiviso di input-output con loro ti aiuta a comunicare quei casi limite in un secondo momento!
• L'esecuzione di tutti i casi di test contemporaneamente rende il controllo e il debug più difficili . Abbiamo dovuto mantenere una mappatura tra il caso di test dell'ID utente per mappare i risultati sui casi di test e controllarli. Abbiamo provato ad aggiungere il numero del test case all'ID utente, contrassegnando i dati "in-band", ma questo ha portato solo più problemi con ID non validi. Sarebbe stato meglio eseguire ogni caso separatamente o avere un modo per tenere traccia del caso a cui corrisponde un determinato output.
• JSON non è un buon formato per gli umani. Nell'esempio sopra, non è esplicito ciò che viene testato e JSON non consente commenti per spiegarlo. Sì, possiamo usare un "_comment": "blah"campo, ma è un hack che deve essere annullato nella conversione in CSV. Inoltre, tenere traccia delle virgole e citare le chiavi degli oggetti è leggermente esasperante .
• I modelli di riga migliorano notevolmente la leggibilità. Sebbene vorremmo che i test fossero molto espliciti e con poca logica nascosta, l'utilizzo di un modello per compilare colonne non interessanti aiuta molto a individuare le differenze tra i valori che sono davvero importanti per il caso di test in questione.

Questa terza e ultima esperienza è avvenuta presso il mio precedente datore di lavoro, Cherre, che fornisce connessione dati per società immobiliari. Abbiamo utilizzato principalmente dbt per scrivere trasformazioni in Google BigQuery, l'offerta commerciale di Dremel. Un cliente ci aveva richiesto di modificare notevolmente la logica (per fortuna, rendendola più semplice!), e volevamo assicurarci di non introdurre modifiche non intenzionali.

Riutilizzo dei modelli dbt

Poiché la logica esistente non è stata testata in unità, ho iniziato con uno smoke test , che consiste semplicemente nel creare input e assicurarsi che nulla si rompa con essi. Il grafico delle dipendenze del modello in prova era profondo, terminando con ~ 20 fonti, quindi sarebbe stato un mal di testa deridere fonte per fonte. Invece, ho scelto un taglio DAG appropriato e ho sostituito solo ~7 ref con dati sintetici, creati con SQL stesso :

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)
)

Per visualizzare e condividere meglio gli input e gli output, ho creato una copia dello script per ciascuna delle CTE (ovvero ciascuna delle tabelle intermedie) e aggiunta SELECT * FROM <cte>alla fine. Ho quindi eseguito ogni copia, ottenendo un CSV per ogni trasformazione intermedia: foo.csv, bar.csv, model_x.csv, ecc. persone quali sarebbero i risultati delle modifiche logiche, dati alcuni input sintetici, in formato CSV.

Guadagni limitati

Alla fine, questo test è riuscito a garantire che non avremmo aggiunto alcun errore con la modifica della logica, poiché gli stessi input sono stati utilizzati per la logica esistente e per quella nuova. Ha anche aiutato a eseguire il debug di un problema di produzione in un secondo momento, consentendoci di riprodurlo con dati sintetici. Sfortunatamente, poiché è stato creato per uno scopo specifico, non abbiamo tentato di generalizzarlo per scrivere test in altri modelli.

Le lezioni principali finora sono state:

• SQL è l'opzione con meno corrispondenza di resistenza per dichiarare dati sintetici. Scrivendo i dati sintetici in puro SQL non dobbiamo preoccuparci di casting, NULL, commenti, leggibilità... visto che la stessa sintassi del linguaggio li fornisce già!
• È importante deridere gli arbitri. La parte più complessa della logica SQL si trova alla fine di un grande DAG e dover deridere ciascuna delle fonti sarebbe stato controproducente. Prendendo in giro le fonti, saremmo in grado di testare tutti i modelli intermedi... ma non volevo testarli tutti, solo quelli interessanti!
• Raggruppare le righe per casi di test è meglio che per tabella. L'avevo già imparato nell'esperienza precedente, ma vorrei solo ribadire che anche quando si scrivono casi di test in SQL con commenti, si perde ancora parte del contesto durante la lettura dei casi e il backtracking dagli output ai test.

Non so se possiamo raggiungere una soluzione perfetta per unit test SQL, ma possiamo ancora sognarlo:

• I casi di test verrebbero eseguiti in isolamento;
• Ogni output indicherebbe da quale caso proviene;
• Le colonne non interessanti possono essere riempite automaticamente e/o ignorate;
• Tutta la logica viene riutilizzata, non sarebbero necessari adattamenti per i test;
• Il punto limite del grafico delle dipendenze può essere arbitrario;
• I casi di test ei relativi risultati sarebbero comprensibili e scrivibili da un esperto di dominio/specialista aziendale;

Epilogo

Questo è tutto, grazie per aver letto! Se sei arrivato fin qui, per favore lascia 10 applausi per aiutare questo articolo a essere visto da altre persone come te. Ancora più importante, condividi le tue esperienze passate con i test unitari nell'ingegneria dei dati, sto ancora iniziando e qualsiasi segnalazione è benvenuta!

Grazie a David Barer, Bruno Santos e Rodrigo Donizetti per la disponibilità e la revisione delle bozze di questo post.