Every click on an ad is money: advertisers pay per click, and they want to watch the count move in near real time. That makes this two problems at once: a streaming problem (count a billion events a day, fresh within a minute) and a billing problem (count each click exactly once, even when events arrive twice, arrive late or arrive during a crash). Interviewers use it to test whether you know the difference between fast and correct, and how to get both.
The ad-click-aggregation interview end to end: a billion clicks a day (about 50,000 a second at peak, 200 GB of raw events) counted per ad per minute within a minute, and exactly once for billing. A click tracker logs to Kafka, Flink dedupes and counts by event time into ClickHouse, and an hourly Spark job recomputes final counts from the raw archive. Deep dives build the exactly-once chain (signed click_id, idempotent producer, seen-ID state, checkpoints, overwrite-not-increment) and handle late clicks with watermarks and viral ads with salted two-stage aggregation.
Phase 1: Requirements (5 minutes)#
"Are these counts used for billing, or just for dashboards?"
"Both. Dashboards can be a little rough in the moment. The number on the invoice must be right."
"Then I'll design a fast path for freshness and keep the raw events so the billed numbers can be recomputed and reconciled."
- Record every click:
click_id, ad_id, campaign_id, timestamp, country, device, user/ip hash - Click counts per ad per minute, queryable for any time range (rolled up to hours and days)
- Top N ads by clicks in the last M minutes, filterable by country and device
- Drop duplicate and invalid clicks (bots, repeat clicks) before billing
- Keep raw events for audits and recomputation
- Freshness: dashboard counts within ~1 minute of the click
- Correctness: billed totals count each valid click exactly once
- Late data: a click that reaches us late (phone offline) is still counted in the right minute
- Durability: no acknowledged click is ever lost
- Scale: 50,000 clicks/s at peak, with single ads going viral
Phase 2: Estimation (5 minutes)#
- Clicks: 1B/day ÷ 86,400 s ≈ 11,600/s on average, ~4x at peak ≈ 50,000/s
- Raw event: ~200 bytes → 1B × 200 B = 200 GB/day of raw clicks
- Raw retention 30 days hot: ~6 TB; older raw events to cheap archival storage
- Active ads: 2M; suppose 20% get a click in any given minute → 400,000 ad-minute rows/minute
- Aggregates: 400,000 × 1,440 min ≈ 576M rows/day × ~50 B ≈ ~29 GB/day at minute grain, much less once rolled up to hours
- Kafka: 50,000/s × 200 B ≈ 10 MB/s at peak: modest; partition count is set by processing parallelism, not bandwidth
"The raw stream is moderate. The aggregates are tiny compared with it. What's hard is the semantics: duplicates, lateness and hot ads."
Phase 3: API (3 minutes)#
GET /c/{signed_click_token} (the ad's link; logs the click, then redirects)
→ 302 Location: https://advertiser.example/landing
GET /v1/ads/ad_123/clicks?from=2026-10-25T10:00Z&to=2026-10-25T11:00Z&granularity=1m
→ 200 { "points": [ { "minute": "10:00", "clicks": 412 }, ... ], "final_until": "10:52" }
GET /v1/ads/top?window=5m&limit=100&country=IN
→ 200 [ { "ad_id": "ad_77", "clicks": 18200 }, ... ]The click token is generated and signed when the ad is served, and it carries the click_id. A forged or replayed token is rejected at the door. final_until tells the client which minutes have passed reconciliation and won't change any more.
Phase 4: High-level design (10 minutes)#
%% title: Ad click aggregation: a stream for freshness, raw events for the billed truth
flowchart TD
U@{ icon: "sd:browser", label: "User clicks an ad" }
CT["Click tracker<br/>verify token · log · 302 redirect"]:::core
K@{ icon: "tech:kafka", label: "Kafka · raw clicks<br/>partitioned by click_id" }
F@{ icon: "tech:flink", label: "Flink<br/>dedupe · 1-min event-time windows" }
CH@{ icon: "tech:clickhouse", label: "ClickHouse<br/>per-ad, per-minute counts" }
S3@{ icon: "tech:s3", label: "S3 · raw click archive" }
SP@{ icon: "tech:spark", label: "Spark · hourly reconciliation" }
Q["Query service<br/>dashboards · top N · billing"]:::service
U --> CT
CT --> K
K --> F
F -->|"upsert counts"| CH
K -->|"archive"| S3
S3 --> SP
SP -->|"corrected, final counts"| CH
Q --> CH
class U client
class K queue
class F,SP service
class CH,S3 store- Log it. The click tracker verifies the token, appends the event to Kafka (waiting for the write to be acknowledged), then redirects. If Kafka is unreachable it buffers locally rather than lose the click.
- Count it fast. Flink reads the topic, drops
click_ids it has already seen, and counts clicks per ad per minute by the time the click happened, not the time it arrived. - Publish. When a minute's window closes, Flink writes
(ad_id, minute) → countinto ClickHouse as an upsert. Dashboards and top-N queries read from there. - Keep the evidence. The same raw events are archived to S3.
- Reconcile. An hourly Spark job recomputes counts from the raw archive (including very late events and fraud decisions made since), overwrites the minute rows, and marks them final. Invoices only use final rows.
This is the classic lambda-style split: a streaming layer that's fast and almost always right, and a batch layer over the same raw data that is slow and authoritative. The batch job also gives you a way to fix a bug in the counting logic: fix it and recompute history.
Phase 5: Deep dives (15 minutes)#
Deep dive 1: Counting each click exactly once#
Where do duplicates come from?
List every way the same click can be counted twice between the user's tap and the ClickHouse row.
One strong answer
- The client: a double tap, or a browser that retries the redirect.
- The tracker: it writes to Kafka, times out waiting for the acknowledgement, and writes again.
- The processor: it crashes after updating counts but before recording how far it had read, then re-reads those events on restart.
- The sink: a retried write to ClickHouse that adds the count a second time if the write is an increment.
Each needs its own defence. "Exactly once" isn't one feature; it's a chain where every link is idempotent or transactional.
1. A stable ID from the start
click_id is minted when the ad is served and signed into the click URL. Every copy of the same click carries the same ID, whoever retried.
2. Idempotent producer
The tracker uses Kafka's idempotent producer, so its own retries don't create duplicate records in the log.
3. Dedupe in the stream
Flink keeps a keyed set of click_ids seen in the last hour (with a TTL). A repeat is dropped. One hour of IDs at 50,000/s is large, so the state lives in Flink's on-disk state backend, not the JVM heap.
4. State and offsets checkpointed together
Flink periodically snapshots its counts, its dedupe set and its Kafka read positions as one consistent checkpoint. After a crash it restores all three and re-reads from the saved position: nothing is lost, and nothing is counted twice.
5. Overwrite, don't increment
The sink writes the full count for (ad_id, minute), not "+5". Writing 412 twice still gives 412.
The batch reconciliation is the final safety net: it recomputes from raw events with click_id dedupe, so even a bug in the streaming chain can't reach an invoice.
Deep dive 2: Late clicks, windows and hot ads#
A phone in a tunnel taps an ad at 10:00:30 and the event reaches us at 10:04. Which minute does it count in, and has that minute already been published?
Count by event time
Windows are per minute of when the click happened. Counting by arrival time would shift clicks into the wrong minute whenever there's lag, and lag is worst exactly during traffic peaks.
A watermark says "probably complete"
Flink tracks a watermark: "I believe I've seen everything up to 10:01 minus 30 seconds of tolerance." When it passes the end of a window, the window's count is published.
Allowed lateness updates the row
For a few minutes after that, a late click updates the window and re-publishes the row (an upsert, so it's safe). Dashboards see the number tick up.
Very late goes to the side
Clicks later than that go to a side output; the hourly batch job counts them from the raw archive anyway.
Hot ads. Kafka is partitioned by click_id, so the log itself stays even. But counting is keyed by ad_id, so all of one ad's clicks meet in one Flink task. A Super-Bowl-sized ad can take a large share of all clicks and overload that task. The fix is two-stage aggregation: key first by (ad_id, salt) with a random salt from 0 to 15, so 16 tasks share the ad and each counts its slice per minute, then a second, much smaller stage merges the 16 partial counts. (Dedupe stays exact because every copy of a click has the same click_id, so the salt is derived from it.)
Top N in the last 5 minutes. Each task keeps per-minute counts, so "top 100 over 5 minutes" is a query over 5 minute-rows per ad in ClickHouse. If that gets too heavy, the stream can maintain a top-K per minute and merge the five.
Phase 6: Trade-offs and failure modes (5 minutes)#
Flink job crashes
Restart from the last checkpoint (seconds to minutes old) and re-read Kafka from the saved offsets. Dashboards pause briefly; counts stay exact.
Kafka unavailable at the tracker
The redirect must still happen: users shouldn't see an error because of our counting. The tracker spools events to local disk and replays them; their event time keeps them in the right minute.
A bug in the counting logic
Fix it, then recompute affected hours from the raw archive. That ability is the reason raw events are kept.
Fraud decided later
A bot farm is identified a day later. The batch job excludes those clicks and corrects the final counts before invoicing.
ClickHouse slow or down
Flink's output buffers and backs off; Kafka holds the input. The fast path catches up afterwards.
| Decision | We chose | We gave up |
|---|---|---|
| Architecture | Stream for freshness + batch for truth | One pipeline to build and run |
| Duplicates | click_id dedupe + checkpoints + overwrite sink | Simple stateless counting |
| Time | Event-time windows with watermarks | Counts that never change after publishing |
| Hot ads | Salted two-stage aggregation | One simple key per ad |
Go deeper in the course#
- Week 8, Day 1: Event ingestion at scale: collecting events durably, buffering and schemas
- Week 8, Day 2: Streaming vs batch: lambda vs kappa, and Flink's checkpointed state
- Week 8, Day 4: Late-arriving data and correctness: event time, watermarks, allowed lateness and reprocessing
- Week 8, Day 3: Data modeling for analytics: fact tables and rollups for the per-minute counts
- Week 8, Day 5: Query layer and optimization: OLAP stores and pre-aggregation behind fast dashboards
- Week 8 capstone: real-time analytics for video streaming: the same pipeline shape, end to end
- Week 2, Day 2: Idempotency in practice: stable IDs and idempotent writes, the links in the exactly-once chain
- Week 3, Day 1: Queue vs stream: why a replayable, partitioned log sits in the middle
- Week 1, Day 4: Hot keys and skew: salting a hot key and merging the parts
Check yourself#
Ad click aggregation
Q1/4 A click happens at 10:00:30 and reaches the pipeline at 10:04. Which minute should it count in?
Answer & explanation
Answer: A
Count by event time. Arrival-time counting moves clicks into the wrong minute whenever there's lag. Watermarks and allowed lateness decide when a minute is published and updated.
Q2/4 Flink crashes after updating counts but before recording its Kafka offsets. What prevents double counting on restart?
Answer & explanation
Answer: B
The checkpoint snapshots state and read positions together, so the restored job re-reads exactly the events whose effects were rolled back.
Q3/4 Why does the sink write "(ad_123, 10:00) = 412" rather than "add 5 to (ad_123, 10:00)"?
Answer & explanation
Answer: C
Idempotent writes make retries safe. The same reasoning drives idempotency keys in payment APIs.
Q4/4 One ad suddenly gets a big share of all clicks and its Flink task falls behind. What's the standard fix?
Answer & explanation
Answer: D
Two-stage aggregation spreads one hot key over many tasks and adds a small merge step. Sampling would break billing accuracy.
New lessons by email
New lessons, side quests and case files as they ship, at most once a week. No spam, unsubscribe anytime.
Discussion · …
Public: everyone reading this lesson sees comments. Be kind and keep it on the material.