Design an ad click aggregation system

Goal: count clicks on ads per ad, per minute (and other dimensions), so dashboards and billing show accurate numbers with low delay.

Requirements

• Very high event rate (for example 100,000+ clicks per second at peak).
• Queries like "clicks for ad X in the last hour" and "top ads in the last 5 minutes".
• Accuracy matters: money depends on it.
• Handle late and duplicate events; recover from failures.

Pipeline

1. Collect: click events arrive through an API or ad servers and go straight into a log (Kafka). Never write each event to the final database directly.
2. Raw storage: also keep every raw event in cheap storage (object storage) as the source of truth.
3. Stream processing: a job (Flink, Spark Streaming or similar) reads the log, groups events by ad_id into time windows (for example 1 minute) and computes counts.
4. Aggregate store: write results to a database suited to analytics queries (a columnar or time-series store).
5. Query API: serves dashboards from the aggregates.

Windows

Use tumbling windows (fixed, non-overlapping, such as each minute) for per-minute counts. Sliding windows give "last 5 minutes" views.

Late events

Events can arrive late because of network delay. Use event time (when the click happened), not arrival time, and a watermark that waits a short while for stragglers before closing a window. Very late events are corrected by a later batch job.

Duplicates and exactly-once

Retries cause duplicate events. Give each click a unique ID and deduplicate (a short-lived set of IDs in the stream job). Combined with checkpointed processing and idempotent writes (upsert by ad_id + window), the result behaves like exactly-once.

Reconciliation

Run a periodic batch job over the raw events to recompute counts and compare them with the streaming results. If they differ, fix the aggregates. This catches bugs and late data, and is how billing-grade systems stay trustworthy.

Hot keys

One viral ad produces a huge share of events. Pre-aggregate in small partial counts across several workers (a local combine), then merge.

Fraud

Filter bots and repeated clicks (for example the same user clicking many times) before billing. Keep invalid traffic marked rather than deleted, for audit.

Scaling

Partition the log by ad_id, scale stream workers with partitions, and keep recent aggregates in a fast store while archiving older data.

Takeaway: log first, aggregate in windows by event time, dedupe by click ID, and reconcile streaming results against raw data.

---

System Design series, Part 3 (case studies): 16 of 16. Previous: Design an online judge for coding contests.