Design an ad click aggregator: a metrics and logging pipeline

In this article

1. Before you start
2. Why interviewers ask this
3. An ad click aggregator from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page is one step on the system design learning path.

Why interviewers ask this

An ad click aggregator tests whether you can keep a count right when the network repeats, delays and reorders messages, because advertisers are billed from these totals. The same skeleton is asked as an ad click aggregator or as a metrics and logging pipeline. A weak answer draws a queue and a database and says "exactly-once". A strong answer explains where duplicates come from, how a click is recognised twice, how late events are treated, and what the system does when the fast count and the true count disagree. The order to practise is: size the load, batch with ids, log, deduplicate, window, roll up, reconcile.

An ad click aggregator from first principles

Event time

Event time is the moment a click actually happened on the user's device, which can differ from the moment the click reaches your servers, and it is the time that counts must be grouped by.

Roll-up

A roll-up is a pre-computed total of many raw events for one key and one time bucket, such as clicks for one ad in one minute, stored so queries read one row instead of scanning millions.

In plain words

Imagine counting votes from polling stations by phone. Some stations phone twice because the line dropped. Some phone an hour late because the road was blocked. If you count every call, you count the same box twice. You also put the late box into the wrong hour. The fix has three parts. Each ballot box carries a unique number, so a repeat is recognised. You file each box under the hour the votes were cast. You recount from the sealed boxes at the end of the night. The analogy breaks in one place: the system never gets a final deadline, so it must decide how long to wait for stragglers.

An ad click aggregator is a pipeline that receives every click on every ad from many clients and serves per-ad totals by time bucket, with the promise that each click is counted once. The Dataflow Model paper (2015) takes the same stance: assume completeness is never known, that new data will keep arriving, and that old data may be retracted.

Step 1: Clarify what to count and size the load

Start by fixing the questions the system answers and the volume it carries, because every later choice follows from the numbers. Ask what is counted (valid clicks per ad, per minute), how fresh the totals must be (seconds for a dashboard, exact for billing), how long raw data is kept, and what happens to invalid clicks. State assumptions aloud. This page uses illustrative inputs: 10 billion clicks a day, a peak of three times the average, 100 bytes per raw event.

At 10 billion clicks a day the average is about 115,741 events a second and the peak about 347,222. The raw log grows by about 1 terabyte a day, or about 30 terabytes over a 30-day retention.¹ Those two numbers say the write path must be partitioned and the raw store cheap.

1 · Size the load2 · Batch with event ids3 · Durable log4 · Dedupe and window5 · Roll-up store6 · Batch recountReplay

Step-by-step description

1. About 115,741 clicks a second on average.
2. Each click carries an event id; batches are retried.
3. Every raw click is kept and replayable.
4. Drop repeats by id; count by event time.
5. Per-ad, per-minute totals serve the queries.
6. Recount the log and fix late-event gaps.

• At 10 billion clicks a day the average is about 115,741 events a second and the peak about 347,222.
• A click server or SDK batches clicks and attaches an event id, so a retried batch can be recognised.
• A durable log keeps every raw click.
• The stream job deduplicates by event id and counts by event time, with a watermark and an allowed lateness.
• A batch recount of the raw log corrects events the stream job dropped as too late.

Step 2: Batch clicks and attach an event id

A click server or client buffers clicks briefly and sends them as one batch, and every click carries a unique event id so a retried batch is recognisable. With batches of 20 clicks, peak requests fall from 347,222 to about 17,361 a second.¹

One caveat decides the design. A click usually navigates the user away, so a client-held batch can lose billable clicks, and a client-reported click can be forged. For billed clicks, send the user through a redirect server that records the click and its event id before redirecting, then sends batches to the collectors of Step 3. Client software development kit (SDK) batching, flushed when the page is hidden, is the metrics and logging variant.

Retries cause duplicates: a lost acknowledgement leaves the sender unsure the server saw the batch, so it resends with the same ids. The Apache Kafka 4.3 documentation says at-least-once delivery is the default, and a broker can deduplicate one producer's resends with a sequence number. A new request for the same click escapes that, so the event id is checked downstream.

Step 3: Collect into a durable log

Stateless collector servers validate each batch and append its clicks to a partitioned, replicated log that keeps every raw click. The log is the system's truth: it can be replayed into a new job, a bug fix or a recount. Partition by ad id so that all clicks of one ad land in one partition, which lets one stream task own that ad's windows and dedupe state.

A collector acknowledges the client only after the log write is durable, which is what makes the client's retry rule safe. The log also decouples speed: collectors only append, and a slow consumer falls behind without losing data. Raw clicks can age into cheap object storage after the hot retention ends, ready for the recount in Step 6. See the distributed rate limiter for what to do when one client floods the collectors.

Step 4: Deduplicate and count by event time, not arrival time

The stream job drops any click whose event id it has already seen and assigns the rest to the window of their event time. Counting every arrival is the error: in an illustrative simulation of 200,000 clicks with 5% of batches resent, counting arrivals gave 209,983, or 4.99% too many.¹

Deduplication needs state: 600 seconds of ids at the average rate is 69 million ids of 16 bytes, or 1.11 gigabytes of raw id bytes before hash-set overhead, and 3.33 at peak.¹ The Apache Flink 2.3 documentation explains how such state survives a crash: checkpoints snapshot it, and recovery restores the latest one and rewinds a replayable source.

Windows raise a second question: how long to wait. A window closes when the watermark passes its end plus an allowed lateness. In the simulation, 3% of batches were 3 to 10 minutes late. Windowed by arrival time, 19.19% of clicks landed in the wrong minute: 3.02% from late batches, the rest from the 10-second flush crossing a minute boundary. Per-minute totals are off by a net 0.99%, as misplaced clicks partly cancel.¹

Step 5: Store roll-ups and serve queries

The stream job writes one roll-up row per ad per minute to a store built for fast aggregate queries, and the write is an upsert on the key (ad id, minute) so a replayed window overwrites itself instead of adding twice. Hour and day totals are computed from the minute rows, not from raw clicks, which keeps dashboard queries cheap.

A query for the last 24 hours of clicks on one ad reads at most 1,440 minute rows (an illustrative count: 24 hours of 60 minutes). The store can be an online analytical processing (OLAP) database or a time-series store; the interview point is the shape: key, bucket, count, plus an upsert that is safe to repeat. Keep the raw log for investigation. A marketing page that shows one global total can read a cached sum of the minute rows, refreshed every few seconds, rather than hitting the store on each page view.

Step 6: Reconcile with a batch recount

A batch job recounts the raw log for closed hours, deduplicating by event id, and overwrites the roll-ups, so the recount corrects events the stream job dropped as too late. The fast path is allowed to be slightly wrong, because the slow path makes it exact later. In the simulation, a 60-second lateness dropped 6,038 clicks and the deduplicating recount restored all of them (a recount without deduplication would reproduce the 4.99% overcount, because the raw log holds the resent batches), giving 200,000 against a true 200,000.¹

Google's Photon paper (2013) describes a joining system for ad data that avoids duplicates at all times, keeps most events in near-real-time output, and reaches exactly-once semantics eventually. That is the same contract: fast and approximately right now, exact later. Say plainly in an interview which numbers are provisional.

Worked example

Load and accuracy both follow from the same few inputs, and the numbers below come from the script, not from memory.¹

Text description of the diagram

• The average is about 115,741 events a second and the peak about 347,222.
• Batches of 20 clicks give about 17,361 requests a second at the peak.

The accuracy half uses an illustrative simulation of 200,000 clicks over one hour on 500 ads with unequal popularity. Clients flush every 10 seconds, 5% of batches are resent after a lost acknowledgement, and 3% arrive 3 to 10 minutes late.

Text description of the diagram

• Counting arrivals gave 209,983, which is 4.99% too many.
• An allowed lateness of 60 seconds drops 3.02% of clicks, and 300 seconds drops 1.95%.
• A lateness of 600 seconds or a deduplicating batch recount gives 0% error.

The core of the stream job is short. It drops repeats, assigns a window from the event time, and drops a click only when its window closed more than the allowed lateness ago:

seen = set()
# watermark approximated by arrival time: a click is late if its window closed before it arrived
for arrival_time, click in stream:                  # arrival order
    if click.id in seen:
        continue                                    # a resent batch
    seen.add(click.id)
    window_end = (click.event_time // 60 + 1) * 60
    if window_end + allowed_lateness <= arrival_time:
        late.append(click)                          # left for the batch recount
        continue
    counts[(click.ad, click.event_time // 60)] += 1 # upsert key: (ad, minute)

With event-time windows, a lateness of 60 seconds dropped 3.02% of clicks as too late, 300 seconds dropped 1.95%, and only 600 seconds dropped none.¹ The numbers come from one seeded run and are illustrative of the effect; the shape, a few percent of error from each cause, is the lesson. A real job keeps seen in checkpointed, expiring state, not an ever-growing set.

Trade-offs and edge cases

Every choice in this design trades freshness, completeness and cost, so name the trade-off before the number. Three knobs matter most: the allowed lateness, the batch size and the partition key.

Freshness against completeness. A longer allowed lateness gives more complete numbers and later ones; a shorter lateness gives fast numbers and relies on the recount. Billing wants the recount; a live dashboard wants the short lateness.

Exactly-once is a property of the whole path. The Kafka documentation notes that exactly-once is supported within Kafka Streams and with the transactional producer and a read-committed consumer, but other destination systems need cooperation. The roll-up upsert of Step 5 is that cooperation: a repeat write has the same result.

Hot keys. One viral ad sends a large share of all clicks to one partition and one task. A common fix is to split a hot ad's key into several sub-keys, count each, and add the sub-totals when querying. Derive the sub-key from the event id (a hash modulo the number of sub-keys), not at random, so a resent click always lands on the same sub-key and is still deduplicated.

Invalid clicks. Bot and fraud filtering is a separate stage that sits before billing and may remove clicks after the fact, which is another reason the recount path must be able to restate numbers; see system design for a payment system for the matching reconciliation idea in money.

Cardinality. The number of roll-up rows is the number of distinct (ad, minute) pairs with at least one click, not the number of clicks, so rows grow with active ads and time, not with traffic spikes. The same shape serves a logging pipeline; see data engineering.

How to answer it in an interview

The spoken structure is: numbers, then batching with ids, then log, then dedupe and windows, then roll-ups, then the recount and what is provisional.

Common mistakes

"Use exactly-once delivery and you are done." (wrong) Delivery promises inside one component do not cover a client resending through a new request. (right) "I make the counting idempotent with event ids and upserts, so duplicates are harmless."

"Count messages as they arrive." (wrong) Retries inflate the count and late batches land in the wrong minute. (right) "I dedupe by id and window by event time with a watermark."

"I close each window only when every click has arrived." (wrong) Waiting for every straggler delays the dashboard forever. (right) "I choose an allowed lateness and let the batch recount fix the rest."

"Write the raw clicks straight to the database." (wrong) The write rate and the 1 terabyte a day overwhelm a transactional store. (right) "Append to a log, aggregate in a stream job, store roll-ups."

"Partition by event id." (trade-off) Partitions stay balanced and dedupe stays local, but one ad's clicks scatter across tasks, so a shuffle by ad must follow before windowing. (right) "I partition by ad id, and split hot ads into sub-keys derived from the event id."

Follow-up questions

How do you get exactly-once counts in a click pipeline?

Make counting idempotent instead of hoping for perfect delivery: unique event ids, a deduplicating stream job with checkpointed state, and upserts keyed by ad and minute. Replayed work then overwrites itself, and the recount repairs what the fast path drops.

What happens when a click arrives after its window has closed?

The fast path drops it, or counts it in a correction if your design supports one, and the next batch recount adds it. In the simulation, a 60-second lateness dropped 3.02% of clicks that the recount restored.¹

How do you handle one viral ad that overloads a single partition?

Split the ad's key into several sub-keys derived from the event id, each counted independently, and sum the sub-totals when the roll-up is read. This spreads the load at the price of a slightly more complex read.

How would you show the last 24 hours of totals on a page?

Read the minute roll-ups for the ad, at most 1,440 rows, or a cached sum of them refreshed every few seconds. Label the most recent minutes provisional, since late clicks can still arrive.

What changes if the same pipeline carries logs and metrics instead of clicks?

The shape stays: batch at the source, durable log, windowed aggregation, roll-ups. The keys change from ad id to dimensions such as service, region or customer, and the cost concern shifts to cardinality, the number of distinct key combinations. A chat system (designing a chat system) also moves messages through a log, but its problem is ordered delivery and fan-out rather than windowed counts. A web crawler needs the same kind of duplicate filter.

Sources

1. Apache Kafka documentation: Design, message delivery semantics — Apache Kafka 4.3 documentation (date = read date), 25 Oct 2026
2. Stateful Stream Processing (Apache Flink 2.3) — Apache Flink documentation (date = read date), 25 Oct 2026
3. Photon: Fault-tolerant and Scalable Joining of Continuous Data Streams — SIGMOD 2013 (Google), 22 Jun 2013
4. The Dataflow Model: A Practical Approach to Balancing Correctness, Latency, and Cost in Massive-Scale, Unbounded, Out-of-Order Data Processing — VLDB 2015 (Akidau et al.), 31 Aug 2015