Entity Identification Problem in Data Mining

Real data mining projects rarely draw from a single, tidy table. A retail company's churn model might need billing data from one system, support tickets from another, and browsing behavior from a third — each built at a different time, by a different team, for a different purpose. Data integration is the process of combining data like this into a single, unified dataset ready for analysis. It matters because integration done well removes duplication, reduces inconsistency, and improves the accuracy of everything built on top of the combined data — but integration is genuinely difficult, precisely because separately built systems rarely agree on data formats, attribute names, or even what a given value actually means.

The Entity Identification Problem

The hardest and most consequential part of integration is deciding, across two different data sources, whether two records actually describe the same real-world thing. This is the entity identification problem: Table A might identify a customer by cust_id, Table B by cust_number — different column names, and possibly different formats entirely — yet both may refer to the exact same customer. Get this wrong in one direction, and two records for the same person are treated as two different people, silently duplicating that customer in every downstream count and average. Get it wrong in the other direction, and two different people are merged into one, corrupting whatever record that merge touches. Because so much of data mining depends on counting, grouping, and comparing records accurately, a mistake at this stage propagates into every result computed afterward.

Why the Problem Occurs at All

Separately built databases disagree about how to represent the same entity for an ordinary, structural reason: they were created at different times, by different people, for different purposes, with no shared design to keep them consistent. A billing system built a decade ago and a support-ticket system built last year have no reason to agree on how a customer record should be structured, even though both describe the same customers. This mismatch shows up at two distinct levels, and it is worth separating them because they call for different fixes.

Schema-Level Problems: The Structures Themselves Disagree

Domain mismatch happens when the same kind of data is recorded in different formats or units — a price recorded in US dollars in one system and Japanese yen in another. Schema mismatch happens when the underlying table structures don't correspond directly — one database has a single "Employee" table while another splits employees into separate "Part-time" and "Full-time" tables, so a straightforward column-to-column merge isn't possible without first reconciling the structural difference. Constraint mismatch happens when the business rules encoded in each system differ — one system enforces a minimum GPA of 3.0 for a given status, another requires 3.5 — so a record that is valid under one system's rules might look invalid under the other's, even though nothing about the underlying data is actually wrong.

Instance-Level Problems: The Data Values Themselves Disagree

Entity identification — the central problem this lesson focuses on — is the task of finding which records across sources describe the same entity. Attribute value conflict is a related but distinct problem: even once two records are correctly matched as the same entity, their values for the same attribute might still legitimately differ, because of different units (kilograms recorded in one system, pounds in another), missing data in one source but not the other, or genuine data entry errors. Verifying this distinction concretely: a weight of 98 kg recorded in one system and 216 lbs recorded in another look like a serious conflict at first glance, but converting units first resolves it immediately:

weight_kg = 98
weight_lbs_recorded = 216.0
converted = weight_kg * 2.20462
print(f"{weight_kg} kg converted to lbs = {converted:.2f} lbs")

Output:

98 kg converted to lbs = 216.05 lbs

The "conflict" was never a data quality problem at all — it was a units mismatch that resolves to a 0.05 lb rounding difference once both values are expressed the same way. This is exactly why attribute value conflict has to be checked for and resolved before concluding that mismatched values represent an actual data error.

Approaches to Solving Entity Identification

Key matching uses a shared identifier — an ID field present and consistent across both sources — to link records directly. It is the simplest and most reliable approach, but it only works when such a common key genuinely exists and is populated consistently, which is often not the case across systems that were never designed to interoperate.

User-specified matching has a person manually define which records correspond to which. It can be highly accurate, since a human can bring context an algorithm lacks, but it does not scale to the millions of records real integration projects typically involve.

Probabilistic and attribute-based matching compares available attributes — names, addresses, dates of birth — and computes a similarity score to decide whether two records likely refer to the same entity, used when no reliable common key exists. This is worth examining directly, because it is also where naive approaches quietly fail.

A Worked Example: Where Similarity-Based Matching Succeeds and Where It Fails

Take four customer names from one system that need to be matched against five slightly different name spellings recorded in another, using a standard string-similarity measure that scores how much two strings overlap, from 0 (nothing alike) to 1 (identical):

import difflib

table_a = ["Jonathan Reyes", "Priya Nair", "Wei Chen", "Fatima Al-Sayed"]
table_b = ["Jon Reyes", "Priya Nayar", "Chen Wei", "Fatima Al Sayed", "Jonathan R."]

for name_a in table_a:
    scores = [(name_b, round(difflib.SequenceMatcher(None, name_a.lower(), name_b.lower()).ratio(), 3))
              for name_b in table_b]
    scores.sort(key=lambda x: -x[1])
    print(name_a, "-> best match:", scores[0])

Output:

Jonathan Reyes  -> best match: ('Jonathan R.', 0.8)
Priya Nair      -> best match: ('Priya Nayar', 0.857)
Wei Chen        -> best match: ('Chen Wei', 0.5)
Fatima Al-Sayed -> best match: ('Fatima Al Sayed', 0.933)

Three of these four matches are both correct and clearly confident: "Fatima Al-Sayed" and "Priya Nair" score high against their true matches because the differences are minor (a hyphen, a single-letter spelling variant). But "Wei Chen" matched against "Chen Wei" — the same two names, in reversed order, referring to the same person — scores only 0.5, barely better than a coin flip and far lower than the other three genuine matches. A naive threshold set high enough to avoid false matches on the other names (say, requiring a score above 0.7) would reject this correct match entirely, misidentifying the same customer as two different people simply because one system recorded the name in a different word order. This is precisely the risk the more reliable approaches below are built to address: a pure string-similarity score has no concept of what a name actually is — it cannot recognize that reordering two name components preserves identity, because it is only comparing character sequences, not the meaning behind them.

Heuristic rules encode this kind of domain knowledge directly — a rule that compares name components regardless of order, or that treats "Corp." and "Corporation" as equivalent before comparing strings — trading the generality of a pure similarity score for accuracy on the specific patterns that repeatedly arise in a particular dataset. A robust real-world matching pipeline typically layers these approaches rather than relying on just one: try key matching first where a reliable key exists, apply heuristic normalization (reordering name components, expanding abbreviations, standardizing units) before comparing, then fall back to similarity scoring only for the genuinely ambiguous remainder, and route the lowest-confidence matches to a person for manual review rather than trusting an automated threshold blindly.

Why Getting This Right Matters

An incorrect match is not a minor inconvenience — it can directly corrupt decisions built on the integrated data. Two employee records incorrectly treated as separate people might each look like a low performer individually, when combined they might tell a completely different story; two records incorrectly merged into one might average together the performance of two unrelated employees into a meaningless composite. Because entity identification sits upstream of essentially every other data mining task, an error introduced here is invisible to whatever classification, clustering, or association analysis runs afterward — those techniques will process the corrupted records exactly as confidently as they process the correct ones, with no way to tell the difference on their own.

Virtual vs. Actual Integration

Once entities are correctly matched, the combined data can be made available in one of two ways. Virtual integration leaves the data in its original source systems and builds a unified view on top of it — queries are translated on the fly to pull from the right underlying source, with nothing physically copied or merged. Actual integration physically merges the data into a single new database, sometimes retiring the original source systems afterward. Virtual integration avoids the cost and risk of a full data migration but depends on the original systems staying available and responsive; actual integration is more expensive up front but produces a single, self-contained dataset that no longer depends on the health of multiple separate systems. Both approaches still require the entity identification problem to be solved first — a virtual, on-the-fly view is only as trustworthy as the record-matching logic behind it, exactly as a physically merged database is.