Modern data warehousing: architecture and concepts

Il problema non è conoscere il warehouse in astratto: è decidere quando i dati arrivano da fonti diverse, quando due dashboard danno numeri diversi per la stessa metrica o quando una query di ieri oggi costa troppo. Per questo serve un’architettura che separi sistemi transazionali, storage analitico e mart, con ogni livello che ha un compito preciso: conservare, trasformare, servire o governare. Qui impari a leggerla e a scegliere la piattaforma giusta.

L’idea in una frase

Il data warehouse moderno separa sistemi transazionali, storage analitico e mart per servire una sola verità condivisa. Ogni livello ha un compito preciso: conservare, trasformare, servire o governare.

La sequenza di progettazione

1. Separa sistemi transazionali, lake, warehouse, mart e semantic layer con responsabilità scritte.
2. Fissa grain, ownership e contratti prima di modellare fatti e dimensioni interrogabili dal business.
3. Scegli la piattaforma in base a workload, volumi e costi con baseline di confronto esplicita.
4. Consolida sorgenti operative in un core modellato e servi i team tramite mart dedicati e documentati.
5. Collega ogni tabella a una decisione con refresh, qualità e accessi dichiarati e verificabili.

Quando il problema diventa concreto

Il problema non è conoscere il warehouse in astratto. È decidere quando i dati arrivano da fonti diverse, quando due dashboard danno numeri diversi per la stessa metrica o quando una query di ieri oggi costa troppo. Leggi l’architettura distinguendo transazionale, lake, warehouse, mart e semantic layer. Ogni livello deve avere un motivo: conservare, trasformare, servire o governare. Un warehouse affidabile semplifica le domande difficili perché rende espliciti grain, ownership e contratti.

Step	Question to ask	Expected output
Decision	Che cosa cambia se l’architettura del warehouse è più chiara?	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

OLTP e OLAP

La prima distinzione è tra database transazionali e analitici, perché servono mondi diversi.

	OLTP (Transactional database)	OLAP (Data Warehouse)
Mission	Manage real-time operations	Support analysis and decisions
Query	Few rows, simple (SELECT by ID)	Many rows, complex (GROUP BY, JOIN)
Schema	Normalized (3NF), no duplicates	Denormalized (star schema), optimized for reading
Examples	PostgreSQL, MySQL for apps	Snowflake, BigQuery, Redshift
Users	Applications	Analyst, BI tools

Star schema secondo Kimball

Il modello dimensionale di Kimball poggia su due tabelle. La fact table sales fact contiene misure numeriche e foreign key con una riga per evento: amount, quantity, date key e customer key. La dimension table dim customer contiene attributi descrittivi con una riga per entità: name, country e segment. Il vantaggio è la semplicità di lettura e la velocità: dimensioni piccole e fatti grandi e indicizzati, compatibile con ogni tool BI.

Snowflake, BigQuery, Redshift

Le tre piattaforme hanno architetture diverse e la scelta dipende da dove sei già e da quanto sono prevedibili i workload.

	Snowflake	BigQuery	Redshift
Architecture	Decoupled storage/compute	Serverless, shared nothing	MPP cluster
Scalability	Configurable warehouse size	Automatic, slot-based	Add nodes to the cluster
Semi-structured	Excellent (VARIANT)	Good (JSON)	Good (SUPER)
Cost	Compute + storage credits	5 dollari per TB scansionato in on-demand	Node/hour

Per un team dati moderno Snowflake e BigQuery sono le scelte dominanti. Redshift resta valido per chi è già in AWS e ha workload prevedibili.

In sintesi: scegli Snowflake per workload variabili con governance, BigQuery per stack Google serverless e Redshift per workload AWS prevedibili già consolidati.

Verdetto: Snowflake vince per workload variabili con governance, BigQuery per stack Google serverless e Redshift solo per workload AWS prevedibili già consolidati.

Il modello di maturità del warehouse

Un warehouse cresce per stadi. Al primo stadio c’è il raw data dump con copie grezze delle tabelle operative: ingestion facile e query impossibili. Al secondo arriva lo star schema con fatti e dimensioni: query veloci con ETL dedicato. Al terzo si passa a data vault o data mesh per decine di team indipendenti, dove ogni team possiede i propri dati e li espone tramite contratti.

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

Il momento in cui il warehouse è diventato cloud

Nel novembre 2012 Amazon Web Services annuncia Redshift al re:Invent di Las Vegas come primo data warehouse cloud con architettura colonnare MPP. La novità è operativa prima che tecnica: capacità su scala petabyte con pricing orario per nodi invece di licenze e appliance da milioni di dollari. Da lì il mercato si spacca tra cluster gestiti e modelli serverless come BigQuery e Snowflake, con storage e compute separati. Il punto è questo: l’architettura vince quando rende espliciti layer, grain e costi prima della modellazione.

Domande per ripassare

1. Quale livello tra lake, warehouse, mart e semantic layer risponde alla tua domanda?
2. Quale grain rende confrontabili due dashboard sullo stesso KPI?
3. Quale piattaforma tra Snowflake, BigQuery e Redshift si adatta al tuo workload?
4. Quale contratto di ownership impedisce metriche divergenti tra team?