Fifty Thousand Retailers

A medium 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

medium

Seniority

L5

Problem

Demand planners on the commercial team can't separate promoted units from baseline, or tell how stale each row's source is. Sell-through arrives as daily distributor EDI files, weekly retailer portal exports from 50,000 partners, and streaming direct-to-consumer orders: design one pipeline that fixes both.

Retail data at CPG scale. Every SKU, every store.

Business requirements

• The commercial team wants one view across direct-to-consumer, distributor feeds, and retailer portals; today the channels are silos with different freshness.
• Retailer files contain duplicate rows and bad product codes; the commercial data team reviews them rather than have the pipeline silently drop them.

Worked solution and explanation

Three clocks, one fact table

This is a freshness contract problem dressed up as a CPG data lake. Anyone can land three feeds in a warehouse. The skill being probed is whether each channel keeps its own clock all the way into the unified fact, so the commercial team can see how stale each slice is. The trap is one shared nightly schedule, which makes DTC a day late and hides the lag. A second trap is joining promotions as of today, which retags last month's sales. Silently filtering bad retailer rows is the third. Get those wrong and the channel totals quietly stop matching what retailers reported.

Step 1: Give each channel its own freshness tier

dtc_stream is a Flink job marked real-time. edi_loader and portal_loader are Spark batch jobs marked < 24h, because the weekly portal drops are polled daily and loaded the moment they land. The grader needs a streaming path and a batch path visible side by side. Merging the three into one job is the mistake.

Step 2: Stamp freshness and promo status in one canonical transform

canonical_transform_freshness_tag is the only place the three shapes become one schema. It writes a source_freshness column on every row. It joins promotion_calendar on sale_date BETWEEN valid_from AND valid_to, so promoted units get a promo_flag and baseline units stay separable. The tag lives in the data, not in a dashboard footnote.

Step 3: Validate retailer rows and route rejects aside

Duplicates and bad product codes hit validate_dedup_retailer_rows. Valid rows continue. Rejects go to triage_review_store with a rejection reason, so the data team reviews them and replays. A filter that drops them hides how often retailers send junk.

Silent filter

A WHERE clause drops duplicates and bad codes. The fact looks clean, and nobody knows a retailer is sending 4% junk until totals fail to reconcile.

Quarantine for triage

The gate sends rejects to triage_review_store with a reason. The good rows keep moving, the data team sees exactly what each retailer sent wrong, and fixes it at the source.

Common follow-up questions

• A promotion is entered retroactively for dates that already have sales. What in this design re-attributes them? (Tests whether the candidate reprocesses the affected dates in `unified_sales_fact` once `promotion_calendar` changes, replacing those rows rather than appending to them.)
• A retailer changes its portal export format mid-quarter. What breaks and what does not? (Tests whether the candidate treats `portal_loader` as the format-aware boundary, so the other channels and the fact schema stay untouched.)