Everyone Wants the Same Data, Differently

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

Several teams at our company need the same event data but query it very differently. Design a data ingestion pipeline and consider the tradeoffs depending on how the data will be accessed.

How you store it decides how fast you can read it.

Business requirements

• Analytics looks at weeks of trends, support pulls one customer's history, and ML scans the last week; one storage shape can't serve all three.
• We can't keep paying full price to store three years of data; older data is rarely touched but has to be retrievable when someone asks.
• When a customer asks to be deleted, every team's view has to forget them, not just the main analytics tables.
• The source delivers events more than once on retries; analytics, support, and ML all have to count each event once.

Worked solution and explanation

What this really is

This is a fan-out problem dressed up as a storage question. One event stream, three read shapes: analytics scans weeks by date, support fetches one customer, ML scans the last week. Anyone can draw the ingest. The trap is the warehouse monolith: one big table, every team writing its own SQL. It fails three ways at once. Support waits behind scans built for analysts. Three years of history bills at hot-storage prices. The first deletion request becomes a week-long hunt through derived tables nobody inventoried.

Walk the requirements

Step 1: Give each read shape its own layout

Analytics gets a date-partitioned Iceberg lake for scans. Support gets a customer-keyed PostgreSQL store for point lookups. ML gets a small recent-week feature store fed by feature_compute. One shared store means at least two workloads lose, because pruning partitions by date does nothing for a lookup by customer.

Step 2: Keep old history in the lake, not offline

event_lake holds the three-year retention on object storage. Old partitions cost object-storage prices and are still scanned on demand through the same table format. That is slower than a hot tier, but it answers in minutes. There is no restore-from-archive ticket, which is the bar the requirement sets.

Step 3: Dedup before the fan-out

Source retries duplicate events. The Flink stage dedup_stream drops repeats on the event id once, upstream of all three paths. If each team dedups for itself, one customer gets three different event counts and nobody trusts any of them.

Step 4: Route deletion to every store

The Airflow deletion_orchestrator issues the delete to event_lake, support_store and feature_store, and closes the request only when all three confirm. That confirmation log is the proof an auditor asks for.

The reference design

Common follow-up questions

• ML now wants three years of features, not one week. What changes? (Tests whether you treat the lake as the long retention. The right move computes long-window features from `event_lake` in batch rather than stretching the feature store to hold them.)
• A delete arrives while `support_store` is down. What does the audit show? (Tests whether the orchestrator keeps the request open and retries, rather than reporting completion before every store has confirmed.)