Testing, refactoring e pattern SQL riusabili

Una query mensile può decidere bonus, forecast e priorità di prodotto, eppure spesso nessuno osa toccarla. Troppe CTE anonime, filtri ripetuti ovunque, zero test e nessuna certezza su cosa significhi davvero ogni campo. È esattamente questa la situazione in cui testing, refactoring e pattern riusabili smettono di essere un lusso. SQL non è più uno script personale, è diventato un artefatto che altri leggono e modificano.

Il problema vero

Il punto è scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite si intrecciano. Non basta che il codice sia elegante. Serve che ogni assunzione sia testata, che ogni trasformazione abbia un nome leggibile e che i pattern ricorrenti riducano il rischio di leggere lo stesso dato in due modi diversi.

L’obiettivo resta lo stesso: capire quale decisione cambia, quale dato osservi e quale errore vuoi evitare. Se non riesci a rispondere a queste tre domande, la competenza non è ancora diventata pratica.

Come tenere il focus sulla decisione

Quattro passaggi tengono insieme il lavoro. La domanda fissa la scelta da migliorare, la misura individua il segnale osservabile, il controllo definisce la baseline che rende interpretabile il risultato e l’azione dice cosa cambia dopo.

Phase	What to clarify	Output
Question	Which real choice needs improvement?	Decision to make
Measure	Which observable signal represents the problem?	Metric or source data
Control	Which baseline makes the result interpretable?	Credible comparison
Action	What changes after the analysis?	Next operational step

Ogni approfondimento tecnico dovrebbe rafforzare almeno uno di questi quattro punti, altrimenti è probabilmente decorazione.

Definire le unità di lavoro

Per rendere il processo analizzabile conviene fissare l’unità di lavoro, che può essere una riga, una partizione, una finestra, un join, una coorte o una metrica temporale. Poi la colleghi a una metrica osservabile come correttezza, performance, duplicati, grain o stabilità. Infine dichiari cosa ti aspetti di produrre: una query, un modello intermedio, un test SQL o un pattern riusabile.

Element	Requested specification
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Primary signal	correttezza, performance, duplicati, grain, stabilità
Baseline	Previous period, comparable group, benchmark, or counterfactual scenario
Decision	query, intermediate model, SQL test, or reusable pattern
Risk	Mistaking an available number for sufficient proof

L’impostazione è solida quando un altro analista può riprodurre la logica, criticare le assunzioni e arrivare alla stessa decisione partendo dagli stessi dati.

Testing dei dati oltre il “la query gira”

Il testing in SQL analitico lavora su tre piani. I test di integrità come unique e not_null garantiscono l’unicità delle chiavi e l’assenza di valori nulli dove non sono ammessi. I test di logica di business come accepted_values e relationships validano le regole di dominio, dai valori attesi negli enum all’integrità referenziale tra tabelle. I test di qualità statistica, infine, sorvegliano la stabilità nel tempo e fanno scattare un alert quando volumi o medie si muovono in modo anomalo.

Il caso più semplice è il controllo di unicità. Ogni order_id deve essere unico:

SELECT order_id, COUNT(*)
FROM orders_clean
GROUP BY order_id
HAVING COUNT(*) > 1;

Se la query restituisce anche solo una riga, il test fallisce e hai un duplicato da indagare.

Per le anomalie di volume il controllo confronta il conteggio giornaliero con la media e la deviazione standard delle due settimane precedenti:

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: estrai, rinomina, riusa

Il refactoring viene trascurato spesso perché “è solo una query”. Ma le query analitiche vivono in produzione per mesi o anni, passano per le mani di più persone e accumulano complessità accidentale a ogni modifica.

Tre operazioni risolvono la maggior parte dei casi. La prima è estrarre le subquery in CTE con nomi parlanti, così la logica diventa esplicita e si modifica senza paura. La seconda è unire le query duplicate individuando il pattern comune, per evitare che la stessa regola viva in tre posti diversi e finisca per divergere. La terza è separare la logica di business da quella di presentazione, in modo che il calcolo resti riusabile a prescindere da come lo mostri.

Un esempio concreto. Prima:

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;

After:

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;

Reusable patterns in analytical SQL

Alcuni schemi tornano utili così spesso da meritare un nome. I flag binari per condizioni, definiti una volta in un modello intermedio, evitano di ripetere la stessa CASE WHEN in dieci query e rendono il codice più leggibile. Gli snapshot per confronti temporali usano le funzioni finestra per mettere a fianco lo stato attuale e quello passato senza join complicati.

Ecco come appaiono i flag binari in pratica:

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
  FROM users
)
SELECT COUNT(*) FILTER (WHERE is_active_30d = 1 AND is_converted = 1) AS active_converted
FROM users_enriched;

Caso studio: GitLab

GitLab ha reso pubblica la propria strategia di data testing nell’handbook aziendale. Tutti i modelli dbt hanno test not_null e unique on primary columns, tests accepted_values sugli enum e test di volume con soglie di alert.

Un incidente del 2021 ha mostrato perché i test statistici servono davvero. Un cambio nello schema dell’API fece arrivare valori in euro invece che in dollari. Il test di volume non rilevò nulla, perché il numero di righe era normale, ma un analista notò che gli importi erano più alti del previsto. Da allora GitLab ha aggiunto sanity check statistici che monitorano le variazioni settimanali superiori al 10%.

Una versione semplificata di quel controllo:

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 (PARTITION BY country ORDER BY week)) / NULLIF(LAG(avg_amount) OVER (PARTITION BY country ORDER BY week), 0) AS pct_change
  FROM weekly_stats
)
SELECT * FROM week_over_week
WHERE ABS(pct_change) > 0.10;

Esercitarsi

Puoi mettere alla prova questi concetti a tre livelli di profondità. Per partire, scrivi le query di test per unique(order_id), not_null(customer_id) e accepted_values(status, ['pending','completed','cancelled']). Salendo di un gradino, implementa un test che alzi un alert quando gli ordini di oggi scendono sotto il 30% della media degli ultimi 7 giorni. Per la sfida più impegnativa, prendi una query legacy di 80 righe piena di subquery annidate, rifattorizzala in CTE con nomi parlanti e scrivi un test per ogni CTE intermedia.

Come materiale usa dati di ordini, eventi, sessioni, coorti, revenue e una tabella calendario. Se non ne hai a disposizione, costruisci un dataset sintetico con almeno 200 righe, una dimensione temporale, un segmento e una metrica di outcome.

L’errore da evitare

Il rischio più comune è usare questi tre concetti come etichetta invece che come processo. Succede quando mostri un grafico senza una decisione collegata, una metrica senza baseline o una conclusione senza dire quale assunzione potrebbe smontarla.

La domanda di controllo è sempre la stessa: se questo risultato fosse instabile, quale scelta sbaglierei? Se non sai rispondere in modo concreto, manca il ponte tra analisi e azione.

Prima di continuare verifica di saper rispondere a poche domande. Quali sono i tre livelli di testing e cosa controlla ciascuno, quali sono le tre operazioni di refactoring, come GitLab ha scoperto il bug del cambio valuta e quale test ha aggiunto, quale unità di analisi rende il problema misurabile e quale output consegneresti a uno stakeholder non tecnico.

Operational Summary

Testing, refactoring e pattern riusabili pagano solo se rendono le decisioni più chiare, non se aggiungono terminologia. Il percorso che va dal problema al modello, dalla formalizzazione all’esempio fino agli esercizi e al controllo finale, trasforma la lezione in pratica verificabile.