The Agency That Changes the Columns

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

L6

Problem

What ETL pipeline should we build to land a government agency's weekly regulatory reporting file in our warehouse, when columns get added or renamed between files and sometimes the whole layout shifts?

The schema changed overnight. Again.

Business requirements

• The compliance team runs the regulatory review at 9am Monday; the weekly file has to be loaded and validated before then, and someone has to hear early if the file is late.
• The agency changes the file format whenever they feel like it and incorrect data triggers regulatory penalties; we can't find out after we've loaded it.
• The agency occasionally sends a corrected file the next day; the warehouse needs the corrected version, not duplicates of both.
• Files contain rows with missing required fields; compliance reviews them rather than have the pipeline silently drop them.

Worked solution and explanation

The trap beneath the moving schema

This is schema-drift detection dressed up as regulatory reporting. Anyone can write an ETL that infers columns and appends. The skill is noticing that today's file is no longer the file you agreed to parse, and stopping before a single row moves. Inference is the wrong instinct. Rename a column and the job happily loads the new name, the old one reads empty, and compliance files a report with half the data missing. The penalty lands on you, and a re-run does not undo it.

Walk the requirements

Step 1: Land and validate before Monday 9am

The orchestrator runs the weekly DAG: wait for the file, validate, load. It alerts hours before 9am if the file is late or a stage is at risk. Without it nothing owns the deadline. Without a warehouse the cleaned data has nowhere to land.

Step 2: Detect a format change on arrival and stop

schema_validator compares incoming column names, types and order to the expected schema before any row moves. Any drift halts the run and alerts on-call. A human updates the schema, then replays. Auto-adapting corrupts downstream on the first rename, and log-and-continue is the same failure with paperwork.

Step 3: Corrections replace the original

staged_load writes to a staging table, then swaps the reporting period's partition with partition_overwrite. A corrected file runs the same DAG against the same partition, so the original rows are replaced, never stacked beside the new ones. Rerunning the same file is also safe.

Step 4: Bad rows go to compliance, the file keeps moving

row_validator routes rows with missing required fields to quarantine_table and passes clean rows on. Silently dropping hides how often it happens. Failing the whole file over one bad row blocks compliance on rows they would have set aside anyway.

The shape that fits

Infer and append

Infers columns per file, drops invalid rows and appends. A new column is silently ignored, a rename empties half the report, and a resend duplicates the period. Compliance finds out two reviews later.

Expected schema and overwrite

Checks structure against config and halts on drift. Quarantines bad rows and swaps the period with partition_overwrite. Failures are loud, and the warehouse never holds a half-right week.

Common follow-up questions

• The agency adds a column compliance actually wants. What is the path from arrival to publish? (Tests whether the validator is a forcing function: the halt fires, a human approves the new expected schema, the load replays. The pipeline never auto-learns.)
• A corrected file arrives where only some rows differ. Why not diff and load only the changes? (Tests whether you see that `partition_overwrite` replaces the whole period atomically, which is simpler and safer than row-level dedup for a small weekly file.)