ClickHouse in the modern data warehouse

Quando una dashboard operativa deve rispondere in tempo reale su miliardi di righe, il warehouse general-purpose costa troppo o risponde troppo tardi. ClickHouse è il motore specializzato che assorbe quel carico interattivo, ma solo se il modello fisico segue i pattern di lettura: colonne, partizioni, sorting key e compressione. In questa lezione impari a valutarlo con volumi, latenze e costi misurati, non con impressioni.

L’idea in una frase

ClickHouse accelera le aggregazioni interattive su miliardi di righe affiancando il warehouse principale dove serve latenza bassa. Non lo sostituisce: assorbe il carico interattivo che il warehouse non serve a costi ragionevoli.

La sequenza di valutazione

1. Misura volumi, filtri ricorrenti, freshness richiesta e latenza attesa prima di valutare il motore.
2. Confronta il workload con la matrice di scelta tra motore real-time e warehouse con governance completa.
3. Progetta sorting key, partizioni e compressione insieme ai pattern di lettura dominanti e misurati.
4. Replica dal warehouse principale solo aggregati e ultimi periodi con pipeline dichiarata e monitorata.
5. Verifica latenza, costi e freschezza su query reali prima di migrare dashboard operative critiche.

Quando il problema diventa concreto

ClickHouse rende velocissime le query su grandi volumi, ma solo se il modello fisico segue i pattern di lettura: colonne, partizioni, sorting key, compressione e merge. Il problema arriva quando una dashboard operativa deve rispondere in tempo reale su miliardi di righe e il warehouse general-purpose costa troppo o risponde troppo tardi. A quel punto o accetti la latenza o introduci un motore specializzato e paghi la complessità in più. Parti sempre dai workload: colonne lette, filtri applicati, freshness richiesta e query interattive.

Step	Question to ask	Expected output
Decision	Che cosa cambia se introduciamo ClickHouse nel sistema?	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 philosophy of ClickHouse

Snowflake è un warehouse general-purpose. ClickHouse è specializzato sulle aggregazioni enormi: ingestione di milioni di righe al secondo, risposte analitiche in millisecondi, compressione da cinque a dieci volte rispetto a Parquet, scalabilità orizzontale senza single point of failure. La specializzazione spiega perché funziona nel suo dominio e delude fuori.

ClickHouse o Snowflake: quando usare cosa

Scenario	Use	Why
Real-time operational dashboard	ClickHouse	Sub-second latency on billions of rows
Financial report with complex transactions	Snowflake	ACID, governance, audit
Log analytics (CDN, firewall, DNS)	ClickHouse	Massive ingestion, extreme compression
Data modeling with slow evolution	Snowflake	Tooling dbt, catalog, lineage
Time-series (IoT, monitoring)	ClickHouse	Specialized engines for time-series

In sintesi: usa ClickHouse per dashboard live e log massivi, e Snowflake per verità canonica con governance, audit e modellazione lenta.

Verdetto: ClickHouse vince per dashboard live e log massivi, Snowflake per la verità canonica con governance e audit; nella maggior parte dei casi ClickHouse affianca il warehouse, non lo sostituisce.

ClickHouse come acceleratore

Nella maggior parte dei casi reali non sostituisce il warehouse: lo affianca con repliche di aggregati e ultimi periodi per query interattive.

Snowflake/BigQuery (source of truth, governance, ETL)
        │
        ▼
ClickHouse (aggregazioni veloci, dashboard live)

I dati canonici restano nel warehouse con modellazione dbt, test e governance. Il subset replicato serve dashboard live. Il pattern è usato da Cloudflare, Uber e Spotify e tiene la verità in un posto solo con query veloci dove servono.

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: ClickHouse. (2024). “ClickHouse vs Traditional Data Warehouses.” clickhouse.com.

Il motore nato per un problema reale

ClickHouse nasce dentro Yandex per interrogare Yandex.Metrica, uno dei servizi di web analytics più trafficati al mondo, e viene pubblicato open source nel 2016. La promessa che lo distingue è quantitativa: aggregazioni su miliardi di righe in meno di un secondo su hardware comune, grazie a storage colonnare e vettorizzazione spinta. Da allora lo adottano Cloudflare, Spotify e decine di piattaforme real-time, spesso affiancato al warehouse principale invece che al suo posto. La decisione architetturale della lezione nasce da qui: ClickHouse non sostituisce il warehouse, assorbe il carico interattivo che il warehouse non serve a costi ragionevoli.

Domande per ripassare

1. Quale workload con volumi e latenza giustifica ClickHouse nel tuo stack?
2. Quale matrice ti dice se usare ClickHouse o Snowflake per un report?
3. Quali sorting key e partizioni seguono i tuoi pattern di lettura dominanti?
4. Quale subset replichi dal warehouse con quale freshness garantita?