# The Identity Problem > Old systems. New demands. The same customer appears under three different names. Canonical URL: Domain: Pipeline Design · Difficulty: hard · Seniority: L7 ## Problem Our client has been running an Informatica ETL that populates a customer dimension with SCD Type 2 history for 15 years, but it breaks every time the source schema changes and takes 8 hours to run on a 10M-row table. We need to rewrite it in PySpark on Databricks while keeping the legacy system live during migration. The hardest part: the same customer appears under different IDs in 40 source systems and we need to unify them without losing the historical SCD trail. ## Worked solution and explanation ### Why this problem exists in real interviews An L7 migration: keeping a 15-year slowly-changing dimension dimension live and correct while replacing the engine, unifying 40 source identifiers without losing the historical trail, preserving point-in-time attribute history anchored to source transaction time, and not breaking 15 years of fact-table joins. The trap is treating it as a code rewrite and discovering on cutover day that the new pipeline produces a different customer dimension than the legacy one. The whiteboard answer is to write the new pipeline, point reporting at it, and decommission Informatica when it works. The first nightly run produces a dimension that almost matches the legacy one, except for a few hundred rows where the slowly-changing dimension trail diverges. Reports break because the surrogate keys are new. The 40-system unification produces golden customers whose addresses on past dates don't match what the legacy system would have returned because effective dates are now anchored to load time, not source transaction time. Reporting calls a halt and the migration backs out. > **Trick to Solving** > > Run both pipelines for a shadow window with a daily parity gate, anchor effective dates on source transaction time, preserve legacy surrogate keys through a mapping table, never cut over until the diff is empty. > > 1. The new pipeline runs alongside the legacy one for a shadow window. A daily diff job compares outputs row-by-row; the orchestrator gates cutover on the diff being empty. > 2. Entity resolution merges identifiers across 40 systems into a golden customer key, but each merged record's prior history is preserved (per-source slowly-changing dimension trails union into the golden record's history). > 3. Effective dates on the slowly-changing dimension anchor to the source's transaction timestamp, not the load time, so reports for past dates return the same attributes the legacy system would have. > 4. A surrogate-key mapping table maps legacy keys to new golden keys. Existing fact-table joins resolve through the mapping during and after cutover. --- ### Walk the requirements #### Step 1: Shadow run with a daily parity gate; cutover when the diff is empty Reporting cannot tolerate a customer-dimension outage. The new pipeline runs alongside the legacy one for a shadow window: every night, both produce their version of the customer dimension, a diff job compares them row-by-row, and the orchestrator publishes the diff for the team to review. Cutover is gated on the diff being empty (or every difference explained); the legacy pipeline stays live until then. Without orchestration owning the dual run, the diff, and the gate, cutover becomes a heroic effort by the on-call engineer. #### Step 2: Resolve identifiers across 40 systems into one golden record without losing history The same customer appears under different identifiers in 40 systems. The pipeline computes a golden customer key (deterministic on a stable rule set, with a manual-review queue for uncertain merges) and writes one golden record per resolved customer. Each source's prior slowly-changing dimension history merges into the golden record's history, ordered by source transaction time. A naive 'merge to latest' approach loses the per-source trail; a 'keep them separate' approach leaves reporting with 40 partial customers. The merge has to retain the union of histories. #### Step 3: Effective dates anchored to source transaction time, not load time Reports running on past dates have to return the customer's attributes as they were on that date. The slowly-changing dimension's valid_from / valid_to columns anchor to the source's transaction timestamp, not the warehouse's load time. A change that happened in March in the source has valid_from=March, regardless of when the new pipeline first loaded it. Anchoring on load time silently rewrites history every time the pipeline backfills, and a March report run today returns today's attributes for a row that changed in April. #### Step 4: Surrogate-key mapping table so existing fact joins keep resolving Fifteen years of fact tables join to the legacy customer dimension by its surrogate key. The new pipeline emits new surrogate keys for golden records; a mapping table records (legacy_surrogate_key → new_golden_key) so existing fact-table queries resolve through the mapping. Reports continue to work during the shadow window and after cutover. Renaming the column to point at the new key without a mapping breaks every report that uses the old key on the same day. --- ### The shape that fits > **What this design gives up** > > Running two pipelines in parallel for the shadow window doubles the compute on the customer dimension. Entity resolution with manual review of uncertain merges is a workflow somebody has to operate. The mapping table grows with every legacy key that ever existed. Migration speed is the cost; in return, reporting never sees an outage, the golden customers don't lose their history, point-in-time queries stay correct across the cutover, and 15 years of facts keep joining cleanly. > **What reviewers check** > > A reviewer looks at the canvas for these properties: > - An orchestration layer runs new and legacy pipelines in parallel for a defined shadow window with a daily diff gate before cutover. > - The new customer dimension lives in a warehouse / lakehouse and preserves slowly-changing dimension history with effective dates anchored to source transaction time. > - Entity resolution unifies cross-system identifiers into one golden customer while preserving per-source change history. > - A surrogate-key mapping table resolves legacy keys to the new golden keys for existing fact joins. > **The mistake that ships** > > What gets built first writes the new pipeline, runs it once on a Sunday night, and points reporting at the new dimension on Monday morning. The diff against the legacy dimension wasn't run, the team trusted that the logic matched. A handful of golden customers have lost their pre-merge history; a few hundred slowly-changing dimension effective dates are anchored to load time and disagree with the legacy ones; existing reports break because the surrogate keys are new and no mapping was built. The migration backs out by Wednesday and the team rebuilds the shadow window, the entity-resolution merge logic, and the mapping table over the following quarter. --- ## Common follow-up questions - After a few weeks of shadow running, the diff still has a small number of rows that disagree on the same customer. How do you decide whether the diff is acceptable for cutover? _(Tests whether the candidate sees the diff as a triage stream rather than a binary gate: each disagreeing row is investigated, classified (legacy bug, new bug, genuinely ambiguous merge), and either fixed or accepted with a documented reason. Cutover happens when every remaining diff has an explanation, not necessarily when the count is zero.)_ - A previously-merged golden customer turns out to be two different real people. What in this design lets you split them, and what doesn't? _(Tests whether the candidate sees that the entity_resolver has to support an un-merge: the manual-review queue captures the split, new golden keys are issued, the surrogate-key map adds entries for the split, and downstream facts re-resolve through the map. Without an explicit un-merge path, the only fix is a destructive rewrite of the golden dimension.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_identity_problem) - [System Design Interview Questions](https://datadriven.io/data-engineering-system-design) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.