System Design Interview
Real-time analytics pipeline inspired by Kafka, Flink and Druid/Pinot architectures: durable replayable ingestion, a raw event lake, event-time windowed aggregation with exactly-once rollups, late-event handling, an OLAP store for ad-hoc queries and anomaly alerts. A teaching design.
See why inserting every event and aggregating on read can't support live dashboards.
What was missing: Nothing yet.
Why that's risky: 200K inserts per second overwhelm a transactional database, and each dashboard refresh scans millions of rows while ingestion waits.
What gets added: One server inserting each event as a row, with dashboards running GROUP BY queries over raw rows.
Trade-offs: Exact and simple, but reads and writes fight over the same table.
Never lose an event, keep every raw event cheaply, and stop dashboards from scanning raw data.
What was missing: Events went straight into a transactional table, and dashboards aggregated raw rows.
Why that's risky: Dashboards are up to an hour behind, which isn't 'real time'.
What gets added: An ingest API that authenticates and validates batches onto a durable, replayable raw event log; a lake archiver writing raw events as hourly columnar files to object storage; an hourly batch job producing rollups; and a query API reading those rollups.
Trade-offs: Batch rollups are simple and exact, but freshness is limited by the batch interval.
Update dashboards within a minute, correctly, even when events arrive out of order.
What was missing: Rollups were computed hourly.
Why that's risky: Malformed events can stall a partition, and events arriving after their window closed are dropped.
What gets added: A stream processor that aggregates events into one-minute windows by event time, using watermarks to decide when a window is complete and checkpoints so each event counts exactly once; rollups are upserted idempotently. A query cache keeps finished windows so only the current one is recomputed.
Trade-offs: Waiting for the watermark delays a window by a few seconds in exchange for counting late-but-not-too-late events correctly.
Handle bad and late data without stopping the pipeline.
What was missing: One bad event could block a partition, and late events were lost.
Why that's risky: Questions the rollups weren't designed for (new dimensions, unique users over a quarter) can't be answered.
What gets added: Malformed events go to a dead-letter queue instead of blocking; a replayer reprocesses them once fixed. Windows accept events up to an allowed lateness and are re-upserted, and anything later is corrected by replaying from the lake.
Trade-offs: Allowing lateness means a past minute's number can still change slightly.
Answer questions nobody planned for, over long time ranges.
What was missing: Only pre-defined rollups could be queried.
Why that's risky: Nobody is told when a metric suddenly drops, and pipeline lag goes unnoticed.
What gets added: An OLAP loader feeding a columnar store of raw events in micro-batches, with HyperLogLog sketches for approximate distinct counts; the query API routes ad-hoc and long-range questions there.
Trade-offs: Distinct counts are approximate (about 1% error) in exchange for tiny memory and fast merges.
Notice problems in the metrics and in the pipeline itself.
What was missing: No alerting on metrics, no producer quotas, no pipeline monitoring.
Why that's risky: Remaining risk is regional: each region's pipeline should keep running if another fails.
What gets added: Completed windows flow to an anomaly detector that compares them with seasonal baselines and alerts the owning team; per-producer quotas at ingest; and monitoring of ingest lag, consumer lag, watermark delay and dead-letter volume.
Trade-offs: Seasonal baselines need weeks of history before alerts are trustworthy.
Prefer at-least-once with idempotent processors and de-duplication keys. Exactly-once often adds complexity without clear benefits; track offsets and use transactional writes only where strictly required.
Use event-time processing with watermarks and allowed lateness. Emit corrections to the same aggregate keys; maintain compactable state and a backfill worker to reconcile windows that exceed lateness thresholds.
Front the query API with a cache for hot dashboards, denormalize aggregates for the most common queries, and use replicas for read scaling. Limit query shapes, paginate, and pre-warm caches around known spikes.
Alert on consumer lag, watermark stall, processing error rate, queue depth, and end-to-end freshness. Create SLOs and burn-rate alerts to react before users notice.
Throttle backfills, write to side tables or temp partitions, and validate results before swapping. Use idempotent jobs and pause conflicting online tasks. Monitor impact on primary queues and stores.
You're in the middle of an interview session. Leaving now will end your current attempt.
Explore concept overviews, real-system examples, key tradeoffs, and interview talking points for each roadmap section.
You've conquered this phase. These are the skills you now own:
