Design an Ad Click Aggregator

1Clarifying the requirements#

Pin down scope before drawing a single box. The four questions below each kill a different ambiguity, and every answer narrows the design hard. Here is how I would open:

Candidate: When you say "click aggregator," are we counting raw click events, or also doing attribution, like which campaign and creative drove a conversion later?

Interviewer: Just clicks for now. Every click already carries the ad ID. We tie it back to the advertiser and campaign. No downstream conversion modeling.

Candidate: Who reads the aggregated data? A dashboard a human refreshes, or automated bidding systems that need near real time?

Interviewer: Both. Advertisers watch a dashboard that should feel live, say within a minute. And our billing and pacing systems query the same counts. Billing has to be exact at the end of the day.

Candidate: So I have a soft real-time path and a hard correctness path. Can clicks arrive late or duplicated?

Interviewer: Yes to both. Mobile SDKs retry. A click can show up ten minutes late or get sent twice. You cannot overcharge advertisers, and you cannot lose clicks either.

Candidate: Last one. What query shapes? Arbitrary slicing, or a known set?

Interviewer: Known set. Clicks per ad, per campaign, bucketed by minute, hour, and day. Filterable by a few dimensions like country and device type. No "group by anything you feel like."

What those answers just told me:

• The read side is OLAP-style rollups on fixed dimensions, not a general analytics engine.
• "Known set" is what lets me precompute tables instead of building a query planner. "Arbitrary slicing" would have me designing a columnar warehouse, a completely different answer.
• "Late and duplicated, and you cannot overcharge" is the interviewer handing me the two theses: idempotency and the streaming-versus-batch split.

Functional requirements

• Ingest click events at very high volume, each tagged with an ad ID, timestamp, and a handful of dimensions.
• Aggregate clicks into time buckets (minute, hour, day) per ad and per campaign.
• Serve queries: "clicks for ad X over the last N minutes," sliceable by country and device.
• Near real-time freshness for the dashboard, roughly one minute.
• Exact, reconciled daily totals for billing.

Non-functional requirements

• Scale to around 500k clicks/sec at peak.
• Ingestion must not drop events, even when a downstream stage is slow.
• Idempotent: duplicate clicks counted once, late clicks still land in the right bucket.
• Query latency under about 200 ms for the dashboard.
• Multi-region, because ads serve globally and clicks happen everywhere.

2Back-of-the-envelope estimation#

Get the scale in your head, because it decides the architecture. I do this math out loud, because the design falls out of exactly one shrink, and I want the interviewer to watch me find it.

Clicks and traffic.

• 10 million active ads, platform doing about 150k clicks/sec on average.
• Peak is spiky (a Super Bowl ad, Black Friday), so design for 500,000 clicks/sec.
• Size for the average and the spike takes you down at the worst possible moment.

Write QPS (the number that shapes everything).

• 500k/sec of counted, correct data at the top.
• No single database swallows that without help. A well-tuned Postgres tops out in the low tens of thousands of writes/sec.
• We are an order of magnitude past that, which is why the log-first design below is not optional.

Event size.

• ad ID (8 bytes), timestamp (8), user/device hash (16), country (2), device (1), plus framing.
• About 100 bytes on the wire, about 200 bytes once we add a click ID for dedup and JSON overhead.
• Round to 200 bytes.

Ingestion bandwidth.

• 500k/sec x 200 bytes = 100 MB/sec, about 800 Mbps.
• A single 10 Gbps NIC could carry the raw bytes, but one node cannot do TLS, validation, and Kafka appends for 500k requests/sec.
• So this is a horizontally scaled stateless tier, never one box.

Raw storage, if we kept every event.

• 500k/sec x 200 bytes x 86,400 sec/day = ~8.6 TB/day, about 3 PB/year.
• Keep raw events in cheap object storage for a retention window (30 to 90 days) for recompute and audits.
• Never serve queries off raw. Scanning 8.6 TB to answer "clicks for ad X last minute" is insane.

Aggregated storage (the number that matters).

• 10M ads, minute buckets, 1,440 minutes/day.
• If 1M ads are active in a given minute: 1M rows/minute x 1,440 = 1.44 billion minute-rows/day.
• At about 50 bytes/row (keys, a counter, a few dimension counts), that is ~72 GB/day at minute granularity.
• Roll minutes into hours after a day, hours into days after a week, and the long-term footprint drops by orders of magnitude.

Read QPS.

• Dashboards plus automated pacing, maybe 50k reads/sec, almost all hitting recent buckets.
• Reads are 10x smaller than writes and overwhelmingly repeat the same "last few minutes" slice.
• A thin Redis layer absorbs most of them, so the read path is cache-friendly and dwarfed by writes.

So the whole problem is three goals: absorb 500k writes/sec safely, aggregate them correctly despite dupes and lateness, and keep the read path cheap. Everything in the rest of this answer serves one of those three.

3Core APIs#

Two surfaces: a fat write endpoint and a thin read endpoint. The asymmetry is deliberate. The write path does the least possible work so it survives peak, and the read path only ever maps to a precomputed table.

POST /v1/clicks
Content-Type: application/json

{
  "click_id": "c_8f2a1b90e4",
  "ad_id": "ad_44921",
  "ts": 1793462400,
  "country": "IN",
  "device": "android"
}

Two choices worth defending here:

• click_id is the dedup key, generated by the client SDK and reused on retries. This is the hinge of the whole correctness story, so I call it out early.
• The response is a fast 202, because the gateway accepts the click into the log and returns before any aggregation happens.

{ "status": "accepted" }

Read side, fixed shapes only:

GET /v1/ads/ad_44921/clicks?granularity=minute&from=1793462400&to=1793466000&country=IN

{
  "ad_id": "ad_44921",
  "granularity": "minute",
  "buckets": [
    { "ts": 1793462400, "clicks": 1204 },
    { "ts": 1793462460, "clicks": 1388 }
  ]
}

The campaign rollup is the same shape with campaign_id. I keep the read API dumb on purpose:

• Every supported query maps to a precomputed table, so the query service never aggregates at read time.
• It does a range scan on a key and returns rows.
• If a product manager wants a new slice later, that is a new precomputed table plus a backfill job, not a new code path in the query service.

4High-level design#

The spine is clicks in through a stateless gateway, written to a durable log immediately, with a stream processor reading that log to maintain rolling counts and a separate serving store answering queries.

Write to a log first, aggregate later.

• The gateway does almost nothing: validate the payload, stamp a server-side receive time, append to Kafka.
• Kafka is the shock absorber. If the stream processor falls behind or crashes, clicks pile up in the log, they do not get dropped.
• This is the single most important choice in the whole design. Durability lives in the log, not in the processor.
• Put aggregation inline in the request handler and a slow DB write at 500k/sec means dropped clicks, which means lost revenue.

Stream processing for the real-time path.

• A Flink-style job (or Kafka Streams) consumes the log, keys events by ad ID plus time bucket, and keeps running counts in windowed state.
• Every few seconds it flushes partial counts to the aggregate store.
• That is what makes the dashboard feel live and comfortably meets the one-minute freshness requirement.

Batch reconciliation for the correctness path.

• The same raw events also land in an object store (an S3-style lake).
• Once a day, a batch job recomputes exact totals from the raw log and overwrites the streaming estimates.
• Streaming gives you fast and approximately right; batch gives you slow and exactly right. Billing reads the reconciled numbers.
• This is the lambda architecture pattern, and for ad billing it is the honest answer: speed for humans, exactness for money.

The aggregate store is a time-series or columnar store.

• Think Druid, ClickHouse, or Cassandra with careful partitioning.
• It is optimized for "sum a counter over a time range for a key," which is literally the only read we do.
• I would not reach for a general relational database, because the access pattern is so narrow that a purpose-built store is both cheaper and faster.

5Data model#

The aggregate table is the heart of it. Partition by ad plus a coarse time bucket so a query touches few partitions. The whole goal of this schema is that every supported query is a tight contiguous range scan, never a full scan.

Table: clicks_by_minute (the live serving table)

• Primary key is (ad_id, bucket_ts, country, device).
• "Ad X, last hour, country IN" hits a tight contiguous slice: 60 minute-rows for one ad, filtered to one country. The opposite of a scan.

Tables: clicks_by_hour and clicks_by_day (same shape, coarser bucket)

• Identical shape with coarser bucket_ts.
• Roll minute into hour into day so historical queries do not scan 1,440 rows per ad per day.
• A "last 30 days" dashboard query hits 30 day-rows, not 43,200 minute-rows.

Table: processed_click_ids (dedup, in the processor's state, not a global DB)

• This never becomes a giant global table. We only dedup within a bounded lateness window (say 15 minutes).
• After that the window closes and the IDs are forgotten.
• That bound is what keeps the seen-set from growing without limit, the failure mode of a naive "store every click ID forever" design.

Table: raw_clicks (the lake, for the batch job)

• Just the original JSON, partitioned by date and hour.
• Partitioning this way means the daily reconciler reads only the partitions it needs instead of the full 90-day history.

6Deep dives#

6.1 Idempotency: counting a click exactly once#

This is where candidates usually hand-wave. Clicks get retried by flaky mobile networks, and a naive increment on every POST overcharges the advertiser.

The fix rides on click_id.

• The client generates it once per real click and reuses it on retries.
• The stream processor keeps a short-lived seen-set keyed by click_id, scoped to the window.
• First time it sees an ID, it counts it and records it. Second time, it drops it.

Two things make this real.

• The seen-set has to be partition-local. I partition Kafka by click_id (or by ad_id, then dedup within partition) so both copies of a duplicate land on the same processor instance. If they split across workers, neither sees the other and dedup fails silently, the worst kind of bug because the counts look plausible but are wrong. Partitioning is the actual hard part here, not the set lookup.
• The seen-set is bounded by the lateness window. I store IDs for 15 minutes, which is how long I promise to accept late events into the correct bucket. After that a straggler is caught by the batch reconciler, and the streaming layer gets to forget. This is the difference between a dedup set that stays a few GB in memory and one that grows to petabytes.

6.2 The hot key problem#

This is the one that separates senior from staff. Click volume is not uniform.

Why one popular ad melts a worker.

• A Super Bowl ad, a flash sale, a viral campaign: one ad_id can suddenly take 50k clicks/sec while most ads take a handful.
• Key the stream by ad_id and every one of those 50k events lands on a single partition. That worker cooks while 200 others idle.
• Because Kafka preserves order within a partition, the backlog on that one partition grows unbounded while everything else is fine.

The technique: key salting plus a two-stage sum.

• Instead of keying by ad_id, key by ad_id plus a random salt from 0 to N-1.
• The hot ad's clicks spread across N partitions, N workers each count their slice.
• A cheap second stage sums the N partial counts back into one number per ad per bucket. It only ever sees N small numbers, so it is trivial.
• If N is 10, a 50k/sec hot ad becomes ten streams of 5k/sec, which any single worker handles easily.

I would not salt every ad.

• Blanket salting doubles work for nothing on the cold 99% and needlessly fans out the dedup state.
• I detect hotness: if an ad's rate crosses a threshold, promote it to the salted path dynamically, or just salt the top-K ads that pacing already flags as high spend.
• This adaptive decision is what I want a candidate to reach for. Blanket salting looks clever and costs you throughput on the common case.

6.3 Late and out-of-order events, and watermarks#

Events do not arrive in timestamp order. A click that happened at 12:00:05 might be processed at 12:00:02's moment, or show up ten minutes later off a backgrounded phone.

Bucket by event time, not processing time.

• A click that happened at noon must count in the noon bucket, so I bucket by the client's ts.
• Sanity-check it: a phone with a wrong clock can claim a click happened in 1970 or next week, so I clamp ts against the server receive time and reject anything implausibly far off.

Watermarks keep the windows honest.

• A watermark of 12:15 means "I believe I have seen all events up to 12:15."
• Windows stay open until the watermark passes their end plus the allowed lateness (my 15-minute grace).
• A click for the noon minute that arrives at 12:10 still updates the noon bucket, because that window is still open.

What about a straggler at 12:30, past the grace period?

• The streaming layer ignores it to keep the real-time numbers from flapping. Reopen old windows forever and the dashboard never settles.
• The batch reconciler, which scans all raw events regardless of arrival time, folds it into the exact daily total.
• The dashboard might be off by a rounding error for a few hours; the bill is never wrong.
• That split is the whole justification for keeping batch around, and the cleanest way to explain why lambda earns its complexity here.

7Bottlenecks and trade-offs#

The log is a single point of pressure.

• Everything funnels through Kafka, so I defend it with hundreds of partitions (each carrying a few thousand writes/sec, comfortable), replication for durability, and generous retention so a slow consumer never loses data.
• If Kafka is unavailable, ingestion is down. I would rather the gateway buffer briefly to local disk and backfill than accept clicks into a void and lose them.
• For money data, losing silently is the worst outcome.

Aggregate store write amplification.

• Minute, hour, and day tables all get updated, but I do not write all three on the hot path.
• The processor writes minute granularity; a lightweight rollup job derives hour and day asynchronously.
• This keeps the write path lean and lets rollups lag without hurting the live dashboard, which only reads minute buckets for recent data anyway.

Streaming versus batch divergence.

• The real-time number and the reconciled number will disagree, sometimes visibly. I treat this as a feature, but I make it explicit.
• The dashboard labels recent buckets "live estimate" and older ones "final."
• Advertisers tolerate a 0.5% wobble on the last few minutes. They do not tolerate a wrong invoice.
• Being honest in the UI about which is which prevents a lot of support tickets.

Multi-region.

• Ingest regionally (low latency for the SDK, local Kafka) and replicate aggregates to a global serving layer.
• I resist a single global Kafka: the cross-region latency and failure coupling are not worth it, and one region's outage should not stall another's ingestion.
• Regional ingest, regional pre-aggregation, then ship the small aggregated results to a central store. You move kilobytes of counts, not terabytes of raw clicks, across oceans.

Cost of exactness.

• I could drop batch and lean fully on streaming with strong exactly-once semantics (Flink checkpointing plus transactional sinks). Tempting and cleaner.
• I keep batch anyway for one reason: auditability. "We recomputed it from the immutable raw log" holds up when finance or an advertiser disputes a number.
• A streaming-only system asks you to trust the pipeline's internal state, and for billing I would rather have the receipts.

Cardinality creep.

• If someone later asks for "slice by any of 40 dimensions," the precomputed-table approach explodes combinatorially. Every new dimension multiplies the rows.
• I would push back hard, or move that specific need to a columnar engine like ClickHouse or Druid that does fast ad-hoc rollups, and keep precomputed tables for the known high-traffic queries.
• Knowing where to draw that line is the trade-off I care most about.

The write path stays dumb so it can survive the spike; all the cleverness lives downstream where it can take its time.

8Summary#

The decisions that matter:

• Durability in the log, not the processor. Append every click to Kafka first, aggregate second. The log absorbs spikes and failures, and is the single most important call.
• Lambda split. Streaming for the live dashboard (fast, approximate), batch reconciliation from the raw lake for billing (slow, exact). Label which is which in the UI.
• Idempotency via client click_id plus a window-scoped seen-set, with Kafka partitioned so duplicates co-locate on one worker.
• Hot keys solved with salting plus a two-stage sum, applied only to the ads that are actually hot, never blanket.
• Event-time windowing with watermarks so late clicks land in the right bucket, and a grace period beyond which batch takes over.
• Precomputed rollup tables keyed by ad and time bucket, because the query set is fixed and small.
• Out of scope by choice: conversion attribution, fraud detection, arbitrary slicing. Naming them keeps me honest about what 45 minutes covered.

The thing to internalize: this problem is not about counting. Anyone can increment a number. It is about counting correctly under duplication, lateness, and skew, while keeping the write path dumb enough to survive 500k/sec. Nail those three and the rest is wiring.

For how specific companies push on this one, see our related guides on streaming systems and real-time analytics interviews.