Merging duplicate master records: what happens to the one that loses

The matching pass found the pair. The survivorship rules picked the values. A steward looked at both suppliers, agreed they are the same company, and clicked Merge. Most of what has been written about deduplication stops there, as if the click were the end of the story.

It is the start of the risky part. Matching and survivorship are decisions. A merge is a transaction, and it has to settle things the two decisions never mention: what happens to the losing identifier, to every row that points at it, to the row itself, and to the audit trail. I have seen merges done as a delete plus a handful of updates more often than I have seen them done properly, and the difference only shows up months later, when someone needs the merge undone or an auditor asks what supplier 1187 used to say.

Merge is the third step, not part of the second

It helps to keep the three apart. Matching answers "are these the same thing?" Survivorship answers "which value wins per field?" Merging carries both answers out against live data: it writes the winning values onto one record, retires the other, moves the references, and leaves a record of having done so.

The first two can be wrong without doing damage. A bad match sits in a review queue. A bad survivorship rule produces a proposal a steward can reject. A bad merge changes rows that other systems depend on, and it does it in one go. That asymmetry is why the merge deserves its own contract.

Five things the click has to settle

Take the two suppliers in the diagram. SUP-0412 survives. SUP-1187 loses. Here is what has to be true afterwards, and what goes wrong when it is not.

The identifier outlives the record

SUP-1187 is a code as much as a row. Someone has copied it into purchase orders, an ERP vendor table, a BI dimension, three spreadsheets and probably an email signature. None of those places will be told about the merge. So the code has to keep resolving. The clean way is an alias entry in the cross-reference table: SUP-1187 now maps to SUP-0412, from this date, because of this merge. Anyone who looks up 1187 lands on the survivor and can see why.

The dirty way is to delete the loser and let the code dangle. It works until the next supplier is created and the code generator hands out SUP-1187 again. Now the old purchase orders point at a different company. That is the same code-reuse trap that makes hard deletes dangerous in master data, and a merge is a delete in disguise.

Every reference, in one transaction

The loser is pointed at by more than you think. In a supplier domain: contracts, contacts, price lists, bank accounts, the parent link on two subsidiaries, and a domain attribute on the Item entity that names the preferred supplier. Each of those is a column somewhere, and each one needs the same statement:

UPDATE dbo.Contract
SET    SupplierId = @Survivor
WHERE  SupplierId = @Loser;
-- and again for Contact, PriceList, BankAccount,
-- Supplier.ParentId, Item.PreferredSupplierId ...

Two rules. First, the list of referencing columns comes from the model, not from memory. The day someone adds an attribute that points at Supplier, the merge must pick it up without a code change, or you get orphans six months later and no idea why. Second, all of the updates run inside one transaction with the row retirement and the audit write. A merge that repoints contracts but fails on price lists is worse than no merge, because it looks finished.

One case needs a decision rather than a statement: a unique constraint on the referencing side. If the survivor and the loser both have a bank account row with the same IBAN, repointing gives the survivor two identical rows and the unique key on (SupplierId, IBAN) throws. Either the merge merges those children too, or it stops and shows the steward. Dropping one without telling anyone is not an option, and neither is letting the transaction die halfway.

The losing row stays, marked

Do not delete it. Set a status of merged, store the survivor's id on it, keep every field value as it was, and hide it from the grid and the integration views by default. Retention rules for merged rows can be stricter than for active data, but the retention clock starts at the merge, not before.

The kept row is what makes two later questions answerable. "What did 1187 say before you merged it?" is an audit question, and the answer is on the row. "Was that merge a mistake?" is the unmerge question, and it needs the row too.

One audit event, not forty

A merge done as ad-hoc SQL leaves a trail of individual field changes on the survivor, a burst of foreign-key updates on the children, and a delete. Each entry is true. Together they say nothing. Six months on, nobody can tell that these forty lines were one action taken by one person for one reason.

The audit trail needs a merge event: both ids, which value won for each field, who approved it, when, and the reason the steward typed. The field-level entries can hang off that event, but the event is the unit. It is also the unit an approval workflow signs off on, and the unit an unmerge reverses.

Unmerge is the test of all of the above

Some merges are wrong. In the 95-4-1 split the four percent that went to review are the ones humans get to look at, and humans also make mistakes. Two branches of a franchise share a VAT number and a trading name and are still separate legal entities with separate contracts. A steward merges them on a Tuesday, and Accounts Payable notices on Friday when a payment goes to the wrong bank account.

Whether that can be undone depends on what the merge recorded. Three things make it possible:

The loser row as it was. Restoring the record is a status change, not a reconstruction, because the values never left.

The list of references that were repointed. Not "everything that points at the survivor", which now includes rows the survivor owned all along plus anything added since. The rows the merge moved, by key, at the time it moved them. C-2201, the Verhoef contact and PL-19 go back to 1187. C-1188 stays with 0412. A contract created on Wednesday stays with 0412 as well, and the steward is told about it, because the merge cannot know which company it was meant for.

The survivor's pre-merge values. Survivorship may have overwritten Net 30 with Net 60. Unmerge should offer to put Net 30 back, and only if that field has not been edited again since. If it has, leave it and say so.

The cost of all this is a small table that stores what each merge moved. The cost of not having it is a database restore, a compare, and a weekend. If a tool tells you it supports unmerge, ask which of the three it keeps. If the answer is "we keep the audit log", that is a reconstruction project with a friendly name.

Telling the systems downstream

The survivor barely changed. Maybe two field values. A consumer that pulls the integration view and syncs modified rows picks those up and moves on. It never learns that SUP-1187 is gone, because the row it used to sync is now hidden and hidden looks the same as unchanged. The BI model keeps both suppliers, forever, and reports their spend as two lines.

The fix is to publish the retirement as an event of its own. Either the loser appears once more in the feed with status merged and a pointer to the survivor, or the cross-reference table is exposed so consumers can join old codes to new ones. Which one depends on the consumer. An ERP wants to be told once. A warehouse wants the mapping table so it can restate history. Give it both and let each side pick.

What MDS gave you here

Nothing built in. Master Data Services never had a merge. Teams that needed one deleted the duplicate member in Explorer and fixed the domain-based attributes by hand, or wrote T-SQL against the staging tables and hoped the transaction log was long enough if it went wrong. The audit trail recorded a delete and some updates, which is the forty-line problem from earlier. If you are moving off MDS, this is one of the places where you should expect the replacement to do more than MDS did, not just the same thing on a newer server.

Where Primentra stands on this

Today Primentra spends its effort on the step before merge. Unique and combination keys stop the second Acme from being created, business rules catch the near-misses a key cannot express, and approvals put a human in front of anything that gets through. Rows are soft-deleted, so codes are never reused. Most mid-market duplicate problems are prevention problems, and that is where we started.

Near-duplicate detection and merge are on the roadmap for Q4 2026. This post is, in part, the contract we are building it against: the losing code stays resolvable, references move from the model's own list of what points where, the row is kept and marked, the merge is one audit event that goes through approval like any other change, and unmerge keeps the three things it needs. If you have merged master data before and got burned in a way not on this list, I would like to hear it before the feature ships rather than after.

Common questions