Ad Click Aggregation System Design Template (Reference Design)

System Design: [Ad click aggregation system name]

Reference design for an ad click aggregation pipeline that turns a stream of raw click events into per-ad counts by minute and hour, top-K rankings and billing-grade daily totals. It covers click deduplication, event-time windows and late events, exactly-once counting with Kafka transactions and idempotent sinks, exact and approximate top-K (the Count-Min sketch of Cormode and Muthukrishnan, Journal of Algorithms 55(1), 2005, and the Space-Saving algorithm), and the batch reconciliation that makes streaming numbers safe to bill. It follows the structure of the System Design Document template, adds a 45-minute interview path, access-pattern and failure-mode tables and a cited "How real systems do it" box, and is pre-filled with a worked design to adapt. Replace every [bracketed] value and every row marked Example; values marked Example are assumptions, not benchmarks.

Document control

Approvals

Revision history

Contents

1. Overview
2. Requirements
3. Capacity estimation
4. API design
5. Data model
6. High-level design
7. Detailed design
8. Scalability and performance
9. Reliability, security and operations
10. Trade-offs and alternatives
11. Delivery plan and risks
12. Appendices

1. Overview

1.1 Problem statement

[Example: Clicks are counted today by a nightly batch job over web server logs. Advertisers see yesterday's numbers only, budget pacing reacts a day late, and two incidents last quarter double-counted clicks when a log shipper retried a file. The sales team wants live dashboards and a "top ads right now" view, and finance wants one number per ad per day that it can defend in a billing dispute.]

1.2 Proposed solution

[Example: A click service records every click as an event with a unique, signed click ID, redirects the user to the advertiser, and writes the event to a Kafka topic partitioned by ad ID. A stream processor deduplicates click IDs, filters invalid traffic flagged upstream, counts clicks per ad in one-minute event-time windows and writes the counts with Kafka transactions and idempotent upserts into an analytics store. Top-K rankings are exact for the ad dimension and approximate (Count-Min sketch with a heap) for the high-cardinality publisher dimension. Raw events are also archived to object storage, and an hourly and daily batch job recomputes counts from the archive and reconciles them with the streaming numbers. Invoices use only the reconciled daily totals.]

1.3 Decisions requested from reviewers

1.4 45-minute interview path

Use this order when the document serves an interview or a short design review. The written sections below are the long form of each step.

Start the default deep dive with the naive answer, a counter per ad incremented in a database on each click, and show the two ways it fails: the row for a popular ad becomes a write hotspot, and a retried request counts twice. Then move to a partitioned log, keyed state and a transactional sink, and say exactly which step removes which duplicate.

2. Requirements

2.1 Core functional requirements

2.2 Non-functional requirements

2.3 Below the line (out of scope)

Name these explicitly so reviewers and interviewers do not pull the design toward them.

• [Example: invalid traffic and click-fraud scoring models (a separate service sets a flag on the event; see the fraud detection design)]
• [Example: impression counting and click-through rate (same pattern, separate pipeline)]
• [Example: conversion attribution and multi-touch models]
• [Example: the ad auction and budget pacing logic that consume the counts]
• [Item deferred, with the release that will take it]

3. Capacity estimation

3.1 Assumptions

3.2 Event rate and bandwidth

At 250 B per event that is 300 GB per day and about 17.4 MB/s at peak. Kafka retention of 7 days with replication factor 3 holds $300 \times 7 \times 3 = 6.3$ TB. Consequence: bandwidth and log storage are small, so the design is shaped by state and correctness, not by throughput.

3.3 Aggregation tasks and partitions

Run 32 tasks (more than 4x headroom for replays, which must catch up faster than real time) over 128 Kafka partitions. Consequence: create 128 partitions from day one, because changing the partition count remaps ad IDs to partitions and breaks per-key ordering; 128 still covers the 10x case (about 70 tasks) in section 10.

3.4 Dedupe state

That is 250 million click IDs if the whole hour ran at peak, an upper bound. Spread over 32 tasks it is about 0.47 GB per task. Consequence: the dedupe state fits in an embedded on-disk state backend (RocksDB) per task with incremental checkpoints; no external lookup per click is needed.

3.5 Aggregate storage

Consequence: minute rows are the big table; keep them 30 days in a columnar OLAP store partitioned by day and roll older data up to hours.

3.6 Top-K state

Exact counts for the ad dimension need one counter per key per window: about 1 million keys x 64 B = 64 MB per minute window across the cluster, which is cheap. The publisher dimension (ad x publisher x country) has about 30 million keys per minute, or 1.92 GB per minute window, multiplied by every open window. A Count-Min sketch with $\varepsilon = 10^{-4}$ and $\delta = 10^{-3}$ needs $w = \lceil e/\varepsilon \rceil = 27\,183$ columns and $d = \lceil \ln(1/\delta) \rceil = 7$ rows, 190 281 four-byte counters or about 761 KB per task and window. Consequence: exact top-K for ads, sketch-based top-K for the publisher dimension (7.6).

3.7 Summary

4. API design

4.1 Interfaces

4.2 Click token and event

The click token is minted when the ad is served and signed with a server key (HMAC), so a click ID cannot be forged and the same click always carries the same ID. Redirects are logged before the 302 is returned, but the write is an asynchronous append to a local buffer that is flushed to Kafka; the redirect never waits for a Kafka acknowledgement.

{"click_id": "c_01JD4Q8V6R2M7X9K3T5W0YH1ZB", "ad_id": "ad_48213", "campaign_id": "cmp_902", "advertiser_id": "adv_77", "publisher_id": "pub_5521", "event_time": "2026-10-30T09:14:07.412Z", "country": "DE", "device": "mobile", "ivt_flag": false, "schema_version": 3}

4.3 Top-K response

Every response says which window it describes and whether the numbers are provisional, so a client never mistakes a streaming count for a billable one.

{"window_start": "2026-10-30T09:14:00Z", "window_end": "2026-10-30T09:15:00Z", "k": 3, "status": "provisional", "watermark": "2026-10-30T09:15:31Z", "items": [{"ad_id": "ad_48213", "clicks": 18342}, {"ad_id": "ad_10077", "clicks": 15120}, {"ad_id": "ad_33190", "clicks": 9981}]}

5. Data model

5.1 Entities

Raw click events are immutable; every count is derived from them and can be rebuilt from the archive.

5.2 Storage choices

5.3 Access patterns

6. High-level design

There are two paths: a hot streaming path for fresh, provisional numbers and a cold batch path that produces the reconciled, billable numbers.

7. Detailed design

The deep dives in this section, each with options, choice and reason:

7.1 Click flow

If the token signature fails, the click service still redirects (users must never see an error for an ad click) but writes the event with an invalid_token reason to a separate topic for the fraud team.

7.2 Duplicate removal

Duplicates enter at three places, and each has its own control:

Dedupe state keeps each click ID for one hour after its event time, with a TTL so state stays near the 15 GB bound in 3.4. A Bloom filter would use less memory, but a false positive drops a genuine click without trace, which is a billing error; exact keyed state is affordable at this scale, so it wins.

7.3 Exactly-once counting

"Exactly once" here means each accepted click changes the published counts exactly once, even when tasks crash and replay. Three mechanisms combine:

1. Source: the aggregator reads Kafka with isolation.level=read_committed and keeps its offsets in its own checkpoints, not in auto-commit.
2. State: periodic checkpoints (every 30 s) capture offsets, dedupe state and open windows together. After a crash the task restores the last checkpoint and re-reads from the saved offsets, so state and input agree.
3. Sink: window results are written either as a Kafka transaction that commits with the checkpoint (when the next stage is another topic), or as an idempotent upsert keyed by (ad_id, window_start, country, device) that overwrites rather than increments (when the sink is the OLAP store). A replayed window writes the same row with the same value.

The rule to review: never write increments (clicks = clicks + n) to an external store from a stream job; write absolute window values that a replay can repeat safely.

7.4 Windows, watermarks and late events

Clicks are counted by event_time, not arrival time, in tumbling one-minute windows. The watermark trails the largest event time seen by 30 s, so a window emits a provisional result about 30 s after it ends. Windows stay open for 10 minutes of allowed lateness; a late click updates the window and the sink overwrites the row with the new absolute value. A click later than 10 minutes goes to a side output that is counted only by the batch path (7.5). Report the late share as a metric; if it rises, mobile clients are probably buffering offline.

7.5 Reconciliation and the billing source of truth

The batch job runs every hour for the previous hour and once a day for the whole day, after a 2-hour delay so stragglers land in the archive. It deduplicates raw events by click_id, drops invalid traffic and counts per ad. The reconciler then compares batch and streaming counts per ad and hour:

Daily billing totals always come from the batch count, stored with a reconciliation version. A correction after invoicing creates a new version and an adjustment line, never an edit in place.

7.6 Top-K

Because Kafka is partitioned by ad_id, every ad's full count lives in one task. Each task keeps exact counts per window and emits its local top 100; the merger takes the top 100 of the union, which is exact because no ad is split across tasks. Five-minute and 60-minute rankings are computed from the stored minute counts with sliding sums.

The publisher dimension (ad x publisher x country) is too wide for exact counts per window (3.6). Each task keeps a Count-Min sketch of $d$ rows and $w$ columns plus a min-heap of the current heaviest keys. For a key $x$ the estimate is the minimum over rows:

where $N$ is the total count in the window. With $\varepsilon = 10^{-4}$ and a peak minute of $N \approx 4.17$ million clicks, the overestimate is at most about 417 clicks with probability 0.999. Sketches with the same dimensions and hash functions merge by adding cells, so the merger sums the per-task sketches. The Space-Saving algorithm (Metwally and colleagues, ICDT 2005) is the alternative when only the heavy hitters matter: it keeps a fixed number of counters and gives a guaranteed error bound per counter. Label these rankings approximate in the API.

7.7 How real systems do it

How real systems do it (paraphrased, not copied). Google's SIGMOD 2013 paper "Photon: Fault-tolerant and Scalable Joining of Continuous Data Streams" (Ananthanarayanan and colleagues) describes a system that joins ad click events with the search query events that led to them, across several datacenters at once. The authors state three guarantees: the joined output never contains a duplicate, most joinable events appear in real time, and every event is eventually joined exactly once. They report that it ran through degraded infrastructure and whole datacenter outages without manual steps, handled millions of events per minute at peak with average end-to-end latency under 10 seconds, and fed advertiser billing. The idea reused here is that a shared record of already-processed event IDs, kept consistent across workers, is what prevents double counting when processing is retried or runs in more than one place. The Apache Kafka design documentation explains the transactional pattern in 7.3: offsets are committed in the same transaction as the output, consumers read only committed records, and for external stores the offset is kept together with the output instead of using two-phase commit. Read the originals in 12.3.

8. Scalability and performance

Freshness budget for a minute window (target NFR-01, 60 s after window close):

9. Reliability, security and operations

9.1 Failure modes

9.2 Security, privacy and abuse

Data protection law applies to click data that can identify a person. For example, the EU GDPR storage-limitation principle (Article 5(1)(e)) and the UK GDPR equivalent require that identifiable data is not kept longer than needed. This is a design note, not legal advice; check the current official text and a qualified professional for your jurisdictions.

9.3 Observability

9.4 Cost estimate

10. Trade-offs and alternatives

11. Delivery plan and risks

11.1 Milestones

11.2 Rollout and rollback

[Example: Run the new pipeline beside the legacy batch job for a full billing month and compare invoices line by line. Dashboards switch first; billing switches only after finance approves the parallel run. Rollback for the stream path is a redeploy from the last savepoint; billing can fall back to the legacy job until it is decommissioned.]

11.3 Risks

12. Appendices

12.1 Open questions

12.2 Glossary

12.3 References

Standard and licence note

Based on the published algorithms and documentation cited in 12.3: the Count-Min sketch (Cormode and Muthukrishnan, 2005), the Space-Saving algorithm (Metwally and colleagues, 2005) and the Apache Kafka delivery semantics documentation. The Photon paper is paraphrased in 7.7 only; no text, figures or code were copied, and every number in this template is an illustrative example computed for this design. Check the current Kafka documentation for your version before relying on a configuration name. The note on data protection in 9.2 is not legal advice. Original text written for this collection. Template licence: MIT.