Data Deduplication Strategies at Enterprise Scale

Why Enterprise Deduplication Is Hard

Small lists with a few thousand records can be deduplicated with simple exact matching. Enterprise databases with millions of records across dozens of systems require a fundamentally different approach.

The challenges at scale:

• Volume. Comparing every record to every other record is O(n^2). At 10 million records, that is 50 trillion comparisons. Even at 1 million comparisons per second, that would take over 1.5 years.
• Variety. The same entity is represented differently across systems (CRM says "John Smith at IBM," marketing automation says "J. Smith at International Business Machines," billing says "JOHN SMITH at IBM Corp.").
• Velocity. New records enter the system constantly. Deduplication is not a one-time event; it must run continuously.
• Authority. When two records are duplicates, which one is correct? Different systems may hold different correct information (marketing has the right email, sales has the right phone number, billing has the right address).
• Relationships. Records have relationships (contacts belong to accounts, deals belong to contacts). Merging duplicates must preserve and correctly reassign all relationships.

Matching Strategies

Exact matching

The simplest approach: two records match if a field is identical after normalisation.

Normalisation steps:

• Lowercase all text.
• Remove leading/trailing whitespace.
• Remove punctuation.
• Standardise abbreviations (St -> Street, Inc -> Incorporated).
• Remove special characters.

Exact matching works for:

• Email addresses (after lowercasing).
• Phone numbers (after formatting to a standard format).
• Government IDs and account numbers.
• Domains.

Exact matching fails for:

• Names (Robert vs Bob vs Rob vs Bobby).
• Company names (IBM vs International Business Machines vs I.B.M.).
• Addresses (123 Main St vs 123 Main Street vs 123 Main St.).

For email-only deduplication, upload your lists to Email Extractor, which performs case-insensitive exact matching across all uploaded files.

Fuzzy matching

Fuzzy matching identifies records that are similar but not identical.

String similarity algorithms:

Example: matching company names

String similarity alone cannot determine that IBM and International Business Machines are the same company. This requires a lookup table of known aliases or more sophisticated matching.

Composite matching

Combining multiple fields produces more accurate matches than any single field.

Composite match scoring:

Match score = (
    weight_email * email_match(a, b) +
    weight_name * name_similarity(a, b) +
    weight_company * company_similarity(a, b) +
    weight_phone * phone_match(a, b) +
    weight_address * address_similarity(a, b)
)

if match_score > threshold:
    flag_as_duplicate(a, b)

Example weights:

Threshold interpretation:

• Score > 0.90: high-confidence duplicate (auto-merge).
• Score 0.70-0.90: probable duplicate (flag for review).
• Score < 0.70: probably not a duplicate (no action).

Machine learning matching

For large-scale, complex deduplication, machine learning models can be trained on human-reviewed examples.

Process:

1. Generate candidate pairs using blocking (see below).
2. Calculate features for each pair (string similarities, field matches, contextual features).
3. Train a classifier (random forest, gradient boosting, neural network) on human-labelled examples of duplicates and non-duplicates.
4. Apply the trained model to score all candidate pairs.
5. Review edge cases and retrain periodically.

Advantages:

• Adapts to the specific characteristics of your data.
• Can capture patterns that rule-based approaches miss.
• Improves over time with more training data.

Requirements:

• Labelled training data (thousands of examples of duplicates and non-duplicates).
• Data science resources to build and maintain the model.
• Ongoing monitoring and retraining.

Scaling Deduplication

Blocking

The fundamental technique for scaling deduplication is blocking: instead of comparing every record to every other record, divide records into blocks and only compare records within the same block.

Blocking strategies:

Multi-pass blocking:

Use multiple blocking keys in separate passes. A record that does not block together with its duplicate in pass 1 may block with it in pass 2.

• Pass 1: block by email domain.
• Pass 2: block by last name + first 3 characters of first name.
• Pass 3: block by phone number (normalised).
• Pass 4: block by company name + city.

Union the candidate pairs from all passes, remove duplicates among the candidate pairs, then score and evaluate.

Sorted neighbourhood

An alternative to blocking: sort records by a key, then compare each record to its neighbours within a sliding window.

1. Choose a sorting key (e.g., concatenation of Soundex of last name + first initial).
2. Sort all records by this key.
3. Compare each record to the next w records in the sorted order (window size w, typically 5-20).

Advantages: simple to implement, handles slight variations in the sorting key. Disadvantages: similar records with very different sorting keys will be missed.

Distributed deduplication

For truly massive datasets (hundreds of millions of records), distribute the work:

• Map-Reduce: Map phase assigns records to blocks; Reduce phase compares within blocks.
• Apache Spark: Spark MLlib includes entity resolution components.
• Dedicated tools: Senzing, Tamr, Informatica MDM handle distributed entity resolution.

Merge Strategies

Deciding which record survives

When two records are confirmed duplicates, one must survive (or a new merged record is created).

Survival rules:

Field-level merge rules

Handling relationships

Merging records means reassigning relationships:

• Contacts to accounts: if two contact records at the same account are merged, simple. If they are at different accounts, investigate (did the person change companies, or is this a false match?).
• Deals/opportunities: reassign to the surviving record. Check for duplicate deals created because of duplicate contacts.
• Activities: merge email history, call logs, meeting records, notes. Preserve chronological order.
• Lists and segments: add the surviving record to all lists that either duplicate was on. Remove the non-surviving record.
• Subscriptions and consent: merge conservatively. If either record has an unsubscribe or consent withdrawal, honour it on the merged record.

Cross-System Deduplication

The multi-system problem

Enterprise organisations typically have the same contacts in:

• CRM (Salesforce, HubSpot, Dynamics).
• Marketing automation (Marketo, HubSpot, Pardot).
• Customer support (Zendesk, ServiceNow, Freshdesk).
• Billing/ERP (NetSuite, SAP, QuickBooks).
• E-commerce platform (Shopify, Magento).
• Event platforms (Cvent, Eventbrite).
• Data providers (ZoomInfo, Apollo).

Each system has its own record structure, identifiers and data quality.

Cross-system matching

Step 1: Extract and normalise

Export contacts from each system. Normalise field names, formats and values to a common schema.

Step 2: Create a common identifier

If no common identifier exists across systems (such as a customer ID), email address is the most reliable cross-system match key.

Upload all exports to Email Extractor to extract and deduplicate email addresses across all source files. This identifies which email addresses appear in multiple systems.

Step 3: Match and link

For records that share an email address, link them. For records that do not share an email address, use composite matching (name + company + other fields) to identify potential cross-system duplicates.

Step 4: Create or update the golden record

Establish one system as the master (usually CRM). Merge the best data from each source system into the master record.

Step 5: Maintain synchronisation

Set up ongoing sync between systems. When a record is updated in any system, propagate changes to the master. When a new record is created, check for duplicates before creation.

Master Data Management (MDM)

For organisations with complex data landscapes, a dedicated MDM platform manages entity resolution across systems:

Implementation

Phase 1: Assessment (week 1-2)

1. Inventory all systems containing contact/company data.
2. Count records in each system.
3. Sample 1,000 records from each system and estimate duplicate rates.
4. Map fields across systems (which fields exist where, what they are called).
5. Identify the most reliable source for each field.

Phase 2: Pilot (week 3-4)

1. Select one system (start with the CRM).
2. Export all records.
3. Apply blocking + matching on a sample (10,000-50,000 records).
4. Review results manually (at least 200 flagged pairs).
5. Calculate precision (what percentage of flagged pairs are true duplicates) and recall (what percentage of true duplicates were flagged).
6. Tune thresholds and weights based on results.

Phase 3: Production deduplication (week 5-8)

1. Run the tuned matching process on the full database.
2. Auto-merge high-confidence duplicates (score > 0.90).
3. Queue medium-confidence duplicates (score 0.70-0.90) for human review.
4. Resolve reviewed pairs.
5. Apply merge rules and reassign relationships.
6. Verify data integrity after merging.

Phase 4: Prevention (ongoing)

1. Implement duplicate detection on record creation.
2. Set up automated matching on new records (daily or real-time).
3. Establish data entry standards to reduce future duplicates.
4. Schedule periodic full-database deduplication (quarterly).
5. Monitor duplicate creation rate as a data quality metric.

Metrics

Related Guides

• How Email Deduplication Works
• Removing Duplicates at Scale
• Building a Data Quality Programme
• Data Governance Frameworks Email
• CRM Data Hygiene Guide