Testing, refactoring e reusable SQL patterns

Una query mensile ormai decide bonus, forecast e priorità prodotto, ma nessuno osa modificarla: troppe CTE anonime, filtri ripetuti, nessun test e nessuna certezza sul significato dei campi. Testing, refactoring e reusable SQL patterns nasce in quel punto, quando SQL smette di essere uno script personale e diventa un artefatto condiviso.

Una scena da cui partire

Leggi questa lezione come un lavoro di manutenzione professionale. L’obiettivo non è rendere una query più elegante per gusto, ma renderla controllabile: ogni assunzione deve avere un test, ogni trasformazione importante deve avere un nome e ogni pattern riusabile deve ridurre il rischio di interpretazioni diverse.

• Contesto: Quale metrica rischia di rompersi se la query cambia?
• Metodo: Quale test intercetta il problema prima del dashboard?
• Applicazione: Quale pattern renderesti riusabile per il prossimo analyst?

Testing dei dati: non solo “la query gira”

Il testing in SQL analitico si articola su tre livelli:

Livello 1: Test di integrità (dbt: unique, not_null)

Garantiscono che ogni riga di un modello sia univocamente identificabile e che le colonne critiche non siano NULL. Sono il minimo indispensabile:

-- Test: ogni order_id è unico
SELECT order_id, COUNT(*)
FROM orders_clean
GROUP BY order_id
HAVING COUNT(*) > 1;
-- Se restituisce righe → fallito

-- Test: ogni order_id ha customer_id non nullo
SELECT COUNT(*) AS null_customer_ids
FROM orders_clean
WHERE customer_id IS NULL;
-- Se > 0 → fallito

Livello 2: Test di logica di business (dbt: accepted_values, relationships)

Validano regole di dominio. Il valore di status deve essere tra quelli attesi; ogni customer_id deve esistere nella tabella customers:

-- Test: status deve essere uno di questi valori
SELECT DISTINCT status
FROM orders_clean
WHERE status NOT IN ('pending', 'completed', 'cancelled', 'refunded');

-- Test: integrità referenziale
SELECT o.order_id
FROM orders_clean o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Livello 3: Test di qualità statistica

Qui entriamo nel territorio avanzato. Si possono testare proprietà statistiche dei dati che dovrebbero essere stabili nel tempo:

• Il numero di righe per giorno non deve scendere del 50% rispetto alla media mobile
• La distribuzione di una colonna categorica non deve cambiare più del 5% rispetto al periodo precedente
• La media di una metrica non deve superare 3 deviazioni standard dalla media storica

WITH daily_stats AS (
  SELECT dt, COUNT(*) AS row_count,
    AVG(COUNT(*)) OVER (
      ORDER BY dt ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING
    ) AS avg_14d,
    STDDEV(COUNT(*)) OVER (
      ORDER BY dt ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING
    ) AS stddev_14d
  FROM orders_clean
  GROUP BY dt
)
SELECT dt, row_count, avg_14d,
  CASE WHEN row_count < avg_14d - 2 * stddev_14d
    THEN 'ALERT: volume anomalo' END AS alert
FROM daily_stats
ORDER BY dt DESC
LIMIT 5;

Refactoring SQL: estratto, rinomina, riusa

Il refactoring del codice SQL è storicamente trascurato perché “è solo una query”. Ma le query analitiche vivono in produzione per mesi o anni, vengono modificate da persone diverse, e accumulano complessità accidentale. Le tre operazioni fondamentali di refactoring SQL sono:

1. Estrai subquery in CTE con nomi parlanti

Query prima del refactoring:

SELECT DATE_TRUNC('month', created_at) AS month,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue,
  COUNT(DISTINCT user_id) AS users
FROM orders
WHERE order_type = 'subscription'
  AND created_at >= '2024-01-01'
GROUP BY 1;

Dopo refactoring:

WITH subscription_orders AS (
  SELECT *
  FROM orders
  WHERE order_type = 'subscription'
    AND created_at >= '2024-01-01'
),
monthly_metrics AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue,
    COUNT(DISTINCT user_id) AS users
  FROM subscription_orders
  GROUP BY 1
)
SELECT * FROM monthly_metrics;

Ogni CTE ha un nome che ne descrive il contenuto. Se qualcuno deve modificare la logica del filtro, sa esattamente dove guardare.

2. Unisci query duplicate identificando il pattern comune

Spesso due query sono quasi identiche tranne per un filtro o un’aggregazione. Invece di mantenerle separate, si può creare un modello intermedio parametrizzabile.

3. Separa logica di business da logica di presentazione

La logica di business (“cos’è un cliente attivo?”) va in una CTE/model upstream. La logica di presentazione (“mostra top 10 per revenue”) va nella query finale.

Pattern riusabili in SQL analitico

I pattern che seguono risolvono problemi ricorrenti in modo standardizzato.

Pattern: Flag binari per condizioni

Invece di ripetere CASE WHEN condition THEN 1 ELSE 0 END ovunque, crea flag in un modello intermedio:

WITH users_enriched AS (
  SELECT *,
    CASE WHEN last_login > CURRENT_DATE - INTERVAL '30 days'
      THEN 1 ELSE 0 END AS is_active_30d,
    CASE WHEN total_orders > 0 THEN 1 ELSE 0 END AS is_converted,
    CASE WHEN subscription_plan != 'free' THEN 1 ELSE 0 END AS is_paying
  FROM users
)
SELECT COUNT(*) FILTER (WHERE is_active_30d = 1 AND is_paying = 1) AS active_paying
FROM users_enriched;

Pattern: Snapshot per confronti temporali

Quando devi confrontare uno stato corrente con uno stato passato, usa le window function per creare snapshot:

SELECT user_id, current_plan,
  LAG(current_plan) OVER (PARTITION BY user_id ORDER BY snapshot_date) AS previous_plan,
  CASE WHEN current_plan != LAG(current_plan)
    OVER (PARTITION BY user_id ORDER BY snapshot_date)
    THEN 1 ELSE 0 END AS plan_changed
FROM subscription_snapshots;

Caso reale: GitLab e la cultura del data testing

GitLab, azienda da 580 milioni di revenue nel 2024, ha documentato pubblicamente la propria strategia di data testing nel handbook aziendale. Tutti i modelli dbt di GitLab hanno test not_null e unique sulle colonne primarie, test accepted_values sugli enum, e test di volume (row count) con soglie di alert.

Un incidente documentato del 2021: un cambiamento nello schema delle API di Salesforce fece sì che la colonna opportunity_amount iniziasse ad arrivare in euro invece che in dollari. Il test di volume non rilevò il problema (il numero di righe era stabile), ma un data analyst notò che le opportuinità europee avevano valori 15-20% più alti del previsto. Da quell’incidente, GitLab aggiunse un test di sanity check statistico: la media e la deviazione standard per paese non devono cambiare più del 10% settimana su settimana.

Il test, semplificato in SQL:

WITH weekly_stats AS (
  SELECT country, DATE_TRUNC('week', close_date) AS week,
    AVG(amount) AS avg_amount
  FROM opportunities
  GROUP BY country, week
),
week_over_week AS (
  SELECT country, week, avg_amount,
    LAG(avg_amount) OVER (PARTITION BY country ORDER BY week) AS prev_avg,
    (avg_amount - LAG(avg_amount) OVER (...)) / NULLIF(LAG(avg_amount) OVER (...), 0) AS pct_change
  FROM weekly_stats
)
SELECT * FROM week_over_week
WHERE ABS(pct_change) > 0.10;

Laboratorio pratico

Dataset: orders con 2M righe e schema noto.

Livello 1 — Test di base: Scrivi query di test per unique(order_id), not_null(customer_id), e accepted_values(status, ['pending','completed','cancelled']).

Livello 2 — Test di volume: Implementa un test che alza un alert se il numero di ordini di oggi è inferiore del 30% rispetto alla media degli ultimi 7 giorni.

Livello 3 — Refactoring di una query legacy: Ti viene data una query di 80 righe con subquery annidate. Rifattorizzala in CTE con nomi parlanti. Scrivi test per ogni CTE intermedia.

🖥️ Prova in Sandbox

[SQL]Prova tu

Test di integrità: scrivi una query che verifica se ci sono ordini duplicati (stesso utente_id, stessa data_ordine, stesso importo). Usa GROUP BY e HAVING COUNT(*) > 1.

SELECT utente_id, data_ordine, COUNT(*) AS n_ordini FROM ordini GROUP BY utente_id, data_ordine ORDER BY utente_id, data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Test di volume: calcola quanti ordini ci sono per categoria e confronta con la media. Segnala le categorie con meno del 30% della media — un possibile alert di dati mancanti.

SELECT categoria, COUNT(*) AS n_ordini FROM ordini GROUP BY categoria ORDER BY n_ordini

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Quali sono i tre livelli di testing in SQL analitico e cosa testa ciascuno?

2. Quali sono le tre operazioni fondamentali di refactoring SQL?

3. Come ha fatto GitLab a scoprire il bug del cambio valuta nelle API Salesforce e che test hanno aggiunto dopo?

---

Riferimenti accademici:

• dbt Labs. (2023). “Best Practices Guide: Testing.” dbt Documentation.
• GitLab. (2024). “Data Team Handbook: Data Tests.” GitLab Handbook. [gitlab.com]
• Reis, J. & Housley, M. (2022). Fundamentals of Data Engineering. O’Reilly. Capitolo 8: “Queries, Modeling, and Transformation.”

Controllo di qualità

Prima di usare testing, refactoring e reusable sql patterns in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Problema reale

Nel dominio di advanced SQL, Testing, refactoring e reusable SQL patterns serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere Testing, refactoring e reusable SQL patterns analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Il team eredita query lunghe, copiate in tre dashboard e modificate a mano. Prima di aggiungere nuove metriche deve estrarre pattern riusabili, aggiungere test su grain e chiavi, e rendere il refactoring controllabile invece di affidarlo alla memoria di chi ha scritto il primo SQL.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per Testing, refactoring e reusable SQL patterns: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare Testing, refactoring e reusable SQL patterns come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Testing, refactoring e reusable SQL patterns diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Tecnico. Difficoltà: advanced. Tempo stimato: 22 min.