Everyone thinks an ad click aggregator is a SELECT COUNT(*) ... GROUP BY ad_id. “Log every click, count the rows, show the advertiser their numbers, done.” Then the interviewer adds the constraints that make it a real system: it is not a thousand clicks, it is millions of clicks per second at peak; advertisers pay per click so a double-counted or dropped click is literally money moving the wrong way; the dashboard must feel live, so a click should show up in seconds, not in tomorrow’s batch; and the same numbers must also be provably correct at the end of the day for billing, even though clicks arrive late, out of order, and sometimes twice.

An ad click aggregator is deceptive because the naive GROUP BY demos fine on a laptop with a million rows and dies the moment you have a firehose of events, a billing system that will not tolerate a counting error, and a product that wants both a real-time graph and an audit-grade daily total from the same pipeline. The whole design comes down to four decisions: how you absorb millions of events per second without dropping any (a durable log, not a database), how you turn that raw stream into per-minute counts continuously (stream processing with windows), how you count exactly once when the network guarantees you at-least-once (idempotency keys and deduplication), and how you serve a fast approximate answer for the live dashboard while keeping a slow accurate answer for billing (the two-path reconciliation). All of it while the dashboard stays under a couple of seconds fresh and the daily total is exact.

Let me do it properly.

Functional Requirements (FR)

In scope:

  • Ingest click events. When a user clicks an ad, the system records the event with its ad ID, timestamp, and enough context (campaign, advertiser, geo, device) to slice on later. This is the firehose.
  • Aggregate clicks over time windows. Produce counts of clicks per ad (and per campaign/advertiser) bucketed by time - per minute, per hour, per day. This is the core output.
  • Query aggregated metrics. An advertiser dashboard asks “clicks for ad X between 09:00 and 10:00, grouped per minute” and gets an answer fast. Support filtering by campaign, advertiser, and common dimensions (geo, device).
  • Support both real-time and historical queries. The last few minutes should be near-live; queries over last week or last month hit pre-aggregated rollups.
  • Idempotent, exactly-once counting. A click counts once and only once, even though it may be delivered to us multiple times. This is the requirement that makes billing trustworthy and is the whole point of the design.

Explicitly out of scope (say it so you own the scope):

  • Click fraud detection. Deciding whether a click is a bot, a click farm, or a fat-finger double tap is a large separate system (behavioral models, IP reputation, anomaly detection). We assume clicks arriving at the aggregator are the ones worth counting, and we note where the fraud-filter gate sits on the ingestion path.
  • Ad serving / auction. Choosing which ad to show and running the real-time bid is a different system entirely. We only count the clicks it produces.
  • Impressions and view-through. Counting views is the same machinery at even higher volume; we design for clicks and note that impressions reuse the identical pipeline with a different event type.
  • Billing and invoicing logic. Turning “1.2M clicks this month at $0.40 CPC” into an invoice is downstream finance logic reading our accurate daily totals. We guarantee the totals are correct; we do not cut the check.
  • Per-user profiles / attribution. Tying a click back to a conversion days later is an attribution system. We aggregate counts, not user journeys.

The one decision that drives everything: an ad click aggregator is a write-heavy, count-over-time problem where the counts are money, so it must be both fast enough for a live dashboard and exact enough for billing - two goals that pull in opposite directions. So the design is dominated by ingesting an un-droppable stream into a durable log, running stream processing to roll it into time-windowed counts, guaranteeing each click is counted exactly once despite at-least-once delivery, and serving a fast-approximate path alongside a slow-accurate path that reconcile.

Non-Functional Requirements (NFR)

  • Scale: on the order of 10B click events per day at a large ad network. That averages ~100K clicks/sec, but traffic is spiky (evenings, big campaigns, live events), so peak is ~1M clicks/sec. Reads are far lighter than writes - advertiser dashboards and internal reporting, low thousands of QPS - and are heavily cacheable and pre-aggregated.
  • Latency: dashboard query p99 under ~200ms (served from pre-aggregated rollups, not raw scans). End-to-end freshness - click happening to it showing on the real-time graph - on the order of a few seconds to ~1 minute, not instant, not nightly.
  • Availability: 99.9%+ on ingestion. Dropping clicks silently is unacceptable because clicks are revenue. Ingestion must stay up and never lose an accepted event even during a node failure or a downstream slowdown. Query-side can degrade to slightly stale rather than error.
  • Consistency: the real-time path is eventually consistent and approximate - a live counter being off by a fraction of a percent for a few seconds is fine. The billing path is strongly consistent and exact - the daily total per ad must be correct to the click. The design serves both, and they reconcile so the exact numbers win.
  • Durability: very high for accepted events. Once we return 200 to the click, that event must survive anything until it is durably counted. The raw event log is retained (days to weeks) so any aggregate can be recomputed - the counts are a derived, rebuildable view of an immutable event log.
  • Correctness: exactly-once counting for billing. At-least-once ingestion (which the network gives us) plus deduplication equals effectively-once counts. No double counts, no dropped counts in the numbers that bill.

Back-of-the-Envelope Estimation (BoE)

Let me ground the design in numbers.

Traffic:

Click events/day:              10,000,000,000   (10B)
Average click QPS = 10B / 86,400 sec ≈ 115,000 QPS   (misleading average)
Traffic is spiky - evenings + big campaigns concentrate clicks:
Peak click QPS (say ~8x avg) ≈ 1,000,000 QPS   (the number we size for)

The daily average of ~115K QPS is a trap. Ad traffic peaks hard in the evening and during marquee events (a finals broadcast, a flash sale), so a large fraction of the day’s clicks land in a few hours. We size ingestion for ~1M events/sec, not 115K.

Read vs write:

Writes (clicks):     ~1,000,000/sec peak. The firehose. Un-droppable.
Reads (dashboards):  advertisers + internal tools checking metrics.
   Say 100K advertisers, each dashboard polls a few times/min when open,
   plus internal reporting. Order of low thousands of QPS, heavily
   cacheable and served from pre-aggregated rollups, not raw events.

The asymmetry is extreme: writes outnumber reads by roughly 1000:1, and the writes are the un-droppable revenue events while the reads are cacheable dashboard polls. That shape - a massive un-droppable write stream feeding a small, pre-aggregated read surface - is why the design is a stream-processing pipeline, not a request/response database.

Storage:

Raw event size:  ad_id, campaign_id, advertiser_id, user context, geo,
                 device, timestamp, click_id ≈ ~200 bytes/event on the wire,
                 ~100 bytes packed in the log.

Raw log/day:     10B * 100 bytes ≈ 1 TB/day of raw click events.
Retain raw ~14 days for recompute:  ~14 TB in the durable log tier.

Aggregated rollups are TINY by comparison:
  per-minute counts per ad. Say 10M distinct ads active in a day,
  1,440 minutes/day, but each ad only has clicks in some minutes.
  Realistically a few billion (ad, minute) rows/day * ~40 bytes
  ≈ low hundreds of GB/day of per-minute rollups, rolled up further
  into per-hour and per-day, which are orders of magnitude smaller.

The raw log is big (~1 TB/day) but cheap and sequential; the aggregates the dashboard reads are tiny because they are counts, not events. This split - fat immutable raw log, thin queryable rollups - is the storage shape of every good aggregation pipeline.

Bandwidth:

Ingest: 1M events/sec * 200 bytes ≈ 200 MB/sec ≈ 1.6 Gbps inbound at peak.
        Non-trivial but well within a fleet of ingestion nodes + Kafka.
Query:  dashboard responses are small aggregates, a few KB each,
        low thousands/sec -> a few MB/sec. A non-issue.

Ingest bandwidth is real (a couple Gbps) but spread across a fleet; the scarce resources are write throughput into a durable log (absorbing 1M/sec without loss), stream-processing throughput (rolling that into windowed counts continuously), and exactly-once correctness (counting each click once under retries). Size for those.

High-Level Design (HLD)

The system is a stream-processing pipeline, not a CRUD app. Clicks land on stateless ingestion nodes that do nothing but validate and append to a durable log (Kafka). A stream processor (Flink) consumes the log, deduplicates, and folds events into time-windowed counts, which it writes to an OLAP store the dashboard queries. A parallel slower batch path recomputes exact daily totals from the same raw log for billing, and the two reconcile.

                 WRITE PATH (click events, ~1M/sec peak, UN-DROPPABLE)
   ┌──────────────────────────────────────────────────────────────────────┐
   │  Browser / SDK  --click beacon-->  CDN edge / API Gateway / LB         │
   └───────────────────────────────────────┬────────────────────────────────┘
                                            │  POST /click  (with click_id)
                                  ┌─────────▼──────────┐
                                  │  Ingestion Service │  (stateless, fleet)
                                  │  - validate + enrich│
                                  │  - assign/keep click_id
                                  │  - append to log   │
                                  └─────────┬──────────┘
                                            │  produce (partitioned by ad_id)
                              ┌─────────────▼──────────────┐
                              │   KAFKA  (durable event log)│
                              │   topic: clicks, N partitions
                              │   retained ~14 days         │
                              └───┬──────────────────────┬──┘
                    real-time     │                      │   batch / accurate
                    (fast path)   │                      │   (slow path)
                       ┌──────────▼─────────┐   ┌────────▼─────────────────┐
                       │  Flink Stream Proc │   │  Raw Event Archiver      │
                       │  - dedup by click_id│   │  -> Object store (S3)    │
                       │  - tumbling windows │   │     partitioned by hour  │
                       │    (per-minute)     │   └────────┬─────────────────┘
                       │  - watermarks for   │            │ nightly / hourly
                       │    late events      │   ┌────────▼─────────────────┐
                       └──────────┬──────────┘   │  Batch Recompute (Spark) │
                    upsert counts │              │  - exact GROUP BY over    │
                                  │              │    the immutable raw log  │
                       ┌──────────▼──────────┐   │  - writes ACCURATE totals │
                       │   OLAP Store        │◄──┘  (overwrites approx path) │
                       │  (Druid / ClickHouse│      reconciliation
                       │   / Pinot)          │
                       │  per-min / hr / day │
                       │  rollups per ad     │
                       └──────────┬──────────┘
                                  │  READ PATH (dashboards, low-thousands QPS)
                       ┌──────────▼──────────┐
                       │  Query / API Service │  GET /metrics?ad&from&to&gran
                       │  + result cache      │
                       └──────────┬──────────┘
                          ┌───────▼────────┐
                          │  Advertiser UI │
                          └────────────────┘

Request flow - a click event (the write hot path):

  1. A click fires a beacon (POST /click) carrying the ad context and, critically, a click_id - a unique ID for this click, minted by the ad-serving SDK at impression/click time. This ID is what makes exactly-once possible later.
  2. The stateless Ingestion Service validates the payload, enriches it (attach campaign/advertiser from the ad ID, resolve geo from IP, tag device), and does nothing else expensive. It appends the event to Kafka, partitioned by ad_id, and returns 200. The ack is honest because the event is now durable in the log - even if every downstream processor died, the click is safe and will be counted when they recover.
  3. Kafka is the durable buffer and source of truth for raw events. It absorbs the 1M/sec spike (producers write sequentially, consumers read at their own pace) and retains events for ~14 days so any aggregate can be recomputed.
  4. The Flink stream processor consumes the clicks topic, deduplicates by click_id (drop any click_id it has already counted in the window), and folds events into tumbling per-minute windows per ad. It uses watermarks to decide when a minute is “closed enough” to emit while still tolerating late events.
  5. Flink upserts the per-minute counts into the OLAP store. Within a few seconds to a minute, the dashboard reflects the click.

Request flow - the read paths:

  1. Dashboard query: the Query Service takes ad_id, a time range, and a granularity (minute/hour/day) and reads the matching pre-aggregated rollups from the OLAP store - never the raw events. A range of “last hour, per minute” is 60 tiny rows; “last month, per day” is 30 rows. Sub-200ms, and cacheable.
  2. Real-time tail: the most recent minute or two comes straight from the Flink-fed rollups (approximate, still settling as late events arrive).
  3. Accurate totals: for closed periods (a finished hour or day), the batch recompute path has written the exact count, which overwrites the approximate value. Billing reads only these settled, reconciled totals.

The key architectural insight: the raw event log is the source of truth, and every count is a derived, recomputable view of it. Ingestion’s only job is to get the event durably into the log fast; all the hard counting happens asynchronously downstream and can be re-run. That decoupling is what lets us absorb a 1M/sec spike (Kafka is the shock absorber), serve a live-ish approximate number immediately, and still produce an exact billing number by recomputing from the same immutable log. The stream processor is never the source of truth - it is a fast index we can always rebuild.

Component Deep Dive

Four hard parts: (1) ingesting an un-droppable 1M/sec firehose, (2) turning that raw stream into time-windowed counts with stream processing, (3) counting exactly once under at-least-once delivery, and (4) serving a fast approximate answer alongside a slow accurate one. I will walk each from naive to scalable.

1. Ingesting the Firehose: Database -> Durable Log

The write peak is ~1M clicks/sec, and none may be lost once accepted.

Approach A: Write each click straight to a database (the naive instinct)

The ingestion service inserts a row per click into a database (or increments a counter row) synchronously.

INSERT INTO clicks (click_id, ad_id, ts, geo, device) VALUES (...);
-- or, the "clever" version:
UPDATE counters SET n = n + 1 WHERE ad_id = ? AND minute = ?;

Where it breaks: at 1M writes/sec, no single relational database keeps up - you are trying to sustain a million inserts per second with index maintenance and durability on every one. The UPDATE ... n = n + 1 version is even worse: the hot ad’s counter row becomes a single contended lock that every click for that ad serializes on, so a viral ad turns into a lock convoy. And a spike above the database’s sustained write ceiling has nowhere to go - you either block the client (losing clicks and revenue) or drop events. The database also couples ingestion speed to aggregation logic: the firehose stalls whenever the counting is slow. Wrong tool.

Approach B: Append to a durable log (Kafka), aggregate downstream

Split “accept the event durably” from “count the event.” Ingestion does only the first, as fast as possible:

Ingestion Service (stateless, horizontally scaled):
   validate(payload)             # schema, required fields, click_id present
   enrich(payload)               # campaign/advertiser, geo, device
   produce("clicks", key=ad_id, value=event)   # append to Kafka
   return 200                    # ack AFTER the durable append

Kafka is built for exactly this: a partitioned, replicated, append-only log that sustains millions of writes/sec because appends are sequential and there are no per-event indexes or random updates. Producers never block on consumers - a 1M/sec spike is just written to the log and drained by consumers at their own pace. This is the shock absorber.

  • Partitioning: key by ad_id so all clicks for one ad land on one partition, in order. This localizes per-ad aggregation to a single consumer and keeps a hot ad’s events together. To avoid one blazing-hot ad overwhelming a single partition, sub-partition the very hottest ads by ad_id + hash(click_id) % K and sum the K sub-counts downstream (the hot-key fix from the bottlenecks section).
  • Replication: each partition has replicas (replication factor 3), so a broker dying loses nothing and ingestion keeps acking.
  • Durability contract: the ingestion service acks the client only after Kafka confirms the append to a quorum of replicas (acks=all). That is what makes the 200 honest - the click is un-loseable before the user is told “ok.”
  • Retention: keep raw events ~14 days. This is the audit log the exact-recompute path replays and the safety net that lets us rebuild any aggregate.

Ingestion nodes are stateless, so we scale them horizontally behind a load balancer and lose nothing when one dies mid-flight (the client retries; dedup handles the retry). All the expensive, stateful counting moved downstream where it can be batched, parallelized, and re-run.

2. Stream Processing: Raw Events -> Time-Windowed Counts

Now we have a durable stream of clicks. We need continuous per-minute (and per-hour, per-day) counts per ad, kept fresh, tolerating events that arrive late and out of order.

Approach A: Periodic batch job over the raw log (naive)

Every few minutes, run a job that scans the recent raw events and does GROUP BY ad_id, minute COUNT(*).

Where it breaks: latency and waste. A batch that runs every 5 minutes means the dashboard is up to 5 minutes stale - too slow for a “real-time” graph. Run it more often and you re-scan overlapping windows repeatedly, doing the same counting again and again. Batch also has an awkward relationship with late events: a click that happened at 09:59 but arrives at 10:03 either lands in the wrong bucket or gets missed by a window that already ran. Batch is right for the accurate daily total (part 4), but it cannot be the real-time path.

Approach B: Continuous stream processing with windows and watermarks

Run a stream processor (Flink) that consumes the clicks topic continuously and maintains tumbling windows - fixed, non-overlapping per-minute buckets keyed by ad_id:

keyBy(ad_id)
  .window(TumblingEventTimeWindows.of(1 minute))   # bucket by EVENT time
  .aggregate(count)                                 # increment per window
  -> upsert (ad_id, minute, count) into OLAP store

Two ideas make this correct and fast:

  • Event time, not processing time. A click is counted in the minute it happened (the event’s own timestamp), not the minute we happened to process it. Otherwise a processing hiccup would smear clicks into the wrong minutes. The stream processor buckets by event time.
  • Watermarks for late events. Events arrive out of order and late (mobile clients buffer offline, networks delay). A watermark is the processor’s assertion “I believe I have now seen all events up to time T.” A window emits its count when the watermark passes the window’s end plus an allowed lateness grace period (say 1-2 minutes). Events later than that are either dropped from the window or sent to a side output for the batch path to reconcile. This is the explicit fast-vs-accurate knob: a short lateness grace means fresher but occasionally-corrected counts; a long grace means slower but more-settled counts.
event time -->   09:58   09:59   10:00   10:01
                   |       |       |       |
watermark trailing real time by ~90s (allowed lateness)
When watermark passes 10:00, the 09:59 window is CLOSED and emitted.
A 09:59 click arriving after that = "late" -> side output -> batch fixes it.
  • Incremental aggregation. Flink keeps only the running count per open window in its state, not the raw events - so state is tiny (a count per (ad, minute)) and it emits incrementally as it goes, not by re-scanning. This is why it is both real-time and cheap.
  • Rollup hierarchy. Per-minute counts roll up into per-hour and per-day either in the same job (a second windowing stage) or by the OLAP store’s own rollup. The dashboard picks the coarsest granularity that covers its range so a “last 30 days” query reads 30 daily rows, not 43,200 minute rows.
  • Fault tolerance. Flink checkpoints its window state and its Kafka consumer offsets together, atomically. On failure it restarts from the last checkpoint and replays Kafka from the checkpointed offset - which is the foundation of exactly-once (next part). No committed count is lost or double-emitted across a restart.

3. Exactly-Once Counting Under At-Least-Once Delivery

This is the requirement that makes the numbers billable. The network and the queue both give us at-least-once delivery: a click beacon may be retried by the client, an ingestion node may crash after appending but before acking (client retries), and Kafka consumers re-read from the last offset after a restart (reprocessing some events). Naively, every one of those becomes a double count. Clicks are money, so a double count is an overcharge.

Approach A: Just count every event you receive (naive)

Increment on every consumed event.

Where it breaks: every retry and every replay inflates the count. A client that retries a slow beacon twice counts the click twice. A Flink restart that replays the last 10 seconds of Kafka re-counts every event in that window. An ingestion node that appends-then-crashes-before-ack gets the same event again on client retry - now in the log twice. At-least-once plus blind counting equals systematically-too-high numbers, and “too high” on a pay-per-click system means overbilling advertisers. Unacceptable.

Approach B: Deduplicate by a stable click_id

The fix is to make counting idempotent: give every click a stable unique identity and count each identity once.

  • Mint the click_id at the edge, once. The ad SDK generates the click_id when the click happens, and it stays the same across every retry of that beacon. So a client retrying the same click sends the same click_id - it is the same click, not a new one. (Generating the ID server-side on receipt would defeat this: two retries would get two IDs and count twice.)
  • Dedup in the stream processor. Flink keeps a set of click_ids it has already counted, keyed and windowed (you only need to remember IDs within the dedup horizon, not forever). On each event: if the click_id is already in the set, drop it; otherwise add it and count it. Because Flink’s state and Kafka offsets are checkpointed together, a replay after restart re-adds the same IDs to a restored set and re-drops the duplicates - so replay does not double count.
on event e:
   if seen.contains(e.click_id):   # retry or replay
        drop                        # already counted
   else:
        seen.add(e.click_id)        # bounded TTL, e.g. keep 24h of ids
        count[e.ad_id][minute(e)] += 1
  • Bound the dedup state. You cannot remember every click_id forever - that is 10B/day. Keep dedup state for a window (say 24 hours, matching the allowed-lateness horizon), after which a click_id is evicted. Duplicates arriving later than the horizon are astronomically rare and get caught by the batch recompute anyway. For memory efficiency, back the dedup set with RocksDB state or a probabilistic structure (a Bloom/Cuckoo filter fronting an exact check) so it fits.
  • Exactly-once sink, not just processing. Counting once internally is not enough - the write to the OLAP store must also be exactly-once, or a retried upsert double-applies. Two standard ways: (a) idempotent upserts keyed by (ad_id, minute) where Flink writes the absolute count for the window, not a +1 delta - re-writing the same window’s count is a harmless overwrite, not a double increment; or (b) transactional/two-phase commit sinks where the write commits atomically with the checkpoint. Prefer (a): writing the window’s absolute count as an upsert is naturally idempotent and dodges an entire class of double-apply bugs. This mirrors the absolute-vs-delta choice in any counting system - absolute values survive retries, deltas do not.

Put together: unique click_id minted once at the edge + windowed dedup in Flink + idempotent absolute-count upsert to the sink = effectively-exactly-once counting on top of at-least-once transport. That chain is the answer to “how do you not double count,” and it is worth stating end to end in the interview.

4. Fast-Approximate vs Slow-Accurate (Lambda-Style Reconciliation)

The last tension: the dashboard wants fresh (seconds), billing wants exact (audit-grade). A single path cannot be both - being fresh means emitting before all late events arrive (so approximate), and being exact means waiting for everything and recomputing (so slow).

Approach A: Pick one (naive)

Serve only the stream’s real-time counts, or serve only the nightly batch totals.

Where it breaks: stream-only means the numbers keep shifting as late events trickle in and a transient bug in the streaming job permanently corrupts a count with no way to fix it - unacceptable for billing. Batch-only means the dashboard is a day stale - unacceptable for a “real-time” product. Neither alone satisfies both requirements.

Approach B: Two paths that reconcile (fast path + accurate path)

Run both, from the same immutable raw log, and let the accurate path overwrite the fast path once a period settles:

  • Fast path (speed layer): the Flink stream job above. Emits per-minute counts within seconds, with a short allowed-lateness. These power the live dashboard and the current, still-open period. Explicitly approximate - the last minute or two is still settling.
  • Accurate path (batch layer): the raw events are also archived to object storage (partitioned by hour). A batch job (Spark) runs after each hour/day closes and computes the exact COUNT DISTINCT click_id GROUP BY ad_id, bucket over the complete, de-duplicated raw log - including every late event that the stream job may have dropped past its grace period. It writes these settled, exact totals into the OLAP store, overwriting the approximate streamed values for that closed period.
  • Reconciliation rule: for any open period, serve the streamed (approximate) value. Once a period is closed and batch-verified, serve the batch (exact) value. Billing reads only closed, batch-verified totals. So the dashboard is fresh (stream) and the invoice is exact (batch), from one source of truth.
    time ------------------------------------------------->
    [ closed hours: EXACT batch totals ] [ current hour: APPROX stream ]
                     ^ billing reads here      ^ dashboard tail reads here
    batch job recomputes each closed hour from the raw log and overwrites.
ConcernFast path (stream)Accurate path (batch)
Freshnesssecondshourly / daily
Correctnessapproximate (late events pending)exact, audit-grade
Handles late eventsup to allowed-lateness onlyall of them (full replay)
Recovers from a processing bugno (count is corrupted)yes (recompute from raw log)
Powerslive dashboard, current periodbilling, closed periods

The reason both paths can coexist cleanly is the earlier decision: counts are a derived view of the immutable raw log. Because the log is the source of truth, the batch path can always recompute the truth, and the stream path is just a fast, disposable approximation that the truth eventually replaces. (If you want to avoid maintaining two codebases, a Kappa variant runs a single stream engine and reprocesses history by replaying the log through the same job - but the two-path split is the clearest way to reason about fast-vs-accurate in an interview.)

API Design & Data Schema

Ingestion API (the write plane)

POST /v1/click
  {
    "click_id":     "clk_8f2a91c4",   # UNIQUE, minted once by the SDK at click time
    "ad_id":        "ad_4471",
    "campaign_id":  "cmp_88",         # optional; else resolved from ad_id
    "advertiser_id":"adv_12",         # optional; else resolved from ad_id
    "ts":           1751932800123,    # event time (ms) - when the click happened
    "geo":          "IN-KA",          # optional; else resolved from IP at edge
    "device":       "mobile",
    "user_agent":   "...",            # for downstream fraud filtering
  }
  200 OK   -> event durably appended to Kafka (acks=all). Counting is async.

Notes:
  - 200 is returned AFTER the durable append, not after counting.
  - click_id is the idempotency key; retries reuse the SAME click_id.
  - This endpoint sits behind the fraud-filter gate; suspicious clicks are
    tagged/dropped before they reach the log.

Query API (the read plane)

GET /v1/metrics?ad_id=ad_4471&from=2026-07-08T09:00&to=2026-07-08T10:00&granularity=minute
  granularity  minute | hour | day  (server picks coarsest rollup covering the range)
  filters      &campaign_id= &advertiser_id= &geo= &device=  (optional slices)
  200 OK   (served from pre-aggregated rollups + result cache)
  {
    "ad_id": "ad_4471",
    "granularity": "minute",
    "series": [
      { "bucket": "2026-07-08T09:00", "clicks": 812,  "settled": true  },
      { "bucket": "2026-07-08T09:01", "clicks": 905,  "settled": true  },
      { "bucket": "2026-07-08T09:59", "clicks": 44,   "settled": false }  # still streaming
    ]
  }

GET /v1/metrics/top?advertiser_id=adv_12&from=...&to=...&limit=10
  200 OK   -> top ads by clicks for an advertiser in a range (from daily rollups)

The settled flag tells the UI which buckets are exact (batch-verified, closed) versus still-approximate (streaming, open) - the fast-vs-accurate distinction surfaced in the response.

Data model: immutable raw log + derived rollups

Three stores for three jobs - a durable event log (source of truth), an OLAP store (queryable rollups), and cold archival (exact recompute).

Raw event log - Kafka (source of truth, replayable):

topic:        clicks
partitioning: by ad_id  (hot ads sub-partitioned by ad_id + hash(click_id)%K)
replication:  factor 3, acks=all on produce
retention:    ~14 days (recompute window + safety net)
message:      { click_id, ad_id, campaign_id, advertiser_id, ts, geo, device }

Raw event archive - Object store (S3/GCS, for exact batch recompute):

path:   s3://clicks-raw/dt=2026-07-08/hr=09/part-*.parquet
format: columnar (Parquet), partitioned by date + hour
purpose: the immutable log the batch job scans for exact COUNT DISTINCT.

Aggregated rollups - OLAP store (Druid / ClickHouse / Pinot, what the dashboard reads):

clicks_by_minute   (and _by_hour, _by_day rollup tables)
  ad_id          STRING     part of key / dimension
  bucket         TIMESTAMP  minute (or hour/day) - part of key
  campaign_id    STRING     dimension
  advertiser_id  STRING     dimension
  geo            STRING     dimension
  device         STRING     dimension
  clicks         BIGINT     the count (metric)
  settled        BOOL       true once batch-verified, else streaming/approx
  Sort/index:    (ad_id, bucket) primary; secondary on (advertiser_id, bucket)
  Rollup:        per-minute -> per-hour -> per-day

Why an OLAP columnar store, not a relational DB or the raw log, for serving: dashboard queries are analytical - “sum clicks for these ads over this time range, grouped by minute, sliced by geo.” Columnar OLAP stores (Druid/ClickHouse/Pinot) are built for exactly that: they scan only the needed columns, exploit the time-bucketed partitioning, and pre-aggregate rollups so a range query touches tens of rows, not billions. A row-store relational DB would choke on the aggregate scans; the raw log cannot answer a grouped range query at all. And it is derived and rebuildable - lose the OLAP store and you recompute it from the raw log.

Dedup state - Flink managed state (RocksDB-backed):

per key (ad_id):  set of seen click_id with ~24h TTL, checkpointed with offsets.
purpose:          drop retries/replays so each click_id counts once.

Why the split - Kafka for un-droppable sequential ingest, object store for cheap immutable archival, OLAP for fast grouped range reads, Flink state for dedup: each store does the one thing it is best at, and every derived store can be rebuilt from the raw log, which is the only thing we treat as precious.

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each:

1. Ingestion write throughput (~1M/sec, un-droppable). No single database sustains a million durable writes/sec, and a hot counter row serializes on a lock. Fix: stateless ingestion appending to a partitioned, replicated Kafka log with acks=all. Sequential appends scale to millions/sec, producers never block on consumers, and the log absorbs spikes. Counting moves off the ingest path entirely.

2. Hot ad / hot partition. A viral ad sends a disproportionate share of clicks to one Kafka partition and one Flink key, hotspotting a single node. Fix: sub-partition the hottest ads by ad_id + hash(click_id) % K so their load spreads across K partitions/tasks, then sum the K sub-counts at the rollup stage. Detect hot ads dynamically and only split those, keeping the common case simple.

3. Double counting under retries and replays. At-least-once transport plus blind counting inflates the numbers, which is overbilling. Fix: unique click_id minted once at the edge + windowed dedup in Flink + idempotent absolute-count upserts to the sink. Retries carry the same id and are dropped; replays re-drop from restored checkpoint state; absolute-count upserts make re-writes harmless. Effectively-once counting on at-least-once transport.

4. Late and out-of-order events. Mobile clients buffer offline; events for 09:59 arrive at 10:03 and land in the wrong bucket or get missed. Fix: event-time windowing with watermarks and an allowed-lateness grace; events past the grace go to a side output and are absorbed by the batch recompute. The lateness grace is the explicit fresh-vs-settled knob.

5. Real-time freshness vs billing exactness. One path cannot be both fresh and audit-exact. Fix: two paths reconciling - the Flink fast path serves the live, approximate current period; the Spark batch path recomputes exact totals from the immutable raw log and overwrites closed periods. Billing reads only settled, batch-verified totals.

6. Query-side scan cost. Dashboards over long ranges would scan billions of rows if they hit raw or per-minute data. Fix: pre-aggregated rollup hierarchy (minute -> hour -> day) in a columnar OLAP store; the query service picks the coarsest granularity covering the range, plus a result cache for repeated dashboard polls. A “last 30 days” query reads 30 rows.

7. Stream processor failure. Flink node loss could lose in-flight window state or re-emit counts. Fix: checkpointing of window state and Kafka offsets together; on restart, replay from the last checkpoint. Combined with idempotent upserts, no committed count is lost or duplicated across a restart. Worst case, recompute the affected window from the raw log.

8. Kafka / log loss. The raw log is the source of truth; losing it loses everything. Fix: replication factor 3 with acks=all, plus continuous archival of raw events to durable object storage. The log survives broker loss, and the archive is the long-term immutable record every aggregate can be rebuilt from.

9. Backpressure during spikes. A 1M/sec burst can outrun the stream processor even though Kafka accepted it. Fix: the log is the buffer - Flink drains at its sustained rate, trading a little extra freshness lag (seconds) for never dropping a click. Scale Flink parallelism (more tasks per key group) and Kafka partitions to raise sustained throughput; the queue smooths the transient.

10. Storage growth of raw events (~1 TB/day). The raw log cannot grow forever. Fix: short Kafka retention (~14 days) for the recompute window, with events tiered to cheaper object storage for longer audit retention, and rollups aged out (keep per-minute for weeks, per-day for years). Counts are tiny; only raw events are fat, and they move to cold storage.

Wrap-Up

The trade-offs that define this design:

  • Durable log over database ingestion. We accept clicks by appending to a replicated Kafka log, not writing a database, trading a familiar CRUD model for the ability to absorb 1M un-droppable events/sec and decouple ingestion speed from counting logic. The log is the shock absorber and the source of truth.
  • Stream processing over batch for freshness. We fold events into event-time tumbling windows with watermarks so the dashboard is seconds-fresh, trading some settling delay (late events) for a live product - and we make that delay an explicit allowed-lateness knob.
  • Exactly-once via idempotency, not magic. A unique click_id minted once at the edge, windowed dedup, and idempotent absolute-count upserts turn at-least-once transport into effectively-once counts. That chain is why the numbers are billable.
  • Fast-approximate and slow-accurate, reconciled. The stream path serves the live approximate number; the batch path recomputes the exact number from the immutable raw log and overwrites closed periods. One source of truth, two views, and billing reads only the settled one.
  • Derived counts over precious counts. Every aggregate is a rebuildable view of the raw event log, so any store below the log - Flink state, OLAP rollups - is disposable and can be recomputed. Only the log is precious.

One-line summary: a stateless ingestion tier appending an un-droppable click stream to a replicated Kafka log, a Flink stream processor deduplicating by click_id and folding events into event-time windowed counts served fresh from a columnar OLAP store, and a Spark batch path recomputing exact totals from the same immutable log for billing - trading a few seconds of freshness on the live path for exactly-once, audit-grade counts under a 1M-clicks-per-second firehose.