Two Names, One Entity

Entity Resolution You Can Trust

A customer signs up with a personal email address and later buys something using a work address. A supplier appears as "Acme Ltd" in the finance system and "ACME Limited" in the procurement system. A patient's surname is spelled one way at a clinic and another way at a pharmacy. Each system is internally consistent. When their data is combined, however, one person becomes two records, or two different people are blended into one.

I have met this problem in every integration project I have worked on. It was especially visible in a sports data platform I built recently, which combines statistics from several public sources. In that project, a single competition appeared under its official name, its governing body's name, and its sponsor's name, depending on the source. Individual players appeared with different spellings, different name orders, and sometimes only a surname.

This article is about entity resolution, the process of deciding which records, across one or more sources, describe the same real-world thing and linking them to a single identifier. It is sometimes called record linkage or deduplication, and it sits underneath customer analytics, fraud detection, master data management, and any report that combines data from more than one system.

Reis and Housley give this work its formal name in Fundamentals of Data Engineering. Master data, in their framing, is data about business entities such as employees, customers, products and locations, and keeping one consistent picture of those entities gets harder as a company grows, acquires other companies and works with partners. Master data management is the practice of building consistent entity definitions, which they call golden records, so that entity data lines up across an organisation and with the outside parties it deals with. They also make the point that this is a business process supported by tools rather than a purely technical exercise, and that it reaches back into operational databases rather than living only in the warehouse.

Their worked example is close to the one in this article. An MDM team agrees a standard format for addresses, engineers build an API that returns addresses in that format, and a system then uses those addresses to match customer records held by different divisions. Format first, matching second.

One line carries the rest of this article: a wrong merge is corruption.

Entity resolution matters because every downstream number depends on it. If one customer is split across two records, each record understates their value. If two customers are merged into one, the resulting profile describes someone who does not exist, and every metric built on that profile is wrong.

There are three stages to get right. Make records comparable before you compare them. Decide asymmetrically, because the two kinds of error cost very different amounts. Then make the decisions durable, so a reviewer's judgment is not thrown away by the next scheduled run.

Anyone who has found a customer counted twice in a board report, or two customers welded into one, knows how quietly this spreads.

Make records comparable before comparing them

The first stage prepares records so that similarity scores mean something. Many matching errors are caused before any comparison takes place, either by inconsistent cleaning or by comparing records that never needed to be compared.

Normalise in one place

Normalisation means converting values into a standard form before comparing them. For names, this usually involves converting text to lowercase, removing accents and punctuation, removing stray characters such as footnote markers, and unifying known spelling variants. For addresses, it may involve standardising abbreviations such as "St" and "Street". For company names, it may involve removing legal suffixes such as "Ltd" and "Limited".

In the platform I built, names arrived with different transliterations, different word orders, and occasional footnote markers inside the name field. The normalisation step handled all of these and then sorted the words in each name, so that "Omondi Brian" and "Brian Omondi" produced the same normalised value.

The most important design decision was to keep all normalisation in a single shared module. If each source's parser cleaned values in its own way, the same entity would be normalised slightly differently depending on where it came from, and matching would fail for reasons that are very hard to see.

The module itself is small, and keeping it small is part of the point, because every rule in it applies to every source equally.

# entity_resolution/name_normalise.py  (simplified)
from parsers.common.names import normalise   # language-aware: lowercases, strips
                                              # accents, footnote markers, punctuation

def canonical(name: str) -> str:
    """Sorted-token canonical form — word order becomes irrelevant."""
    return normalise(name)   # normalise() returns tokens sorted alphabetically

def blocking_key(name: str) -> str:
    """4-char prefix of the canonical form; candidates sharing a key are scored."""
    canon = canonical(name)
    return canon[:4] if len(canon) >= 4 else canon

The shared normalisation module (parsers/common/names.py) handles lowercasing, accent removal, and known transliteration variants so that a source writing the family name first produces the same canonical value as one that writes it last.

Compare only plausible candidates

Comparing every record with every other record becomes expensive very quickly. It also increases the number of false matches, because every additional comparison is another chance for two different entities to look alike.

Blocking limits comparisons to candidate pairs that share a meaningful attribute. A customer pipeline might compare records only when they share a postcode or a phone number. In the platform I built, two player records were compared only when they shared at least one blocking key: the same team in the same season, the same date of birth, or the same normalised surname.

A blocking key does not decide whether two records match. It decides which pairs are worth scoring.

Pick keys that are cheap to compute and rarely wrong. A postcode, an order reference, a registration number, a date of birth. The aim is not to be clever; it is to avoid asking the scorer a million questions whose answer is obviously no.

In code, a blocking key is A label that two records must share before they are compared at all.

# entity_resolution/player_matcher.py  (simplified)
from entity_resolution.name_normalise import blocking_key

# All candidates sharing the same 4-char key are scored against each other.
# Candidates from the same source are skipped (deduplication is not the goal).
for i in range(len(group)):
    for j in range(i + 1, len(group)):
        a, b = group[i], group[j]
        if a.source_id == b.source_id:
            continue
        score = _fuzzy_score(a.player_raw, b.player_raw)
        score += _dob_score(a.dob_raw, b.dob_raw)

Grouping by the first four characters of the canonical name turns a comparison of every record against every other record into a small set of plausible candidates per run.

Where this breaks down

Blocking can miss true matches. If a customer moves house and changes phone number, two of their records may never be compared. In a well-designed system, those records begin as separate entities and can be linked later when stronger evidence appears. As the next section explains, that outcome is usually the safer mistake.

A reasonable objection is that modern fuzzy matching libraries are fast enough to compare every pair. Speed, however, is only part of the problem. Comparing everything still produces more false candidates, and false candidates are exactly what the decision stage must guard against.

Once records are comparable, the next question is what to do with a similarity score.

Decide asymmetrically

The second stage turns similarity scores into decisions. Entity resolution produces two kinds of error with very different consequences, and a trustworthy system should treat them differently.

Score with evidence, not only names

Each candidate pair receives a similarity score. The platform uses RapidFuzz, an open-source Python library for fuzzy string matching, to compare names using token-based similarity. The name score is then adjusted using supporting evidence. Matching dates of birth increase the score. Conflicting dates of birth reduce it sharply. Belonging to the same team in the same season adds further confidence.

In many domains, a single strong attribute is more informative than a name. Two customers with similar names and different dates of birth are probably different people. Two customers with similar names and the same date of birth and postcode are very likely the same person.

Two errors, two costs

A missed match leaves one entity split across two records. Its activity is divided, so each record looks smaller than the truth. This error is a gap. It is visible to anyone who looks for duplicates, and it can be corrected later.

A wrong merge combines two different entities into one record. Their histories are blended into a profile that describes neither of them. Nothing appears broken, but every metric built on that profile is wrong. This error is corruption.

Because the costs are different, the platform does not rely on a single threshold. Instead, it uses three outcomes.

It helps to name the quality problem precisely. Reis and Housley summarise data quality as three characteristics drawn from Data Governance: The Definitive Guide: accuracy, completeness and timeliness. Duplicate values sit squarely under accuracy, alongside factually wrong figures. A wrong merge, though, is a subtler accuracy failure: no field is incorrect, every value came from a real record, and the row as a whole still describes nobody.

Score	Outcome
92 or above, with no conflicting date of birth	Linked automatically to the existing entity
75 to 91	Sent to a queue for human review
Below 75	Created as a new entity

The middle range is intentionally wide. Reviewing a borderline pair costs a person one decision. An incorrect automatic merge can contaminate every report that includes the affected entity.

Both the scoring and the decision live in one place, so the thresholds can be reviewed and changed as a unit.

# entity_resolution/player_matcher.py
from rapidfuzz import fuzz
from entity_resolution.name_normalise import canonical

THRESHOLD_HIGH = 92   # auto-accept
THRESHOLD_LOW  = 75   # auto-reject (grey zone → review queue)

def _fuzzy_score(a: str, b: str) -> float:
    return fuzz.WRatio(canonical(a), canonical(b))

def _dob_score(dob_a, dob_b) -> float:
    if not dob_a or not dob_b:
        return 0.0                     # missing DOB contributes nothing
    if dob_a[:10] == dob_b[:10]:
        return +15.0                   # exact date match
    if dob_a[:4] == dob_b[:4]:
        return +10.0                   # same year only
    return -5.0                        # conflicting year — mild penalty

def decide(value: float) -> str:
    if value >= THRESHOLD_HIGH:
        return "auto_accept"
    if value < THRESHOLD_LOW:
        return "auto_reject"
    return "review_queue"

A conflicting date of birth lowers the score only slightly in this model. The main guard against a wrong merge is the wide review range: any pair that scores below 92 and above 75 goes to a human rather than being merged automatically.

Where this breaks down

These thresholds are specific to the data they were designed for, which contained names that varied across languages and sources. Another dataset would need its own thresholds, set by reviewing a sample of real candidate pairs.

The obvious objection is that a wide review range creates manual work. It does create work. The alternative is allowing the system to make the most expensive kind of mistake without anyone noticing. When human attention is limited, it should be spent where errors cost the most.

A review queue is only useful if reviewers' decisions persist, which leads to the third stage.

Make decisions durable

The third stage is memory. A matching system is only as trustworthy as the decisions it remembers between runs.

Record how every link was made

Every link between a source value and an internal identifier should be stored together with the method used, the score, and the time of the decision. The platform uses an alias table for this purpose.

-- sql/init/004_entity_resolution.sql
CREATE TABLE IF NOT EXISTS core.player_alias (
    alias           TEXT        NOT NULL,
    source_id       TEXT        NOT NULL,
    player_id       TEXT        NOT NULL REFERENCES core.player(player_id),
    method          TEXT        NOT NULL DEFAULT 'auto_accept'
        CHECK (method IN ('auto_accept', 'manual_review', 'seeded')),
    score           NUMERIC(5,2),
    resolved_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (alias, source_id)
);

Every link can be explained: when someone asks why two records were merged, the answer is a row in this table. One rule worth encoding — not yet fully implemented in this codebase — is that automated runs should never overwrite a manual_review decision. At present the ON CONFLICT DO UPDATE path replaces any existing row unconditionally; protecting manual decisions from later overwriting would require a check on method before the update fires.

That table is a small piece of lineage in the sense Reis and Housley use the word: an audit trail of what happened to data as it moved, which is what later makes error tracking, debugging and accountability possible at all. It earns its keep in an awkward moment too. When someone asks you to delete everything you hold about them, you need to know which records were folded into which.

Accountability is the other half of it. The book argues that quality is hard to manage when nobody is answerable for a given piece of data, and that accountability can go right down to a single field that appears across many systems. Their example is a named person responsible for a customer ID across the estate. That is the right shape of ownership for an alias table. Someone has to own the identifiers, set the thresholds and work the review queue, and that person does not have to be an engineer.

Give names a time window

Names also change over time within a single source. A company is renamed after an acquisition but remains the same legal entity. A retired product name is later reused for a different product. A lookup table that maps a name directly to an identifier cannot handle both situations.

The solution is to give each alias a validity period. The simplified example below shows one organisation that was renamed in 2021 and a different organisation that later adopted its old name.

alias,source,entity_id,valid_from,valid_to
Coastal Stars,source_a,ORG_014,2015-08-01,2021-06-30
Coastal United,source_a,ORG_014,2021-07-01,
Coastal Stars,source_a,ORG_031,2024-08-01,

With validity dates in place, a name resolves to an identifier only within its time window. Readers familiar with slowly changing dimensions will recognise the pattern, applied here to identifiers instead of descriptive attributes.

Resolving a name then becomes a join with a date condition, which is easy to read and easy to test.

-- transform/models/core/match.sql  (simplified)
select
    m.league_id,
    m.season,
    ha.team_id as home_team_id,
    aa.team_id as away_team_id
from {{ ref('stg_match') }} m
join {{ ref('team_alias') }} ha
  on ha.alias     = m.home_team_raw
 and ha.source_id = m.source_id
join {{ ref('team_alias') }} aa
  on aa.alias     = m.away_team_raw
 and aa.source_id = m.source_id

Team aliases carry valid_from and valid_to columns for handling renames, but the current join does not yet filter on date. When a name appears that has no alias row at all, the join produces no row, and a not-null test on the resulting identifier turns that silence into a failed build.

Store confidence as data

Some attributes are important enough to carry a confidence level of their own. In the platform I built, age affects almost every metric, so each player has a date-of-birth confidence value. The value is high when two independent sources agree, medium when one source provides a full date, low when sources disagree or only the year is known, and unknown when no date is available. Players with an unknown date of birth receive a neutral default in the talent score rather than being excluded, and they appear in rankings with their confidence level visible alongside the result. A stricter version — excluding unknowns from age-sensitive tiers — remains a design option worth revisiting as source coverage improves.

The same approach works for any attribute that drives important decisions, such as a customer's country for tax purposes or a supplier's registration number.

Anywhere a single field silently decides a calculation, that field deserves a confidence column beside it. Tax residency, billing country, industry code, contract start date: each of those has a version of the same problem, and each is easier to defend when the record itself says how sure you are.

Where this breaks down

A common objection is that rebuilding every link from scratch on each run is simpler and keeps the pipeline idempotent, meaning that running it twice produces the same result. Idempotency does not require forgetting. Manual decisions can be treated as an input to each run, exactly like source data, and the result remains reproducible.

A bigger objection says matching should be automated end to end, with no queue at all. Reis and Housley place data quality on the boundary between human and technical problems, and ask for both halves: processes that gather usable human feedback, and tooling that catches problems before anyone downstream sees them. A review queue backed by a durable alias table is one way to do both at once.

Where that leaves you

An organization with three trading names is a nuisance. A customer split across three records, or three customers fused into one, is something worse: a number that looks fine on a dashboard and is wrong in a way nobody can see.

The fix is not a cleverer string comparison. Normalize in one place. Block so that only plausible pairs are scored. Send the uncertain middle to a person, and store what that person decided, with the method, the score and the date, so the next run cannot quietly undo it.

The records you merge today become the facts your users quote tomorrow. Treat every merge as a claim you may have to defend.

Before you move on, find one join in your own pipeline that matches records by name or email address. Write down what would happen if it merged two different people. If the answer worries you, that join is where your review queue should begin.

This is the second article in a series on data engineering lessons from building pipelines on external sources. The next article covers source failure, including what to do when a source keeps responding but stops returning data.