Design an ad click aggregator

“Design a system that counts ad clicks. Advertisers want to see clicks per ad in near real time, and we bill them per click.”

Counting is the easy part. The hard part is that every count is money. A click counted twice overcharges an advertiser. A click lost undercharges them. And a bot’s click is fraud, whether it is counted once or not. All of that has to hold while clicks arrive late, out of order, twice, and at tens of thousands a second.

So this page starts with one service and one table, and lets the numbers grow it. Then it tests the result against late clicks, what “counted exactly once” really means, a viral ad, regions and fraud.

The framework

1. 01Answer
2. 02Scope
3. 03Numbers
4. 04Build
5. 05Stress
6. 06Failures
7. 07Wrap up

The answer in one minute

If you only have a minute, this is the design. The rest of the page explains each part.

1. Save the click before the redirect. The click server writes each click to Kafka, on two machines, before it sends the user on. A lost click is lost money.
2. Remove duplicates by impression ID. Each shown ad has a signed ID. A double click, a retry and a replay all carry the same ID, so each shown ad is billed at most once.
3. Count on the time of the click. A stream job counts clicks per ad per minute. It counts by when the click happened, not when the job read it. So a replay lands in the same minutes.
4. Write totals, not increments. “Ad 991204 had 413 clicks in 14:31”, never “plus one”. Writing it twice changes nothing.
5. Bill from a daily recount. The same counting code runs again over the full archive each night. Invoices use that number, and the stream is checked against it.

Scope it

Four questions decide the design: do we bill on these counts, how fresh, how late can a click arrive, and do we own the redirect.

Interviewerminute 0

Design an ad click aggregator. Advertisers see clicks per ad, and we bill per click.

What they are testing

Whether you find out early that counts are money, and how fresh and how exact they must be, because those two decide the whole pipeline. Three minutes is right. Starting with “Kafka and Flink” before asking whether billing depends on the numbers is the red flag.

These four questions matter most:

• Do we bill on these counts? Yes, about $0.50 a click. So the numbers must be provable.
• How fresh? Dashboards within 2 minutes. That means a stream, not an hourly batch.
• How late can a click arrive? Seconds usually, hours after an outage. That decides how long a minute stays open.
• Do we own the redirect? Yes. The click goes through our server, which then sends the browser on with a 302. So we have our own clock and our own record of every click.

From these answers we make five promises:

• Fast redirects. Under 20 ms of server time at p99, saving the click included, and up 99.99% of the time.
• Never lost. A click is on two machines in two zones before its redirect returns. If that is impossible, it’s on the click server’s own disk.
• Fresh. Dashboard counts within 2 minutes of the click.
• Counted once. Each valid shown ad is billed exactly once. Final counts can be rebuilt from the raw log.
• Fast queries. 30 days of a campaign in under 500 ms.

We leave out the ad auction and serving, conversion tracking (a follow-up), and invoicing itself, which is a payment system that uses our final counts.

Go deeperMore questions, and what each changes1 min

Question	We assume	If the answer were different
How many clicks?	1 billion a day, 2 million active ads	Ten times more makes hot-ad splitting and regional ingest day-one work
Analytics only?	No, billing	At-least-once counting and no recount, like a URL shortener
Hourly dashboards OK?	No, 2 minutes	A batch job alone would do
The ad SDK reports clicks?	No, our redirect	We lose server timestamps and must trust clients
Clicks days late?	No, hours at most	Invoices would wait days
What do advertisers query?	By ad, campaign or account; minute, hour or day	Slicing by any user detail means storing raw clicks in the query store too

The numbers that decide it

Now we work out the numbers, and the one that sets the tone is money.

Assumptions

Clicks a day

1 billion

Peak over average

5×

Bytes per click event

500 bytes

Hottest ad’s share of clicks

10%

Stream dedup window, minutes

60

Rollup rows per click

50%

Years to keep raw clicks

5 years

Clicks12,000/s58,000/s at peak

1 billion a day ÷ 86,400 seconds, and evenings and big sports events push that five times.

So we need a fleet of click servers and a log in front of anything that counts.

Money$500Ma day; 0.1% error is $500K

At $0.50 a click, that is about $500 million a day. A 0.1% counting error is $500,000 a day.

So we need a way to prove the counts, not best-effort analytics.

Dedup state13 GBone hour of IDs

An hour of impression IDs at 64 bytes each, at peak.

So we keep it in the stream job's local state on SSD. A day would be 320 GB, so the stream checks one hour and the daily recount checks the whole day.

Rollups29,000/srow writes at peak

Most ads get a click every few minutes, so most minute rows hold one click: about half the clicks.

So we need a store that takes writes in large batches, not one row at a time.

Hot ads5,800/sone viral ad at peak

A viral ad can take 10% of all clicks. One stream task counts about 30,000 a second.

So we count by ad today, and split hot ads at ten times.

Kafka takes 29 MB/s at peak, and a week of it is about 11 TB. Try the calculator: at 200 million clicks a day, the rollups fit in Postgres. At 10 billion, the hottest ad outruns one stream task.

Change an assumptionReset to the page's numbers

Clicks a day

1 billion

Peak over average

5×

Bytes per click event

500 bytes

Hottest ad’s share of clicks

10%

Stream dedup window, minutes

60

Rollup rows per click

50%

Years to keep raw clicks

5 years

Clicks, average

12,000/s

Clicks, peak

58,000/s

A few dozen click servers; one Kafka cluster takes this easily.

Hottest ad at peak

5,800/s

One stream subtask (about 30,000 clicks/s) counts it alone.

Kafka ingress, peak

29 MB/s

Bandwidth is not the limit; partitions are sized for consumer parallelism.

Kafka storage, 7 days × 3 replicas

11 TB

A week of replay without touching S3, on a handful of brokers.

Dedup state at peak (64 bytes per id)

13 GB

Spread over the job’s subtasks in RocksDB on local SSD, checkpointed incrementally.

Rollup rows, peak

29,000/s

Past what one Postgres primary upserts: use a columnar OLAP store that ingests in batches.

Raw archive over the plan (Parquet, 5× smaller)

180 TB

Object storage, a few thousand dollars a month: keep it all for audits and replays.

Build the design

We start with the simplest design that is correct. Then we let the numbers break it, one at a time. Each step fixes only what broke, adds only the boxes that fix needs, and says what the fix costs. Throughout, follow one scenario: a user clicks an ad, and an advertiser opens their dashboard.

Step 1. One service, one table

The simplest version: the click service inserts one row per click into Postgres and returns the 302. Dashboards run GROUP BY ad, minute over that table.

Ad click aggregator · architecture

Stage 1: One service, one table

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 1 of 7

One service, one table

Why this stage

Nothing yet.

This is the smallest design that redirects a click and counts it.

What we add

A click service that inserts one row per click into Postgres and returns a 302, and a query API that runs GROUP BY over that table.

What it costs

Every redirect waits on a database insert, dashboards scan raw rows, and duplicates and bots are counted like real clicks.

At 58k clicks per second, Click servers is 580% busy. Stage 2 is the fix.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

It breaks at once. At 58,000 clicks a second, the server and the database are about six times past what they can do. Every redirect waits on an insert. It also counts every retry and every bot.

Step 2. Save the click before the redirect

The ad server signs a click token when it shows the ad. It holds the impression ID, ad ID, landing page and time, signed with a key the click servers share. So nobody can forge clicks for an ad they never saw, or use our domain to redirect anywhere they like.

The click server checks the token, and writes the click to Kafka. It waits for two brokers in two zones to confirm (acks=all). Only then does it send the 302. That costs 3 to 5 ms. If Kafka doesn’t answer within 10 ms, the server writes the click to a spool on its local disk. It redirects anyway, and sends the spool later. A Kafka outage costs freshness, never clicks.

A connector copies Kafka to object storage as Parquet files every few minutes. That archive is the record of every click. A first stream job reads Kafka and writes counts per ad per minute.

Ad click aggregator · architecture

Stage 2: Log first, count in a stream

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

12

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 2 of 7

Log first, count in a stream

Why this stage

At the 58,000 clicks/s peak, one Postgres taking every insert is almost 600% busy, and one click server is too.

What we add

A fleet of click servers that append each click to Kafka and redirect once the log has it, a raw archive in S3, and a stream job that counts clicks per ad per minute into Postgres.

What it costs

Counts lag by about a minute, and a crash or a retry can still count a click twice.

At 58k clicks per second, OLAP store is 145% busy. Stage 3 is the fix.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

Clicks are now safe. But a crash or a retry can still count a click twice, and every count is money.

Go deeperWhy the log comes first, and counting second1 min

The click servers could update counts directly, in Redis or a database. Writing every click to a log first looks like an extra step. It is the most important choice on the page.

A count is a summary. Once a click has been added into “ad 991204, 14:31, 37 clicks”, the click itself is gone. Nobody can ask later whether one of those 37 was a duplicate or a bot. A log keeps every click as it arrived. So the counting rules can change, a bug in the job can be fixed, and the counts can be computed again from the start. The daily recount in step 5 depends on exactly this.

The log also separates two speeds. Clicks arrive at whatever rate users click, with spikes during a big game. The counting job reads at its own pace. If it falls behind or restarts, clicks wait safely in Kafka for up to 7 days, and nothing upstream notices.

Step 3. Count once, on the time of the click

A Flink stream job reads Kafka, keyed by ad, and counts in one-minute windows. Four things can make a count wrong, and each needs its own fix:

• Duplicates. A double click, a browser retry and a replayed spool all carry the same impression ID. The job remembers IDs for an hour and drops repeats.
• Invalid traffic. Rules (data-center IPs, declared bots, too many clicks from one device) mark a click invalid. It is counted separately, never dropped, so a wrong verdict can be reversed.
• Time. A click belongs to the minute the click server received it, not the minute the job read it. The first stress test explains how.
• Restarts. Flink saves its state and Kafka positions together every 30 seconds, and replays from there after a crash. The sink writes absolute totals, so a rewrite is harmless. The second stress test explains it.

The core of the job for one ad, in plain code instead of Flink’s API:

JClickAggregator.javaPyclick_aggregator.pyC#ClickAggregator.csGoclick_aggregator.goTSclick-aggregator.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

// A click as the stream sees it. eventTimeMs is the click server's receive time, never the
// browser's clock, which can be wrong by hours. valid is the verdict of the inline bot rules.
record Click(String impressionId, String adId, long eventTimeMs, boolean valid) {}

// What the job emits: the absolute totals for one ad and one minute, never a "+1".
// An absolute total can be written twice safely; a delta cannot.
record WindowCount(String adId, long windowStartMs, long validClicks, long invalidClicks) {}

interface CountSink {
    void write(WindowCount count);
}

// One instance per ad key (Flink keyed state in production). Dedup, windows and lateness all
// run on event time, so replaying the same clicks gives the same counts.
class ClickAggregator {
    static final long WINDOW_MS = 60_000;

    private final long dedupMs;            // 1 hour: a second click on one impression is not billed
    private final long allowedLatenessMs;  // 10 minutes: late clicks still update their minute
    private final CountSink sink;

    // impression id -> event time. RocksDB state in production, about 13 GB at peak.
    private final Map<String, Long> seen = new HashMap<>();
    // window start -> {valid, invalid}. Kept until the window is past its allowed lateness.
    private final Map<Long, long[]> windows = new HashMap<>();
    private final Set<Long> fired = new HashSet<>();
    // Clicks too late for any open window. Not dropped: the daily recount picks them up.
    final List<Click> tooLate = new ArrayList<>();
    private final String adId;
    private long watermark = Long.MIN_VALUE;

    ClickAggregator(String adId, long dedupMs, long allowedLatenessMs, CountSink sink) {
        this.adId = adId;
        this.dedupMs = dedupMs;
        this.allowedLatenessMs = allowedLatenessMs;
        this.sink = sink;
    }

    void onClick(Click c) {
        long start = c.eventTimeMs() - Math.floorMod(c.eventTimeMs(), WINDOW_MS);
        if (start + WINDOW_MS + allowedLatenessMs <= watermark) {
            tooLate.add(c);                 // its window's state is gone; the batch counts it
            return;
        }
        // Dedup before counting: double clicks, browser retries and a click server replaying
        // its spool all carry the same impression id.
        if (seen.putIfAbsent(c.impressionId(), c.eventTimeMs()) != null) return;

        long[] n = windows.computeIfAbsent(start, k -> new long[2]);
        n[c.valid() ? 0 : 1]++;
        // A late click for a minute already emitted: emit the new total, which replaces the old.
        if (fired.contains(start)) emit(start, n);
    }

    // The watermark says "no click older than this is still expected". Only it closes windows,
    // so a backlog after an outage lands in the right minutes, not the minute it was read.
    void onWatermark(long wm) {
        watermark = Math.max(watermark, wm);    // watermarks never move backwards
        for (var e : windows.entrySet()) {
            long start = e.getKey();
            if (start + WINDOW_MS <= watermark && fired.add(start)) emit(start, e.getValue());
        }
        // Free state: windows past their lateness, and impressions older than the dedup window.
        windows.keySet().removeIf(s -> s + WINDOW_MS + allowedLatenessMs <= watermark);
        fired.removeIf(s -> !windows.containsKey(s));
        seen.values().removeIf(t -> t < watermark - dedupMs);
    }

    private void emit(long start, long[] n) {
        sink.write(new WindowCount(adId, start, n[0], n[1]));
    }
}

A double click and a replayed spool are billed once

Scroll or pinch to zoom, drag to pan

• Color key
• Database
• Queue or stream
• Service

Ad click aggregator · architecture

Stage 3: Counts you can bill on

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

123

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 3 of 7

Counts you can bill on

Why this stage

Clicks are billed at about $0.50 each.

A double-clicked ad, a retried request, a bot or a crashed job must not change an invoice.

What we add

Deduplication by impression id, inline invalid-traffic rules fed by bot signals, event-time windows with watermarks, and checkpoints with an idempotent sink.

What it costs

Gigabytes of keyed state to checkpoint, results that wait for the watermark, and a late-data policy to explain to advertisers.

At 58k clicks per second, OLAP store is 145% busy. Stage 4 is the fix.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

Step 4. A store built for the queries

At peak the job writes 29,000 rollup rows a second, past what one Postgres primary takes. And the queries are sums over thousands of ads and minutes, grouped by country, which suits a column store. We use Apache Pinot: it reads straight from a Kafka topic and can replace a row by its key. Rows are sorted by account and time, so a dashboard reads a narrow slice. Minutes fold into hours once an hour is past its lateness, and the daily recount writes the day table.

Ad click aggregator · architecture

Stage 4: A store built for the queries

Grow itTrace a request

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

1234

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 4 of 7

A store built for the queries

Why this stage

Minute rollups are still about one row per two clicks: 29,000 upserts/s at peak is 145% of a Postgres primary, and a 30 day campaign query scans millions of rows.

What we add

A columnar OLAP store (Apache Pinot) with minute, hour and day rollups, sorted by advertiser and time, that ingests the stream job’s output.

What it costs

A second kind of database to run, and rollups that are cheap to read but awkward to correct in place.

At 58k clicks per second, every part has headroom. Raise the traffic until something turns red.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

Follow one click, from the redirect to the store:

Ad click aggregator · architecture

Trace a request: A click and its redirect

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasionalHop in motionHops doneResponse

Play

PreviousNext

A click and its redirect: step 1 of 5

Ad viewers to Load balancer

The browser follows the ad’s click URL, ad.example/c/{token}. The token carries the impression id, ad id and landing page, signed by the ad server when it rendered the ad.

2 ms of a 20 ms budget

1. Ad viewers → Load balancer2 ms
2. Load balancer → Click servers1 ms
3. Click servers → Kafka4 ms
4. Kafka → Stream jobasync
5. Response

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

Step 5. Bill from a daily recount

The stream is right almost always. At $500 million a day, that is not enough to invoice on. Its duplicate check covers an hour, very late clicks miss their minute, and the strongest fraud signals only exist after the day is over. So invoices come from a daily recount.

The same Flink job runs again in batch mode, over yesterday’s archive files. Its duplicate check covers the whole day, and the fraud model’s verdicts are joined in. The counting rules are one library with one version number. That number is saved on every final row, so an auditor can rerun it and get the same answer.

Each morning, the recount is compared with the stream, per account, under the same rules. They should agree within 0.1%. An account off by more than 0.5% pages someone, because that is a bug. Then the day is marked final, and invoices bill that number and nothing else.

Ad click aggregator · architecture

Stage 5: Reconcile against the raw log

Grow itTrace a request

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

12345

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 5 of 7

Reconcile against the raw log

Why this stage

A stream that is right 99.9% of the time is still $500,000 a day wrong at this volume.

Invoices need a number computed once, from complete data, that an auditor can reproduce.

What we add

A daily reconciler that recounts every click from the raw archive with a 24 hour dedup window and final fraud verdicts, writes the final counts and flags any drift.

What it costs

Billing waits a day, and the batch and stream paths must share their counting rules or they disagree.

At 58k clicks per second, every part has headroom. Raise the traffic until something turns red.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

Follow an advertiser’s query:

Ad click aggregator · architecture

Trace a request: An advertiser’s dashboard

A click and its redirectAn advertiser’s dashboard

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasionalHop in motionHops doneResponse

Play

PreviousNext

An advertiser’s dashboard: step 1 of 4

Advertisers to Query API

GET clicks for campaign 81 over the last 30 days, by day and country. The API authenticates the advertiser and adds account_id to every filter, so no query can read another account’s rows.

20 ms of a 500 ms budget

1. Advertisers → Query API20 ms
2. Query API → OLAP store120 ms
3. OLAP store → Query API5 ms
4. Response

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

This is enough for a mid-level answer. Kafka before the redirect. A stream job that removes duplicates and counts on event time. A column store. And a daily recount for billing. The stress tests are what make it a senior answer.

Go deeperThe API2 min

GET /c/eyJpbXAiOiI4ZjNh...Q.hmac-sha256-sig

302 Found
Location: https://shoes.example/autumn?utm_source=ads
Cache-Control: no-store

A bad signature gets 400. no-store makes every click reach us. Signing keys rotate across ad servers and click servers together. Old keys stay valid for a day, so a rotation never rejects clicks on ads already shown.

GET /v1/accounts/42/clicks?campaign=81&from=2026-09-29&to=2026-10-28&granularity=day&groupBy=country
Authorization: Bearer <advertiser token>

200 OK
{
  "series": [
    { "bucket": "2026-10-27", "country": "US", "valid": 18234, "invalid": 412, "status": "final" },
    { "bucket": "2026-10-28", "country": "US", "valid": 9102,  "invalid": 377, "status": "provisional" }
  ],
  "asOf": "2026-10-28T14:31:00Z"
}

status says which numbers can still change, and asOf is how far the stream has counted, so a half-counted minute never looks complete. Minute detail is allowed up to 24 hours and hour detail up to 31 days, so no query asks the store for a million rows. The API scopes every query to the caller’s account, never to a parameter the caller sends.

Go deeperThe tables, and the options we turned down2 min

-- From the stream. Replaced by key; kept 30 days.
CREATE TABLE ad_clicks_minute (
  account_id   BIGINT,
  campaign_id  BIGINT,
  ad_id        BIGINT,
  minute       TIMESTAMP,
  country      CHAR(2),
  device       VARCHAR(8),     -- mobile, desktop, tablet, ctv
  valid        BIGINT,
  invalid      BIGINT,
  PRIMARY KEY (ad_id, minute, country, device)
);  -- sorted by (account_id, minute), so one account's range is one narrow scan

-- Folded from minutes once an hour is past its lateness; kept 2 years.
CREATE TABLE ad_clicks_hour (/* same columns, hour instead of minute */);

-- Written only by the daily recount; kept 7 years. Invoices read this and nothing else.
CREATE TABLE ad_clicks_day_final (/* same columns, day, plus rules_version */);

Account and campaign IDs are copied onto every row, because every query filters by account.

Saving the click	Redirect cost	Problem
Insert into a database	5 to 10 ms	The database takes every click
Buffer in memory, send in batches	Under 1 ms	A crash loses seconds of clicks, which is money
Kafka with acks=all, disk spool	3 to 5 ms	Spools to watch, and duplicates when they replay

Counting with	Problem
INCR in Redis per click	Can’t replay, a retry counts twice, no event-time windows
An hourly batch	Two hours stale
The column store alone	Removing duplicates and updating a closed minute are hard to express
A stream job with saved state	One more distributed system to run

Store	Fit
Postgres	Below about 20,000 rows a second
Cassandra	Fixed time series per ad; no slicing by country
ClickHouse	Strong, but replacements settle at merge time, so queries need FINAL
Pinot	Reads from Kafka, replaces rows by key

Stress it

The design is complete. Now we test it where counting money is hard. Clicks that arrive late. What “exactly once” really means. One viral ad. Clicks from every region. And fraud. The viral ad and the regions each add a few boxes.

Time, and clicks that arrive late

Interviewerminute 20

The job was down for 20 minutes and is now catching up. Which minute do those clicks land in?

What they are testing

Whether you know event time from processing time, and what a watermark does and doesn’t promise.

Say we count by the time the job reads a click. Then 20 minutes of backlog land in one minute. We get one spike, 20 empty minutes, and a different answer on every replay. Now count by the time the click happened, called event time. Each click lands in its own minute.

A watermark is the job saying “I expect no more clicks older than T”. When it passes the end of a minute, that minute is sent out. Each Kafka partition’s watermark is the newest time it has seen, minus 10 seconds. The job’s watermark is the lowest of its inputs. Event time is the click server’s clock, so 10 seconds covers small clock differences between servers.

A watermark is a guess. A replayed spool delivers clicks behind it. So:

• send each minute on time;
• keep its state 10 minutes more, and send the full new total for each late click;
• send anything later to a side output that only feeds a metric. Those clicks are in the archive, so the daily recount bills them.

One quiet partition, one late click

Scroll or pinch to zoom, drag to pan

• Color key
• Database
• Queue or stream
• Service

A partition with no traffic would hold the job’s watermark still, and no minute would close. So a partition that is quiet for 60 seconds is marked idle and left out.

Common mistake

"A watermark means no more late data"

A watermark is an estimate. Flink, Beam and Dataflow all expect data behind it, which is why they offer allowed lateness and side outputs. And never use the browser’s clock as event time: bots control it.

Go deeperOther policies for late clicks1 min

Option	Late click	Cost
Drop after the watermark	Lost	Lost money, and nothing tells you how much
Wait longer (a 10 minute watermark delay)	Counted	Every dashboard is 10 minutes stale
Allowed lateness plus a side output	Updates its minute, or goes to the recount	A minute’s count can rise for 10 minutes
Hold windows open for a day	Counted	A day of window state for 2 million ads

Exactly-once, stated precisely

You keep saying exactly-once. What exactly is guaranteed, and by what?

No network delivers a message exactly once. What we promise is something you can check. Each valid shown ad adds exactly one click to the published count for its minute, whatever retries, crashes and replays happen. Some clicks are processed twice along the way. Three mechanisms cover three different gaps.

1. Duplicates in the input: the impression ID. Kafka’s exactly-once features can’t help here, because a double click is two genuine events.
2. Restarts: checkpoints. A checkpoint saves state and Kafka positions as one snapshot. On restore, state rolls back and input replays from the matching position, so state counts each event once. But windows sent out after the snapshot are sent again.
3. Repeated output: a sink that is safe to repeat.

Each write says “ad 991204 had 413 valid clicks in 14:31”, never “plus one”. Writing it twice changes nothing. A replay can briefly send an older total, 412 after 413, so the sink keeps the larger value, and a dashboard never moves backwards. That is sound only because every total is the full, deduplicated count for its minute, which only grows. In Postgres it is GREATEST in ON CONFLICT. In Pinot it is a partial-upsert table with the MAX rule on the count columns.

JMaxUpsertStore.javaPymax_upsert_store.pyC#MaxUpsertStore.csGomax_upsert_store.goTSmax-upsert-store.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

// The sink side of exactly-once. Flink restores state and Kafka offsets together from a
// checkpoint, so after a crash it re-emits windows it may already have written. Instead of a
// two-phase commit, every write is an upsert that keeps the larger total:
//
//   INSERT INTO ad_clicks_minute (ad_id, minute, valid, invalid) VALUES (?, ?, ?, ?)
//   ON CONFLICT (ad_id, minute) DO UPDATE SET
//     valid   = GREATEST(ad_clicks_minute.valid,   EXCLUDED.valid),
//     invalid = GREATEST(ad_clicks_minute.invalid, EXCLUDED.invalid);
//
// Writing the same total twice changes nothing (idempotent). A replay that briefly re-emits an
// older, smaller total cannot move a dashboard backwards (monotonic). The price: this table can
// never be corrected downwards in place, so fixes after a bug go to a new table version.
class MaxUpsertStore implements CountSink {
    private record Key(String adId, long windowStartMs) {}

    private final Map<Key, long[]> rows = new ConcurrentHashMap<>();

    @Override
    public void write(WindowCount c) {
        rows.merge(
                new Key(c.adId(), c.windowStartMs()),
                new long[] {c.validClicks(), c.invalidClicks()},
                // merge runs atomically per key, like the row lock behind ON CONFLICT
                (old, neu) -> new long[] {Math.max(old[0], neu[0]), Math.max(old[1], neu[1])});
    }

    long valid(String adId, long windowStartMs) {
        long[] r = rows.get(new Key(adId, windowStartMs));
        return r == null ? 0 : r[0];
    }
}

A crash after a checkpoint, and the replay

Scroll or pinch to zoom, drag to pan

• Color key
• Database
• Queue or stream

Common mistake

"Kafka exactly-once means each click is counted once"

Kafka’s idempotent producer and transactions stop the pipeline copying a record it already has, inside Kafka. They know nothing of a user clicking twice, a spool replay, or a sink outside Kafka. End to end, it takes three things. A duplicate key from the source. State that can be replayed. And a sink that is safe to repeat. Missing any one gives at-least-once with extra steps.

Go deeperSinks compared1 min

Sink	Safe to replay	Visible after	Problem
Add a change	No	Seconds	Every replay adds the same clicks again
Transactional (two-phase commit to Kafka)	Yes	The next checkpoint, 30 s	Results wait for checkpoints; readers need read_committed
Write absolute totals, keep the larger	Yes	Seconds	Totals can never be corrected downwards in place

A plain Pinot full upsert is wrong here. It keeps the row with the largest time column. That’s equal for 412 and 413, so the replayed older total can win. Transactional sinks are right when the reader can’t handle repeats, such as one that adds changes. They cost a checkpoint interval on every result.

One viral ad

A Super Bowl ad takes 10% of all clicks, and the job is keyed by ad. What happens to that key?

Keying by ad sends every click of one ad to one task. At ten times the load, the hottest ad brings 58,000 clicks a second to a task that counts about 30,000. It falls behind, its watermark stalls, and since minutes close on the lowest watermark, every ad’s counts stop.

The fix: split every ad 16 ways, then merge. Phase 1 keys by ad and a salt: a stable hash of the impression ID, modulo 16. Each part counts exactly as before. Phase 2 keys by ad. It keeps the largest total seen from each of the 16 parts, and sends their sum. It replaces a part’s total, never adds it, because a late click makes phase 1 resend its whole total.

Ad click aggregator · architecture

Stage 6: Hot ads

Grow itTrace a request

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

123456

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 6 of 7

Hot ads

Why this stage

Forced only at the 10x mark.

At today’s peak the hottest ad is 5,800 clicks/s, 19% of one subtask.

At 580,000 clicks/s it is 58,000, all keyed to one subtask that handles about 30,000.

What we add

Two-phase aggregation: clicks are counted per (ad, salt), the salt a hash of the impression id, then merged per ad by keeping the latest total from each salt and summing.

What it costs

One more shuffle and a few hundred milliseconds of extra latency on every result.

At 58k clicks per second, every part has headroom. Raise the traffic until something turns red.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

JSalting.javaPypartial_merger.pyC#Salting.csGopartial_merger.goTSpartial-merger.ts

JavaPythonC#GoTypeScript

JavaPythonC#GoTypeScript

// Two-phase aggregation for hot ads. Phase 1 counts per (ad, salt), so one viral ad is spread
// over SALTS subtasks. Phase 2 merges the partial totals per ad.
final class Salting {
    static final int SALTS = 16;

    // The salt comes from the impression id, not from a random number. Duplicates of one
    // impression land on the same partial, so dedup stays local, and a replay sends every click
    // to the same partial it went to the first time, so the totals come out the same.
    // FNV-1a rather than a platform hash: the salt must be identical on every machine and every
    // run, and some runtimes randomize string hashes per process.
    static int saltOf(String impressionId) {
        int h = 0x811C9DC5;
        for (byte b : impressionId.getBytes(StandardCharsets.UTF_8)) {
            h ^= (b & 0xFF);
            h *= 0x01000193;
        }
        return Math.floorMod(h, SALTS);
    }

    // The phase 1 key. A Flink job keys by this string; ClickAggregator runs unchanged per key.
    static String partialKey(String adId, String impressionId) {
        return adId + "#" + saltOf(impressionId);
    }
}

// Phase 2, keyed by ad only. Each partial arrives as an absolute total for its salt, so the
// merger keeps the largest total per salt and emits the sum: still idempotent, still monotonic.
class PartialMerger {
    // window -> [salt] -> totals. Expired with the same lateness rule as ClickAggregator.
    private final Map<Long, long[][]> byWindow = new HashMap<>();
    private final CountSink sink;

    PartialMerger(CountSink sink) { this.sink = sink; }

    void onPartial(String adId, int salt, WindowCount partial) {
        long[][] parts = byWindow.computeIfAbsent(
                partial.windowStartMs(), k -> new long[Salting.SALTS][2]);
        parts[salt][0] = Math.max(parts[salt][0], partial.validClicks());
        parts[salt][1] = Math.max(parts[salt][1], partial.invalidClicks());
        long valid = 0, invalid = 0;
        for (long[] p : parts) { valid += p[0]; invalid += p[1]; }   // 16 adds, not 58,000 a second
        // A partial that has not reported yet counts as zero, so an early sum is low, never high,
        // and the sink's keep-the-larger rule lets later sums replace it.
        sink.write(new WindowCount(adId, partial.windowStartMs(), valid, invalid));
    }
}

Taking the salt from the impression ID matters twice. Duplicates of one shown ad land in the same part, so the duplicate check stays local. And a replay sends every click to the same part, so totals come out the same. A random salt breaks both. Every ad is split, not just hot ones: nobody has to predict which ad goes viral, and a quiet ad’s empty parts cost nothing.

Go deeperOther fixes for a hot ad1 min

Option	Helps one hot ad?	Problem
More machines, more partitions	No	One key still maps to one task
Combine before the shuffle	Partly	Less traffic, but duplicate removal still runs on one task for the ad
A separate path for known hot ads	Yes	Someone must predict which ad goes viral
Split the key, then merge	Yes	One more shuffle, and a sum briefly low until every part reports

Clicks from every region

The one counting region has an outage during a big event. What happens to clicks?

The redirect’s 99.99% allows 52 minutes of downtime a year, which one regional outage can use up. Far-away users also pay 80 to 150 ms per click. But counting can pause for 15 minutes and lose nothing, if the clicks are safe.

So save clicks in every region, and count in one. Each region has its own click servers and Kafka. MirrorMaker copies each region’s clicks to the counting region and to a standby region.

Ad click aggregator · architecture

Stage 7: Clicks from every region

Grow itTrace a requestBreak it

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

1234567

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 7 of 7

Clicks from every region

Why this stage

The redirect needs 99.99% and low latency worldwide.

One region is one failure domain, and a click that cannot be logged is lost revenue.

What we add

Ingestion (load balancer, click servers, Kafka) in every region, each mirrored to the aggregation region and a standby.

Counting stays in one place.

What it costs

Mirroring adds seconds of delay, and a region that falls behind holds back the watermark for everyone.

At 58k clicks per second, every part has headroom. Raise the traffic until something turns red.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

Why not count in each region and add them up? After a failover, a click’s retry could land in another region, and count twice. Copying everything to one job keeps the duplicate check global. Say the counting region fails. The standby job restores the last checkpoint. It rewinds each copied topic by time to a few minutes before it. Then the duplicate check and the keep-the-larger sink absorb the overlap.

Fraud

A competitor runs a script that clicks a customer’s ads all day. How do we avoid billing for it?

Industry guidelines split invalid traffic in two. Simple cases are caught by rules: data centers and declared bots. Clever cases need analysis: click farms, hijacked devices, and bots that act like people. That maps onto layers:

Layer	Where	Catches	When
Token checks	Click server	Forged or changed tokens, tokens older than a day	Inline
Rules	Stream job	Data-center IPs, bot user agents, over 5 clicks a minute from one device	Seconds
Models	Daily recount	Click farms, rings of devices, a device’s whole day	Next day
Investigations	Offline	Fraud found weeks later	Weeks

Three rules hold across the layers. Mark, never drop: invalid clicks are kept with their reason, so a wrong verdict can be reversed. Version the rules, so the same input gives the same invoice. Late findings become credits on the next invoice, never edits to a past one. Thresholds stay private, or fraudsters learn how fast to click.

Common mistake

Blocking suspicious clicks at the redirect

Refusing to redirect a suspected bot breaks a real user who was wrongly flagged, and tells the bot which signal gave it away. Redirect every valid token, and decide what to bill afterwards.

When things break

Interviewerminute 40

A big sale triples clicks, and the stream counter falls an hour behind. What do advertisers see?

What they are testing

Whether you know the log keeps every click, so lag delays dashboards but never changes a bill.

Under stress the order is: redirects, then saving clicks, then counting, then dashboards.

The stream counter falls behind. Clicks still go into Kafka, which keeps them for days, so nothing is lost. Only the lag grows. Dashboards show older numbers, with a note that says how far behind they are. Billing is not touched, because invoices come from the daily recount, not the stream. We add counting workers, up to one per partition, and the backlog drains faster than it built up. Windows run on event time, so clicks counted late still land in the right minute.

Kafka is down for an hour. Nobody clicking an ad notices. Clicks go to local spools, and dashboards freeze. When Kafka returns, the spools drain, and the duplicate check removes anything sent twice. The backlog lands in the right minutes, because windows run on event time.

Where can a click still be lost? Only if a server dies after receiving it but before Kafka or the spool has it. Then the user got no redirect either, and usually clicks again, which counts once.

Click any box to break it and see what users would notice:

Ad click aggregator · architecture

Break it: what fails, who notices, what users see

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Break

A whole regionA click serverKafkaRaw click archiveStream jobBot signalsOLAP storeQuery APIReconciler

Failure: click any box to break it

Kafka goes down

What breaks

Click servers cannot get an ack, so new clicks go to local disk spools.

How we notice

Produce error rate and spool size on click servers.

What users see

Nobody clicking notices: redirects still return in milliseconds.

Dashboards stop moving.

What we do

One broker failing is invisible (replication factor 3).

For a cluster outage, spools hold hours of clicks and replay when it returns.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.

Go deeperEvery failure, and what users see1 min

Part	Failure	What users see	What we do
Click server	Instance dies	A few redirects fail and are retried	Stateless; a replacement drains the spool
Kafka	Cluster down	None for users; dashboards freeze	Spools hold hours; replayed and deduplicated
Stream job	Task dies	Dashboards pause about a minute	Restore the checkpoint; the sink absorbs repeats
Column store	Server lost	Slower or stale dashboards	Two replicas per segment; resume from positions
Archive	Connector stalls	None while Kafka still has the data	Resume from positions
Recount	Run fails	Yesterday stays provisional; invoices wait	Run again over files that never change
Whole region	Outage	Clicks move by DNS in one to two minutes	Other regions take clicks; backlog arrives late

Path	Guarantee	What users can see
Saving a click	On two zones (or a local spool) before the 302	Nothing
Dashboard minute counts	Within 2 minutes; never go down	A minute’s count can rise for 10 minutes after it appears
Yesterday’s counts	The same answer on every rerun	Usually drops 1 to 2% overnight as invalid traffic is removed
Invoices	Made once from final counts; never edited	Late fraud findings arrive as credits on the next invoice

Go deeperFixing the numbers after a bad deploy2 min

A deploy at 08:00 counted mobile clicks twice, and it is now 14:00. The archive never changes, so any count can be rebuilt. But the keep-the-larger sink refuses to write a smaller, correct number over a larger, wrong one, so corrections go into a new table.

Recomputing six hours of counts

1. Stop the damage

Roll back the deploy, so new minutes are counted correctly.

2. Start a shadow job

Run the fixed version as a second job from the Kafka positions for 07:50, writing to ad_clicks_minute_v2. Six hours is about 250 million clicks. At ten times the normal rate, it catches up in about 40 minutes.

3. Check

Compare the two tables: mobile counts from 08:00 should halve, and nothing else should change.

4. Swap

Point the API at the new table in one step, so dashboards jump to correct numbers.

5. Money

Today isn’t invoiced yet, so the recount bills it correctly. Had a bad day been invoiced, its recount would produce the credit.

Replay is the recovery plan for every software bug, which is why Kafka keeps 7 days and the archive keeps everything.

Go deeperRunning it: alerts, deploys and cost1 min

Wake someone up when:

• redirect success drops below 99.95%, or p99 passes 20 ms
• spool bytes grow for 5 minutes
• the job’s watermark is more than 2 minutes behind
• 3 checkpoints in a row fail
• the recount and the stream differ by more than 0.5% for an account

Deploys. The stream job upgrades from a savepoint, and every operator has a fixed ID so its state carries over. A risky change runs first as a shadow job writing to a second table, compared for an hour.

Privacy. Raw IPs feed fraud rules but are personal data, so after 30 days the archive keeps only a shortened prefix.

Cost. The biggest lines are the column store, the Kafka brokers and the stream job’s SSDs. The archive costs a few thousand dollars a month.

Wrap up

The whole design

Here is everything we built, in the order we built it. Use the buttons to step through it.

Ad click aggregator · architecture

The full design, stage by stage

Color keyServiceDatabaseCacheQueue or streamBackground workerNew in this stageAsync or occasional

Play

PreviousNext

Stage

1234567

Clicks per second:

58,000

12k average58k peak580k (10×)2M

Stage 7 of 7

Clicks from every region

Why this stage

The redirect needs 99.99% and low latency worldwide.

One region is one failure domain, and a click that cannot be logged is lost revenue.

What we add

Ingestion (load balancer, click servers, Kafka) in every region, each mirrored to the aggregation region and a standby.

Counting stays in one place.

What it costs

Mirroring adds seconds of delay, and a region that falls behind holds back the watermark for everyone.

At 58k clicks per second, every part has headroom. Raise the traffic until something turns red.

Pinch or tap the zoom buttons to read small labels.Drag any box to move it. Reset puts every box back.The whole design. Step through it: one service and one table, a log and a stream, counts you can bill on, a store built for the queries, a daily recount, split hot ads, then clicks saved in every region.

The numbers behind the busy bars

What one machine can do

• One click server handles about 10,000 redirects a second.
• One Postgres primary takes about 10,000 single-row inserts a second, or 20,000 batched upserts.
• A 6-broker Kafka cluster takes about 1 million 500-byte events a second.
• The OLAP cluster ingests about 400,000 rows a second at stage 4, and 1 million once it has grown.

The hot ad

• The stream job's bar shows its busiest subtask: the one holding the hottest ad, which gets 10% of all clicks.
• One subtask counts about 30,000 clicks a second, with state lookups. Salting spreads that ad over 16 subtasks.

Other numbers

• At minute grain, there are about half as many rollup rows as clicks.
• In stage 7 the largest region carries half of the clicks.

The decisions, and what each one costs

Decision	We chose	We gave up	We’d choose differently if
Saving the click	Kafka on two zones before the 302, disk spool	3 to 5 ms per redirect	Counts were analytics only
Duplicate key	Signed impression ID, 1 hour in the stream	Second clicks after an hour, until the recount	The ad SDK reported clicks without our redirect
Time	Event time, 10 s watermark, 10 min lateness	Minutes that change after they appear	Dashboards could be an hour stale
Sink	Absolute totals, keep the larger	Corrections in place	A reader added changes (transactional sink)
Store	Pinot rollups by minute, hour, day	Slicing by details we didn’t roll up	Free-form slicing mattered most (raw clicks in ClickHouse)
Billing	Daily recount by the same code	Same-day invoices	Volume was small enough for one batch path
Hot ads	A 16-way split by impression ID	One more shuffle	Hot ads were known in advance
Regions	Save everywhere, count in one region	Seconds of copying delay	Counting had to survive a region with no pause

How to spend 45 minutes

• 0 to 4. Billing, freshness, lateness, and who owns the redirect.
• 4 to 8. 58,000 clicks a second, $500,000 a day per 0.1% error, 13 GB of duplicate state.
• 8 to 20. Build it: Kafka before the redirect, the four fixes in the stream job, the column store, the daily recount. A mid-level answer can stop here.
• 20 to 28. The stress tests: late clicks, then exactly-once stated precisely.
• 28 to 40. More stress tests: hot ads, regions, then fraud.
• 40 to 45. Kafka failing, then your own questions.

Never skip the duplicate key, event time, the repeat-safe sink, or the recount.

Follow-up questions

Interviewer asksAn advertiser says you overbilled them 3% last Tuesday. How do you answer?

Rerun Tuesday’s recount for their account, with the rules version on the final rows. Same input, same code, same total, with invalid clicks by reason and duplicates removed. If they are right, fix the rule, recount as a new version, and credit the difference.

Interviewer asksHow does the ad server stop showing an ad when its budget runs out?

It needs spend within seconds. The stream job sends early results every 10 seconds for open minutes. A pacing service sums them per campaign, and tells ad servers to slow or stop. A campaign can overspend by a few seconds of clicks. Billing still uses final counts.

Go deeperMore follow-up questions1 min

Advertisers want conversions per click. What changes? A conversion arrives hours or days later, tagged with the impression ID. A 30 day stream join is too much state. So keep clicks in a key-value store by impression ID for 30 days. Look each conversion up there, and count with the same window and sink pattern.

Now count impressions too, at 100 billion a day. Same pipeline, same key. An hour of IDs at that rate is over a terabyte of state. So the stream checks duplicates over minutes, and the recount over the day. Unique users per ad come from HyperLogLog sketches, which merge.

Show the top 100 ads by clicks in the last 5 minutes, updated live. Heavy hitters over a sliding window, from the same click stream. The top-k problem covers it.

How do you test that the stream and the batch really agree? Run both modes of the job on the same recorded day in CI, and require identical totals. In production, the morning comparison is the test.

Further reading

• Design a URL shortener for the redirect path, and design a payment system for idempotency and reconciliation, on this site.
• Streaming 101, Tyler Akidau: event time, processing time and watermarks.
• The Dataflow Model, VLDB 2015: the paper behind windows, triggers and allowed lateness in Beam and Flink.
• Flink: timely stream processing: how Flink generates watermarks, handles idle sources and treats late data.