Click analytics pipeline — a full worked answer
The whole thing, start to finish, in the order you would actually say it. Read it to learn the shape of a strong answer — then go and produce one yourself, because reading is not the same skill as producing.
The question
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.
Data pipelines. 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.
Before anything: where is the slack?
How long the system has before a person notices. Ask this before you draw a single box — it decides more of the design than scale does.
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.
Requirements
about 5 minWhy this stage exists: Because the question is deliberately vague, and every box you draw before this is a guess.
Assumptions: a large site, billions of events a day, global. Events are small: a page id, a user or session id, a timestamp, a referrer, a device, maybe 200 bytes. Queries come from dashboards and analysts, so query volume is low but query complexity is high. Two years of retention.
First question, which decides the storage design: must counts be exact, or is a small error acceptable? Exact distinct-user counts require keeping every identifier, while approximate counts fit in kilobytes using a sketch. For revenue and conversions people want exact; for unique visitors, approximate is usually fine. I will assume exact for money-related counters and approximate for distinct counts, and I would say so explicitly rather than picking one for everything.
Second question: are the queries known in advance, or ad hoc? Known queries mean I can precompute aggregates and serve them instantly, while ad hoc analysis means keeping the raw events and running real queries over them. I will assume both: precomputed rollups for dashboards, and raw events retained for analysts. Mature pipelines usually end up with both.
Scope: accept events reliably, aggregate them, and serve both dashboard and ad hoc queries. Out of scope: the tracking client itself, identity resolution across devices, and privacy compliance mechanics. I will note, though, that retention and deletion requests are a real constraint on any design that keeps raw events for two years.
What an interviewer is ticking off in this stage
- Asked exact versus approximate counts and split the answer by metric type
- Asked whether queries are known in advance or ad hoc
- Concluded both precomputed rollups and raw retention are needed
- Noted query volume is low but complexity is high
- Raised privacy and deletion as a real constraint on two-year retention
- Proposed a scope
Actors and lifecycle
about 6 minWhy this stage exists: Because the happy path is the easy half. The failure branches are where the design actually lives.
Actors: the browser or app emitting events, the analyst querying, and the system, which decides what is a duplicate, which time bucket a late event belongs to, when an aggregate is final, and what to do with events that arrive after that.
The chain: emitted by the client → accepted at the edge → written to a durable log → aggregated into rollups → queried → eventually expired.
Failure branches. At emitted: the user closes the tab before the request completes, so the event is lost. Mitigate it with a send mechanism designed to survive page unload (such as navigator.sendBeacon) and by batching events client-side with a flush on unload. Some loss is unavoidable and should be acknowledged rather than pretended away. At emitted: the client retries after a timeout, producing duplicates. So each event carries a client-generated id, and deduplication happens downstream.
At accepted: the ingestion endpoint is down. This is the branch with no recovery, which is why that endpoint must do almost nothing: accept, append to a log, return. No validation against a database, no enrichment, no synchronous processing. Anything that can fail is pushed behind the log.
At aggregated: an event arrives three hours late, from a mobile client that was offline, after its hour has already been aggregated and reported. This is the defining problem of the group. The answer is that aggregates are not final. They are recomputed for a window of time, and dashboards must tolerate yesterday's number changing slightly. Alternatively, late events go into a correction bucket. Either way it is a design decision, not an edge case, and assuming events arrive in order is a common failure here.
At aggregated: the job has a bug and produces wrong numbers for a week. This is why raw events are retained: you fix the job and reprocess. A pipeline that only keeps aggregates has no way back, which is the strongest argument for keeping raw data.
At aggregated: the job runs twice and double-counts, so aggregation must be idempotent: write the result for a time bucket as a replacement, not as an increment. That choice makes reruns safe and is worth stating.
At queried: someone asks for two years of data at full granularity and the query takes an hour. That needs tiered granularity, with older data stored more coarsely.
What an interviewer is ticking off in this stage
- Traced the event from browser to expiry
- Handled loss at emission and acknowledged some is unavoidable
- Handled client retries with an event id
- Made the ingestion edge do almost nothing, and said why
- Handled late-arriving events explicitly, with a stated policy
- Retained raw events so a buggy job can be fixed by reprocessing
- Made aggregation idempotent by replacing rather than incrementing
- Handled expensive historical queries with tiered granularity
The lifecycle, drawn
Numbers
about 6 minWhy this stage exists: Because one part of this is hard and the rest is easy, and they look identical until you do the arithmetic.
Ingestion: 10 billion events a day is roughly 115,000 per second average, and traffic is peaky, so call it 350,000 per second at peak. At 200 bytes an event that is 70 MB per second at peak, and about 2 TB of raw events a day.
Raw storage: 2 TB a day for two years is about 1.5 PB. Compressed in a columnar format, realistically 150 to 300 TB, since this data compresses very well (repeated page ids, referrers and user agents). That is a real cost but an ordinary one for object storage, and it is the price of being able to reprocess.
Now the reduction, which is the number that changes the design. Aggregating to hourly counts per page: if the site has a million distinct pages, that is 24 million rows a day instead of 10 billion. About 400 times smaller, and small enough that dashboard queries are instant. Roll up further to daily and it shrinks again.
That ratio sets the design. The raw data is expensive to store and too big to query interactively; the aggregate is cheap and instant. So the system is a funnel: keep everything, serve almost nothing from it directly.
Query volume: perhaps 100 dashboard queries per second against rollups, and a handful of ad hoc analyst queries against raw data. The two have different profiles and should not share a system. One wants millisecond lookups on small tables; the other wants to scan terabytes.
Compute: aggregating 10 billion events a day is a substantial batch job but a well-understood one, and it can run on interruptible capacity because of the slack.
So the hard part here is ingestion reliability and the volume reduction: accepting 350,000 events a second without losing them, and turning them into something a dashboard can query in milliseconds. Not query latency and not the aggregation logic.
What an interviewer is ticking off in this stage
- Calculated events per second including peak
- Calculated raw storage over the retention period, with compression
- Calculated the aggregation reduction ratio, the key number
- Separated dashboard query volume from analyst query volume
- Noted compute can run on interruptible capacity
- Finished the sentence naming ingestion reliability and volume reduction
The numbers, drawn
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.
High-level design
about 12 minWhy this stage exists: Because now — and only now — you have a reason for every box you are about to draw.
Ingestion edge: a tiny stateless service behind a CDN or load balancer that validates the shape of the event, appends it to a durable log, and returns. Nothing else: no database lookup, no enrichment, no synchronous processing. Justified by the Stage 2 branch. This is the one component whose failure loses data permanently, so it must have almost no dependencies and almost no reasons to fail. It should also be geographically distributed so clients hit a nearby endpoint, since a slow ingestion endpoint means dropped events from clients that navigate away.
The log: a partitioned, durable, replayable stream (Kafka or an equivalent), partitioned by page id or site id so that events for the same key are ordered and so consumers can parallelise. Justified by 350,000 events a second and by the need to replay when a job is wrong. Retention of a few days on the stream, with everything also archived.
Archive: raw events written from the stream into object storage in a columnar format, partitioned by date and hour. Justified by the reprocessing requirement and by the compression numbers. This is the cheapest place to keep 1.5 PB, and it is the safety net that makes every other part of the pipeline correctable.
Aggregation: a stream processor computes rolling aggregates into time buckets, writing each bucket as a replacement rather than an increment so a rerun is idempotent. Justified by the Stage 2 double-run branch. Because the slack is hours, this can also be a batch job over the archive. In practice a combination is common: a fast streaming path for near-real-time dashboards, and a batch recomputation over the archive that produces the authoritative numbers and corrects the streaming ones.
Serving store: aggregates go into a columnar analytical database, keyed by dimension and time bucket. It is small (millions of rows a day rather than billions), so dashboard queries are millisecond lookups. Justified by the 400x reduction.
Ad hoc queries run over the archive with a query engine that reads object storage directly. It is separate from the dashboard store on purpose, because the two query profiles differ: one wants small fast lookups, the other scans terabytes occasionally.
Tiered granularity: recent data kept hourly, older data rolled to daily, oldest to weekly. Justified by how people query: hourly data from eighteen months ago is rarely needed, and keeping it costs storage and slows queries.
Late events: the streaming aggregation keeps a window open for a period, and the batch recomputation over the archive picks up anything later than that. So the batch path is not redundant, it is the correctness backstop for the streaming path.
What an interviewer is ticking off in this stage
- Ingestion edge does almost nothing, justified by the no-recovery branch
- Ingestion geographically distributed
- Durable partitioned log, with the partition key stated
- Raw events archived in columnar format for reprocessing
- Aggregation writes replacements, not increments, so reruns are safe
- Streaming path for freshness plus batch path as the authoritative correction
- Separate serving store for dashboards, justified by the reduction ratio
- Ad hoc queries run against the archive, separate on purpose
- Tiered granularity over time
- Late events handled by the batch recomputation
The architecture, drawn
Deep dive and tradeoffs
about 14 minWhy this stage exists: Because naming what a decision costs is the single clearest signal of seniority there is.
Hard part one: skew. One page (a homepage, or a viral article) can be a large fraction of all events. Partitioning by page id puts all of it on one partition, which becomes a hot spot that limits the whole pipeline. The fix is to partition by page id plus a random suffix, spreading one page across N partitions, then sum the N partial aggregates at the end. Cost: reads must merge N partials instead of one, and N is a tuning parameter. It is a two-stage aggregation, which is more machinery than a single pass. This is the same pattern as a sharded counter, applied to a pipeline.
Detecting skew automatically matters more than handling it, because the hot page changes daily. A count-min sketch over the stream identifies heavy hitters cheaply, and those keys get the suffix treatment automatically. Cost: a small amount of extra processing on every event to maintain the sketch, which is worth it because the alternative is a manual list that goes out of date.
Hard part two: distinct counts, the genuinely hard aggregation. Counts are additive — hourly counts sum to a daily count. Distinct users are not: you cannot add yesterday's uniques to today's, because the same person appears in both. Exact distinct counting over a year means keeping every identifier, which is the raw dataset all over again. HyperLogLog solves it: about 12 KB per sketch with under 1% standard error, and the sketches merge. An hourly sketch can be combined into a daily one, and daily into yearly, with no loss beyond the inherent error. Cost: under 1% error and no way to drill into who those users were. For a "unique visitors" dashboard number that is the right trade, and I would still offer exact counts for small, specific segments computed from the raw data on request.
The wrong-job scenario tests whether the design is sound: a bug produced wrong numbers for a week. Because raw events are retained and aggregation is idempotent (it writes replacements per time bucket), the fix is to correct the job and reprocess that week, and the rollups are overwritten with correct values. That is only possible because of two decisions made earlier: keep the raw data, and make aggregation replace rather than increment. If either were different, the numbers would be permanently wrong. I would also version the aggregation logic so it is possible to say which version produced a given rollup. Cost: reprocessing consumes significant compute, and dashboards change retroactively, which needs to be communicated rather than surprising someone.
Backpressure: if consumers fall behind, the log grows. Because the log has finite retention, a consumer down long enough loses data permanently. So the alert must be on consumer lag against retention, not just on lag. Cost: a monitoring requirement that is easy to overlook and expensive to discover.
Privacy, raised unprompted because two years of raw event data makes it unavoidable: deletion requests must be satisfiable, which is hard when data is spread across immutable columnar files. Practical answers are storing identifiers in a form that can be broken (a keyed hash, where destroying the key destroys the linkage) or periodically rewriting partitions. Cost: real engineering effort and a compaction process, and it is a requirement rather than a nice-to-have.
Consistency per feature: the real-time dashboard is approximate and a few seconds behind. The hourly rollup is eventually consistent and may be corrected by the batch path. The daily authoritative numbers are exact for counts and approximate for distincts. Financial conversion counts are exact and reconciled against the transactional system, because a marketing number that disagrees with the revenue number quickly becomes a credibility problem.
What I would monitor: ingestion success rate (the number that maps directly to lost data), consumer lag against retention, aggregation job duration and success, the count of late events arriving after their window closed, and a reconciliation check between rollups and a recomputation from raw for a sample of buckets.
What an interviewer is ticking off in this stage
- Handled partition skew with key suffixing and two-stage aggregation
- Automatic heavy-hitter detection rather than a manual list
- Explained why distinct counts are not additive
- Chose an approximate sketch and named the error and the mergeability
- Walked through fixing a week of bad numbers, and tied it to earlier decisions
- Backpressure alerting on lag against retention
- Raised privacy and deletion unprompted, with a real mechanism
- Per-feature consistency answer
- Named what to monitor, including a reconciliation check
- Cost named after every decision
The decision this turns on
- —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.
- —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.
And then they push back
A complete answer is where the interview starts, not where it ends. These are the questions this design invites — the interview is decided here far more often than in the diagram.
The queue is backed up by an hour. What do you do?
"Add more consumers." Sometimes right, sometimes wrong: if consumers are failing and retrying, adding more accelerates the failure. Not distinguishing the two cases is the tell.
"First I work out which of two things it is, because the fixes are opposite. If consumers are healthy and simply outnumbered, scale them out, and check what they are hitting, because often the ceiling is a database and adding workers just moves the queue there. If consumers are failing and retrying, more consumers makes it worse; I find the poison message or the broken dependency and stop the retry storm. Then I triage rather than drain in order: if urgent and non-urgent work share a queue, the non-urgent is now delaying the urgent, which is a lesson for the design, not just the incident. Then I decide what to drop — an hour-old 'your driver is arriving' notification is worse than useless and I would discard it rather than deliver it. And afterwards I alert on message age and backlog growth rate, not depth, because depth crossing a threshold tells you an hour too late."
One key gets 90% of the traffic. What breaks, and what do you do?
"Consistent hashing spreads the load evenly." It spreads keys evenly, not traffic. All requests for one key go to one place no matter how the keys are hashed. This answer confuses two different problems.
"Whichever node owns that key is saturated while the rest idle, and no partitioning scheme fixes it because partitioning distributes keys, not requests for a single key. Three options. Replicate the hot key across N nodes with a suffix and read a random one. It costs N times the memory and a fanned-out invalidation, and it is the right answer for a read-hot key. For a write-hot key, shard the counter and sum on read, accepting that the total is now a computed value. Or handle it on a separate path: an in-process cache on every server absorbs a read-hot key almost completely. I would also detect hot keys automatically with a count-min sketch rather than maintaining a manual list, because yesterday's hot key is not today's."
The write worked but the response was lost and the client retried. What happens?
"The client would get an error and could try again." They already tried again. That is the premise. If the retry does the work a second time, you have charged the card twice.
"With idempotency keys, nothing bad: the retry carries the same key, the server finds it already claimed, and returns the original stored response, so the client sees success and the card is charged once. Without them, two charges and a customer complaint. The implementation detail that makes it airtight is inserting the key with a unique constraint in the same transaction as the work, because two separate steps leave a crash window between them. Two things people get wrong: the client must reuse the key across retries rather than generating a fresh one each attempt, which defeats the scheme without raising any error; and if the retry arrives while the first request is still running, I return an in-progress status so the client backs off, rather than doing the work again or returning a wrong answer. Keys expire after a day or so, or the table grows forever."
Why a queue instead of just calling the service directly?
"Queues decouple services and are more scalable." True in the abstract and it does not explain why this particular call needs one. Queues also add duplicate delivery, ordering questions and debugging difficulty, and not mentioning those means you added one by reflex.
"Because the user does not need to wait for this work to finish, and because the downstream cannot absorb my spikes. Concretely: sending the receipt email takes 800 ms and can fail, and there is no reason to make the customer wait for it or to fail their order because a mail provider is down. The queue absorbs a 10x spike into a longer backlog instead of a broken service. What it costs: the customer gets 'received' rather than 'done', so the product has to be honest about that; every consumer must be idempotent because delivery is at-least-once; and a failure now happens in a worker minutes later, which is harder to debug. I would not queue the stock check or the payment, because the user needs a real yes-or-no answer before they leave the page. A queue there adds latency and buys nothing."
What does this cost per month?
"I am not sure, it depends on the provider." Interviewers rarely want exact prices. They want to know whether you understand which line item dominates, and that is answerable from your own estimates.
"I would not guess at prices, but I can tell you the shape, which is what matters. The dominant cost here is egress bandwidth: my estimate said 1,350 GB a second to viewers, and at that volume nothing else on the bill comes close. Second is storage, because I keep raw data for two years and it grows every day whether anyone reads it or not. Third is compute, which is elastic and therefore the easiest to control. That ordering tells me where to spend engineering effort: a percentage point of CDN hit rate is worth more than any amount of compute optimisation, so cache hit rate is the number I would put on a dashboard and defend. Storage cost I would attack with lifecycle policies moving cold data to cheaper tiers and with tiered granularity. Compute I would run on interruptible capacity because the work has hours of slack."
Now it must be privacy compliant — users can demand deletion of everything about them.
"We would delete their row from the users table." Their data is in caches, replicas, backups, logs, analytics, search indexes and immutable event archives. One row is the easy part.
"The hard part is that data has been copied everywhere by design. I would inventory it: primary store, replicas, caches, search indexes, the raw event archive, backups, logs, and any third party I forwarded it to. Deleting from the primary is trivial; the archive is the hard one, because immutable columnar files cannot have a row removed cheaply. Two practical approaches: crypto-shredding, where each user's identifiers are encrypted with a per-user key and deleting the key makes the data unreadable, which is what makes this tractable at scale; or periodic compaction that rewrites partitions with deleted users removed, which is expensive but complete. For backups, deletion usually cannot be immediate, so the policy is that backups expire within a stated window and deletion is applied on restore. What I would change on day one knowing this: keep identifying data in as few places as possible, reference it by id everywhere else, and never copy raw identifiers into derived stores. That one rule turns this from an archaeology project into a manageable one."
Which alert fires first when this system starts failing?
"We would alert on CPU and memory." Those are symptoms of the machine, not of the user experience, and they fire either far too late or constantly for no reason.
"I would alert on user-visible symptoms first and causes second. The first alert should be error rate or latency at p99 on the main user path, because that is what maps to someone being unhappy. Then a small number of leading indicators that fire before users notice: queue age (not depth), because age maps directly to how late the work is; replication lag; cache hit rate dropping, which precedes database saturation; and connection pool wait time, which is the earliest sign of a slow dependency. For this system specifically, the earliest signal is usually cache hit rate, because everything downstream is sized assuming the cache absorbs most reads. I would not page on CPU. It is a dashboard metric, not an alert, because a machine at 90% CPU serving requests fine is not an incident, and a machine at 20% CPU timing out is."
10x traffic tomorrow. What fails first?
"We would add more servers and scale horizontally." This says nothing. Every system has a first thing to break, and not naming it means you do not know your own design well enough to have found it.
Name a specific component and the reason. "The stateless services scale out fine, so they are not the constraint. The database primary is, because every write goes through one machine and I am at roughly 40% of its write capacity now. So at 4x I am at the ceiling, and I would see write latency climb and connection pool waits before anything errors. The second thing is the cache, because 10x reads on a fixed working set is fine but 10x on a growing key space means my hit rate drops and the misses land on the database that is already the constraint. In order: database writes, then cache hit rate, then the connection pool. And I would find out from p99 write latency, not from errors."
The topics this answer used
You have read a good answer. That is worth very little until you can produce one on a blank page — so go and write this problem without looking, and compare afterwards.