01 · The challenge
Same store. Two account numbers. Broken joins.
After a move to a new ERP, some data pipelines started failing. When an inactive store reactivated, it could end up with two account numbers: an old system-generated one and the official new one.
Silver-layer tables still carried the old number while the master list had the new one, which broke joins and caused pipeline failures plus missing or duplicated records.
02 · What I did
What I owned and built.
- Built Phase 1, a SQL script that finds every agency/chain/store combination with conflicting account numbers and exposes them in a conflicts view.
- Built Phase 2, a PySpark script that scans a controlled list of Silver tables and writes the size of the impact to a summary table for team review.
- Investigated duplicate records in a POS detail table tied to the account-correction process, narrowed the cause to MERGE behavior with duplicate IDs, and confirmed it with diagnostic queries.
- Kept the table list manually maintained so the team has full control over what gets checked.
03 · Architecture
How it fits together.
SQL view
PySpark · curated table list
04 · Key decisions
The judgment behind the build.
Why two phases?
First prove the conflicts exist and list them, then measure how far they reach. Each phase answers one question cleanly.
Why a manually curated table list?
Automatic discovery is convenient but opaque. A curated list keeps the team in control of scope and cost.
Why chase the MERGE duplicates?
Duplicates caused by the correction process itself would quietly undo the fix. Finding them early protected every downstream consumer.
05 · Impact
What changed because of the work.
- Turned unexplained pipeline failures into a concrete, reviewable list of conflicts.
- Shows how far each conflict reaches across Silver tables, so remediation can be prioritized.
- Directly informed downstream fixes, including a join correction in the Sales Datamart Explorer.
06 · What’s next
Where it goes from here.
Review impact results with the team, agree on a remediation strategy, and put prevention in place for future reactivations.
This case study is a sanitized reconstruction. Organization names, proprietary code, data, and internal schemas are intentionally left out.