Engineering Atlas › System Design Interviews

Design an ad click analytics pipeline

18 min read · last reviewed 2026-10-17

Open in the interactive app: practice, quiz, mock interview →

In one minute

Requirements

Functional

Non-functional

Out of scope: impression counting (same pipeline, larger volume), full click-fraud detection (I leave a hook for it), auctions and ad serving, and attribution of conversions.

Back-of-envelope

API

GET /c/{click_token}
  click_token = base64(impression_id, ad_id, campaign_id, served_at, landing_url_id) + HMAC
  -> 302 Found, Location: https://advertiser.example/landing?...

GET /v1/advertisers/{advertiser_id}/clicks
  ?ad_ids=a1,a2&from=2026-10-01T00:00Z&to=2026-10-08T00:00Z&granularity=hour
  -> 200 {
       "granularity": "hour",
       "series": [{"ad_id": "a1", "points": [["2026-10-01T00:00Z", 1834], ...]}],
       "freshness": {"final_through": "2026-10-07T00:00Z", "provisional_after": "2026-10-07T00:00Z"}
     }

The freshness block matters. It tells the dashboard which buckets are reconciled and which are provisional streaming numbers that may still move slightly.

Data model

Kafka topic clicks.raw, partitioned by click_id. That spreads load evenly and sends all copies of one click to one partition, so dedup is local. Retention is 7 days for replays.

Raw archive in object storage, Parquet files under dt=YYYY-MM-DD/hour=HH/. This is the source of truth for the batch reconcile and for audits. The partition key is time because every batch job reads a time range.

OLAP table ad_clicks_minute (ClickHouse, Druid or Pinot):

ColumnTypeNote
advertiser_idUInt64first sort key, so one advertiser's rows are contiguous
ad_idUInt64second sort key
window_startDateTimetime partition by day
clicksUInt64summed
sourceEnum(stream, batch)batch rows replace stream rows

The sort key (advertiser_id, ad_id, window_start) makes the common query a few contiguous range reads. Partitioning by day makes dropping old minute data a metadata operation. Hourly and daily tables are materialized from this one.

Ad metadata (ad_id → advertiser_id, campaign_id, landing_url) is small and cached in the click service and the stream job.

High-level design

flowchart LR
  U[User] -->|"GET /c/token"| CS["Click service"]
  CS -->|302| LP["Landing page"]
  CS -->|"produce (acks=all)"| K[("Kafka clicks.raw")]
  K --> F["Flink: dedup + event-time windows"]
  F -->|"upsert minute counts"| O[("OLAP store")]
  F -->|"late events"| LQ[("Kafka clicks.late")]
  K --> AR["Archiver"] --> S3[("Object store, Parquet")]
  S3 --> B["Daily batch reconcile"]
  LQ --> B
  B -->|"overwrite day partition"| O
  Q["Query service"] --> O
  A[Advertiser] --> Q

Write path. The user taps an ad. The click service verifies the HMAC on the token, which rejects forged or tampered clicks cheaply. It derives click_id = hash(impression_id), so a double tap or a browser retry on the same impression gives the same ID. It produces the event to Kafka with acks=all and only then returns the 302. If Kafka is slow, it redirects anyway after a short timeout and spools the event to local disk for a sidecar to drain. A lost visitor is worse than a delayed count.

Stream path. Flink reads clicks.raw, drops events whose ID it has already seen within the dedup horizon, assigns each click to its one-minute event-time window, pre-aggregates per task, then re-keys by ad_id and sums. When the watermark passes a window's end, the window fires and Flink upserts (ad_id, window_start, count) into the OLAP store; each late click within the allowed lateness fires it again and the row is overwritten with the new total. Clicks that arrive after the lateness has expired go to a side output topic.

Batch path. Every night a Spark job reads the previous day's Parquet, dedups by click ID across the whole day, applies fraud filters with the full day's context, and writes exact counts that replace the day's streaming rows.

Read path. The query service checks ownership, picks the coarsest rollup table that satisfies the granularity, and caches results for 30 seconds.

Deep dives

1. Counting each click exactly once

There are three places a click can be duplicated: the client retries, the producer retries after a timeout, and the stream job replays after a crash.

The output must also be exactly once. A two-phase-commit transactional sink works but ties output visibility to the checkpoint interval: Uber's ads pipeline commits its Kafka output on 2-minute checkpoints and names that interval as the main latency bottleneck (Uber). I chose an idempotent upsert: each fired window writes its full count to the row keyed by (ad_id, window_start), never an increment, so a replayed write is harmless. The dedup set is keyed state with a TTL of about 10 minutes plus the allowed lateness; older duplicates are rare and the batch path catches them.

"Upsert" means different things in different OLAP stores. Pinot's upsert tables return the latest row per primary key, but only if the input stream is partitioned by that key (Pinot docs). ClickHouse's ReplacingMergeTree removes duplicate rows only during background merges at an unknown time, so a query needs FINAL (or an argMax per key) to avoid counting a window twice (ClickHouse docs). Pick the store with this in mind.

2. Event time, watermarks and late clicks

Processing-time windows are easy and wrong: a consumer lag spike shifts clicks into later minutes, drawing a fake dip then a fake spike. So windows use event time, stamped by the click service rather than the device, whose clock can be off by hours.

The watermark is Flink's estimate that "no more events older than T will arrive". I use a bounded-out-of-orderness watermark of about 30 seconds, plus an allowed lateness of 2 minutes during which a fired window can still be updated by re-upserting the row. The trade-off is direct: a larger bound means more complete numbers but staler dashboards and more open window state.

Later events go to clicks.late, which the nightly batch includes, so nothing is lost. One subtlety is idle partitions. A watermark is the minimum across input partitions, so one partition with no traffic stalls every window. Flink's idleness timeout (withIdleness on the watermark strategy) excludes a silent partition from the minimum after a configured period (Flink docs).

3. Hot ads and the shuffle

A Super Bowl ad might get 20% of all clicks for a few minutes. If every click for that ad goes to one Flink subtask, that subtask falls behind, its watermark holds back the whole job and dashboards freeze.

The fix is two-stage aggregation. Stage one, keyed by click ID for dedup, keeps a map of (ad_id, window) → count per subtask and emits partial sums when the window fires. Stage two, keyed by ad_id, only adds partials. With 64 upstream subtasks, a hot ad generates at most 64 records per minute into stage two, whatever its click rate. Salting the key (ad_id#0..7) is the alternative if stage one cannot pre-aggregate, at the cost of a third merge step.

Down the stack

Layer 1The contract

  • For any (ad_id, minute) bucket, the streaming count never counts a click ID twice within the dedup horizon. A first value is visible about one watermark delay after the minute ends (roughly 30 to 40 seconds with the upsert sink), and it may be revised by late clicks until watermark delay + allowed lateness, about 2.5 minutes, after which it changes only when the batch reconcile replaces it.
  • A click acknowledged with a redirect is in Kafka or in the click service's durable spool. Kafka with replication.factor=3 and min.insync.replicas=2 survives one broker loss without losing it.
  • The batch count for a day is exact with respect to the archived log and replaces the streaming count. The difference between the two is reported as a metric, and a drift above about 0.5% pages someone.

Layer 2The mechanism

  • Checkpoints use asynchronous barrier snapshotting, a variant of Chandy-Lamport. The job manager injects numbered barriers into each source. When an operator has received barrier n on all inputs, it snapshots its state and forwards the barrier. A checkpoint completes when every sink acknowledges it; its Kafka offsets become the restart point.
  • Keyed state lives in RocksDB: a ValueState per click ID with state TTL for dedup, and a ReducingState per window holding one running count rather than a list of events.
  • Watermarks flow as special records in the stream. Each operator's watermark is the minimum of its inputs' watermarks, which is why one slow or skewed input holds back everything downstream.
  • Window firing is an event-time timer at the window's end. When the watermark passes it the window emits; a late click within the allowed lateness is added and fires the window again with the updated count, and the state is cleared only when the watermark passes window_end + lateness. The default lateness is zero, which drops late events silently unless they are sent to a side output with sideOutputLateData (Flink docs).

Layer 3The machine

  • Kafka append path. A produce request appends a batch to the active segment file of the partition with a write into the page cache. Followers fetch it, and the leader acknowledges once the in-sync replicas have it, typically in 2 to 10 ms in one region. Kafka does not fsync per message (log.flush.interval.messages defaults to the maximum long); it relies on replication for durability, because forcing an fsync on every write would cost two to three orders of magnitude in throughput (Kafka design docs). Consumers are served with sendfile, so bytes go from page cache to the socket without a copy into user space.
  • Batching. linger.ms=5 (the default since Kafka 4.0, up from 0 (KIP-1030)) and batch.size raised from its 16 KB default to 64 KB pack roughly 300 clicks per request, and LZ4 compresses repetitive click records perhaps 3 to 5×.
  • RocksDB on local NVMe. A state update is a memtable insert, tens of microseconds at most. A dedup lookup that misses the block cache costs one SSD read of a 4 KB block, about 100 µs; Bloom filters on each SST file avoid most of those reads for IDs never seen. Incremental checkpoints upload only new SST files to object storage, so a checkpoint of a 2 GB state might send tens of megabytes.

Why it's built this way

  1. Advertisers pay per click, so overcounting is a billing error. That forces a stable click ID and deduplication on it.
  2. Mobile networks deliver clicks out of order and late, so counting by arrival time gives wrong minutes. That forces event-time windows and watermarks.
  3. Waiting forever for stragglers makes dashboards useless, so the stream closes windows on a bound and accepts small incompleteness. That forces a second, exact path for billing.
  4. Network shuffles cost CPU and create hot spots, so partial aggregation happens before the shuffle.

Failure modes

In the wild

Trade-offs

OptionStrengthsWeaknessesChoose when
Stream onlyOne codebase, fresh numbersLate data and bugs permanently affect billingInternal metrics, not money
Stream plus batch reconcileFresh and exactTwo code paths that must agreeBilling-grade counts
Idempotent upsert sinkSimple, replay-safeRequires a store with keyed upsertsAggregates with a natural key
Transactional (2PC) sinkTrue exactly-once to KafkaLatency tied to checkpoints, more moving partsDownstream is another Kafka consumer
Large allowed latenessMore complete stream numbersMore state, later finalityMobile-heavy traffic
Salting hot keysWorks without pre-aggregationExtra merge stage, harder queriesAggregation cannot be combined locally

Leadership lens

Check yourself

  1. One click-service host's clock runs 10 minutes fast. What goes wrong in the stream job, and how does the design contain it?

    Show answer

    Its future event times push the watermark forward, so everyone else's clicks start looking late and fall out of their windows. The click service rejects or clamps timestamps more than a few seconds ahead of the Kafka broker's append time, and NTP offset is alerted on.

  2. Why does the stream sink write each fired window as an idempotent upsert instead of using a two-phase-commit transactional sink?

    Show answer

    Writing the full count to the row keyed by (ad_id, window_start), never an increment, makes a replayed write harmless, while a 2PC sink ties output visibility to the checkpoint interval and adds moving parts. The cost is that the store must support keyed upserts.

  3. Estimate the dedup state for a 10-minute horizon at peak. Why is it acceptable that duplicates older than that horizon slip through the stream?

    Show answer

    About 60k clicks/s times 600 s is 36M IDs, roughly 1.8 GB at about 50 B each, which fits in keyed RocksDB state across the job. Older duplicates are rare, and the nightly batch dedups by click ID across the whole day before billing.

  4. A Super Bowl ad gets 20% of all clicks. Why can that freeze every dashboard, and how does two-stage aggregation bound the damage?

    Show answer

    All its clicks land on one subtask, which falls behind, and because watermarks are a minimum over inputs it holds back the whole job. Stage one pre-aggregates (ad_id, window) counts per subtask, so with 64 upstream subtasks the hot ad sends at most 64 records per minute into stage two.

Sources and further study

Primary

Systems docs

Books

Other curricula

Watch

Free resources

Open in the interactive app: practice, quiz, mock interview →