Cruxly › System design › Design Exercises

Ad Click Aggregation

counting billions of events exactly once when the network lies

Prompt: design a pipeline that counts ad clicks in near-real-time — advertisers see click counts per campaign updated within a few seconds, at a rate of hundreds of thousands of click events per second, and a click must be counted exactly once even though the network duplicates and reorders events.

40 minutes first. This is a streaming/counting problem, and the entire exercise lives in the gap between "at least once" and "exactly once."

Applying the four moves: Scope — count clicks per campaign, visible within seconds, exactly once despite duplicates and reordering; fraud detection out. Estimate — hundreds of thousands of events per second, which rules out a database write per click and forces aggregation in a stream. Sketch — an event log, a stream processor aggregating per campaign per time window, and a store for the aggregates. Deep dive — "exactly once" is really "deduplicate by event ID, effectively once," and late or reordered events need a watermark policy that decides when a window is closed — and what happens to an event that arrives after.

Scope and why exactly-once delivery is the wrong goal

In scope: ingest click events, deduplicate, aggregate counts per campaign per time window, serve near-real-time counts to an advertiser dashboard. Out of scope: fraud detection (a related, much harder classification problem — name it as out of scope rather than folding it in), billing reconciliation, ad targeting/serving itself.

Estimate. Say 500,000 clicks/second at peak, each event roughly 200 bytes (ad ID, campaign ID, user ID, timestamp, metadata) — 100 MB/s of raw ingest, which immediately rules out any design where a single process or single database handles the write path directly.

The framing that matters most: true "exactly-once delivery" across a network is not achievable — a client can retry a click event because it never received the ack, and the server has no way to distinguish "this is a retry of an event I already processed" from "this is a genuinely new, coincidentally similar event" without extra information. The tractable goal is exactly-once processing effect — at-least-once delivery, paired with deduplication at the processing stage, achieves the same externally-visible result (each real click counted once) without claiming an impossible delivery guarantee. This reframing is the single most important thing to say out loud in this exercise.

The pipeline: ingest, dedupe, aggregate, serve

Partitioning key choice determines whether aggregation is a local, single-partition operation or a distributed merge — campaign ID makes it local.

Partition the queue by campaign ID, not randomly. Every click for a given campaign lands on the same partition, which means the stream processor consuming that partition can maintain that campaign's running count entirely in local memory/state, with no cross-partition coordination needed to produce a correct per-campaign total. Random partitioning would spread one campaign's events across every partition, turning every count query into a scatter-gather across the whole fleet.

Deduplication needs a stable idempotency key generated by the client at the moment of the click — not a server-generated ID, because if the client retries due to a lost ack, it must resend the same key for the dedup step to recognize it as a repeat. A key built from (user_id, ad_id, timestamp_bucketed_to_second) or a client-generated UUID stored with the click both work; the requirement is that a retried send reproduces the identical key. This is the same idempotency-key principle used for payments and notifications elsewhere in this track, applied to a much higher-throughput, lower-stakes event.

Windowed aggregation, and the late-event problem

Counts are aggregated into fixed time windows (say, 10-second buckets) per campaign, incremented in the stream processor's local state and periodically flushed to the counts store — flushing every second or two, not per-event, batches the store writes down from 500,000/s to a manageable rate.

Late-arriving events are the genuinely hard part. A click event delayed by a slow mobile network might arrive 30 seconds after its timestamp, by which point its 10-second window has already been flushed and, from the dashboard's point of view, finalized. Three honest options:

Watermarking — declare a window "final" only after waiting some grace period (say, 60 seconds) past its end, accepting a small reporting delay in exchange for including most late events.
Accept a bounded correction window — publish counts as provisional for the first few minutes, then final; the dashboard shows "~12,450 clicks (finalizing)" and updates once, rather than silently drifting.
Drop events later than the grace period, and say so explicitly — a small, bounded undercount is the accepted cost, disclosed rather than hidden.

Every real system picks one of these and documents which — presenting counts as if they were both real-time and perfectly final is the wrong answer, because it's not actually true at this scale, and pretending otherwise is exactly the kind of unstated tradeoff this track's exercises are designed to surface.

Failure modes and scaling the counts store

Stream processor crashes mid-window. Its local in-memory count for the current, unflushed window is lost. Mitigate with periodic checkpointing of partial window state (to the same queue's committed offset plus a snapshot) so a restarted processor resumes from the last checkpoint and re-processes only the small window of events since — a bounded, cheap re-derivation, not a full reprocess.

The counts store becomes a hot spot for a viral campaign. One campaign ID receiving a disproportionate share of all clicks concentrates writes on one partition/key — the same hot-key problem covered in this track's caching and sharding material. A per-campaign write-behind buffer that locally batches increments before touching the shared store absorbs this, the same batching idea used for the rate limiter's counter design elsewhere in this track.

Exact counts versus approximate counts is itself a choice. For a dashboard showing "1.2M clicks," an approximate count (HyperLogLog-style, or simply a slightly-lagging aggregate) is often good enough and far cheaper than maintaining an exact count under high write concurrency — raising this tradeoff explicitly, rather than assuming exactness is always required, is a strong signal in this exercise specifically because ad metrics are one of the few domains where advertisers do tolerate "approximately right, always available" over "exactly right, sometimes delayed."

How to say it in the interview

"Every part of this pipeline — the client, the network, the message queue — will duplicate or reorder events at least once at this scale, so the design has to make counting idempotent rather than trying to prevent duplicates from ever happening, which is not achievable."

Checklist

Reframed the goal from impossible exactly-once delivery to achievable exactly-once processing effect
Partitioned the queue by campaign ID so aggregation is local, not a cross-partition merge
Required the idempotency key to be client-generated and stable across retries
Batched counts-store writes by flushing per time bucket instead of per event
Named late-arriving events, not steady-state throughput, as the genuinely hard part of windowed aggregation
Picked one late-event policy explicitly — watermarking, provisional-then-final, or disclosed bounded drop
Checkpointed partial window state so a crashed stream processor re-derives only a small bounded window
Identified viral-campaign hot-keying as the same hot-key problem covered in caching and sharding
Raised approximate counting as a legitimate, often-preferable option for this specific domain

Practice this interactively