Design an Ad Click Aggregator

“Design the system behind an advertising dashboard” is a favourite analytics question: it tests whether you can move from request/response thinking to stream processing, and whether you understand the tension between fast and correct. Advertisers want to watch clicks tick up in near real time, but they are also billed on those clicks — so the numbers must eventually be exact, and resistant to duplicates and fraud. Let’s build a pipeline that serves both masters.

Understanding the Problem

Functional Requirements

1. Ingest clicks — Record every ad click event (which ad, which advertiser, when, from where).
2. Aggregate — Produce counts (and spend) per ad over time windows (per-minute, per-hour, per-day).
3. Query — Advertisers query aggregates: “clicks for campaign X in the last hour, by minute.”
4. Near-real-time dashboards — New clicks appear within seconds.
5. Billing accuracy — The counts used to charge advertisers must be exact and de-duplicated.

Non-Functional Requirements

Scale Estimation

• 100k clicks/sec × ~200 bytes ≈ 20 MB/sec of raw ingest, ~1.7 TB/day of raw events.
• Aggregated to per-minute-per-ad counts, the data shrinks by orders of magnitude — a few million rows/day — which is what the query layer actually serves.

The gap between 1.7 TB of raw events and a few million aggregate rows is the whole point: pre-aggregate on write so reads are cheap. The hard part is doing that aggregation continuously, accurately, and fast.

Core Entities and APIs

The core entities are the raw ClickEvent and the derived Aggregate rows.

Data Model

ClickEvent = {
    click_id:    uuid        # unique per click — the key to exactly-once
    ad_id:       string      # which creative
    campaign_id: string
    advertiser:  string
    ts:          int64       # event time (ms)
    user_id:     string      # for fraud / dedup
    context:     { ip, geo, device }
}

# Pre-aggregated, the thing queries actually read
Aggregate = {
    ad_id:       string
    window_start:int64       # e.g. minute bucket
    granularity: enum        # minute | hour | day
    clicks:      int64
    spend:       decimal
}   # primary key (ad_id, granularity, window_start)

API Design

# Ingestion (from the ad-serving edge)
POST /click
  body: { click_id, ad_id, campaign_id, advertiser, ts, user_id, context }
  response: 202 Accepted           # fire-and-forget, buffered

# Query (advertiser dashboard)
GET /aggregate
  query: { ad_id | campaign_id, from, to, granularity: "minute" }
  response: { series: [{ window_start, clicks, spend }, ...] }

The ingestion endpoint returns 202 Accepted immediately and never does synchronous aggregation — it just durably buffers the event. Everything expensive happens downstream in the stream.

High-Level Design

The pipeline has four stages:

• Collection — The ad-serving edge writes each click to a durable log (Kafka), partitioned by ad_id. This decouples the spiky, high-volume ingest from the processing and absorbs bursts.
• Stream aggregation — A stream processor (Flink, or Kafka Streams/Spark Streaming) consumes the log, groups by ad_id, and maintains tumbling time-window counts (e.g. one bucket per minute). As each window closes, it emits an Aggregate row.
• Serving store — Aggregates are written to an OLAP store (Druid, ClickHouse, or Pinot) optimized for fast range scans and group-bys over time. Dashboards query this, never the raw log.
• Batch reconciliation — A slower batch job periodically re-reads the raw events from durable storage (the log, or a data lake) and recomputes the authoritative, de-duplicated counts, correcting the fast path.

This split — a fast approximate path plus a slow exact path — is the lambda architecture, and this problem is its canonical example.

Deep Dive: Streaming Aggregation with Windows

The heart of the system is turning an unbounded stream of clicks into bounded, queryable counts.

Partition by ad id

Clicks are written to Kafka with partition = hash(ad_id) % N. This guarantees that all clicks for one ad go to one partition, so a single stream-processor task owns the complete count for that ad — no cross-task coordination to sum a counter. It also lets consumption scale: more partitions, more parallel Flink tasks.

Tumbling windows and keyed state

The processor keys the stream by ad_id and maintains a running count in windowed keyed state:

# Conceptual Flink job
clicks.key_by(lambda c: c.ad_id) \
      .window(TumblingEventTimeWindows.of(minutes=1)) \
      .aggregate(CountAndSpend()) \
      .add_sink(olap_sink)          # emit {ad_id, window_start, clicks, spend}

A tumbling window is a fixed, non-overlapping bucket (00:00–00:01, 00:01–00:02, …). When a window closes, its count is final and gets written out. Keyed state means each ad_id’s running total lives in the processor’s local (checkpointed) state — fast to update per event.

Event time and late data

Clicks are stamped with event time (when the click happened), not processing time (when it arrived). Mobile clients buffer offline and a click can arrive seconds or minutes late. The processor uses a watermark — “I believe I’ve seen all events up to time T” — to decide when a window is safe to close, plus a small allowed lateness so a straggler updates the already-emitted bucket instead of being dropped. State this explicitly in the interview: event-time windows + watermarks are how streaming handles out-of-order data.

Fault tolerance

Flink checkpoints its keyed state and the Kafka offsets together, periodically and atomically. On failure it restarts from the last checkpoint and replays the log from the checkpointed offset — giving exactly-once state updates within the streaming layer despite crashes.

Deep Dive: Accuracy, Dedup and the Batch Path

Dashboards can tolerate being approximate for a few seconds; billing cannot. This is where most candidates stop too early — getting the numbers exact is the real test.

Why duplicates happen

Ingestion is at-least-once: the edge retries on network failure, Kafka may redeliver, and the processor may reprocess after a restart. So the same physical click can appear two or three times. Naively counting would over-bill advertisers.

Dedup by click id

Every click carries a unique click_id generated at the edge. Before counting, drop events whose id has already been seen. Two practical approaches:

• Windowed dedup — keep a set of seen click_ids for a bounded time window (say, the last hour) in the stream processor’s state; drop repeats. Cheap, catches the common near-in-time duplicates.
• Probabilistic filter — a Bloom filter or an approximate set over a longer horizon when storing every id is too expensive; accept a tiny false-positive rate for the streaming path and let the batch path be exact.

Combined with at-least-once ingestion, a unique id plus dedup gives effectively exactly-once counting — the same lesson as the message-queue chapter.

The batch reconciliation path

The streaming numbers are fast but can drift (dropped late events, approximate dedup, a bug in the job). So a batch job runs hourly or daily over the raw events stored in a data lake (S3/HDFS), does an exact de-duplicated GROUP BY ad_id, minute, and overwrites the streaming aggregates for closed windows. Dashboards show the fast numbers for the current minutes and the reconciled numbers for older ones; billing always uses the reconciled output. This is the lambda pattern: streaming for latency, batch for truth.

(If you prefer, a kappa variant drops the separate batch system and instead re-runs the same streaming job over replayed history to recompute — one codebase, at the cost of long replays. Mention it as the modern alternative.)

Click fraud

Because clicks are money, defend them: per-user and per-IP rate limits at ingest, anomaly detection on click-through rates (a sudden 100× spike on one ad), and bot filtering. Fraudulent clicks are flagged and excluded from billed totals — often in the batch path where you have the full context.

Deep Dive: Serving Queries at Scale

Advertisers run range queries: “campaign 17, last 24 hours, by hour.” These are OLAP queries — scan a time range, filter by dimension, aggregate — and a row store would choke.

• Columnar OLAP store — Druid/ClickHouse store data by column and by time segment, so a time-range group-by reads only the relevant columns and segments. Pre-rolled per-minute aggregates keep the scanned row count tiny.
• Pre-aggregation granularities — store minute, hour, and day roll-ups. A “last 30 days by day” query hits the day table (30 rows), not 43,200 minute rows.
• Hot-ad skew — a viral ad gets a disproportionate share of clicks, making its partition and its aggregate rows a hotspot. Mitigate with sub-partitioning (split a hot ad_id across sub-keys and sum on read) and by caching the handful of hot advertisers’ dashboards.
• Caching — dashboards refresh on a timer; cache the last computed series per query for a few seconds to shield the OLAP store from repeated identical reads.

Key Takeaways

An ad click aggregator is really a stream-processing question wearing an ads costume. Interviewers want to see you reach for a log + stream processor, use windowing and watermarks, and — the part that separates strong answers — handle duplicates and reconcile the fast path against a batch path so the money numbers are exact. Draw the two-path pipeline and talk about fast vs correct out loud.