Under One Roof

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

We run a global retailer with physical stores and e-commerce, and four teams pull the same sales from one long-retention data lake: merchandising watches live performance and needs each transaction within minutes, finance reconciles daily totals, data science trains weekly models, and supply chain fires reorder triggers. Card numbers fall under PCI and can't be written to the lake at all, and both the point-of-sale stream and the e-commerce stream retry, so the same sale can arrive twice. Design the architecture from those two event streams through to each of the four consumers.

Business requirements

• Merchandising adjusts inventory and pricing on live performance; sales have to land in their dashboard within minutes.
• Merchandising runs interactive dashboards, finance reconciles daily, data science trains weekly, and supply chain reads inventory triggers; one storage layer can't serve all four.
• Card data is regulated; we can't fail a PCI audit on what's stored in the data lake.
• Both ingestion paths can deliver the same sale twice on retry; the merchandising and finance views can't double-count.

Worked solution and explanation

What this really is

This is a fan-out problem dressed up as retail analytics. You have one sales stream and four query shapes with four cadences, so you need one clean stream feeding several stores. Anyone can draw Kafka into a lake. What separates candidates is where they put the two irreversible steps. Tokenization and dedup have to happen once, at ingest, before any storage node. Put them in the lake as a masking view and the auditor finds raw PANs in the underlying files. Dedup per consumer and finance's daily total drifts away from merchandising's live count by exactly the retries.

Walk the requirements

Step 1: Put the tokenizer ahead of every store

The queue may carry raw card data, but the first storage node may not. Swap the PAN for a token in-stream, so PCI scope shrinks to the tokenizer and its vault instead of every table the sale ever touches.

Step 2: Dedup on transaction_id before fan-out

POS and e-commerce both retry, so both must converge on the same dedup step. Pair it with an idempotent upsert on the same key, so a replay after a crash rewrites a row instead of adding one.

Step 3: Give merchandising a real-time path

dedup_stream runs on Flink at real-time freshness and writes straight into merchandising_store. The lake is not on this path. Minutes-fresh dashboards cannot wait for a daily partition to land.

Step 4: Route each team to its own shape

Finance reads a daily Snowflake build from the lake. Data science reads stable lake history for weekly training. Supply chain gets a point-lookup store keyed on SKU for reorder triggers. The lake is the shared long-retention anchor, not the table every team queries.

The reference design

Dedup per consumer

Each store runs its own DISTINCT logic with its own window. Finance catches a late retry and merchandising does not, so the two totals never reconcile.

Dedup once at ingest

One step keyed on transaction_id feeds every store. Downstream upserts on the same key make replays harmless, and every view counts the same sale once.

Common follow-up questions

• Data science wants original card numbers for fraud features. What does this design allow? (Tests PCI scope: derive BIN or issuer country at the tokenizer and carry those as fields. Raw PAN access stays a separate, audited path.)
• A late POS retry arrives after the daily finance build. How does the number stay right? (Tests idempotency plus `partition_overwrite` backfill: rebuild the affected day's partition instead of appending.)