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, versioning each account's history. The on-call data engineer keeps watching that run blow past 7am on nights a few corporate accounts churn millions of changes. Design the nightly pipeline she can trust to land on time.

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 rest of the cluster idles. Overwrite the table and you destroy the history analysts need for as-of joins. 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. The same scheduler owns the nightly run, and customer_dimension pages on-call if it is not published by 7am.

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 salted_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 delete and rewrite the night's partition in customer_dimension. A retry converges to the same rows. Appending straight into the live table leaves 2 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

• 2 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 and a deterministic tie-break for same-night changes.)
• A late correction arrives for a change versioned 2 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.)