Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced

Aggregation where the numbers are money

Counting clicks sounds like the easiest problem in this guide. It is one of the hardest, for a reason that has nothing to do with volume: the output is an invoice. Advertisers are billed from these counts and publishers are paid from them, so "approximately right" is not acceptable, double-counting is fraud, and under-counting is lost revenue. Every design choice below follows from that.

Scope

• Ingest click events: {ad_id, user_id, timestamp, ip, country}.
• Serve aggregated counts per ad per minute, and top-N ads by clicks in a window.
• Scale: ~1 billion clicks/day, peaking several times the average.
• Support recalculation — if a bug corrupts yesterday's numbers, you must be able to fix them.

The distinction that everything hinges on

Event time is when the click happened. Processing time is when your pipeline saw it. They differ — by milliseconds usually, by minutes when a phone loses signal in a lift and buffers events, by hours when a mobile SDK retries after a long outage.

You must aggregate by event time, because that is what the advertiser's campaign schedule and budget refer to. A click at 11:59:58 belongs to the 11:59 minute even if it arrives at 12:04. Aggregating by processing time is easier and produces bills that are simply wrong.

Windows and watermarks

Tumbling windows are fixed, non-overlapping intervals — each event belongs to exactly one. This is what billing needs: sum the 12:00 minute, sum the 12:01 minute, no double counting. Sliding windows overlap (a 5-minute window advancing every minute), so an event belongs to several. They are for smoothing and detection ("clicks in the last 5 minutes"), never for billing, because an event counted in five overlapping windows cannot be summed into a total.

The problem is knowing when a window is done. You cannot wait forever, and you cannot close at the wall clock, because late events would be lost. A watermark is the pipeline's assertion: "I do not expect any more events with event time earlier than T." It is typically derived as the maximum event time seen minus an allowed-lateness margin.

• Before the watermark passes a window: keep it open, accept late events, and restate the count.
• After the watermark passes: the window is sealed and emitted. Arrivals after that go to a side output for separate handling.

The watermark is therefore a direct latency-versus-completeness dial: a long allowed-lateness gives accurate counts published slowly; a short one publishes fast and misses stragglers. There is no setting that avoids the choice, and the honest architecture publishes both — a fast provisional number for dashboards and campaign pacing, and a finalized number for billing after the watermark plus a grace period.

Whatever you do, late events must not be silently dropped. Route them to a side output and count them. Silently discarding them is revenue lost with no trace, and it is invisible precisely because the discarded data is the evidence.

Deduplication: the hard requirement

The ingest path is at-least-once (retries after a lost acknowledgement, an SDK re-sending, a consumer reprocessing after a crash), so duplicates are guaranteed. Since the output is billing, they must be removed.

The mechanism is an idempotency key per click — generated at the client or edge, not by the aggregator — and a dedupe store keyed on it. The design question is the store's window: keeping every key forever is unbounded, so you keep them for as long as duplicates can plausibly arrive (say 24 hours) and accept that a duplicate arriving after that is uncatchable. That is a real, bounded exposure, and the reason SDK retry windows and dedupe windows should be chosen together rather than independently.

Note what "exactly-once" means here in practice: at-least-once delivery, plus a deduplicating and idempotent aggregation step, plus an atomic commit of the aggregate together with the input offset. If the offset advance and the aggregate write are not atomic, a crash between them either double-counts or loses a batch — which is why the aggregate and the offset belong in one transaction, or the aggregate must be keyed so a replay overwrites rather than adds.

Lambda versus Kappa, and why it matters here

Lambda architecture runs two pipelines over the same data: a streaming path for fast approximate results and a batch path that recomputes authoritative results later. The serving layer merges them. It directly satisfies "fast dashboards plus correct billing", and its cost is the thing that sinks it in practice: two implementations of the same aggregation logic, which drift, disagree, and must both be debugged.

Kappa architecture keeps one streaming pipeline and gets correction by replay — the event log is retained, so to recompute you rewind the offset and run the same code again. One codebase, one set of semantics. The requirement it imposes is that the log retention must cover the longest period you might need to recompute, which is the storage bill you pay for architectural simplicity.

For ad click aggregation, Kappa is usually the better answer because recalculation is a first-class requirement: you will find a bug in the aggregation logic, and replay is a far more trustworthy fix than a second pipeline that may contain the same bug. Whichever you choose, you still need reconciliation — a scheduled job comparing aggregated totals against raw event counts, because the alternative is discovering a discrepancy when an advertiser disputes an invoice.

Scaling: partitioning and the hotspot problem

Aggregation partitions by ad_id, so all clicks for one ad reach the same aggregator and can be counted without cross-node coordination. That works until an ad goes viral: one ad_id is one partition is one worker, and no amount of scaling out helps because the key pins it.

The standard fix is a two-stage aggregation with key salting: partition on ad_id + random_suffix (say 1 of 16), aggregate each shard independently, then sum the 16 partial counts in a second stage. The hot key's work spreads across 16 workers; the second stage handles 16 rows instead of millions of events. The cost is an extra stage and slightly higher latency — a good trade, and the same pattern used for hot keys in counters and leaderboards.

For top-N ads, exact global ranking would require a total order over all ads. The scalable shape is map-reduce-like: each partition computes its local top-N and a reducer merges them. Note the subtlety — merging local top-N lists is exact for a sum-based ranking only if every partition reports each candidate; otherwise it is an approximation, which is fine for a dashboard and not for billing. Keep the ranking approximate and the per-ad totals exact.

Which choice, when

Decision	Option	Choose when	Breaks when
Time semantics	Event time + watermark	Billing, anything auditable	Requires trusted client clocks or edge-stamped time
Time semantics	Processing time	Ops dashboards, rough monitoring	Billing — late events land in the wrong period
Window	Tumbling	Billing totals per period	You need smoothed trends
Window	Sliding	Anomaly/fraud detection, pacing	Summing for invoices — events counted repeatedly
Correction	Kappa (replay)	Recalculation is a requirement; one codebase preferred	Retention cannot cover the replay horizon
Correction	Lambda (batch + stream)	Batch system already exists and is trusted	Two codebases drift — and the drift is the bug
Hot keys	Salted two-stage aggregation	Viral ads, skewed key distributions	Adds a stage and latency; overkill for uniform traffic
Top-N	Local top-N then merge	Dashboards and reporting	Exactness required — the merge can miss candidates

Pitfalls

• Aggregating by processing time and billing from it. The foundational error.
• Dropping late events silently. Lost revenue with no audit trail; always use a side output and alert on its rate.
• Trusting client timestamps blindly. Device clocks are wrong and can be manipulated; stamp at the edge and treat client time as a hint, or a skewed clock becomes a billing dispute.
• Committing offsets separately from aggregates. Guarantees double-counting or gaps on crash.
• No reconciliation job. Then your first discrepancy report comes from a customer.
• Partitioning by ad_id alone and being surprised when a viral ad saturates one worker.
• Summing sliding-window counts into a total — overlapping windows double-count by construction.
• An unbounded dedupe store. It grows forever; bound the window deliberately and know your residual exposure.

Cost model — what dominates the bill

This pipeline's cost is retained raw events plus stream-processing state — and the retention exists to buy replay, so the architecture choice and the bill are the same decision.

Rough BOTE: 1 billion clicks/day ≈ 11,600 events/second average, peaking maybe 50,000/s. At ~200 bytes per event that is 200 GB/day raw. Keeping 30 days for replay is ~6 TB; at replication factor 3 that is ~18 TB in the log tier, roughly $1,800/month on SSD-backed storage at ~$0.10/GB-month — and materially cheaper on tiered object storage for older segments. The aggregated output is tiny by comparison: per-ad-per-minute counts for a million ads is 1M × 1,440 = 1.44 billion rows/day of a few tens of bytes — tens of GB/day, which compresses well and is what actually gets queried.

The dedupe store is the sleeper cost: one entry per click for 24 hours means ~1 billion keys resident. At even 50 bytes/key that is 50 GB of hot key-value state, which must be fast enough to check on every event at peak — 50,000 lookups/second. That is a real cluster, not a cache afterthought.

Dominant line items: replicated raw-event retention (the replay window); then dedupe-store memory and throughput; then stream-processing compute.

Levers: tier old log segments to object storage (largest saving, minimal downside since replay of old data is rare and can be slow); shorten the replay window to the shortest period you would actually recompute; shrink the dedupe window in line with the real SDK retry horizon; and pre-aggregate at the edge or in a collector so the stream carries partially-summed counts rather than individual events — though note this weakens per-event dedupe, so it suits impression-style counting more than billable clicks.

Operability: the fingerprints of a broken aggregation pipeline

Because the output is money, the fingerprints worth knowing are the ones that produce plausible but wrong numbers. Counts that quietly drift below raw event volume is the late-event drop — visible only if you count the side output, which is exactly why it must be counted. Totals that change after a window was declared final means the watermark is advancing too aggressively and windows are being restated after publication, which erodes trust in every number the system emits.

One aggregator with far higher CPU and consumer lag than its peers is the hot-key signature; confirm by looking at the top ad by volume in that partition rather than by adding capacity. A step change in counts exactly at a deploy boundary points at aggregation logic, and is the case replay exists for — which is only useful if retention still covers the affected period, so check that before anything else.

The most serious fingerprint is reconciliation diffs trending upward: raw counts and aggregated counts diverging slowly. It usually means duplicates slipping past an undersized dedupe window, or a partition whose failures are being retried in a way that re-adds rather than overwrites. Watch also for watermark stalling — one idle partition with no events prevents the watermark from advancing, so windows never close and output stops entirely while ingest looks perfectly healthy. That one surprises everybody the first time.

Signals worth having: side-output (late event) rate and lateness distribution, watermark lag per partition, reconciliation diff between raw and aggregated totals, dedupe-store hit rate and eviction age, per-partition consumer lag and CPU skew, and count-restatement events after publication.

---

Authored for this guide to cover the ad click event aggregation design (Alex Xu Vol. 2, ch. 21 — not present in the Vol. 1 PDF); event-time versus arrival-order watermark diagram hand-authored as SVG. Builds on this guide's "Windowing and Watermarking in Streaming Systems", "Idempotent Producers and Consumers", and Kafka topics.

I just read a lesson on **Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced** (System Design) and want to truly understand it. Explain Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Designing Ad Click Event Aggregation — Windows, Watermarks & Exactly-Once, Traced** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.