Ad Click Aggregator System Design

Advertising is the economic backbone of a large part of the internet, and at the center of that ecosystem is measurement: counting clicks, attributing conversions, and producing reports that advertisers trust enough to pay for. An ad click aggregator sits on the hot path of both product and revenue. It ingests raw click events at extreme scale, turns them into real-time dashboards for optimization, and produces audited, correct billing reports.

This question is a System Design favorite because it forces you to balance competing truths. Real-time reports need freshness and low latency, but billing needs correctness and traceability. The inputs are messy: SDK retries, offline devices, duplicate events, skewed traffic (hot campaigns), evolving schemas, and adversarial behavior. A strong answer makes these realities explicit and uses architecture—not wishful guarantees—to contain them.

This blog walks through a practical dual-pipeline design: a streaming path for near-real-time estimates and a batch path for the billing truth, with an event log as the source of truth. Along the way, you’ll cover “effective exactly-once,” late events and watermarking, skew mitigation, multi-dimensional analytics without exploding storage costs, and reconciliation/audit for billing correctness.

Interviewer signal: In ad measurement, “real-time” is for decisions and “batch” is for money. If you design only one pipeline, you’ll either be slow or wrong.

Clarify requirements and pick the right guarantees#

In interviews, you’re not graded on listing every metric; you’re graded on defining contracts. An ad click aggregator is fundamentally a counting and attribution system, so you must state what you can guarantee, what you approximate, and where the source of truth lives. Start by splitting outcomes into two categories: fast estimates (dashboard) and authoritative truth (billing). Then decide which dimensions matter (campaign, ad, publisher, geo, device, time bucket) and which queries must be fast.

You also want to clarify the scale and granularity. “Billions of events per day” translates into tens of thousands to millions of events per second at peak, with burstiness (live sports, breaking news) and heavy skew (a few campaigns dominate). That reality pushes you toward an append-only log for ingestion, streaming compute for incremental aggregates, and a batch warehouse for correctness and complex joins.

Finally, be explicit about semantics. You should not promise global exactly-once across the whole distributed pipeline. Instead, promise at-least-once ingestion plus effective exactly-once results achieved through idempotent sinks, dedupe, and checkpointing. That’s the language interviewers expect from someone who has operated streaming systems.

Common pitfall: Saying “exactly-once” as a blanket statement. Strong answers explain where you get idempotency and how you recover on replay.

Summary (after the explanation):

• Split the system into real-time estimates vs batch truth.

• Commit to at-least-once ingestion and effective exactly-once outputs.

• Define dedupe keys, late-event handling, and query expectations early.

• Call out skew and burstiness as first-class constraints.

High-level architecture: why dual pipelines exist#

A dual pipeline exists because a single pipeline cannot optimize for both freshness and correctness at internet scale. The streaming pipeline is optimized for speed: it provides approximate or near-real-time aggregates for dashboards and alerting. The batch pipeline is optimized for completeness: it reconciles late data, applies finalized fraud decisions, corrects drift, and produces audited billing outputs.

The backbone of both paths is the raw event log (Kafka or equivalent). In interviews, treat the log as the “system of record” for events before long-term archival. It gives you buffering (backpressure absorption), replayability (bug fixes and backfills), and a clean contract between ingestion and compute. From that log, two consumers diverge: a stream processor (Flink/Spark/Beam) and a batch pipeline (Spark/Trino/warehouse jobs) reading from archived raw events in object storage.

A practical storage layout uses a hot store for fast recent queries (for example, ClickHouse/Druid/Pinot for near-real-time aggregates) and a warehouse/lakehouse for authoritative queries and billing (for example, BigQuery/Snowflake/Iceberg + Trino). Query routing chooses the correct store depending on time range and accuracy needs.

Interviewer signal: When you say “raw log is the source of truth,” you unlock replay, backfills, and audits. That’s the difference between a demo and revenue infrastructure.

Summary (after the explanation):

• Use streaming for freshness and batch for correctness.

• Make the raw log the shared source of truth and replay mechanism.

• Route queries to hot OLAP for recent data and warehouse for billing ranges.

Ingestion edge and event integrity#

Ingestion is where measurement systems quietly succeed or fail. The inputs are adversarial and unreliable: SDKs retry, devices go offline, clocks are wrong, and some publishers may tamper with payloads to inflate counts. Your ingestion edge should therefore be optimized for fast acceptance while enforcing minimal integrity checks that prevent garbage from poisoning downstream systems.

A good design uses globally distributed ingestion endpoints (CDN/edge POPs or regional API gateways) that accept events via HTTP. They do not do heavy aggregation; they validate schemas, authenticate sources, apply rate limits, and attach server metadata (ingest_time, region, sampler flags). They then batch and compress events into the log. Batching improves throughput and lowers cost; compression reduces bandwidth and Kafka storage.

Schema evolution matters because mobile SDK versions roll out slowly. You want a versioned envelope (schema_version, event_type, required fields) and forward-compatible parsing. In practice, you validate required fields, drop or quarantine malformed payloads, and ship unknown optional fields into a flexible map for later use. This prevents hard failures when the schema changes.

Event integrity is not full fraud prevention, but you should include basics: signed click URLs or tokens, validation that click_id corresponds to an issued impression, and lightweight anti-tamper checks (HMAC signatures for partner integrations, replay protection with nonce windows). The goal is not perfect security at the edge; it’s preventing easy spoofing and making fraud harder.

What strong answers sound like: “The edge does minimal, fast validation and integrity checks, then writes to the log. Heavy logic belongs downstream where we can replay and correct.”

Summary (after the explanation):

• Keep ingestion fast: validate minimally, batch, compress, enqueue.

• Design for schema evolution with versioned envelopes and tolerant parsing.

• Add basic integrity: signed tokens, replay windows, issuer validation.

• Use backpressure and sampling to protect Kafka and downstream compute.

Streaming aggregation: state, windows, late events, and skew#

The streaming pipeline produces near-real-time aggregates: clicks per campaign per minute, spend pacing, geo/device breakdowns, and alerting metrics. This requires stateful processing with windowing. Most systems use event-time windows (based on click_time) rather than processing time, because you want metrics aligned to when clicks occurred, not when they arrived.

Late events are unavoidable: mobile devices buffer events, networks delay packets, and edge retries can arrive minutes late. You handle this with watermarking: define a lateness tolerance (for example, 5–30 minutes depending on product). Within that watermark, late events update the windowed aggregates. After the watermark passes, you either drop late updates from real-time and rely on batch correction, or you route them into a “late update” side output that triggers small backfills in the hot store.

Skew and hot keys matter because some campaigns dominate traffic. If you partition strictly by campaign_id, one hot campaign can overload a Kafka partition and a stream task. You mitigate skew with key-splitting (salting) for hot keys, hierarchical aggregation (local partials then merge), or dynamic repartitioning. The trick is keeping merges deterministic and idempotent.

Common pitfall: Ignoring skew. Interviewers will ask: “What happens when one campaign gets 30% of traffic?” You need a mitigation beyond “add more machines.”

Summary (after the explanation):

• Use event-time windows with watermarks to manage late arrivals.

• Bound state growth by limiting allowed lateness and relying on batch for truth.

• Mitigate hot keys with salting and hierarchical aggregation.

Effective exactly-once: correctness without magical guarantees#

For billing and trusted metrics, you need correctness that behaves like exactly-once even when the underlying delivery is at-least-once. This is where you explain “effective exactly-once”: the stream processor may reprocess events after failures, but the sinks and dedupe logic ensure final aggregates are not double-counted.

The practical recipe combines three ideas. First, every event has a stable event_id (or a composite key derived from click_id + source + timestamp) that supports dedupe. Second, the stream processor uses checkpointing so it can restart from a consistent point in the log. Third, the sink is idempotent: writes are either transactional (two-phase commit integration) or are upserts keyed by (bucket, dimensions) with deterministic increments applied exactly once per event_id.

In practice, true exactly-once from source to sink depends on the stack, but interviewers don’t want vendor-specific promises. They want to hear that duplicates are expected and contained. You can also mention that some aggregates are naturally idempotent (set-like metrics) while counters require careful dedupe or transactional writes.

Interviewer signal: Saying “we accept at-least-once and make the sink idempotent” is more credible than claiming global exactly-once. It shows you’ve debugged replays.

Summary (after the explanation):

• Expect duplicates; contain them with event_id dedupe and idempotent sinks.

• Use checkpointing to restart deterministically after failures.

• Let batch recompute overwrite authoritative partitions for billing truth.

Multi-dimensional analytics without storage explosion#

Advertisers want slices: campaign by geo by device by publisher by hour. The naive approach—precomputing every combination—explodes storage and compute. Your job is to support flexible analytics while keeping the system economical. This is where rollups, query routing, and approximate algorithms become essential interview material.

A good approach starts with defining a small set of “primary cubes” that cover the majority of queries (for example, by campaign/ad/time, by campaign/geo/time, by campaign/device/time). You materialize these in the hot OLAP store for recent data and in the warehouse for history. For rarer combinations, you route queries to the warehouse where scans are cheaper and latency tolerance is higher.

Distinct counts (unique users) are another trap. Exact distinct across high-cardinality dimensions is expensive in streaming. In interviews, mention approximate distinct using HyperLogLog (HLL) sketches. You can maintain HLL per bucket and merge sketches at query time, which is compact and fast. For billing, you may still compute exact distinct in batch for a limited set of official metrics, while using HLL for dashboards.

Common pitfall: Promising “arbitrary slicing in real time” without cost controls. A strong answer says which slices are fast and which route to batch/warehouse.

Summary (after the explanation):

• Materialize a small set of high-value rollups; don’t precompute every cube.

• Route long-range or rare-dimension queries to the warehouse.

• Use HLL (or similar) for scalable approximate distinct; reserve exact for billing-critical metrics.

Storage layout and query routing#

Storage choices should reflect two facts: recent dashboards require low-latency queries, and historical billing analytics require cheap scans and correctness. That often yields a hot OLAP store for recent rollups (ClickHouse/Druid/Pinot) and a lake/warehouse for authoritative partitions (Iceberg/Delta/Snowflake/BigQuery). The raw log is archived to object storage in partitioned files for replay and audit.

Query routing is the interview move that ties this together. A reporting API inspects the query (time range, dimensions, accuracy mode) and decides which store to hit. For example, “last 30 minutes by campaign” goes to hot rollups, while “last quarter by campaign + geo + device” goes to the warehouse. If users request “billing mode,” you route to batch truth even if it’s slower.

You should also mention how you handle corrections. The batch pipeline may rewrite partitions (for example, day-level partitions) and the reporting API should either read versioned partitions or ensure a consistent view via snapshotting.

Interviewer signal: “Billing mode vs dashboard mode” is a crisp product-level contract. It prevents arguments about why numbers don’t match minute-to-minute.

Summary (after the explanation):

• Use hot OLAP for recent rollups and warehouse/lake for long-range truth.

• Route queries based on time range, dimensions, and accuracy requirements.

• Support corrected partitions and consistent reads for billing.

Walkthrough 1: click → real-time dashboard#

A user clicks an ad in a mobile app. The SDK emits a click event with a stable event_id, click_time, campaign_id, ad_id, device info, geo, and an integrity token. If the network is flaky, the SDK retries. The edge ingestion service accepts the event, applies minimal validation, stamps ingest_time, and writes it to Kafka in a compressed batch. If the same event arrives again due to retry, it still lands in Kafka because ingestion is at-least-once by design.

The stream processor consumes the Kafka topic. It verifies basic integrity signals, performs dedupe using event_id within a bounded window, and assigns the event to an event-time window (for example, 1-minute bucket) using watermarking. It updates stateful aggregates keyed by (bucket, campaign_id, ad_id, geo, device), and writes incremental results to the hot OLAP store. Within seconds to a few minutes, the advertiser dashboard queries the hot store and sees updated click counts and pacing metrics.

If a late event arrives within the watermark tolerance, the stream updates the earlier bucket and the dashboard corrects itself. If it arrives after the watermark, the real-time view may not change, but the batch truth will.

What great answers sound like: “Real-time numbers are fast estimates from the streaming path. Late events can correct within a bounded window; beyond that, batch truth fixes it.”

Summary (after the explanation):

• Ingestion is at-least-once; duplicates are expected.

• Streaming dedup + windowing produces fresh rollups in the hot store.

• Late events correct within watermark; batch corrects beyond it.

Walkthrough 2: click → billing report#

That same click event is also archived from Kafka to object storage (for example, hourly partitions by ingest_time and/or event_time). Overnight (or hourly for faster billing cycles), the batch pipeline reads raw partitions, applies the finalized dedupe rules, joins against campaign configuration, applies fraud decisions (which may lag real time), and produces authoritative aggregates partitioned by day and campaign.

These authoritative partitions overwrite prior results for the day, which is how the system incorporates late events, corrected attribution rules, and fraud reclassification. Billing reports read from these truth tables, not the streaming rollups. If an advertiser disputes a charge, the audit path traces from the billing line item back to the underlying aggregates and, if needed, to sampled raw events by event_id.

This is why both pipelines exist: streaming drives immediate optimization decisions, batch drives money and disputes. Your design should make that separation explicit and visible in the product.

Interviewer signal: When you describe partition overwrite + audit traceability, you’re showing revenue-grade thinking, not just data plumbing.

Summary (after the explanation):

• Batch reads raw archives, applies final dedupe and fraud decisions.

• Outputs are authoritative partitions overwritten on recompute.

• Billing queries route only to batch truth, enabling disputes and audits.

Degradation and cost controls#

At this scale, you can’t treat cost and reliability as afterthoughts. You need explicit controls for overload (burst traffic, Kafka lag, hot campaigns) and explicit controls for spend (storage growth, compute time, cardinality explosion). Interviewers often ask what you do when downstream systems are slow or when a single customer drives extreme costs.

On the overload side, Kafka lag and processor backpressure are your early warning signals. The system should be able to shed non-critical workload: sample some dimensions for real-time, reduce rollup granularity temporarily, or pause expensive unique computations. For ingestion, you may apply adaptive rate limits per source and reject clearly abusive traffic. The key is preserving the raw log first; you can always recompute later if the raw events are safe.

On the cost side, tiered retention is your friend. Keep raw events in Kafka for short retention (hours to days), archive to object storage for longer retention, and keep hot rollups for only the window needed for dashboards. Also track “cost per billion events” as a real metric, and design your rollups to match common queries rather than theoretical flexibility.

Common pitfall: Dropping raw events during overload. Strong designs sacrifice real-time fidelity first and preserve the source-of-truth log.

Summary (after the explanation):

• Use lag/backpressure to trigger graceful degradation in streaming.

• Preserve raw events first; recompute later if needed.

• Control cost with tiered retention, compression, and disciplined rollups.

Reconciliation and audit for billing correctness#

Billing is where measurement systems get tested in the real world. Even with careful engineering, numbers drift: streaming and batch differ, dedupe rules evolve, fraud models reclassify events, and late arrivals shift totals. Your system needs a reconciliation strategy that detects drift, explains it, and corrects it with traceability.

The core principle is that raw logs are the source of truth. Everything else is a derived view. Reconciliation compares derived outputs across paths (streaming rollups vs batch truth) and flags discrepancies beyond tolerance. Some differences are expected (watermark cutoffs, late events), so you need explicit thresholds and categories for drift. When drift is unacceptable, you trigger backfills or recompute partitions.

Auditability requires lineage. A billing line item should be traceable to a set of aggregate partitions, and those partitions trace back to raw event ranges. You don’t need to store every raw event_id in the billing table, but you should be able to sample and prove correctness. For high-stakes disputes, you can maintain a “trace index” for a limited time window mapping event_id to storage location (object path + offset), which accelerates investigations.

Interviewer signal: “Raw log as source of truth + reconciliation + lineage” is the trifecta for billing correctness. It shows you understand disputes, not just pipelines.

Summary (after the explanation):

• Treat raw logs as the source of truth; everything else is derived.

• Monitor drift between streaming estimates and batch truth.

• Use partition overwrite backfills and lineage metadata for traceability.

Observability: the metrics that keep you honest#

The right metrics map directly to the failure modes of the system: ingestion overload, streaming lag, late arrivals, dedupe effectiveness, and cost blowups. In interviews, naming concrete metrics signals you’ve operated these systems and know what breaks first.

You want metrics at each stage: ingestion QPS and error rates, Kafka lag per topic/partition, stream processor checkpoint health, end-to-end freshness for dashboards, late-arrival rate by source, dedupe drop rate, and cost per billion events. You also want correctness indicators: reconciliation drift, backfill frequency, and billing job completion SLAs.

Common pitfall: Only tracking throughput. High throughput with growing lag is failure in slow motion. Freshness and lag are the real “live-ness” metrics.

Summary (after the explanation):

• Measure ingestion QPS, Kafka lag, and dashboard freshness.

• Track dedupe and late-arrival rates to understand data quality.

• Monitor reconciliation drift and cost per billion events to protect billing and budgets.

What a strong interview answer sounds like#

A strong answer is structured and honest about contracts. You state dual pipelines, define effective exactly-once, explain late events and skew, and connect all of it to billing auditability and cost controls. You don’t drown the interviewer in tool names; you use tools to support invariants.

Sample 30–60 second outline: “I’ll build a dual-pipeline click aggregator: a streaming path for near-real-time estimates and a batch path for billing truth. Clicks arrive at an edge ingestion layer that does minimal validation, schema-versioned parsing, basic integrity checks, and writes batched/compressed events into Kafka as the raw source of truth. A stream processor consumes Kafka with event-time windows and watermarks, dedupes by stable event_id, mitigates hot keys via salting/partial aggregates, and writes incremental rollups to a hot OLAP store for dashboards. In parallel, raw events are archived to object storage; batch jobs recompute authoritative partitions with full dedupe, late-event inclusion, and finalized fraud decisions, overwriting daily partitions for billing. Queries route to hot estimates for recent windows and to batch truth for billing. I monitor ingestion QPS, Kafka lag, freshness, dedupe and late-arrival rates, and cost per billion events, and I reconcile streaming vs batch outputs with drift detection and lineage for audits.”

Checklist (max 6 bullets):

• Dual pipelines: real-time estimates vs batch truth for billing

• Raw log as source of truth with replay/backfills

• Effective exactly-once via dedupe + checkpointing + idempotent sinks

• Watermarks and late-event handling, plus batch corrections

• Skew/hot key mitigation (salting, hierarchical aggregation)

• Reconciliation, lineage, and cost controls with concrete metrics

Interviewer signal: If you can explain correctness and billing auditability without claiming magical exactly-once, you’re answering at Staff level.

Happy learning!