Case Study: Building a Data Warehouse

Questo laboratorio appartiene al binario ml-tabellare: il warehouse che costruiremo è fatto di fact table, dimensioni e query di validazione, tutte operazioni su tabelle.

L’idea in una frase

Costruire un data warehouse significa trasformare sorgenti operative disordinate in layer, modelli e metriche riusabili dal team. L’obiettivo non è caricare tabelle: è creare qualcosa che altri usano senza reinterpretare tutto da capo.

La sequenza di lavoro

1. Raccogli le domande di CFO, category manager e marketing con metriche, dimensioni e finestre richieste.
2. Fissa il grain a riga per item di scontrino e disegna fact table con misure e chiavi degenerate.
3. Modellizza quattro dimensioni con attributi, gerarchie e chiavi surrogate stabili e documentate.
4. Valida lo schema con tre query di business su margine, categoria e quintili di clientela reale.
5. Pianifica evoluzione con nuove dimensioni, snapshot e aggregati materializzati con priorità e tempi scritti.

Il problema vero

Il caso costruisce un warehouse da sorgenti disordinate: eventi prodotto, pagamenti, CRM e anagrafiche account. L’obiettivo non è caricare tabelle: è creare layer, modelli e metriche che altri team usano senza reinterpretare tutto da capo. Ogni scelta deve chiarire quale domanda diventa più semplice e quale rischio viene controllato.

Step	Question to ask	Expected output
Decision	Che cosa cambia se il warehouse è progettato bene?	Scelta esplicita
Signal	Quale dato osservabile riduce l’incertezza?	Metrica o evento
Baseline	Rispetto a cosa interpretiamo il risultato?	Credible comparison
Vincolo	Che cosa può falsare la lettura?	Assunzione da dichiarare
Action	Quale passo operativo segue?	Raccomandazione controllabile

Element	Operational Definition	Controllo minimo
Unit of analysis	Oggetto su cui misuri il fenomeno	Utente, account, evento, ordine o periodo
Variabile osservata	Segnale che rappresenta il comportamento	Definizione stabile e tracciabile
Baseline	Stato contro cui confronti il segnale	Periodo, segmento, controllo o benchmark
Soglia decisionale	Punto in cui cambia l’azione	Criterio scritto prima della lettura
Rischio residuo	Errore che può restare anche dopo l’analisi	Sensitivity check o revisione qualitativa

Fase 1: requisiti di business

Tutto parte dalle domande degli stakeholder, perché lo schema risponde a loro. Il CFO vuole ricavo e margine per negozio, categoria e mese. Il Category Manager chiede quali prodotti vendono di più per regione e quali sono in declino. Il Marketing vuole chi sono i clienti del top 20 per cento e qual è la loro frequenza di acquisto.

Fase 2: progettazione dello star schema

Al centro c’è la fact table sales fact, con granularità di una riga per item venduto in ogni scontrino. Le misure sono quantity, amount, cost e margin. Le foreign key sono date key, store key, product key, customer key e transaction id how degenerate dimension. Attorno ci sono quattro dimensioni. La dim product porta product name, category, subcategory, brand e package sizecount. The dim store raccoglie store name, city, region, country e opening datecount. The dim customer, alimentata dal CRM della loyalty card, contiene customer name, signup date, segment, city e age groupcount. The dim date espone date, year, month, quarter, day of week e flag festivo.

In sintesi: una fact con grain a riga di scontrino e quattro dimensioni conformed copre le tre domande di business senza join ambigui.

Verdetto: una fact con grain a riga di scontrino e quattro dimensioni conformed vince sulle alternative: copre le domande di business senza join ambigui e resta estendibile con supplier, snapshot e aggregati.

Fase 3: query di validazione

Prima di considerare lo schema affidabile, mettilo alla prova con le domande reali.

-- Monthly revenue by category
SELECT d.year, d.month, p.category,
       SUM(s.amount) AS revenue, SUM(s.margin) AS margin
FROM sales_fact s
JOIN dim_date d ON s.date_key = d.date_key
JOIN dim_product p ON s.product_key = p.product_key
GROUP BY d.year, d.month, p.category;

-- Top 20% customers (by revenue)
SELECT c.customer_id, c.customer_name,
       SUM(s.amount) AS total_spent,
       NTILE(5) OVER (ORDER BY SUM(s.amount) DESC) AS quintile
FROM sales_fact s JOIN dim_customer c ON s.customer_key = c.customer_key
GROUP BY c.customer_id, c.customer_name;

Fase 4: evoluzione futura

Lo schema iniziale è una base. In seguito aggiungi una dim supplier per la supply chain, una fact inventory snapshot per lo stock e aggregazioni materializzate per dashboard con refresh sotto il secondo. La consegna minima resta chiara: uno star schema con una fact e quattro dimensioni, almeno tre query di business funzionanti e un documento di design con granularità e logica ETL.

Esempio SQL: una vista di controllo

Il pattern seguente è eseguibile nella maggior parte dei warehouse moderni e crea una base con metrica, segmento e finestra temporale per confrontare periodi e gruppi senza riscrivere la logica.

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;

Esempio Python: stabilità e anomalie

# 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 caso da manuale del retail

Nel febbraio 1995 Tesco lancia la Clubcard con Dunnhumby e raccoglie circa 5 milioni di membri nel primo anno di programma. La loyalty card alimenta una dimensione cliente con dati di scontrino, segmento e frequenza che è il caso da manuale di star schema retail: una fact per riga di scontrino e dimensioni per prodotto, punto vendita, cliente e data. Le domande di questa lezione diventano query dirette: margine per categoria e mese, prodotti in declino per regione, top quintile di clienti per spesa. Il messaggio è diretto: il warehouse vince quando la dimensione cliente è conformed e ogni dashboard legge lo stesso grain.

Domande per ripassare

1. Quali tre domande di business guidano fact, dimensioni e grain?
2. Quale grain a riga di scontrino evita doppi conteggi nel tuo schema?
3. Quali tre query di validazione provano margine, categoria e top clienti?
4. Quale evoluzione con supplier, snapshot e aggregati pianifichi per prima?