SQL per data warehouse: query pattern essenziali

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 metodo di conteggio. La difficoltà non è ricordare la sintassi di un join: è scrivere query con grain esplicito, duplicati gestiti e finestra temporale chiara. Qui impari i pattern difensivi che rendono ogni KPI riusabile.

L’idea in una frase

I query pattern del warehouse rendono ogni KPI riusabile perché fissano grain, finestre temporali e controlli di cardinalità. La difficoltà non è la sintassi: è scrivere query difensive.

La sequenza di scrittura

1. Dichiara grain, finestra temporale e metrica prima di scrivere righe di codice.
2. Filtra sulla dimensione temporale prima del raggruppamento e usa chiavi numeriche nei join.
3. Isola aggregazione e confronto temporale in CTE separate con nomi espliciti e verificabili.
4. Aggiungi un controllo di cardinalità contro i join che moltiplicano le righe prima di pubblicare.
5. Rendi la query rileggibile con CTE ordinate e alias stabili che un collega modifica senza romperla.

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 metodo di conteggio. La difficoltà non è ricordare la sintassi di un join: è scrivere query con grain esplicito, duplicati gestiti e finestra temporale chiara. Una query professionale è difensiva. Usa CTE leggibili, dedup esplicita, finestre dichiarate, controlli di cardinalità e aggregazioni al grain giusto.

Passaggio	Domanda da fare	Output atteso
Decisione	Quale numero deve produrre la query, e per quale scelta?	Metrica definita
Segnale	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à
Azione	La query è leggibile e modificabile da un collega?	CTE e nomi chiari

Elemento	Specifica richiesta
Unità di analisi	tabella, fact, dimensione, grain o modello dati
Segnale principale	grain corretto, integrità, performance, costo query, tracciabilità
Baseline	periodo precedente, gruppo comparabile o benchmark
Decisione	schema, mart, query pattern o scelta architetturale
Rischio	scambiare un numero disponibile per una prova sufficiente

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 raggruppamento quando puoi. Usa chiavi numeriche come date_key e customer_key per i join invece delle stringhe. Aggrega nella fact table sales_fact e non nelle dimensioni. Sono accorgimenti semplici 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;

La CTE chiamata monthly isola l’aggregazione mensile. La window function LAG recupera il valore di dodici mesi prima per la crescita anno su anno. Separare aggregazione e confronto temporale rende la query leggibile e meno fragile al cambio di 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. Per query che girano su warehouse diversi conviene restare sul CASE WHEN.

In sintesi: per aggregazioni portabili usa CASE WHEN, e riserva l’operatore PIVOT nativo ai soli warehouse dove la portabilità non serve.

Verdetto: CASE WHEN vince per aggregazioni portabili su warehouse diversi; l’operatore PIVOT nativo solo dove la portabilità non serve.

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 con window su aggregazione calcola il totale globale e lo rende disponibile a ogni riga. Così ogni paese diventa percentuale del totale senza seconda query o sottoquery di servizio.

Esempio SQL: costruire una vista di controllo

Il pattern seguente è volutamente generico ma eseguibile nella maggior parte dei warehouse moderni. Crea una base analitica con metrica, segmento e finestra temporale, così confronti periodi e gruppi senza riscrivere la logica ogni volta.

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 crea una superficie di osservazione con trend, segmenti, differenze tra canali e variazioni nel tempo. Da qui l’analista formula ipotesi più precise.

Esempio Python: controllare stabilità e anomalie

Una metrica utile è stabile abbastanza da orientare decisioni e sensibile abbastanza da segnalare cambiamenti reali. In Python controlli 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 controllo con rolling_mean, rolling_std e z_score evita di reagire a ogni oscillazione casuale e segnala quando una variazione merita indagine. In azienda alimenta alert, review settimanali e retrospettive di prodotto.

Il warehouse che ha reso questi pattern un business

Nel settembre 2020 Snowflake si quota al New York Stock Exchange con il simbolo SNOW a 120 dollari per azione, raccogliendo circa 3,36 miliardi di dollari nella maggiore IPO software di sempre fino a quel momento. La valutazione premia un warehouse separato tra storage e compute con query SQL standard su scala elastica. Milioni di query analitiche come quelle di questa lezione girano ogni giorno su quel modello a consumo da 5 dollari per terabyte scansionato in modalità on-demand. Il punto è questo: i pattern di aggregazione, finestre e pivot restano il collo di bottiglia tra costo, latenza e fiducia nel numero.

Domande per ripassare

1. Quale grain dichiari prima di scrivere una aggregazione su fact e dimensioni?
2. Quale controllo di cardinalità ti protegge dai join che moltiplicano le righe?
3. Quando preferisci CASE WHEN portabile all’operatore PIVOT nativo?
4. Quale CTE separa aggregazione mensile e confronto anno su anno?