Testing, refactoring e pattern SQL riusabili

Anche questa lezione appartiene al binario ml-tabellare: test, refactoring e pattern si applicano tutti a query che lavorano su tabelle.

L’idea in una frase

Il testing rende esplicite le assunzioni della query, mentre refactoring e pattern riusabili rendono visibile il livello di dettaglio e stabile la logica condivisa.

Il metodo in cinque passi

1. Dichiara il grain di ogni passaggio con commento esplicito su cosa rappresenta ciascuna riga.
2. Verifica chiavi, nulli, vocabolari e relazioni con query su orders_clean che devono restituire zero righe a ogni esecuzione.
3. Sorveglia volumi e medie contro baseline storica con soglie larghe, poi tarate sui falsi positivi osservati.
4. Estrai le sottoquery in passaggi nominati (CTE) con filtro in un solo punto e separa calcolo e presentazione.
5. Centralizza le regole condivise in un modello intermedio testato (users_enriched) e riusa il modello invece di ricalcolare.

Perché le query analitiche si rompono in silenzio

Il database raramente avvisa. Una condizione di JOIN dimenticata restituisce dieci volte le righe senza errori. Un nuovo valore di status ignorato dai rami condizionali sparisce dai report senza lamentele. Un cambio di valuta a monte sposta solo le medie, mentre i conteggi restano identici. Dietro quasi tutti questi incidenti ci sono duplicazione logica della stessa regola in più query, livello di dettaglio ambiguo tra passaggi e assunzioni implicite mai scritte. Il testing rende esplicite le assunzioni, il refactoring rende visibile il dettaglio, e i pattern scrivono la regola una volta sola.

Il grain prima di tutto

Ogni passaggio misterioso merita la stessa prima domanda: quante righe rappresenta ciascuna riga in quel punto della query. Il grain è il contratto che tiene insieme filtri, JOIN, aggregazioni e window function. Il caso classico unisce orders, con righe per ordine, a order_items, con righe per articolo, e poi somma un importo che vive a livello ordine. Il risultato gonfia il totale tante volte quante sono le voci.

-- SBAGLIATO: il grain del join e' riga-per-articolo,
-- ma amount vive al grain riga-per-ordine -> totale gonfiato
SELECT SUM(o.amount) AS revenue_gonfiato
FROM orders o
JOIN order_items i ON i.order_id = o.order_id;

La correzione aggrega prima al livello giusto e poi unisce. La regola operativa chiede un commento sul grain per ogni passaggio e JOIN leciti solo tra dettagli compatibili. Il conteggio righe prima e dopo ogni JOIN in sviluppo intercetta subito le esplosioni anomale.

-- CORRETTO: aggrego gli articoli al grain ordine prima di unire,
-- cosi' ogni riga contribuisce una sola volta al totale
WITH revenue_per_ordine AS (
  SELECT order_id, SUM(quantity * unit_price) AS line_revenue
  FROM order_items
  GROUP BY order_id -- ora il grain e' una riga per ordine
)
SELECT SUM(r.line_revenue) AS revenue
FROM orders o
JOIN revenue_per_ordine r USING (order_id);

Test di integrità su chiavi, nulli e relazioni

Il primo strato di test parla di contratti e non di statistica. Ogni tabella intermedia promette unicità delle chiavi, assenza di nulli vietati, vocabolari noti e chiavi esterne risolte. Senza strumenti dedicati sono semplici query che devono restituire zero righe: ogni riga restituita è un fallimento. Il controllo di unicità resta il più redditizio, perché trova duplicati da JOIN sbagliati o ricariche doppie con una sola scansione.

-- TEST unique(order_id): deve restituire zero righe.
-- Ogni riga restituita e' un duplicato da indagare subito.
SELECT order_id, COUNT(*) AS n
FROM orders_clean
GROUP BY order_id
HAVING COUNT(*) > 1;

Poi vengono i nulli dove non sono ammessi e i valori fuori vocabolario che rompono la logica a valle. Una colonna di status con tre valori ammessi è un contratto con tutto il codice che filtra per stato.

-- TEST accepted_values: intercetta nuovi stati non gestiti
-- dalla logica a valle prima che falsino i totali
SELECT DISTINCT status
FROM orders_clean
WHERE status NOT IN ('pending', 'completed', 'cancelled');

Il terzo controllo è relazionale: le chiavi esterne devono trovare la dimensione. Ordini con cliente orfano producono JOIN che perdono righe in silenzio e fatturato che sparisce senza errori.

-- TEST relationships: ordini il cui cliente manca in dim_customers.
-- Zero righe attese; altrimenti il join a valle perde fatturato.
SELECT o.order_id, o.customer_id
FROM orders_clean o
LEFT JOIN dim_customers c USING (customer_id)
WHERE c.customer_id IS NULL
LIMIT 20;

Questi test vanno eseguiti a ogni esecuzione e non una tantum, con blocco della pubblicazione quando falliscono. Su tabelle enormi conviene test esaustivo su chiavi e vocabolari e campionamento ragionato su partizioni recenti, dove il volume morde.

Test statistici quando i volumi mentono senza cambiare

I test di integrità non vedono righe valide con medie spostate da cambio valuta, tasso applicato due volte o unità diversa. Qui servono controlli contro baseline storica con soglie dichiarate. Il più semplice confronta il conteggio di oggi con media e deviazione dei quattordici giorni precedenti. Il controllo gemello sorveglia le medie per segmento con variazione percentuale settimana su settimana.

-- TEST di volume: confronta il conteggio di oggi con media e
-- deviazione standard dei 14 giorni precedenti; segnala outlier
WITH daily AS (
  SELECT dt, COUNT(*) AS righe
  FROM orders_clean
  GROUP BY dt -- grain: una riga per giorno
),
con_banda AS (
  SELECT
    dt,
    righe,
    AVG(righe) OVER (ORDER BY dt ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING) AS media_14g,
    STDDEV(righe) OVER (ORDER BY dt ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING) AS sd_14g
  FROM daily
)
SELECT
  dt, righe, media_14g,
  CASE
    WHEN righe < media_14g - 2 * sd_14g THEN 'VOLUME_ANOMALO_BASSO'
    WHEN righe > media_14g + 2 * sd_14g THEN 'VOLUME_ANOMALO_ALTO'
  END AS esito
FROM con_banda
ORDER BY dt DESC
LIMIT 7;

Su serie con forte stagionalità settimanale la baseline va condizionata al giorno della settimana, altrimenti il test segnala falsi allarmi e il team smette di guardarlo. Su segmenti piccoli la varianza naturale supera la soglia standard e la soglia va scalata al volume. La protezione contro divisione per zero con NULLIF resta obbligatoria per i nuovi segmenti alla prima settimana.

-- TEST di stabilita' delle medie: variazione settimana su settimana
-- per paese; soglia 10% come prima rete, da tarare per segmento
WITH settimanale AS (
  SELECT
    country,
    DATE_TRUNC('week', close_date) AS settimana,
    AVG(amount) AS scontrino_medio
  FROM opportunities
  GROUP BY country, 2 -- grain: una riga per paese-settimana
),
confronto AS (
  SELECT
    country, settimana, scontrino_medio,
    LAG(scontrino_medio) OVER (PARTITION BY country ORDER BY settimana) AS media_prec
  FROM settimanale
)
SELECT
  country, settimana, scontrino_medio, media_prec,
  (scontrino_medio - media_prec) / NULLIF(media_prec, 0) AS variazione
FROM confronto
WHERE ABS((scontrino_medio - media_prec) / NULLIF(media_prec, 0)) > 0.10;

Da subquery annidate a passaggi leggibili

Il refactoring in SQL serve quando query di mesi e cinque autori diversi diventano illeggibili proprio mentre servirebbe modificarle in fretta. La prima mossa estrae le sottoquery in passaggi nominati (CTE) che dichiarano grain e contenuto. Il prima e il dopo fanno la stessa cosa sul fatturato mensile dei soli abbonamenti, ma solo il secondo dice dove filtrare se cambia la definizione.

-- PRIMA: filtro sepolto nella query, riuso impossibile,
-- ogni modifica tocca l'intero blocco
SELECT DATE_TRUNC('month', created_at) AS mese,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS ricavi,
  COUNT(DISTINCT user_id) AS utenti
FROM orders
WHERE order_type = 'subscription' AND created_at >= '2024-01-01'
GROUP BY 1;

-- DOPO: ogni CTE ha un grain dichiarato; il filtro vive in un
-- solo punto e la metrica e' separata dalla selezione
WITH ordini_abbonamento AS (
  -- grain: una riga per ordine di tipo subscription dal 2024
  SELECT *
  FROM orders
  WHERE order_type = 'subscription'
    AND created_at >= '2024-01-01'
),
metriche_mensili AS (
  -- grain: una riga per mese
  SELECT
    DATE_TRUNC('month', created_at) AS mese,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS ricavi,
    COUNT(DISTINCT user_id) AS utenti
  FROM ordini_abbonamento
  GROUP BY 1
)
SELECT * FROM metriche_mensili;

La seconda mossa unifica le query duplicate con la stessa regola scritta in tre formulazioni diverse: la versione unificata vive in un modello intermedio testato e le query finali lo leggono. La terza mossa separa calcolo e presentazione, con metriche pure nei passaggi e formattazione solo nell’ultimo SELECT o nello strumento di visualizzazione.

Pattern riusabili che reggono nel tempo

Il primo schema ricorrente è il flag binario calcolato una volta in un modello intermedio. Invece di ripetere la stessa condizione in dieci query, la definisci in un punto e a valle filtri su una colonna dal significato ovvio come is_active_30d o is_converted.

-- MODELLO intermedio users_enriched: i flag vivono qui, testati qui.
-- A valle nessuno riscrive la regola, la riusa soltanto.
WITH users_enriched AS (
  SELECT
    *,
    CASE WHEN last_login > CURRENT_DATE - INTERVAL '30 days'
      THEN 1 ELSE 0 END AS is_active_30d, -- 1 se login negli ultimi 30 giorni
    CASE WHEN total_orders > 0
      THEN 1 ELSE 0 END AS is_converted -- 1 se almeno un ordine
  FROM users
)
SELECT COUNT(*) FILTER (WHERE is_active_30d = 1 AND is_converted = 1) AS attivi_convertiti
FROM users_enriched;

Il secondo pattern è lo snapshot temporale con window function per mettere stato attuale e precedente sulla stessa riga senza JOIN. Il terzo è la spina calendario continua a cui agganciare i fatti con LEFT JOIN per mostrare i giorni a zero eventi. Senza spina i buchi spariscono dal grafico e un crollo a zero diventa invisibile proprio quando conta.

-- TEST completezza spine: ogni giorno dell'intervallo deve esistere
-- una e una sola volta; i buchi rendono invisibili i crolli a zero
SELECT COUNT(*) AS giorni, COUNT(DISTINCT giorno) AS giorni_unici,
  MIN(giorno) AS inizio, MAX(giorno) AS fine
FROM dim_calendar
WHERE giorno BETWEEN DATE '2024-01-01' AND CURRENT_DATE;
-- atteso: giorni = giorni_unici = numero di giorni nell'intervallo

Il limite dei pattern va detto con chiarezza: un pattern riusato nel contesto sbagliato fa più danni di una query scritta da zero. Prima di riusare conviene verificare che grain e definizione corrispondano al nuovo caso.

Mettere i test in produzione senza impazzire

Il modo più comune per fallire è voler testare tutto subito, con falsi positivi che sommergono il team. Meglio una progressione deliberata: dai contratti sulle tabelle economiche ai controlli di volume con soglie larghe, fino ai controlli statistici sulle medie tarati sui dati reali. I test bloccanti verificano proprietà sempre vere come l’unicità delle chiavi, mentre i controlli statistici producono avvisi da triage e non blocchi automatici. Bloccare un deploy per un’oscillazione fisiologica di un paese piccolo insegna al team a disattivare i test.

In sintesi: usa passaggi nominati con grain dichiarato e test di contratto bloccanti a ogni esecuzione, poi aggiungi controlli statistici solo come avvisi da indagare.

Verdetto: i test di contratto bloccanti a ogni esecuzione vengono prima di tutto; i controlli statistici restano avvisi da indagare, mai blocchi automatici.

Il caso GitLab: test statistici che hanno trovato la valuta sbagliata

GitLab ha reso pubblica la propria strategia di data testing nel manuale aziendale, con test di unicità, completezza e valori ammessi su ogni modello. Nel 2021 un cambio di schema fece arrivare importi in euro invece che in dollari su una pipeline di opportunità. Righe, chiavi e nulli risultarono regolari, mentre lo scontrino medio per paese risultò anomalo. Il team aggiunse controlli statistici sulle variazioni settimanali oltre la soglia del dieci per cento, nella forma mostrata sopra.

Domande per chiudere la lezione

1. Quale test intercetta per primo una JOIN che duplica righe e gonfia il revenue?
2. Perché un controllo di volume resta verde mentre la valuta errata sposta la media?
3. Quando un controllo statistico merita un blocco e quando merita solo un avviso da indagare?
4. Quale grain deve dichiarare ogni passaggio per rendere lecita la JOIN successiva?