Case 04
Master Data Consolidation
Healthcare and pharmacy organisation
146 data sources. 2,116,759 records. One trusted customer record.
- Source files and SQL sources
- 146
- Records loaded and processed
- 2,116,759
- Consolidated golden record set
- 1
An organisation's customer master existed in 146 separate files and SQL sources, with more than two million records and no single version of the truth. Ixia profiled, validated, cleansed, matched and consolidated the estate into one governed golden record set.
Challenge
- Customer information had accumulated across 146 files and SQL sources, each with its own structures, conventions and duplicates.
- No process could answer the basic question: how many customers do we actually have, and which record do we trust for each one?
Scale
- 146 separate source files and SQL sources brought into one controlled pipeline.
- 2,116,759 records loaded, profiled and carried through validation, matching and consolidation.
Ixia's role
- Design and build of the end-to-end consolidation pipeline.
- Profiling and validation rule definition with the business.
- Matching, deduplication and survivorship logic.
- Delivery of the consolidated golden record set with the evidence behind it.
Engineering approach
- Source-by-source profiling before any consolidation logic was written.
- Check and validation rules applied at entry, so bad records were caught, not blended.
- Cleansing and enrichment to standardise names, addresses and identifiers ahead of matching.
- Matching and deduplication across sources, clustering candidate records for survivorship decisions.
- Consolidation into a single governed golden record per customer, with lineage back to every contributing source.
What made it difficult
- 146 sources meant 146 sets of conventions, and the same customer appearing under different names, addresses and identifiers.
- Matching at this scale cannot be eyeballed. The logic had to be trustworthy enough to merge records automatically and honest enough to queue the doubtful ones.
- Survivorship is a business decision. Which value wins had to be agreed, recorded and repeatable, not decided silently by code.
Outcome
- A single consolidated customer record set, built from 2,116,759 source records.
- Duplicates identified and resolved through agreed matching and survivorship rules.
- Lineage preserved from every golden record back to its contributing sources.
Delivery lessons
- Golden records are earned through rules and evidence, not declared by a tool.
- Profiling first. Every hour spent understanding the sources saved days in matching.
- Survivorship logic belongs to the business. Engineering makes it repeatable.
Technologies
- Data profiling
- Validation and check rules
- Cleansing and enrichment
- Fuzzy and exact matching
- SQL-based consolidation
Related capabilities and insights
Facing something similar?
Bring us the programme. We will tell you honestly what it takes.