Entity Resolution: Matching and Deduplicating Records Across Sources

Entity Resolution (ER) is the process of finding, matching, and merging records that correspond to the same real-world entity. These records might exist within a single database or be spread across dozens of disparate systems. The core challenge is that there's often no single, reliable identifier connecting these records. A customer might appear as "John Smith" in your CRM, "J. Smith" in a billing system, and "Jonathan P. Smith" with a misspelled address in a marketing list. Entity resolution techniques are the mechanism for determining that these three records all refer to the same person, allowing an organization to build a unified, 360-degree view of that entity.

This task is fundamental to data integrity and a prerequisite for trustworthy analytics, operational efficiency, and machine learning applications. Without effective ER, a business might send three marketing emails to the same person, fail to recognize a high-value customer across product lines, or base financial projections on inflated customer counts. It is a complex discipline that blends data processing, domain-specific rules, statistical modeling, and, increasingly, machine learning. Understanding its mechanics is essential for any technical leader responsible for a company's data assets.

What is Entity Resolution?

Formally, entity resolution is the task of identifying and linking manifestations of the same real-world objects from one or more data sources. The term is often used interchangeably with others like record linkage^[1], data matching, and reference reconciliation. A common sub-problem is deduplication, which typically refers to performing entity resolution within a single dataset to eliminate duplicate entries. The foundational problem ER solves is the absence of a persistent, universal primary key for most real-world entities. People change names, companies get acquired, and products are rebranded. Data entry errors, formatting differences, and missing values further complicate the matching process.

The output of an ER process is typically a set of links that connect duplicate records. These links can then be used to create a single, consolidated "golden record" for each entity. This process is a core component of Master Data Management (MDM)^[2], a broader discipline focused on managing an organization's critical data assets.

The Canonical Entity Resolution Pipeline

Modern ER systems, whether custom-built or part of a commercial platform, generally follow a multi-stage pipeline designed to manage the computational complexity and statistical uncertainty of the task. Directly comparing every record to every other record (an O(n²) operation) is computationally infeasible for all but the smallest datasets.

Step 1: Data Standardization and Preprocessing

Raw data is messy. The first step is to clean and standardize records to make them more comparable. This is a crucial, often underestimated, part of the process. Effective preprocessing significantly improves the accuracy of subsequent matching steps. Common techniques include:

• Normalization: Converting text to a consistent case (e.g., lowercase), removing punctuation, and stripping whitespace.
• Standardization: Transforming values to a common format. For example, using a library to parse and standardize street addresses according to postal service standards like those from the USPS^[3]. This might convert "St.", "Str", and "Street" all to "STREET".
• Parsing: Breaking down composite fields into their constituent parts, such as splitting a full name "Dr. Johnathan Smith Jr." into prefix ("Dr."), first ("Johnathan"), last ("Smith"), and suffix ("Jr.").

Step 2: Indexing and Blocking

To avoid the O(n²) comparison problem, records are grouped into smaller, manageable blocks. The assumption is that records that are potential matches are more likely to share some common characteristics. Only pairs of records within the same block are considered for comparison. This step dramatically reduces the number of pairs to evaluate.

Common blocking strategies include:

• Simple Blocking: Grouping records by an exact value, such as the same zip code or the same first three letters of a last name. This is fast but brittle; a typo in the blocking key will cause a missed match.
• Phonetic Blocking: Grouping records by how their names sound, using phonetic algorithms. Classic examples include Soundex^[4], developed for the U.S. Census, and the more advanced Metaphone^[5] and Double Metaphone algorithms, which better handle a wider variety of languages and names.
• Locality-Sensitive Hashing (LSH): A more advanced technique that uses hashing functions to group similar items into the same "buckets" with high probability. LSH^[6] is particularly useful for finding similar documents or high-dimensional data.

The goal of blocking is to maximize recall (keeping true matches in the same block) while reducing the total number of pairs as much as possible.

Step 3: Pairwise Comparison and Similarity Scoring

For each pair of records within a block, the system compares their attributes and calculates similarity scores. Different metrics are used for different data types:

• String Similarity: For names, addresses, and other text fields, edit-distance metrics are common. Levenshtein distance^[7] counts the minimum number of single-character edits (insertions, deletions, or substitutions) required to change one word into the other. Jaro-Winkler distance^[8] is a variant that is particularly well-suited for short strings like names, as it gives more favorable scores to strings that match from the beginning.
• Numeric Similarity: For numbers like age or price, the similarity can be calculated based on the absolute or relative difference.
• Geographic Similarity: For latitude and longitude, haversine distance^[9] can be used to calculate the great-circle distance between two points on a sphere.

The result of this step is a "comparison vector" for each pair, containing the similarity scores for each of the compared attributes (e.g., [name_similarity, address_similarity, dob_similarity]).

Step 4: Classification and Decision Making

This is the core decision step where a pair is classified as a Match, Non-Match, or Possible Match. There are three main approaches.

1. Deterministic (Rule-Based) ER: A set of expert-defined rules determines the outcome. For example: IF (social_security_number_is_identical) OR (jaro_winkler(name) > 0.95 AND exact_match(date_of_birth)) THEN Match. This approach is transparent and easy to implement but can be brittle and difficult to maintain as data characteristics change.

2. Probabilistic ER: This approach is grounded in the foundational Fellegi-Sunter model of 1969. It uses statistical principles to calculate a match weight. For each attribute, it estimates two probabilities: the m-probability (the probability of agreement given the pair is a true match) and the u-probability (the probability of agreement given the pair is a non-match). The ratio of these probabilities is used to calculate a weight for each attribute comparison. These weights are summed to produce a total score for the pair. Two thresholds are set: pairs scoring above the upper threshold are declared matches, pairs below the lower threshold are non-matches, and those in between are flagged for manual review.

3. Supervised Machine Learning: The modern standard for high-accuracy systems. The problem is framed as a binary classification task. The comparison vectors from Step 3 are used as features to train a model (e.g., Logistic Regression, Random Forest, Gradient Boosting) to distinguish between matches and non-matches. This requires a set of labeled training data, pairs of records that have been pre-identified as either matches or non-matches. While powerful, this approach's performance is highly dependent on the quality and quantity of the training data. The class imbalance problem (far more non-match pairs than match pairs) also needs to be handled carefully.

Step 5: Clustering and Merging

Once pairs are classified as matches, the final step is to group them into clusters where all records in a cluster refer to the same entity. This is effectively a graph problem where records are nodes and a Match classification creates an edge between them. The goal is to find the connected components^[10] in this graph. If record A matches B, and B matches C, then A, B, and C form a single cluster.

After clustering, a single canonical representation, or "golden record," is often created for each entity. This requires defining survivorship rules to decide which value to keep for each attribute. For example, the system might be configured to use the most recently updated address, the most frequently occurring last name, or the first non-null value for a given field.

A Concrete Example: Deduplicating Customers

Imagine a simplified customer database with potential duplicates.

id	name	street_address	city	state	zip
101	Jon Smith	123 Main St.	Anywhere	CA	90210
102	Susan Jones	456 Oak Avenue	Someplace	NY	10001
103	Jonathan Smith	123 Main Street	Anywhere	CA	90210
104	Jon Smyth	789 Pine Ln.	Anywhere	CA	90210

1. Preprocessing: Standardize the street_address field.
123 Main St. -> 123 main street
123 Main Street -> 123 main street

2. Blocking: Use a blocking key of zip + first_3_letters_of_name.

• Block 90210JON: Records 101, 103, 104
• Block 10001SUS: Record 102 (no pairs to compare)

3. Pairwise Comparison: Now we only compare pairs within the 90210JON block: (101, 103), (101, 104), (103, 104).

Let's compare records 101 and 103 using a Python snippet with the jellyfish library.

import jellyfish

# Record 101
name1 = "jon smith"
address1 = "123 main street"

# Record 103
name3 = "jonathan smith"
address3 = "123 main street"

name_similarity = jellyfish.jaro_winkler_similarity(name1, name3)
# Result: 0.9454545454545454

address_similarity = jellyfish.levenshtein_distance(address1, address3)
# Result: 0 (exact match after normalization)

# Comparison vector for (101, 103): [0.945, 1.0] (using 1.0 for perfect address match)

4. Classification: Using a simple deterministic rule: IF name_similarity > 0.9 AND address_similarity == 1.0 THEN Match. The pair (101, 103) would be classified as a Match.
The pair (101, 104) would have a lower name similarity (jaro_winkler_similarity('jon smith', 'jon smyth') is ~0.91) and a completely different address, so it would likely be a Non-Match.

5. Merging: The cluster {101, 103} is formed. A survivorship rule might choose the longer name, resulting in a golden record:

• Golden ID: ent_1
• Name: Jonathan Smith
• Address: 123 Main Street, Anywhere, CA 90210
• Source IDs: {101, 103}

Historical Context and Evolution

The roots of entity resolution lie in public health and census work. Computer-based record linkage was pioneered by Howard L. Newcombe in the late 1950s in Canada to study population health records. The formal mathematical theory, however, was established by Ivan Fellegi and Alan Sunter in their 1969 paper, which provided the statistical foundation for probabilistic record linkage that is still influential today. For decades, these processes were run as massive batch jobs on mainframes.

The rise of commercial databases and distributed computing brought ER into the corporate world as a key part of Master Data Management. The last decade has seen a significant shift toward machine learning, with supervised models becoming the norm for high-stakes applications. Open-source libraries and cloud-based services have now made sophisticated ER capabilities accessible to a much broader range of organizations.

The Technical Leader's Decision Lens: Tradeoffs and Costs

Accuracy vs. Scalability: This is the central tradeoff. Highly accurate models that use complex similarity functions and machine learning are computationally expensive. Aggressive blocking (creating many small blocks) improves performance but increases the risk of false negatives (missed matches), reducing recall. Conversely, loose blocking reduces missed matches but increases the number of pairs to compare, demanding more computational resources. This is a classic precision-recall tradeoff^[11] applied at the blocking stage.

Build vs. Buy:

• Build: Using open-source libraries like Python's dedupe^[12] or Splink^[13], or building on frameworks like Apache Spark^[14], offers maximum flexibility and control. This path is suitable when ER is a core competency, the data domain is highly specific, or extreme scale is required. However, it demands significant, specialized engineering expertise and a long-term commitment to maintenance.
• Buy: Commercial MDM platforms (from vendors like Informatica, Reltio, or Tamr) or cloud-native services (AWS Lake Formation FindMatches^[15], BigQuery entity resolution framework) can dramatically accelerate deployment. These solutions package the entire pipeline, often with UIs for data stewardship. The tradeoff is cost (licensing fees), potential vendor lock-in, and less customizability.

The Human-in-the-Loop Cost: No automated system is perfect. The Possible Match category from the classification step requires manual review by data stewards. This is a significant and ongoing operational cost. The quality of the automated system directly impacts this cost; a more accurate model reduces the number of pairs needing expensive human adjudication. The design of the stewardship interface is critical for efficiency.

Failure Modes and Their Impact:

• False Positives (Type I Error): Incorrectly merging two distinct entities. This is often the more dangerous error. Merging the records of two different patients in a healthcare system or two different borrowers at a bank can have catastrophic consequences.
• False Negatives (Type II Error): Failing to merge records for the same entity. This leads to a fragmented view of the entity, resulting in operational inefficiencies (duplicate mailings), poor customer experience, and inaccurate analytics (e.g., miscalculating customer lifetime value).

Common Misconceptions

"We'll just match on email address or phone number."
This is a common starting point, but it's flawed. These identifiers are not stable or unique. People change their email and phone numbers. Families may share an email address. Data entry errors are frequent. While email and phone are very strong signals, they cannot be treated as infallible primary keys. A robust ER system uses them as powerful features among many.

"Entity resolution is a one-time cleanup project."
This is fundamentally incorrect. ER is not a batch job you run once. It must be an ongoing, dynamic process. New data arrives constantly, and existing data is updated. A proper ER system continuously processes new information, resolves new duplicates, and updates golden records. It is a core piece of data infrastructure, not a project with an end date.

"More data fields will always improve matching."
Not necessarily. While more high-quality, distinct attributes can improve accuracy, adding noisy or sparsely populated fields can degrade performance. An attribute with many nulls or default values adds little signal and can confuse a statistical or ML model. The focus should be on the quality and discriminatory power of attributes, not just their quantity.

The State of Entity Resolution Today

Entity resolution is a mature but active field of development.

• AI and Graph-based Methods: The industry has largely moved from purely deterministic rules to ML-based classification. Active research focuses on using deep learning for generating better feature representations (embeddings) of entities. Modeling the problem with graph databases^[16] like Neo4j^[17] is becoming more common, allowing for more complex relationship analysis and real-time resolution.

• Privacy-Preserving Record Linkage (PPRL): With increasing data privacy regulations, there's a growing need to perform ER on sensitive data without exposing the raw values. PPRL techniques use methods like cryptographic hashing, homomorphic encryption, and Bloom filters to enable matching on encrypted or anonymized data. This is critical for collaboration between organizations, especially in government and healthcare.

• Democratization via Cloud and Open Source: The availability of powerful open-source libraries and managed cloud services has made sophisticated ER capabilities accessible to organizations of all sizes. What once required a dedicated team of specialists and expensive software can now be implemented by a small data engineering team, lowering the barrier to achieving a single source of truth.