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.
Step 1: Understand the problem
The word “count” hides four different requirements. Separate them before you draw, because each one pulls the design somewhere else.
| You ask | They say | What it settles |
|---|---|---|
| How many events a second, and how spiky? | A million a second, with regional peaks. | An append only log in front of everything. Nothing that processes events is allowed to sit in the path of producing them. |
| Is the count for a dashboard, or an invoice? | Both. Advertisers watch it live and are billed from it monthly. | The hardest requirement on the page. Live means seconds and approximate, billing means exact and reconcilable, and that tension is the whole design. |
| How fresh does the dashboard have to be? | About a minute behind is fine. | Minute windows in a stream processor rather than an update per event, which is what makes a million events a second affordable. |
| What happens to an event that arrives late? | It still has to be counted correctly. | Event time processing with watermarks, and the ability to restate a window you already published. This single answer rules out every design that only ever appends. |
| Can the same click be counted twice? | Never. That is fraud, and it gets refunded. | A click id minted at the source and exactly once semantics somewhere in the pipeline. The cheap approximations are off the table. |
| How long do we keep raw events? | Years. Advertisers dispute invoices. | The raw log is archived and is the real source of truth. Every aggregate on this page can be deleted and rebuilt from it, which is what makes restatement safe. |
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.
Back of the envelope
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.
Step 2: Propose the high level design
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" }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. |
The whole system on one whiteboard
Walking Figure 1:
- The click is a redirect through your ad server, which is what makes the event yours to count rather than something you have to be told about.
- The ad server appends to the log and returns immediately. It never waits for anything downstream, and it never aggregates.
- Events are deduplicated by the click id minted at step 1, before anything counts them.
- The stream processor groups them into one minute windows by event time.
- Those windows land in the serving store within about a minute of the click.
- In parallel, everything on the log is archived raw. This archive, not the serving store, is the source of truth.
- An hourly batch job reads the archive and computes the same windows exactly, with all the late data that has arrived since.
- It overwrites the rows the stream already wrote. Same table, same rows, better number.
- Reconciliation compares your totals against what the advertiser’s own system saw, because the disagreement is the thing that actually costs money.
- The dashboard and the invoice read the same table. A row starts out approximate and becomes final, and a flag says which.
Edge 8 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.
A click for 14:59 arrives at 15:04, after the 14:00 to 15:00 window was already published. What do you do with it, and what does your answer cost?
Step 3: Design deep dive
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.
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.
Two pipelines, one number
Partitioning the stream
Break it
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?
- A million events a second, and every count is money.
- One minute windows cut the row count by orders of magnitude. That ratio is the whole business case for aggregating.
- Event time, not arrival time, with a watermark that says when a window is safe to publish.
- The raw archive is the truth. Every aggregate on the page can be deleted and rebuilt from it.
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.
