44. Ad Click Aggregation & Top-K

Business context. Advertisers pay per click. Aggregated counts drive billing (must be exact and auditable) and real-time dashboards and budget pacing (must be fresh within about a minute). Fraudulent clicks must be filtered out, or advertisers lose trust and demand refunds.

Requirements. Ingest 1M+ click events/s at peak; aggregates per ad, per minute; queries for the top-K ads per category over the last N minutes; dedupe; late events up to hours later; reconciliation with billing.

Design (Kappa + reconciliation).

Click ─▶ click redirect service (log event with click_id, then 302 to advertiser)
      ─▶ Kafka (partition by ad_id) [retain 7+ days for replay]
      ─▶ Stream job (Flink): dedupe by click_id (keyed state with TTL) ─▶ fraud filter (rules + model scores)
           ─▶ tumbling 1-min windows by event time, watermarks, allowed lateness
           ─▶ sink: OLAP store (Pinot/Druid/ClickHouse) for dashboards; budget pacing service
      ─▶ Raw events ─▶ data lake ─▶ daily batch recompute ─▶ authoritative billing numbers
           ─▶ reconcile vs streaming results; differences > threshold → alert
Top-K: per window, per category, keep count per ad → heap of size K (exact for this cardinality)
       or Count-Min Sketch + heap when cardinality is huge (approximate, bounded memory)

Deep dives.

Exactly-once counting: at-least-once delivery plus dedupe on click_id, with checkpointed state and idempotent or transactional sinks (§21.3).
Late data: watermarks with allowed lateness update windows that were already emitted. Billing uses batch numbers anyway.
Hot ads: a Super Bowl ad on one partition. Pre-aggregate locally (combiner) before the keyed shuffle, or salt the key and merge.
Approximate vs exact: Count-Min Sketch overestimates counts within bounded error and is fine for "trending". Never bill from a sketch.

Strategic lens. Two consumers with different needs: finance needs exact and auditable, eventually; pacing and dashboards need fresh, approximately right, now. Designing explicitly for both, with reconciliation between them, is the senior insight.

🏗️ Flow: click to bill.

 Impression served (impression_id, ad_id, campaign, price) ─▶ log
 Click: GET /c?imp=…&sig=… → validate signature → log click {click_id, imp_id, ts} → 302 to advertiser
   ─▶ Kafka "clicks" (key=ad_id)
   ─▶ Flink: dedupe(click_id, 24 h state) → join with impression (by imp_id, within 1 h; late → side output)
            → fraud scoring (IP/device velocity, known bot lists, model) → valid / invalid
            → 1-min windows per (ad_id, campaign) → OLAP + pacing service (budget remaining)
   ─▶ raw + scored events → lake (hourly partitions)
 Nightly batch: recompute from raw with final fraud model → billing totals → reconcile with stream
   → invoice; diffs > 0.1% → investigate before billing

🌍 How it's built in production.

Google Photon (SIGMOD 2013): joins clicks with queries in real time, exactly once, across geographically distributed data centers. It uses a Paxos-replicated "IdRegistry" of already-joined event IDs, so either data center can process an event without double counting.
Google Mesa (VLDB 2014): a geo-replicated, near-real-time warehouse for ad metrics with atomic batch updates, the serving side of ad reporting.
Pinot/Druid at LinkedIn, Uber, and others: real-time OLAP for user-facing analytics dashboards with sub-second queries on fresh data.

⚠️ Corner cases.

Click arrives before its impression (separate pipelines, different latencies). Join with a time-bounded buffer, and send unjoined clicks to a retry side output.
Budget overspend from pacing lag: the stream is 60 s behind, and a campaign spends past its daily budget during that window. Use conservative pacing (slow down near the limit), and define who pays for overdelivery as a business rule.
Advertiser time zones: "daily budget" in the advertiser's local day, with DST days of 23 or 25 hours.
Retroactive fraud decisions: clicks invalidated after billing need credits and adjustments in the ledger, never mutations of history.
Bot bursts: sudden 100× clicks on one ad. Velocity rules at ingestion, and alerting on per-advertiser anomalies.