import pandas as pd

SQL for data warehouse: essential query patterns

Nel warehouse SQL non serve solo a estrarre righe. Serve a codificare definizioni condivise, a controllare il grain, a costruire aggregazioni e a proteggere le metriche da join sbagliati. Questa lezione raccoglie i pattern che rendono una query riusabile, leggibile e verificabile, quelli che un’altra persona può modificare senza rompere il significato. Il punto non è collezionare sintassi, ma capire quale query rende difendibile una decisione.

Il problema concreto

Una query analitica sembra innocua finché due persone calcolano lo stesso KPI in due modi diversi e ottengono numeri diversi. A quel punto la riunione si sposta dal merito al “ma tu come l’hai contato?”. La difficoltà di SQL nel warehouse non è ricordare la sintassi di un join, ma scrivere query in cui il grain è esplicito, i duplicati sono gestiti e la finestra temporale è chiara.

Una query professionale è difensiva: usa CTE leggibili, dedup esplicita, finestre temporali dichiarate, controlli di cardinalità e aggregazioni al grain giusto. Quando manca uno di questi elementi il numero esce comunque, ma nessuno sa fino a che punto fidarsi.

Come ragionare prima di scrivere la query

Prima di scrivere conviene fissare poche scelte. Su quale grain stai aggregando, e quella granularità corrisponde alla domanda di business? Quale dimensione temporale filtri, e la stai filtrando prima del GROUP BY quando possibile? I join usano chiavi numeriche o stringhe, e c’è il rischio di moltiplicare le righe?

Step	Question to ask	Expected output
Decision	Quale numero deve produrre la query, e per quale scelta?	Metrica definita
Signal	Su quale grain e quale finestra temporale aggrego?	Granularità esplicita
Baseline	Rispetto a cosa confronto il risultato?	Periodo o segmento
Vincolo	Quali join possono moltiplicare le righe?	Controllo di cardinalità
Action	La query è leggibile e modificabile da un collega?	CTE e nomi chiari

Ogni passaggio serve a evitare che una nozione tecnica diventi un rituale: la query non vale per la sua eleganza, ma per la fiducia che puoi riporre nel numero che produce.

Definizioni, grain e rischio

Conviene rendere esplicita l’unità su cui ragioni, che può essere una tabella, una fact, una dimensione o un intero modello dati. Collega quell’unità a un segnale osservabile come il grain corretto, l’integrità, le performance o il costo della query, e dichiara quale decisione architetturale ne deriva.

Element	Requested specification
Unit of analysis	table, fact, dimension, grain, or data model
Primary signal	grain corretto, integrità, performance, costo query, tracciabilità
Baseline	periodo precedente, gruppo comparabile o benchmark
Decision	schema, mart, query pattern, or architectural choice
Risk	scambiare un numero disponibile per una prova sufficiente

Una query è solida quando un altro analista può rileggerne la logica, criticare le assunzioni e ottenere lo stesso risultato partendo dagli stessi dati. Se questo non accade, il problema raramente è la sintassi: è una definizione lasciata implicita.

Pattern 1: aggregazione con dimensioni

SELECT d.year, d.quarter, c.country,
       SUM(f.amount) AS revenue,
       COUNT(DISTINCT f.customer_id) AS customers
FROM sales_fact f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, c.country;

Tre regole valgono quasi sempre. Filtra sulla dimensione temporale prima del GROUP BY quando puoi, usa chiavi numeriche per i join invece delle stringhe, e aggrega nella fact table e non nelle dimensioni. Sono accorgimenti banali da enunciare e costosi da dimenticare, perché incidono insieme su correttezza e performance.

Pattern 2: analisi temporale con window function

WITH monthly AS (
  SELECT d.year_month, SUM(f.amount) AS revenue
  FROM sales_fact f JOIN dim_date d ON f.date_key = d.date_key
  GROUP BY d.year_month
)
SELECT year_month, revenue,
  LAG(revenue, 12) OVER (ORDER BY year_month) AS revenue_ly,
  ROUND((revenue - LAG(revenue,12) OVER (ORDER BY year_month))
        / LAG(revenue,12) OVER (ORDER BY year_month) * 100, 1) AS yoy_growth
FROM monthly;

Qui la CTE monthly isola l’aggregazione e la window function LAG(revenue, 12) recupera il valore di dodici mesi prima per calcolare la crescita anno su anno. Separare l’aggregazione dal confronto temporale rende la query più leggibile e meno fragile quando devi cambiare la finestra.

Pattern 3: pivot con CASE WHEN

SELECT d.year_month,
  SUM(CASE WHEN c.country = 'IT' THEN f.amount ELSE 0 END) AS revenue_IT,
  SUM(CASE WHEN c.country = 'FR' THEN f.amount ELSE 0 END) AS revenue_FR,
  SUM(CASE WHEN c.country = 'DE' THEN f.amount ELSE 0 END) AS revenue_DE
FROM sales_fact f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY d.year_month;

Il pivot con CASE WHEN è portabile su ogni database. L’operatore PIVOT nativo di SQL Server e Snowflake è più elegante ma meno portabile, quindi quando devi scrivere query che girano su warehouse diversi conviene restare sul CASE WHEN.

Pattern 4: percent of total

SELECT country, SUM(amount) AS revenue,
  ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM sales_fact f JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY country ORDER BY revenue DESC;

L’espressione SUM(SUM(amount)) OVER () è una window function applicata a un’aggregazione: calcola il totale globale e lo rende disponibile a ogni riga, così puoi esprimere ciascun paese come percentuale del totale senza una seconda query o una sottoquery di servizio.

La query come parte di un sistema

In un progetto reale i query pattern non vivono isolati. Sono parte di un sistema più ampio fatto di decisioni, dati disponibili, vincoli tecnici, incentivi organizzativi e qualità dell’esecuzione. Il rischio di chi è alle prime armi è trattare il tema come una definizione da memorizzare. Il lavoro professionale è diverso: serve capire quale problema risolve una query, quali assunzioni contiene e cosa succede quando quelle assunzioni saltano.

Nel data warehousing la prima domanda non è “quale metrica calcolo?” ma “quale decisione dovrà essere presa grazie a questa analisi?”. Una query ha valore solo se riduce l’incertezza di una scelta. Un buon modo per impostare il lavoro è definire il problema in linguaggio business, identificare l’unità di analisi corretta (utente, account, evento, sessione, ordine, campagna), controllare se i dati misurano davvero il fenomeno, costruire una metrica interpretabile, segmentare per non far nascondere alla media pattern opposti e trasformare il risultato in una raccomandazione verificabile.

Real case: Netflix and the discipline of metrics

Netflix è un esempio utile perché ha costruito molte decisioni di prodotto intorno a segnali comportamentali osservabili: completamento degli episodi, tempo di ricerca prima della riproduzione, abbandono dopo pochi minuti, ritorno nella settimana successiva, efficacia delle raccomandazioni. Il punto non è che ogni azienda debba copiare Netflix, ma che lì il dato non è un ornamento, è infrastruttura decisionale.

Quando Netflix valuta una modifica all’esperienza, per esempio una nuova riga di raccomandazioni, una diversa immagine di copertina o un algoritmo di ranking, non misura solo il click immediato. Guarda anche segnali di qualità: l’utente guarda davvero il contenuto, torna nei giorni successivi, riduce il tempo speso a cercare. Questa disciplina evita di ottimizzare vanity metric positive nel breve ma dannose nel lungo periodo. Lo stesso vale per le query del warehouse: vanno collegate a un outcome, perché se il risultato non aiuta a scegliere tra due azioni alternative l’analisi è incompleta.

SQL example: building a control view

The following pattern is intentionally generic but executable in most modern warehouses. The goal is to create an analytical base with metric, segment, and time window, so you can compare periods and groups without rewriting logic each time.

WITH base_events AS (
  SELECT
    user_id,
    account_id,
    event_type,
    event_time,
    DATE_TRUNC('week', event_time) AS week,
    source,
    device_type
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '180 days'
    AND user_id IS NOT NULL
),
weekly_user_metrics AS (
  SELECT
    week,
    user_id,
    COALESCE(source, 'unknown') AS source,
    COALESCE(device_type, 'unknown') AS device_type,
    COUNT(*) AS total_events,
    COUNT(DISTINCT DATE(event_time)) AS active_days,
    COUNT(DISTINCT event_type) AS event_diversity,
    MAX(CASE WHEN event_type IN ('purchase', 'subscribe', 'activation') THEN 1 ELSE 0 END) AS reached_key_outcome
  FROM base_events
  GROUP BY week, user_id, source, device_type
)
SELECT
  week,
  source,
  device_type,
  COUNT(DISTINCT user_id) AS users,
  ROUND(AVG(active_days), 2) AS avg_active_days,
  ROUND(AVG(event_diversity), 2) AS avg_event_diversity,
  ROUND(AVG(reached_key_outcome) * 100, 2) AS key_outcome_rate
FROM weekly_user_metrics
GROUP BY week, source, device_type
ORDER BY week, source, device_type;

Questa query non pretende di essere la risposta finale. Serve a creare una superficie di osservazione fatta di trend, segmenti, differenze tra canali e variazioni nel tempo. Da qui l’analista può formulare ipotesi più precise.

Python example: checking stability and anomalies

Una metrica utile deve essere stabile abbastanza da orientare decisioni e sensibile abbastanza da segnalare cambiamenti reali. In Python possiamo controllare le variazioni anomale settimana su settimana.

# df contiene: week, segment, users, key_outcome_rate
# key_outcome_rate espresso in percentuale, es. 12.4

df = df.sort_values(['segment', 'week']).copy()
df['previous_rate'] = df.groupby('segment')['key_outcome_rate'].shift(1)
df['wow_change_pp'] = df['key_outcome_rate'] - df['previous_rate']
df['rolling_mean'] = df.groupby('segment')['key_outcome_rate'].transform(
    lambda s: s.rolling(4, min_periods=2).mean()
)
df['rolling_std'] = df.groupby('segment')['key_outcome_rate'].transform(
    lambda s: s.rolling(4, min_periods=2).std()
)
df['z_score'] = (df['key_outcome_rate'] - df['rolling_mean']) / df['rolling_std']

anomalies = df[df['z_score'].abs() >= 2].sort_values('z_score')
print(anomalies[['week', 'segment', 'key_outcome_rate', 'wow_change_pp', 'z_score']])

Il valore di questo controllo è pratico: evita di reagire a ogni oscillazione casuale, ma segnala quando una variazione merita un’indagine. In un contesto aziendale alimenta alert, review settimanali e retrospettive di prodotto.

Common mistakes to avoid

Il primo errore è lavorare su dati aggregati troppo presto, perché una media globale può nascondere due segmenti che si muovono in direzioni opposte. Il secondo è non controllare la qualità del dato: eventi duplicati, tracking incompleto, timezone incoerenti e definizioni cambiate producono conclusioni false. Il terzo è confondere correlazione e causalità: se gli utenti che usano una feature convertono di più, non significa che la feature causi la conversione, perché potrebbero usarla proprio perché sono già più motivati.

Per ridurre questi rischi ogni analisi dovrebbe includere almeno una definizione esplicita della metrica, un confronto per segmento e una verifica contro un periodo precedente o un gruppo di controllo. Nel caso specifico delle query, l’errore più frequente è proprio il join che moltiplica le righe senza che nessuno se ne accorga: un controllo di cardinalità prima di pubblicare il numero costa pochi secondi e salva molte riunioni.

Summary

I query pattern del warehouse vanno trattati come strumenti decisionali, non come esercizi di stile. Il valore nasce quando colleghi il problema di business al grain corretto, alla finestra temporale giusta e ai controlli che proteggono il numero. Una buona query non finisce con “il numero è salito” o “il numero è sceso”, ma con una frase operativa: quale decisione prendiamo, con quale livello di confidenza e con quale metrica verificheremo se avevamo ragione. Decisione, segnale, baseline, rischio e azione restano la forma corretta del lavoro; tutto il resto serve solo se rende più affidabile uno di questi passaggi.