Design Ad Click Aggregator
Real-time log ingestion and aggregation stream pipeline computing metrics for billing.
1. System Requirements
- Ingest billions of ad click events daily from global websites.
- Aggregate clicks by advertiser and ad ID in real-time (1-minute intervals).
- Deduplicate click events to prevent double charging.
- Highly scalable write path.
- Exactly-once processing guarantees.
2. Scale & Resource Estimates
Throughput: 10 Billion ad impressions/day = ~115,000 click events/sec writes.
Storage & RAM: Aggregated metrics database: ~10 GB database storage/month. raw logs are archived in cold storage.
3. System API Endpoints
POST /api/v1/clicks
Request: { click_id: "clk-abc-992", ad_id: "ad-4412", advertiser_id: "adv-881", timestamp: 1783708872 }
Response: { status: "received" }4. Database Schema Design
Table: ad_click_metrics (InfluxDB / DynamoDB - sharded by advertiser_id)
- advertiser_id (UUID - Partition Key)
- timestamp_minute (Timestamp - Sort Key)
- ad_id (UUID)
- clicks_count (BIGINT)
- total_cost (NUMERIC)5. Interactive High-Level Architecture
6. Scalability Deep Dive & Bottlenecks
Utilizes Apache Flink for stream processing. To achieve exactly-once processing, click events are passed through a sliding/tumbling 1-minute time window in Flink, and a Redis Bloom Filter deduplicates duplicate events. Aggregated metrics are written to a time-series database (like InfluxDB or Cassandra sharded by `advertiser_id`) using bulk/batch writes, allowing advertisers to view analytics in under 5 seconds.
7. Production Scaling Strategies
Flink Checkpointing: Enable incremental checkpointing with RocksDB state backend. Checkpoint interval: 30 seconds. At 1M events/sec, each checkpoint captures ~30M events of state. Incremental checkpoints upload only changed SST files — completes in <5 seconds.
Bloom Filter Deduplication: Use a partitioned Redis Bloom Filter (capacity: 10B, false positive rate: 0.01%) to detect duplicate click events. Each click is checked against the filter before processing. Bloom filter is reset daily to bound memory growth.
Time-Series Rollup: Raw click events aggregated into 1-minute, 1-hour, and 1-day rollups. Minute-level data retained for 7 days, hourly for 90 days, daily for 2 years. Reduces query scan range by 1000× for dashboard reads. Stored in ClickHouse for fast analytical queries.
Real-Time Bidding Integration: The click aggregation pipeline feeds back into the RTB (Real-Time Bidding) system. Click-through rates (CTR) per ad creative are updated every minute. The bidding engine uses fresh CTR data to adjust bid prices in real-time.
Fraud Detection: A secondary Flink job monitors click patterns for fraud: (1) same IP clicking >10 times/minute, (2) click farms (many clicks from a single /24 subnet), (3) suspiciously high CTR (>20%). Flagged clicks are excluded from billing and reported to advertisers.
Multi-Tenant Isolation: Each advertiser's metrics are isolated in separate Cassandra partitions (partition key: advertiser_id + date). Large advertisers (>1M clicks/day) get dedicated Flink task slots to prevent noisy-neighbor effects on aggregation latency.
8. Runnable Production Starter Code
Scaffold