Every Version of 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

mid

Asked in

Citi

Problem

A retail bank rebuilds its customer dimension every night from a 400M-row account-state extract, and analysts need every change to an account preserved as its own dated version rather than overwritten. The build has to finish before the 7am reporting window even though a few corporate accounts each generate millions of change records, and it can only start once the upstream extract job signals it has landed. Lately the run has been blowing past 7am on nights when those heavy accounts churn.

Business requirements

• Analysts need to see what an account looked like on any past date, so every change has to become its own dated version instead of overwriting the row.
• A handful of corporate accounts generate millions of change records each, and the run drags on the nights they churn.
• The merge must not start until the upstream account-state extract has finished landing.
• The dimension has to be published before the 7am reporting window, every night.
• A failed or re-run night should not duplicate versions or corrupt the history.

Worked solution and explanation

Strip the disguise

This is an SCD Type 2 build dressed up as a nightly refresh, and the 7am miss is a skew problem, not a size problem. Anyone can close one version and open the next. What separates candidates is seeing that a few corporate accounts put millions of change records on one key, so a single task runs for hours while the cluster idles. Overwrite the table and you destroy the history the regulatory joins need. Run a full 400M-row compare and every churn night blows the deadline.

Walk the requirements

Step 1: Gate the build on the extract, not the clock

The extract slips from 2am to 4am on bad nights. The orchestrator waits on a completion signal from account_state_extract before anything starts. A fixed clock time would read a half-written extract and publish a wrong dimension nobody notices until reports are out.

Step 2: Version only what changed in staged_changes

Compare a hash of the tracked attributes to the hash on each account's open version. Equal means no new version. Different means close the old row by setting valid_to and open a new one. Decide up front what a change record's date means and how same-night changes to one account are ordered, because the hash alone does not say.

Step 3: Spread the hot keys inside scd2_merge

In a plain keyed merge the heavy accounts all land on one partition. Salt those keys, or build the versions set-wise by ordering each account's changes and deriving valid_to with lead(), so work spreads by account and date. Either shape is correct. What matters is that no one key owns a task.

Step 4: Make the night re-runnable

Build into staging, pass the quality_gate, then overwrite the night's partition in customer_dimension. A retry converges to the same rows. Appending straight into the live table leaves two open versions on an account after a half-finished night, and every downstream join double counts.

Full overwrite

Reads all 400M rows, rewrites the table, loses history, and re-joins the hot accounts every night.

Hashed SCD2 build

Versions only the changed rows, keeps point-in-time history, and confines the skew so the 7am window holds on busy nights.

Common follow-up questions

• Two changes to one account arrive in the same extract. Which is the current version, and what does `valid_from` mean for each? (Tests whether the candidate defines the change date (effective date or landing date) and a deterministic tie-break for same-night changes.)
• A late correction arrives for a change versioned two nights ago. How do you repair history without breaking downstream joins? (Tests a retroactive repair: rewrite the affected partitions with corrected `valid_from` and `valid_to`, not a new version dated today.)