The Identity Problem

A hard 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
hard
Seniority
L7

Problem

How would you replace a 15-year-old Informatica job that builds the customer dimension with full change history and takes 8 hours on a 10M-row table? The data platform team wants the replacement in PySpark on Databricks.

Old systems. New demands. The same customer appears under three different names.

Business requirements

Worked solution and explanation

What this really is

This is a live migration with an identity merge inside it, dressed up as a PySpark rewrite. Anyone can port Informatica logic to Spark. What separates candidates is proving the new dimension equals the old one before anyone depends on it, while three things quietly change underneath: the customer key, the merge of 40 identities, and the clock that stamps each history row. Treat it as a code rewrite and cutover day hands reporting new surrogate keys, merged customers missing their pre-merge history, and past-date reports showing today's addresses. Reporting calls a halt and the migration backs out.

Four decisions, in order

Step 1: Run both pipelines and gate cutover on a daily diff

Legacy stays live. Each night the orchestrator builds customer_dim, reads legacy_informatica_dim, and runs the parity_gate. A non-empty diff holds cutover; it never flips reporting. Without orchestration owning the dual run, cutover depends on one engineer's heroics.

Step 2: Resolve 40 identities into customer_identity_map

Deterministic rules map every source ID to one golden key, and merging unions the per-source histories. 'Merge to latest' throws away the older record's trail, which is exactly what reporting cannot lose.

Step 3: Stamp valid_from with source transaction time

scd2_builder writes each change into customer_dim as a new version, with valid_from set to when the change happened in the source, not when Spark loaded it. Anchor on load time and every backfill rewrites history: a March report run today shows April's address.

Step 4: Carry legacy surrogate keys forward

Fifteen years of facts join on the old key. entity_resolver reads each legacy_sk from legacy_informatica_dim into legacy_surrogate_key_map, and scd2_builder carries it onto every row of customer_dim, so existing joins resolve during and after cutover instead of breaking overnight.

Airflow runs the new build beside the legacy dimension and gates cutover on the parity diff; `customer_identity_map` resolves source IDs to the golden key and `legacy_surrogate_key_map` keeps the old keys on every `customer_dim` version.
  • forty_source_systems (source, PostgreSQL)
  • legacy_informatica_dim (source, PostgreSQL)
  • orchestrator (transform, Airflow)
  • entity_resolver (transform, Spark)
  • customer_identity_map (storage, Delta Lake)
  • legacy_surrogate_key_map (storage, Delta Lake)
  • scd2_builder (transform, Spark)
  • customer_dim (storage, Databricks)
  • parity_gate (quality gate, Great Expectations)
  • reporting (consumer, Metabase)
  • orchestrator -> entity_resolver
  • orchestrator -> scd2_builder
  • orchestrator -> parity_gate
  • forty_source_systems -> entity_resolver
  • legacy_informatica_dim -> entity_resolver
  • entity_resolver -> customer_identity_map
  • entity_resolver -> legacy_surrogate_key_map
  • forty_source_systems -> scd2_builder
  • customer_identity_map -> scd2_builder
  • legacy_surrogate_key_map -> scd2_builder
  • scd2_builder -> customer_dim
  • customer_dim -> parity_gate
  • legacy_informatica_dim -> parity_gate
  • parity_gate -> reporting
  • Sources + control: forty_source_systems, legacy_informatica_dim, orchestrator
  • Identity: entity_resolver, customer_identity_map, legacy_surrogate_key_map
  • History: scd2_builder, customer_dim
  • Parity + reporting: parity_gate, reporting

Common follow-up questions