Duplicate records are the most common failure in data integration projects. The sync runs without errors, the logs stay clean, and the data still becomes unusable within weeks.
This guide explains why duplicates occur, how confidence scoring prevents them, and how to design a review process that teams actually use.
Why duplicate records occur in data sync
Most integrations are built with a single instruction: create a record in the destination system.
That instruction executes correctly every time. It also executes when the same customer returns, producing a second record, then a third.
Test data hides the problem
Test environments contain clean, unique records. Production data does not.
The same person appears as:
Jane Smith and J. Smith
Different email addresses across different years
A changed surname after marriage
A shared household phone number
None of these variations trigger an error. Each one creates a new record according to the rules as written.
The business impact
Duplicate records cause four measurable problems:
Split history. Five years of customer activity divided across three records
Duplicate communications. The same person receives the same message multiple times
Incorrect reporting. Totals and counts based on inflated record numbers
Wasted spend. Marketing and mailing costs applied to records that are the same person
The three-outcome model
Standard integration logic has two outcomes: match an existing record, or create a new one.
Two outcomes force the system to guess whenever it is uncertain. That guess is the source of the duplicates.
A reliable integration has three:
Outcome Condition Action
Confident match Strong signal agreement Attach to existing record
Confident non-match Little or no agreement Create new record
Uncertain Partial agreement Escalate for review
Folding the third outcome into "create" produces duplicates. Folding it into "match" merges two people into one record, which is harder to reverse and may be reportable depending on the data type and jurisdiction.
How to build a confidence score
Single-field matching fails. Email addresses change, are shared, and are reused.
Confidence scoring compares multiple signals and applies thresholds to the result.
Example scoring model
score = 0
if email_exact_match: score += 50
if phone_normalised_match: score += 30
if postal_address_match: score += 20
if name_fuzzy_match > 0.9: score += 15
if shared_household_link: score += 10
if score >= 70: attach to existing record
if score <= 25: create new record
else: escalate to review queue
Weights should be tuned to your data. The structure matters more than the specific values: no single signal is sufficient alone, and a deliberate uncertainty band sits between the two confident outcomes.
Normalise data before comparison
Raw values do not compare reliably.
Phone numbers arrive as +44 7700 900123, 07700900123 and (07700) 900 123. Strip to digits and apply a consistent country prefix.
Email addresses should be lowercased. Note that some providers ignore dots in the local part while string comparison does not.
Postal addresses need consistent abbreviation handling before comparison.
Set fuzzy matching thresholds carefully
Levenshtein distance and trigram similarity handle typos and name variants such as "Jon" and "John."
At lower thresholds they also match genuinely different names, including "Erin" and "Eric." Keep the threshold high and let other signals carry the decision.
Log the matching decision
Store the score and contributing signals with every write.
Without an audit trail, questions about why two records merged have no answer. The log is what makes the system defensible.
How to design a review queue
The escalation path only works if the queue is processed. Most queues are abandoned within a month.
Three factors determine whether that happens.
Control volume
A queue receiving more than a few items per day indicates incorrectly tuned thresholds, not an insufficient reviewer.
Tune the scoring until only genuinely ambiguous cases arrive.
Provide context in one screen
The reviewer should not need to open two systems.
Display both candidate records side by side, highlight matching and conflicting fields, and show the score breakdown. Each decision should take seconds.
Apply an expiring default
Items should not remain queued indefinitely.
Set a safe default — normally "create new," because duplicates are recoverable and merges are not — applied after a defined window with notification.
Preview writes before committing them
Generate and display the exact payload before writing to the destination system, including which record will be modified and which fields will change.
This applies only to first runs of a new mapping and to flagged records. Standard records flow through without delay.
The purpose is adoption. Teams continue using workflow automation they can inspect. Teams abandon systems they cannot see inside, and revert to manual checking.
Effort distribution in integration projects
In a production sync between a fundraising platform and a donor CRM, the data movement accounted for a small share of build time.
Identity matching, confidence scoring, the review queue and the preview mechanism accounted for the remainder. The full integration case study covers the implementation.
This distribution is consistent across integration projects. Moving data is straightforward. Handling uncertainty is the work.
For sizing a build before approaching a vendor, an automation cost calculator produces a closer estimate than assumption.
Requirements checklist before building
Confirm the following before writing integration code:
Matching signals. Which fields are available in both systems, and how reliable is each
Uncertainty handling. What should happen when the system cannot determine whether two records are the same person
Review ownership. Who processes the queue, and within what timeframe
Default action. What applies when an item expires unreviewed
Audit requirements. What must be logged for compliance
If the uncertainty question cannot be answered clearly, that judgement is currently undocumented and held by an individual. Document it before encoding it.
This is the same principle that governs which process to automate first: processes with undefined exception handling are not ready for automation.
Frequently asked questions
What causes duplicate records in a data sync?
Integration logic that creates a record without first checking whether a matching record exists, or that matches on a single field such as email address, which changes and is shared between people.
How do you match records without a shared unique ID?
Score multiple signals — email, phone, address, name similarity, household links — and apply thresholds. Records above the upper threshold are matched, below the lower threshold are created, and those between are reviewed.
Is it better to over-match or under-match?
Under-match. Duplicate records can be merged later. Two people incorrectly merged into one record is significantly harder to reverse and may constitute a data incident.
How many records should reach the review queue?
A small number per day. Higher volumes indicate the thresholds require tuning.
Top comments (0)