Dev Learning
Data pipelines

Click analytics pipeline

Design a system that ingests click and view events from a large website, and lets people query aggregates — clicks per page per hour, top referrers, conversion counts — over the last two years.

coreMessage queues and backpressureIdempotency, and why exactly-once is a mythDistributed countersPartitioning (sharding)Change data captureBatch and stream processingObject storage and presigned uploadsCapacity planning and cost

Where’s the slack?

Ingestion: must not drop. Aggregates: minutes to hours. Historical: rebuild whenever.

Minutes, sometimes hours, of slack on every output. In this group the usual mistake is failing to use it.

Almost no decision depends on a click count within seconds of the click. A marketing dashboard refreshed every five minutes is fine; a daily report tomorrow morning is fine. That is a lot of slack, and the design should spend it: batch aggressively, write in large chunks, and process on cheap capacity.

The temptation is to build this like a request path: low latency, per-event processing, immediate consistency. That is how pipelines end up many times more expensive than they need to be. Each extra second of accepted latency lets you batch more and pay less, and here you have hundreds of them.

The one thing with no slack is ingestion itself. The browser fires an event and moves on; if the endpoint is down or slow, that event is gone and no retry will recover it. So the accepting edge must be highly available and cheap, and it must do nothing but accept. All processing goes behind it.

There is usually one narrow real-time carve-out: a "live visitors" counter that must be current within seconds. Treat it as a small separate approximate path, never as a reason to make the main pipeline real-time.

So: Hours of slack means batch everything, and it means a wrong aggregate can be fixed by reprocessing rather than by a scramble. That is why the raw events must be kept. No slack on ingestion means the accepting edge does nothing but write to a durable log.

Work it through

Five stages, gated. You write your answer for each stage before its model answer unlocks. That is the core of the method.

Loading your progress…

Reference material for this problem. The diagrams below are here to check your own against once you have drawn it, not to read first.

The lifecycle

EmittedbrowserAcceptededgeIn the logsystemAggregatedjobQueriedanalystTab closed mid-requestunload-safe send and client batching — some loss is unavoidableClient retriesclient-generated event id, deduplicated downstreamIngestion downno recovery — so the edge does nothing but append to the logEvent arrives 3 hours lateopen window plus batch recomputation; aggregates are not finalJob ran twicewrite replacements per bucket, never incrementsJob was buggy for a weekfix and reprocess from the raw archiveTwo years at full granularitytiered granularity — hourly recent, daily older
An event, browser to dashboard. The late-arrival branch defines this group, and the "job was wrong" branch is what raw retention exists for.

The numbers

Events ingested~350,000 / sec at peak
Raw data per day~2 TB / day
Hourly rollup rows per day~24M rows — ~400x smaller

Raw data is impossible to query interactively and essential to keep. Rollups are instant and 400x smaller. So the hard part is ingestion reliability and that reduction. Everything else follows from the funnel shape.

The reduction ratio is the design.

The architecture

correctsBrowsersIngest edgeaccept + append onlyDurable logpartitioned, replayableStream aggregatorfresh, approximateRaw archivecolumnar, 2 yearsBatch recomputeauthoritativeRollup storesmall, fastDashboardsAnalystsad hoc over raw
A funnel. Everything is kept cheaply so it can be reprocessed; almost nothing is served from it directly.

The mechanism

ProducerfastQueuebacklogWorkerslow
four inone out — the queue absorbs the difference, and grows
Producers are faster than consumers, and the log absorbs it. With finite retention, the alert has to be on lag against retention. Past that point, data is gone.

The decision this turns on

A
Stream processing
  • —Aggregates are seconds fresh, which is what a live dashboard needs.
  • —Late events are hard: windows must close eventually, and something arrives after.
  • —A bug produces wrong numbers continuously until you notice.
  • —Runs continuously, so it costs continuously.
B
Batch over the archive
  • —Sees all the data including late arrivals, so it is the correct answer.
  • —Easily rerunnable: fix the code, reprocess, numbers become right.
  • —Hours behind, which is useless for anything live.
  • —Runs on interruptible capacity because nothing is waiting on it.
Which one: Both, with clear roles: streaming for freshness, batch as the authoritative correction that overwrites it. The cost is maintaining the same aggregation logic twice, which is a real burden. But the slack budget in this group makes batch correctness available, and refusing to use it is how pipelines end up permanently wrong without anyone noticing.
Streaming or batch. Many pipelines end up with both, and it is worth being able to say why.

Follow-ups you should expect

Many interviews are decided in the follow-ups, not the first diagram.

The queue is backed up by an hour. What do you do?One key gets 90% of the traffic. What breaks, and what do you do?The write worked but the response was lost and the client retried. What happens?Why a queue instead of just calling the service directly?What does this cost per month?Now it must be privacy compliant — users can demand deletion of everything about them.Which alert fires first when this system starts failing?10x traffic tomorrow. What fails first?

Same shape, other products

Enormous volume in, aggregates out, and nobody is waiting for any single event.

Here you can usually afford to be minutes behind. Spend that slack: batch aggressively, and design for replay when a job is wrong.

Others with this shape: Analytics ingestion · Click aggregation · Metrics

Before you leave
Try this problem again in a week without reading anything. Comparing what you write then with what you wrote today shows you what stuck.
Stuck, or new to this?Read the full worked solutionAll five stages end to end, with the diagrams and the costs, written out the way you would say it. Best read after you have had a go.Read it →

How it is really built

Write your own answer first. Then read how teams who run this at scale describe it. Their constraints were real, and the write-ups often say what their choices cost.