Master Data and Entity Resolution Pipelines

Three systems hold what is, in the illustrative case below, one customer. The CRM has “Jo Smith” with an email address. Billing has “Joan Smith” at the same postal code, with a different email. Support has “J. Smith” and a phone number. No shared identifier connects them, because each system issued its own. A question that sounds simple, how many distinct customers do we have, cannot be answered until someone decides which records refer to the same person, and the answer will be wrong in one direction or the other with some frequency.

That decision is entity resolution, and the pipeline that applies it repeatedly and maintains a stable identifier for each resolved entity is a master data pipeline. This post covers the parts in the order the data flows: normalizing and blocking records, comparing and scoring pairs, handling uncertainty in the scores, turning pair decisions into entities, deciding which values the combined record shows, applying manual corrections, and keeping merges reversible and measurable. The central design theme is that a merge is a recorded decision derived from evidence, not an edit to the data, and every choice below follows from that.

The previous post, Metadata and Lineage for Change Impact Analysis, described how to record which assets and columns derive from which and how to estimate the reach of a change. Here that idea appears at the level of records: a master record is derived from source records, and the ability to say which ones, by which rule, at which time, is what lets you correct a wrong merge without losing data. The post assumes you know what a primary key, a join, and a hash are, and it explains the matching methods from scratch.

What a master data pipeline produces

The entity resolution entry defines the problem as deciding which records, across systems or within one dataset, refer to the same real-world thing when no shared identifier says so. It exists because a key is missing. If two systems share an identifier that is issued by one authority, accurate, complete, and never reused, a join does the job and none of this is necessary. A pipeline that does entity resolution typically produces three outputs, and keeping them separate is the most important structural decision.

Output	Content	Changes when
Crosswalk (link table)	One row per source record: source system, source identifier, master identifier, reason, run	Matching decisions or corrections change
Decision log	Manual and rule-based overrides with who, why, scope, and effective period	Someone decides
Consolidated view	One row per master identifier with surviving values and per-field provenance	Crosswalk or survivorship rules change

The source records themselves are never rewritten. The primary and foreign keys entry describes the same structure: a crosswalk maps the pair of source system and local identifier to a master identifier, the master record is derived from the rows that point at it, and nothing overwrites the originals, which is what makes the link reviewable. The consolidated view is what the literature calls a golden record, with the caution that the word suggests a truth that was recovered when in fact the row is the output of two sets of judgments, matching and survivorship, and is correct only to the extent those were.

Master data systems differ in where the master is authored. In a consolidation style, the master is derived from sources that keep their own authority. In a centralized style, records are created and changed in the hub under approval and the sources receive them. A registry style stores only links and leaves values in the sources. The choice changes what “reversible” means: a derived consolidated view can be rebuilt from retained sources, while values authored in a hub are themselves source data and need their own history. The rest of this post mostly describes the derived case because it is the common one for analytical platforms.

The stages run in this order.

source records
   |  standardize (case, whitespace, formats)
   v
candidate pairs  <-- blocking (cheap keys that bring likely matches together)
   |  compare fields, score each pair
   v
scored pairs     <-- thresholds: match / possible / non-match
   |  group linked records (connected components or constrained clustering)
   v
clusters = entities  <-- decisions: must-link, cannot-link
   |  survivorship rules per field
   v
consolidated view + crosswalk + decision log

Identity matching

Standardize before comparing

Matching quality starts with normalization. AWS’s documentation for its entity resolution service describes normalizing input before comparison by removing special characters and extra spaces and formatting text to lowercase (documentation read on 3 October 2026), and the entity resolution entry notes that this alone resolves a surprising share of apparent mismatches. Standardization should be deterministic and versioned, because changing it changes which pairs become candidates. It should also never replace the original value: keep the raw field and the standardized field side by side.

Blocking: choosing which pairs to compare

Comparing every record with every other record grows with the square of the number of records. The Splink documentation gives the arithmetic: the number of pairs is \(n(n-1)/2\), so a dataset of one million records generates around 500 billion pairwise comparisons, and the number of pairs the model has to process is the main driver of run time. Blocking reduces the work by comparing only pairs that satisfy a cheap rule, such as sharing a postal code or the first letters of surname and birth year. A pair is compared if it satisfies at least one of several blocking rules.

Blocking is a deliberate trade. A tight rule produces few pairs and misses true matches whose blocking fields differ because of typos or changes. A loose rule keeps more true pairs and produces many more comparisons. The Splink documentation recommends several strict rules over one loose rule so that a true match needs to be caught by only one of them. The measurement that matters is pair completeness, the share of true matching pairs that appear among the candidates, because no later stage can recover a pair that blocking dropped. Pair completeness puts a ceiling on recall for the whole pipeline.

Comparing fields

Once pairs exist, each pair is described by field-level comparisons. Common comparisons are exact equality (an email, a postal code), similarity of strings (names), and agreement of dates. One simple string similarity is the Jaccard index of character trigrams. The Jaccard index of two sets is the size of the intersection divided by the size of the union. Pad each name with a leading and trailing space, break it into overlapping three-character pieces, and compare the sets. For “jon smith” and “john smith” the sets share 8 trigrams and their union has 13, so the similarity is 8/13, about 0.615. A swap of two letters, an omitted letter, or a nickname changes the set partly, not entirely, which is why trigram similarity tolerates the kind of noise that exact equality does not.

No similarity measure fixes the semantic cases: “Elizabeth” and “Liz”, a transliterated name written in two scripts, or a married name. Those need reference lists, phonetic encodings, or other fields that agree. Name comparison also performs unevenly across naming conventions, because tokenization assumptions, name order, and the use of single names differ between cultures, so an evaluation that reports only an overall rate can hide a group for which matching is much worse. That is an inference from how the methods work, and it is a reason to evaluate by segment.

Turning comparisons into a score

A rule can say “same email, or same name and postal code.” That is simple, explainable, and brittle when fields are missing. A probabilistic model weighs the evidence from each field. The classical form is the Fellegi–Sunter model, and the Splink documentation describes its parameters. The prior \(\lambda\) is the probability that two records in the compared set match. For an observed comparison outcome, \(m\) is the probability of that outcome given that the records match, and \(u\) is the probability of that outcome given that they do not match. The documentation interprets \(m\) as a measure of data quality: how often a true match shows a different value because of typos, changes, or nicknames. It interprets \(u\) as a measure of coincidence: how often two different entities share the value, which is high for gender and tiny for a precise date of birth.

The match weight is \(\log_2\) of the prior odds plus, for each comparison, \(\log_2(m/u)\):

\[ M = \log_2\!\left(\frac{\lambda}{1-\lambda}\right) + \sum_i \log_2\!\left(\frac{m_i}{u_i}\right) \]

A positive term is evidence for a match, a negative term is evidence against, and the weights add because the model assumes the comparisons are independent given match status. The documentation names this as a key assumption. The posterior probability of a match is \(1/(1+2^{-M})\).

A small calculation shows how it works. These \(m\) and \(u\) values are assumed for illustration, not estimated from data. Let \(\lambda = 0.001\), so the prior weight is \(\log_2(0.001/0.999) \approx -9.96\). Suppose a high name similarity has \(m = 0.90\) and \(u = 0.002\), giving a weight of \(\log_2(450) \approx 8.81\). Suppose agreement of the date of birth has \(m = 0.95\) and \(u = 0.0001\), giving \(\log_2(9500) \approx 13.21\). Suppose agreement of postal code has \(m = 0.80\) and \(u = 0.01\), giving \(\log_2(80) \approx 6.32\). With all three the total is about 18.39 and the posterior probability is above 0.99999. If the date of birth is missing, so only name and postal code contribute, the total is about 5.17 and the probability is about 0.973. If the name is similar but the date of birth and postal code both disagree, with weights of about \(-4.32\) and \(-2.31\) for disagreement under these assumptions, the total is about \(-7.78\) and the probability is about 0.0045.

Two cautions come with the model. First, independence is rarely true. A city and a postal code agree together, so counting both as separate evidence counts the same fact twice and inflates the weight. Second, \(m\) and \(u\) are estimated, and a model with badly estimated values produces confident wrong scores. Treat the score as a ranking of evidence until it has been checked against labeled pairs.

AWS’s service documentation describes two matching techniques side by side: rule-based matching, where each match group carries the number of the rule that generated it, and machine-learning matching, where each group carries a confidence score between 0.0 and 1.0. The practical difference is explainability. A rule can be read aloud and argued with. A score can be reported, and whether the reasons can be shown depends on the model. Where a match decides who sees whose data, someone will eventually ask why two records were joined, and the answer needs to be better than a number. Whichever method you use, store the reason with each link.

Uncertainty

Scores are not decisions

A score becomes a decision only when a threshold is applied, and the threshold is where cost enters. The entity resolution entry describes the asymmetry. An over-merge joins two different entities, so one person’s data appears under another’s record, and undoing it is hard because the merged record was read and acted on. An under-merge leaves one entity split, which duplicates outreach and hides history, and is easy to fix by merging later, though actions taken on the split history are not undone. Where a wrong merge exposes data across parties, the safe design is three outcomes: auto-merge above a high bar, auto-reject below a low bar, and send the middle to a person or to additional evidence.

The measures are pairwise precision and recall, which the precision and recall entries define. Precision here is the share of linked pairs that are truly the same entity, and recall is the share of truly matching pairs that were linked. Precision depends on how many non-matching pairs the candidate set contains, so it moves with the prevalence of true matches in the candidate set, and a precision measured on a convenient sample need not hold in production.

An assumed calculation shows how a cutoff trades the two. Suppose blocking produces 2,000,000 candidate pairs of which 20,000 are true matches. At a low cutoff the pipeline recovers 97 percent of true matches and wrongly links 0.05 percent of the 1,980,000 non-matching pairs: 19,400 true links and 990 false links, a precision of about 95.1 percent, with 600 true matches missed. At a high cutoff it recovers 90 percent and wrongly links 0.005 percent: 18,000 true links and 99 false links, a precision of about 99.5 percent, with 2,000 true matches missed. The second setting makes 891 fewer wrong links at the price of 1,400 more missed ones. Which is better depends on what each error costs. Those numbers are assumptions, and a real pipeline needs a labeled sample to estimate them.

Evaluating with a labeled sample

Estimating precision and recall needs ground truth, which in practice means a labeled sample of pairs or of clusters, drawn so that it includes the hard region near the threshold and not only obvious cases. Sampling only high-scoring links overstates precision, and sampling only random pairs finds almost no true matches, because most pairs are non-matches. Splink’s evaluation documentation separates model evaluation, edge evaluation (the links), and cluster evaluation (the entities), and each stage can fail while the others look fine.

Evidence from a synthetic experiment

To see these effects without any real personal data, a synthetic dataset was generated and a small pipeline was run on it. The data is entirely made up: 600 entities with names drawn from small word lists, birth dates, and postal codes; each entity appears in one to three of the sources crm, billing, and support, at most once per source. Records receive noise: a nickname in a quarter of cases, a swap of two letters in about half of the longer names, a missing birth date for 15 percent, and a different postal code for 20 percent. An email field is generated for some records but is not used by the code below. The name pools are small, so coincidences are far more common than in real data, and the \(m\) and \(u\) values are assumed. The dataset is therefore a model for explaining the mechanics, not evidence about real-world accuracy. This is the data generation and the blocking.

import random, itertools, math
from collections import defaultdict

random.seed(55)
FIRST = ["alan","berta","carlo","dina","eli","fiona","goran","hana","ivo","jelena","karim","lena"]
NICK  = {"alan":"al","berta":"bert","carlo":"carl","dina":"di","eli":"eli","fiona":"fi",
         "goran":"gor","hana":"han","ivo":"ivo","jelena":"jel","karim":"kar","lena":"len"}
LAST  = ["marek","novak","orlov","petrin","quade","rasmus","sedlak","tamm","ulmer","varga",
         "weiss","xander","yilmaz","zeman","brandt","cortez","dvorak","engel","frost","gruber"]

def typo(s):
    if len(s) < 4 or random.random() > 0.5: return s
    i = random.randrange(1, len(s) - 1)
    return s[:i] + s[i + 1] + s[i] + s[i + 2:]          # swap two letters

entities = []
for e in range(600):
    entities.append(dict(f=random.choice(FIRST), l=random.choice(LAST),
                         dob=f"19{random.randint(60,99)}-{random.randint(1,12):02d}-{random.randint(1,28):02d}",
                         pc=f"{random.randint(10000,99999)}"))
records = []
for e, p in enumerate(entities):
    for s in random.sample(["crm", "billing", "support"], random.choice([1, 2, 2, 3])):
        f = NICK[p["f"]] if random.random() < 0.25 else p["f"]
        records.append(dict(rid=len(records), ent=e, src=s, first=typo(f), last=typo(p["l"]),
            dob=p["dob"] if random.random() > 0.15 else None,
            pc=p["pc"] if random.random() > 0.20 else f"{random.randint(10000,99999)}",
            email=f"{p['f']}.{p['l']}@example.test" if random.random() < 0.4 else None))
print("records", len(records), "entities", len(entities))

def tri(s):
    s = f"  {s} "; return {s[i:i+3] for i in range(len(s) - 2)}
def jac(a, b):
    A, B = tri(a), tri(b); return len(A & B) / len(A | B)

# blocking: a pair is compared if it satisfies any rule
def blocks(r):
    yield ("ln3dob", r["last"][:3], (r["dob"] or "")[:4]) if r["dob"] else None
    yield ("pc", r["pc"])
    yield ("fn2ln2", r["first"][:2], r["last"][:2])
idx = defaultdict(list)
for r in records:
    for k in blocks(r):
        if k: idx[k].append(r["rid"])
pairs = set()
for ids in idx.values():
    for a, b in itertools.combinations(sorted(ids), 2): pairs.add((a, b))
truth = {(a["rid"], b["rid"]) for a, b in itertools.combinations(records, 2) if a["ent"] == b["ent"]}
n = len(records); allp = n * (n - 1) // 2
print(f"all pairs {allp}, candidate pairs {len(pairs)}, reduction {1-len(pairs)/allp:.3f}")
print(f"true match pairs {len(truth)}, captured by blocking {len(truth & pairs)} (pair completeness {len(truth&pairs)/len(truth):.3f})")

The three blocking rules are the first three letters of the surname plus the birth year, the postal code, and the first two letters of both names. The output was:

records 1191 entities 600
all pairs 708645, candidate pairs 2611, reduction 0.996
true match pairs 728, captured by blocking 646 (pair completeness 0.887)

Blocking cut 708,645 possible pairs to 2,611, a reduction of 99.6 percent. It also dropped 82 of the 728 true pairs, because every rule they would have satisfied was broken by noise in the fields it uses. Pair completeness of 0.887 means that no matter how good the scoring is, recall cannot exceed 0.887 for this pipeline. A good reduction ratio with an unmeasured pair completeness is a common way for a pipeline to look efficient while quietly missing matches.

The scoring and clustering follow. The weights are Fellegi–Sunter style log-odds with assumed \(m\) and \(u\) values, and a prior of 0.003 within the candidate pairs. A pair counts as a first-name agreement when the trigram similarity is at least 0.5 or the first two letters agree, and as a last-name agreement when the trigram similarity is at least 0.5. Missing birth dates contribute nothing. Clustering uses a union-find structure, which makes any chain of links into one cluster.

W = {"first_hi": math.log2(0.80/0.01), "first_lo": math.log2(0.20/0.99),
     "last_hi":  math.log2(0.85/0.03), "last_lo":  math.log2(0.15/0.97),
     "dob_eq":   math.log2(0.85/0.0005), "dob_ne": math.log2(0.15/0.9995),
     "pc_eq":    math.log2(0.80/0.0001), "pc_ne": math.log2(0.20/0.9999)}
def weight(a, b):
    w = math.log2(0.003 / 0.997)                       # prior within candidate pairs, assumed
    w += W["first_hi"] if jac(a["first"], b["first"]) >= 0.5 or a["first"][:2] == b["first"][:2] else W["first_lo"]
    w += W["last_hi"] if jac(a["last"], b["last"]) >= 0.5 else W["last_lo"]
    if a["dob"] and b["dob"]: w += W["dob_eq"] if a["dob"] == b["dob"] else W["dob_ne"]
    w += W["pc_eq"] if a["pc"] == b["pc"] else W["pc_ne"]
    return w
scored = {p: weight(records[p[0]], records[p[1]]) for p in pairs}

def clusters(links):
    parent = list(range(n))
    def find(x):
        while parent[x] != x: parent[x] = parent[parent[x]]; x = parent[x]
        return x
    for a, b in links: parent[find(a)] = find(b)
    g = defaultdict(list)
    for i in range(n): g[find(i)].append(i)
    return list(g.values())

print(f"{'cutoff':>7}{'links':>7}{'precision':>10}{'recall':>8}{'clusters':>9}{'largest':>8}{'mixed':>7}")
for cut in (-4, 0, 2, 4, 6, 8, 12, 18):
    links = {p for p, w in scored.items() if w >= cut}
    tp = len(links & truth)
    cl = clusters(links)
    mixed = sum(1 for c in cl if len({records[i]["ent"] for i in c}) > 1)
    print(f"{cut:>7}{len(links):>7}{tp/max(len(links),1):>10.3f}{tp/len(truth):>8.3f}{len(cl):>9}{max(map(len,cl)):>8}{mixed:>7}")

Its output was:

 cutoff  links precision  recall clusters largest  mixed
     -4   1471     0.419   0.848      364      19    134
      0    865     0.687   0.816      519      16     78
      2    577     0.991   0.786      696       5      2
      4    504     1.000   0.692      754       3      0
      6    504     1.000   0.692      754       3      0
      8    495     1.000   0.680      762       3      0
     12    318     1.000   0.437      905       3      0
     18    253     1.000   0.348      964       3      0

The columns are the match-weight cutoff, the number of links at or above it, pairwise precision and recall against the generated truth, the number of clusters after grouping, the size of the largest cluster, and the number of clusters containing records from more than one true entity. Four things stand out.

First, the precision curve falls off a cliff between a cutoff of 2 and a cutoff of 0, from 0.991 to 0.687, while recall changes from 0.786 to 0.816. In this data, lowering the cutoff by two units of match weight buys 3 percentage points of recall at the price of about 30 points of precision. A score has no natural threshold; the shape of this trade-off in your own data is what you need to see before choosing one.

Second, recall never exceeds 0.848, and it reaches that only at the loosest cutoff, because blocking limited it to 0.887 and the scoring could not rescue all the rest.

Third, the cluster columns show chaining. At a cutoff of 4 and above, the largest cluster has 3 records, which is the maximum the generator allows because each entity has at most three source records. At a cutoff of 0 the largest cluster has 16 records, and the extra lines shown below report that those 16 records come from 9 different entities. Pairwise links turned into clusters by transitive closure, which means that if A links to B and B links to C then A, B, and C are one entity even if A and C are not similar at all. A few wrong links therefore do more damage than their count suggests.

Fourth, the arithmetic of one wrong link. If two correct clusters of sizes 5 and 4 are joined by a single false link, the merged cluster implies \(\binom{9}{2} = 36\) pairs, while the correct clusters account for \(\binom{5}{2} + \binom{4}{2} = 10 + 6 = 16\). One wrong edge creates 20 wrong implied pairs. Pairwise precision counts the one wrong link, while the damage to the entity view is the 20 implied pairs and every attribute and fact that now sits under the wrong master.

The review band is the remaining question. These extra lines count the pairs whose weight is from 0 up to but not including 2, the pairs below 0, and the largest cluster at cutoff 0:

band = {p for p, w in scored.items() if 0 <= w < 2}
print("review band [0,2):", len(band), "pairs, of which true", len(band & truth))
below = {p for p, w in scored.items() if w < 0}
print("below 0:", len(below), "pairs, of which true", len(below & truth))
cl = clusters({p for p, w in scored.items() if w >= 0})
big = max(cl, key=len)
print("largest cluster at cutoff 0:", len(big), "records from", len({records[i]['ent'] for i in big}), "entities")

They printed:

review band [0,2): 288 pairs, of which true 22
below 0: 1746 pairs, of which true 52
largest cluster at cutoff 0: 16 records from 9 entities

Of 288 pairs in the band between cutoff 0 and 2, 22 are true matches, about 7.6 percent. A human reviewer sending these pairs through a queue would reject most of them, and reviewing 288 pairs recovers 22 true matches. Below zero there are 1,746 pairs, of which 52 are true. This is a property of this synthetic data and these assumed weights, and it shows why the review band should be sized to reviewer capacity and sampled to estimate how many true matches it contains, instead of assumed to be a rich vein.

Checking clusters with business rules

Cluster-level invariants, in the sense of the previous post on invariants and reconciliation, catch errors that pairwise scores cannot. Splink’s cluster evaluation guide recommends graph metrics such as cluster size, density, and bridges: a bridge is an edge whose removal splits a cluster in two, and bridges are possible false positives, especially when they join two highly connected groups. The documentation adds that cluster size should be judged against what is plausible for the dataset, and that a large but dense cluster is less suspicious than a large and sparse one. In the synthetic data there is a simpler rule available: if each source system is itself deduplicated, a cluster should contain at most one record per source. The following check applies it and also counts mixed clusters, which can be computed only because the truth is known.

for cut in (0, 2, 4):
    cl = clusters({p for p, w in scored.items() if w >= cut})
    dup_src = sum(1 for c in cl if len([records[i]["src"] for i in c]) != len({records[i]["src"] for i in c}))
    mixed = sum(1 for c in cl if len({records[i]["ent"] for i in c}) > 1)
    both = sum(1 for c in cl if len({records[i]["ent"] for i in c}) > 1
               and len([records[i]["src"] for i in c]) != len({records[i]["src"] for i in c}))
    print(cut, "clusters with two records from one source:", dup_src,
          "mixed clusters:", mixed, "mixed AND flagged:", both)

0 clusters with two records from one source: 73 mixed clusters: 78 mixed AND flagged: 73
2 clusters with two records from one source: 2 mixed clusters: 2 mixed AND flagged: 2
4 clusters with two records from one source: 0 mixed clusters: 0 mixed AND flagged: 0

At cutoff 0, the rule flags 73 of the 78 mixed clusters without ever seeing ground truth, and at cutoff 2 it flags both. That is a strong signal, but it holds because the generator guarantees one record per source per entity. In real systems a source can contain true duplicates, and the rule then flags correct clusters. The general lesson is to look for domain constraints that a correct clustering must satisfy and to measure how many violations a candidate threshold produces. Violations are evidence of false links, never proof, and the absence of violations does not mean the clusters are correct.

Survivorship

Resolution produces groups and an identifier. It does not say what the combined record should contain. When the records in a group disagree, a survivorship rule decides which value the consolidated record shows. The same entity can carry three names, two emails, and one phone number, and a choice has to be made for each field.

The survivorship entry lists four bases for choosing a value and their failure modes.

Basis	Claim	Where it fails
Source authority	One system is where this field is legitimately maintained	The authoritative system is stale, or authority was assigned by diagram and not by practice
Recency	The most recently updated value is the current one	A careless edit looks like a real change; a bulk migration makes one source look newest
Completeness	A populated value beats a null; a fuller value beats a partial one	Longer is not more correct; a non-null value can be wrong
Verification	A value confirmed by a process beats an unconfirmed one	Rarely available for every field, and confirmations expire

No basis is right in general, so the rules belong per field, and they are best written as an ordered fallback: take the verified value, then the authoritative source’s value, then the most recent non-null. Writing the chain explicitly prevents the last implicit step, whatever the load order happened to leave, from becoming the rule.

A small illustrative example with invented data shows the mechanics. Three source records describe one entity.

Field	crm (updated 2026-01-05)	billing (updated 2026-02-11)	support (updated 2026-03-02)
Name	Jo Smith	Joan Smith	J. Smith
Email	jo@example.test (unverified)	joan.smith@example.test (verified)	(empty)
Phone	+00 555 0100	(empty)	cleared by the customer

Suppose the rules are: name from billing because it issues invoices, email from the verified value, then the most recent, and phone from the most recent record, with a clear treated as a value. The consolidated row is name “Joan Smith” with provenance billing, email joan.smith@example.test with provenance billing, verified, and phone empty with provenance support, cleared. If the phone rule treated an empty value as unknown and fell back to the most recent non-null value, the consolidated row would show the CRM phone and silently restore a number the customer asked to remove. The survivorship rule entry makes the same point: a clear has to arrive as its own state, distinct from missing, and an authoritative clear must not be filled from an older and lesser source.

Provenance per field is what makes the result inspectable. A disputed value becomes a lookup of which source and which rule produced it. It also exposes bad rules in aggregate: if one source wins every field, the rule is more likely a bug than a fact about the source. Version the rules and date the changes, because a rule change rewrites derived values, and a report run last quarter and rerun today can differ for no reason visible in the data. A rule change that moves published values is a restatement and should be announced as one.

Keep these two steps apart in code and in storage. Matching decides membership of a group; survivorship decides the values. When a wrong match is discovered, you correct the membership and rederive the values, and the rules do not change.

Corrections

Automatic matching makes mistakes, and people find some of them. The design question is how a correction survives the next automated run.

A manual correction is a decision with a scope. The two useful forms are must-link, which says that two source records are the same entity regardless of score, and cannot-link, which says they are not. Each decision should be stored as data with who made it, why, which records and fields it covers, from when, and until when or until what condition. The next run reads the decisions in force and applies them before clustering. A pipeline that recomputes everything from scratch and quietly discards human corrections will be discovered by the person who made them.

Cannot-link raises an algorithmic issue. If A links to B and B links to C by score, and a steward has declared A and C different, transitive closure puts all three in one cluster and contradicts the decision. A constrained clustering step has to decide which link to cut, and the cut can be guided by the link weights and the graph structure: a weak bridge is a better cut than a link inside a dense group. The simple closure used in the experiment above has no such step, and any production pipeline with cannot-link decisions needs one.

A correction applied through a crosswalk

The following Python and SQLite code is a minimal sketch of the structure. A first run over-merges three source records into one master. A steward records a cannot-link decision between two billing accounts, and a second run publishes a new crosswalk run that honors it. Payment facts keep the source key pair and reach the master through the crosswalk at read time, so the correction changes results without touching any fact row.

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE source_record(source_system TEXT, source_id TEXT, name TEXT, email TEXT, updated TEXT,
                           PRIMARY KEY (source_system, source_id));
CREATE TABLE decision(decision_id INTEGER PRIMARY KEY, kind TEXT CHECK (kind IN ('must_link','cannot_link')),
                      a_sys TEXT, a_id TEXT, b_sys TEXT, b_id TEXT, who TEXT, why TEXT, active INTEGER DEFAULT 1);
CREATE TABLE crosswalk(source_system TEXT, source_id TEXT, master_id TEXT, rule TEXT, run_id INTEGER,
                       PRIMARY KEY (source_system, source_id, run_id));
CREATE TABLE payment_fact(payment_id TEXT PRIMARY KEY, source_system TEXT, source_id TEXT, amount_minor INTEGER);
""")
db.executemany("INSERT INTO source_record VALUES (?,?,?,?,?)", [
  ("crm","1001","Jo Smith","jo@example.test","2026-01-05"),
  ("billing","1001","Joan Smith","joan.smith@example.test","2026-02-11"),
  ("billing","4420","Jo Smith","jo@example.test","2026-03-02")])
db.executemany("INSERT INTO payment_fact VALUES (?,?,?,?)", [
  ("p1","crm","1001",1500),("p2","billing","1001",2500),("p3","billing","4420",4000)])
def publish(run_id, links):                       # links: groups of source keys judged to be one entity
    n = 0
    for g in links:
        n += 1
        for s, i in g:
            db.execute("INSERT INTO crosswalk VALUES (?,?,?,?,?)", (s, i, f"M-{run_id}{n}", "rule:email", run_id))
publish(1, [[("crm","1001"),("billing","1001"),("billing","4420")]])      # run 1 over-merges
def revenue_by_master(run_id):
    return db.execute("""SELECT x.master_id, sum(f.amount_minor) FROM payment_fact f
        JOIN crosswalk x ON x.source_system=f.source_system AND x.source_id=f.source_id AND x.run_id=?
        GROUP BY 1 ORDER BY 1""", (run_id,)).fetchall()
print("run 1:", revenue_by_master(1))
db.execute("INSERT INTO decision(kind,a_sys,a_id,b_sys,b_id,who,why) VALUES ('cannot_link','billing','1001','billing','4420','steward-7','different billing accounts')")
publish(2, [[("crm","1001"),("billing","4420")],[("billing","1001")]])     # run 2 applies the decision
print("run 2:", revenue_by_master(2))
print("sources untouched:", db.execute("SELECT count(*) FROM source_record").fetchone()[0], "rows")

run 1: [('M-11', 8000)]
run 2: [('M-21', 5500), ('M-22', 2500)]
sources untouched: 3 rows

Run 1 attributes 8,000 minor units to a single master, which is the sum of three payments from what are in truth two entities. Run 2, after the decision, splits that into 5,500 and 2,500 without a single source row or fact row being changed. The code is a sketch: the second run’s groups are written by hand to stand in for a constrained clustering step, the master identifiers are regenerated per run, and a real pipeline needs the stable-identifier policy described next. What it demonstrates is the shape of the design, namely a crosswalk keyed by run, facts that keep source keys, and decisions held as data.

Stable identifiers

Consumers store the master identifier, so it must not change without a record. When two clusters merge, one identifier has to survive and the other has to be retired with a pointer to the survivor. When a cluster splits, the identifier stays with one part, usually the larger or the one containing the oldest records, and the other parts receive new identifiers. An identifier map records each retirement and each split, so a fact that stored an old identifier can be followed to its current one. The policy is a design choice with trade-offs: keeping the older identifier favors continuity of history, while keeping the identifier of the larger part favors the majority of facts. Measure identifier churn, the share of source records whose master identifier changed between two runs, because a sudden jump means a rule or threshold change or a data problem. The master identifier should be issued by the pipeline, not borrowed from a source system whose own identifiers may be retired or reused on its own schedule, as the primary and foreign keys entry warns. Related ideas for warehouse keys appear in natural keys and surrogate keys, and for history in slowly changing dimensions.

Data also arrives later than the first run. A new source record can link two previously separate clusters, which merges them, so clusters are not monotone over time. A pipeline therefore needs the same handling for late data that the late data and lookback entry describes for aggregates: a defined rule for how a new record can change an existing entity, and a restatement when it does.

Merge reversibility and quality

What reversible means

A merge is reversible when you can restore the state as if the wrong link had never been made, or at least restore the corrected state. That requires four things to be true.

1. Source records are retained and never overwritten. If the sources were replaced with merged values, a matching error is permanent data loss, which no later governance repairs.
2. The grouping is stored, not implied. A crosswalk row per source record, with the reason and the run, lets you change one row and rederive.
3. Facts keep a way back to their source identity. The primary and foreign keys entry distinguishes two designs. Facts that keep the source key pair and resolve through the crosswalk change as soon as the crosswalk changes. Facts that store the master identifier directly need a data migration for a re-link, and a fact that holds only the master identifier with no recoverable source identity cannot be split after a merge because nothing remains to say which side each row came from.
4. Decisions are logged. The log answers why two records were joined, who unjoined them, and what is in force.

Reversal still has limits. Rederiving a view does not undo actions already taken on the wrong view, such as a message sent to the wrong person or an access granted. It does not restore reports that were published. Reproducing the record that was published on a past date needs the inputs, the crosswalk state, the rule versions, and the decisions in force on that date, and not the rule versions alone. The golden record entry makes the same point: a derived view is a good operational convenience and a poor system of record for legal or contractual evidence, where the evidence is the source document and the system that recorded it.

An unmerge, as an operation, has a checklist: change the crosswalk rows for the affected source records, rederive the consolidated records for every master touched, repair or remap facts that stored the old master identifier, rebuild aggregates that counted the merged entity once, write the identifier map entries, record the decision, and announce the restatement to consumers of the affected numbers. Erasure requests add a further constraint, because deleting a person must reach the crosswalk, the decision log, and derived copies as well as the sources, and the post on access, retention, and erasure is planned to cover how deletion propagates through derived copies.

Measuring quality continuously

Quality of a master pipeline is a set of measurements, each answering a different question, and none replaces the others.

Measure	Question	Needs
Pairwise precision and recall on a labeled sample	How often are links right and how many are missed?	A sample that includes the hard region near the threshold
Pair completeness of blocking	Is blocking dropping true matches?	Labeled pairs
Cluster size distribution, density, and bridges	Is chaining happening?	The link graph
Domain-rule violations	Do clusters break constraints a correct clustering must satisfy?	A stated constraint such as one record per source
Identifier churn	Are entities stable between runs?	The identifier map
Override re-application	Are manual decisions still honored?	The decision log and the crosswalk
Golden record completeness and consistency	Are surviving values complete and coherent?	Field-level rules

Structural invariants from the previous post apply directly. Every source record appears in exactly one cluster per run, so the crosswalk has exactly one row per source record per run, which can be checked with a count and a uniqueness test. The crosswalk covers every source record, which is a reconciliation between the crosswalk and the sources. No cluster contains a pair covered by a cannot-link decision. Every surviving field value matches a value in one of the member records. A reconciliation of counts alone does not catch a silent change in grouping, in which the pipeline begins to group records differently while every row looks valid, and for that the labeled sample and the approved match cases have to be checked against each run, as the golden record entry notes.

Failure modes and limits

• Over-merging from chaining. Transitive closure converts a few wrong links into large wrong clusters. Use cluster size and density checks, domain rules, and constrained clustering.
• A threshold set by a duplicate-rate target. A program measured on duplicate counts is pushed toward over-merging, since duplicates are easy to count and wrong merges are not. Set thresholds from the cost of each error and measure wrong merges with a labeled sample.
• Blocking that hides misses. A good reduction ratio can coexist with a poor pair completeness. Measure both.
• Scores treated as probabilities. A model’s output is a probability only if its \(m\), \(u\), and prior are good estimates and the independence assumption is not badly violated.
• Destructive merges. Overwriting sources with merged values turns every error into a recovery problem.
• Rules that live only in a mapping tool. A survivorship rule that nobody can find is a rule nobody can argue with. Keep the rules, their owners, and their versions in a place people read.
• Evaluation on convenient data. A sample that excludes the hard region overstates quality. Evaluate by segment, because a pipeline can perform well overall and badly for a group of names or a source.
• Linking increases exposure. Joining records across systems can reveal information about a person that no single system held. This is an inference, and it is a reason to restrict access to the consolidated view and the crosswalk and to apply the same retention and erasure rules to them as to the sources.
• No shared meaning of “same.” Whether two accounts at one household, or a person and a business they own, are the same entity is a business definition, not a property of the data. Decide the entity definition first and write it down, as you would for a metric.

Entity resolution is also optional when a good shared identifier exists. If an identifier is issued by a single authority, covers the same population, and is accurate and never reused, join on it and use matching only for the records that lack it. The identity stitching entry describes a related analytical case, linking observations under different identifiers to a chosen identity under a stated policy, where the result is a profile and not proof of a unique human.

References

• Splink documentation: The Fellegi-Sunter model (read 3 October 2026)
• Splink documentation: What are blocking rules? (read 3 October 2026)
• Splink documentation: Evaluation overview (read 3 October 2026)
• Splink documentation: Graph metrics for cluster evaluation (read 3 October 2026)
• AWS Entity Resolution User Guide: What is AWS Entity Resolution? (read 3 October 2026)