Design an ad click aggregator
Counting is the easiest thing a computer does, right up to the moment the count is money. Then a duplicate is fraud, a dropped event is a refund, and an advertiser who cannot reconcile your number against theirs stops spending.
This question is asked at senior level because it has no comfortable answer. Streaming gives you fast numbers that are occasionally wrong. Batch gives you correct numbers hours late. The design is how you offer both without maintaining two truths.
A click event for 14:59 arrives at 15:04, after you already published the 14:00 to 15:00 total. What do you do with it, and what does your answer cost?
What you are building
- Ingest click and impression events. From ad servers worldwide, at rates that make per event database writes impossible.
- Aggregate by advertiser, campaign, ad and minute. Minute granularity, rolled up on read.
- A near real time dashboard. Advertisers watch spend live and pause campaigns based on it.
- A billing grade number. Correct, reproducible, and reconcilable, even if it arrives hours later.
- 1 million events per second at peak.
- Dashboard within about a minute of the event.
- Billing figures must be exact and must not change after they are finalised.
- A replay must produce byte identical results, because disputes get audited.
- Ad serving and auction. Microsecond budgets and a completely different design.
- Click fraud detection. It sits downstream of this and is a machine learning problem.
- Attribution windows and conversion tracking. Related, larger, and its own question.
The numbers
Writing 1,000,000 rows a second to a database is not a thing you can buy. Aggregating first turns it into 200,000 rows a minute, which any columnar store handles comfortably. Everything difficult in this design comes from the fact that aggregation happens before storage, so correctness has to be established in the stream rather than fixed later with a query.
The design
Edge 6 is the design. The batch job does not write to a different table for a different
audience. It overwrites the same rows the stream produced, so a number on the dashboard
gets more accurate over the following hours and then stops changing. There is one table
and one truth, with a finalised flag saying which rows are safe to bill against.
Deep dive one: event time is not arrival time
The click happened at 14:59 on a phone with a poor connection. It reached you at 15:04. Which minute does it belong to?
Event time, always. Bucketing by arrival time means a network delay silently moves revenue between advertisers, and it means replaying the log produces different answers than the original run, which destroys any hope of reconciliation.
Choosing event time creates the problem this whole section is about: you never know when a window is complete, because there might always be one more straggler.
Drag the slider from zero to the end. At zero you publish the moment the window closes and lose three events. At thirty you lose none and every number is thirty seconds older. There is no setting that gives both, and finding one is not an engineering problem you have failed to solve.
So do not choose. Emit early with what you have, keep the window open, and emit again if stragglers arrive. The dashboard shows the early number and corrects itself. Billing waits for the batch job, which reads the archive long after every straggler has landed.
Arrival time is tempting because it needs no state and never has stragglers. It also means the same log replayed tomorrow produces different totals than it did today, since arrival times are gone. Any system that has to defend a number to an advertiser needs replay to be deterministic, and that requires event time.
Deep dive two: counting each click exactly once
Every layer here delivers at least once. The ad server retries on a timeout, the log redelivers on a consumer restart, and the stream processor reprocesses a window after a crash. Without care, a single click becomes three.
Three ideas are doing all the work there, and they are worth naming separately because each one solves a different failure:
Event ids created at the edge. Anything generated later cannot tell a retry from a second real click.
Deduplication scoped to the window. Remembering every id ever seen is not affordable. Remembering ids within a window is, and a duplicate arriving after its window has closed is rare enough to be handled by the batch job.
Writes that set rather than increment. An increment is not idempotent, so reprocessing doubles it. Writing the computed total for a window keyed by that window makes reprocessing free, which in turn makes crash recovery boring.
Deep dive three: two pipelines, one number
The data model
| minute | timestamp | PK | Event time, truncated. Never arrival time. |
| ad_id | bigint | PK | |
| advertiser_id | bigint | IDX | Clustering key, since almost every query filters by advertiser first. |
| clicks | bigint | Set, never incremented. That is what makes reprocessing safe. | |
| impressions | bigint | ||
| spend_micros | bigint | Integer micros. Money is never a float. | |
| finalised | boolean | IDX | False from the stream, true after the recompute. Billing reads only where this is true. |
| computed_at | timestamp | Which run produced this row. A late row never overwrites a newer one. |
Partitioning the stream
Break it
The API
{
"eventId": "c-8f21ab...",
"type": "click",
"adId": 88231,
"eventTime": "2026-08-25T14:59:58.221Z"
}{ "from": "2026-08-25T14:00Z", "to": "2026-08-25T16:00Z" }Trade-offs
| Choice | What you gain | What you pay | Pick it when |
|---|---|---|---|
| Event time over arrival time | Replay is deterministic, and a network delay cannot move revenue between minutes or advertisers. | Windows are never provably complete, so you need watermarks and a late data policy. | Anything that gets audited. For internal traffic dashboards, arrival time is fine and much simpler. |
| Set the aggregate rather than increment it | Reprocessing a window is free, which makes crash recovery and replay boring. | The processor has to hold the whole window in state rather than emitting deltas. | Always, for anything that gets recomputed. Increments and at least once delivery cannot coexist. |
| One table with a finalised flag | One number, converging. No second system for anyone to reconcile against. | Consumers must respect the flag, and one that ignores it will bill against provisional data. | When the same figure serves both a live dashboard and an invoice. |
| Salting hot keys | One enormous campaign stops holding up its partition. | A second aggregation stage, and locality lost for that key. | The moment one key exceeds what a single partition can carry, which for ads is every large campaign launch. |
Interview replay
Checkpoint
1. Why bucket by event time rather than arrival time, given arrival time never has stragglers?
2. Your processor crashes and reprocesses a window. Which design choice prevents double counting?
3. A backlog forms and you hold watermarks back so no late events are dropped. What is the danger?
Events are bucketed by event time, not arrival time, because replay has to be deterministic for a number anyone might audit, and because a network delay should not move revenue between hours. That means windows are never provably complete, so I run two speeds against one table: the stream emits provisional minute aggregates on a short watermark and updates them if stragglers arrive, and a recompute over the archived raw events overwrites the same rows hours later and marks them finalised, which is what billing reads. Double counting is prevented by writing the computed total for a window rather than incrementing, so reprocessing is idempotent, plus deduplication on an event id generated at the ad server to absorb publish retries. Partitioning is by ad id for aggregation locality, with salting for campaigns large enough to swamp one partition. And under backlog I would close windows on schedule with an explicit completeness flag rather than holding watermarks back, because holding them turns a throughput problem into unbounded processor state.
