How do I deduplicate records when moving data between systems?

Deduplicating records when moving data between systems is mostly about defining identity clearly and then applying a repeatable matching strategy.

1) Decide what “duplicate” means

A record is a duplicate if it represents the same real-world entity as another record. That could mean:

• Exact duplicates: every field is identical
• Key-based duplicates: same email, customer ID, SKU, etc.
• Fuzzy duplicates: same person/company but slightly different spelling or formatting

You usually need rules per entity type:

• Customers: email, phone, name + address
• Products: SKU, UPC, normalized title
• Orders: source system order ID, external reference, timestamps

2) Choose a deduplication strategy

Common approaches:

A. Deterministic matching

Use exact rules such as:

• Same source ID
• Same email address
• Same normalized tax ID

Best when you have trusted unique identifiers.

B. Probabilistic / fuzzy matching

Use similarity across multiple fields:

• Name similarity
• Address similarity
• Date of birth
• Phone/email normalization

Best when data is messy or incomplete.

C. Master data / survivorship rules

When duplicates are found, decide which record wins:

• Most recent update
• Most complete record
• Trusted source wins
• Merge fields from multiple records

3) Normalize data before comparing

Deduping fails if formats differ. Normalize things like:

• Trim whitespace
• Lowercase emails
• Standardize phone numbers
• Parse/standardize addresses
• Remove punctuation where appropriate
• Normalize dates and country/state codes

Example:

• John.Doe@Example.com → john.doe@example.com
• (415) 555-1212 → +14155551212

4) Use matching keys and scoring

A practical pattern is:

1. Exact match first on strong identifiers
2. If no exact match, compute a match score
3. If score exceeds threshold, treat as duplicate
4. Otherwise keep as distinct

Example scoring:

• Email match: +100
• Phone match: +80
• Same normalized name: +30
• Same address: +20

If score ≥ 100, merge; if 70–99, flag for review; below 70, keep separate.

5) Handle one-to-many and many-to-one mappings

When moving between systems:

• One record in source may map to multiple in target
• Multiple source records may map to one target record

You need a crosswalk table that stores:

• Source system
• Source record ID
• Target system
• Target record ID
• Match confidence / rule used
• Timestamp

This helps prevent re-importing the same record and makes the process auditable.

6) Process in stages

A safe migration flow is:

1. Extract
2. Profile data
3. Normalize
4. Match / dedupe
5. Resolve conflicts
6. Load into target
7. Log decisions

Do a test run before the real migration.

7) Keep an audit trail

Always record:

• Which records were merged
• Which fields came from which source
• Why a match was made
• Who approved exceptions, if manual review was used

This is important for troubleshooting and compliance.

8) Be careful with false positives

Over-aggressive deduplication can merge distinct records incorrectly. To reduce risk:

• Require stronger matches for sensitive entities
• Use manual review for borderline cases
• Start conservatively
• Tune thresholds based on samples

9) Tools and implementation options

Depending on your stack, you can use:

• SQL dedupe queries and unique constraints
• ETL/ELT tools with matching rules
• Python with pandas / record linkage libraries
• MDM (Master Data Management) platforms
• CDC pipelines with upsert logic and idempotency keys

10) Simple practical rule set

A good starting rule set:

• Exact match on source ID → duplicate
• Else exact match on normalized email → duplicate
• Else exact match on normalized phone + last name → duplicate
• Else fuzzy name/address score above threshold → review or merge
• Everything else → new record

If you want, I can also show:

• a SQL example,
• a Python example,
• or a migration workflow for a specific type of data like customers or products.