Design Ad-Click Aggregation (Real-Time Stream Processing)

TL;DR. Ad-click aggregation is a billing pipeline disguised as an analytics problem. At 1M clicks/sec peak, the system must deliver exactly-once semantics end-to-end (Kafka idempotent producer + Flink aligned checkpoints + idempotent sink) because every click is an invoice line item^[1]. The architecture layers a three-tier fraud filter (rules, velocity, ML) over a Kappa-style streaming core, sinks aggregates into a real-time OLAP store (Druid or Pinot) for sub-second dashboard queries^[2], and reconciles against a batch audit trail for billing correctness. The pivotal trade-off: exactly-once costs 3-30% throughput depending on batch size and commit interval, but eliminates chargebacks^[3].

Learning Objectives#

• Design an end-to-end click ingestion pipeline that achieves exactly-once semantics across source, state, and sink
• Explain the three-component chain (Kafka transactions, Flink checkpoints, idempotent sink) and what breaks when any link is missing
• Apply layered fraud detection (rules, velocity aggregates, ML scoring) as in-stream operators
• Justify the choice of a real-time OLAP store over a relational database for advertiser dashboards at billion-row scale
• Estimate capacity for a system processing 10B impressions and 100M clicks per day
• Trade off freshness against correctness in the context of advertiser billing

Intuition#

Counting clicks sounds trivial. A GROUP BY campaign_id on a Postgres table handles 100 advertisers. At 100 million clicks per day with peaks of 1 million per second, that approach collapses, and the reason is not volume alone. It is money.

Every click is a billable event. Under-count by 0.1% and you lose millions in revenue annually on a platform the size of Google advertising (~$264B in FY2024)^[4]. Over-count by 0.1% and advertisers dispute invoices, demand credits, and churn. A single production bug that double-counts clicks for an hour is an earnings-call event.

The naive "at-least-once plus dedup at read time" approach fails because dedup at read time is not auditable. You cannot prove to an advertiser that their invoice is correct if the pipeline silently dropped or duplicated events somewhere in the middle. The insight that unlocks the design: treat the click pipeline as a financial ledger, not an analytics pipeline. Every event must be processed exactly once, provably, with a batch reconciliation job as the audit trail.

This means the architecture is not "Kafka plus a consumer." It is Kafka with transactional producers, Flink with aligned checkpoint barriers that atomically commit state and sink transactions, and an OLAP store that ingests pre-aggregated, fraud-filtered, exactly-once-committed records. The complexity is justified because the output is an invoice.

Requirements#

Clarifying Questions#

• Q: What is the billing model? Assume: Cost-per-click (CPC). Every valid click generates a charge. Invalid clicks (fraud) must be excluded before billing but retained for audit.

• Q: What freshness do advertisers expect? Assume: Dashboard refresh within 5 seconds of click. Billing reconciliation runs hourly with final invoice daily.

• Q: What fraud rate is acceptable? Assume: Less than 0.5% of billed clicks should be invalid. Vendor estimates suggest 14-22% raw invalid traffic on search campaigns^[5]; the system must filter most of it.

• Q: Multi-region? Assume: Yes. Clicks arrive from global edge PoPs. Processing is regional with cross-region reconciliation for billing.

• Q: What attribution model? Assume: Last-click for billing (simple, auditable). Data-driven multi-touch for reporting (requires sufficient conversion volume per campaign for statistical stability; Google recommends at least 200 conversions in 30 days)^[6].

• Q: Privacy constraints? Assume: iOS ATT opt-out at ~75%^[7]. SKAdNetwork postbacks arrive 24-48 hours delayed^[8]. Chrome deprecated Privacy Sandbox Attribution Reporting in 2025; third-party cookies remain. No per-user conversion logs for opted-out users.

Functional Requirements#

• Ingest click events from edge trackers with exactly-once delivery to the processing layer
• Aggregate clicks per campaign, per advertiser, per dimension (country, device, placement) in tumbling 1-minute windows
• Detect and flag invalid traffic in-stream across three layers (rules, velocity, ML)
• Serve pre-aggregated metrics to advertiser dashboards with sub-second query latency
• Reconcile streaming output against the raw event log via batch for billing correctness
• Support budget pacing: real-time spend counters that throttle ad serving when budgets approach exhaustion

Non-Functional Requirements#

• Load: 10B impressions/day, 100M clicks/day (1% CTR), peak 1M clicks/sec
• Latency: click-to-dashboard visibility under 5 seconds; query p99 under 200 ms
• Accuracy: 99.999% event accuracy on billable data (no double-bills, no lost clicks)
• Availability: 99.99% on the ingestion path; 99.9% on the dashboard query path
• Durability: zero data loss; raw events retained in object storage for 7 years (audit)

Capacity Estimation#

Key ratios:

• Impression:click = 100:1 (1% CTR). Impressions dominate storage; clicks dominate processing complexity.
• Valid:invalid = ~80:20. Vendor estimates suggest roughly 14-22% of raw clicks are invalid traffic^[5:1]; after filtering, less than 0.5% of billed clicks should be fraudulent.
• Stream:batch = real-time aggregates feed dashboards; batch reconciliation feeds billing. Both read from the same Kafka topic (Kappa architecture).

API and Data Model#

API Design#

POST /v1/click?t=<signed_token>
  Response: 302 Location: <advertiser_landing_url>
  Side effect: click event produced to Kafka (idempotent)

GET /v1/campaigns/{id}/metrics?window=1h&dimensions=country,device
  Response: 200 { "clicks": 142000, "spend": 8520.00, "ctr": 0.012,
                  "breakdowns": [...], "as_of": "2026-05-04T12:00:05Z" }

GET /v1/campaigns/{id}/budget
  Response: 200 { "daily_budget": 50000, "spent_today": 32100,
                  "pacing_rate": 0.85, "estimated_exhaustion": "18:30Z" }

POST /v1/reports/reconciliation
  Body: { "date": "2026-05-03", "advertiser_id": "adv_123" }
  Response: 200 { "stream_total": 1420000, "batch_total": 1420003,
                  "delta": 3, "status": "within_tolerance" }

Data Model#

-- Kafka click event (Avro schema, partitioned by campaign_id)
record ClickEvent {
  string   request_id;       -- idempotency key
  string   campaign_id;      -- partition key
  string   ad_id;
  string   advertiser_id;
  string   user_cohort;      -- privacy-safe cohort, not user_id
  string   geo_country;
  string   device_type;
  long     timestamp_ms;     -- event time
  string   click_token;      -- HMAC-signed, validates origin
  float    fraud_score;      -- populated by Flink (0.0 = clean)
  string   fraud_flags;      -- comma-separated rule IDs that fired
}

-- OLAP rollup table (Druid/Pinot segment schema)
table click_aggregates (
  campaign_id     string,     -- primary dimension
  window_start    timestamp,  -- 1-minute tumbling window
  geo_country     string,
  device_type     string,
  valid_clicks    long,
  invalid_clicks  long,
  spend_cents     long,
  unique_users    long        -- HyperLogLog sketch
)

-- Redis hot counters (per campaign, today)
HASH campaign:{id}:today {
  spend_cents: 3210000,
  click_count: 142000,
  last_click_ts: 1714824005000
}

High-Level Architecture#

The click data path from edge tracker to advertiser dashboard, with a parallel batch reconciliation branch feeding billing.

The write path: a user taps an ad. The click tracker at the edge verifies the HMAC-signed token (preventing forged clicks), produces the event to Kafka with enable.idempotence=true and acks=all, and returns a 302 redirect to the advertiser's landing page. Total tracker latency: single-digit milliseconds.

The stream path: Flink consumes from the clicks topic, applies fraud detection (rules, velocity, ML), computes 1-minute tumbling-window aggregates keyed by (campaign_id, geo, device), increments Redis hot counters for budget pacing, and sinks aggregates to a second Kafka topic via exactly-once transactions. The OLAP store ingests from the aggregates topic continuously.

The batch path: raw events land in S3 via Kafka Connect. A Spark job runs hourly, re-aggregates from the raw log, and compares against the stream output. Deltas beyond tolerance trigger alerts and billing adjustments.

Deep Dives#

Exactly-once semantics: the three-component chain#

The most common interview mistake is claiming "at-least-once plus idempotent writes equals exactly-once." It does not. Idempotent writes handle duplicate delivery, but they do not handle the case where Flink processes an event, updates internal state, but crashes before the sink commits. On recovery, the event is reprocessed with stale state, producing a different aggregate. True exactly-once requires atomicity across source offset, operator state, and sink output^[9].

Component 1: Kafka idempotent + transactional producer. KIP-98 (Kafka 0.11, 2017) introduced producer_id and per-partition sequence numbers^[3:1][10]. The broker rejects duplicates from producer retries. Adding transactional.id makes the id survive restarts: the TransactionCoordinator fences zombie producers via producer_epoch, so a crashed producer cannot commit stale transactions after restart^[10:1].

Component 2: Flink aligned checkpoint barriers. The JobManager injects a barrier into the source stream. Each operator snapshots its state to S3 when the barrier passes through. The Kafka sink calls preCommit (flush pending writes inside an open Kafka transaction) but does not commit yet^[9:1][11].

Component 3: Atomic commit on checkpoint success. Only after all operators acknowledge the barrier does the JobManager call notifyCheckpointComplete. The sink then calls commitTransaction on the Kafka TransactionCoordinator. Consumers with isolation.level=read_committed see the records only after this commit^[9:2][3:2].

Kafka transaction coordinator, Flink checkpoint barrier, and sink transaction commit atomically; failure at any stage rolls back the entire set.

Cost: Confluent benchmarks show 3% overhead versus at-least-once (acks=all) for the producer, and 15-30% for the Streams API with short commit intervals (100 ms); real-world overhead for ad workloads with frequent checkpoints is typically in this upper range^[3:3]. This is the price of correctness when the output is an invoice.

In-stream fraud detection: rules, velocity, ML#

Vendor estimates suggest invalid traffic ranges from 14% to 22% of search clicks^[5:2]. The system must filter most of it before billing while retaining all events for audit. Each layer tags events with a fraud_flag; no layer deletes data.

Layer 1: Rules (stateless, p99 < 1 ms). IP blocklists (known data centers, VPN exit nodes), user-agent denylist (headless browsers, known bot signatures), and click-token validation (reject expired or malformed tokens)^[12]. This runs as the first Flink operator. Google's Ad Traffic Quality team uses similar rule-based filters as the first line^[13].

Layer 2: Velocity aggregates (stateful, windowed). Flink sliding windows compute: same_ip_same_ad_clicks_in_10s, same_user_clicks_in_1m, new_campaign_click_rate_vs_baseline. A Count-Min Sketch tracks approximate frequencies for high-cardinality dimensions (IP x ad_id) without exploding state. Thresholds are tuned per-campaign based on historical baselines.

Layer 3: ML scorer (async, p99 < 50 ms). A gradient-boosted tree (XGBoost/LightGBM) trained on labelled invalid traffic data scores each click on enriched features: time-since-impression, mouse-movement entropy, device fingerprint novelty, geo-mismatch with campaign targeting. The model is served via a feature store (Feast/Tecton) and called asynchronously; results arrive before the window closes.

Every click passes through three fraud layers in order; each tags but never drops, letting billing exclude flagged events while analytics retains full data for audit.

Real-time OLAP for advertiser dashboards#

Advertisers refresh dashboards every few seconds. The query pattern is: SELECT SUM(valid_clicks), SUM(spend) FROM aggregates WHERE campaign_id = X AND window_start >= now() - 1h GROUP BY geo_country. This is a high-concurrency, low-latency analytical query over billions of pre-aggregated rows.

Why not Postgres? At 500K campaigns x 1440 minutes/day x 5 dimensions, the rollup table grows by 3.6B rows/day. Postgres cannot serve thousands of concurrent analytical queries at sub-second latency over this volume.

Druid separates ingestion (MiddleManagers), query serving (Historicals + Brokers), and coordination (Coordinators + Overlords)^[14]. Segments are column-oriented, bitmap-indexed, and immutable. Streaming ingestion via the Kafka Indexing Service materializes new segments in near real time. Query latency: sub-second to a few seconds on trillions of rows^[15].

Pinot (LinkedIn, SIGMOD 2018) takes a similar approach with Helix-based cluster management and a star-tree index that pre-aggregates multi-column rollups^[2:1]. LinkedIn reports thousands of QPS with p99 under 100 ms after smart routing optimizations^[16].

ClickHouse uses MergeTree columnar storage with vectorized execution. Strongest for batch-flavored OLAP with SQL; weaker on high-concurrency real-time ingestion compared to Druid/Pinot^[17].

Our pick: Druid for the primary dashboard path (optimized for high-concurrency, real-time ingestion, sub-second queries). ClickHouse as a secondary store for ad-hoc analyst queries that need full SQL expressiveness.

Druid's ingestion path: Kafka Indexing Service builds immutable columnar segments on MiddleManagers, publishes to deep storage, and Historicals serve sub-second queries via the Broker's scatter-gather layer.

Real-World Example#

Uber Eats Ads: Flink + Kafka + Pinot for exactly-once ad attribution.

Uber's ad events processing system manages the flow of click and impression events, cleanses them, aggregates by campaign, attributes clicks to Uber Eats orders, and feeds both advertiser reporting and budget pacing^[1:1]. The stated reliability target is 100% accurate ad attribution, because clicks map directly to advertiser charges and double-counting is a billing incident.

The architecture mirrors our design: Kafka as the source of truth, Flink for stateful stream processing with exactly-once checkpoints, and Pinot for real-time OLAP serving advertiser dashboards^[1:2]. Uber chose exactly-once over at-least-once explicitly because the pipeline output is financial: a duplicated click event means an overcharged restaurant advertiser.

Key engineering decisions:

• Flink over Samza for the attribution job because Flink's event-time semantics and TwoPhaseCommitSinkFunction integrate cleanly with Kafka's transactional producer^[1:3][9:3].
• Pinot over Druid because Uber had an existing Pinot platform powering hundreds of real-time analytics use cases, and operational familiarity outweighed Druid's slightly larger community^[1:4][18].
• Attribution is closed-loop. Unlike third-party ad networks, Uber Eats owns the marketplace. When a user clicks an ad and places an order, Uber has ground-truth order data. Attribution correctness is verifiable end-to-end without relying on third-party conversion pixels.

The insight non-experts miss: Uber did not build exactly-once because it is theoretically elegant. They built it because the alternative (at-least-once with reconciliation) meant issuing credits to thousands of restaurant advertisers every billing cycle, which destroyed advertiser trust.

Uber's closed-loop attribution: because Uber Eats owns the marketplace, the Flink job can join click events to order events with 100% ground-truth accuracy.

Trade-offs#

The biggest meta-decision: exactly-once versus at-least-once. At-least-once is simpler and faster, but it shifts correctness responsibility to downstream reconciliation. For ad billing, reconciliation is not optional either way (auditors require it), but exactly-once reduces the delta from thousands of events to single digits, making reconciliation a verification step rather than a correction step.

Scaling and Failure Modes#

At 10x (1B clicks/day, 10M/sec peak):

• Kafka partitions must increase from ~100 to ~1,000 per topic. Flink parallelism scales linearly with partitions.
• OLAP ingestion becomes the bottleneck. Mitigation: tiered ingestion with regional Druid clusters and a global query broker.
• Redis hot counters approach memory limits. Mitigation: shard by advertiser_id across a Redis Cluster.

At 100x (10B clicks/day):

• Single Flink job cannot hold window state. Mitigation: split into per-region jobs with regional Kafka clusters; cross-region aggregation via a lightweight merge job.
• Fraud ML inference at 100M/sec requires GPU-accelerated serving or pre-scored feature vectors cached in the feature store.

At 1000x (Netflix/Twitter scale: trillions of events/day):

• Netflix Keystone operates over 15,000 Flink jobs processing over 60 PB of data per day^[19]. The architectural shift: per-job Flink clusters (not shared multi-tenant), S3 for checkpoint state, and a declarative reconciliation architecture that continuously drives jobs toward desired state^[20].

Failure modes:

• Flink job crash mid-checkpoint. Recovery: Flink restores from the last completed checkpoint. Kafka consumer offsets roll back to the checkpoint's committed position. Exactly-once guarantees hold; the only cost is reprocessing latency (seconds to minutes depending on checkpoint interval).
• Kafka broker failure. With acks=all and replication factor 3, no data is lost. Producers retry transparently via the idempotent protocol. Consumers rebalance partitions across surviving brokers.
• OLAP ingestion lag. Dashboard shows stale data. Mitigation: expose as_of timestamp in every API response so advertisers know data freshness. Alert on lag exceeding 30 seconds.

Common Pitfalls#

Follow-up Questions#

Exercise#

Exercise 1: Detecting a distributed bot network#

A fraud pattern surfaces: a bot network clicks ads from 10,000 distinct IPs, each clicking once per minute, spread across 500 campaigns. No single IP or campaign trips a velocity rule. Design the detection approach: what streaming features would you compute, what model inputs would you use, and how would you retroactively credit affected advertisers?

Key Takeaways#

• Exactly-once is real but expensive. Use it where money flows (billing); use at-least-once for analytics where a 0.01% delta is acceptable.
• Fraud is layered defense. Rules are fast and auditable, velocity catches patterns, ML catches novelty. You need all three; no single layer suffices^[12:2][13:1].
• The batch job is not a failure mode; it is the audit trail. Without it, you cannot prove to an advertiser that their invoice is correct.
• OLAP stores exist because Postgres cannot do "billions of rows, many dimensions, sub-second query, real-time ingestion" simultaneously.
• Privacy is now a first-class architectural constraint. iOS ATT, SKAdNetwork, and the deprecation of cross-site tracking APIs change what data the pipeline can retain, not just how it is displayed^[7:2].
• Budget pacing is a control-theory problem. Treat it as a feedback loop with damping, not a threshold check^[23:2].

Further Reading#

• Real-Time Exactly-Once Ad Event Processing (Uber). The closest public analog to this chapter's design; walks through Kafka + Flink + Pinot for ad billing.
• Exactly-Once Semantics Are Possible: Here's How Kafka Does It (Confluent). The canonical explanation of KIP-98, idempotent producers, and transactional messaging.
• End-to-End Exactly-Once Processing in Apache Flink (Flink blog). Nowojski and Winters explain the TwoPhaseCommitSinkFunction and checkpoint barrier protocol.
• Pinot: Realtime OLAP for 530 Million Users (SIGMOD 2018). LinkedIn's case for real-time OLAP; covers star-tree indexing and ingestion architecture.
• Apache Druid Architecture. Official reference for the six-service-type architecture and segment lifecycle.
• Preventing Overdelivery via Daily Budget Pacing at DoorDash (arXiv). Production pacing paper with concrete control-loop design and "Smooth Fast Finish" approach.
• Netflix Keystone Real-time Stream Processing Platform. Trillions of events/day; per-job Flink clusters; declarative reconciliation architecture.
• Chrome Privacy Sandbox Attribution Reporting API (deprecated 2025). The post-third-party-cookie measurement specification that briefly reshaped conversion pipelines before Google reversed course.

Flashcards#

References#