The Clicks We Throw Away

A hard Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

hard

Seniority

L4

Problem

Our apps emit a constant stream of user interaction events that today land in a server log file and get thrown away. Design a clickstream pipeline that collects every event once and then serves two very different readers: the product team analyzing history and on-call engineers chasing bug reports.

Every tap, swipe, and scroll. At scale.

Business requirements

• Every event from every app has to be captured once into one place before anyone reads it. Today they scatter into log files and vanish.
• The product team runs funnel and feature-adoption analysis over months of history. Give them a store built for wide historical scans.
• On-call engineers must pull a single device's last few hours of events within minutes of a bug report.

Worked solution and explanation

Two readers, one stream

This is two incompatible query patterns wearing one clickstream costume. Product analytics wants wide scans over months, grouped by day and event type. On-call wants a needle: every event for one device in the last few hours. The trap is landing everything in one date-partitioned warehouse and pointing both readers at it. The single-device lookup then scans the wrong axis, times out, and on-call goes back to grepping raw logs, which is the workflow this pipeline was meant to kill.

Product analytics

Wide scans over months, grouped by day, screen and event type. Wants aggregates, not rows. Store: a warehouse, columnar and partitioned by date. Freshness: hourly loads are fine.

On-call debug

All events for one device_id in the last few hours, in order. Wants rows, not aggregates. Store: a low-latency serving store keyed by device_id plus a time bucket. Freshness: queryable within minutes.

The two readers disagree on partition key, storage layout and freshness. That is why one store cannot serve both: with date partitioning, one device's four hours are smeared across every partition, so the query fans out over the whole table.

Step 1: Collect once at the edge

The collectors are the ingestion boundary. Deduplicate on the client-generated event_id and normalize iOS 'tapped', Android 'clicked' and web 'click' into one action name. Everything downstream trusts the bus to hold clean events, so skipping this means both readers inherit the mess.

Step 2: Put a durable log in the middle

The event bus is the single place every event lands before anyone reads it. Because it is a replayable log, you can rebuild either store from it, add a third reader later without touching collection, and absorb a traffic burst without dropping events. It turns 'two pipelines' into 'one pipeline, two sinks'.

Step 3: Key the debug store by device

The stream processor upserts each event into a store partitioned by device_id and bucketed by time, so a single-device pull touches one partition and returns fast. Keep retention short, a few days, since nobody debugs last quarter's crash from here. This is the near-real-time tier.

Step 4: Load the warehouse in hourly batches

The same processor output is loaded into date-partitioned, columnar warehouse tables for funnels and adoption. Hourly is the right cadence: historical analysis tolerates the lag, and batching keeps load cost low. This is the batch tier, and it is what gives the design two distinct freshness tiers.

Common follow-up questions

• iOS buffers events and flushes late, so pre-crash events arrive after the crash event. How do you let on-call see the two minutes before a crash? (Tests late data: event-time windows with a watermark and allowed lateness, or ordering by event time at read.)
• Product teams add event fields weekly and old app versions omit them. How does the pipeline not break? (Tests schema evolution: backward-compatible rules so missing fields are nullable, not fatal.)
• Client SDKs retry on network failure and about 2 percent of events arrive twice. How do you keep funnels from double-counting? (Tests idempotency: dedup on `event_id` at the edge and idempotent upserts into both stores.)
• A user is anonymous, then logs in mid-session. How do you attribute the earlier events? (Tests session stitching: a session gap rule and back-filling `user_id` onto earlier anonymous events.)