Design an Ad Click and Impression Aggregation System

Problem

An ads platform serves impressions and records clicks. Advertisers need near-real-time dashboards of impressions, clicks, CTR and spend per campaign. Finance bills advertisers daily based on valid clicks. Design the aggregation system.

Clarifying questions

1. Requirements

• Real-time: per-minute counts per ad (impressions, clicks, spend), sub-second queries by advertisers (thousands of concurrent users).
• Billing: exactly-once counting, invalid-traffic (IVT) filtering, immutable daily invoices, reconciliation.
• History: 2+ years for analysis.

2. Estimates

Impressions 10B/day → 100k/s avg, 500k/s peak; clicks 1B/day → 10k/s avg, 50k/s peak
~300 B/event Avro → ~165 MB/s peak → Kafka 128+ partitions
Raw storage: 11B × 300 B ≈ 3.3 TB/day → ~0.5 TB/day Parquet → 2 years ≈ 365 TB
Aggregates: 1M ads × 1,440 minutes = 1.44B minute-rows/day worst case;
            realistically ~100M (sparse) → OLAP store with rollups to hour/day after 7 days

3. Architecture

Requirements diverge (fast/approximate vs slow/exact), so this is one of the rare places where a Lambda-style split is justified, but with shared code.

flowchart LR
    ADS[Ad servers] -->|impression| K1[[Kafka: impressions]]
    CLK[Click redirect service] -->|click, click_id| K2[[Kafka: clicks]]
    K1 --> F[Flink / Spark SS<br/>dedup + 1-min aggregates]
    K2 --> F
    F --> AGG[[Kafka: agg_minute]]
    AGG --> OLAP[(Pinot / Druid<br/>real-time table)]
    OLAP --> DASH[Advertiser dashboards]
    K1 --> BR[(Bronze Delta)]
    K2 --> BR
    BR --> IVT[Batch IVT / fraud filtering<br/>bots, click farms, rules + ML]
    IVT --> SIL[(silver.valid_clicks<br/>exact, deduped)]
    SIL --> BILL[(gold.billing_daily<br/>immutable per day)]
    BILL --> INV[Invoicing]
    SIL --> OFF[(OLAP offline table<br/>replaces real-time segments)]
    OFF --> OLAP

The OLAP store’s hybrid table (Pinot real-time + offline segments) lets the batch-corrected numbers replace the streaming numbers for completed days, so advertisers converge to billing-grade numbers.

4. Data model

-- click event
click_id STRING (UUID generated by redirect service), impression_id STRING, ad_id, campaign_id,
advertiser_id, user_id_hash, ip_hash, user_agent, country, device, click_ts, received_ts, cost_micros

-- real-time aggregate (grain: ad × minute × country × device)
minute_ts, ad_id, campaign_id, country, device, impressions BIGINT, clicks BIGINT, spend_micros BIGINT

-- billing (grain: advertiser × campaign × day), immutable once closed
bill_date, advertiser_id, campaign_id, valid_clicks, invalid_clicks, amount_micros, run_id, closed_at

Money in integer micros (never floats).

5. Deep dives

5.1 Exactly-once counting

sequenceDiagram
    participant C as Click service
    participant K as Kafka
    participant S as Stream job
    participant O as Sink (agg topic / Delta)
    C->>K: produce (idempotent producer, key=click_id)
    K->>S: read offsets 100-199
    S->>S: dedup by click_id (state, 24h TTL)
    S->>O: write aggregates in a transaction
    S->>K: commit offsets in the same transaction
    Note over S,O: crash before commit means abort and re-read, no double count

• Source of duplicates: client retries, redirect service retries, at-least-once delivery. Every click gets a click_id at the redirect service; dedup by it.
• Streaming: dedup state keyed by click_id with TTL = max lateness; Flink two-phase commit sink or Kafka transactions for agg output.
• Billing: batch dedup over the whole day (ROW_NUMBER() OVER (PARTITION BY click_id ...)) is the source of truth, independent of streaming.

5.2 Hot campaigns (skew)

A Super Bowl campaign can be 5% of all traffic. Keyed aggregation by ad_id overloads one task.

• Two-stage aggregation: stage 1 keyed by (ad_id, salt = hash(click_id) % 16) → partial counts per minute; stage 2 keyed by ad_id merges 16 partials. Counts are additive, so this is exact.
• Or partition Kafka by click_id (uniform) and aggregate with partial pre-aggregation in each task before the shuffle.

5.3 Late data

• Streaming windows: event time with a 1-minute watermark for freshness; late clicks still land in bronze.
• Real-time OLAP table accepts upserts per (ad, minute) or late segments; the nightly batch job recomputes the full day (including events up to 24 h late), and the offline segment replaces the real-time segment for that day.
• Billing for day D closes at D+1 26:00 (after the 24 h lateness window) and is then immutable; anything later becomes an adjustment line on the next invoice, never a rewrite of a closed invoice.

5.4 Invalid traffic filtering

• Real-time: lightweight rules (blocklisted IPs, known bots by UA) to keep dashboards sane.
• Batch: heavier detection (click bursts per IP/device, CTR anomalies per publisher, ML model), possibly using the full day’s context. That’s another reason billing is batch.
• Output: is_valid + invalid_reason per click; advertisers see both valid and filtered counts (transparency).

5.5 Reconciliation

Daily job compares: streaming totals vs batch totals per campaign (expect small diffs from late data/IVT), batch clicks vs click-service logs, billing totals vs invoices. Alert on diffs above thresholds.

6. Trade-offs

7. Failure modes

8. What separates a senior answer

• Recognises the two different correctness requirements and justifies separate paths.
• click_id-based dedup and idempotent/transactional sinks, spelled out.
• Closed, immutable billing periods plus adjustments: real finance-system thinking.
• Handles hot campaigns with two-stage aggregation.
• Money as integers, reconciliation between paths.

9. Follow-up questions

Self-assessment rubric

• Identified differing correctness needs (dashboard vs billing)
• Estimated throughput and aggregate cardinality
• Dedup with a unique click id; idempotent/transactional writes
• Skew handling for hot campaigns
• Late data policy and closed billing periods
• Fraud / invalid traffic filtering placement
• Serving store choice for high-concurrency advertiser queries
• Reconciliation between paths