Two Years of Clicks, Cheap

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

Our platform generates 600 million user interaction events per day through Kafka and we need a cost-efficient architecture to store and query this data for analytics with a two-year retention requirement. Query latency and storage cost are both first-class constraints. Design the end-to-end ingestion, storage, and query architecture.

Two years of clicks. Every query has to be affordable.

Business requirements

• The VP of Engineering wants the bill to come down without losing the two years of history analysts depend on.
• Analysts run trend reports going back two years; recent queries have to be fast and older queries can be slower as long as they still complete.
• Product dashboards show what users just did and need to feel current, not yesterday's.
• Some events arrive twice from client retries; for purchase events that directly distorts the revenue numbers finance reads.

Worked solution and explanation

What this really is

This is a storage-tiering problem dressed up as an ingestion question. Getting events out of Kafka is the easy part. The real skill is giving each byte the engine its access pattern deserves: two years of history on cheap object storage, the last few minutes on a small fast store, and queries billed by the bytes they scan. The trap is one big warehouse with a two-year retention setting. It works on day one. Then the bill climbs every month, a heavy trend query stalls the product dashboards, and a retried purchase lands twice in the revenue finance reads.

The shape that fits

Step 1: Put dedup upstream of both tiers

dedup stream keys its state on event_id, with a TTL that covers the client retry window. Place it before the fork and both tiers inherit clean data. Place it after the fork and you run two dedup implementations that will eventually disagree.

Step 2: Keep the hot tier small and fresh

session aggregates holds recent rollups in Redis, fed in real time. Dashboards never read the lake, so a two-year scan cannot slow the page that shows the last minute.

Step 3: Batch the lake writes and compact them

lake writer flushes every few minutes, not once per event. Iceberg compaction then merges the output into large Parquet files partitioned by event_date. Thousands of tiny files an hour is how a cheap lake turns into a slow one.

Step 4: Roll up nightly for the common questions

daily rollup builds per-day aggregates on a < 24h cadence, and finance reconciles against those. Most trend reports read the rollup. query serving layer falls through to raw partitions only when a question needs event-level detail.

One warehouse, two-year retention

Every event sits on warehouse storage and every query competes for the same compute. Dashboards and heavy scans share one queue. Cost tracks retention, so it only goes up.

Tiered lake plus hot store

History sits on object storage in compressed columnar files, recent data on a small hot store. Queries pay only for the partitions they prune to. Cost tracks access, and the dashboard path stays isolated.

Common follow-up questions

• An event arrives three days after its `event_time`. Which tiers does it touch, and what has to rerun? (Tests late data. The lake accepts the write into the old partition, and `daily rollup` reruns only that one `event_date`.)
• Product adds a new field to every event. What breaks? (Tests schema evolution. `Iceberg` adds columns without rewriting old files, so two-year queries keep working while the change stays additive.)