Data Modeling for Warehouse

La stessa domanda sui clienti attivi cambia risposta se il grain è utente, account, contratto o workspace. La modellazione dimensionale rende queste scelte visibili prima che entrino in dashboard e KPI, con fatti, dimensioni e definizioni che reggono nel tempo. Un buon modello non è quello con più tabelle eleganti: è quello che impedisce join ambigui, doppi conteggi e metriche instabili.

L’idea in una frase

La modellazione dimensionale fissa grain, fatti e dimensioni per impedire join ambigui e doppi conteggi. Un buon modello non è quello con più tabelle eleganti: è quello che rende le metriche stabili nel tempo.

La sequenza di progettazione

1. Fissa il grain scrivendo cosa rappresenta una riga della fact table senza ambiguità.
2. Separa fatti transazionali, snapshot periodici e snapshot cumulativi in base alla domanda di business.
3. Classifica le misure in additive, semi-additive e non additive prima di permettere somme e medie.
4. Condividi le dimensioni conformed tra fact table e relega gli identificatori senza attributi a degenerate dimension.
5. Denormalizza le gerarchie in star schema salvo vincoli espliciti che impongono lo snowflake documentato.

Quando il problema diventa concreto

La stessa domanda sui clienti attivi cambia risposta se il grain è utente, account, contratto o workspace. La modellazione rende queste scelte visibili prima che entrino in dashboard e KPI, con fatti, dimensioni e definizioni che reggono nel tempo. Un buon modello non è quello con più tabelle eleganti: è quello che impedisce join ambigui, doppi conteggi e metriche instabili.

Step	Question to ask	Expected output
Decision	Che cosa cambia se modelliamo meglio i dati?	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

The hierarchy of modeling

Un modello maturo si costruisce a strati con compiti precisi. Lo staging è la copia uno a uno delle sorgenti senza trasformazioni. Il cleansed applica pulizia, standardizzazione e deduplicazione con dati ancora normalizzati. Il dimensional è lo star schema con fatti e dimensioni che il business interroga. L’aggregated tiene summary table pre-calcolate per le query frequenti.

Fact table: granularity and types

La decisione più importante è la granularità: cosa rappresenta una riga. La transactional ha una riga per evento, come sales fact con una riga per item venduto: massimo dettaglio e massima flessibilità. La periodic snapshot ha una riga per periodo, come inventory snapshot con una riga per prodotto per giorno: meno dettaglio e query più veloci. La accumulating snapshot ha una riga per evento con molte date, come order fulfillment with order date, ship date e deliver date: ideale per funnel e pipeline.

Le misure vanno trattate per tipo. Le additive si sommano su tutte le dimensioni, come amount. Le semi-additive si sommano su alcune dimensioni ma non su altre, come il livello di magazzino che sommi per prodotto ma non per data. Le non-additive non si sommano affatto, come price o rate.

In sintesi: usa transactional per dettaglio, periodic snapshot per velocità e accumulating snapshot per pipeline, e non sommare mai misure semi-additive sulle date.

Dimensioni: conformed e degenerate

Una conformed dimension è condivisa tra più fact table, come dim date tra sales fact e inventory fact, e garantisce coerenza cross-dominio. Una degenerate dimension vive dentro la fact table senza tabella propria: order number è un identificatore senza attributi che serve per raggruppare. La distinzione evita proliferazione di dimensioni inutili e perdita di coerenza tra report.

Gestire le gerarchie

Un cliente appartiene a città, regione e paese. Lo snowflake usa tabelle separate per livello, con query complesse e purezza accademica. Lo star denormalizzato mette tutto in dim customer with city, region e country nella stessa tabella, con query semplici e ridondanza controllata. Lo star denormalizzato resta preferito nella maggior parte dei casi reali, perché la semplicità delle query vale più della purezza.

In sintesi: usa star denormalizzato come default e riserva lo snowflake ai vincoli espliciti di normalizzazione documentata.

Verdetto: lo star denormalizzato vince come default: la semplicità delle query vale più della purezza, e lo snowflake resta solo per vincoli espliciti di normalizzazione documentata.

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;

Python example: checking stability and anomalies

# 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']])

Reference: Kimball, R. e Ross, M. (2013). The Data Warehouse Toolkit, 3rd ed. Wiley.

Il libro che ha fondato il vocabolario

Nel 1996 Ralph Kimball pubblica la prima edizione di The Data Warehouse Toolkit e fissa il vocabolario che questa lezione usa ancora oggi. Tra i contributi più operativi ci sono le Slowly Changing Dimensions di tipo 1, 2 e 3: sovrascrittura, storicizzazione con nuove righe e doppia colonna per valore corrente e precedente. La terza edizione del 2013 con Margy Ross consolida il metodo con fact di tre tipi e dimensioni conformed. Il messaggio è diretto: senza grain scritto e senza tipo di storicizzazione dichiarato, ogni join diventa ambiguo e ogni media sui prezzi mente nei roll-up.

Domande per ripassare

1. Cosa rappresenta esattamente una riga della tua fact table?
2. Quale misura è additiva e quale non si somma sulle date?
3. Quando usi una dimensione conformed e quando una degenerate dimension?
4. Quale tipo di slowly changing dimension conserva la storia del cliente?