Back to System design
Design Ad Click Aggregation / Real-time Analytics
hardEvery time someone clicks an advert, you record it. At the end of the day you tell the advertiser how many clicks they got, and you charge them for it.
Scale1M+ events/s; sub-second freshness on dashboards
StorageRaw events to lake; aggregates compact in OLAP
Key Concepts
1
Counting sounds simple. It becomes hard because these counts are money: count one click twice and you have
overcharged someone.
2
How much is arriving. Ten billion clicks a day is about 116,000 every second, and several times
that at busy hours.
How much is arriving.116,000 every second
3
Nothing in that path can write a row to a normal database per click. Clicks go onto a log — an append-only
stream that many programs can read — and separate programs add them up as they flow past.
4
You also keep every raw click. When an advertiser disputes an invoice, the raw records are what settle it.
5
Counting in time buckets, and the problem with that. You want clicks per hour. So you open a bucket
for 10:00 to 11:00, add clicks to it, and close it at 11:00.
Counting in time buckets, and the problem with that.
6
Then a click arrives at 11:00:03 that happened at 10:59:59. Someone's phone lost signal for four seconds.
The bucket is closed. Where does that click go?
7
If you put it in the 11:00 bucket, both hours are now wrong. If you drop it, you have lost a click someone
should pay for.
8
The fix is to wait a little, deliberately. The system tracks how far behind the latest events are
running — that estimate is called a watermark — and holds the bucket open for a few extra minutes after the
hour ends. Clicks that arrive within that grace period go into the right bucket.
The fix is to wait a little, deliberately.
9
Anything later goes to a separate stream rather than being thrown away, and gets fixed in the daily
recount.
10
Choosing the grace period is a real trade. Short means the dashboard updates sooner and more numbers get
corrected later. Long means fewer corrections and a dashboard that lags.
11
Never count the same click twice. Streams re-deliver messages when something fails. That is normal and
you must survive it.
Never count the same click twice.
12
Give every click a unique ID at the moment it happens. Aggregation is then done in a way that re-processing
the same ID changes nothing. People call this "exactly once", and the honest description is "at least once
delivery, plus counting that does not care about repeats".
13
Unique viewers need a different tool from totals. "How many clicks" you can add up across machines.
"How many different people" you cannot — adding two counts double-counts anyone who appears in both.
Unique viewers need a different tool from totals.
14
Keeping every user ID per advert is far too much data. Instead you use a sketch: a small summary, about
12 KB, that estimates the number of distinct values within roughly 1%. Two sketches can be merged, which
is exactly what you need across machines and hours.
15
Say the error rate out loud, and say that invoices use the exact count while dashboards use the estimate.
16
Remove fraud before counting, not after. Bots and click farms are removed before the totals are built,
starting with simple rules — the same user clicking the same advert twice in a second — and later with
models. Keep a record of what was filtered, because advertisers will ask why a click they can see was not
billed.
Remove fraud before counting, not after.
17
Recount everything once a day. A batch job recomputes the same totals from the raw clicks and compares
them with what the streaming system produced. Differences are investigated. This is the safety net that
lets you defend an invoice.
Recount everything once a day.
18
What the interviewer is probing.
What the interviewer is probing.
19
1. "Ten billion clicks a day. Where do they go?" Probing: whether you turn it into a rate. Stalls:
"Into a database." Moves up: 116,000 a second, so a log plus stream processing, with raw clicks kept for
billing.
1. "Ten billion clicks a day. Where do they go?"
20
2. "A click arrives after its hour has closed." Probing: event time versus arrival time. Stalls:
"Put it in the current hour." Moves up: hold the bucket open for a grace period, and send later arrivals
to a separate stream for the daily recount.
2. "A click arrives after its hour has closed."
21
3. "How do you avoid double counting?" Probing: idempotency where money is involved. Stalls:
"Exactly-once processing." Moves up: unique click IDs and counting that ignores repeats.
3. "How do you avoid double counting?"
22
4. "How many unique users saw this campaign?" Probing: why totals and uniques differ. Stalls:
"Count distinct." Moves up: a mergeable sketch at about 12 KB and 1% error, with invoices using the
exact path.
4. "How many unique users saw this campaign?"
23
5. "The dashboard and the invoice disagree." Probing: whether that is expected. Stalls: "One is
wrong." Moves up: the fast path is approximate and the daily recount is authoritative; the gap should be
small, monitored, and explainable.
5. "The dashboard and the invoice disagree."
High-level design
Edge collector → Kafka (partitioned by ad_id, long retention).
Stream processor (Flink) → windowed aggregation → dedup → emit.
Sinks: OLAP (Druid/ClickHouse) for dashboards; data lake (S3) for replay; billing pipeline for invoicing.
Side: fraud detector reads raw stream; flags / removes bad clicks.
Dashboards / API → OLAP query.
Components
- Edge collector (stateless, batches to Kafka).
- Kafka (partitioned by ad_id; week+ retention for replay).
- Stream processor (Flink): keyed state per ad_id, tumbling windows.
- Dedup state (RocksDB-backed in Flink).
- OLAP store (Druid, ClickHouse, Pinot) for sub-second queries.
- Data lake (S3 + Parquet).
- Billing pipeline (separate; exactly-once or reconciled).
- Fraud detection service.
- Dashboards (Superset, Grafana, internal).
Volume and storage, worked
Events per day 10,000,000,000 Average rate 10B / 86,400 = 116,000 /s Peak (x4, evening + campaign launches) = 464,000 /s
Event record
event id (UUID) 16 B
user id 8 B
ad id / campaign id 16 B
timestamp 8 B
context (device, geo, placement) ~80 B
-----------------------------------------------------
per event ~128 B Raw retention
Per day 10B x 128 B = 1.28 TB
Per day compressed (x4) = 320 GB
13 months (billing disputes) = ~127 TB Aggregates (the part you serve)
Campaigns 500,000
Dimensions (geo x device x hour) = 24 x 50 x 5
Rows per day 500k x 6,000 = 3B
-- which is why aggregates are pre-rolled by hour and day,
and why arbitrary slicing is a query-time cost you must
bound. Throughput conclusions
- 464k events/s is a partitioned log; partition by campaign id
so aggregation per campaign is local.
- 1.28 TB/day raw means tiered storage: hot for days, cold
object storage for months.Windows, watermarks, lateness
Event time vs processing time
Event time when the click happened (on the device)
Processing time when your system saw it
Aggregates must use EVENT time, or a network delay silently
moves revenue between hours. Tumbling window, 1 hour, with allowed lateness
Window 10:00 - 11:00
Watermark system's estimate of event-time progress,
derived from observed timestamps
Close condition watermark > 11:00 + allowed_lateness allowed_lateness = 5 min
click at 10:59:59 arriving 11:00:03 -> included
click at 10:59:59 arriving 11:07:00 -> side output Side output handling
Late events are NOT dropped. They go to a late-events stream,
are stored, and the daily batch recomputation includes them.
The dashboard number for that hour is corrected the next day,
and the invoice uses the corrected figure. Choosing allowed lateness
Too short: more corrections, advertisers see numbers move.
Too long: the window stays open, dashboards lag by that much.
Minutes is the usual answer; state the trade rather than a
number alone.Exact vs approximate
Totals (clicks, impressions, spend)
Must be exact -- they are billed.
Path: idempotent aggregation keyed by event id, transactional
writes, daily batch reconciliation. Unique users / reach
Exact distinct count requires the set:
100M users x 8 B per id x 500k campaigns -- impossible
HyperLogLog
Memory per counter ~12 KB
Standard error ~0.8%
Union of two sketches exact and cheap
-> reach per campaign, per day, per geo, all mergeable. Top-K (best performing ads)
Count-Min Sketch or a heavy-hitters algorithm; approximate
ranks are fine for a dashboard. Percentiles (time to click, dwell)
t-digest or HDR histogram; do not store every sample. The rule to state
Approximate structures for exploration and dashboards, exact
aggregation for anything that appears on an invoice. Mixing
them up is how a reporting feature becomes a billing incident.