Master Data Management (MDM) for Data Engineers: Golden Records & Survivorship Rules

master data management is the discipline of taking the same real-world thing — one customer, one product, one supplier — as it appears, spelled differently and half-populated, across a dozen source systems, and producing one trusted, deduplicated, best-of-breed record that every downstream system can agree on. It is not a vendor tool and it is not a data-warehouse layer you buy; it is a set of engineering decisions — how you match records that belong to the same entity, how you merge them, and which value wins when two sources disagree — and those decisions almost always land on a data engineer to implement in SQL and pipelines. Get the matching wrong and you fuse two different customers into one; get the survivorship rules wrong and you publish a stale phone number as the truth.

This guide walks the whole thing end to end, the way you would actually build it: the three classes of data you have to tell apart before anything else (master, transactional, and reference data), the domains MDM governs, and then the pipeline that turns many messy source rows into a golden record — standardizing and running match and merge / identity resolution, linking source keys through a cross-reference table to one durable master key, and applying survivorship rules to pick the winning value for every attribute, with data stewardship overrides sitting on top of the automated logic. Each domain pairs a teaching block with worked examples — real input tables, the SQL, a step-by-step trace, the output, and a concept-by-concept breakdown — and closes on the four MDM architecture styles and the single source of truth trade-offs an interviewer is really probing.

When you want hands-on reps alongside the reading, drill the identity-resolution mechanics on the SQL joins practice library →, rehearse the merge pipelines on the ETL practice library →, and pressure-test the modeling on the design practice library →.

---

On this page

• What MDM is — and why data engineers own it
• Match & merge — identity resolution
• Golden record construction
• Survivorship rules and data stewardship
• MDM architecture styles and interview signals
• Cheat sheet — MDM recipes
• Frequently asked questions
• Practice on PipeCode

---

1. What MDM is — and why data engineers own it

MDM produces one trusted version of a shared business entity across every system that touches it

The one-sentence framing that makes everything else fall into place: master data management is the process of consolidating the master entities — customers, products, suppliers, locations — that are duplicated and inconsistent across your source systems into a single, deduplicated, best-of-breed golden record, so that "who is customer X" has exactly one answer instead of six. Everything in this guide is a step in that consolidation: telling master data apart from the rest, matching records that describe the same entity, merging them, and choosing which attribute value survives.

The three data classes — learn to tell them apart, because MDM only governs one of them. Before you can manage master data you have to recognise it, and the fastest way is to ask "is this a noun, an event, or a lookup?"

• Master data — the nouns. The core business entities that are referenced over and over and change slowly: a customer, a product, a supplier, a store location, an employee, an account. This is what MDM governs. It is relatively low-volume, high-value, and shared across many systems.
• Transactional data — the verbs / events. The things that happen to master entities: orders, payments, shipments, clicks, support tickets. High-volume, append-mostly, timestamped. MDM does not master transactions; transactions reference the mastered entities by key.
• Reference data — the controlled vocabularies. The small, standardised lookup sets that both master and transactional data point at: ISO country codes, currency codes, unit-of-measure, order-status enums, product-category taxonomies. Reference data is often centrally governed too, but it is a thin, enumerated domain (a fixed list), whereas master data is rich and instance-level (millions of distinct customers).

The MDM domains — the entity types you master. Most programs start with one domain and expand. Each has its own match rules and survivorship policy.

• Customer / party — the classic first domain (also called Customer 360). Highest duplication, hardest matching (people move, marry, mistype).
• Product — SKUs, variants, hierarchies; matching is often on GTIN/UPC plus attributes.
• Supplier / vendor — the buy-side twin of customer; drives spend analytics and compliance.
• Location / address — sites, stores, facilities; leans on address standardization and geocoding.
• Employee / person — HR master; feeds provisioning and org hierarchy.
• Account / asset / financial — ledgers, contracts, instruments where consistency is regulated.

Why the golden-record work lands on data engineers. In an org with a dedicated MDM tool, a steward configures rules in a UI — but someone still has to feed clean, standardized data in, reconcile the tool's output back into the warehouse, and reproduce the same logic in the lakehouse for analytics. In an org without a tool (most of them), the entire thing is a data-engineering job: the matching is JOINs and window functions, the xref is a table you maintain, survivorship is CASE/COALESCE/ROW_NUMBER, and publishing the golden record is a pipeline you schedule. Either way, the correctness of "the single source of truth" is your correctness.

The MDM lifecycle, as a pipeline. Hold this shape in your head; the rest of the guide is these stages in order.

• Collect — land raw records from every source system into a staging area, keyed by (source_system, source_key).
• Standardize / cleanse — normalise formats (case, whitespace, phone, email, address) so comparable things look comparable.
• Match — identity resolution: find the sets of records that refer to the same real-world entity.
• Merge — assign each match set one stable master id and record the mapping in a cross-reference (xref) table.
• Survive — for each attribute, choose the winning value across the contributing sources using survivorship rules.
• Publish — materialise the golden record and expose it (a view, a table, an API) to downstream consumers.
• Steward — let humans review low-confidence matches and override wrong values; feed those decisions back in.

Classifying a schema into master, transactional, and reference — a worked teaching example

Detailed explanation. Before you write a single match rule you have to decide which tables even contain master data, because mastering the wrong table (say, trying to dedupe an append-only orders fact) is a category error that senior reviewers pounce on. The mechanical test is the noun/verb/lookup question plus three tells: cardinality of change (master changes slowly, transactions never change once written), whether the table is referenced by foreign keys from many others (master is referenced; transactions reference), and whether the domain is a fixed enumerated list (reference).

• Master tell — a slowly-changing business entity referenced by many *_id foreign keys (customer, product).
• Transactional tell — an append-mostly, timestamped event that carries those foreign keys (orders, payments).
• Reference tell — a small, closed vocabulary that everything joins to for a label (country, order_status).

Question. Classify each table in a small retail schema as master, transactional, or reference, and mark which ones MDM should govern.

Input.

Table	Row count (order of magnitude)	Changes after insert?	Referenced by FKs?
customer	10^6	yes (address, phone)	yes (orders, tickets)
orders	10^9	no (immutable event)	no
product	10^5	yes (price, name)	yes (order_items)
order_status	10^1	rarely (new enum value)	yes (as label)
country	~250	almost never	yes (as label)
support_ticket	10^7	status only	no (references customer)

Code.

-- A quick profiling query that surfaces the master/transactional tell:
-- master entities are referenced by many tables; transactions reference others.
SELECT
  referenced_table_name           AS table_name,
  COUNT(*)                        AS inbound_fk_count   -- how many tables point AT it
FROM information_schema.referential_constraints rc
JOIN information_schema.key_column_usage k
  ON rc.constraint_name = k.constraint_name
GROUP BY referenced_table_name
ORDER BY inbound_fk_count DESC;   -- high inbound FK count -> candidate master entity

Step-by-step trace.

1. customer and product change after insert and are pointed at by many foreign keys → nouns referenced everywhere → master (govern with MDM).
2. orders and support_ticket are timestamped, append-mostly, and carry foreign keys to customer/product → verbs → transactional (not mastered; they consume the master ids).
3. order_status and country are tiny closed lists everything joins to for a label → reference (governed, but as enumerations, not instance-level golden records).
4. The profiling query ranks tables by inbound foreign-key count; the top of that list is where your master domains almost always are.

Output:

Table	Class	MDM governs it?
customer	master	yes (Customer domain)
product	master	yes (Product domain)
orders	transactional	no
support_ticket	transactional	no
order_status	reference	as reference data
country	reference	as reference data

Rule of thumb. If a table is a slowly-changing noun that other tables point at by id, it is master data and MDM should govern it; if it is a timestamped event that carries those ids, it is transactional and you leave it alone.

The MDM lifecycle as a staged pipeline — a worked teaching example

Detailed explanation. The second thing to internalise is that MDM is a pipeline with named stages, not a single magic step, because every interview and every real incident traces back to "which stage failed." Landing raw source rows keyed by (source_system, source_key) preserves lineage; standardizing before matching is what makes matching work at all; the xref table is the durable artifact that makes the whole thing idempotent (re-running never re-invents master ids). Seeing the stages as distinct, re-runnable steps is what lets you debug "why did these two customers merge?" by pointing at the match stage rather than guessing.

• Stage boundaries are debugging boundaries — a bad merge is a match bug; a stale value is a survivorship bug; a lost id is an xref bug.
• Standardize before match — normalization is what turns "Jon"/"J."/"Jonathan" into comparable keys.
• The xref is the memory — it maps (source_system, source_key) → master_id so re-runs are idempotent.

Question. Given three raw customer rows from three systems, walk them through collect → standardize → match → merge → survive and show what each stage adds.

Input.

source_system	source_key	name	email	updated_at
CRM	C-88	Jon Smith	[e-mail]	2026-08-10
ERP	E-13	Jonathan Smith	[e-mail]	2026-07-01
WEB	W-42	jon smith	(null)	2026-08-12

Code.

-- Stage 2 (standardize): build a normalized match key per raw row
CREATE OR REPLACE VIEW stg_customer_std AS
SELECT
  source_system,
  source_key,
  name,
  LOWER(TRIM(email))                                   AS email_norm,
  LOWER(REGEXP_REPLACE(name, '[^a-zA-Z]', ''))         AS name_norm,   -- "jonsmith"
  updated_at
FROM stg_customer_raw;

-- Stage 3+4 (match + merge): group rows that share a normalized email into one master_id
CREATE OR REPLACE VIEW xref_customer AS
SELECT
  source_system, source_key,
  DENSE_RANK() OVER (ORDER BY COALESCE(email_norm, name_norm)) AS master_id  -- naive single-key demo
FROM stg_customer_std;

Step-by-step trace.

1. Collect keeps each row tagged with its (source_system, source_key) so we never lose where a value came from.
2. Standardize lowercases the emails ([e-mail] → [e-mail]) and strips the names to jonsmith, so all three rows now share comparable keys.
3. Match sees CRM and ERP share email_norm = [e-mail]; WEB has a null email but the same name_norm = jonsmith, so a name-based rule folds it in too.
4. Merge assigns the three rows one master_id and records it in the xref.
5. Survive (next sections) then picks one name, one email, one updated_at for the published golden record.

Output:

source_system	source_key	master_id	note
CRM	C-88	M-1	matched on email
ERP	E-13	M-1	matched on email
WEB	W-42	M-1	folded in on name

Rule of thumb. Treat MDM as named, re-runnable stages keyed by (source_system, source_key); standardize before you match, and let the xref table — not a fresh guess each run — be the durable memory of who is who.

---

2. Match & merge — identity resolution

Identity resolution is the hard core of MDM: decide which records describe the same real-world entity before you dare merge them

The invariant to burn in: match and merge always runs in the same order — standardize the fields into comparable form, reduce the comparison space with a blocking key, compare candidates with deterministic rules first and probabilistic (fuzzy) scoring second, then stitch the surviving pairwise links into match groups via connected components — and a false merge (two real entities fused) is far more damaging than a missed match, so you tune toward precision. Every identity-resolution decision is a point on that pipeline.

Standardize first — matching is only as good as the normalization under it. You never compare raw values.

• Case / whitespace / punctuation — lowercase, trim, collapse internal spaces, strip punctuation.
• Emails — lowercase; optionally strip +tags and dots in the local part for gmail-style addresses.
• Phones — strip to digits, normalise country code to E.164.
• Names — split into parts, standardise nicknames (Jon→Jonathan via a nickname dictionary), remove titles/suffixes.
• Addresses — parse and standardise (USPS/libpostal style): "St"→"Street", ZIP+4, geocode.

Blocking — you cannot compare every pair, so you don't. Comparing N records pairwise is O(N²); at 10 million records that is 10^14 comparisons — impossible. Blocking partitions records into buckets by a cheap key (e.g. ZIP + first initial, or the first 4 chars of a normalized name) and only compares within a block, cutting comparisons by orders of magnitude. The art is choosing a blocking key loose enough not to split true matches apart but tight enough to shrink the candidate set.

Deterministic vs probabilistic matching — exact keys first, fuzzy scoring for the rest.

• Deterministic matching — records match if they agree exactly on a chosen key or key combination (email_norm, or phone_norm + name_norm). Fast, explainable, high precision. This clears the easy 80%.
• Probabilistic / fuzzy matching — for the remainder, compute a similarity score across several fields (name via Jaro-Winkler/Levenshtein, address token overlap, birthdate agreement), weight the fields, and match above a threshold. Higher recall, but you must set thresholds and accept a review band.
• The threshold and the review band — above the high threshold auto-merge; below the low threshold reject; in between, route to a steward. This three-way split is the professional pattern.

Match groups — matching is transitive, so pairwise links must be closed into groups. If A matches B and B matches C, then A, B, and C are one entity even if A and C never scored a direct match. Treat each pairwise match as an edge in a graph and compute connected components; each component is one match group that will get one master id. Skipping this step is the classic bug that leaves the same entity split across two master ids.

Common trap answers to pre-empt.

• Matching on raw, un-standardized fields — "[e-mail]" ≠ "[e-mail]" to a raw equality check; you'll miss obvious duplicates.
• Skipping blocking — an all-pairs comparison that "works" on 10k rows melts down at 10M.
• Auto-merging on a fuzzy score with no review band — one over-eager threshold fuses two different people; a false merge is expensive to unwind.
• Forgetting transitivity — computing pairwise matches but never taking the connected components, so one entity ends up as two master ids.

Deterministic matching with normalized match keys — a worked teaching example

Detailed explanation. The workhorse of MDM is deterministic matching on a normalized key: standardize the discriminating fields, then group rows that share the key. Start with the strongest single identifier available (email or phone for customers), fall back to a composite key (name_norm + zip) when the strong one is null. This clears the bulk of duplicates cheaply and explainably before you ever reach for fuzzy scoring.

Question. From four raw customer rows, produce a deterministic match group id by grouping on a normalized email, falling back to name_norm + zip when email is null.

Input.

src	key	name	email	zip
CRM	C1	Jon Smith	[e-mail]	94107
ERP	E1	Jonathan Smith	[e-mail]	94107
WEB	W1	J. Smith	(null)	94107
CRM	C2	Mia Wong	[e-mail]	10001

Code.

WITH std AS (
  SELECT
    src, key, name, zip,
    LOWER(TRIM(email))                                AS email_norm,
    LOWER(REGEXP_REPLACE(name, '[^a-zA-Z]', ''))      AS name_norm
  FROM customer_raw
),
keyed AS (
  SELECT
    src, key,
    -- deterministic match key: prefer email; else fall back to name_norm + zip
    COALESCE(email_norm, name_norm || '|' || zip)     AS match_key
  FROM std
)
SELECT
  src, key,
  DENSE_RANK() OVER (ORDER BY match_key)              AS match_group
FROM keyed
ORDER BY match_group, src;

Step-by-step trace.

1. Standardize: [e-mail] and [e-mail] both normalise to [e-mail]; names strip to jonsmith / jsmith.
2. CRM C1 and ERP E1 share email_norm = [e-mail] → same match_key → same group.
3. WEB W1 has a null email, so it falls back to jsmith|94107. That does not equal jonsmith|94107, so deterministic matching misses it — a real limitation we fix with fuzzy matching next.
4. CRM C2 has a unique email → its own group.

Output:

src	key	match_group	matched on
CRM	C1	1	email
ERP	E1	1	email
WEB	W1	2	name+zip fallback (missed C1/E1)
CRM	C2	3	email

Rule of thumb. Deterministic-match on the strongest normalized identifier first with a composite fallback; it is fast and explainable, but expect it to miss records with null strong keys — that gap is exactly what probabilistic matching fills.

Probabilistic (fuzzy) matching with blocking — a worked teaching example

Detailed explanation. For the records deterministic matching misses, you score similarity across several fields and match above a threshold — but only within a block, so you never compute the O(N²) all-pairs product. Choose a blocking key that co-locates likely matches (here, zip), then within each block compute a weighted similarity (name edit-distance similarity, plus agreement bonuses) and compare against a high auto-merge threshold and a lower review threshold.

• Blocking key — zip (or ZIP + first name initial) so only same-area records are compared.
• Similarity — a name similarity in 0,1 as the core signal.
• Auto-merge ≥ 0.90; review 0.75–0.90; reject < 0.75 — the three-way band that keeps precision high.

Question. Within a ZIP block, decide whether W1 = "J. Smith" (null email) matches C1 = "Jon Smith" using a name-similarity threshold.

Input.

pair	left name_norm	right name_norm	same zip?	name_sim
C1–W1	jonsmith	jsmith	yes (94107)	0.78
C1–E1	jonsmith	jonathansmith	yes (94107)	0.71
C1–C2	jonsmith	miawong	no	—

Code.

WITH blocked AS (           -- only compare pairs inside the same zip block
  SELECT a.key AS lkey, b.key AS rkey, a.zip,
         a.name_norm AS lname, b.name_norm AS rname
  FROM std a
  JOIN std b
    ON a.zip = b.zip           -- blocking predicate: shrink the comparison space
   AND a.key < b.key           -- each unordered pair once
),
scored AS (
  SELECT lkey, rkey,
         -- normalized similarity in [0,1]; higher = more similar
         1.0 - (levenshtein(lname, rname)::float
                / GREATEST(LENGTH(lname), LENGTH(rname))) AS name_sim
  FROM blocked
)
SELECT lkey, rkey, ROUND(name_sim, 2) AS name_sim,
       CASE WHEN name_sim >= 0.90 THEN 'auto-merge'
            WHEN name_sim >= 0.75 THEN 'review'
            ELSE 'reject' END                            AS decision
FROM scored
ORDER BY name_sim DESC;

Step-by-step trace.

1. Blocking on zip means C2 (ZIP 10001) is never compared to the 94107 rows — the candidate set shrinks to same-ZIP pairs only.
2. Within block 94107, the pair C1–W1 scores name_sim = 0.78 → falls in the review band (0.75–0.90), so it is routed to a steward, not auto-merged.
3. The pair C1–E1 scores 0.71 on raw name distance alone → below 0.75 → would reject on name alone, which is why real systems add an email/phone agreement bonus (here email already matched deterministically, so E1 is already grouped).
4. No pair clears 0.90, so nothing auto-merges purely on fuzzy name — the system correctly defers the uncertain calls to human review.

Output:

pair	name_sim	decision
C1–W1	0.78	review (steward confirms)
C1–E1	0.71	reject on name (but already email-matched)
C1–C2	—	never compared (different block)

Rule of thumb. Block to make the comparison affordable, score similarity within blocks, and use a three-way threshold (auto-merge / review / reject) — never a single cutoff — so uncertain matches go to a steward instead of silently fusing two people.

Interview scenario on identity resolution

You have run deterministic and probabilistic matching and produced a set of high-confidence pairwise matches: (A,B), (B,C), and separately (D,E). A and C never scored a direct match. The naive pipeline assigns a master id per pair, so A, B, C are landing under two different master ids. Design the step that assigns exactly one master id per real entity.

Solution Using connected components to close pairwise matches into match groups

Answer choices (as an interviewer would frame them).

• A. Assign a master id per matched pair and accept that A and C may differ.
• B. Treat matches as graph edges and assign one master id per connected component (transitive closure).
• C. Only keep pairs that all mutually matched (require A–B, B–C, and A–C) before grouping.
• D. Pick the row with the smallest key in each pair as the master and drop the rest.

Code.

-- Iterative connected components: propagate the minimum key across match edges
-- edges(a, b): one row per confirmed pairwise match (both directions inserted)
WITH RECURSIVE cc AS (
  SELECT node, node AS comp        -- seed: every node is its own component
  FROM (SELECT a AS node FROM edges UNION SELECT b FROM edges) n
  UNION
  SELECT e.b AS node, LEAST(cc.comp, e.b) AS comp   -- pull the min label across each edge
  FROM cc
  JOIN edges e ON e.a = cc.node
)
SELECT node                       AS source_row,
       MIN(comp) OVER (PARTITION BY node) AS master_id  -- stable id = min node in the component
FROM cc;

Step-by-step trace.

1. Constraint: matching is transitive — A–B and B–C imply A, B, C are one entity even without a direct A–C link.
2. A assigns ids per pair, so A–B and B–C become two ids and split one entity — reject: it ignores transitivity.
3. C demands a fully-connected clique (A–C too), which throws away legitimate transitive matches and lowers recall — reject.
4. D arbitrarily keeps one row per pair and deletes the others, destroying the other sources' data before survivorship even runs — reject.
5. B models matches as graph edges and computes connected components; {A,B,C} collapse to one component (one master id) and {D,E} to another — every real entity gets exactly one id, and the min-node convention makes the id stable across re-runs.

Output:

source_row	component	master_id
A	{A,B,C}	M-A
B	{A,B,C}	M-A
C	{A,B,C}	M-A
D	{D,E}	M-D
E	{D,E}	M-D

Why this works — concept by concept:

• Transitive closure — matching is an equivalence relation, so pairwise links must be closed into groups; connected components is exactly that closure, guaranteeing A–B–C land together.
• Graph model of matches — treating each confirmed match as an edge turns "who is the same" into a standard connected-components problem you can solve in SQL, Spark GraphFrames, or union-find.
• Stable master id from the component — using the minimum node (or a hash of the sorted component) as the id makes re-runs idempotent: the same group always yields the same master id.
• Precision over recall on the merge — you only add an edge for a confirmed match, because a single wrong edge can chain two unrelated entities into one giant erroneous component (an over-merge).
• Cost — components over M confirmed edges is near-linear with union-find (~O(M·α)); the recursive-SQL version is heavier, so at scale you push this to a graph engine, but the semantics are identical.

SQL
Topic — joins
Join, self-join and matching problems

Practice →

ETL Topic — data-validation Data-quality and validation problems

Practice →

---

3. Golden record construction

The golden record is one surviving row per entity, assembled through an xref table that pins every source key to a stable master id

The invariant: the golden record is never "pick a winning row" — it is "pick a winning value per attribute," assembled from all contributing sources that the xref table maps to the same master id, so a customer's best name might come from CRM, best phone from ERP, and best address from the billing system, all fused into one row that no single source ever held. The cross-reference (xref) table is the load-bearing artifact: it links (source_system, source_key) → master_id and makes the whole process durable and idempotent.

The cross-reference (xref) table — the spine of MDM. Everything hangs off this mapping.

• Grain — one row per (source_system, source_key); its value is the assigned master_id.
• Stable master id — a durable surrogate (a hash of the component, or an assigned surrogate that never changes once issued) so downstream foreign keys don't break when you re-run matching.
• Idempotency — because the xref remembers past assignments, re-running the pipeline reuses existing master ids instead of minting new ones; new source rows get attached to an existing entity or start a new one.
• Lineage — keeping the mapping (and, ideally, which source won each attribute) means you can always answer "where did this golden value come from?"

Assembling the surviving record — attribute by attribute, not row by row. Once every source row carries a master_id, you GROUP BY master_id and, for each attribute, apply that attribute's survivorship rule (next section) to choose one value. The result is a best-of-breed row: each field independently sourced from whichever contributor is most trustworthy/recent/complete for that field.

Durable keys, lineage, and idempotent re-merges — the properties seniors check for.

• Never reuse a retired master id — if two entities were wrongly merged and later split, the split-off entity gets a new id; the old id is not recycled.
• Survivorship metadata — store, per attribute, which source_system supplied the surviving value; it is your audit trail and your steward's debugging aid.
• Re-merge safety — the pipeline must be safe to run repeatedly; the xref plus deterministic survivorship rules make the output a pure function of the inputs.

Common trap answers.

• Picking one "survivor row" wholesale — you lose the best phone from source B because you kept source A's row; golden records survive per attribute.
• Regenerating master ids every run — breaks every downstream foreign key that referenced yesterday's id; ids must be stable.
• Storing no lineage — you can never explain or debug a golden value, and stewards can't tell which source to fix.
• Merging without an xref — you conflate "the match decision" with "the published row" and lose idempotency.

The xref table and a stable master id — a worked teaching example

Detailed explanation. The xref is where the match groups from section 2 become a durable, queryable mapping. You take the connected-component output (source_row → master_id) and persist it as one row per (source_system, source_key), deriving the master id from the content of the component (a hash of the sorted source keys) so it is stable and reproducible rather than an auto-increment that changes every run.

Question. Turn three matched source rows into an xref table with a stable, reproducible master id.

Input.

source_system	source_key	match_group
CRM	C-88	g1
ERP	E-13	g1
WEB	W-42	g1

Code.

-- Stable master id = deterministic hash of the SORTED set of source keys in the group.
-- Re-running with the same members always yields the same id (idempotent).
CREATE OR REPLACE TABLE xref_customer AS
WITH members AS (
  SELECT match_group,
         source_system, source_key,
         source_system || ':' || source_key AS src_ref
  FROM match_output
),
group_id AS (
  SELECT match_group,
         'M-' || SUBSTR(
           MD5(STRING_AGG(src_ref, '|' ORDER BY src_ref)), 1, 10
         ) AS master_id                       -- content-addressed, order-independent
  FROM members
  GROUP BY match_group
)
SELECT m.source_system, m.source_key, g.master_id
FROM members m
JOIN group_id g USING (match_group);

Step-by-step trace.

1. Each source row is expressed as source_system:source_key (e.g. CRM:C-88).
2. Per group, the refs are sorted and concatenated deterministically (CRM:C-88|ERP:E-13|WEB:W-42) — sorting makes the id independent of input order.
3. Hashing that string yields a stable master_id (M-xxxxxxxxxx); the same three members always produce the same id, so re-runs are idempotent.
4. The final xref has one row per source key, all pointing at the shared master id.

Output:

source_system	source_key	master_id
CRM	C-88	M-a1b2c3d4e5
ERP	E-13	M-a1b2c3d4e5
WEB	W-42	M-a1b2c3d4e5

Rule of thumb. Derive the master id from the content of the match group (a hash of the sorted source keys), not an auto-increment; content-addressing makes the id stable and the whole merge idempotent across re-runs.

Pivoting contributing sources into one golden skeleton — a worked teaching example

Detailed explanation. With the xref in place, building the golden record is a JOIN from xref back to the standardized source rows, then a GROUP BY master_id that assembles one row per entity. Before survivorship rules choose the winning value, it helps to see the "skeleton": all contributing values for each attribute collected under one master id, with the metadata (source, recency) that survivorship will use to pick.

Question. For master id M-1001 with three contributing source rows, assemble the per-attribute candidate set that survivorship will choose from.

Input.

src	master_id	name	phone	address	updated_at
CRM	M-1001	Jonathan Smith	(null)	12 Oak St	2026-08-10
ERP	M-1001	J Smith	[phone]	(null)	2026-07-01
BILL	M-1001	Jon Smith	[phone]	12 Oak Street, SF	2026-08-12

Code.

-- Golden "skeleton": collect every candidate value per attribute under the master id,
-- carrying source + recency so survivorship rules (next section) can choose.
SELECT
  x.master_id,
  ARRAY_AGG(STRUCT(s.source_system, s.name    AS val, s.updated_at) ) AS name_candidates,
  ARRAY_AGG(STRUCT(s.source_system, s.phone   AS val, s.updated_at) ) AS phone_candidates,
  ARRAY_AGG(STRUCT(s.source_system, s.address AS val, s.updated_at) ) AS address_candidates
FROM xref_customer x
JOIN stg_customer_std s
  ON s.source_system = x.source_system
 AND s.source_key    = x.source_key
WHERE x.master_id = 'M-1001'
GROUP BY x.master_id;

Step-by-step trace.

1. The xref join pulls all three contributing rows for M-1001 back together under one master id.
2. GROUP BY master_id with ARRAY_AGG(STRUCT(...)) collects, per attribute, the set of candidate values along with their source and updated_at.
3. name now has three candidates (Jonathan/J/Jon), phone has two non-null candidates (ERP, BILL) and one null (CRM), address has two non-null candidates.
4. Nothing has been chosen yet — this skeleton is the input to survivorship, which applies one rule per attribute to pick the winner.

Output:

attribute	candidate values (source)
name	Jonathan Smith (CRM), J Smith (ERP), Jon Smith (BILL)
phone	[phone] (ERP), [phone] (BILL)
address	12 Oak St (CRM), 12 Oak Street, SF (BILL)

Rule of thumb. Build the golden record by joining xref back to sources and collecting all candidate values per attribute first; only then apply survivorship — assembling the skeleton separately keeps merge and survivorship independently testable.

Interview scenario on golden-record construction

Analysts complain that your "customer 360" view sometimes shows a customer with a missing phone even though one of the source systems clearly has it, and other times shows yesterday's stale email. You currently build the golden record by picking, per master id, the single most-recently-updated source row and using all of its fields. Redesign the construction so each field independently uses the best available value.

Solution Using xref + attribute-level survivorship join

Answer choices.

• A. Keep picking one winning row per master id (most-recent updated_at), all fields from that row.
• B. Build the golden record per attribute: join xref → sources, then choose each field independently (non-null + rule) via GROUP BY master_id.
• C. Concatenate all source values for every field so nothing is lost.
• D. Let analysts pick the source per query at read time.

Code.

-- Attribute-level survivorship: each field chosen independently under the master id.
SELECT
  x.master_id,
  -- name: most-complete (longest non-null), tie broken by most-recent
  (ARRAY_AGG(s.name ORDER BY LENGTH(s.name) DESC NULLS LAST, s.updated_at DESC))[SAFE_OFFSET(0)]  AS name,
  -- phone: first non-null by most-recent
  (ARRAY_AGG(s.phone IGNORE NULLS ORDER BY s.updated_at DESC))[SAFE_OFFSET(0)]                     AS phone,
  -- address: most-recent non-null
  (ARRAY_AGG(s.address IGNORE NULLS ORDER BY s.updated_at DESC))[SAFE_OFFSET(0)]                   AS address
FROM xref_customer x
JOIN stg_customer_std s
  ON s.source_system = x.source_system AND s.source_key = x.source_key
GROUP BY x.master_id;

Step-by-step trace.

1. The complaint has two causes: (a) picking one whole row means a null phone in the "winning" row hides a real phone in another row; (b) "most-recent row" can be recent for one field but stale for another.
2. A is the current design and reproduces both bugs — reject.
3. C concatenates values, producing garbage like "12 Oak St / 12 Oak Street, SF" that no consumer can use — reject.
4. D pushes the merge to read time, so every analyst reinvents survivorship inconsistently — reject.
5. B joins xref to all contributing source rows and chooses each field independently: phone uses the most-recent non-null (so a null in one source never hides a value in another), name uses most-complete, address uses most-recent non-null — the golden row is best-of-breed per attribute.

Output:

master_id	name	phone	address
M-1001	Jonathan Smith	[phone]	12 Oak Street, SF

Why this works — concept by concept:

• Attribute-level survivorship — choosing per field, not per row, is the entire point of a golden record; it is why the merged row can be better than any single source.
• Non-null preference — IGNORE NULLS (or COALESCE ordering) guarantees a populated value in one source is never masked by a null in the "winning" row.
• Per-field ordering — each attribute gets the rule that fits it (most-complete for name, most-recent for phone/address), so recency in one field never drags a stale value into another.
• xref as the join spine — grouping by the stable master_id from the xref keeps construction idempotent and independent of the matching step.
• Cost — one join and a grouped aggregation per master id, O(rows) with the xref already built; the heavy lifting (matching) happened upstream, so publishing the golden record is cheap.

ETL
Topic — data-transformation
Data transformation and merge problems

Practice →

Design Topic — dimensional-modeling Dimensional modeling and master-entity problems

Practice →

---

4. Survivorship rules and data stewardship

Survivorship is the policy layer that decides, for every attribute, which value wins — with a human steward's override sitting above all automated rules

The invariant: survivorship rules are applied per attribute, each attribute gets the rule family that fits its semantics (recency, source trust, completeness, or aggregation), rules are ranked into a precedence order so ties resolve deterministically, and a manual data-steward override always outranks every automated rule. "Which value survives?" is never a single global policy; it is a per-attribute decision governed by a ranked set of rules with a human at the top.

The four survivorship rule families — pick the one whose semantics match the attribute.

• Most-recent — the value with the newest updated_at/source_ts wins. Right for volatile attributes (phone, address, marketing consent) where newer is truer.
• Most-trusted-source (source priority) — a fixed ranking of source systems (e.g. Billing > CRM > Web) decides; the highest-priority source that has a non-null value wins. Right when one system is authoritative for a field (Billing owns the legal name).
• Most-complete — prefer the non-null, longest, or most-detailed value (full address over partial). Right when sources differ mostly in how much they populate.
• Aggregation — compute across sources: MAX(lifetime_value), SUM(order_count), MIN(first_seen_date), boolean OR of an opt-out flag. Right when the golden value is a roll-up, not a single source's value.

Conflict resolution and precedence — rank the rules so ties break deterministically. A single attribute often needs a chain: "steward override, else highest-priority source, else most-recent, else most-complete." You encode that as an ordered set of tie-breakers in the ORDER BY of a ranking window (or a COALESCE cascade). Without an explicit order, two equally-recent values from two sources make the result non-deterministic — a bug that shows up as the golden record flickering between values on re-runs.

Data stewardship — the human override that outranks the machine. No rule set is perfect. A data steward is the business owner who reviews low-confidence matches and manually corrects golden values (fixing a wrong merge, pinning the correct legal name). Those overrides are stored as an authoritative source with the highest priority, so the automated pipeline reads them first and never clobbers a human decision on the next run. Stewardship also feeds back: a steward's "these two are NOT the same" becomes a rule input that stops the match from recurring.

• Overrides are data, not code — stored in an overrides table keyed by (master_id, attribute), applied with top precedence.
• Overrides are sticky — the pipeline must re-apply them every run; a steward should never have to redo a correction.
• Stewardship closes the loop — match rejections and confirmations feed the next matching run (allow-lists / block-lists).

Common trap answers.

• One global survivorship rule for all attributes — "always most-recent" makes a typo'd recent name beat a correct older one; rules are per attribute.
• No precedence / tie-breaker — equally-recent values yield non-deterministic golden records that change across runs.
• Overrides that get overwritten — if the next pipeline run ignores steward decisions, humans lose trust and stop stewarding.
• Aggregation done as most-recent — lifetime value is a SUM/MAX across sources, not the newest single source's number.

Source-priority survivorship via ranking — a worked teaching example

Detailed explanation. The most-trusted-source rule is cleanest as a ranked window: attach a numeric priority to each source system, then per (master_id, attribute) pick the non-null value from the highest-priority source. ROW_NUMBER() over a partition ordered by priority (and a recency tie-breaker) gives you exactly one surviving value per attribute, deterministically.

Question. Choose the surviving legal_name for one master id where Billing is the authoritative source, falling back to CRM then Web, ignoring nulls.

Input.

master_id	source	source_priority	legal_name	updated_at
M-1001	WEB	3	jon smith	2026-08-12
M-1001	CRM	2	Jonathan Smith	2026-08-10
M-1001	BILL	1	Jonathan A. Smith	2026-07-01

Code.

WITH ranked AS (
  SELECT
    master_id, source, legal_name, updated_at,
    ROW_NUMBER() OVER (
      PARTITION BY master_id
      ORDER BY source_priority ASC,      -- 1 = most trusted wins
               updated_at   DESC          -- tie-break: newer within same priority
    ) AS rn
  FROM contrib_values
  WHERE legal_name IS NOT NULL            -- never let a null "win"
)
SELECT master_id, legal_name AS surviving_legal_name
FROM ranked
WHERE rn = 1;

Step-by-step trace.

1. Each contributing row carries a source_priority (Billing=1, CRM=2, Web=3).
2. The WHERE legal_name IS NOT NULL filter drops null candidates so they can never rank first.
3. ROW_NUMBER() orders by priority ascending, so Billing (1) ranks above CRM (2) and Web (3); the recency tie-breaker only matters within a single priority.
4. rn = 1 selects Billing's Jonathan A. Smith — the authoritative legal name — even though Web's value is newer, because source trust outranks recency for this attribute.

Output:

master_id	surviving_legal_name
M-1001	Jonathan A. Smith

Rule of thumb. Encode most-trusted-source as ROW_NUMBER() partitioned by the entity and ordered by source_priority (with a recency tie-breaker), filtering nulls first — it gives one deterministic surviving value per attribute and makes the authority order explicit and auditable.

Most-recent + most-complete + steward override — a worked teaching example

Detailed explanation. Real attributes need a chain of rules, and a steward override has to sit on top. The clean encoding is a precedence cascade: first check the overrides table (steward), else apply the automated rule (here most-recent non-null, tie-broken by most-complete). A COALESCE of a steward-value subquery with a ranked automated value expresses "human first, machine second" directly.

• Rung 1 (top): steward override — an authoritative manual value from the overrides table.
• Rung 2: most-recent non-null — newest updated_at among populated values.
• Rung 3 (tie-break): most-complete — longer/more-detailed value wins ties.

Question. Choose the surviving address: use the steward's override if one exists for (master_id, 'address'), otherwise the most-recent non-null address, breaking ties by longest.

Input.

master_id	source	address	updated_at
M-1001	CRM	12 Oak St	2026-08-10
M-1001	BILL	12 Oak Street, San Francisco	2026-08-12

Steward overrides table:

master_id	attribute	value
M-1001	address	12 Oak Street, San Francisco, CA 94107

Code.

WITH automated AS (         -- rung 2+3: most-recent non-null, longest breaks ties
  SELECT master_id, address,
         ROW_NUMBER() OVER (
           PARTITION BY master_id
           ORDER BY updated_at DESC, LENGTH(address) DESC
         ) AS rn
  FROM contrib_values
  WHERE address IS NOT NULL
),
auto_pick AS (
  SELECT master_id, address FROM automated WHERE rn = 1
)
SELECT
  a.master_id,
  COALESCE(o.value, a.address) AS surviving_address,   -- rung 1: steward override wins
  CASE WHEN o.value IS NOT NULL THEN 'steward'
       ELSE 'most-recent' END AS decided_by
FROM auto_pick a
LEFT JOIN overrides o
  ON o.master_id = a.master_id AND o.attribute = 'address';

Step-by-step trace.

1. The automated CTE ranks the two non-null addresses: Billing's is newer (2026-08-12) and longer, so it wins the automated pick.
2. The LEFT JOIN overrides looks for a steward decision on (M-1001, 'address') — one exists.
3. COALESCE(o.value, a.address) puts the steward value first, so the human-verified full address (with ZIP) survives, outranking the automated most-recent choice.
4. decided_by records that the value came from the steward — lineage that proves a human, not the machine, chose it.

Output:

master_id	surviving_address	decided_by
M-1001	12 Oak Street, San Francisco, CA 94107	steward

Rule of thumb. Encode survivorship as an explicit precedence cascade with the steward override at the top (COALESCE(steward, automated_rule)), and record decided_by for lineage — human corrections must survive every re-run, never get overwritten by the automated rule.

Interview scenario on survivorship conflict resolution

Your golden customer table has a boolean marketing_opt_out and a numeric lifetime_value. Two sources disagree: one has opt_out = true, the other false; and each source only knows the spend it processed. A junior engineer applied "most-recent source wins" to both fields. Compliance is furious that opted-out customers are getting emailed, and finance says lifetime value is understated. Fix the survivorship for both attributes.

Solution Using per-attribute rule families — boolean OR for consent, SUM for the roll-up

Answer choices.

• A. Keep most-recent-source-wins for both fields.
• B. Consent uses a safety-biased boolean OR (any opt-out ⇒ opted out); lifetime value uses an aggregation SUM across sources.
• C. Use highest source priority for both fields.
• D. Let the steward manually set both values for every customer.

Code.

SELECT
  master_id,
  -- consent: if ANY source says opt-out, the customer is opted out (fail safe)
  BOOL_OR(marketing_opt_out)                    AS marketing_opt_out,
  -- lifetime value: a roll-up across sources, not one source's number
  SUM(source_lifetime_value)                    AS lifetime_value,
  MIN(first_seen_date)                          AS first_seen_date   -- earliest wins
FROM contrib_values
GROUP BY master_id;

Step-by-step trace.

1. The two attributes have different semantics, so a single rule cannot be right for both — that is the whole bug.
2. A (most-recent) can flip a true opt-out to false because a later row said false, and it discards the other source's spend — reject on both compliance and finance.
3. C (source priority) still picks one source's spend, understating lifetime value, and could pick a source that says opt_out=false — reject.
4. D (manual for every customer) does not scale and misuses stewardship, which is for exceptions, not bulk policy — reject.
5. B matches each attribute to its rule family: consent is safety-critical so BOOL_OR makes any opt-out win; lifetime value is additive so SUM rolls it up across sources; first_seen_date uses MIN. Each field now uses the aggregation its meaning demands.

Output:

master_id	marketing_opt_out	lifetime_value	first_seen_date
M-1001	true	4,820.00	2019-03-11

Why this works — concept by concept:

• Per-attribute rule families — consent, spend, and first-seen each need a different survivorship rule; forcing one global rule violates at least one field's semantics.
• Safety-biased boolean OR — for consent/compliance flags, the correct default is the conservative value (BOOL_OR of opt-out), so a stale false can never re-enable emailing a customer who opted out anywhere.
• Aggregation for roll-ups — lifetime value and order counts are SUMs across sources and first-seen is a MIN; picking a single source's number is categorically wrong for additive measures.
• Stewardship stays for exceptions — bulk policy belongs in rules, not manual edits; reserving the steward for genuine conflicts keeps the human workload sane.
• Cost — a single grouped aggregation over the contributing rows, O(rows) per master id; correctness came from choosing the right aggregate, not from more compute.

SQL
Topic — slowly-changing-data
Slowly-changing data and survivorship problems

Practice →

Analytics Topic — data-aggregation Aggregation and roll-up survivorship problems

Practice →

---

5. MDM architecture styles and interview signals

The four MDM styles differ on one axis — who owns the write — from a thin virtual registry to a fully authored central hub

The invariant: the four MDM implementation styles — registry, consolidation, coexistence, and centralized — sit on a spectrum from "link but don't copy" to "author the master in the hub," and you pick by answering one question: who owns the write to the master entity, and does the golden record need to flow back to the source systems? Registry only links (xref, read-through), consolidation materializes a downstream golden record for analytics, coexistence adds write-back to sources, and centralized makes the hub the system of record.

The four styles, from thinnest to heaviest.

• Registry — the hub stores only the xref (matches + master ids); it does not copy source data. Golden values are computed read-through on demand by federating to sources. Lowest footprint and lowest disruption; no single stored golden record, and read-time federation can be slow. Good first step and good for "we just need to know who's the same."
• Consolidation — source data is pulled into the hub and a materialized golden record is built downstream (typically in the warehouse/lakehouse) for analytics and reporting. Sources are unchanged; the golden record is read-only and not pushed back. This is the most common data-engineering pattern — it is a customer-360 / conformed-dimension build.
• Coexistence — like consolidation, but the golden record is written back to the source systems so operational apps also see the cleaned master. Higher value (everyone sees the truth) but higher complexity: bidirectional sync, conflict handling, and loop-prevention.
• Centralized (transaction) — the MDM hub becomes the system of record; master data is authored there and published outward. Cleanest governance and a true single source of truth, but the biggest organisational change — source apps give up ownership of the entity.

Batch vs real-time — how fresh does the master need to be?

• Batch MDM — nightly/hourly match-merge-survive over staged data; simplest and what most consolidation builds do.
• Real-time / streaming MDM — incoming records are matched and merged as they arrive (via an API or a stream processor), so the golden record is current within seconds. Needed when operational apps read the master live; it constrains matching to incremental, low-latency techniques.

Interview signals — what a senior answer sounds like. When MDM comes up in a data-modeling or system-design round, interviewers listen for a few specific reflexes:

• You separate match from survive from publish — three stages, three failure modes, three test surfaces.
• You insist on a stable master id and an xref — and can explain idempotent re-merges.
• You choose survivorship per attribute and can name the rule families (recency, source priority, completeness, aggregation).
• You put stewardship above automation — human overrides are sticky and outrank rules.
• You pick a style by "who owns the write" — and don't jump to a centralized hub when consolidation suffices.
• You think about lineage — "which source won this field" is stored, not guessed.

Common trap answers.

• "Just build one big table" — no xref, no per-attribute survivorship, no idempotency; it works in a demo and rots in production.
• Reaching for centralized/coexistence first — write-back and system-of-record are the heaviest options; most needs are met by consolidation.
• Real-time everywhere — streaming MDM is costly; use it only where operational apps read the master live.
• No stewardship plan — a fully automated MDM with no human review over-merges silently and no one can fix it.

Registry vs consolidation — a worked teaching example

Detailed explanation. The most common architecture decision a data engineer actually makes is registry versus consolidation, because it's the difference between "store only the xref and compute golden values on read" and "materialize a golden table downstream." Registry keeps a live federated view (no copied data, always current, but read-time joins across sources); consolidation builds a physical golden table (fast reads, point-in-time stable, but as fresh as its last build). The choice is a freshness-vs-read-performance trade.

• Registry — thin: store xref; golden value = a view that joins sources at read time.
• Consolidation — thick: run survivorship on a schedule and write a physical golden_customer table.

Question. Contrast the registry read-through view with the consolidation materialized build for the same golden email.

Input.

aspect	registry	consolidation
stored golden data	none (xref only)	physical golden table
freshness	live	as of last build
read speed	slower (federate)	fast (pre-built)
source disruption	none	none

Code.

-- REGISTRY: golden value computed read-through from the xref + live sources (a VIEW)
CREATE VIEW golden_customer_registry AS
SELECT x.master_id,
       (ARRAY_AGG(s.email IGNORE NULLS ORDER BY s.updated_at DESC))[SAFE_OFFSET(0)] AS email
FROM xref_customer x
JOIN stg_customer_std s USING (source_system, source_key)
GROUP BY x.master_id;          -- recomputed on every query -> always current, slower

-- CONSOLIDATION: same survivorship, but MATERIALIZED on a schedule (a TABLE)
CREATE TABLE golden_customer AS
SELECT * FROM golden_customer_registry;   -- rebuilt nightly -> fast reads, point-in-time

Step-by-step trace.

1. Both use identical survivorship SQL — the only difference is materialization.
2. The registry VIEW recomputes the golden email on every read by federating to stg_customer_std, so it is always live but pays the join cost per query.
3. The consolidation TABLE runs that same logic on a schedule and stores the result, so reads are cheap but the data is only as fresh as the last build.
4. You pick registry when freshness and zero-copy matter most, consolidation when read performance and stability matter most — most analytics stacks choose consolidation.

Output:

style	golden email freshness	read cost
registry (view)	live	high (federate per query)
consolidation (table)	last build	low (pre-materialized)

Rule of thumb. Use registry (a read-through view over the xref) when you need zero-copy and live freshness; use consolidation (a materialized golden table) when you need fast, stable analytical reads — the survivorship logic is identical, only the materialization changes.

Coexistence write-back and centralized authoring — a worked teaching example

Detailed explanation. The heavier styles add write direction. Coexistence pushes the golden record back to the source systems so operational apps see cleaned data — which means you must prevent update loops (a write-back that re-triggers matching) and handle source conflicts. Centralized goes further: the hub authors the entity, so creates/updates happen in the hub and flow out. Expressing these as pseudo-config makes the loop-prevention and system-of-record flags explicit.

• Coexistence — golden record flows both ways; needs writeback: true, source-of-record still the app, loop guard on.
• Centralized — hub is system_of_record; sources become read replicas of the master.

Question. Configure coexistence write-back for the customer domain without creating an update loop, and contrast it with a centralized config.

Input.

requirement	coexistence	centralized
who authors the entity	source apps	the hub
golden record direction	hub → sources (write-back)	hub → sources (publish)
loop risk	high (write-back re-ingests)	low (one-way author)

Code.

# Coexistence: consolidate, then write the golden record back to sources
mdm_style: coexistence
domain: customer
survivorship: per_attribute            # rules from section 4
writeback:
  enabled: true
  targets: [crm, erp]                  # push cleaned golden values back
  loop_guard:
    tag_writes_with: "mdm_writeback"    # mark hub-originated writes
    ignore_on_ingest: "mdm_writeback"   # skip re-matching our own write-backs
conflict_policy: source_priority        # if an app edited after write-back, priority decides

---
# Centralized: the hub is the system of record; sources subscribe
mdm_style: centralized
domain: customer
system_of_record: mdm_hub               # entity is AUTHORED here
publish:
  mode: outbound_only                   # hub -> sources, never sources -> hub authoring
  subscribers: [crm, erp, analytics]

Step-by-step trace.

1. Coexistence enables writeback to CRM and ERP so operational apps see the cleaned golden values, not just analytics.
2. The loop_guard tags hub-originated writes and ignores them on ingest, so a write-back does not get re-matched as if it were a fresh source change — the loop is broken.
3. conflict_policy: source_priority decides what happens if an app edits a field after a write-back — the survivorship priority still governs.
4. The centralized config flips ownership: the hub is system_of_record, publishing is outbound_only, and sources subscribe — no source authors the entity anymore.

Output:

style	write direction	loop risk handled by
coexistence	hub ↔ sources (write-back)	loop_guard tag + ignore-on-ingest
centralized	hub → sources (author + publish)	one-way authoring (no inbound author)

Rule of thumb. Only add write-back (coexistence) when operational apps must see the golden record, and always ship a loop guard; reserve centralized for when the org agrees the hub is the system of record — both are heavier than the consolidation most teams actually need.

Interview scenario on architecture choice

A retailer wants a trustworthy customer view: analysts need a clean customer-360 for reporting now, and within a year the call-center app should also show the cleaned, deduplicated customer so agents stop seeing three records for one person. Minimise disruption to the source systems today, but leave a path to operational adoption. Which MDM architecture do you propose, and in what order?

Solution Using consolidation now, coexistence later for the customer domain

Answer choices.

• A. Go straight to a centralized hub as the system of record for customers.
• B. Start with consolidation (materialized golden record for analytics), then evolve to coexistence (write-back to the call-center app) once matching is trusted.
• C. Registry only — never materialize anything.
• D. Build separate one-off dedupe scripts per team with no xref.

Code.

Sequencing:
Phase 1 (now)   consolidation: xref + survivorship -> materialized golden_customer   -> analytics 360
Phase 2 (later) coexistence:   add write-back of golden_customer to the call-center app (loop-guarded)
Never (yet)     centralized:   only if the org agrees the hub becomes system of record

Elimination:
A  centralized first  -> biggest org change, sources lose ownership on day one   [reject: disruption]
C  registry only      -> no materialized 360, slow federated reads for analytics [reject: analytics need]
D  per-team scripts   -> no xref, no idempotency, duplicate/ conflicting logic    [reject: no single truth]
B  consolidation -> coexistence                                                   [ACCEPT]

Step-by-step trace.

1. Constraints: "clean 360 for analytics now", "operational adoption within a year", "minimise source disruption today", "leave a path forward".
2. A makes the hub the system of record immediately — maximum disruption, sources give up ownership on day one — reject against "minimise disruption today".
3. C (registry only) never materializes a golden record, so analytics pay federated read-time joins and there's no stable 360 — reject against the analytics-now need.
4. D hand-rolls per-team dedupe with no xref — no idempotency, no single source of truth, conflicting logic — reject.
5. B delivers the materialized golden record for analytics now (consolidation, zero source disruption), then adds loop-guarded write-back to the call-center app later (coexistence) — matching the "now, then later, minimal disruption" shape exactly, and centralized stays available only if the org later decides the hub is the system of record.

Output:

Phase	Style	Delivers
Now	Consolidation	Materialized customer-360 for analytics, no source changes
Later	Coexistence	Write-back so the call-center app sees the golden record
If agreed	Centralized	Hub as system of record (biggest change)

Why this works — concept by concept:

• Pick the style by who owns the write — analytics only reads, so consolidation suffices now; operational adoption needs write-back, which is coexistence — the requirement maps straight to the style.
• Least-disruption-first sequencing — consolidation changes nothing in the sources, so you earn trust in the matching before you ever write back, de-risking the harder phase.
• The xref carries across phases — the same stable master ids and survivorship rules built for consolidation are exactly what coexistence writes back, so phase 2 reuses phase 1.
• Centralized is opt-in, not default — making the hub the system of record is an org decision, not a technical default; keeping it as a later option avoids over-engineering.
• Cost — consolidation is a scheduled batch build (cheap, low-risk); coexistence adds bidirectional sync and loop-guarding (more expensive), so deferring it until the matching is trusted spends effort only when it pays off.

Design
Topic — design
MDM and data-system design problems

Practice →

Design
Course — data modeling
Data modeling for data engineering interviews

Practice →

---

Cheat sheet — MDM recipes

Data-class decision table (what MDM governs).

Signal	Master	Transactional	Reference
Nature	noun / entity	verb / event	lookup / vocabulary
Volume	moderate	very high	tiny (closed list)
Changes after insert	slowly	never (immutable)	almost never
Referenced by FKs	yes (many)	no (it references)	yes (as a label)
MDM governs it?	yes	no	as reference data
Examples	customer, product, supplier	orders, payments, clicks	country, currency, status

Match-and-merge checklist.

• Standardize first (case, email, phone→E.164, name parts, address) — never match raw values.
• Block to cut O(N²): compare only within a cheap bucket (ZIP + initial, name prefix).
• Deterministic match on the strongest normalized key; composite fallback for nulls.
• Probabilistic match the remainder; three-way band: auto-merge ≥0.90 / review / reject <0.75.
• Close pairwise matches into groups via connected components (transitivity).
• Bias toward precision — a false merge is costlier than a missed match.

Survivorship rule → SQL pattern lookup.

Rule family	When to use	SQL pattern
Most-recent	volatile fields (phone, address, consent)	ROW_NUMBER() … ORDER BY updated_at DESC
Most-trusted-source	one system is authoritative	ROW_NUMBER() … ORDER BY source_priority
Most-complete	sources differ in population	prefer non-null / ORDER BY LENGTH() DESC
Aggregation	roll-up measures	SUM() / MAX() / MIN() / BOOL_OR()
Steward override	human correction	COALESCE(override, automated_rule) (top precedence)

Architecture-style decision line. Analytics only, no source changes → consolidation (materialized golden table). Just need to know who's the same, zero-copy → registry (xref + read-through view). Operational apps must see the master → coexistence (write-back, loop-guarded). Hub authors the entity → centralized (system of record). Default to consolidation; escalate only when write direction demands it.

Golden-record QA checklist.

• Every source row maps to exactly one master_id (no entity split across two ids).
• Master ids are stable across re-runs (content-addressed or persisted surrogate).
• Survivorship chosen per attribute, not per row; nulls never mask real values.
• Steward overrides re-apply every run (sticky) and are recorded in lineage.
• Aggregated fields use SUM/MAX/BOOL_OR, not most-recent.
• Lineage stored: which source won each attribute.

---

Frequently asked questions

What is master data management in simple terms?

Master data management is the practice of taking the core business entities — customers, products, suppliers — that are scattered and duplicated across many systems and consolidating them into one trusted, deduplicated record per entity. Instead of six slightly different versions of "customer X," you get a single golden record every system can agree on. It combines matching (finding records that describe the same thing), merging, and survivorship (choosing the best value per field), and in most organisations it is implemented by data engineers in SQL and pipelines.

What is a golden record and how is it different from a master record?

A golden record is the single, best-of-breed representation of one entity, assembled attribute-by-attribute from every contributing source — its name might come from CRM, its phone from ERP, its address from billing. "Master record" is often used loosely as a synonym, but the important distinction is that a golden record is computed by survivorship across sources, not just one system's copy promoted to authoritative. The goal of master data management is precisely to produce that golden record as the single source of truth.

What are survivorship rules in MDM?

Survivorship rules decide, for each attribute, which value wins when the contributing sources disagree. The common families are most-recent (newest timestamp wins), most-trusted-source (a fixed source-priority ranking decides), most-complete (prefer non-null/most-detailed), and aggregation (SUM, MAX, or a safety-biased boolean OR for roll-ups and flags). Crucially, rules are applied per attribute and ranked into a precedence order, with a human data-stewardship override sitting above every automated rule.

Deterministic vs probabilistic matching — which should I use?

Use both, in that order. Deterministic matching (exact agreement on a normalized key like email or phone) is fast, explainable, and clears the easy majority of duplicates, so run it first. Probabilistic (fuzzy) matching then handles the remainder by scoring similarity across fields and matching above a threshold, with an in-between review band routed to a steward — it catches the records deterministic rules miss (nulls, typos, nicknames) at the cost of tuning and human review.

Do data engineers or a dedicated MDM tool own this?

Both patterns exist, but the engineering work lands on data engineers either way. With a dedicated MDM tool a steward configures rules in a UI, yet engineers still feed it standardized data, reconcile its output into the warehouse, and often reproduce the logic in the lakehouse for analytics. Without a tool — which is most teams — the entire match/merge/survivorship pipeline is a data-engineering build in SQL, so understanding master data management end to end is a core data-engineering skill, not a niche one.

How is MDM different from a data warehouse or a CDP?

A data warehouse stores and serves analytical data; master data management is the upstream discipline that makes the entities inside that warehouse trustworthy — a conformed customer dimension is often a consolidation-style MDM output. A CDP (customer data platform) is a productised, marketing-focused flavour of customer MDM plus activation. In short, MDM is the reusable golden-record capability; the warehouse consumes it, and a CDP is one packaged application of it for the customer domain.

---

Practice on PipeCode

Turn MDM theory into a build you can defend

Guides explain golden records; PipeCode drills build the reflexes an interviewer and a production incident both test — normalizing and matching records, closing pairwise matches into groups, and defending which value survives per attribute under a source-priority-versus-recency conflict. Pipecode.ai is Leetcode for Data Engineering — scenario-first practice on SQL, ETL, and data modeling tuned to the trade-offs master data management actually rewards.

Practice matching problems →
Practice ETL problems →