Master Data Management (MDM)

Every system in a company keeps its own list of customers. The same person can be in all of them, with different spellings.

Master Data Management (MDM) finds the records that match. Then it builds one trusted record for each real person.

In Keys & Identity you gave one person one surrogate key. Here the ids come from different systems, so no shared key exists.

The shop, seen from three systems

First, the shop app. Its customers table is the one you know, with the email column from Keys & Identity. Asha lives in DE, because she moved on 2026-01-08.

Two more systems also hold customers. The CRM (customer relationship management) is the sales team’s tool. The support desk is where help tickets live.

These CRM and support records are made up, and the emails are fake. Their ids start at 320. The updated column is the day the record last changed.

Count the records in each system:

SELECT source_system, COUNT(*) AS records
FROM source_customers
GROUP BY source_system
ORDER BY source_system;

Look at Asha. She is shop 1, CRM 320 and support 340. Ben is three records too.

This chapter finds the 6 people and builds one record for each.

What you will learn

1. What master data is, and what it is not.
2. How to clean, block and match records.
3. How to decide: merge, review or keep apart.
4. How the master id and the crosswalk table work.
5. How one golden record is built, field by field.
6. How to undo a wrong merge, and how privacy fits in.

Master data is the set of core things that many systems share. Customers, products, suppliers and locations are common examples.

Transactional data is the events that happen to those things. Reference data is a short list of standard codes. Here is the shop sorted into the three kinds:

Row count does not decide the kind. A list of six suppliers you keep yourself is master data. It can hold duplicates like any other.

Orders are only as good as the customers they point at. If one person has two customer records, their orders split in two. So MDM works on the master data first.

Identity resolution means finding which records describe the same person. No shared key exists, so you compare the values. It has three steps.

1. Clean: make the values look the same, so you can compare them.
2. Block: choose which pairs to compare, so you do not compare everything.
3. Match: score each pair, then decide what to do.

Step 1: clean

Cleaning lowercases the email and removes spaces at the ends. Here are the three records for Asha:

SELECT source_system, source_id,
       LOWER(TRIM(email)) AS email_clean
FROM source_customers
WHERE source_id IN (1, 320, 340)
ORDER BY source_id;

Step 2: block

Comparing every pair is slow. Ten records make 45 pairs. One million records make 499,999,500,000 pairs.

Blocking puts records into groups with a cheap key. You compare only inside a group.

Here the key is the first letter of the name. Count each group:

SELECT LOWER(LEFT(name, 1)) AS block,
       COUNT(*) AS records
FROM source_customers
GROUP BY LOWER(LEFT(name, 1))
ORDER BY block;

Blocks a and b each make 3 pairs. Block c makes 1 pair. Blocks d and o make none, so you compare 7 pairs, not 45.

Step 3 is the match. A deterministic rule is a fixed test with a yes or no answer. For example: two records with the same clean email are the same person.

Find every pair that has the same clean email:

SELECT a.source_system AS a_system, a.source_id AS a_id,
       b.source_system AS b_system, b.source_id AS b_id
FROM source_customers AS a
JOIN source_customers AS b
  ON LOWER(TRIM(a.email)) = LOWER(TRIM(b.email))
 AND (a.source_system, a.source_id) < (b.source_system, b.source_id)
ORDER BY a_system, a_id, b_system, b_id;

Asha’s three records make three pairs. Ben’s CRM and support records make one pair. Shop 2 has a different email, so this rule does not find it.

A probabilistic rule gives each pair a score. A higher score means the pair is more likely the same person. You add points for each field that agrees:

• Same clean email: 60 points.
• Same first name: 20 points. One name starting the other, such as Ben and Benjamin, or an initial and a name: 10 points.
• Same last name, when both records have one: 20 points.
• Same country, when both records have one: 10 points.

A missing value gives 0 points. These weights are made up to teach the idea. Real tools use more fields and tune the weights.

Score shop 2 against support 341. Shop 2 is Ben, and support 341 is Ben Hall:

The deterministic rule missed this pair. The score finds it, but 30 points is not sure. The next section decides what to do with it.

A score is not an answer. You set two cut lines, called thresholds, to turn a score into an action. This chapter uses these:

Blocking gave 7 pairs. Here is each pair with its points:

Four pairs merge, two need review and one stays apart. Asha’s three records are one group. CRM 321 and support 341 are another.

A data steward works the review queue. A steward is the person who decides unclear matches and owns the matching rules. Here are the two pairs, with the steward’s notes:

Both pairs scored 30, and the answers differ. The score only told the steward where to look.

Shop 2 and CRM 321 scored 20, and they stay apart as a pair. But shop 2 joins support 341, and support 341 is joined to CRM 321. So all three become Ben.

Each real person gets one master id, a surrogate key with no meaning, like customer_id in Keys & Identity. The number of a master id never changes. A merge moves records off the extra id, and that id is retired.

A crosswalk table maps each source record to its master id. It is also called a cross-reference, or xref. Here is the table:

CREATE TABLE customer_xref (
  source_system TEXT   NOT NULL,
  source_id     BIGINT NOT NULL,
  master_id     BIGINT NOT NULL,
  linked_by     TEXT   NOT NULL,
  PRIMARY KEY (source_system, source_id)
);

Load the systems in order: shop, then CRM, then support. A record with no merge-lane match starts a new master id. Here is the table after the load and the rule matches, before the steward works the queue:

Ben now has two master ids. Shop 2 has 502. CRM 321 and support 341 share 506.

The steward said shop 2 and support 341 are the same person.

A merge points all the records at one master id. The older id, 502, survives. Id 506 is retired:

UPDATE customer_xref
SET master_id = 502,
    linked_by = 'steward'
WHERE master_id = 506;

SELECT source_system, source_id, master_id, linked_by
FROM customer_xref
WHERE master_id = 502
ORDER BY source_system, source_id;

Keep a small note that 506 now means 502. Then any system that saved 506 can still find Ben. Here is the full crosswalk now:

Six master ids are live: 501, 502, 503, 504, 505 and 507. Ten source records point at them.

The golden record is the one trusted record for a person. You build it field by field, not by copying one source record.

Survivorship rules choose which value survives for each field. This chapter uses three rules. The trust order is a made-up choice: shop, then CRM, then support.

• Name: the most complete wins. That means a first and a last name written out. A tie goes to the most trusted source.
• Email: the most trusted source wins, after cleaning.
• Country: the most recent update wins. A NULL never wins.

Apply the rules to Asha, who is master 501:

The golden record uses three different sources. Write down which source each value came from. This record of origin is called lineage.

Now do the country rule in SQL, for every master at once. In PostgreSQL, DISTINCT ON keeps the first row for each master id, in the ORDER BY order:

SELECT DISTINCT ON (x.master_id)
       x.master_id, s.country, s.source_system
FROM customer_xref AS x
JOIN source_customers AS s
  ON s.source_system = x.source_system
 AND s.source_id = x.source_id
WHERE s.country IS NOT NULL
ORDER BY x.master_id, s.updated DESC;

Dev is master 504. His only record, shop 4, has a NULL country, so the WHERE removed him. To keep him, start from the list of masters and use a LEFT JOIN.

Apply the three rules to all masters. This is the golden table:

Ben shows the name rule. Benjamin Hall (CRM) and Ben Hall (support) are both complete, so the more trusted CRM wins. His email comes from the shop.

Dev’s country stays NULL.

Reports use the golden table as the customer dimension. A conformed dimension is one customer table that all reports share. Dimensional Modeling: Facts & Dimensions explains it.

Each fact row keeps its source id. You join through the crosswalk to reach the master id.

The shop orders are real. These support tickets are made up:

Count paid money and tickets for each master:

WITH paid AS (
  SELECT x.master_id, SUM(o.amount) AS paid_total
  FROM orders AS o
  JOIN customer_xref AS x
    ON x.source_system = 'shop' AND x.source_id = o.customer_id
  WHERE o.status = 'paid'
  GROUP BY x.master_id
), tickets AS (
  SELECT x.master_id, COUNT(*) AS tickets
  FROM support_tickets AS t
  JOIN customer_xref AS x
    ON x.source_system = 'support' AND x.source_id = t.support_customer_id
  GROUP BY x.master_id
)
SELECT g.master_id, g.name, p.paid_total, t.tickets
FROM golden_customer AS g
LEFT JOIN paid AS p ON p.master_id = g.master_id
LEFT JOIN tickets AS t ON t.master_id = g.master_id
ORDER BY g.master_id;

Before the master id, shop 1 and support 340 were strangers. Now one row shows Asha’s money and her ticket. Ben has two tickets and no paid order, because order 103 was refunded.

The query adds money and counts tickets in two separate steps. If you join orders and tickets first, each order repeats once for every ticket, and the totals grow.

The golden table shows today’s country, DE. To keep Asha’s country on each past date, use history.

Slowly Changing Dimensions (SCD 0–7) and Temporal & Historical Modeling teach how.

Matching makes mistakes. Say a steward approved shop 3 and CRM 322 by error. CRM 322 moves to master 503, and master 507 is retired.

The name rule now sees Carla and Carla Mendes. Carla Mendes is more complete, so she wins. Here is the golden record for master 503:

The shop customer Carla now has the wrong name. An unmerge withdraws the merge. You point the record back at its own master id:

UPDATE customer_xref
SET master_id = 507,
    linked_by = 'steward'
WHERE source_system = 'crm'
  AND source_id = 322;

SELECT source_system, source_id, master_id, linked_by
FROM customer_xref
WHERE source_id IN (3, 322)
ORDER BY source_id;

This worked only because the source records were kept. If the merge had overwritten them, you could not tell whose name was whose.

Also record a rule: do not merge these two records again. If you do not, the next matching run may repeat the mistake.

An MDM style says how the golden record relates to the source systems. In MDM, the hub is the central database that holds the master data. It is not the hub table of Data Vault 2.0.

Four common styles exist. Here they are, from the lightest to the strongest:

In the shop, a registry would keep only customer_xref. A consolidation hub would also hold golden_customer. Coexistence would send Asha Kumar and DE back to the shop app and the CRM.

Choose the lightest style that gives the control you need. The golden table of this chapter fits consolidation.

Governance is who owns the rules and the data. A steward does more than clear the queue:

• Decide the pairs that rules cannot decide.
• Own the matching rules, the trust order and the thresholds.
• Turn repeated decisions into rules, so the queue gets shorter.
• Own reference data, such as the country code list, and keep it fresh.

A golden record joins data from many systems. That can show people more than they expect. A support ticket about a refund may now sit next to a sales note.

Give each field the same access rules it had in its source. Security, Governance & Compliance explains how to handle personal data and erasure requests.

Erasure must follow the crosswalk. Say Ben asks you to delete his data. Find every record with his master id:

SELECT source_system, source_id
FROM customer_xref
WHERE master_id = 502
ORDER BY source_system, source_id;

Wrong matches matter here too. If Carla Mendes had been merged into Carla, erasing Carla would erase her as well. If shop 2 had never been linked to Ben, his shop record would stay.

MDM turns many records into one golden record for each real person. Here is the whole chapter, step by step, on the shop data:

Test yourself without looking back:

• Why is orders not master data, while customers is?
• What does blocking save, and what can it hide?
• Two pairs scored 30. Why did the steward decide them differently?
• Which survivorship rule would you use for email, and why?
• Why must you keep the source records?

Next you move to the organization. When many teams own data, who owns the customer?

Four exercises for this chapter. You build each one on the ERD canvas. A review then checks it against sample rows.

Try them with the chapter closed. If one goes wrong, go back to the matching section.