#6 Ad click event aggregation system
Below is a complete, time-boxed, interview-ready answer (1 hour) for designing an Ad Click Event Aggregation system — i.e., a system that ingests millions of ad click events from clients/publishers/SDKs, deduplicates & attributes them to campaigns, computes aggregates (clicks, unique users, CTR, conversions) in real time and batch, and serves dashboards / billing / reporting with strong correctness guarantees.
Use this script in an interview: speak the minutes noted, cover each section, and show diagrams / trade-offs as you go.
0 – 5 min — Problem recap, scope & assumptions (Set the stage)
Start by restating the goal and getting alignment.
Problem: Build a system that collects ad click events from mobile/web SDKs and ad servers, deduplicates and attributes them to campaigns/ads, computes aggregates (clicks, uniques, CTR, conversions) with low-latency dashboards and accurate billing reports.
Primary goals:
High ingestion throughput (millions of events/sec depending on scale).
Accurate deduplication and attribution (billing-grade).
Real-time aggregation for dashboards (seconds to 1 minute latency) + batch for daily reporting.
Fraud detection and tolerant to late/duplicate events.
Durable storage and retention (raw events for audit).
Assumptions (example numbers you can change):
100M MAU, 5M clicks/sec peak globally (choose smaller if interviewer gives constraints).
Average event size: 400 bytes (JSON-ish).
Retention: raw events 30 days hot, 1 year cold; aggregated metrics kept longer.
SLA: real-time dashboard freshness ≤ 30s; billing reports daily with correctness guarantees.
5 – 15 min — Functional & Non-Functional Requirements
Talk through FR and NFR clearly and concisely.
Functional Requirements (Must / Should / Nice)
Must
Ingest click events: accept events from publishers, SDKs, and ad servers. Support HTTP/gRPC/SDKs and message formats (JSON/Protobuf).
Deduplicate: ensure duplicate clicks (retries, network retries, client retries) are not double-counted.
Attribute: map click → campaign, creative, publisher, ad placement, and user/session where applicable.
Aggregate metrics in real time: clicks/sec, unique users, CTR, conversions per campaign/ad/geography, with time-windowed metrics (1m, 5m, hourly).
Store raw events: durable storage for auditing and reprocessing.
Provide APIs / Dashboards: real-time dashboards (near-real-time), and historical OLAP queries for reports.
Billing/export: produce daily billing reports with reconciliation data and export for finance.
Fraud detection: flag anomalous click patterns and optionally exclude from billing.
Should
Provide webhooks/stream for partners.
Support replay/reprocess from raw events for backfills or corrected attribution logic.
Nice-to-have
Real-time anomaly alerting and automatic exclusion rules.
Multi-touch attribution models.
Non-Functional Requirements
Performance: ingest up to X events/sec (configure per interview); dashboard latency P95 < 30s.
Scalability: linear scale horizontally (by partitioning).
Durability: zero data-loss guarantee for accepted events (ACK semantics), durable replication.
Accuracy: billing-grade correctness – tolerance for <0.1% error in billed counts (specify target).
Availability: 99.95% for ingestion and near real-time metrics; batch reports SLAs as agreed.
Consistency: eventual consistency for dashboard metrics; batch reports must be strongly consistent (after reconciliation).
Security & Privacy: TLS, auth, PII minimization, GDPR-compliant deletion.
Observability: metrics for ingestion rate, lag, dedupe rate, aggregation errors.
15 – 25 min — API & Event Schema (External contract)
Show sample schemas and key APIs.
Event schema (example)
APIs
POST /event/click — ingest single event (sync ack or async).
POST /events/batch — ingest batch of events (preferred for SDKs).
GET /metrics?campaign=...&start=...&end=...&granularity=1m — aggregated metrics API (real-time or cached).
GET /raw-event/{id} — for audit (ACL-protected).
POST /admin/reprocess — reprocess raw data for corrected logic (internal).
Ingress semantics: offer at-least-once delivery from client perspective: client retries until ack. System must dedupe.
25 – 40 min — High-level Architecture & Data Flow
Draw or describe the data flow from clients to dashboards and store.
Key components explained
API Gateway / Edge: TLS termination, auth, client rate-limits, request validation, lightweight signature verification. Reject malicious high-volume clients early.
Message Bus (Kafka): durable, partitioned stream; critical for buffer/decouple and replay. Partition by (campaign_id || publisher_id || hashing(event_id)) to enable locality.
Stream Processor: stateful processing per key; dedupe by event_id or combination (user_id, timestamp, campaign_id) within a reasonable time window. Use event-time processing with watermarking to handle late events.
Deduplication approach:
Option A: Use a stateful dedupe store in stream processor (keep event_ids in time-windowed TTL store e.g., RocksDB state in Flink). This handles at-least-once client semantics.
Option B: A down-the-line idempotent store keyed by event_id (e.g., insert-if-not-exists into DB using conditional writes).
Attribution:
Join click events with campaign metadata and optionally impression events to support view-through attribution.
Support multiple attribution models: last-click, last-touch, time-decay, multi-touch modeling (implemented in streaming or batch).
Aggregates & Serving:
Real-time materialized views: incremental aggregates written to a fast read store (ClickHouse / Druid / Cassandra / Redis). Choose ClickHouse or Druid for time-series OLAP with fast rollups.
Batch OLAP: write raw events to object store (S3), run nightly Spark jobs to compute authoritative aggregates / billing.
Fraud detection:
Real-time scoring (ML or heuristic) in stream processor to flag suspicious clicks; flagged events routed to separate pipeline for manual review and exclusion.
Reconciliation / Reprocessing:
Ability to replay raw data from Kafka or S3 to recompute aggregates if bug or logic change.
Exports: billing files generation, data warehouse, partner webhooks.
40 – 50 min — Core algorithms, correctness, and edge cases
Explain how dedupe, attribution, windowing, late events, and exactly-once semantics are handled.
Deduplication (key ideas)
Primary dedupe key: event_id if clients supply unique ids (strongest). If not, fallback to (publisher_id, user_id_hash, timestamp_bucket, campaign_id) approximate key.
Keep dedupe state for a TTL window (e.g., 7 days) using a bounded state store (RocksDB) with compaction.
For very high volumes where storing all ids not possible, use Bloom filters + tombstones to probabilistically filter duplicates (trade-off false positives). For billing-grade accuracy, prefer exact dedupe (disk-backed state).
Event-time processing & watermarks
Use event-time processing: assign timestamps from event, maintain watermark to handle late arrivals. E.g., allow lateness of 5–15 minutes for near-real-time aggregates; late events are either:
Upserted into aggregates with correction; or
Emitted to a “corrections” stream for reconciliation in batch.
Windowing & Aggregation
Maintain sliding windows (1m, 5m, 1h) and tumbling windows for daily totals.
Use incremental combiners (add/subtract) to compute per-window counts and uniques.
Unique users: for uniques you may use HyperLogLog for approximate counts in real-time; but produce exact uniques in daily batch for billing.
Exactly-once vs At-least-once
Stream processors (Flink + Kafka) can provide exactly-once semantics across sink if using two-phase commit / transactional writes and Kafka’s EOS. For OLAP sinks that don’t support transactions, use idempotent upserts with deterministic keys (e.g., (campaign_id, window_start)).
For billing-grade final numbers, rely on batch authoritative job for final reconciliation (nightly job reading raw events from S3) and mark dashboard metrics as “near-real-time (approx)” vs “official”.
Attribution Models
Implement last-click / last-touch in streaming: for each click, assign attribute to campaign_id based on priority rules. Multi-touch models are heavy—do in batch.
Maintain per-user last-touch state for short windows (session-level) in stream processor.
Fraud detection
Real-time heuristics: high click rates from single IP/user, impossible geolocation jumps, improbable user-agent patterns.
ML model scoring in streaming for probability of fraud; maintain thresholds and feedback loop.
50 – 55 min — Back-of-the-envelope capacity & sizing
Show sample math (use interviewer numbers if provided).
Example scale (pick and show math): assume 5M events/sec peak, avg 400 B/event.
Ingress bandwidth: 5M × 400 B = 2,000,000,000 B/s = ~2 GB/s ≈ 16 Gbps.
Kafka retention and storage:
If keeping 24 hours of raw events in Kafka: 2 GB/s × 3600 × 24 ≈ 172,800 GB ≈ 169 TB. (Consider tiering to S3 where Kafka retention is short).
S3 raw storage (30 days): 2 GB/s × 86,400 ≈ 172.8 TB/day → 30 days ≈ 5.18 PB raw (compress and possibly pre-aggregate).
Clearly, you’d compress (gz/snappy) and avoid keeping full raw for all events for all customers; archive selectively.
Processing capacity:
Stream processors scaled by partition count and state size. Partition by campaign/publisher to distribute load.
Serving store:
ClickHouse clusters sized for aggregates: depends on cardinality (campaigns × geos × creatives × hourly windows). Estimate bits per aggregate row and size.
Optimization strategies:
Pre-aggregate at collectors where possible (per-publisher batching).
Use compression and storage tiering (hot real-time vs cold archived).
Apply sampling for analytics (not billing!).
55 – 58 min — Reliability, security & operational concerns
Briefly cover ops & safeguards.
Reliability & DR
Multi-AZ Kafka clusters, cross-region replication for regional resiliency.
Exactly-once in streaming where possible; nightly batch reconciliation to detect drift and correct.
Backpressure: API gateway throttles per-publisher; collectors buffer to Kafka.
Security & Privacy
TLS, mTLS for internal comms, API keys for publishers.
Validate event signatures to avoid spoofing.
PII: hash or remove user identifiers; store IPs only if required and with TTL; support GDPR deletion (delete raw & derived data or mark excluded in billing).
Monitoring & Alerts
Ingest metrics: events/sec, partitions lag, processing latency, dedupe rate, correction rate, fraud events rate.
Alerts on Kafka lag, processor backpressure, significant mismatch between real-time and batch numbers.
58 – 60 min — Trade-offs, evolution & summary (Wrap-up)
Finish with trade-offs, evolution plan, and a one-paragraph summary.
Key trade-offs
Real-time accuracy vs cost: exact dedupe & exact uniques in real-time is expensive; hybrid approach — approximate real-time + authoritative nightly batch — balances cost and correctness.
Stateful dedupe memory vs disk: keeping full dedupe state (event_ids) consumes storage; trade-off with bloom filters (probabilistic).
Stream processing complexity vs simplicity: Flink (stateful, exactly-once) adds complexity but gives correctness; simpler micro-batch jobs are easier to operate.
Evolution path
MVP: API gateway → Kafka → Stateless consumers that produce per-campaign counts → ClickHouse for serving; raw events to S3.
Phase 2: Introduce stateful stream processing (Flink) for dedupe & attribution; ClickHouse materialized views for real-time dashboards.
Phase 3: Add fraud detection ML models in real-time; multi-touch attribution; cross-region replication.
Phase 4: Optimize storage (tiering, sampling), advanced attribution & predictive analytics.
One-sentence summary
Build a pipeline with durable ingestion (Kafka), stateful stream processing (dedupe, attribution, fraud scoring), materialized real-time aggregates (ClickHouse/Druid), and authoritative batch reprocessing over raw S3 data for billing-grade reconciliation — balancing low-latency visibility with correctness and cost-efficiency.
If you’d like, I can:
Turn this into a 5–10 slide interview deck (one per time-box section), or
Produce a detailed diagram (textual or pseudo-graph) for the architecture, or
Run a capacity calculator if you provide your expected events/sec, event size, and retention targets. Which would you prefer?
Last updated