Design ad click aggregation

Every time someone taps a sponsored banner in a cricket app or a “Shop now” ad on a news site, the ad platform has to do two things: get that person to the advertiser’s page instantly, and count the click. The counting is the hard part. Ten billion clicks a day arrive from millions of pages and phones, a few of them twice, some of them minutes late, a worrying share from bots, and during the ad break of an IPL final one sponsor’s ad can draw 400,000 clicks a second. Three customers read those counts and want different things. Advertisers want dashboards that are a minute old. The budget-pacing system wants spend within seconds, so a campaign with a ₹50 lakh daily budget stops being shown when the money runs out. And finance wants a number it can put on an invoice: every valid click billed exactly once, every invalid click never. This problem is about building one pipeline that satisfies all three.

Understand the problem

Weak answers jump straight to “Kafka, then Flink, then a database”. The real problem is that one click count has three readers with three definitions of “correct”: a dashboard that wants it soon, a pacing system that wants it now, and an invoice that wants it exactly. Say that early; it decides almost every trade-off that follows.

Functional requirements

1. Users who click an ad should land on the advertiser’s page immediately, and the click should be recorded durably on the way.
2. Advertisers should be able to query click metrics — clicks, invalid clicks and spend per ad or campaign, sliced by country and device, over any time range — with numbers about one minute old.
3. Advertisers should be billed for each valid click exactly once, with invalid clicks (bots, accidental taps, click farms) excluded before the invoice or credited after it.
4. The budget-pacing system should see spend within seconds, so it can slow or stop a campaign that is about to hit its daily cap.

Non-functional requirements

• Throughput. 10 billion clicks a day, about 116k per second on average, designed for a peak of 1M clicks per second when a single ad break in a cricket final reaches tens of millions of screens at once.
• Redirect latency. Under 10 ms at p50 and under 50 ms at p99 of server time. The user is waiting on a blank page while we count.
• Durability. A click is never lost once the user has been redirected. Lost clicks are lost revenue; double-counted clicks are overcharged advertisers. Both are bugs.
• Freshness. Dashboards within about 1 minute at p95; pacing within 10–20 seconds.
• Accuracy. Billing is exact: each valid click billed once. Live dashboard numbers are labelled provisional and converge to the billing numbers to within 0.1% once the day closes. Final daily numbers are ready by 06:00 IST the next morning.
• Queries. About 6,000 queries a second at the evening peak, under 1 s at p99 for a 30-day dashboard.
• Availability. 99.99% for the click path (if it is down, advertisers lose visitors), 99.9% for dashboards.
• Retention. Raw clicks for 13 months for audits and disputes; minute-level aggregates for 30 days, hourly for 2 years, daily indefinitely.

Out of scope

• Choosing which ad to show (auction, targeting, bidding). That system consumes our spend numbers; it is not part of this design.
• Impression and viewability counting. It uses the same pipeline at roughly 100 times the volume; clicks are where the money and the hard correctness questions are.
• The fraud models themselves. We design the plumbing that applies their verdicts, not the machine learning.
• Invoicing, payments and tax. They start from our final numbers; see usage metering and billing and a double-entry ledger.
• Conversion attribution (which click led to a sale). Covered briefly as a follow-up.

The set-up

Plan the approach

Build the simplest path for each requirement — redirect, live counts, billing, pacing — then spend most of the time in the deep dives, because this problem is won on correctness under failure. Name the shape early: “an edge that records clicks, a log, a stream job for fresh numbers, an archive and a batch job for final numbers.”

Core entities

• Impression token. Created by the ad server when an ad is shown and embedded in the ad’s link: impression ID, ad, campaign, advertiser, publisher, the price per click cleared in the auction, landing URL, issue time and expiry — signed with HMAC so nobody can edit it.
• Click event. One row per recorded click: a click ID derived from the impression ID, the event time stamped by our click server, slicing dimensions (country, device, OS), and validity flags.
• Aggregate row. Counts for one ad, one minute and one slice: clicks, invalid clicks and spend, plus whether the window is final.
• Campaign budget. Daily cap, spend so far, pacing state (on, slowed, paused).
• Invalid-traffic verdict. A click ID, a verdict and reason from a rule or model, and when it was decided.
• Billing line. Advertiser, campaign, day, billable clicks, spend and later adjustments such as credits.

A click event, as the click server writes it:

{
  "click_id": "c_5f0d9a7e21b4",
  "imp_id": "i_7a1e44c09d3f",
  "ad_id": 4242,
  "campaign_id": 311,
  "advertiser_id": 77,
  "publisher_id": 9001,
  "event_time": "2026-05-24T19:41:07.412Z",
  "country": "IN",
  "device": "mobile",
  "cpc_micros": 4500000,
  "ip_prefix": "49.36.1.0/24",
  "ua_hash": "u_51c0be",
  "valid": true,
  "invalid_reason": null,
  "region": "ap-south-1"
}

And the aggregate table the dashboards read. The primary key is the whole point: it makes every write idempotent.

CREATE TABLE click_agg_minute (
  ad_id          BIGINT,
  window_start   TIMESTAMP,     -- event-time minute, UTC
  country        CHAR(2),
  device         VARCHAR(8),
  region         VARCHAR(16),   -- regional pipeline that produced the row
  campaign_id    BIGINT,
  advertiser_id  BIGINT,
  clicks         BIGINT,        -- valid and de-duplicated
  invalid_clicks BIGINT,
  spend_micros   BIGINT,
  is_final       BOOLEAN,       -- false while the window can still change
  PRIMARY KEY (ad_id, window_start, country, device, region)
);

API

Three surfaces: the click link, the metrics API and the billing API. Pacing reads an internal stream.

The click. The ad’s link points at our click server with the signed token. The response is a 302 Found, not a 301: a permanent redirect may be cached by the browser, and a cached redirect skips the click server, so the next click is never counted. Cache-Control: no-store makes the intent explicit.

GET /c?t=eyJpbXAiOiJpXzdhMWU0NGMwOWQzZiIsImFkIjo0MjQyfQ.Hk3s9QzL HTTP/1.1
Host: clk.adplatform.example

HTTP/1.1 302 Found
Location: https://shop.example/sale?utm_source=ads&utm_campaign=311
Cache-Control: no-store

Metrics. Advertisers query by time range, granularity, grouping and filters. The response says up to when the numbers are final, so the dashboard can grey out the provisional tail.

GET /v1/advertisers/77/metrics?metrics=clicks,invalid_clicks,spend&group_by=campaign_id,country&granularity=minute&from=2026-05-24T19:00:00Z&to=2026-05-24T20:00:00Z&filter=device:mobile

{
  "granularity": "minute",
  "final_until": "2026-05-24T19:58:00Z",
  "rows": [
    { "ts": "2026-05-24T19:41:00Z", "campaign_id": 311, "country": "IN",
      "clicks": 18342, "invalid_clicks": 412, "spend": 82539.00, "final": true },
    { "ts": "2026-05-24T19:59:00Z", "campaign_id": 311, "country": "IN",
      "clicks": 9120, "invalid_clicks": 133, "spend": 41040.00, "final": false }
  ]
}

Billing. One call per campaign-day, readable once the daily close has run. Spend is capped at the budget; anything delivered beyond it is the platform’s cost, not the advertiser’s.

GET /v1/billing/campaigns/311/days/2026-05-24

{
  "campaign_id": 311,
  "day": "2026-05-24",
  "status": "final",
  "billable_clicks": 1111397,
  "invalid_clicks": 38810,
  "budget": 5000000.00,
  "charged": 5000000.00,
  "delivered_over_budget": 1286.50,
  "adjustments": []
}

Back-of-envelope estimates

Show the arithmetic; it decides partition counts, state size and where the pressure is. (For the method, see back-of-envelope estimation.)

Click rate. 10 billion clicks a day: 10,000,000,000 ÷ 86,400 s ≈ 116k clicks/s on average. Traffic bunches into evening hours and synchronised ad breaks, so design for 1M clicks/s at peak — about 1,000,000 ÷ 116,000 ≈ 8.6× the average.

Bandwidth. A click event with IDs, a hashed user agent, IP prefix, dimensions and price is about 500 bytes. At peak: 1M × 500 B = 500 MB/s, about 4 Gbit/s into the log. On average: 116k × 500 B ≈ 58 MB/s.

Raw volume. 10B × 500 B = 5 × 10¹² B = 5 TB a day. Three days in Kafka with three replicas: 5 TB × 3 × 3 = 45 TB of broker disk before compression. Parquet compresses a day to roughly 1 TB (assume 5×), so 13 months ≈ 395 days × 1 TB ≈ 400 TB in object storage.

Partitions. With 256 partitions, the peak per partition is 1M ÷ 256 ≈ 3,900 clicks/s, about 2 MB/s — comfortable for one partition and one consumer task.

Dedup state. Remember every click ID for one hour. In a busy hour at 3× the average rate: 116k × 3,600 × 3 ≈ 1.25B IDs. At about 50 bytes each (16-byte ID plus expiry and overhead): 1.25B × 50 B ≈ 62 GB across the job, or about 245 MB per task — easily held on local SSD.

Aggregate rows. An average minute has 116k × 60 ≈ 7M clicks. Assume they land on about 1M distinct (ad, country, device) combinations — about 7 clicks per row. That is 1M × 1,440 minutes = 1.44B rows a day at minute grain; at ~100 bytes a row, about 144 GB a day before columnar compression, or 4.3 TB for 30 days. If a typical combination is active in 12 of an hour’s 60 minutes, hourly rows are 1.44B ÷ 12 = 120M a day; if active in 6 of 24 hours, daily rows are 120M ÷ 6 = 20M a day.

Query load. At the evening peak, say 50,000 dashboards are open, each with 6 panels refreshing every minute: 50,000 × 6 ÷ 60 = 5,000 queries/s, plus about 1,000/s from the reporting API: ~6,000 queries/s.

Stream job size. 256 tasks each handle 3,900 clicks/s at peak, but size for recovery, not steady state: a 10-minute outage at peak leaves 1M × 600 s = 600M clicks of backlog. Draining it in 10 minutes while live traffic continues needs 2M clicks/s, so plan for 2× peak, about 7,800 clicks/s per task.

Click servers. An HMAC check and a local append cost microseconds; assume 20,000 redirects/s per server: 1M ÷ 20k = 50, doubled for zone failures = 100 click servers.

High-level design

Build one requirement at a time; by the end the design is correct and complete, though not yet hardened for scale.

Clicks redirect immediately and are recorded durably

A click must prove it came from an ad we served, be written somewhere it cannot be lost, and send the user on. Everything else happens after the user has left.

The click server verifies the token’s HMAC and expiry, so nobody can forge clicks or change the price. It stamps the event time from its own NTP-synced clock — never the phone’s, which a bot can set to anything. And it derives the click ID from the impression ID, so a double tap, a retried request or a replayed log line all carry the same ID: one impression yields at most one billable click, by construction.

1. The browser follows the ad’s link to the click server with the signed token.
2. The click server checks the token, stamps the event time and derives click_id. A bad signature is still redirected — never punish a real user for a stale page — but recorded as invalid.
3. It appends the event to a local spool, an append-only file on SSD flushed with one fsync per batch every few milliseconds (group commit), so one flush covers thousands of clicks.
4. Once the batch is flushed, it returns 302 Found and the browser loads the advertiser’s page.
5. A shipper tails the spool and sends batches to the clicks topic in Kafka with the idempotent producer.

Why not write straight to Kafka? A fully replicated Kafka write takes on the order of 10 ms, per the Kafka design docs, which fits the budget — but a Kafka incident would then become a redirect outage, at the moment you can least afford it. The spool decouples them: 100 click servers with 100 GB SSDs can buffer 100 × 100 GB ÷ 500 B = 20 billion clicks, two full days. The cost is a shipper to operate, and a crashed server’s spool must be drained before its clicks are counted.

Partition the clicks topic by click ID, not by ad. Keying by ad would put the IPL sponsor’s 400k clicks/s — 200 MB/s — on one partition while 255 idle. Click IDs hash evenly; the stream job re-keys by ad later, where it can handle skew.

Advertisers query fresh metrics

Now count. A Flink job reads clicks, drops duplicates, applies cheap validity rules, and counts clicks per ad, country and device in one-minute tumbling windows based on event time. Each count is written as an upsert to a real-time OLAP store — Apache Pinot, Apache Druid or ClickHouse are the usual choices — keyed by the window. A query service sits in front for authentication, tenant isolation and caching.

1. Validate. Parse the event, check required fields, and apply rules that need no history: bad signature, expired token, a user agent on a known-bot list. Invalid clicks are labelled, not deleted, so they can be counted separately.
2. Dedup. Key the stream by click_id and keep a set of IDs seen in the last hour in Flink’s keyed state. A repeat ID is dropped.
3. Window. Re-key by (ad, country, device) and count in one-minute event-time windows, emitting the running count every 10 seconds and a final count when the window closes.
4. Upsert. Each emission overwrites the row for its key in the OLAP store. Dashboards query it through the query service, which adds the advertiser filter itself — a tenant never sees another tenant’s ads.

One minute is a deliberate grain: small enough for fresh dashboards and clean rollups into hours and days, large enough that 7M clicks become about 1M rows. Uber’s ad-event pipeline chose the same one-minute tumbling window for the same reasons.

Advertisers are billed for each valid click exactly once

Dashboards can be provisional; invoices cannot. Billing needs what the live stream lacks: clicks that arrived hours late, verdicts from fraud models that take hours to run, and a result you can recompute in a dispute six months later. So billing reads an immutable archive, not the dashboard tables.

An archiver, a second consumer of clicks, writes raw events to object storage as hourly Parquet files. A daily billing job reads the whole day, drops duplicate click IDs across it, removes clicks the fraud models marked invalid, prices each click at the cost-per-click in its token, caps each campaign at its budget, and writes a billing ledger for invoicing.

1. The archiver writes one Parquet file per partition per hour, committed atomically, so a half-written file is never visible.
2. Fraud models read the archive and write verdicts keyed by click ID.
3. At 02:00 IST, leaving two hours for stragglers, the billing job processes the previous day: full-day dedup, verdicts, pricing, budget caps, billing lines.
4. Reconciliation compares billing totals per campaign with the live OLAP totals and alerts on a difference above 0.1%.

The batch job is slow by design, and that buys completeness. Reading 1 TB of Parquet at about 100 MB/s per core is 10,000 core-seconds — under three minutes on 64 cores before shuffling — so it finishes long before 06:00, even with a rerun.

Budget pacing sees spend within seconds

The last reader is the most impatient. When a campaign nears its daily budget, ad servers must stop showing it; every second of delay is delivery the platform cannot charge for. The Flink job emits a second output, spend per campaign every 10 seconds, on a campaign-spend topic. A budget service tracks each campaign’s spend for the day and pushes slow-down or pause flags to the ad servers.

1. The Flink job sums spend per campaign in 10-second windows and writes it to campaign-spend.
2. The budget service adds each amount to the campaign’s daily total. Records are keyed by (campaign, window), so a duplicate overwrites rather than adds.
3. At, say, 95% of the cap the service tells ad servers to slow the campaign; at 100%, to stop. Ad servers apply the flag on the next auction.

Why 10 seconds matters: a sponsor in an ad break might get 20,000 clicks a minute at ₹5 each, ₹1 lakh of delivery every minute. With 2 minutes of lag the campaign could overshoot by about ₹2 lakh; with a 10-second loop, by about ₹1 lakh × 10 ÷ 60 ≈ ₹17,000. Uber’s write-up names this risk: when its aggregation job falls behind, pacing goes stale and campaigns overspend. The follow-ups cover a fallback.

You now have a working system: fast redirects, fresh dashboards, billing from an archive, and a pacing loop. The deep dives are about what breaks when machines crash, clicks arrive late, one ad gets 40% of the traffic, and bots join in.

Deep dives

How do we count every click exactly once?

“Exactly-once” is a misleading name. Messages will be delivered more than once — acknowledgements get lost, jobs crash and replay. What you can guarantee is that every click affects the result once, however often it is delivered. That must hold at every hop, because duplicates appear at four places.

Bad: at-least-once everywhere, and hope. Easy and fast, but duplicates are not rare: a Flink restart replays everything since the last checkpoint. With 30-second checkpoints at 1M clicks/s, one crash re-counts up to 30M clicks, and every advertiser in that half-minute is overcharged.

Good: deduplicate at the very end. Store every raw click in the OLAP store keyed by click_id and count at query time. Correct whatever happens upstream, but every dashboard query scans raw rows — 10B a day instead of 1.44B pre-aggregated ones. You pay for exactness on every read.

Great: make each hop idempotent, so the result is exact without paying at read time.

• At the edge, the click ID comes from the impression ID, so retries and double taps collapse to one ID.
• Into Kafka, the idempotent producer tags each batch with a producer ID and sequence number, and the broker discards a batch it has already written. It is a config flag.
• Inside Flink, dedup is keyed state: click ID to “seen”, with a one-hour time-to-live. A checkpoint snapshots all state plus the Kafka offsets; after a crash both are restored together, so replayed clicks meet dedup and window state that have not seen them yet, and count once. The MillWheel paper describes the same idea: each record’s unique ID is stored in the same atomic write as the state change it caused.
• Into the sink, make writes idempotent or transactional.

Why only one hour of dedup state? Retries arrive within seconds, and checkpoints already handle replays. MillWheel garbage-collects dedup IDs once senders stop retrying — usually minutes, a few hours for sources known to deliver late. A repeat click on a token over an hour old slips into the live count; the daily billing job, which dedups over the whole day, removes it.

Now the sink. If the window writes “+40 clicks” and the job replays, the sink receives “+40” again. Whether that double counts depends on the sink.

There are two ways to make the last hop safe.

Transactional sink (two-phase commit). Flink writes each checkpoint’s output inside a Kafka transaction: it pre-commits when the checkpoint barrier reaches the sink and commits once every operator has finished the checkpoint. Consumers read with isolation.level=read_committed, so output from a run that crashed before committing is never seen. It works for any output, even without a natural key — but nothing is visible until the next checkpoint. Uber’s ad pipeline does this with a two-minute checkpoint and names the checkpoint as its main freshness bottleneck; it also stamps each aggregate with a record ID that Pinot upserts and the warehouse use to drop duplicates.

Idempotent upsert by a deterministic key. Emit the window’s running total, not the increment, under the key (ad, minute, country, device, region). A replay overwrites the same keys. Output is visible immediately, so 10-second early firings reach dashboards within seconds. The cost: the sink must support upserts, and during a restart a row can briefly show an older value. Because a window’s counts only grow, you can tell the sink to keep the larger value, so a late duplicate never moves a number backwards.

This design uses both: upserts for aggregates, where freshness matters and the key is natural, and two-phase commit for the archiver’s files — each Parquet file is written to a temporary path and renamed into place on commit.

What do we do with clicks that arrive late or out of order?

Clicks do not reach the job in the order they happened. A shipper pauses during a network blip and then sends a minute of clicks at once; one region’s Kafka lags another’s; after a 10-minute outage the job reads a 600M-click backlog in a rush.

Bad: windows in processing time. Count each click in the minute the job sees it. Simple — and wrong exactly when it matters. After that outage, all 600M clicks land in the minute the job came back: dashboards show ten flat minutes and then a gigantic spike, and pacing sees ten minutes of spend in one window and pauses campaigns that were on track. A replay of yesterday would land in today’s windows.

Good: windows in event time with a watermark. Count each click in the minute it happened, by the click server’s timestamp. The job then needs to know when a minute is complete. A watermark is that estimate: “I expect no more clicks older than t.” With 15 seconds of bounded out-of-orderness, the watermark trails the newest event time by 15 seconds; when it passes 12:02:00, the 12:01 window fires.

Two details bite in production:

• The watermark is the minimum across inputs. An operator’s event time is the smallest watermark among the partitions feeding it, so one slow partition holds back every window — the Dataflow paper calls watermarks “too slow” for this reason. A partition that goes quiet stalls event time entirely, unless you let Flink mark it idle after a timeout.
• The watermark is a guess. Sometimes it is “too fast” and a click arrives after its window fired. You need a policy for late data.

Great: event time, plus a policy for each degree of lateness.

• Slightly out of order (seconds): absorbed by the 15-second watermark delay. Click C happened at 12:01:55, arrived at 12:02:05 and was counted on time.
• Late (up to 10 minutes): set an allowed lateness of 10 minutes. Flink’s default is zero: late elements are dropped. With allowed lateness, Flink keeps window state after firing, and a late click such as D triggers a late firing; the upsert changes the row from 3 to 4. The cost is state: each window lives 11 minutes instead of 1, about 11M open windows at ~64 bytes each — roughly 700 MB across the job.
• Very late (beyond 10 minutes): send to a side output instead of dropping. Click E goes to a late-clicks topic that a small job applies as corrections to hourly rows; the daily billing job includes it anyway.
• Fresh before final: fire the open window every 10 seconds with its running total. The Dataflow paper calls this accumulating mode — each result replaces the previous one for the window, exactly what an upsert sink expects.

And once more: event time comes from our server, not the device. A click a bot stamps “last week” would rewrite closed numbers. Keep the device time only as a fraud signal.

What happens when one ad gets a huge share of the clicks?

Picture the toss of an IPL final. One sponsor has bought the ad break, and for ninety seconds its ad draws 400,000 clicks a second — 40% of all traffic. Dedup is keyed by click ID and spreads evenly. But windowing is keyed by ad, and every click for a key goes to the same task: one task of 256 receives 400k clicks/s while sized for about 8k.

And it is not just slow for ad42: it back-pressures every upstream task, so all 256 sources slow down and every advertiser’s dashboard freezes on the busiest night of the year.

Bad: more machines or more parallelism. A key goes to exactly one task however many exist. Going from 256 to 1,024 tasks spreads the other ads thinner and leaves ad42’s task as overloaded as before.

Good: pre-aggregate before the shuffle. Each source task keeps an in-memory map of (ad, minute, country, device) to a partial count and flushes it once a second. This is MapReduce’s combiner; Flink’s SQL engine ships it as local-global aggregation on top of mini-batching. For ad42, each of 256 sources now sends one partial per slice per second: with about 4 slices, 256 × 4 ≈ 1,000 records a second instead of 400,000. The cost is up to a second of latency and a small buffer per task. It works because counts and sums are associative — partials add up to the total; it would not work for a median.

Great: combine everywhere, and salt known-hot keys too. Some keys deserve more than one task even after combining — an ad with thousands of slices, or a future per-second view. Key those by (ad42, n) with n from 0 to 15, so 16 tasks share the work, and sum the 16 partials in a small second stage. That costs a second shuffle and more state, so salt only keys flagged as hot; the ad server knows in advance which ads bought an ad break.

Two more places need care: never partition Kafka by ad, and keep the budget service off the per-click path — pacing reads one 10-second total per campaign, whatever the click rate. More in hot keys.

How do we keep invalid clicks out of the counts and the bill?

Some clicks are not real interest: bots, click farms, a publisher clicking ads on their own site, competitors burning a rival’s budget, a thumb that slipped. Google’s help pages describe its defence as automated filters, machine learning and manual review; invalid traffic found before the billing cycle closes is removed from metrics and the bill, and traffic found later is credited on a following invoice. A good model to copy, because it admits that you will not catch everything in real time.

Bad: delete suspicious clicks in the stream. Fast, and it destroys evidence. When a rule turns out wrong — it flagged a whole mobile carrier because thousands of users share one IP — the clicks are gone for good, and you cannot show advertisers what you filtered.

Good: label, never delete. Every click keeps a valid flag and a reason; the stream counts clicks and invalid_clicks separately, and the archive keeps everything. A wrong rule is fixed by re-running it over the archive.

Great: layers that trade speed for depth, with verdicts stored as data.

1. At the click server (microseconds): flag forged or expired tokens and user agents or IP ranges on known-bot and data-centre lists. No history needed.
2. In the stream (seconds to a minute): rules with a little state — more than 5 clicks on one ad from one device in a minute, a click under half a second after the ad rendered, a burst from one IP prefix on one publisher. They are keyed counters in short windows, the same machinery as the aggregation, and each click records the rule-set version that judged it.
3. Offline (hours to days): models over the archive spot what no single click reveals — a publisher whose click-through rate tripled overnight, a thousand devices behaving identically. They write verdicts (click ID, verdict, reason, model version) to a table.
4. Billing applies verdicts at the close. Clicks judged invalid before the close are never billed; those judged invalid after invoicing become a credit on the next invoice. A closed invoice is never edited.

Dashboards can therefore change after the fact — yesterday’s 18,342 clicks may become 18,101 when a model catches a click farm. Say so in the UI (“final after the daily close”), or advertisers will file tickets about disappearing clicks.

How do we produce billing-grade numbers, and fix them when something goes wrong?

Live numbers want speed and may be revised. Billing numbers want completeness — every late click, every fraud verdict — and must be reproducible: if an advertiser disputes March in July, you must show how every rupee was computed. And one day a bug will corrupt some numbers, so you need a way to recompute history.

Bad: bill straight from the live stream. One system, one number. But very late clicks went to a side output, fraud verdicts arrive hours after windows close, and a bug that overcounted for six hours has written rows nothing can recompute. You would invoice from numbers you cannot defend.

Good: the Lambda architecture. A streaming job for fresh, approximate numbers and a separate batch job over the archive for correct ones. Billing becomes complete and recomputable. The cost is two implementations of “a valid click” — say a Flink job and a Spark job — that slowly disagree. The Dataflow paper reports exactly this: a team running a weakly consistent stream plus a nightly batch “truth” job found customers stopped trusting the streaming numbers, and rebuilt around strong consistency.

Great: one pipeline definition, run in two modes, over an immutable log.

• The raw log is the source of truth. Clicks are immutable facts — three days in Kafka, 13 months in object storage. Every number is a function of that log plus versioned rules and verdicts.
• One definition of a valid click. Write dedup, rules, windowing and pricing once — in Flink, which can run the same program over bounded input in batch mode, or in Apache Beam, which grew out of the Dataflow model — and run it live over Kafka and as a batch over a closed day.
• The daily close is a replay with full-day dedup and the latest verdicts, writing billing lines keyed by (campaign, day, run version), so a rerun replaces rather than adds.
• Backfills write a new version. After a bug, replay the affected hours into a new version of the tables, compare, and switch readers in one step. Nothing is edited in place, so a bad backfill is undone by switching back.
• Reconcile daily. Small live-versus-close differences are expected (repeats over an hour apart, very late clicks, verdicts); above 0.1% pages someone, because the runs saw different data or ran different code.

How do we answer advertiser queries over billions of rows in under a second?

This is a read-heavy analytical workload: 6,000 queries a second, each grouping many rows by a few dimensions over a time range, on data that changes every second. That is columnar OLAP territory — but what you store matters more than which store.

Bad: query raw clicks. Every click in the OLAP store, GROUP BY at query time. Maximum flexibility, but a 30-day dashboard for a big advertiser scans hundreds of millions of rows, and at 6,000 queries a second the cluster cost is enormous. Raw clicks belong in object storage for batch jobs.

Good: one pre-aggregated minute table, the stream job’s 1.44B rows a day. A one-hour view is fast, but a 30-day chart still reads 43,200 minute buckets per slice, and minute data kept forever costs 144 GB a day.

Great: tiered rollups and a query router.

• Roll up on a schedule. When an hour is past allowed lateness, sum its minute rows into hour rows; when a day closes, hours into days — and the daily close overwrites day rows with billing-grade numbers.
• Route by range and grain. The query service picks the coarsest table that answers exactly. A 30-day chart for a campaign with 20 slices reads 30 × 24 × 20 = 14,400 hour rows instead of 864,000 minute rows.
• Partition for the access pattern. Every query filters on one advertiser and a time range, so segment by time and partition or sort by advertiser, letting the store skip whole segments. Pinot’s star-tree index and Druid’s ingestion-time rollup are built-in forms of the same pre-aggregation.
• Cache what cannot change. A final hour never changes before the daily close, so cache final buckets and ask the store only for the open tail.
• Guard the dimension list. Every dimension multiplies rows; adding publisher, with a million values, could multiply the minute table many times. Put high-cardinality slices in dedicated tables, and use HyperLogLog sketches for unique users.

Regions merge at query time: rows carry their region and queries sum across them, so one region’s outage never blocks another’s windows from closing.

The final design

The user-facing path is just the click server and its spool; everything else is asynchronous, and everything that touches money is idempotent by construction.

Follow one click, and the numbers built from it:

1. Click. The click server verifies the token, stamps the event time, derives the click ID, appends to its spool with a group commit and returns 302 Found — a few milliseconds of server time.
2. Log. Shippers send spooled batches to the regional clicks topic with the idempotent producer, partitioned by click ID across 256 partitions; Kafka keeps three days.
3. Count. The Flink job labels clicks with cheap rules, drops repeat IDs using an hour of keyed state, pre-aggregates per source task, and counts per (ad, minute, country, device) in event-time windows — 15-second watermark, 10-minute allowed lateness, 10-second early firings, side output beyond that — checkpointing state and offsets every 30 seconds.
4. Serve fresh numbers. Results are upserted by key into the minute table; hour and day rollups follow as windows become final.
5. Pace. Spend per campaign every 10 seconds goes to the budget service, which sends slow-down or pause flags to the ad servers.
6. Query. The query service enforces the tenant filter, picks the coarsest exact table and caches final buckets.
7. Archive. The archiver writes hourly Parquet files to object storage with a two-phase commit; fraud models read them and write verdicts.
8. Close the books. At 02:00 IST a batch run of the same code replays yesterday with full-day dedup and verdicts, prices and caps, writes the ledger, reconciles with live totals and overwrites the day rows, so dashboards and invoices agree.

Check it against the requirements. Redirects cost a token check and a batched local write, with no remote call. Durability: a click is on local disk before the 302 and in three Kafka replicas soon after, with two days of spool headroom. Throughput: about 2 MB/s per partition at peak, a stream job sized for 2× peak, and the hottest ad reduced to ~1k records a second. Freshness: 10-second early firings and upserts keep dashboards well inside a minute; pacing sees spend in 10–20 seconds. Accuracy: click IDs, idempotent producers, checkpointed dedup and upserts make live counts exact except for rare repeats and very late clicks, which the close catches; billing is exact and recomputable, with drift over 0.1% raising an alarm. Queries: 6,000 a second hit tiered rollups and a cache, never raw clicks.

What interviewers look for by level

Follow-up questions

What if the stream job falls behind during the biggest ad break?

Dashboards freeze and, worse, pacing goes blind while spend is highest — the overspend risk Uber’s write-up calls out. Make the budget service lag-aware: each spend record carries the watermark it reflects, and when that is more than about 60 seconds old, the service projects spend from the campaign’s recent rate and slows campaigns near their cap until fresh data returns. Keep the job ahead, too: size for 2× peak, pre-aggregate hot keys, alert on lag in seconds rather than messages, and give pacing its own small job so trouble in the heavy per-ad windows cannot starve it.

What if advertisers want unique users reached, not just clicks?

Counts add across minutes and regions; unique users do not — someone clicking in two different minutes counts once for the hour. Storing every user ID per row is too big, so store a HyperLogLog sketch: a few kilobytes, about 1–2% error, and sketches merge, so hours combine into days and regions at query time. Use hashed IDs, keep sketches out of billing — nobody is invoiced on an estimate — and label the metric “estimated reach”.

What if a whole region goes down?

Regions run independent pipelines, so the others keep counting while DNS or anycast moves users to healthy click servers. Clicks spooled in the failed region wait on disk and ship when it returns — late, often beyond allowed lateness, so they flow through the side output and the daily close, which reads every region’s archive. One hazard: a retry that lands in a second region gives the same click ID to two regional pipelines, which per-region dedup cannot catch. The close dedups globally, so billing is unaffected; live numbers may be a hair high.

What if we also need to attribute sales to the clicks that caused them?

That joins two streams far apart in time: a click now, an order up to 7 days later. Keep valid clicks in a store keyed by user with a TTL equal to the attribution window — Flink keyed state, or a key-value table with expiry, as Uber does with a document store — and when an order arrives, look up matching clicks and emit an attribution with a deterministic ID built from order and click. The close joins the same way over the archive, so attributions are as reproducible as clicks.

What if an advertiser disputes their bill?

Show your working. Each billing line records the run, code, rule and model versions it used; the raw clicks are in the archive; and the close can be re-run for one campaign to reproduce the number. Offer a click-level export — click ID, time, country, device, valid or not and why — so the advertiser can compare with their own analytics, and end disputes with a credit on the next invoice, never an edit to a closed one. Thirteen months of raw retention exists for exactly this conversation.

How to say it in the interview

“I’m designing click aggregation for an ad platform: about ten billion clicks a day, a peak of a million a second during a big ad break, and three readers — dashboards that want minute-fresh numbers, pacing that wants spend in seconds, and billing that wants every valid click exactly once.

The click server verifies a signed impression token, stamps the event time with its own clock, derives the click ID from the impression ID, appends to a local spool and returns a 302. Kafka is never on the user’s path. Shippers send clicks to Kafka with idempotent producers, partitioned by click ID so no ad becomes a hot partition.

A Flink job drops repeat click IDs with an hour of keyed state, labels invalid clicks with cheap rules, pre-aggregates per source task so a hot ad doesn’t overload one task, and counts per ad, minute, country and device in event-time windows — a fifteen-second watermark, ten minutes of allowed lateness, and a side output for anything later. Checkpoints snapshot state and Kafka offsets together, and results are upserted by window key into a real-time OLAP store, so a replay overwrites instead of double counting. The same job sends per-campaign spend every ten seconds to a budget service that pauses campaigns at their cap.

For billing, an archiver writes the raw log to object storage. Each night a batch run of the same pipeline code replays the day with full-day dedup and fraud-model verdicts, and writes the ledger. Live numbers are provisional, billing numbers are final, we reconcile them daily, and invalid traffic found after invoicing becomes a credit.

Queries hit minute, hour and day rollups through a router and a cache, so a thirty-day chart reads thousands of rows, not billions.”

Concepts used in this design

• Flink and stream processing — keyed state, checkpoints, event-time windows and watermarks.
• Kafka — the replayable log between the click servers and everything else, with idempotent producers and transactions.
• Delivery guarantees: at-least-once, exactly-once and DLQs — why “exactly-once” means effectively once.
• Effectively-once: idempotent consumers and the inbox pattern — dedup by click ID and upserts by window key.
• Idempotency — deterministic IDs that make retries and replays harmless.
• Batch vs stream processing — live numbers from a stream, final numbers from a batch replay of the same code.
• Clocks, ordering and conflict resolution — event time versus processing time, and whose clock to trust.
• Hot keys, cache stampede and invalidation — the sponsor ad that takes 40% of all clicks.
• Backpressure and load shedding — how one overloaded task stalls the whole job.
• Bloom filters — a fast path for dedup, and why false positives cost revenue.
• Count-min sketch and HyperLogLog — approximate unique reach and heavy hitters.
• OLTP vs OLAP: warehouses and columnar storage — why dashboards run on a columnar, pre-aggregated store.
• Blob storage (S3) — the 13-month raw archive that billing and replays read.
• Metering and usage-based billing — turning counted events into invoices and credits.
• Design top-K trending and Design web analytics — sibling problems built on the same event pipeline.
• Design a distributed message queue (Kafka) — what sits underneath the clicks topic.

References and further reading

1. MillWheel: Fault-Tolerant Stream Processing at Internet Scale (Akidau et al., VLDB 2013)research.googleLow watermarks; exactly-once by storing each record’s unique ID in the same atomic write as the state change; a Bloom filter as a fast path for never-seen IDs; dedup IDs garbage-collected after minutes, or hours for late sources.
2. The Dataflow Model (Akidau et al., VLDB 2015)vldb.orgEvent time versus processing time, watermarks that are “too fast” or “too slow”, triggers with accumulating and retracting panes, ad billing as the motivating example, and the Lambda-architecture team whose customers stopped trusting weakly consistent numbers.
3. Real-Time Exactly-Once Ad Event Processing with Apache Flink, Kafka, and Pinot (Uber Engineering)uber.comA production ad-event pipeline: keyed-state dedup, one-minute tumbling windows, transactional Kafka sinks committed on a two-minute checkpoint, read_committed consumers, record IDs for idempotency, Pinot upserts, three-day Kafka retention, and pacing overspend when the job lags.
4. An Overview of End-to-End Exactly-Once Processing in Apache Flink (with Apache Kafka, too!)flink.apache.orgCheckpoints as snapshots of state plus input positions, and the two-phase commit sink: pre-commit at the checkpoint barrier, commit after every operator succeeds, commits that must be idempotent.
5. Apache Flink documentation: Kafka connectornightlies.apache.orgKafkaSink delivery guarantees; EXACTLY_ONCE delays visibility until a checkpoint and needs read_committed consumers; the transaction timeout must exceed checkpoint plus restart time or data can be lost.
6. Apache Kafka documentation: Design (message delivery semantics)kafka.apache.orgThe idempotent producer (producer ID plus sequence numbers), atomic writes across partitions with transactions, read_committed isolation, and a commit wait on the order of 10 ms.
7. Apache Flink documentation: Windowsnightlies.apache.orgTumbling windows, allowed lateness (default zero, so late elements are dropped), late firings, window state kept until lateness expires, and side outputs for late data.
8. Apache Flink documentation: Generating watermarksnightlies.apache.orgBounded out-of-orderness watermarks, the watermark as the minimum across parallel inputs, and idleness detection so one quiet partition does not stall event time.
9. Apache Flink documentation: Performance tuning for streaming aggregationnightlies.apache.orgMini-batch aggregation (buffering trades latency for fewer state accesses) and local-global aggregation as the combine-then-reduce fix for skewed keys.
10. Google Ads Help: Invalid trafficsupport.google.comInvalid traffic is found by automated filters, machine learning and manual review; what is caught before the billing cycle closes is removed from metrics and the bill, and what is found later is credited on a following invoice.