Design a Real-Time Ad Click Aggregator

Functional Requirements

• ·Real-time click counting: Count clicks per ad, campaign, ad group, and advertiser in real-time with sub-minute freshness
• ·Click deduplication: Eliminate duplicate click events caused by network retries, client-side double-taps, and replay after failures
• ·Near-real-time dashboards: Serve aggregated metrics to advertiser dashboards with < 1 minute end-to-end latency from click to chart
• ·Multi-dimensional drill-down: Support slicing by campaign / ad_group / ad / geo (country, region) / device_type / time granularity (1m, 1h, 1d)
• ·Billing-grade accuracy: Produce invoiceable click counts per advertiser per billing period; financial accuracy is non-negotiable
• ·Click fraud / anomaly detection: Flag suspicious patterns (burst from single IP, bot signatures, abnormal CTR) and exclude fraudulent clicks from billing

Non-Functional Requirements

• ·Throughput: Sustain 580K clicks/sec average, 1.5M clicks/sec at peak (2.5x burst during flash sales, Super Bowl, etc.)
• ·Exactly-once counting: Every click counted exactly once for billing — no undercounting (revenue loss), no overcounting (advertiser trust)
• ·Availability: 99.99% uptime (~52 min downtime/year); click ingestion must never drop events
• ·Latency: < 1 minute from click event to updated dashboard; < 200ms dashboard query response (p99)
• ·Data retention: Raw click events retained 30 days; aggregated data retained 2 years; archived to cold storage indefinitely

Constraints / Assumptions

• ·This is a financial system — billing accuracy is non-negotiable and must survive streaming pipeline failures
• ·Must support retroactive corrections (e.g., batch reconciliation discovers streaming drift, fraud detected post-hoc)
• ·GDPR compliance: Click data contains PII (user_id, IP address); must support right-to-deletion and data anonymization after retention period
• ·Ad server already generates globally unique click IDs; we do not control the ad-serving layer
• ·Peak traffic is predictable (tied to major events) — capacity planning can be proactive

POST /v1/clicks — Click Event Ingestion

Called by ad servers. Accepts batches for efficiency.

GET /v1/metrics — Dashboard Query

GET /v1/reports/billing — Billing Report (Reconciled)

Click Event Schema (Kafka Message)

ClickHouse Tables

Storage Technology Choices

Flow 1: Click Ingestion Path

1. User clicks an ad. Ad server generates a UUID v7 click_id and fires a click event.

2. Click event is sent to the /v1/clicks ingestion endpoint (or directly to Kafka via ad server SDK).

3. Kafka assigns the event to a partition (partitioned by campaign_id hash for ordering guarantees within a campaign).

4. Flink consumer reads the event. Dedup operator checks Redis Bloom filter — if “probably new,” passes through. If “possibly seen,” checks Redis exact set. If confirmed duplicate, drops.

5. Deduplicated event enters a 1-minute tumbling window. At window close, Flink emits aggregated counts per (campaign_id, ad_id, country, device_type).

6. Flink writes raw deduplicated events to ClickHouse raw_clicks table (batch insert every 5 seconds).

7. Flink atomically updates Redis counters for real-time dashboard queries.

8. ClickHouse materialized views automatically compute aggregated_clicks_1m and aggregated_clicks_1h.

Flow 2: Dashboard Query Path

1. Advertiser opens dashboard, UI calls GET /v1/metrics?campaign_id=X&granularity=1m&group_by=country.

2. For last 5 minutes (hot data): API reads from Redis counters (< 10ms, pre-aggregated).

3. For older windows: API queries ClickHouse aggregated_clicks_1m or aggregated_clicks_1h depending on granularity.

4. Results merged and returned. Freshness indicator shows last-processed timestamp.

Flow 3: Billing Reconciliation Path

1. Hourly Spark job reads raw events from Kafka (30-day retention guarantees full replay).

2. Spark re-computes exact counts per (advertiser_id, campaign_id, hour) with deterministic dedup on click_id.

3. Compares streaming-produced counts (from ClickHouse aggregated tables) vs batch-produced counts.

4. If |difference| > 0.1%, flags the discrepancy and generates correction records.

5. Corrections written to ClickHouse billing_adjustments table and propagated to invoicing system.

6. At billing cycle close (monthly), reconciled totals become the authoritative billing numbers.

Deep Dive 1: Exactly-Once Deduplication at Scale

Why this is hard: At 1.5M clicks/sec peak, even a 0.01% duplicate rate means 150 extra clicks/sec billed incorrectly. For a $0.50 CPC campaign, that is $6,480/day in billing errors. We need layered defense.

Click ID generation: UUID v7 (RFC 9562) generated at the ad server. Timestamp-ordered prefix enables efficient range-based Bloom filter rotation. The ad server is the single point of ID assignment — no coordination needed.

Layered deduplication strategy:

Bloom filter math:

• ·Optimal bit array size: m = -(n * ln(p)) / (ln2)^2
• ·With n = 50B clicks/day, p = 0.01 (1% false positive rate):
• ·m = -(50 × 10^9 × ln(0.01)) / (0.693)^2 = 480 billion bits = 60 GB
• ·Optimal hash functions: k = (m/n) ln2 = (480B/50B) 0.693 = 6.6 ≈ 7
• ·Hourly rotation: partition Bloom filter into 24 hourly segments (2.5 GB each). Expire segments older than 25 hours. This bounds memory and keeps false positive rate stable.

Dedup approach comparison:

The layered approach gets near-zero false positives: only 1% of traffic hits the exact Redis check (Bloom filter positives), and those are resolved exactly. Net false positive rate: 0%.

Flink exactly-once semantics:

Dedup operator pseudocode (Flink):

Deep Dive 2: Aggregation Pipeline Design

Window type selection:

We choose 1-minute tumbling windows because: (a) dashboard SLA is < 1 minute, so 1-min granularity meets requirements; (b) tumbling windows produce exactly one output per event (no duplication across overlapping windows); (c) clean 1-minute boundaries align with ClickHouse materialized view rollups.

Pre-aggregation in Flink (reduces ClickHouse write volume by ~1000x):

ClickHouse materialized view rollup chain:

High-cardinality dimension handling:

• ·Problem: If we allow arbitrary drill-down on all dimensions, the number of aggregation buckets explodes. 100M campaigns x 200 countries x 5 devices x 1440 minutes = 144 trillion rows/day.
• ·Solution 1: Pre-aggregate only the top 4 dimensions (campaign_id, ad_id, country, device_type). Rarer drill-downs (region, placement) query raw_clicks directly.
• ·Solution 2: Approximate unique counts via uniqHLL (HyperLogLog) in ClickHouse for unique_users — trades 2% accuracy for 1000x memory savings.
• ·Solution 3: Adaptive rollup — if a dimension combination has < 100 clicks/day, merge into an "other" bucket at the 1h level.

Deep Dive 3: Lambda Architecture — Streaming + Batch Reconciliation

Why streaming alone is insufficient for billing:

1. Flink job restarts: During recovery (30s checkpoint interval), events may be reprocessed. Exactly-once mitigates this but edge cases exist (e.g., ClickHouse sink timeout during two-phase commit).

2. Bloom filter false positives: 1% of legitimate clicks flagged as “possibly seen” are routed to Redis exact check. If Redis has a brief partition, those checks may fail open (allowing duplicates) or fail closed (dropping valid clicks).

3. Late arrivals beyond watermark: Flink watermark allows 5 minutes of lateness. Clicks arriving later than 5 minutes are emitted via side output but may not make it into the streaming aggregation.

4. Network partitions: Kafka consumer may lose connectivity, causing rebalance and potential duplicate processing during consumer group reassignment.

For these reasons, streaming provides the real-time view, but batch reconciliation provides the billing source of truth.

Batch reconciliation architecture:

Reconciliation pseudocode:

Late arrival handling:

Bottleneck 1: Bloom Filter Memory (60 GB)

• ·Problem: 60 GB for a single day's Bloom filter exceeds typical Redis node memory.
• ·Mitigation: Partition by hour (24 segments of 2.5 GB each). Expire segments after 25 hours. Distribute across Redis Cluster (8 nodes, ~8 GB each). Use Redis Bloom module (RedisBloom) for native BF.ADD/BF.EXISTS commands.

Bottleneck 2: Flink Backpressure During Traffic Spikes

• ·Problem: Super Bowl / flash sale spikes to 1.5M clicks/sec. If Flink cannot keep up, Kafka consumer lag grows, dashboard freshness degrades.
• ·Mitigation: Kafka acts as buffer (hours of retention). Flink autoscaling via Kubernetes (reactive mode: add TaskManagers when consumer lag exceeds 30 seconds). Pre-provision 2x capacity before known events.

Bottleneck 3: ClickHouse Write Throughput at 10 TB/day

• ·Problem: 116 MB/s sustained write rate. Individual row inserts would overwhelm ClickHouse.
• ·Mitigation: Batch inserts of 100K rows every 5 seconds via Buffer table engine. Flink accumulates rows in memory and flushes periodically. ClickHouse async inserts with async_insert=1 for overflow. Shard across 10 ClickHouse nodes (12 MB/s per shard).

Bottleneck 4: High-Cardinality Dimension Explosion

• ·Problem: Full cross-product of all dimensions at 1-minute granularity is computationally infeasible.
• ·Mitigation: Pre-aggregate only top 4 dimensions (campaign, ad, country, device). Serve rare drill-downs (region, placement) from raw_clicks table with ClickHouse's columnar scan speed. Use approximate algorithms (HyperLogLog) for unique counts.

Bottleneck 5: Kafka Partition Hot Spots

• ·Problem: Partitioning by campaign_id means viral campaigns create hot partitions.
• ·Mitigation: Use compound partition key: hash(campaign_id + ad_id) % num_partitions. Monitor partition lag skew and rebalance. For known large campaigns, assign dedicated partitions.

Key Tradeoffs Summary

Monitoring & Observability

Failure Modes