Design an Ad Click Event Aggregation System

Prompt

"Advertisers pay per click. Design the system that ingests click events and answers 'how many clicks did ad X get in the last M minutes' and 'top 100 ads by clicks in the last hour'. Billing depends on these numbers."

Clarifying questions

• What is a click event and who produces it? Ad-serving frontends emit one event per user click: ad_id, click_id, user_id, timestamp, country, ip, plus a few attributes.
• How fresh must the counts be? Dashboards want under one minute of lag. Billing runs on end-of-day numbers and must be exact.
• Are duplicates possible? Yes. Clients retry, and frontends may re-emit on timeout. Each click carries a globally unique click_id.
• What about late events? Events can arrive minutes late due to buffering; a small fraction hours late. We need a policy, not a promise that it never happens.
• Filters on the queries? Yes: by country and by ad campaign. Assume at most a handful of low-cardinality dimensions.
• Is fraud detection in scope? Out of scope, but the pipeline must be able to attach a "suspect" flag later, so raw events must be retained.
• How long is history kept? Raw events for 90 days; minute-level aggregates for 2 years.

Requirements

Functional

• Ingest click events at scale without loss.
• Query 1: click count for one ad_id in a time window, at one-minute granularity.
• Query 2: top-K ads by clicks over a window, optionally filtered by country.
• Exact, deduplicated counts for billing; near-real-time approximate counts for dashboards.

Non-functional

• End-to-end freshness under 60 seconds for the streaming path.
• No double counting for billing; no lost events even during failover.
• Correctness must be provable: the batch path must be able to recompute any window from raw data.

Back-of-envelope

• Assume 1 billion ad impressions per day is too low for a Google-scale system, so take 10 billion impressions/day with a 1% click-through rate: 100 million clicks/day.
• Average: 100 M / 86,400 s = about 1,160 clicks/s. Peak at 5x average is about 6,000 events/s. Ingestion volume is small; the hard part is correctness, not throughput.
• Event size: ~0.2 KB. Raw storage: 100 M x 0.2 KB = 20 GB/day, 1.8 TB for 90 days before compression, roughly 0.5 TB compressed.
• Aggregates: say 2 million distinct ads active per day. Minute-level rows per day: 2 M ads x 1,440 minutes would be 2.9 billion rows if every ad clicked every minute, but most ads are sparse; assume 5% density, so ~150 M rows/day at ~50 bytes = 7.5 GB/day, about 5.5 TB over 2 years. This is a modest columnar table.
• Query load: a few thousand dashboard queries per second at most; each top-K query touches at most 60 minute-buckets.
• Ingest bandwidth: 6,000 x 0.2 KB = 1.2 MB/s peak. Trivial, and worth saying out loud because it reframes the problem correctly.