Design Ad Click Aggregator

Assume 4 billion clicks a day across about 2 million active ads. Traffic has a strong daily curve and spikes during major sporting events and shopping days. Each raw click event is about 300 bytes once enriched with ad, campaign and device fields.

There are two audiences. Browsers hit the click endpoint and must be redirected instantly. Advertisers and internal tools read aggregates.

The redirect service is deliberately dumb. It verifies the token signature, appends the event to an in-memory buffer, writes the 302 and moves on. A background thread flushes the buffer to Kafka every few milliseconds with acks from all in-sync replicas. If Kafka is slow, the buffer grows; if the process dies, the last few milliseconds of clicks are the exposure, and the impression log lets you estimate how many were lost.

Kafka is partitioned by ad_id so all clicks for one ad reach the same Flink task, which keeps per-ad state local. A single Flink job does three things in order: drop duplicates, score fraud, and aggregate into one-minute event-time windows. Closed windows are written to ClickHouse and the running spend per campaign goes to a low-latency store the ad server reads before every auction.

A second path reads the same raw events from object storage. Hourly and daily batch jobs recompute the aggregates with slower, more thorough fraud models and overwrite the provisional rows. Billing reads only from that reconciled data. The two paths agree on schema and keys, so the dashboard simply shows whichever version is newest for a given window.

Ordering by advertiser and campaign first means a report for one campaign reads a narrow, contiguous slice of each daily partition. A materialised view sums minute rows into an hourly table with the same key minus the minute, kept for two years. The batch path writes the same keys with a version taken from its run epoch, which is always higher than anything the stream produced, so reconciled numbers replace provisional ones without a separate delete step.

Bucket by when the click happened, not when it reached the job, or a two-minute Kafka hiccup will shift money from one minute to the next and make hourly charts lie. Mobile SDKs batch clicks offline and deliver them minutes later, so events arrive out of order.

A watermark is the job's estimate that it has seen everything up to time T. Set it to the maximum event time seen minus a bounded delay, say 90 seconds. Keep each window open for an extra two minutes of allowed lateness after the watermark passes, and emit an updated row if a straggler arrives. Anything later than that goes to a side topic. It is not lost: the batch path counts it in the next reconciliation.

End-to-end exactly-once is built from three at-least-once pieces plus idempotence. Kafka delivers events at least once. Flink restores state from its last checkpoint after a crash and replays from the matching offsets, so its internal counts are consistent. The sink is where double counting would sneak in, so make writes idempotent: every output row is keyed by (window_start, ad_id, country, device, placement) and carries a version number.

In ClickHouse, a ReplacingMergeTree on that key keeps only the highest version after merges, and queries use FINAL or an argMax pattern for recent windows. Replaying a window just writes the same key with the same or higher version. Nothing is added twice.

Upstream duplicates are a separate problem. A user double-clicks, or the redirect retries a Kafka send. The click token carries an impression_id, and the job keeps a one-hour keyed set of seen click ids per ad in RocksDB. Duplicates inside that window are dropped before counting; the batch path catches any older ones.

Partitioning by ad is natural, until a soft drink runs a halftime ad and one key receives 40,000 clicks a second. One Flink task cannot keep up and its backpressure slows the whole job. Fix it with a two-stage aggregate: append a salt from 0 to 15 to hot keys, pre-aggregate per salted key, then sum the sixteen partial counts in a second, much lighter stage. Detect hot keys from the previous minute's counts so the salt only applies where needed.

Run cheap rules first and expensive checks later. In the stream: drop clicks with bad or expired token signatures, clicks from known data-centre IP ranges, clicks arriving under 300 ms after the impression, and more than a few clicks per device per ad per minute. These catch the noisy majority. The batch path then runs models over hours of history, finding click farms that look normal one event at a time but form tight clusters across devices and campaigns.

Invalid clicks are counted, not discarded. Advertisers see them as a separate column, and the batch job can promote a click from invalid to valid if a rule was too aggressive. Credits issued after the fact are entries in the billing ledger, never edits to old invoices.