Master Data Management and Duplicate Resolution

What Is Master Data Management?

Master Data Management (MDM) is the discipline of creating and maintaining a single, authoritative, trusted version of key business entities — customers, products, vendors, locations — across an organization's systems. When the same customer exists in a CRM, a billing platform, and a support ticketing system under slightly different names or addresses, every downstream report, mailing, and analysis is contaminated. MDM resolves that fragmentation by producing a golden record: one canonical row that represents the true entity, built by merging the best attributes from all source copies.

MDM is a core topic in the DAMA DMBOK and is tested on the CDMP Associate exam. It also appears in Oracle and Microsoft DP-300 contexts wherever data integration and quality are discussed.

Duplicate Records: Why They Exist

Duplicates arise from multiple entry points (web sign-up vs. call-center entry), inconsistent data standards ("St." vs. "Street"), human keying errors, and system migrations that lack deduplication steps. A customer named "Jon Smith" entered in System A and "Jonathan Smith" entered in System B may be the same person. Detecting that match requires logic beyond a simple primary-key join.

Blocking: Narrowing the Search Space

Comparing every row in Table A against every row in Table B produces a Cartesian product. For two tables of 5,000 rows each, that is 25 million pairs — expensive and slow. Blocking groups records by a shared key so that comparisons happen only within blocks. Common blocking keys include:

• First three characters of last name
• ZIP code or postal area
• Soundex or metaphone code of first name
• First digit of phone number

Blocking trades completeness for performance. You must choose blocking keys that are unlikely to separate true matches.

Deterministic Matching

Deterministic (rules-based) matching declares a match when specific fields are identical. Example: two records are a match if email address AND date of birth are equal. It is fast and auditable but brittle — one typo breaks the match.

Probabilistic Matching

Probabilistic matching assigns a similarity score to each field and combines scores into an overall match weight. PostgreSQL supports this through the pg_trgm extension, which computes trigram similarity (a value from 0.0 to 1.0) between two strings. The Levenshtein distance function (also available via `fuzzystrmatch`) counts the minimum single-character edits needed to transform one string into another; smaller distances indicate closer matches.

Enable the extensions once per database:

```sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

```

Weighted Scoring Rubric

A weighted rubric assigns a maximum point value to each field and multiplies it by the normalized similarity score for that field. A typical four-field rubric for customer records:

| Field | Max Points | Similarity Function |

|---------|------------|-----------------------------|

| Name | 35 | pg_trgm similarity |

| Address | 25 | pg_trgm similarity |

| Phone | 20 | Exact or partial match |

| Email | 20 | Exact or trigram similarity |

Total score = sum of weighted field scores (0–100). Classification thresholds:

• 80–100: Match — merge automatically
• 50–79: Possible Match — route to human review
• 0–49: Non-Match — discard pair

Thresholds must be calibrated against your data; these are starting points, not universal rules.

Survivor Rules and the Golden Record

Once duplicates are confirmed, you must decide which source value wins for each attribute. A survivor rule is a documented policy. Common policies:

• Most recent update wins: the attribute from whichever record has the later `updated_at` timestamp is kept.
• Most complete wins: a non-null value beats a null value.
• Trusted source wins: System A is authoritative for billing address; System B for email.

In today's lab the policy is: most recently updated record wins for each attribute independently. Apply it field by field, not record by record — a golden record may assemble its name from System A and its address from System B.

Document every survivor rule in the data dictionary: what the rule is, why it was chosen, and when it was last reviewed. Without documentation, future maintainers cannot reproduce or audit your merge logic.

Building the Golden-Record Table

The golden-record target table must have a surrogate key (e.g., `golden_id`) and a `source_keys` column or separate linkage table that maps back to the originating system IDs. This linkage is essential for downstream reconciliation and audit trails.

After loading, verify:

```sql

SELECT COUNT(*), COUNT(DISTINCT golden_id) FROM golden_customer;

```

If both counts are equal, there are zero duplicate keys. This is a required pass criterion today.

Data Safety and Professional Responsibility

This lesson works with synthetic data, but in production environments customer records are personally identifiable information (PII). Treat them accordingly:

• Never copy production PII to a laptop or unencrypted external drive.
• Apply role-based access controls so only authorized users can read the match table.
• Log all merge operations — who ran them, when, and what changed.
• Confirm your organization's data retention and deletion policies before merging; GDPR and state privacy laws may impose obligations.

These are professional, not optional, responsibilities. Mishandling PII can result in regulatory penalties and harm to individuals.

Checklist Before You Submit

1. Match table generated by SQL with no syntax errors.

2. Scoring rubric applied to all candidate pairs (Match / Possible Match / Non-Match column populated).

3. Golden-record table passes the zero-duplicate-key check.

4. Merge logic documented in the data dictionary template.