Design an ad-click aggregation pipeline

An ad network records clicks. Advertisers want per-ad counts for each minute, within about a minute of real time. The ad server also needs running spend to stop an ad when its daily budget runs out. Billing must be exact, with no double counts and no lost clicks. Clients retry, so duplicate click events are common.

The big idea

Use two paths. The streaming path is fast and can be a little wrong. The batch path is slower and exact. The batch path is the one that bills advertisers.

The order of decisions matters. Give each click a unique ID at the source. Dedupe on that ID. Use event-time windows with explicit lateness rules. Write idempotently. Reconcile in batch.

Walking through it

Estimates first. These come from the requirement of 1 billion clicks per day.

Clicks:       1e9 / 86,400 = about 11,600 per second on average
Peak:         5x average = about 60,000 per second
Event size:   about 200 bytes, so about 200 GB per day raw
Aggregates:   up to 500,000 rows per minute, before compaction

1. Ingest. The ad server generates a click_id when it serves the ad. Client retries reuse the same ID. Partition by ad_id. About 12 MB per second fits in a few dozen partitions.
2. Deduplicate. Flink keys the stream by click_id and keeps a one-hour time-to-live. One hour is about 42 million IDs, at roughly 50 bytes each, so about 2 GB of state, spread across tasks.
3. Filter fraud. Drop or flag bot clicks and clicks with no matching impression.
4. Aggregate. Key by ad_id. Use 1-minute tumbling windows, a 30-second watermark, and 5 minutes of allowed lateness. Events later than that go to a side output.
5. Sink idempotently. Upsert into ClickHouse keyed by (ad_id, minute). Replays overwrite rather than double count.
6. Pace budgets. A consumer keeps running spend per ad and warns the ad server when a budget is nearly spent. A small overshoot is acceptable for pacing.
7. Reconcile. A daily Spark job recomputes exact counts from the raw clicks in Iceberg. That output is the billing source of truth. If it differs from streaming totals beyond a threshold, it overwrites them and raises an alert.

Common mistakes

• Using processing time for business metrics. Lag and replays change the totals.
• Unbounded deduplication state. Without a time-to-live, the state grows until the job runs out of disk.
• Billing from the streaming totals. The batch reconciliation owns the money.

Check yourself

1. Why does the ad server, rather than the client, generate click_id? Retries reuse the same ID, so the deduplication step can drop repeats.
2. Which number is the billing source of truth? The daily Spark reconciliation output, which recomputes exact counts from raw clicks.

Key takeaways

• Give every event a unique ID at the source.
• Dedupe with keyed state that has a time-to-live.
• Use event-time windows, explicit lateness, and idempotent sinks.
• Use a batch reconciliation path for anything that involves money.

Further learning

The estimation method used above is covered in estimation.

For the architecture behind the two paths, see Lambda vs Kappa.