Design ad-click aggregation

How do you count clicks exactly once when the pipeline can replay?

You do not stop the replay; you make it harmless. Each aggregator keeps its open counts and the click ids it has seen in local state and snapshots both, with its log offset, every ten seconds. After a crash it restores the snapshot and re-reads the log from that offset, which recomputes the same counts. Each row is written to the store as the whole count with a version, the offset of the last click in it, so a replayed write carries the same number and an older version never overwrites a newer one. At-least-once delivery plus an idempotent write is what exactly-once means here.

What happens to a click that arrives late?

A click is placed by the time it happened, not the time it arrived. A minute is first written when the watermark, the newest click time seen minus five seconds of normal disorder, passes its end. Its count stays open for five more minutes, and a late click inside that window updates the count and rewrites the row with a higher version. A click older than that goes to a late log, and the nightly recount from the raw clicks folds it into the billing numbers.

How do you handle one ad that gets a huge share of the clicks?

Partitioning by ad id puts all of one ad on one aggregator thread, and a thread does two thousand clicks a second, which is four percent of the peak. An ad above that fills its partition. The fix is to split that ad's key by a hash of the click id into eight sub-keys: the pieces land on different partitions, each counts its piece, and the query sums them. Because a click and its duplicates share an id, they still meet in one place, so the dedupe keeps working.

Why not insert every click into a database and count with a query?

At fifty thousand clicks a second at the peak, every click becomes an insert and an index write, and every dashboard becomes a count over millions of rows on the same disks. One relational node takes a few thousand writes a second. Counting as the clicks stream past turns three million clicks in the busiest minute into two hundred thousand rows, fifteen clicks a row, and a dashboard reads one row per minute. The raw clicks are still kept, in the log and its archive, for the nightly recount.

Where should the aggregated counts live?

In a columnar store sorted by ad and minute. A dashboard reads one ad's minutes as one contiguous range, and a campaign report sums the count column across thousands of ads, which a column layout scans and compresses well. A rewritten row is appended with a higher version and the newest version wins on read and on merge, so the store never needs an in-place update. A row store would serve the single-ad read as well and lose on the reports.