16. Ad Click Aggregation Advanced + High-Volume Stream Processing

A. Realistic Interview Prompt

Every prior problem in this document that deals with high write volume (News Feed's fanout, Rate Limiter's counters) still, underneath, imagines a single logical event landing as a single logical write. This problem is the first one where that assumption itself has to be abandoned: at the volumes an ad network actually operates at, there is no database that will accept one row per click and still answer "how many clicks did ad X get in the last minute" fast enough to render a dashboard — and there is no way to count the number of distinct people who clicked an ad without an amount of memory that grows without bound, if you insist on an exact answer. The load-bearing decision this design is built around is: land every click into a durable, append-only log (Kafka) rather than writing it directly to a database; aggregate that log continuously through windowed stream processing rather than running periodic COUNT(*) queries over raw rows; deduplicate the inevitable stream of duplicate click events using an idempotency key established at click-generation time; and, for the specific question of how many distinct users clicked an ad, stop insisting on an exact answer altogether and use a probabilistic structure (HyperLogLog) that trades a small, known error rate for a fixed, tiny memory footprint per ad, no matter how large the true count actually is.

B. Difficulty

Advanced — the individual building blocks (a log-based queue, a stream-processing window, a probabilistic counting structure) are each well-documented and not exotic in isolation, but a candidate who hasn't internalized just how much a naive per-event database write costs at this specific volume will reach for "just write it to Postgres and shard it" and burn most of the interview discovering, step by step, exactly why that doesn't work. The real signal here is recognizing, unprompted, that this is fundamentally a streaming/aggregation problem rather than a storage problem, and being comfortable stating out loud that the system's headline numbers — click counts, unique reach — are deliberately approximate, not as a shortcut but as the correct engineering answer at this scale.

C. What This Problem Tests

• Recognizing when raw event volume has crossed the threshold where a database is no longer the right place to absorb writes at all, and reaching for a durable log (Prerequisites G) as the ingestion point instead
• Distinguishing a per-event write-and-query architecture from a windowed stream-aggregation architecture, and knowing precisely why the former's cost grows with raw event volume while the latter's cost grows with the (far smaller) number of distinct aggregation keys
• Applying idempotency (Prerequisites O, Patterns Playbook E) to a context where the duplicate isn't a retried API call but a duplicate real-world event arriving through an at-least-once pipeline — and recognizing it's the identical underlying problem wearing different clothes
• Knowing when an exact answer is worth its cost (a billable click count) versus when a probabilistic approximation is the objectively correct engineering trade (a unique-reach estimate) — and being able to name a concrete probabilistic structure (HyperLogLog) and explain, precisely, how it differs from the other probabilistic structure this document already covers (a Bloom filter, Prerequisites M)
• Reasoning about event time versus processing time, and designing a system that behaves correctly — meaning: predictably, not perfectly — in the presence of events that legitimately arrive late

D. Requirements

Functional

1. Every ad click is recorded, associated with the specific ad, campaign, and advertiser it belongs to
2. An advertiser can view near-real-time aggregate click counts for their campaigns, broken down per ad and per time bucket (minute/hour/day)
3. An advertiser can view an estimate of unique reach — the number of distinct users who clicked a given ad — for billing-adjacent reporting, separate from raw click volume
4. A click that's billable (counts toward an advertiser's spend) is counted exactly once, even if the underlying event is delivered to the system more than once
5. An eventual, fully-exact reconciled count is available for each time bucket after some bounded delay, for billing disputes and audits

Non-Functional

• Extremely high write throughput — the ingestion path must sustain hundreds of thousands of click events per second at peak without falling behind or dropping events
• Near-real-time aggregate visibility — an advertiser's dashboard should reflect a click within roughly a minute of it happening, not require a nightly batch job to appear at all
• Billing-grade correctness for the exact count, on a delay — the number an advertiser is actually charged against must ultimately be exact and auditable, even though the number they see live is explicitly provisional
• Resilient to one ad's traffic spiking disproportionately — a single viral ad must not be able to overwhelm the shard or partition it happens to land on and degrade every other ad sharing that resource
• Resistant to inflated counts from fraudulent or bot-driven clicks, close enough to ingestion time that fraud doesn't silently drain an advertiser's daily budget before it's caught

Out of scope: ad serving/targeting/auction logic (assumed to already exist upstream and simply hand this system a rendered ad's click-through event); the advertiser-facing billing/invoicing system itself (this design produces the click counts that system consumes as an input); impression tracking (a related but separate, even higher-volume pipeline sharing much of this same architecture).

E. Clarifying Questions

F. Assumptions

• Confirmed: record every click, provide near-real-time per-ad/per-campaign aggregate dashboards, produce an eventual exact count for billing, and never let a naively-retried or duplicated event inflate a billable count.
• Assumption: only the eventually-reconciled batch count needs to be exact; the live dashboard number is explicitly provisional, and the business already understands and accepts that framing (E's clarifying question 1) — this is what licenses windowed streaming aggregation as the primary path rather than forcing a strongly-consistent counter into the hot path.
• Assumption: a one-to-two-minute end-to-end latency from click to dashboard visibility is acceptable (E's clarifying question 2) — generous enough to allow a real windowing/watermark scheme (O, Q), tight enough to still feel "live" to an advertiser watching spend in real time.
• Assumption: unique-reach is a genuine, first-class requirement, not a hypothetical extension (E's clarifying question 3) — this is what makes HyperLogLog (P) a load-bearing part of the design rather than a footnote.
• Assumption: fraud/bot filtering is in scope, at least at the heuristic level (E's clarifying question 4) — a full ML-based fraud model is explicitly out of scope, but the pipeline stage and its placement (S) are not.
• Assumption: a 30-day dispute/audit window (E's clarifying question 5) — raw events are retained in a replayable form for at least that long (G, K).

G. Capacity Estimation

H. Core Entities

I. API Design

-- public, hit directly by a user's browser/device when they click a rendered ad:
GET  /click?ad_id=<id>&campaign_id=<id>&click_id=<generated-at-ad-serve-time>&redirect=<landing_url>
     -> 302 redirect to the advertiser's landing page, near-instantly (M, L)
     (as a side effect, produces one ClickEvent onto the ingestion log — never blocks the redirect on it)
-- advertiser-facing reporting:
GET  /campaigns/{campaign_id}/stats?window=1h&from=...&to=...
     -> { ad_id, click_count (provisional, V), unique_reach_estimate (P), spend, is_final }
GET  /campaigns/{campaign_id}/stats/exact?window=1h&from=...&to=...
     -> { ad_id, exact_click_count, reconciled_at }              (only available once reconciliation (V) has run for that window)
-- internal, cluster-facing:
(stream processor) consumes ClickEvent topic -> dedup (N) -> windowed aggregate (O) -> WindowedRollup   (K)
(fraud worker) consumes the same ClickEvent topic in parallel, tags/suppresses suspicious clicks close to ingestion (S)
(reconciliation job) periodic: recompute exact counts from the durable log/archive, diff against WindowedRollup (V)

Why the public click endpoint has no request body and does the absolute minimum before redirecting: this is the one endpoint in the entire system sitting directly in a real user's page-load/navigation path — every extra millisecond here is a millisecond added to a real person waiting for a page to load, which is exactly the same "keep expensive work off the hot path" reasoning named generally in Patterns Playbook C. Producing to the log (M) is a fast, fire-and-forget append; every heavier step — dedup, aggregation, fraud scoring — happens entirely downstream, off this request's critical path.

Why there are two separate stats endpoints, not one: the plain /stats endpoint returns whatever the streaming pipeline currently believes, refreshed roughly every window (V) — fast, always available, explicitly not final. /stats/exact only ever returns a value once the slower batch reconciliation (V) has actually finished recomputing that specific window from the durable log — the same two-speed shape Payment System's saga-versus-reconciliation split uses (P15), reused here for a click count instead of a ledger balance.

J. Basic / Naive High-Level Design (V1)

The simplest thing that satisfies the functional requirement in isolation: the click-tracking redirect endpoint, on every click, runs INSERT INTO clicks (ad_id, campaign_id, user_id, ts) VALUES (...) directly against a relational database, synchronously, before issuing the 302 redirect. An advertiser's dashboard, to show "clicks in the last hour," runs SELECT ad_id, COUNT(*) FROM clicks WHERE campaign_id = ? AND ts >= NOW() - INTERVAL '1 hour' GROUP BY ad_id directly against that same table, on demand, every time the dashboard refreshes.

V1 — a direct, synchronous per-click insert, with reporting implemented as ad hoc aggregate queries over the raw table.

Why this collapses under this problem's actual load, in the two ways that matter most: first, the write side simply cannot keep up — G's arithmetic isn't a rough approximation, it's the concrete reason a single relational node is already short of the platform's average click rate before any peak multiplier is applied at all, and no amount of "just add read replicas" helps, because replicas don't add write capacity. Sharding the writes helps to a point, but every shard boundary now has to somehow still answer "give me the total across all ads in this campaign" without re-introducing a fan-out query across every shard on every dashboard refresh. Second, and just as fatal even if the write problem were solved: COUNT(*)/GROUP BY over a raw table that grows by tens of millions of rows a minute means every dashboard refresh re-scans an ever-larger slice of raw data to answer the exact same question it answered a few seconds ago — the query gets slower over the course of the day, not just at scale, because "the last hour" itself contains more rows every single time it's asked. Neither flaw is fixable by throwing more database hardware at it; both point at the same root cause — this design treats aggregation as something computed fresh, from scratch, on every read, over ever-growing raw data, instead of computed once, incrementally, as data arrives.

K. Data Model & Store Choice

The raw click log lives in Kafka, not in any database: every ClickEvent (H) is appended to a durable, partitioned Kafka topic (Prerequisites G), partitioned by ad_id (with the hot-key exception handled separately, R). This is the direct structural fix for V1's write-throughput flaw (J) — appending to a log's sequential, ordered partition is a fundamentally cheaper operation than a random-access, indexed relational insert, and a log's whole design point is absorbing exactly this kind of high-volume, append-only, rarely-if-ever-updated event stream. Raw events are retained "hot" in Kafka for roughly two weeks (G), then archived to cold object storage (Prerequisites I) for the remainder of the ~30-day dispute window (F) — a metadata/blob-style split in spirit, though here what moves to cold storage is the raw event stream itself, not a large binary payload.

The rollup store is a wide-column/OLAP-oriented store, not the same relational table V1 reached for: WindowedRollup rows (H) are written by the stream-processing layer (O), keyed by (ad_id, window_start), into a store optimized for exactly this access pattern — range-scan a specific key's time series, not join across arbitrary tables (Prerequisites B's wide-column category, e.g. Cassandra-style, or a purpose-built time-series/OLAP columnar engine). This is the direct fix for V1's second flaw: a dashboard query now reads a small number of already-computed rollup rows for the requested window, never re-scanning raw events, because the aggregation work already happened once, incrementally, as the stream flowed through.

The dedup/idempotency window store is a fast, TTL'd keyed store, deliberately small and short-lived: DedupRecords (H, N) live in the stream processor's own keyed state (backed by something like RocksDB) or an external fast store such as Redis, bounded to the dedup window (~10 minutes, G) rather than growing with total event volume — the same TTL-bounding discipline Payment System's idempotency store uses (P15-M), reused here for a duplicate-event window instead of a client-retry window.

HyperLogLog sketches are stored as compact binary blobs alongside each rollup row, or natively in Redis: a hll_sketch field (H) attached to each WindowedRollup — a fixed handful of bytes to a fixed ~12KB, never a growing set of raw user IDs (P).

L. End-to-End Flow — A User Clicks an Ad: Ingestion, Dedup, and Windowed Aggregation into a Queryable Rollup

The redirect to the user completes before any of the aggregation work even starts — everything from the Kafka produce onward is fully decoupled from the request the user is actually waiting on.

M. Deep Dive 1 — Ingesting at Scale via a Log

Requirement this addresses: the extremely-high-write-throughput NFR (D) — the direct fix for V1's fatal write-side flaw (J).

Why a log, specifically, and not just "a faster database": Prerequisites G draws the general distinction — a traditional queue deletes a message once consumed, while a log-based broker like Kafka retains every event and lets multiple independent consumer groups each read the full stream at their own pace. This system needs exactly that fan-out: the windowed-aggregation consumer (O), the fraud-detection consumer (S), and the cold-storage archival consumer (K) all need to read the identical stream of clicks, independently, without the click-redirect endpoint needing to know any of them exist or coordinate delivery to each. A traditional single-consumer queue would force either one consumer to do all three jobs, or the producer to write to three separate queues — a log lets the producer write once and stay entirely ignorant of how many downstream systems are reading it.

Why appending to a log is cheaper than a relational insert at this specific volume: a Kafka partition is, mechanically, a sequential append to the end of a file — no index to update, no row-level lock to acquire, no page to potentially split. A relational insert with even a couple of indexes pays for random-access index maintenance on every single write; at tens of thousands of writes/sec sustained, and hundreds of thousands at peak (G), that per-write overhead is exactly what makes the relational approach infeasible and the log-append approach tractable.

Why the click-redirect endpoint's produce call is fire-and-forget, not a blocking, fully-acknowledged write: the endpoint's only real job (I) is getting the user redirected quickly — it needs the produce call to be durable enough to survive a normal broker failure (acknowledged by at least the partition leader) but does not need to wait for the aggregation pipeline downstream to do anything at all before responding. This is the same "keep expensive work off the hot path" reasoning as Patterns Playbook C, applied to a redirect instead of a job submission.

Partitioning strategy: partitioned by ad_id by default, which keeps every click for a given ad in delivery order on one partition (the ordering guarantee Kafka only makes within a partition, per Prerequisites G) — a property the windowed-aggregation stage (O) benefits from, though it does not strictly require it, since the watermark mechanism (Q) is designed to tolerate some disorder regardless. This default partitioning is exactly what breaks down for one specific case, covered on its own (R).

N. Deep Dive 2 — Deduplication of Duplicate Click Events

Requirement this addresses: the exactly-once-billable-count NFR (D) — duplicate click events are not a rare edge case here, they're an expected, routine byproduct of at-least-once delivery (Prerequisites G) compounded by real client-side behavior: a user double-clicking, a flaky mobile connection causing a browser to silently retry the same GET request, or a bot deliberately re-firing the same tracking URL.

The idempotency key, established at ad-serve time, not at click time: a click_id is generated once, when the ad is actually rendered to the user (embedded directly into the tracking URL the ad itself links through), so that a double-fire of the exact same click — whether from a genuine double-click or a network-level retry — carries the identical click_id both times. This is precisely the same idempotency-key mechanism named generally in Prerequisites O and cataloged across three other domains in Patterns Playbook E (Chat's client_msg_id, Ticketmaster's payment key, Job Scheduler's execution key) — the only thing different here is that the "client" retrying isn't an API caller reacting to a lost response, it's a browser or bot reacting to normal, everyday network flakiness.

Bloom filter as a cheap pre-filter in front of the exact dedup store — and why the direction of the check matters: checking every incoming click_id against an exact dedup store (K) for every single click is itself real work at this volume. A Bloom filter (Prerequisites M) sitting in front of that store answers the cheap, common case fast: if the filter says "definitely not seen," that's a guaranteed-correct answer — the click is genuinely novel, no exact-store lookup needed at all. Only when the filter says "probably seen" does the click fall through to the heavier exact check, to rule out a false positive before treating it as a real duplicate. Because the overwhelming majority of clicks in any real workload are genuinely novel, this ordering means the expensive exact lookup is paid only for the minority of cases that actually need it — and, critically, the filter's one-directional error (false positives only, never false negatives, Prerequisites M) means this optimization can never itself cause a real click to be wrongly discarded as a duplicate.

Why the dedup window is bounded, not permanent: a DedupRecord only needs to live as long as a duplicate could realistically still arrive — roughly ten minutes (F, G) comfortably covers a retried request or a delayed redelivery, without growing the dedup store's size in proportion to the platform's total daily click volume. This mirrors the exact TTL trade-off Payment System's idempotency store makes for its own, differently-scoped window (P15-M).

O. Deep Dive 3 — Batched / Windowed Aggregation

Requirement this addresses: the near-real-time-visibility NFR (D) — the direct fix for V1's second flaw (J), replacing "re-scan raw rows on every query" with "aggregate incrementally, once, as data arrives."

Tumbling windows, the primary aggregation unit: a stateful stream processor (conceptually, a Flink- or Spark-Streaming-style engine) groups deduplicated events into fixed, non-overlapping one-minute buckets keyed by (ad_id, window_start) — every click belongs to exactly one tumbling window, which is what makes higher-level rollups (per-hour, per-day) a simple matter of summing already-computed child windows rather than ever re-touching raw events. The processor maintains a running count (and a running HyperLogLog sketch, P) as its keyed state for each currently-open window, and emits a finalized WindowedRollup row once that window closes (governed by the watermark, Q).

Sliding windows, for the dashboard's "trailing N minutes" display: a dashboard's "clicks in the last 5 minutes, updated every 30 seconds" view is a sliding window — overlapping, recomputed on a shorter interval than its own width. Rather than maintaining a second, separate streaming aggregation for this, it's computed on read, on top of the already-finalized one-minute tumbling rollups (summing the last five one-minute buckets) — cheap precisely because the expensive part, the actual per-click aggregation, already happened once in the tumbling layer.

The one detail that breaks if it's missed: summing works for counts, but not for HLL sketches. Rolling a per-minute count up into a per-hour count is a simple sum of 60 child values. Rolling a per-minute unique-reach estimate up into a per-hour estimate is not a sum — summing 60 minutes' distinct-user counts would badly overcount anyone who clicked in more than one of those minutes. HLL sketches instead merge (a register-wise max across the 60 sketches, equivalent to computing the union of the underlying sets), which is exactly the property that makes HLL sketches composable across time and across partitions in the first place (P) — a distinction worth stating explicitly, because it's the single most common mistake a candidate makes the first time they reach for "just roll it up the same way as the count."

P. Deep Dive 4 — Approximate Counting at Extreme Scale

Requirement this addresses: the unique-reach functional requirement (D) — and the second half of this design's central bet: some numbers this system reports are not worth computing exactly, because the exact version is prohibitively expensive and the approximate version is, for the actual business purpose, just as useful.

Why exact unique counting doesn't scale here: computing the true number of distinct users who clicked a given ad requires remembering every distinct user ID ever seen for that ad — a full hash set, growing linearly with true cardinality, per ad, per window. At tens of millions of ads and per-minute granularity (G), that's simply not a viable amount of memory to hold, and it doesn't compress: a hash set of a million distinct IDs is a million distinct IDs' worth of memory, no matter how it's stored.

HyperLogLog, the fix: a HyperLogLog sketch estimates the number of distinct elements added to it using a small, fixed amount of memory — a dense representation uses roughly 12KB regardless of whether the true distinct count is a hundred or a hundred million, with a standard error around 0.8-2% depending on configuration (AD). Adding an element hashes it and updates a small number of registers based on patterns in the hash's bits; estimating cardinality reads those registers back through a formula, never the raw elements themselves — which is exactly why the memory footprint never grows with the true count, only the accuracy's confidence interval narrows or widens with configuration choices made up front.

Why sketches can be merged, and why that's the whole point: a per-minute HLL sketch and a different per-minute HLL sketch for the same ad can be merged into a single sketch representing their union — a per-hour reach estimate is the merge of that hour's 60 per-minute sketches (O), and a platform-wide reach estimate for an ad is the merge of every shard's local sketch for it, all without ever needing to bring the underlying raw user IDs back together in one place.

How this differs from a Bloom filter — the two probabilistic structures answer genuinely different questions: a Bloom filter (Prerequisites M, and this design's own dedup layer, N) answers "have I seen this specific element before" — a membership question about one item, with no false negatives and a small false-positive rate, and it fundamentally cannot tell you how many distinct elements it has seen in total. HyperLogLog answers the opposite question: "how many distinct elements have I seen overall" — a cardinality question about the whole set, and it fundamentally cannot tell you whether any one specific element was among them; there's no way to ask an HLL sketch "was user_42's click already counted," the way a Bloom filter's entire purpose is answering exactly that. This design needs both, for two entirely different jobs: the Bloom filter cheapens the dedup check (N); the HLL sketch estimates reach (P) — and conflating the two, or reaching for one where the problem actually calls for the other, is a common, avoidable mistake.

Q. Deep Dive 5 — Handling Late-Arriving / Out-of-Order Events

Requirement this addresses: the near-real-time-visibility NFR (D), specifically the honest admission that "near-real-time" also means "not every event arrives on time," and the design needs a defined, bounded answer for the ones that don't.

Event time versus processing time: a click's event time is when it actually happened, according to a timestamp assigned as close to the user's action as possible; its processing time is whenever the stream processor actually gets around to handling it. These two can legitimately diverge — network delay, a mobile device that buffers clicks while offline and replays them once reconnected, or ordinary multi-hop pipeline lag can all mean an event's processing time trails its event time by anywhere from milliseconds to, occasionally, minutes.

Watermarks — the processor's own evolving estimate of "how caught up are we, really": a watermark is a continuously-advancing threshold, computed from the event times actually observed so far, representing the processor's belief that it has now seen every event with an event time earlier than the watermark (allowing for some configured maximum expected disorder). A given tumbling window (say, 14:32:00-14:33:00) closes and emits its finalized WindowedRollup not the instant its wall-clock end arrives, but once the watermark passes that window's end plus a configured allowed-lateness buffer (e.g., two extra minutes) — giving legitimately-delayed events a real, bounded chance to still land in the correct bucket before it's considered final.

What happens to an event that's later than even that: a click whose event time falls inside an already-closed, already-emitted window, but which only arrives after the allowed-lateness buffer has also passed, is genuinely too late for the window's live-emitted rollup. Rather than silently mutating that already-published WindowedRollup row in place, it's routed to a side stream and applied as an explicit LateCorrection record (H) — a small, attributable adjustment layered on top of the original, never a quiet rewrite of a number a dashboard already displayed. This is the same "never silently mutate a financial fact, write a new attributable adjustment instead" discipline Payment System's ledger uses for corrections (P15-N, P15-Q), reapplied here to a click count instead of a balance.

Why this bounded-lateness gap is exactly what the later reconciliation job exists to close systematically: the watermark and allowed-lateness window are deliberately finite — extending them indefinitely to catch every conceivable straggler would mean windows never close and dashboards never show a "final" number at all. The batch reconciliation job (V, cross-referencing P15-Q's identical role in a different domain) runs hours later against the full durable log, with no lateness bound at all, and is what ultimately catches anything the streaming layer's necessarily-finite patience for stragglers still missed.

R. Deep Dive 6 — The Hot Ad Problem

Requirement this addresses: the resilience-to-a-single-spike NFR (D) — a distinct scaling problem from the platform-wide peak G already accounts for: one specific ad's traffic becoming disproportionate to every other ad's, concentrated on whatever single partition that ad's ad_id happens to hash to.

Why the default partitioning scheme (M) breaks down for exactly this case: partitioning by ad_id keeps a given ad's clicks ordered and together, which is normally a feature — but it also means one viral ad's entire click volume lands on a single Kafka partition, read by a single consumer instance within its consumer group. This is structurally the identical shape as two problems already solved elsewhere in this document for different resources: Rate Limiter's hot-key/single-abusive-client problem (P2-Q), where one client's traffic can't be spread across shards without breaking per-client atomicity, and News Feed's celebrity problem (P3-N), where one account's follower count skews a fanout scheme built assuming a roughly uniform distribution. All three are the same underlying fact — a partitioning scheme chosen for the average case has no defense against one key's traffic being wildly non-average.

The fix — salt the hot key, merge the pieces back downstream: once an ad is detected as disproportionately hot (a lightweight streaming heavy-hitter estimate is enough — exact top-k tracking isn't needed to make this call), its clicks are salted with a random shard suffix before being keyed into Kafka (ad_id#0 through ad_id#N instead of a single ad_id), fanning that one ad's write volume across N partitions instead of one — the direct structural echo of Rate Limiter's own hot-key fan-out mitigation (P2-Q). The aggregation layer (O) then merges the N shards' partial results back into one logical rollup for that ad: click counts sum trivially across the salted shards, and — because HyperLogLog sketches merge rather than sum (O, P) — the reach estimate recombines just as cleanly, with no special-casing needed for the fact that the same ad's data briefly lived on multiple partitions.

Why this fix is applied selectively, not universally: salting every ad's key by default would spread ordinary, non-hot ads' small click volumes across more partitions than they need, adding merge overhead for no benefit. Detecting hotness first and only paying the fan-out cost for the small number of ads that actually need it — the same selective-cost reasoning News Feed's hybrid fanout applies to celebrity accounts specifically, rather than to every account (P3) — keeps the common case simple.

S. Deep Dive 7 — Fraud / Bot Click Filtering

Requirement this addresses: the fraud-resistance NFR (D) — protecting an advertiser's spend from clicks that were never a genuine expression of interest in their ad.

Representative heuristics: click velocity from a single IP or device exceeding a threshold within a short window; a click with no corresponding ad impression ever having been served to that user; traffic originating from known datacenter IP ranges or exhibiting headless-browser signatures; mechanically regular click-timing patterns inconsistent with normal human variance; a single device clicking on every ad it's ever shown, regardless of relevance. None of these individually proves fraud with certainty — each contributes a confidence signal that, combined, determines whether a click is flagged, suppressed from billing, or simply allowed through with a lower confidence tag for later review.

Why this must run close to ingestion time, not after billing has already run: an advertiser's campaign spend and daily budget are decremented against near-real-time click counts (D, V) — the whole point of the near-real-time dashboard is that an advertiser can watch their budget deplete as clicks happen. If fraud filtering only ran as a nightly batch pass, fraudulent clicks would already have both charged the advertiser and consumed real budget/inventory-pacing decisions for that day before anyone caught them — and refunding the charge afterward doesn't undo the fact that the campaign may have been paused early, or that a legitimate click was crowded out of that day's budget by a fraudulent one that arrived first. This is a mirror image of Payment System's own "slow correct answer beats a fast wrong one" reasoning (P15): there, the fix is to slow down and verify before acting; here, verification has to happen fast enough to still be before the money-affecting decision, because by the time a fully-certain, slow answer is available, the budget has already moved.

Where this lives in the pipeline: a dedicated fraud-scoring consumer reads the same Kafka topic (M) in parallel with the aggregation consumer (O) — not as a gate the click has to pass before ever being logged (that would risk losing a genuinely legitimate click to a slow or overzealous check), but as an independent stream that tags or suppresses clicks from rollups quickly enough to matter for same-day budget pacing, while the raw event itself remains durably logged either way for later audit.

T. Full Solution at Scale — Final Architecture

Two speeds, deliberately: the streaming path (top) trades a small, bounded error for near-real-time visibility; the batch path (bottom) trades latency for an eventual, fully-exact number.

U. Design Evolution

What stays stable throughout: the small, click-through-redirect contract from I — every later refinement changes how a click's data flows and gets aggregated downstream, never the basic shape of what a user's browser actually hits or how quickly it gets redirected.

V. Consistency Model

This design sits at almost the opposite end of this document's consistency spectrum from Payment System (P15-V) — worth stating that contrast explicitly, because both problems' central lesson only lands fully once compared against each other. Payment System scopes strong consistency down to the smallest possible island (a single ledger commit) precisely because a financial fact being briefly wrong is unacceptable, no matter how narrow the window. This system does the reverse: it deliberately, explicitly treats its headline, live-facing numbers as approximate and provisional, on the honest premise (Prerequisites L's PACELC framing, taken to its logical extreme) that a click dashboard which is 30 seconds stale, or off by a fraction of a percent on a reach estimate, has lost nothing a real advertiser actually needs from it in the moment.

The two-speed shape, made explicit: the live dashboard's numbers (V's provisional WindowedRollup counts, T) are eventually consistent in the ordinary sense — they converge toward correct as stragglers and corrections (Q) land — and are additionally, deliberately approximate in the statistical sense for reach specifically (P), which is a stronger and different kind of "not exact" than mere staleness. Underneath that live number, a slower batch reconciliation job (cross-referencing P15-Q's identical structural role for Payment System's ledger) independently recomputes an exact count from the full durable log, with no lateness bound and no statistical shortcuts, publishing it via the separate /stats/exact endpoint (I) once it's done — hours, not seconds, later. Billing ultimately settles against that slower, exact number, not the fast provisional one; the fast number's entire job is to be a good-enough, near-real-time signal for a human watching a dashboard, not the final financial word.

W. Failure Scenarios

• The stream-processing layer falls behind the ingestion rate during a peak spike: Kafka's durable retention (M) means nothing is lost — the consumer simply catches up once the spike subsides, at the cost of temporarily wider staleness on the live dashboard, never at the cost of a dropped click.
• A consumer instance crashes mid-window: its keyed aggregation state, checkpointed periodically by the stream-processing framework, is recovered on restart from the last checkpoint plus replaying the small slice of the log since then — no window silently loses its partial count.
• The Bloom filter pre-filter is temporarily unavailable: every click simply falls through to the exact dedup store (N) directly — slower, but never incorrect; the filter is a pure optimization, never load-bearing for correctness.
• A viral ad's hotness detection lags behind its actual traffic spike (R): the affected partition temporarily lags further behind than others, visible as extra staleness for that one ad specifically, until the salting kicks in — a graceful, localized degradation rather than a platform-wide one.
• An event arrives after both the allowed-lateness window and, rarely, after reconciliation has already run for that window: it generates a second, explicit correction on top of the first (Q) — never silently discarded, never silently merged into a number already reported to an advertiser without a trace.
• The fraud-scoring consumer is down: clicks continue to be aggregated and counted normally, unflagged, with the gap in fraud coverage explicitly logged — accepting some short-term fraud exposure is judged safer than blocking legitimate ad traffic from being counted at all (S).

X. Bottlenecks & Scaling Boundary

Y. Design Trade-Offs

Z. Common Bad Designs

• Writing every click as a row via a synchronous INSERT, then running COUNT(*)/GROUP BY for reporting. The single most important mistake to avoid on this problem — it collapses on both the write side and the query side independently, and no amount of sharding fully rescues either half (J).
• Treating a disabled/one-time redirect as sufficient duplicate protection. A browser retry, a bot re-firing a URL, or a slow network causing a resend all happen below any client-side safeguard — only a server-recognized idempotency key genuinely deduplicates (N).
• Trying to compute exact unique reach with a per-ad hash set of user IDs "for accuracy," without first asking whether the business actually needs exactness there. Burns unbounded memory on a number a HyperLogLog sketch answers, at fixed cost, within a well-understood, small error bound (P).
• Confusing a Bloom filter with a cardinality estimator, or vice versa. One answers "have I seen this specific thing," the other answers "how many distinct things have I seen" — reaching for the wrong one for a given question is a fast way to lose credibility in this specific problem (P).
• Assuming all events arrive in event-time order, with no watermark or lateness handling at all. Guarantees silently wrong counts the moment any client is delayed even briefly — which, at this volume, is not a rare event but a routine one (Q).
• Running fraud detection as a nightly batch job, after the day's budget has already been spent. Technically catches the fraud eventually, but too late to have protected the advertiser's actual spend or budget-pacing decisions in the moment it mattered (S).

AA. Interviewer Follow-Ups

AB. Interviewer Evaluation Rubric

• Weak candidate: writes every click directly to a relational table and proposes COUNT(*)/GROUP BY for reporting, with no answer for duplicate clicks and no awareness that this collapses well before the stated peak load.
• Mid-level: correctly identifies that a queue/log is needed for ingestion and that some form of aggregation should be precomputed, but treats unique-reach as "just COUNT(DISTINCT user_id)" without recognizing the memory cost at this scale, and has no concrete answer for late-arriving events beyond "hope they don't happen."
• Senior: proactively reaches for Kafka as the ingestion log and windowed stream aggregation as the computation model; correctly applies an idempotency key to deduplicate events; recognizes that exact unique counting doesn't scale and reaches for HyperLogLog, and can explain — unprompted — how it differs from a Bloom filter.
• Staff+: is explicit, unprompted, about event time versus processing time and designs a concrete watermark/allowed-lateness scheme with a defined fallback (correction records) for genuine stragglers; names the hot-ad partitioning problem before being asked and connects it to the identical hot-key pattern elsewhere in the interview; treats the live dashboard number and the eventual reconciled number as two deliberately different guarantees serving two different audiences, and can defend why that split is correct rather than a compromise.

AC. Interview Simulation

AD. Online Solution Comparison

AE. Interview Cheat Sheet