The 100 problems, solved / Data pipelines and operations / 084 · Design a real-time analytics pipeline

Design a real-time analytics pipeline

Billions of events a day, dashboards that lag by seconds, and exact daily totals by morning.

Problem 084 of 100Data pipelines and operationsL51 Nov 2026
Concepts this design leans on

01The design, in the six-step framework

1. Requirements

Functional.

  • Web and mobile clients emit events (page views, clicks, purchases, playback) with properties; back-end services emit their own.
  • Dashboards show live metrics (active users now, events per minute by country, top pages) that are at most a few seconds behind.
  • Analysts run ad hoc queries over months of events, and nightly reports must be exact (billing-grade daily totals).
  • Out of scope: the visualisation tool itself, machine learning on the events.

Non-functional.

  • 5 billion events per day, about 60,000 per second average and 300,000 at peak.
  • Live dashboards lag by under 10 seconds; exact daily totals ready by 06:00 for the previous day.
  • No event lost; duplicates (retries from flaky clients) must not inflate counts.
  • Ad hoc query over one month returns in under a minute.
Back of the envelope

5 × 109 events per day at 500 bytes raw is 2.5 TB per day; compressed columnar storage at 10:1 is 250 GB per day, 90 TB per year.

Peak ingest: 300,000 events/s × 500 B = 150 MB/s into the event log

Live state for the dashboards (counts per minute per dimension, about 1 million distinct keys) is under 1 GB and lives in memory in the stream processors.

2. Core entities

  • Event: event_id (client-generated UUID), type, user_id (hashed), session_id, client_timestamp, server_timestamp, properties, schema_version.
  • Live aggregate: (metric, dimensions, minute window) → count, sum, approximate distinct users.
  • Partition file: date/hour, event type, columnar file in object storage, row count, min/max timestamps.
  • Daily report: date, metric, exact value, computed_at, source files.
  • Schema: event type, version, fields and types; events that fail validation go to a quarantine table.

3. API

  • Clients: POST /collect batch of events with event_ids; returns 200 as soon as the batch is durable in the log. SDKs retry with the same ids.
  • GET /live/{metric}?by=country&window=1m → current values; served from the stream processors' state, polled by dashboards every few seconds.
  • POST /query {sql, from, to} → job id; GET /query/{id} → results. The warehouse path for analysts.
  • GET /reports/daily?date= → the exact numbers, with a flag saying whether late data has been folded in.

4. High-level design

SourcesIngestSpeed layerBatch layerhourly sinkexact dailiesClients and servicesCollector API(validate, dedup window)Event log(Kafka-style, 7 days)Stream processors(windows, watermarks)Live store(keyed counters)Landing files(hourly, columnar)Warehouse(ad hoc SQL)Nightly exact jobsDashboards and analysts

An event, twice:

  1. The collector validates the event against its schema, stamps a server time, and appends it to the event log, partitioned by event type and a hash of user id; the client gets an acknowledgement only after the log has it.
  2. Speed path: stream processors read the log, deduplicate by event_id within a window, bucket events into one-minute windows per dimension, and update counters in the live store; dashboards read those counters.
  3. Batch path: a sink writes the same log into hourly columnar files in object storage, partitioned by date and type; the warehouse reads those files for ad hoc SQL.
  4. At night, exact jobs read the day's files, deduplicate by event_id globally, apply late events, and write the daily report, which overwrites whatever the speed path showed for that day.

5. Deep dives

How do live counts stay close to right with duplicates and late events?

Clients retry on timeouts, so duplicates are normal; the stream processor keeps a set of recent event_ids per partition (a few minutes, in memory, backed by a Bloom filter for size) and drops repeats. Mobile events arrive late, sometimes by hours, because the phone was offline; windows are assigned by the event's client timestamp, not arrival time, and the processor keeps windows open until a watermark passes (say, 5 minutes behind the newest event seen). Events later than the watermark update the window anyway, as a correction, and are counted as "late" in a metric that tells us how wrong the live numbers were. The key sentence for the interviewer: live numbers are approximate on purpose, and the batch path replaces them with exact ones.

Why two paths, and how do the numbers reconcile?

Because the two requirements pull apart: seconds of lag with bounded memory means approximate (windows close, late data is a correction, distinct counts use sketches), and exact billing totals mean a full pass over all of the day's data with global dedup, which takes hours. Running both over the same immutable log (the "lambda" shape) is the honest design; the simpler "kappa" shape, where only the stream exists and exactness comes from replaying the log with larger windows, works when the lateness bound is small and memory is cheap. We reconcile by having the batch job overwrite the daily aggregates and by alerting when live and batch differ by more than 1 percent, which usually means a dedup bug or a broken client.

QuestionSpeed layerBatch layer
LagSecondsHours
DedupRecent window, Bloom filterGlobal, exact
Distinct usersHyperLogLog, about 1 percent errorExact count distinct
Late eventsCorrections up to the watermarkAll of them, up to the report run
ServesDashboards, alertsReports, billing, ad hoc analysis

How does the ad hoc query over a month finish in a minute?

Columnar files partitioned by date and event type mean a query over "purchases in March by country" reads only the purchase partitions for 31 days and only the country and amount columns: perhaps 2 GB instead of 7 TB. Files carry min/max statistics per column block so filters skip blocks. The warehouse engine scans in parallel across hundreds of workers; 2 GB over 100 workers is milliseconds of IO each. Small files are the enemy (hourly sinks produce many), so a compaction job merges them into larger files daily. Common dimensions get pre-aggregated rollup tables refreshed hourly, which answer most dashboard-style questions without touching raw events.

How do schema changes not break everything?

Events carry a schema version; the collector validates against a registry and quarantines unknown shapes instead of dropping them. Adding a field is backward compatible; renaming or retyping is a new version, and the stream and batch jobs read both until the old one ages out. The registry is the contract between the client teams and the analytics team, and the quarantine table is the alarm that someone shipped a client without registering.

6. What a strong answer sounds like

  • Lead with the two conflicting requirements and say that the design has two paths over one immutable log because of them.
  • Be precise about time: event time versus arrival time, windows, watermarks and late corrections.
  • Handle duplicates with client-generated ids at two scopes: a window in the stream and globally in batch.
  • Give the storage arithmetic and explain why columnar, partitioned files make a month-long query cheap.
  • Expected follow-ups: exactly-once from the stream processor into the live store (idempotent upserts keyed by window); privacy (hash user ids at the collector, delete by user with partition rewrites); multi-region collectors and ordering.
  • Common mistake: promising exact live numbers, or counting distinct users with a plain set that grows without bound.

02Where this question was reported