# Counting a billion ad clicks a day without double-counting

Source: https://www.techinterview.org/post/3233476913/design-ad-click-aggregator/
Updated: 2026-07-30 · techinterview.org

A viral ad gets clicked 40,000 times a second for about ninety seconds, and the billing system has to charge the advertiser for that many clicks. Not 39,500, not 42,000. Counting correctly under that kind of burst, while an advertiser dashboard still refreshes every few seconds, is the whole problem behind an ad click aggregator, and it's why the question keeps showing up in senior system design loops at Google, Meta, Amazon, and most ads-adjacent startups.

The setup is easy to state. Users see ads, users click ads, and you need two things out of those clicks: a running count per ad that advertisers watch in near real time, and an accurate total that finance bills against at the end of the day. Those two readers pull the design in opposite directions, and a good answer names that tension in the first two minutes.

## What the interviewer is actually testing

Pin the functional scope first, because "design an ad click aggregator" hides three different systems. You are not building the ad-serving path or the targeting model. You are building the counting pipeline that sits behind them. The questions worth asking out loud before you draw anything:

- How fresh do the advertiser-facing numbers need to be, seconds or is a minute fine?

- Do the billing numbers have to be exact, or is a 99.9% estimate acceptable?

- What query shapes do we serve: clicks per ad per minute, top ads, filters by region or campaign?

- What is peak clicks per second, and how skewed is traffic across ads?

The answers drive every later decision. In the common version of this problem, dashboards tolerate a lag of a minute and a small margin of error, while billing has to be exact but can run a few hours behind. That gap between fast-and-approximate and slow-and-exact is the seam the entire architecture is built around, so surface it early instead of discovering it halfway through.

## Nailing down the numbers first

Say average traffic is 10,000 clicks per second, peaking near 50,000 during big campaigns. That is roughly a billion clicks a day. Each click event carries an ad ID, a user ID, a timestamp, and some context like region and device, call it 100 to 200 bytes. Raw ingest lands around 100 to 200 GB a day, so a few tens of terabytes a year if you retain the raw logs, which you will want to.

The read side is smaller in volume but latency-bound: advertisers and internal tools firing aggregate queries that need to come back in well under a second. Heavy skewed writes on one side, light latency-sensitive reads on the other. That asymmetry is the reason the write path and the query path end up on different storage instead of a single database trying to do both jobs badly.

## The backbone: a log, a stream processor, an OLAP store

Almost every strong answer converges on the same shape. A click hits a thin gateway that validates it, stamps it with a unique event ID and an event-time timestamp, and appends it to a durable log like Kafka partitioned by ad ID. A stream processor, usually Flink with Spark Structured Streaming as the alternative, reads the log, groups events into one-minute tumbling windows keyed by ad ID, and writes the per-minute counts into a column-oriented OLAP store such as Druid, Pinot, or ClickHouse. Dashboards query that store, never the raw events.


```
clicks
  .keyBy(event -> event.adId)
  .window(TumblingEventTimeWindows.of(Time.minutes(1)))
  .allowedLateness(Time.minutes(5))
  .aggregate(new CountClicks())
  .addSink(olapSink);   // idempotent upsert keyed by (adId, minute)
```


Kafka earns its slot by absorbing bursts and decoupling ingest speed from compute speed. If Flink falls behind during a spike, events pile up in the log instead of getting dropped on the floor. Keying the windows by ad ID lets the processor shard the aggregation across many parallel tasks. The OLAP store exists because scanning a billion raw rows per query is hopeless, while scanning pre-aggregated minute buckets is quick.

| Pipeline stage | Typical technology | What it does | Freshness or guarantee |
| --- | --- | --- | --- |
| Click gateway (edge) | Thin HTTP service plus Kafka producer | Validate, drop obvious bot traffic, stamp each click with a unique event ID and an event-time timestamp | Single-digit milliseconds |
| Durable log | Kafka, partitioned by ad ID | Absorb bursts, decouple ingest from compute, allow replay after a failure | Retains raw events for days |
| Stream aggregator | Flink with RocksDB state, checkpoint every ~30s | One-minute tumbling windows keyed by ad ID, dedup by event ID, exactly-once write to the sink | About one to two minutes behind real time |
| Serving store | Druid, Pinot, or ClickHouse | Hold per-minute rollups, answer dashboard aggregate queries | Sub-second reads |
| Cold archive and recompute | Object storage (S3) plus Spark batch | Keep raw events, recompute exact billing totals, correct any streaming drift | Authoritative, hours behind |

## Why your counts drift, and how to stop double-counting

Ask how a naive version overcounts and you get at what actually makes this hard. Networks retry. A client whose request times out fires the click again. Kafka producers retry on ambiguous failures. A Flink task dies mid-window and replays from its last checkpoint. Each of these can turn one real click into two counted clicks, and across a billion events a 1% duplication rate is ten million phantom clicks that someone gets billed for.

The fix has two layers. Stamp every click with a unique event ID at the edge, a UUID or a hash of user plus ad plus a coarse timestamp, so duplicates become identifiable instead of indistinguishable. Then dedup on that ID inside the aggregation window using keyed state, so a repeated event ID lands in the count only once. For the processor-to-sink hop, Flink's exactly-once mode with checkpointing and a transactional or idempotent sink keeps a post-crash replay from writing the same count twice. An idempotent upsert keyed by (ad ID, minute) is the easiest sink to reason about: replaying a window just overwrites the same cell with the same number.

Be candid about the limit. Exactly-once inside the pipeline is not exactly-once end to end. If a user's browser genuinely fires two click pings, only a stable event ID derived from the click itself catches that, and a user who clicks the same ad twice a second apart is a product question, not a plumbing one. Interviewers like it when you draw that boundary yourself rather than claiming the diagram counts perfectly.

## The one ad that melts a single partition

Partitioning by ad ID spreads load evenly right up until one ad goes viral, all of its clicks hash to a single partition, and that partition feeds a single Flink task. The task's queue grows, its window falls behind, and one advertiser's dashboard goes stale while everyone else's stays fresh. This hot-key case is the follow-up interviewers steer toward once the happy path is on the board.

The standard move is to split the hot key. Append a small random salt to the partition key, ad ID plus a bucket from 0 to N, so a hot ad's clicks fan out across N partitions and N parallel tasks. Each task counts its slice, and a second, cheaper step sums the N partial counts per minute back into one number. You pay a little extra state and a summation step, and you buy back the ability to survive a single ad taking a large share of total traffic. To decide which keys even need salting, keep a count-min sketch over recent traffic and split only the ones running hot, so the ordinary long tail of ads stays on the simple path.

## Clicks that show up late, and the batch layer that covers you

Event time and processing time drift apart in practice. A phone loses signal, buffers a click, and delivers it forty seconds later. A tracking pixel retries after a timeout. If you aggregate by the wall-clock time an event arrives, that click lands in the wrong minute and now two minutes are wrong. Aggregating by event time, the timestamp stamped at the edge, puts the click in the right bucket, and a watermark tells the processor when a window is probably complete and safe to emit. Allowed lateness keeps a window's state around for a grace period so stragglers still update the correct bucket, and anything later than that goes to a side output for separate handling instead of quietly corrupting a closed window.

Even with all of that, the streaming numbers are a fast approximation. For billing you keep the raw events in cheap object storage and run a batch job, Spark for example, on a schedule that recomputes exact per-ad totals from the source of truth and corrects the serving store. This is the lambda arrangement: a speed layer that is fresh and roughly right, and a batch layer that is slow and exactly right, with the batch results treated as authoritative whenever the two disagree. Some teams argue for a single streaming path, the kappa style, with replay from Kafka's retained log instead of a second batch codebase. That is a defensible position to voice, especially if your retention window is long enough to reprocess from. Naming the tradeoff between one codebase and two carries more weight than which side you pick.

## What separates a strong answer

Weaker candidates draw the boxes and stop. Stronger ones say which number has to be exact and which can be approximate, then design each path to its real requirement instead of making everything exact and slow. They raise the hot-key case before being asked. They treat exactly-once as a property of a specific hop rather than a word you sprinkle on the diagram, and they can point to where duplicates still slip through. And when asked how fresh the dashboard is, they answer with a number and the reason behind it: a one-minute window plus watermark plus allowed lateness leaves you a couple of minutes behind real time, which is fine for an advertiser watching spend and not fine for fraud detection, which is a separate pipeline with a tighter budget and a different design.