The Vendor Who Never Warns You

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

Equinox

Problem

An external vendor sends us a data file every month, and its structure shifts without warning: new columns appear, existing ones get renamed, and some are occasionally dropped. Design the pipeline that ingests it for the analysts' monthly reporting.

Every month, something is different. The dashboards have no idea.

Business requirements

• The vendor file lands on the 1st of each month and analysts run monthly reports starting 9am on the 2nd; the warehouse has to be loaded by then.
• Analyst dashboards can't break each time the vendor renames a column; they need a stable contract.
• The vendor occasionally sends a truncated or malformed file; partial loads are worse than no load and the team has to know immediately.

Worked solution and explanation

This is a contract boundary dressed up as a file-ingest job. Anyone can land a file and load it. The skill being probed: can you put a stable interface between a producer who changes shape without warning and consumers who cannot change at all? The trap is inferring the schema each month and loading whatever parses. A rename then becomes a silent null column in the dashboards, and a truncated file becomes a partial load that analysts find out about at 9am on the 2nd, when the totals are low.

Walk the requirements

Step 1: Land the raw file untouched, then let the orchestrator own the clock

The file arrives on the 1st and is needed by 9am on the 2nd, so you have a day of slack, not minutes. Keep the original in a landing zone so a bad run can be replayed without re-asking the vendor. The orchestrator schedules the run and pages if a stage is at risk, which is how you get warned before 9am rather than at 9am.

Step 2: Gate the load with a schema validator that alerts

Compare the file's columns, row counts and types against the registered schema on arrival. Anything missing, truncated or unrecognized stops here and alerts the team immediately. The validator is the only door into the load, so a partial file never reaches a table. That is what 'partial loads are worse than no load' means in practice.

Step 3: Normalize into the warehouse, then expose a contract view

The normalization transform maps whatever the vendor called a column onto your stable names and loads the warehouse table. Dashboards read only the contract view. When the vendor renames a column, you edit the mapping and the view definition stays put, so no dashboard query changes.

Load what parses

Infers columns each month. A rename lands as a new column, dashboards return nulls, and a truncated file loads partially. Nobody hears about it until analysts open their reports.

Validate, then load

Checks structure against the registered schema first. A bad file stops at the gate with an alert, and the warehouse keeps last month's complete data until someone decides.

Common follow-up questions

• The vendor adds a new column analysts want. What in this design lets them use it without breaking existing dashboards? (Tests whether you see the contract view as additive: register the column, extend the mapping, expose it in the view, and existing queries are unaffected.)
• The 1st is a Saturday and the file arrives a day late. What does the pipeline do and what do analysts see on Monday? (Tests alerting on a missing file before the deadline, and whether analysts keep last month's complete data instead of a half-loaded table.)