The Event Pile

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

L7

Problem

Hundreds of millions of user interaction events a day land in Kafka, every one must stay queryable for two years, and today nothing can query them. Design the architecture that serves the analytics team's funnel studies, customer support's live session lookups and the product teams' last-hour dashboards from that one stream.

600 million clicks a day. The budget is not infinite.

Business requirements

• The VP of Engineering wants the bill to come down without losing the two years of history we keep.
• Customer support pulls up a user's session while the customer is on the phone; today it takes minutes and customers hang up.
• Funnel analysts scan months by event type, support fetches one session, and dashboards aggregate the last hour; one storage shape can't serve all three.
• Data older than 90 days is queried rarely, but when audit or trend reports need it, the team has to be able to retrieve it.

Worked solution and explanation

The trap

This is a cost problem wearing a query problem's clothes. Three readers want three different physical layouts. Analysts scan months by event type. Support fetches one session by key. Dashboards roll up the last hour. Anyone can land Kafka into a warehouse; the trap is pretending one layout serves all three. Partition by date and support's single-session lookup scans the wrong axis while the customer hangs up. Put everything on hot, always-on storage and two years of history sits on the most expensive tier, which is exactly the bill the VP is complaining about.

Walk the requirements

Step 1: Make the lake the source of truth

Store the events as Parquet files in an Iceberg table on object storage, partitioned by date and event type. Two years of history live here at object-storage prices. The query engine bills per byte scanned, so a funnel over one event type skips every other partition. Write in batches every few minutes so the lake gets a few large files instead of millions of tiny ones.

Step 2: Key the session store by user, not by date

Support's lookup is a point read. A Flink job writes events into Redis keyed on user and session, with a TTL of a few days. The phone call becomes one key fetch that returns in milliseconds, not a scan over partitions.

Step 3: Pre-aggregate the last hour on the stream

Dashboards never need raw rows. A second Flink job rolls events into per-minute counts by event type and writes those small rows to a rollup store that only holds the trailing hour. A heavy analyst scan on the lake can then never slow a dashboard down.

Step 4: Keep old data online, not archived

Data older than 90 days stays in the same Iceberg table. A lifecycle rule can move older partitions to an infrequent-access storage class that is still readable in milliseconds. Audits run the same SQL over more bytes. Deep archive that needs a restore of several hours breaks the one-hour retrieval requirement.

The reference design

One warehouse for everything

Two years sit on always-on warehouse storage. Support's lookup scans date partitions. An analyst's scan of last quarter competes with the dashboards for the same compute. Teams patch it with materialized views, and the bill keeps rising.

Three stores from one stream

Most bytes sit in the lake at object-storage prices. Support reads one key, dashboards read precomputed rollups, and analysts pay per byte scanned. There are more moving parts, but a failure in one store stays in that store.

Common follow-up questions

• Support asks for a session from a year ago. What changes? (Tests whether you keep the hot store small and serve the long tail from the lake, with files sorted by user inside each date partition so the lookup stays a short scan.)
• A client sends every event twice. Which store cares? (Tests dedup on a stable event id before the fan-out. Dashboard counts double, and the lake writer needs an idempotent upsert.)