The Distributor Filing Problem

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

Asked in

PepsiCo

Problem

A consumer goods company receives weekly sales files from hundreds of independent distributors, each in its own reporting format, and the sales forecasting and supply chain teams analyze them centrally. Design the pipeline that ingests, normalizes, and loads these files into the central warehouse.

Hundreds of suppliers. One warehouse. One deadline.

Business requirements

• The Monday morning sales report runs on data from hundreds of distributors; every available file has to be loaded by Sunday midnight, and late files loaded retroactively.
• Each distributor uses its own column names and units; a quirky file or quality failure can't block the other distributors from loading.
• The sales ops team chases distributors who haven't delivered; today they file tickets to engineers because there's no self-service view.

Worked solution and explanation

This is fault isolation at fan-in, dressed up as a Monday sales report. Hundreds of feeds, each in its own dialect, one deadline. Anyone can draw files flowing into a warehouse. What separates candidates is whether one bad distributor can hold the other few hundred hostage. Get that wrong and Monday's report is empty because one supplier sent kilograms in a column the loader read as pounds.

Walk the requirements

Step 1: Land raw files untouched, then orchestrate per distributor

Files drop into the landing zone exactly as sent, so a bad parse never loses the original. The orchestrator watches arrivals and launches one run per distributor and period. A late file is just a run whose period is already past, so nothing has to be re-run in bulk.

Step 2: Normalize from a mapping, not from inference

Each distributor's column names and units are registered in the mapping config. The normalization layer reads that row and emits the canonical schema. Onboarding a distributor is a config insert, not a deploy.

Step 3: Validate per distributor and park the failures

The validation layer checks each distributor's output on its own. A failing batch goes to a dead letter queue via errorAction set to dlq, and every other distributor keeps flowing to the warehouse.

Step 4: Overwrite by period and track arrivals separately

The warehouse loads with partition_overwrite on the period each file covers, so a late file replaces its own slice and nothing else. The orchestrator also writes arrivals to a delivery status table, and sales ops reads it through their own dashboard instead of filing tickets.

One shared ingester

A single job infers each format and loads everything together. The first quirky file fails the run, and the loader keyed on 'this Sunday's drop' ignores anything that arrives late.

Per-distributor units of work

Each distributor and period is its own run with its own mapping. A failure stays in that run, and a late file replays one period with partition_overwrite instead of reloading the week.

Common follow-up questions

• A distributor resends last month's file with corrected numbers. What makes the reload safe? (Tests whether the candidate sees `partition_overwrite` on the reporting period replacing the old slice, so the corrected file cannot double count.)
• A distributor silently changes a column's unit from kilograms to pounds. Where does it get caught? (Tests whether the candidate puts range and distribution checks in the validation layer, scoped per distributor, so the batch parks instead of corrupting the warehouse.)