Designing a Real-Time OLAP System: Segments, Ingestion, and the Druid/Pinot Shape

Dashboards want two incompatible things: query the last thirty seconds (which lives in a stream) and the last two years (which lives in columnar files), in one query, in under a second, while a million events per second pour in. Warehouses answer the second half; stream processors answer the first; real-time OLAP systems — Druid, Pinot, ClickHouse in its streaming configurations — exist to answer both at once. Their shared architecture is worth learning as a shape, because it recurs any time "fresh" and "historical" must pretend to be one table.

The two-tier read path

                     broker (scatter-gather, merge)
                    /                        \
real-time nodes                          historical nodes
  consume Kafka directly                   serve immutable segments
  query in-memory rows (seconds old)       memory-mapped from local disk
  build segments as data ages              loaded from deep storage

Real-time nodes consume the stream and make events queryable in seconds — held row-oriented in memory, indexed incrementally. Historical nodes serve segments: immutable, columnar, heavily indexed files covering a time chunk (say one hour) of one datasource. The broker plans each query across both worlds — recent interval → real-time nodes, older intervals → the historical nodes owning those segments — and merges partial aggregates. The client sees one table; the system runs two databases stitched by time.

The handoff is the elegant part: when a real-time node's in-memory chunk ages past its window, it seals it into a segment, publishes it to deep storage (S3 — the durability plane, never queried directly), registers it in metadata, and historical nodes pull it down to serve. Local disk is cache, deep storage is truth: losing any serving node costs re-download time, not data — the same "serving tier is a rebuildable view" stance as everywhere else in this series, and the reason these systems scale storage and compute independently.

Inside a segment: why queries are fast

Per column: dictionary encoding (strings → ints), compressed value arrays, and inverted bitmap indexes (value → compressed bitmap of matching rows — Roaring bitmaps), so filters become bitmap ANDs and aggregations scan only surviving positions of only referenced columns. Time is the first-class partition dimension — every query carries a time range, every segment covers one, so time pruning eliminates most segments before any I/O. Optional rollup at ingestion pre-aggregates identical dimension tuples per time grain (sum/count/min/max — the mergeable family), often collapsing raw events 10-40× — the classic trade: query speed and storage for the loss of row-level detail; keep a raw copy in the lake for the day someone needs the needle, serve the dashboard from rollups.

Ingestion semantics

Stream consumption is partition-offset-anchored (Kafka), with segment publication and offset commits tied together so each event lands in exactly one published segment — exactly-once materialization via the same "atomic commit of data + position" trick as Flink sinks and CDC consumers. Late events beyond the real-time window can't enter sealed segments; they arrive via compacting re-ingestion (rebuild the affected hour's segment from lake data — batch and stream writing the same table, reconciled by segment replacement, which is lambda architecture with an honest merge story). Segment replacement being atomic (metadata swap, version bump) is what makes backfills safe while queries run — the lakehouse pointer-swap, applied per time chunk.

The operational surface

Segment balancing (a coordinator spreads segments across historicals by size/traffic, replicates hot ones), tiering (last week on NVMe nodes, last year on dense cheap nodes — query cost follows data age), and the broker's scatter-gather amplification problem: a 2-year query fans to thousands of segments; guardrails are mandatory — per-query segment/row budgets, timeouts, and result-level caching for dashboard queries that repeat every 30 seconds. GroupBy cardinality is the other cliff (grouping by user_id across a billion rows is a shuffle wearing an OLAP costume) — high-cardinality group-bys get pushed to approximate sketches (HLL, theta) or refused; declaring that boundary is part of the design, not a failure of it.

Interview probe	Answer sketch
vs the warehouse you already have?	Warehouses batch-load and optimize for complex joins; this shape trades join power (mostly denormalized/star) for second-fresh ingestion and sub-second slice-and-dice at high QPS (it's a serving system — thousands of dashboard/API queries/sec)
Duplicate events from producer retries?	Offset-anchored exactly-once into segments; upstream idempotency still matters for the lake copy
One dashboard hammers one hour of one datasource?	Segment replication for the hot chunk + broker result cache — hot-object playbook again
Schema change on a datasource?	Segments are self-describing; new columns appear going forward, old segments return null — schema-on-segment, evolution without rewrite

The reusable shape: stream-fed mutable head + immutable columnar body + deep-storage truth + time-pruned scatter-gather on top. Recognize it once and you'll see it in metrics TSDBs, log analytics, and every "real-time analytics" product pitch — the logos change, the three tiers don't.