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

How would we carry sales from our point-of-sale and e-commerce event streams, peaking near 5,000 a second, into a data lake that keeps seven years of history, and on to merchandising, finance, data science and supply chain?

Business requirements

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. Collapse the retries there, before the fan-out, and every store downstream receives each sale exactly once without needing its own logic.

Step 3: Give merchandising a real-time path

dedup_stream runs on Flink and writes straight into merchandising_store, which is kept fresh within a minute. 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 warehouse 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

Tokenize and dedup once in-stream, then fan out: live stores for merchandising and supply chain, the object-storage lake for data science, and a daily warehouse for finance.
  • pos_terminals (source, Kafka)
  • ecommerce_orders (source, Kafka)
  • tokenizer (transform, Flink)
  • dedup_stream (transform, Flink)
  • sales_lake (storage, object storage)
  • merchandising_store (storage, PostgreSQL); freshness: < 1min
  • finance_warehouse (storage, warehouse); freshness: < 24h
  • inventory_triggers (storage, PostgreSQL)
  • merchandising_team (consumer, Grafana)
  • finance_team (consumer, dashboard)
  • data_science (consumer, Jupyter)
  • supply_chain (consumer, API)
  • pos_terminals -> tokenizer
  • ecommerce_orders -> tokenizer
  • tokenizer -> dedup_stream
  • dedup_stream -> sales_lake
  • dedup_stream -> merchandising_store
  • dedup_stream -> inventory_triggers
  • sales_lake -> finance_warehouse
  • merchandising_store -> merchandising_team
  • finance_warehouse -> finance_team
  • sales_lake -> data_science
  • inventory_triggers -> supply_chain
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, so a retried sale never reaches any consumer twice and every view counts the same sale once.

Common follow-up questions