Design a clickstream ingestion and aggregation pipeline for billions of events per day

##### Question You are designing the data platform for a large consumer streaming/media company. Client applications (web, mobile, smart TVs, set-top boxes) emit **clickstream / telemetry events** — page views, taps and navigation clicks, play/pause/seek actions, ad impressions, and device heartbeats. The platform produces on the order of **one billion events per day**, and the business wants two things out of the same pipeline: **near-real-time dashboards** (hourly/daily active users, plays per title, ad impressions per campaign, funnel conversion, feature adoption) that are fresh within minutes, and **accurate, finalized daily aggregates** that finance, analytics, and reporting can trust. Design an end-to-end pipeline that ingests, processes, and serves these events **without falling behind (lagging)** as traffic spikes — the processing layer must keep up with the incoming rate so dashboards reflect reality within minutes, not hours. Cover the path from the client SDK, through ingestion and stream/batch processing, to the storage and serving layer where aggregates are queried. Your design should specifically address: 1. **Architecture and sizing** — the stages from client SDK through edge collector, durable log, processing, storage, and serving, with back-of-envelope numbers (events/sec at average and peak, event size, daily bytes, log partition count, processor parallelism) that justify each choice. 2. **Lag and load** — how you detect that the pipeline is falling behind, and how you keep it caught up as traffic spikes, without silently dropping events. 3. **Late-arriving data** — a device goes offline and syncs telemetry hours (or days) later, so events arrive long after the time they actually occurred. How do daily aggregates stay accurate, and what happens to events that arrive *after* a daily aggregate has been finalized? 4. **Duplicates / exactly-once** — delivery from clients and collectors is at-least-once, so network retries mean the same event can appear multiple times. How do you guarantee each event is counted exactly once in your metrics? ```hint Decompose the path Split the problem into clear stages — **collection** (client SDK + edge collector), **buffering** (a durable, partitioned log), **processing** (stream and/or batch), and **serving** (warehouse + a fast query layer). Reason about each stage's throughput and back-pressure independently. ``` ```hint Back-of-envelope first 1 billion events/day is roughly **11.6k events/sec average**, but real traffic is spiky — size for a **peak of 3-10x average** (roughly 35-115k events/sec). Estimate event size (~0.5-1 KB) to get ingest bandwidth and daily storage, which justifies partitioning and tiered storage choices. ``` ```hint The hard parts are correctness, not just throughput The follow-ups (late data, duplicates, lag, hot keys) are where strong answers separate. Think **event-time vs processing-time**, **watermarks + allowed lateness**, **idempotent/exactly-once writes**, and **autoscaling driven by consumer lag** — not just "add more machines." ``` ### Constraints & Assumptions - ~1 billion events/day average; traffic is diurnal and spiky (launches, live events), so design for a **peak of roughly 3-10x average** — sustained tens of thousands of events/sec with bursts toward ~100k events/sec. - Average event size ~0.5-1 KB (user/device id, event type, event timestamp, session id, properties) as JSON/Avro; ~0.5-1 TB/day of raw data. - Events carry an **event-time** (when it happened on the device) that can differ substantially from **ingest-time** (when the server received it). - Delivery from clients and collectors is **at-least-once**; you cannot assume the network delivers each event exactly once. - Real-time dashboards need a few minutes of end-to-end lag; hourly rollups within minutes; the finalized daily aggregate within a small number of hours after midnight. - Aggregates must be **accurate and reconcilable**: late-arriving and duplicate events must not corrupt counts, and a finalized daily number must be trustworthy for finance. - The system should degrade gracefully under spikes — buffer rather than drop events, and catch back up. - Assume a cloud environment with managed object storage, a managed log/queue, a stream processor, and a columnar warehouse/OLAP store available. ### Clarifying Questions to Ask - What is the freshness SLA — do consumers need near-real-time (single-digit-minute) rollups, or are hourly/daily batch aggregates sufficient? - How "final" must the daily numbers be — can a day's total be revised after midnight, or must it be frozen once published? - What is the accuracy requirement — are approximate counts acceptable (e.g., HyperLogLog for distinct users), or must daily aggregates be exact and reconcilable? - How late can late-arriving data realistically be — minutes, hours, or several days — and is there a cutoff after which late events are dropped or routed to a correction job? - What is the deduplication key? Does every event carry a stable client-generated unique id, or must we synthesize one from (user, event type, event time, salient properties)? - Which metrics and dimensions matter most (unique counts like DAU vs additive counts like plays or ad impressions vs funnels), and what query patterns and cardinality do downstream dashboards have? - Is the event schema stable, or do we need schema evolution and a registry? - What are the retention, cost, and compliance requirements (raw vs aggregated retention, PII handling, deletion requests) the storage tier must honor? - Is the team optimizing for lowest latency, lowest cost, or operational simplicity? ### What a Strong Answer Covers - A clear staged architecture — **client SDK / batching → edge collector (HTTP ingest) → durable partitioned log (Kafka/Kinesis/PubSub) → stream processor (Flink/Spark Structured Streaming) and/or batch → object storage + warehouse/OLAP serving layer** — with the reasoning behind each choice. - **Back-of-envelope sizing**: events/sec at average and peak, event size, daily and yearly bytes, partition count for the log, and parallelism for the processor. - A justified **Lambda or Kappa** choice: a low-latency streaming path for fresh dashboards plus a reconciling batch path (or a single replayable stream) for correctness, with an explicit statement of how the two are merged and how duplicated logic is avoided. - **Back-pressure and durability**: the log absorbs spikes; consumers can lag and replay; nothing is dropped silently. Partition keys chosen to spread load and preserve per-key ordering. - **Event-time processing**: windowing on event time (not arrival time), **watermarks**, and **allowed lateness** so the system waits a bounded time for stragglers before finalizing a window — and a correct account of what a watermark actually is. - **Late data handling**: a bounded lateness window updates open aggregates in place; events arriving after finalization route to a **late side-output / correction path** that re-emits a delta or triggers a partition reprocess, so dashboards are reconciled rather than silently wrong. A **provisional → finalized** lifecycle so consumers know when a number can still move. - **Deduplication / exactly-once**: client-generated **event IDs** (idempotency keys), bounded dedup state within a window, and **idempotent / transactional sinks** (Flink two-phase-commit, or upsert/MERGE keyed by window) so retries and replays don't double-count — plus an honest acknowledgement of the cost of true exactly-once versus effectively-once. - **Lag mitigation and autoscaling**: **consumer lag** and **watermark skew** as the primary signals; autoscale processors and collectors on lag; pre-partition for peak; rate-limit or shed at the edge only as a last resort. - **Hot-key / skew handling**: per-partition lag to detect a skewed key, then key-salting or two-stage aggregation instead of re-partitioning the whole topic. - **Storage strategy**: raw immutable events in object storage (Parquet, partitioned by event-time date/hour) as the replayable source of truth, feeding both real-time rollups and authoritative batch recomputation; aggregates upserted into a columnar warehouse or OLAP store keyed by window. - **High-cardinality uniques**: exact `COUNT(DISTINCT ...)` in batch versus mergeable HyperLogLog sketches for the streaming path, and the accuracy trade-off that implies. - **Observability**: ingest rate, end-to-end latency, consumer lag, watermark skew, late/dead-letter counts, dedup hit rate, provisional-vs-finalized deltas; a **dead-letter queue** for malformed events; alerting on SLA breach. - **Trade-offs**: streaming (low latency, harder exactness) vs batch (simpler, higher latency); exact distinct counts vs approximations; cost vs freshness. ### Follow-up Questions - **Late-arriving data and correction propagation**: Your streaming path produced a DAU number at 11:59pm; at 3am the next day a batch of yesterday's device events arrives. Walk through watermarks, allowed lateness, exactly how the day's DAU gets corrected, and how downstream consumers learn the number changed. - **Deduplication / exactly-once**: Compare exactly-once in **Flink** (checkpoint barriers + two-phase-commit sinks) with **Spark Structured Streaming** (checkpointed offsets + idempotent `foreachBatch` upserts). When would you pick each, where can each still produce duplicates, and where does dedup state live and how is it bounded? - **Lag under spikes**: Traffic surges 10x during a live event. How do you detect that the pipeline is falling behind, and what is your autoscaling and back-pressure strategy so it catches up without dropping data? - **Hot-key skew**: A single very hot key (one viral title, or a bot) is skewing one partition and causing lag on just that partition. How do you detect and fix this without re-partitioning the whole topic? - **High-cardinality unique counts**: The business now wants accurate **unique** counts (DAU/MAU) across very high cardinality at low cost and low latency. How does that change your aggregation strategy (exact sets vs HyperLogLog), and what accuracy trade-off do you accept? - **Reprocessing**: A bug in the aggregation logic shipped yesterday. How do you recompute corrected aggregates from the raw event store without disrupting live ingestion?