System Design Lab›System Design Questions›Design Ad Click Aggregator
Design Ad Click Aggregator
MediumData Processingstreamingdeduplicationreal-timedistributed🟣 Meta🔵 Google
Question Overview
Design the pipeline that turns billions of daily ad clicks into accurate per-ad, per-minute counts for advertiser dashboards and billing. Expect to cover stream processing, deduplication, event-time windows with late data, exactly-once writes to the aggregation store, and batch reconciliation.…
Sign up to see the full question and AI interviewer
Requirements
- Log every ad click and redirect the user to the landing page, adding under 50 ms
- Aggregate valid clicks per ad per minute, with hourly and daily rollups and dimension filters
- Deduplicate and filter fraudulent clicks so each valid click is counted exactly once
- Handle events up to 1 hour late using event-time windows and watermarks
- Counts visible within about a minute; advertiser queries under 1 second at p99
- Daily batch recomputation from raw logs reconciles streaming counts and is the source for billing
Back-of-the-envelope numbers
- Ingest: 10B clicks/day ÷ 86,400 s ≈ 116K/s average, ~350K/s at 3× peak
- Bandwidth: 350K/s × 300 B ≈ 105 MB/s at peak; raw log 10B × 300 B = 3 TB/day ≈ 1.1 PB for 1-year retention
- Minute aggregates: at most 5M ads × 1,440 minutes = 7.2B rows/day; if ~20% are non-empty, ≈ 1.4B rows × 50 B ≈ 72 GB/day
- Minute retention: 72 GB/day × 30 days ≈ 2.2 TB before dimension breakdowns, with hourly and daily rollups kept longer
- Dedup state: 1-hour lateness window × 350K/s ≈ 1.26B impression IDs × 16 B ≈ 20 GB, spread across stream workers' keyed state
- Partitioning: 100 Kafka partitions keyed by ad_id → ~3.5K events/s and ~1 MB/s per partition at peak
Key components
- Click service: a stateless redirect endpoint that validates the signed impression ID in the ad URL, appends the click to Kafka, then returns a 302
- Kafka topic partitioned by ad_id, with raw events also archived to object storage for batch recomputation and audit
- Stream processor (e.g. Flink) that deduplicates on impression ID with TTL keyed state and applies fraud rules before counting
- Event-time tumbling 1-minute windows with watermarks and allowed lateness, emitting updated counts when late events arrive
- Exactly-once sink: transactional writes tied to checkpoints, or idempotent upserts of absolute counts keyed by ad and minute, so replays overwrite rather than add
- OLAP store such as Druid, Pinot, or ClickHouse holding minute aggregates plus hourly and daily rollups for advertiser queries
- Daily batch job over raw logs that recomputes counts with the same dedup and fraud logic, feeds billing, and flags discrepancies
Common mistakes
- Incrementing a database counter per click, causing 350K contended writes/s on hot ads and double counts whenever a write is retried
- Windowing by processing time instead of event time, so late mobile clicks are counted in the wrong minute
- Assuming the stream processor's exactly-once checkpoints automatically cover the external database, when the sink must be transactional or idempotent
- Deduplicating on user, ad, and timestamp, which misses retries with new timestamps, or keeping dedup state with no TTL
- Writing to the analytics store synchronously in the redirect path, coupling user-facing latency to the aggregation system
- Billing straight from streaming counts with no batch recomputation to reconcile and correct them
Likely follow-ups
- How would you handle events that arrive 3 hours late after a regional outage?
- How would you detect click fraud such as bot farms clicking a competitor's ads?
- If streaming and batch counts disagree by 0.5%, how would you find the cause?
- How would you support queries by new dimensions such as browser version or campaign?
- How would you reprocess a day of events after fixing a bug in the aggregation logic?
- How would you keep redirects working if the Kafka cluster becomes unavailable?
No community solutions yet
Be the first to publish your solution
Practice ‘Design Ad Click Aggregator’ with an AI Interviewer
Get scored feedback on your diagram, scalability approach, and trade-offs. Free while we grow — up to 3 full interviews a day.