Design an Ad Click Aggregator

• Record every click and redirect the user instantly — The click is a redirect on the critical path of a real person going to a landing page. It must be logged in single-digit milliseconds or not at all.
• Aggregate by campaign, creative, geography and time — Advertisers slice by whatever they like. The cube is small in dimensions and huge in rows.
• Near-real-time dashboards — Clicks and spend visible within seconds, so a campaign manager can react during a launch rather than the next morning.
• Budget enforcement — Stop serving a campaign once its daily budget is spent. Seconds of lag here is money given away.
• Exact numbers for billing — The dashboard may be approximate; the invoice may not. Both numbers come from the same events and must be reconcilable.
• Deduplicate clicks and filter invalid traffic — A double-submitted redirect, a retry, a crawler and a click farm must not all be billed as demand.
• Out of scope — Ad serving and auction (the decision of which ad to show), attribution of conversions after the click, and the advertiser-facing campaign editor.

• Ingest rate (1 M clicks/s peak) — Roughly 50 billion a day. Impressions would be a hundred times this; clicks are the expensive, billable subset.
• Redirect latency (p99 < 10 ms) — A human is waiting on this hop. Logging must be fire-and-forget to a local buffer, never a synchronous database write.
• Dashboard freshness (< 10 s) — Advertisers accept "a few seconds behind". They do not accept an hour, and they do not need the last millisecond.
• Billing accuracy (exactly once, reconciled daily) — Every billable click counted once, with an audit trail from the raw event to the invoice line.
• Durability of raw events (no loss, 13 months) — Raw events are the source of truth for disputes and for recomputation when the aggregation logic changes.
• Query latency (p95 < 1 s for a month of one campaign) — Dashboards are interactive. Scanning raw events per query would never meet this.
• Budget enforcement lag (< 5 s) — Overspend is refunded out of our own pocket, so this lag has a direct, measurable cost.

• Clicks per day: ~50 B — 1 M/s at peak, and peak is roughly twice the average over a day: ~0.5 M/s × 86 400 ≈ 50 B clicks/day.
• Raw event size and daily volume: ~400 B · ~20 TB/day — Click id, timestamp, campaign, creative, placement, user hash, geo, device, referrer, cost: ~400 B raw, ~100 B compressed columnar. 50 B × 400 B = 20 TB/day raw, about 5 TB stored.
• Raw retention: ~2 PB — 13 months × 5 TB/day compressed = ~2 PB in object storage. Cheap, and the thing that makes a recount possible when a billing bug is found.
• Aggregated rows per day: ~100 M — Minute buckets × ~1 M active (campaign, creative) pairs × a handful of dimension combinations, after rollup: ~100 M rows/day at ~100 B = 10 GB/day. Four orders of magnitude smaller than the raw stream, which is the entire point of pre-aggregation.
• Dedupe window state: ~2 B ids · ~30 GB — A 30-minute dedupe window at 1 M/s is ~1.8 B click ids. Stored exactly that would be ~50 GB; a partitioned Bloom filter at 1 % false positives is ~2 GB per window, so the choice is exactness against memory.
• Kafka partitions: ~1 000 — 1 M events/s ÷ ~1–2 k events/s per partition that a single consumer thread handles comfortably = ~1 000 partitions, keyed by campaign id so all events for a campaign land in order on one consumer.
• Dashboard query cost: ~45 k rows scanned — One campaign, one month, minute granularity: 43 200 rows in the aggregate table. A columnar store answers that in milliseconds. The same question over raw events would scan tens of billions of rows.

• User click — A real person clicking an ad and expecting to arrive at the advertiser's page. Everything on this path is measured against the delay they perceive, which is the reason the ingest design looks the way it does.
• Click endpoint (edge PoP · stateless) — Verifies the signed click token, writes the event to a local buffer, and issues a 302 to the landing page. No database, no lookup, no blocking write — a few hundred microseconds of work.
• Collector — Batches buffered events from the edge and produces them to the log with a durable ack. The edge is fire-and-forget; the collector is where the durability promise actually begins.
• Event log (Kafka · ~1 000 partitions by campaign) — The backbone. Partitioned by campaign id so every consumer sees a campaign's events in order, retained for a few days so a broken consumer can be rewound rather than losing data.
• Dedupe & filter (stream job) — Drops repeated click ids inside a rolling window and tags obviously invalid traffic — known bots, impossible click rates per user, clicks with no preceding impression. Tags rather than deletes, so a mistake is reversible.
• Stream aggregator (Flink · tumbling minute windows) — Keyed by (campaign, creative, geo, minute), counting clicks and summing cost. Checkpointed state, watermarks for late events, and idempotent writes downstream so a replay after a crash does not double-count.
• Serving store (columnar OLAP) — Pre-aggregated minute buckets rolled up to hour and day. Answers dashboard queries by scanning tens of thousands of rows instead of tens of billions. Upserts by window key, so a window can be corrected in place.
• Budget controller — Keeps a running spend per campaign in memory from the same stream, compares it to the daily budget, and publishes a pause the moment it is exceeded. Separate from the aggregator because it trades accuracy for latency and nothing else in the system should.
• Ad serving — The auction and serving system. It subscribes to pause and pacing signals; it is not part of this design, but the latency of the loop back to it is what determines overspend.
• Raw archive (object store · Parquet by hour) — Every event, partitioned by hour and campaign, written by a sink consumer. The audit trail and the input to any recount. Untouched by the fast path.
• Batch reconciler (nightly, over the archive) — Recomputes each hour from the raw archive with the full dedupe window and the final invalid-traffic verdicts, then corrects the serving store. The number the invoice is built from.
• Billing — Reads the reconciled, closed days and produces invoice lines. It never reads the streaming numbers, so a bad minute in the stream can never become a bad charge.
• Advertiser dashboard — Interactive slicing over the serving store, labelled with how fresh the numbers are and whether the day is still provisional. The label is a design decision, not decoration: it is what stops support tickets about two numbers disagreeing.

1. One click, from redirect to dashboard — The path everything else hangs off. Note what is not on it: no database write, no lookup, no synchronous aggregation.
   1. The user clicks; the edge validates the signed token and redirects immediately.
   2. The event is appended to an in-process buffer and flushed in batches to the collector.
   3. The collector produces to the event log with acks from all in-sync replicas.
   4. Dedupe and filtering drop repeats and tag suspicious traffic.
   5. The aggregator folds the event into its minute window and upserts the bucket.
   6. The dashboard reads the bucket seconds later.
2. A campaign hits its daily budget — The one place latency costs money directly. A separate, faster, less accurate path exists purely for this.
   1. The budget controller keeps running spend per campaign from the same clean stream.
   2. Spend crosses the daily budget and a pause is published immediately.
   3. Serving stops within a couple of seconds; clicks already in flight still arrive.
   4. Overspend beyond a threshold is absorbed rather than billed.
   5. As the budget resets or is raised, the controller unpauses.
3. Traffic triples during a live event — The scale-breaking case. The log absorbs the burst and the consumers fall behind, which is exactly what should happen.
   1. Click rate goes from 1 M/s to 3 M/s in a minute.
   2. The collector keeps producing; the log takes the write rate.
   3. Consumer lag grows and dashboards drift from seconds to minutes behind.
   4. Aggregator instances scale out; partition assignment rebalances.
   5. The budget path is prioritised over the reporting path while lag persists.
   6. The spike passes and consumers catch up by draining the backlog.
4. A worker crashes mid-window — The failure path that decides whether the numbers can be trusted. Replay must not double-count.
   1. An aggregator instance dies holding uncommitted window state.
   2. The replacement restores the last checkpoint and rewinds the log to that offset.
   3. Events between the checkpoint and the crash are processed a second time.
   4. Windows are re-emitted and upserted by key, overwriting rather than adding.
   5. Events arriving after their watermark are counted into a correction, not dropped.
   6. The archive sink, on its own consumer group, was unaffected throughout.
5. The nightly recount that produces the invoice — Two paths from the same events: one fast and approximate, one slow and exact. Billing only ever reads the second.
   1. After the day closes, the reconciler reads the raw archive for each hour.
   2. Dedupe runs over the whole day, not a rolling window.
   3. Final invalid-traffic verdicts are applied, including ones that need hindsight.
   4. The serving store is corrected and the day is marked final.
   5. Billing reads only finalised days and produces invoice lines.

Ask: Do you run a fast approximate pipeline and a slow exact one, or make a single pipeline serve both?

Good answers name: Streaming for serving, batch reconciliation for billing, shared upsert sink, Streaming only, with long allowed lateness, Batch only, every few minutes, Kappa: one streaming job, replayed from the log to correct.

Our pick: Streaming aggregation into the serving store for dashboards and budget control, plus a nightly batch reconciliation over the raw archive that corrects the same store and marks the day final. The dashboard labels provisional days explicitly, and the stream-versus-batch delta is monitored so drift is caught by us rather than reported by an advertiser. Billing reads finalised days only.

1. How do you keep the two implementations from drifting?
   Share the aggregation logic as a library used by both runners, and run a continuous differential check: for each closed hour, compare stream and batch totals and alert above a threshold. The check is the real defence — shared code drifts anyway through configuration and window semantics, and only a comparison catches that.
2. An advertiser screenshots a number and it changes the next morning. What do you say?
   That it was labelled provisional, that the difference is deduplication and invalid-traffic filtering applied with a full day of hindsight, and that they were billed the lower final number. That conversation is survivable only if the UI said "provisional" before the change rather than after.
3. Could you make the streaming number exact?
   Not within seconds, no. Exactness needs whole-day dedupe and fraud verdicts that depend on behaviour later in the day. You can narrow the gap — a longer dedupe window, more aggressive early filtering — but the last fraction of a percent requires hindsight, and pretending otherwise is how billing bugs get shipped.
4. Why does the batch job write to the same store rather than its own?
   So there is one place to query and one definition of a number. Two stores means every consumer has to choose, and they will choose inconsistently. The provisional/final flag carries the distinction inside one dataset, where it can be enforced.

Ask: Where does exactly-once actually come from in a pipeline with at-least-once delivery everywhere?

Good answers name: Click id dedupe in the stream plus absolute-value idempotent upserts, Transactional sink (two-phase commit into the store), At-least-once with dedupe at query time, Bloom filter for dedupe instead of exact ids.

Our pick: A click id generated at ad-serve time and carried in the signed token, deduplicated in the stream against a 30-minute rolling window held in the job's keyed state. Aggregation windows emit absolute totals, and the serving store takes them as upserts on the window key, so any replay overwrites rather than accumulates. The nightly batch repeats deduplication across the whole day from the raw archive, which is where any duplicate wider than the streaming window is caught.

1. Why is the click id minted when the ad is served rather than when it is clicked?
   Because a click generated at click time by a retrying client would be a new id on each retry, and dedupe would never fire. Minting it at serve time and signing it into the token means every retry of the same human click carries the same id — and it also gives you the impression-to-click join for free.
2. A user genuinely clicks the same ad twice, ten minutes apart. One click or two?
   A product decision, not a technical one, and the usual answer is one billable click per impression: the second click reuses the same token and therefore the same id, so dedupe collapses it. If it was a fresh impression it is a fresh token, a fresh id, and a fresh billable click. Stating that rule explicitly is what makes the system explainable.
3. How do you bound the dedupe state at a million events a second?
   A keyed, windowed state partitioned by the same key as the log — campaign — so no single instance holds the global set, with entries expiring on the window. Roughly two billion ids over thirty minutes across a thousand partitions is a few million per instance, which fits in RocksDB-backed state comfortably.
4. What if the same event lands in two partitions?
   It cannot, if the partition key is deterministic from the event — and that is why the key is campaign id rather than something random. Keyed partitioning is what makes per-key dedupe state correct; a round-robin producer would scatter duplicates across instances and defeat it.

Ask: When do you close a minute, and what do you do with a click that arrives afterwards?

Good answers name: Event-time tumbling minutes, watermark with bounded lateness, re-emit on late arrival, Processing-time windows, Session windows keyed by user, Sliding windows for smoother charts.

Our pick: Event-time tumbling one-minute windows keyed by (campaign, creative, geo), with a watermark derived from the maximum event time seen per partition minus a few seconds of slack, and allowed lateness of five minutes during which the window re-opens and re-emits. Events later than that are dropped from the stream but remain in the raw archive, where the nightly reconciliation counts them. Minute buckets are rolled up to hour and day in the serving store.

1. One partition stops receiving events. What happens to the watermark?
   It stalls, because the global watermark is the minimum across partitions, and every window everywhere stops emitting. That is the classic idle-source problem. The fix is an idleness timeout that excludes a silent partition from the watermark calculation after a while — at the cost of possibly treating its events as late when it wakes up.
2. How much state does the aggregator hold?
   Roughly the number of active keys times the number of open windows. With about a million active campaign-creative pairs and six open minutes, that is a few million entries per window generation — hundreds of megabytes to a few gigabytes across the cluster in RocksDB-backed state. Allowed lateness is the multiplier, which is why it is five minutes and not an hour.
3. An advertiser asks why their 10:00 minute changed at 10:04.
   Because clicks that happened at 10:00 arrived late — a buffering mobile client, a briefly partitioned edge — and the window re-emitted with the complete figure. The honest answer is that the first number was a lower bound. The UI should mark the last few minutes as still settling for exactly this reason.
4. Why minute buckets and not ten-second ones?
   Row count. Ten-second buckets are six times the rows for a granularity nobody queries at, and the serving store would grow to around 600 M rows a day. Minutes are fine enough for the dashboards people actually build and coarse enough to keep the store cheap. The budget controller, which does want sub-minute reaction, does not use windows at all.

Ask: What kind of store serves "clicks by creative by hour for the last month" in under a second?

Good answers name: Columnar OLAP store with pre-aggregated rollups and upsert support, Relational database with summary tables, Key-value store keyed by (campaign, dimension, bucket), Query the raw archive with a data-lake engine.

Our pick: A columnar OLAP store holding minute buckets partitioned by day and sorted by (advertiser, campaign, time), with materialised hour and day rollups that queries are routed to by granularity. Writes are upserts keyed by the window key, which serves both the stream and the batch corrections. Minute data is kept for two weeks, hours for three months, days for thirteen; the raw archive stays behind all of it for recounts and disputes.

1. An advertiser wants clicks broken down by a dimension you do not pre-aggregate.
   Pre-aggregation trades flexibility for speed, so an unforeseen dimension means either adding it to the cube — multiplying rows by its cardinality — or falling back to a slower query over the raw archive with an explicit "this may take a minute" experience. Which dimensions are in the cube is a product decision with a direct storage cost, and it should be made deliberately rather than by request.
2. What stops row count exploding as dimensions are added?
   Cardinality discipline. The cube holds low-cardinality dimensions only — campaign, creative, country, device type — and never user id or exact URL, which would multiply rows by millions. High-cardinality analysis belongs in the archive, queried on demand.
3. How do the rollups stay consistent with the minutes when a correction lands?
   Rollups are derived, not independently maintained: a corrected minute marks its hour and day dirty, and a job recomputes them from the minute buckets. Incrementally patching a rollup with a delta is the tempting shortcut and it drifts, because you cannot tell a correction from a new event.
4. Two weeks of minute data is a lot. Why keep it at all?
   Because the most common investigation is "what happened during our launch yesterday", which is a minute-level question. Beyond a couple of weeks nobody asks it, so minutes expire and hours carry the long tail. Retention per granularity is the cheapest lever on the storage bill in the whole system.

Ask: How do you record a million events a second on a path where a human is waiting?

Good answers name: Stateless edge endpoint, signed token, buffered batch to a durable log, Synchronous write to the log before redirecting, Client-side beacon after the redirect, Write to a local append-only file, shipped by an agent.

Our pick: A stateless edge endpoint that verifies an HMAC-signed click token carrying campaign, creative, placement, price and click id, appends the event to an in-process buffer, and redirects — no lookups, no blocking I/O. Buffers flush to collectors every few hundred milliseconds, and collectors produce to the log with acks from all in-sync replicas. The loss window is bounded by the flush interval and measured: it is a number on a dashboard, not an assumption.

1. How much data does a node dying actually lose?
   At most one flush interval of that node's share: a few hundred milliseconds of one node out of many, so tens of thousands of clicks in the worst case across the fleet, and far less typically. The trade is explicit — sub-10 ms redirects against a bounded, measurable loss — and making the flush interval configurable lets the business choose where on that curve to sit.
2. Why put the price in the token rather than looking it up?
   Because a lookup is a network hop on a path with a 10 ms budget, at a million requests a second. The price was already known when the auction ran, so signing it into the token makes the click self-describing. The cost is that a price change only affects ads served after it, which is the correct semantics anyway.
3. Someone fabricates clicks by replaying tokens. What stops them?
   The signature stops forgery, an expiry bounds the replay window, and the click id makes a replay a duplicate that dedupe collapses. Beyond that it becomes an invalid-traffic problem rather than an authentication one: the same id arriving from a hundred addresses is a pattern the filters catch.
4. How do you rotate the signing key without invalidating live tokens?
   Key id in the token and an overlapping validity window: the edge accepts both the old and the new key for as long as a token can live, typically an hour or two. Rotating without an overlap invalidates every ad currently on a screen, which is a self-inflicted outage.