Build a columnar OLAP store (ClickHouse / Druid style) (13 scenes)
Scene 13

Design canvas — pick a workload, ship a schema

Every column-store knob — sort key, partition, encoding, skip index, MV, denormalization — is a deliberate choice driven by the workload shape, and the right schema for observability is wrong for BI which is wrong for event analytics.

Previously

We've now seen every lever — layout, encoding, execution, parts, merge, sort key, sparse index, skip index, MVs, denormalization. The last move is to face a real workload and pick the configuration end-to-end.

Scene 13

Design canvas: pick a workload, ship a schema

  1. Watch
  2. Try it
  3. Predict
  4. Capture
WORKLOADACTIVEObservability / metricscardhigh · per-service tagswriteappend-heavy, time-orderedquerytime range × service filterBI dashboardscardmedium · dim joinswritenightly batch + tricklequerygroup-by, top-N, p95Event analytics / clickstreamcardvery high · user_id, urlwritestreaming event firehosequeryfunnels, retention, cohortENGINE CONFIGsort key(service, ts)partitionmonthlyskip indexeshost:set(100)trace_id:bloom_filterMV rollupsper-minute service latency (AggregatingMergeTree)denormalizationOFF · joined at queryshards × replicas4 shards × 2 replicasSIMULATIONinsert rate195.0k rows/sparts vs threshold64 / 300query p99180 msmerge queue2 pendingMV drift0%✓HEALTHYall meters below thresholds — design fits the workloadEvery knob traces back to a named earlier scene.
What to watch for

Workload A — Observability. Defaults are pre-loaded: ORDER BY (service, ts), partition monthly. The verifier walks green and the chips trace every knob back to an earlier scene. This is what twelve scenes of column-store thinking cash out to.

Continue unlocks when the animation finishes.
Implementation

Highlighted lines are the ones running in the diagram right now.

Stop A — Observability schema
(service, ts) prefix · monthly partition · skip indexes on host/trace_id
CREATE TABLE events (
  service   LowCardinality(String),
  host      LowCardinality(String),
  ts        DateTime CODEC(Delta, ZSTD),
  trace_id  String,
  metric    Float64,
  INDEX idx_host  host     TYPE set(100)       GRANULARITY 4,
  INDEX idx_trace trace_id TYPE bloom_filter   GRANULARITY 4
) ENGINE = MergeTree
ORDER BY (service, ts)
PARTITION BY toYYYYMM(ts)
SETTINGS index_granularity = 8192;
Stop B — BI dashboards schema
fact table + AggregatingMergeTree MV · LowCardinality dim columns
CREATE TABLE events (
  service     LowCardinality(String),
  region      LowCardinality(String),
  ts          DateTime CODEC(Delta, ZSTD),
  customer_id UInt64,
  revenue     Decimal(18, 2)
) ENGINE = MergeTree
ORDER BY (service, ts)
PARTITION BY toYYYYMM(ts);
 
CREATE MATERIALIZED VIEW daily_revenue_mv
ENGINE = AggregatingMergeTree
ORDER BY (toDate(ts), region) AS
SELECT toDate(ts) AS d, region,
       sumState(revenue) AS revenue_state
FROM events GROUP BY d, region;
Stop D — Anti-pattern: per-row UPDATE
verifier REFUSES — every UPDATE rewrites a whole part
CREATE TABLE inventory (
  sku_id   UInt64,
  qty      Int32,
  price    Decimal(18, 2),
  updated  DateTime
) ENGINE = MergeTree
ORDER BY (sku_id)
PARTITION BY toYYYYMM(updated);
 
-- Per-row UPDATE arrives from the OLTP app:
ALTER TABLE inventory UPDATE qty = qty - 1
  WHERE sku_id = 42;   -- rewrites every part touching `qty`
 
-- Verifier REFUSES: parts are immutable; one new part per write.
-- Merges cannot keep up → 'too many parts' fires in minutes.
-- Right answer: use Postgres, or stage in PG and CDC to ClickHouse.
Wrong knobs — yellow / red on stops A & B
leading with ts → useless sparse index · hourly → too many parts
CREATE TABLE events_bad (
  service LowCardinality(String),
  ts      DateTime,
  metric  Float64
) ENGINE = MergeTree
ORDER BY (ts)
-- Leading with ts: every WHERE service=... scans every
--   granule; sparse index prunes nothing.
PARTITION BY toStartOfHour(ts);
-- Hourly × shards → tiny-part explosion within minutes;
--   INSERTs throw 'too many parts', merges never catch up.
Sources

Where this sits in Build a columnar OLAP store (ClickHouse / Druid style)

Scene 13 of 13, in the Design canvas act — Pick the workload, configure the engine, watch each failure mode cite its scene.. Every knob — sort key, partition, skip index, MV, denormalization — is a workload-shaped choice. The right observability schema is wrong for BI.

All 13 scenes in Build a columnar OLAP store (ClickHouse / Druid style) · Every curriculum

Built with Arqly
Every scene in Build a columnar OLAP store (ClickHouse / Druid style) builds on the one before it.All 13 Build a columnar OLAP store (ClickHouse / Druid style) scenes