Design an ad click aggregator
Every click on an ad is money: advertisers pay per click and want to watch the numbers move in near real time. Counting sounds trivial until clicks arrive late, twice, or ten thousand a second for one viral ad. This design is the classic introduction to stream processing: event time versus arrival time, windows, watermarks and exactly-once counts. It follows the interview framework phase by phase.
1. The prompt
"Design an ad click aggregator: users click ads on websites and apps; we send them to the advertiser's page and record the click; advertisers query click counts per ad over time (per minute, per hour), with filters such as country or device."
Two very different paths meet here: a redirect that must be instant for the user, and a counting pipeline that must be right to the click because it decides invoices.
2. Frame the problem
How many clicks?
About 10,000 a second on average, 50,000 at peak.
Far too many for a database counter per click; a log and a stream processor.
How fresh must dashboards be?
Within about a minute.
Minute windows, not per-click updates to the dashboard.
Are counts used for billing?
Yes, so the final numbers must be exact.
A fast approximate path plus an exact reconciliation path.
Smallest time unit for queries?
One minute; longer ranges by hour and day.
Pre-aggregate by minute and roll up.
Which filters?
Country, device, placement.
Each dimension multiplies rows: an OLAP store and careful choices.
What counts as a valid click?
One per impression; bots and repeats filtered.
Deduplicate by a signed impression id.
3. What it must do
- A click redirects the user to the advertiser's landing page.
- Clicks are counted per ad per minute, with filters.
- Advertisers query any time range at minute granularity.
Not now:
- Ad serving and auctions.
- Impressions and conversion tracking (the same pipeline, more events).
4. How well it must do it
| Target | Why this number |
|---|---|
| Fast redirect: under ~50 ms added speed | The user must not wait for counting; a slow redirect loses the click. |
| Fresh: dashboards within ~1 minute freshness | Advertisers watch campaigns live and adjust budgets. |
| Accurate in the end: no lost or double-counted clicks consistency | Billing; disputes are costly. |
| Scale: 50,000 clicks a second; one ad can go viral load shape | Hot keys are the norm, not the exception. |
5. Numbers that change the design
| Quantity | Working | → so we… |
|---|---|---|
| Events | 10,000 a second × 86,400 | ~860M clicks a day; peak 50,000 a second → so a partitioned log (Kafka) absorbs bursts |
| Raw storage | 860M × ~200 bytes | ~170 GB a day → so keep it cheaply in object storage for audits and recomputation |
| Aggregates | Say 2M active ads × 1,440 minutes | ~2.9B rows a day at most, far fewer in practice → so an OLAP store built for time-series rollups |
| Query load | Dashboards refreshing every few seconds | → so pre-aggregated rows make each query a small range scan |
| Hot ad | A viral ad at 5,000 clicks a second | Too much for one task → so split hot keys |
6. The nouns
- Click event: immutable fact, with the time it happened.
- Aggregate: a count per ad, per minute, per dimension.
- Impression: the ad being shown, which makes the click identifiable.
click event click_id, impression_id, ad_id, event_time, received_at, country, device, ip_hash aggregate (ad_id, minute, country, device) → clicks (OLAP, rolled up to hour and day) raw log object storage, partitioned by hour (kept for audits and recounts)
7. The contract
GET /click?ad=42&imp=…&sig=… → 302 Location: https://advertiser.example/landing
GET /v1/ads/42/clicks?from=2026-10-16T12:00Z&to=…&granularity=minute&country=IN → [{minute, count}, …]The impression id comes from the ad server when the ad was shown, signed so it can't be forged. It makes every click identifiable, which is the basis for deduplication.
Data flow: (1) the click service validates and redirects → (2) the event goes to Kafka → (3) a stream processor counts per ad per minute by event time → (4) counts are upserted into the OLAP store → (5) raw events land in object storage → (6) a nightly recount produces the billing numbers.
8. First working version
A click service that redirects, and a database with a counter per ad per minute.
8.1 A click
Version one increments a counter row for every click, then redirects.
8.2 A dashboard query
Sum the counter rows for the ad over the time range.
Parked for later (section 9 fixes each)
- The user waits on the database write before being redirected.
- A click that arrives late is counted in the wrong minute.
- Retries and repeat clicks are counted twice.
- One viral ad's counter row becomes a lock bottleneck.
- No raw record to check invoices against.
9. Make it hold up
9.1 The redirect path fast redirect
The click service validates the signature, writes the event to Kafka (asynchronously, with a local buffer) and immediately returns a 302. If Kafka is briefly slow the user is still redirected; the buffer catches up. Bot filtering runs downstream on the stream, never on the user's path.
9.2 Event time, windows and watermarks accurate · fresh
A click made at 12:00:58 on a train might reach us at 12:02:30. If we count by arrival time, it lands in the wrong minute. Stream processors such as Apache Flink count by event time (when it happened) in tumbling windows (12:00–12:01, 12:01–12:02, …).
- A watermark is the processor's estimate of "we've now seen everything up to time T". A common rule: the latest event time seen, minus a bound on out-of-order delay (say 10 s).
- When the watermark passes the end of a window, the window fires and its count is emitted.
- Events for an already-fired window are late. With allowed lateness the window keeps its state a while longer and emits a corrected count; after that, late events are dropped or sent to a side output for the batch job.
The lab in section 13 replays a real-looking stream so you can watch the watermark, the late clicks and the duplicates.
9.3 Deduplication and exactly-once counts accurate
| Approach | Why | |
|---|---|---|
| ✗ Bad | Increment a counter in a database for every click | Retries double-count, a crash between "read event" and "increment" loses or repeats counts, and one hot ad's row becomes a lock bottleneck. |
| ~ Good | Stream processor with windowed counts, written to the store as idempotent upserts keyed by (ad, minute) | Re-writing the same window gives the same row. But retries inside the pipeline and duplicate clicks still inflate counts. |
| ✓ Great | Deduplicate by impression or click id (keyed state with a TTL), checkpointed state with exactly-once processing, idempotent sinks, plus a nightly batch recount from raw events | The stream gives fast, nearly exact numbers; the batch job over the immutable raw log gives the final billing numbers and fixes anything the stream missed. |
9.4 Hot ads scale
All clicks for one ad go to one partition and one task if keyed by ad id. A viral ad then overloads one machine. Split the key: ad_id + random(0..N) spreads the ad across N tasks, each counts its share, and a second small step adds the N partial counts. Only hot ads need splitting, and they can be detected automatically from per-key rates.
9.5 Storage and queries fresh
Minute-level aggregates go to a columnar OLAP store built for time ranges and group-bys (Apache Pinot, Apache Druid and ClickHouse are common choices). Roll minutes up into hours and days for long ranges. Raw events go to object storage, partitioned by hour, for the batch job and audits. Each extra dimension multiplies rows; pre-aggregate the common ones and query raw data for rare ones.
9.6 The nightly recount accurate
A batch job reads the day's raw events, deduplicates by click id, applies the same bot rules and the final lateness policy, and writes the billing numbers. Where it disagrees with the stream's numbers, the batch result wins and the difference is logged. Running two paths (stream plus batch) is the "lambda" shape; replaying the log through the same stream job ("kappa") avoids two code paths but needs long log retention.
9.7 The whole design after the deep dives
One click through the pipeline. Press Play or drag the timeline.
10. Interviewer follow-ups
How do you choose the out-of-order bound?
From measured delay: pick a bound that covers, say, 99.9% of clicks, and let allowed lateness and the nightly recount handle the rest. Larger bounds delay every dashboard.
Unique users who clicked.
HyperLogLog sketches per window: about 1% error in a few KB, and they merge across windows and dimensions.
Click fraud.
Rules (rate per IP or device, impossible timing) on the stream, a model in batch, and clicks marked invalid rather than deleted so the billing job can exclude them.
The stream processor crashes.
It restarts from the last checkpoint (state plus Kafka offsets) and reprocesses from there; idempotent upserts make the replay harmless.
Multi-region.
Count per region near the users, then merge regional aggregates centrally; raw logs are copied for the global recount.
An advertiser disputes a bill.
Recompute from the raw log for that ad and period, with the click ids, timestamps and invalid-click reasons as evidence.
11. When things break
| Failure | What users see | How we detect it | What limits the damage |
|---|---|---|---|
| Kafka slow | Nothing (redirects continue) | Producer buffer size | Local buffer, then catch-up |
| Stream job down | Dashboards stop moving | Consumer lag | Restart from checkpoint; nothing lost |
| OLAP store slow | Slow dashboards | Query latency | Rollups; cache recent results |
| Hot ad | Lag for that ad | Per-key rate | Key splitting |
| Stream and batch disagree | Small dashboard corrections | Nightly diff | Batch is the billing truth |
12. Wrap-up and the clock
Trade-offs we made:
- Freshness vs completeness: a larger out-of-order bound or allowed lateness gives more accurate first answers but delays them and keeps more state in memory.
- Stream plus batch: two paths, but fast dashboards and exact invoices.
- Exact vs approximate: sketches for unique users; billing counts stay exact.
- Dimensions: pre-aggregate the common ones only.
What we'd watch:
- Consumer lag.
- Late and dropped event rates.
- Stream vs batch difference.
- Redirect latency.
- Per-key hot spots.
At 10×:
- More partitions and tasks.
- Regional pre-aggregation.
- Stricter dimension limits.
A 45-minute interview
| 0–4 | Frame the problem (volume, freshness, billing) |
| 4–9 | Must do, how well, numbers |
| 9–12 | Nouns, contract, data flow |
| 12–18 | First working version, parked list |
| 18–40 | Deep dives: redirect (9.1), event time (9.2), exactly-once (9.3), hot ads (9.4) |
| 40–45 | Follow-ups and wrap-up |
A 60-minute interview
| 0–5 | Frame the problem |
| 5–11 | Must do, how well, numbers |
| 11–15 | Nouns, contract, data flow |
| 15–23 | First working version |
| 23–50 | Deep dives 9.1–9.6 and the final picture |
| 50–60 | Follow-ups: out-of-order bound, fraud, disputes; wrap-up |
| Level | What a strong answer shows |
|---|---|
| Mid-level | Click service with redirect, a queue, a consumer that aggregates per minute, a database for queries. |
| Senior | Kafka + stream processor, event time vs processing time, windows and watermarks, idempotent upserts, deduplication by impression id, hot-key splitting, an OLAP store, raw data in object storage. |
| Staff+ | Exactly-once guarantees end to end and their cost, lateness policy tied to billing rules, batch reconciliation, fraud filtering, multi-region, dimension explosion and rollups. |
Common mistakes:
- Counting on the redirect path.
- Counting by arrival time.
- No deduplication.
- One task per viral ad.
- No raw log to recount from.
13. Try it, check yourself, read more
67 clicks for one ad over five minutes, replayed in the order they arrive. Each dot falls into the minute it was clicked. The teal line is the watermark (latest event time minus 10 s); a minute's column turns teal when its window fires.
For billing, which time should a click be counted under?
What is a watermark?
One viral ad overloads one stream task. What do you do?
Stream and batch counts differ for an ad. Which one goes on the invoice?
| Resource | Why |
|---|---|
| Timely stream processing (Apache Flink docs) | Event time and watermarks, from the source. |
| Windows (Apache Flink docs) | Tumbling windows, triggers and allowed lateness. |
| Design an ad click aggregator (Hello Interview) | Another walkthrough. |