Design an Ad Click Aggregator: Exact Counts at Streaming Speed

Four capabilities define the product. A click must forward the user to the advertiser's landing page, typically via an HTTP 302. That same click must be recorded with the ad it belongs to, a timestamp and some identity of the clicker. Advertisers then need to read click totals per ad at one-minute resolution, over any window from the past hour to the past year.

Leaving out ad selection, targeting and invoicing keeps the scope honest. Those systems consume the counts we produce, but none of them change how clicks are captured or aggregated. Declaring them out of scope early frees time for the pipeline and storage questions where the real difficulty lives.

Dashboards should render in well under a second even for a year-long range, which rules out any approach that touches raw events at read time. Freshness matters too: advertisers watching a campaign launch expect a click to be reflected within roughly a minute, so a nightly batch alone would not be enough.

Accuracy is the requirement that shapes everything else. Since spend is calculated per click, losing an event under-bills and counting it twice over-bills, and both erode trust. Finally, the traffic is bursty and global, with peaks around 50,000 clicks per second, so every stage needs a buffer that absorbs spikes without dropping data.

Assume one billion clicks per day. Spread evenly that is about 12,000 per second, but traffic clusters around events and time zones, so plan for peaks near 50,000. At roughly 200 bytes per event, raw storage grows by about 200 GB a day, which adds up to tens of terabytes per year. That volume is cheap to archive in object storage but expensive to scan.

The figure that drives the design is about ten million active ads, each viewable at minute granularity. Counting raw clicks whenever a dashboard opens would mean scanning billions of rows per request. The only workable approach is to compute per-minute totals as clicks arrive and have queries read those small, precomputed rows instead.

Three entities are enough. The Ad holds the destination URL. The click event is the immutable raw record with ad ID, impression ID, timestamp and user. The minute count is the aggregated row keyed by ad and minute, and it is what the read path serves.

The redirect endpoint, GET /click/{ad_id}?imp={imp_id}, logs the event and answers with a 302 to the advertiser's URL; the impression ID becomes important for deduplication. The reporting endpoint, GET /ads/{ad_id}/metrics, accepts a start, an end and a granularity, and returns an array of time buckets with click totals. Keeping write and read paths on separate endpoints lets each scale independently.

The click service does as little as possible on the hot path: it publishes the event to Kafka and immediately returns the redirect, so the person clicking never waits on analytics. Kafka is keyed by ad ID and acts as a durable shock absorber; if downstream consumers slow down, events queue up instead of being dropped.

A Flink job consumes the topic, maintains one-minute counters per ad, and writes completed windows into a columnar OLAP store such as ClickHouse or Pinot. In parallel, a separate consumer archives every raw event to S3. An hourly Spark job recounts from that archive and overwrites any minute where the streaming total disagrees, so the archive acts as the ledger of record.

Advertisers reach a query service that reads only from the OLAP aggregates, never from Kafka or S3. Separating roles this way means the fast path can be slightly imperfect for a few minutes, the batch path eventually makes it exact, and dashboard latency stays independent of how many raw clicks exist.

Keying Kafka on ad ID guarantees that every click for a given ad lands in one partition, so a single Flink subtask owns that ad's counters and no cross-machine merge is needed. Within the subtask, tumbling one-minute windows keep a tiny counter per ad per minute in memory, which is cheap even across millions of ads.

Windows are assigned by event time, the moment the click happened, rather than when it reached the server. A mobile client that buffers a click for thirty seconds should still credit the correct minute. Watermarks let Flink hold a window open for a short grace period, say a minute, before finalising it; anything arriving after that is left for the batch recount to fix.

When a window closes, its totals are upserted into the OLAP store keyed by ad and minute. Periodically, Flink checkpoints its consumed offsets together with all in-flight window state. After a crash it rewinds to that consistent snapshot, so counts resume as if nothing happened rather than restarting from zero or skipping ahead.

Exactly-once counting starts before the click. When an ad is rendered, the server attaches a signed impression ID, and the click endpoint verifies that signature. Fabricated or tampered IDs are rejected outright, which blocks the simplest form of click fraud and gives every legitimate click a natural deduplication key.

Flink keeps recently seen impression IDs in keyed state with a time-to-live, so a double tap or a browser retry is discarded instead of incrementing the counter. Because offsets and state are restored as a pair after failure, replaying a stretch of the topic cannot inflate totals. The sink is idempotent as well: upserting the row for a given ad and minute twice leaves the same value.

Even with all of that, edge cases slip through, such as events later than the watermark allows. The hourly Spark job recomputes totals from the raw archive and replaces any minute that differs. This reconciliation is what lets the team state with confidence that billed numbers match the underlying events.

Querying raw clicks directly offers unlimited flexibility, but a year-long chart would scan billions of rows and take seconds or minutes. Storing aggregates in Postgres is familiar, yet range scans across millions of ads and long time spans degrade quickly on a row-oriented engine.

A columnar OLAP database with rollups is the chosen option. Minute rows are retained for about thirty days, then compacted into hourly and daily rows that are kept for years. A one-year daily chart for one ad touches only 365 rows. Partitioning tables by day and sorting by ad_id means each query reads a narrow slice of disk, and caching popular dashboard queries for a few seconds absorbs refresh storms when many viewers open the same report. The extra operational burden of another datastore is worth it here.

Running both a streaming and a batch pipeline doubles the code to build, deploy and monitor, but it is the only way to get answers that are both fresh and auditable. The watermark length is a dial: waiting longer makes streaming totals more accurate, while waiting less makes dashboards feel more live.

Pre-aggregating by ad and minute makes reads very fast, but any new breakdown, such as by country or device, has to be added to the pipeline ahead of time. For a single viral ad that overwhelms one partition, salting the key across several sub-keys spreads the load, at the cost of a merge step that sums the partial counts before serving.