Analytics Pipeline

What is a user analytics pipeline?

A user analytics pipeline collects every click, page view, and custom event from your product, stores billions of those events durably, and lets you query them seconds later on a dashboard. The deceptively hard part is not collecting events; it is reconciling two contradictory requirements that live in the same system.

Real-time dashboards demand low-latency streaming. Historical funnels and cohort analysis demand high-throughput scans over months of data. Building both without creating two aggregation implementations that drift apart is the core engineering challenge here. This question tests pipeline architecture, columnar storage selection, the Lambda versus Kappa architecture debate, and write-heavy system design.

TL;DR

Accept client batches into a stateless ingestion tier, acknowledge them after durable Kafka publication, and write the stream asynchronously to a columnar event store. Keep raw events as the replayable source, use ClickHouse materialized views for standard historical aggregates, and add a Flink or equivalent stream processor only for panels that need sub-minute freshness.

Route dashboard queries by freshness and time range, submit expensive funnel computations asynchronously, and make client retries safe with a stable event_id. Keep operational definitions in a transactional store, while treating raw event data and derived rollups as separate storage concerns.

Scope and assumptions

The following are illustrative planning assumptions for the design; they are not vendor guarantees:

• Approximately 1 billion events per day, about 11,500 events/second sustained and 35,000 events/second at the stated peak, with an average raw event size near 1 KB.
• Ninety days of hot data, three years of cold archive, dashboard aggregates under five seconds at p95, funnels under 30 seconds when submitted asynchronously, and fresh panels no older than 60 seconds.
• Web, mobile, and server SDKs can batch events and persist a retry queue on the client where appropriate. The server assigns a receive timestamp in addition to the client event timestamp.
• The platform provides collection, durable storage, aggregate queries, and ordered funnel analysis. Anomaly detection, deletion workflows, experiment assignment, real-time personalization, and per-customer encryption isolation are outside the primary design.

Functional Requirements

Core Requirements

1. Collect page views, clicks, and custom events from web and mobile clients.
2. Store events durably and allow querying by time range, user segment, and event type.
3. Serve near-real-time and historical dashboards with aggregated metrics.
4. Support funnel analysis: track the sequence of steps users take toward a conversion goal.

Below the Line (out of scope)

• ML-based anomaly detection
• GDPR-compliant data deletion workflows
• A/B test assignment and exposure logging
• Real-time personalization scoring

The hardest part in scope: Balancing query freshness against query cost. Scanning raw events for every dashboard request at 1B events/day is prohibitively slow. Pre-aggregating too aggressively locks you into fixed query shapes. The architecture must support both approximate fresh aggregates and exact historical scans without duplicating the full pipeline.

ML-based anomaly detection is below the line because it is a consumer of the pipeline, not the pipeline itself. To add it, attach a Flink job to the raw Kafka topic that scores each event against a trained model and emits anomaly signals to a separate alert topic. The ingestion and storage layers are unchanged.

GDPR deletion is below the line because it requires finding and purging a specific user's events across all partitions of a columnar store (ClickHouse, BigQuery) where rows are immutable by design. To add it, maintain a deletion log in a mutable store (PostgreSQL). The query layer joins against the deletion log at read time to filter suppressed user IDs. Periodic compaction jobs physically remove the rows during off-peak hours.

A/B test assignment is below the line because it requires a consistent assignment service (low-latency, avoid re-assigning users mid-experiment) and the logging of assignment events, which is a write path concern separate from the analytics query path.

Non-Functional Requirements

Core Requirements

• Durability: 99.9% of events must be delivered and stored. Losing a handful of debug-level events during a broker restart is tolerable; losing conversion events is not.
• Write throughput: 1 billion events per day, roughly 11,500 events/second sustained. Peak traffic (product launches, sales events) can reach 3x that: approximately 35,000 events/second.
• Query latency: Dashboard aggregate queries must return in under 5 seconds at p95. Funnel computation for 90-day cohorts must complete in under 30 seconds.
• Freshness: Real-time dashboard panels must reflect events no older than 60 seconds. Historical reports are batch-computed and may be up to 1 hour stale.
• Retention: 90 days of hot storage (fully indexed, fast query). 3 years of cold archival (object storage, queryable via batch scan).
• Scale: At 1 KB average event size, 1B events/day is roughly 1 TB of raw ingestion per day. After 90 days that is 90 TB of hot storage.

Below the Line

• Sub-second dashboard latency (requires fully pre-materialized views for every possible query shape)
• Per-customer data isolation with separate encryption keys (multi-tenant SaaS concern)
• Central schema-registry enforcement and breaking-change detection (basic request validation remains in scope)

Read/write ratio: Writes completely dominate this system. 1B events per day hit the ingestion layer continuously. Dashboard queries are sporadic: a few thousand users check their dashboards per hour, each query scanning billions of rows. The ratio is roughly 500:1 writes to interactive reads under normal load. Every architectural decision flows from this asymmetry: the write path must be cheap and embarrassingly parallel; the read path must be powered by pre-computation rather than raw scans.

Treat the 5-second query SLA as the forcing function for storage and query routing. Any path that cannot meet the target at the stated 90-day data volume needs pre-aggregation, a narrower query shape, more capacity, or a deliberately asynchronous response.

30-second answer / outline

1. Batch events in web, mobile, and server SDKs; validate them at a stateless ingestion service.
2. Publish batches to Kafka partitioned for the required ordering and workload, then acknowledge receipt. Consumers write raw events to ClickHouse in bulk.
3. Use materialized views for hourly/daily aggregates and a short-window streaming summary for panels with the 60-second freshness target.
4. Route queries by freshness and time range; run long funnel queries asynchronously with a funnel_id.
5. Carry a client-generated event_id, deduplicate in the consumer, retain raw events for replay, and monitor lag and freshness.

5-minute explanation

Start with the write/read asymmetry. Event collection is continuous and bursty, while dashboard queries are comparatively sparse but can scan a large time range. A durable log between ingestion and storage lets the client receive a bounded acknowledgement without coupling user traffic to ClickHouse write latency.

The raw event table is the durable analytical base. ClickHouse materialized views calculate standard counts and distinct-user states as inserts arrive, so most dashboard queries read a small rollup. A Flink stream is reserved for the newest minute when the panel cannot wait for the normal materialization path. The query service returns freshness_seconds so the UI can distinguish a current summary from an older exact result.

Funnels are a different shape: they correlate ordered events per user/session over a window. Submit that work asynchronously and use the columnar store's ordered-event functions or a distributed job. Do not hold the ingestion path open while the funnel is computed.

Finally, define correctness explicitly: event time and receive time are both retained, retries are deduplicated by stable IDs, late data is handled by watermarks or re-aggregation, and raw events can be replayed to repair a rollup. The deep dives explain the storage, freshness, and failure trade-offs.

45-minute interview approach

This is a time-boxed plan for answering the design question, not a claim that the article should be read in 45 minutes.

• 0–5 minutes — Clarify the contract: Confirm event types, identity/session semantics, dimensions, query shapes, freshness, retention, deletion requirements, and whether approximate distinct counts are acceptable.
• 5–10 minutes — Establish scale: Use the illustrative event rate, average size, retention, dashboard latency, and funnel targets. Separate continuous writes from interactive reads.
• 10–15 minutes — Define APIs and schema: Walk through batch ingestion, query, funnel submission/results, event IDs, client timestamps, receive timestamps, and validation behavior.
• 15–22 minutes — Draw ingestion: Show SDK buffers, stateless ingestion, Kafka, consumer batching, raw ClickHouse storage, acknowledgement semantics, and replay.
• 22–30 minutes — Draw query/freshness paths: Show the query cache, realtime summary, materialized hourly/daily views, raw partitions, and routing decisions. Explain freshness_seconds.
• 30–35 minutes — Deep dive on funnels and data model: Cover event-time ordering, session windows, identity resolution, partition/sort keys, and asynchronous execution.
• 35–41 minutes — Reliability, scale, security, and operations: Cover consumer lag, late events, deduplication, ClickHouse failure, cold export, access control, retention, and monitoring.
• 41–45 minutes — Trade-offs and close: Compare Lambda, Kappa, and materialized-view approaches; discuss ClickHouse versus object-store engines; recap the write-ahead buffer and invite follow-ups.

Core Entities

• Event: A single user action with event type, anonymous or identified user ID, session ID, timestamp, device and geo metadata, and a free-form properties map.
• User: An identity record linking anonymous IDs (browser fingerprint, cookie) to an identified user ID after login. Many anonymous IDs may map to one user.
• Session: A bounded window of contiguous user activity (max 30 minutes of inactivity). Groups events for funnel computation.
• Funnel: A named, ordered sequence of event types representing a conversion path (e.g., View Product -> Add to Cart -> Purchase).
• AggregateResult: A pre-computed rollup: metric name, dimension values (event type, country, device), time bucket, and count or sum. The unit that powers dashboard panels.
• Dashboard: A saved collection of query definitions rendered as charts. Backed by the query service.

Schema details and partitioning strategies are deferred to the deep dives. These six entities are sufficient to drive the API and High-Level Design.

API Design

Start with one endpoint per functional requirement. Evolve where the naive shape breaks at scale.

FR 1 - Ingest events:

Naive shape:

POST /events
Body: { event_type, user_id, session_id, timestamp, properties }
Response: { event_id }

This breaks at 11,500 events/second: each client sending individual HTTP requests generates enormous per-request overhead. Clients on mobile networks with 100ms+ round-trip times can barely sustain 10 requests/second per connection. The evolved shape batches events:

POST /events/batch
Body: { events: [ { event_type, user_id, session_id, timestamp, properties }, ... ] }
Response: { accepted: 247, failed: 0, batch_id }

Batch size caps at 500 events or 512KB, whichever is smaller. The server acknowledges receipt of the batch and writes to Kafka asynchronously. The client retries the entire batch on failure, so each event must carry a client-generated event_id UUID for deduplication downstream.

FR 2 - Query aggregated metrics:

GET /query
Query params: metric, event_type, start_time, end_time, granularity (minute|hour|day), group_by
Response: {
  data: [ { timestamp, value, dimensions } ],
  next_cursor: "...",
  freshness_seconds: 42
}

The freshness_seconds field tells the dashboard UI whether it is rendering the streaming aggregate (slightly stale) or a fully materialized batch result. This lets the UI render a staleness badge without a second round-trip.

FR 3 - Funnel analysis:

POST /funnels
Body: { steps: ["view_product", "add_to_cart", "purchase"], window_hours: 24, start_date, end_date }
Response: { funnel_id }

GET /funnels/{funnel_id}/results
Response: {
  steps: [ { name, users_entered, users_completed, conversion_rate } ],
  computed_at: "2026-03-29T14:00:00Z"
}

Funnel computation is expensive (correlated scan across user sessions). It is submitted asynchronously and polled. The funnel_id pattern avoids a synchronous 30-second HTTP hold.

High-Level Design

Critical flows

Read the architecture as four connected flows: batch ingestion into a durable log; raw-event storage; freshness-aware dashboard querying; and asynchronous funnel computation. The raw stream is the common source that keeps the derived read paths repairable.

1. Ingesting events from clients

The write path must handle 11,500 events/second sustainably and absorb 3x traffic spikes without dropping events.

Naive approach: Client sends individual events synchronously to an ingestion server that writes directly to a PostgreSQL events table.

This fails immediately under real load. At 11,500 writes/second, a single PostgreSQL instance saturates. Network round-trips from mobile clients add latency that makes synchronous per-event writes impractical. A single spike trips a circuit breaker on the database and events are lost.