Match Thresholds and the USD 340,000 Credit Note
A distributor's customer master holds 310,000 account records sourced from Salesforce, an on-premise ERP and a legacy billing system. The first matching pass produced 22,000 candidate pairs. On a hand-labelled sample of 500 pairs, precision is 0.96 for scores at or above 0.95, 0.71 between 0.90 and 0.95, and recall across the whole rule set is 0.62. A pilot that auto-merged everything above 0.90 combined two subsidiaries of the same legal entity. They share one tax identifier but are invoiced separately, and a duplicate suppression rule in billing then misapplied a USD 340,000 credit note. Separately, a sales representative cannot see that two accounts with different names and email domains belong to the same parent group. Daily volume is about 4,000 new or changed accounts and there are two part-time stewards to work a review queue.
1. Where should the auto-merge threshold sit, and how should the remaining pairs be processed given the stewards' capacity?
Read the worked answer
The cost asymmetry drives the answer. A false merge caused a USD 340,000 misapplied credit and is expensive and slow to unwind; a false split merely leaves two records that a human can later combine. So auto-merge should be narrow: score at or above 0.95 and at least one corroborating hard attribute - identical tax identifier, exact address plus phone, or a shared bank detail. Name similarity alone should never auto-merge, whatever the score.
Scores between 0.90 and 0.95 go to a steward queue; below 0.90 auto-reject into a suppressible candidate store. Now size the queue: 22,000 pairs at roughly 250 pairs per steward per day means about 44 working days for the initial backlog before the 4,000 daily changes are counted. That is unworkable without shrinking the queue. Improve blocking first - tax ID, normalised name plus ZIP5, phone, email domain - and add active learning so the most ambiguous pairs are reviewed first.
Recall of 0.62 is the more serious signal: 38% of true duplicates never reach the queue. Add fuzzy blocking keys before adding headcount.
2. How should the data model accommodate subsidiaries that share a tax identifier yet are invoiced separately?
Read the worked answer
The record should not be modelled as a single flat 'customer'. The defect is that the entity resolution was operating at the wrong grain: a tax identifier distinguishes a legal entity, not a bill-to account.
Model a hierarchy - ultimate parent party, legal entity, billing account, contact - with identifiers appropriate to each level. The shared tax identifier is a matching attribute at the legal-entity level and should correctly merge the two subsidiaries into one legal entity. It must not collapse their billing accounts, which carry independent payment terms, credit limits and invoicing relationships. Billing account therefore remains a child entity with its own key, and the merge is scoped to the level at which the evidence is valid.
The sales visibility problem is solved by the same structure rather than by merging records: maintain a relationship to the global ultimate parent, so two differently named accounts with different email domains can still roll up to one group. Store hierarchies as parent-child plus a closure table for fast roll-up queries, and version the hierarchy so retroactive reorganisations do not rewrite historic invoices.
3. How should the golden record be constructed, and what controls prevent this class of defect from recurring?
Read the worked answer
Use attribute-level survivorship, not record-level. Each attribute gets a source precedence - ERP wins on legal name, tax identifier and registered address; billing wins on credit terms and payment method; CRM wins on contacts and industry - with recency as the tie-breaker and a trust score per source. Store the winning source and timestamp alongside every attribute so any value on the golden record can be explained in a dispute.
Never delete or recycle the losing identifiers. Keep a cross-reference table mapping every source key to the master key, so a merge can be reversed and downstream systems can still resolve their own IDs.
Controls: a mandatory human confirmation step for any merge that would cross a legal entity or change a billing account; an unmerge path that is tested, not theoretical; and monitoring on merge rate, unmerge rate, and match precision from a monthly relabelled sample, alerting if unmerge exceeds roughly 2%. Log every steward decision with the evidence they saw so it becomes training data for the next threshold revision rather than an unexamined opinion.