Deduplicating a Messy B2B Lead Enrichment Table
Your B2B marketing team has 480,000 lead records assembled from a webinar platform, a purchased list, and three years of trade-show scans. Roughly 12% are suspected duplicates. Country is missing in 23% of records, 4% of emails fail syntax validation, phone numbers arrive in seven formats, and company names vary: 'Acme Inc.', 'Acme, Inc.', and 'ACME Corporation'. A fuzzy-match job at a 0.85 similarity threshold produced 9,000 merges. A manual audit of 200 merges found 27 incorrect ones, a 13.5% false-merge rate, including two different subsidiaries of the same parent that were collapsed. Sales wants one golden record per company before next quarter's campaign, but legal wants an audit trail and the ability to reverse merges. You have one data engineer for six weeks and no budget for a new master-data-management platform.
1. Evaluate the 0.85 threshold and propose a matching architecture that improves precision without losing too many true duplicates.
Read the worked answer
The 0.85 threshold is not the root problem; the architecture is. A single similarity score over company name invites false merges because 'Acme Inc.' and 'Acme Corporation' can be the same firm, while two subsidiaries may share almost the same name but have different domains and addresses. A 13.5% false-merge rate is too high for sales territory assignment and for legal reversibility.
I would use deterministic keys first: normalized email, normalized phone in E.164, and company domain. These have high precision and are cheap. Records matching on a deterministic key can be merged automatically with provenance. For the remaining records, use blocking to avoid comparing all pairs: block on normalized domain suffix, postal code, phone area code, and a cleaned company-name token. Within blocks, score fields separately rather than one blended score: name similarity, domain equality, address similarity, and contact-name similarity. Set a high auto-merge threshold, a low reject threshold, and a review band in between. Given the audit result, the auto-merge threshold should be stricter than 0.85, and the review band should capture the 27 bad cases. Subsidiaries should be modeled as separate companies linked to a parent, not merged into one record. With one engineer and six weeks, this is feasible using open-source fuzzy matching and a review queue.
2. Design validation and survivorship rules for country, email, phone, and company name. What should the golden record prefer when sources disagree?
Read the worked answer
Validation should run before matching. Email: syntax check plus domain-level checks, with role-based addresses flagged separately from personal addresses. Phone: parse into E.164 using a library, record the original format, and flag numbers that fail country-specific length rules. Country: do not infer from email domain alone; use a source-priority rule and mark inferred values as inferred rather than observed. Company name: keep the legal or display name separate from the normalized match key, and preserve the source string.
Survivorship should be field-specific, not record-specific. For email, prefer the most recently verified deliverable address, then the webinar source, then trade-show scans, and treat purchased-list emails as lowest trust. For phone, prefer a number observed on the company website or provided by the contact, not one inferred from a similar record. For country, prefer a verified billing or shipping address, then a country observed on the company site, then a manual review; never let a low-trust purchased record overwrite a verified value. For company name, prefer the source with the most recent human confirmation, but store all variants in a names table.
Every surviving value needs provenance: source, timestamp, method, and confidence. Legal's requirement means merges must be reversible, so keep a merge map with merged_into, merged_at, and rule_id, and never physically delete the source rows.
3. What metrics and audit controls would you put in place to know whether the deduplication is working and to satisfy legal's reversibility requirement?
Read the worked answer
I would track four groups of metrics. First, duplicate detection: estimated duplicate rate from a manual sample, pairs auto-merged, pairs sent to review, and pairs rejected. Second, merge quality: precision from a weekly random audit of auto-merges and review-band decisions, false-merge rate, and false-split rate from a sample of known duplicates. Third, operational: review queue age, percentage of merges with complete provenance, and time to reverse a merge. Fourth, business impact: campaign bounce rate, duplicate sends, and sales acceptance of golden records.
The audit should not rely only on the engineer's judgment. A sales operations reviewer should sample high-value accounts and all merges above a certain revenue threshold. Every merge should write an immutable audit event containing the input record IDs, the rule and score, the fields changed, and the actor. Reversal should be a supported operation that restores the prior state and records the unmerge. I would also run a canary: hold back 1,000 records from auto-merge, resolve them manually, and compare the manual result with the algorithm. If precision falls below an agreed threshold, the auto-merge band narrows automatically. This gives legal a defensible trail and gives sales a measurable quality target rather than a one-time cleanup.