Design an Ad Click Aggregator

[!NOTE] This module explores the core principles of Design an Ad Click Aggregator, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

1. Problem Statement

In the world of online advertising, every click counts—literally. An Ad Click Aggregator is a system that processes billions of click events from various websites and mobile apps to provide real-time reports for advertisers and billing services.

The Challenge:

  • Scale: Handling billions of clicks per day.
  • Precision: We cannot overcharge (double counting) or undercharge (missing clicks).
  • Latency: Advertisers want to see their budget consumption in near real-time (< 1 minute).

Real-World Examples:

  • Google Ads: Real-time bidding and budget tracking.
  • Facebook Ads: Aggregating impressions and clicks for campaign performance.

[!TIP] Analogy: The Toll Booth Imagine a massive highway with 1,000 lanes. Every time a car passes, you need to count it to bill the driver. If you miss a car, you lose money. If you count a car twice because it swerved between lanes, the driver gets angry. This system is the digital version of that toll booth, but for billions of “cars” (clicks).

2. Requirements & Goals

Functional Requirements

  1. Aggregate Cicks: Count clicks per ad_id over time windows (e.g., 1 minute, 1 hour).
  2. Filter Fraud: Detect and filter out bot clicks or duplicates.
  3. Queryable Results: Provide an API for internal dashboards to query aggregated stats.

Non-Functional Requirements

  1. Exactly-Once Processing: Every click must be counted exactly once, despite failures.
  2. High Throughput: Support up to 100,000 clicks/sec.
  3. Low Latency: End-to-end delay from click to dashboard should be < 5 seconds.
  4. fault Tolerance: The system must recover from node crashes without data loss.

3. High-Level Architecture

We use a Kappa Architecture (Stream Processing) to handle both real-time counts and re-processing.

User Clicks
Log Ingestor
(HTTP API)
Message Bus
(Kafka)
Flink App
(Windowed Agg)
Fraud Filter
(Redis Lookup)
OLAP DB
(ClickHouse)

4. Deep Dive: Exactly-Once Processing

In a distributed system, network timeouts or crashes can cause a message to be processed twice or lost. We solve this using Kafka Transactions and Flink Checkpointing.

The Mechanism: Two-Phase Commit

  1. Source: Flink reads an event from Kafka and gets its offset.
  2. Processing: Flink updates the count in its state (memory).
  3. Sink: Flink prepares to write to the database (ClickHouse).
  4. Checkpoint: Flink triggers a snapshot of its state and current Kafka offsets to S3.
  5. Commit: Once the snapshot is successful, Flink commits the Kafka offset.

[!IMPORTANT] If a node crashes at Step 3, Flink restarts from the last successful Checkpoint (Step 5), essentially “rewinding” the stream and reprocessing precisely from where it left off.

Click to view Flink Logic (Pseudo-code) 
DataStream<ClickEvent> clicks = env.addSource(new KafkaSource<>(...));

clicks
  .keyBy(event -> event.adId)
  .window(TumblingEventTimeWindows.of(Time.minutes(1)))
  .reduce(new CountAggregater())
  .addSink(new JDBCSink(...)); // Committed on Checkpoint

5. Scaling the Data Store

Standard relational databases (PostgreSQL) struggle with the high-velocity append-only writes needed for aggregation.

Store Type Performance Optimization
OLTP (MySQL) 🔴 Slow for aggregates Requires heavy indexing; bottlenecked by LOCKS.
NoSQL (Redis) 🟢 Very Fast Great for real-time counters, but hard to query historical trends complexly.
OLAP (ClickHouse) 🟣 The Winner Columnar storage; stores data in compressed chunks. Ideal for SELECT SUM(count) FROM clicks.

Optimization: Pre-Aggregation

Instead of writing 100,000 raw clicks to ClickHouse, the Flink Application buffers them in memory and writes 1 aggregated row per Ad per Minute. This reduces DB writes from 100k/s to ~100/s.

6. Fraud Detection & Deduplication

How do we prevent a user from clicking an ad 50 times in a row?

  1. Deduplication Layer:
    • Store a unique hash of (user_id, ad_id, timestamp) in Redis with a TTL of 1 minute.
    • If the hash already exists, discard the click.
  2. Rate Limiting:
    • Allow a maximum of 5 clicks per user per IP per minute.

7. Interview Gauntlet

  1. How do you handle late-arriving events?
    • Ans: Use Watermarks in Flink. We allow a “grace period” (e.g., 2 seconds). Events arriving later than that are either discarded or written to a “Late Arrival” side-output for auditing.
  2. What happens if your Flink job fails?
    • Ans: The system recovers from the last Checkpoint stored in persistent storage (e.g., S3). It replays messages from Kafka from the saved offset.
  3. Why not just use MapReduce?
    • Ans: MapReduce is Batch Processing. It would take minutes or hours to get results. We need Stream Processing for real-time aggregation.

8. Summary

  • Ingest: Use Kafka to buffer billions of events.
  • Process: Use Apache Flink for windowed aggregation with Exactly-Once guarantees.
  • Store: Use a Columnar OLAP database like ClickHouse for fast historical queries.
  • Scale: Pre-aggregate data in-memory to minimize database write pressure.