<

Golden Record Registry

Summer 2025

A Golden Record Registry (GRR) pipeline was a problem I was introduced to and tasked with solving as a major piece of my 2025 internship with the Cincinnati Reds. The core of the problem is mathematically matching off seemingly duplicate entries into one "master" record from a singular dataset. The article I've written here will be a simplified example of steps I took to complete this as well as explanations behind terms used and decisions made along the way.


The problem we are solving:

where the data came from name zip code gender
CRM Jake Sanders 45202 Male
customer billing Jake Sanders 45202 Female
account sign-up Jacob Sanders 45022 Male

These 3 distinct entries are all the same person. How can we mathematically remove duplicates?

A human can look at these and think "these are obviously the same guy", but how can a computer or an algorithm? Across tens of millions of rows of data, eyeballing a solution is impossible. This problem is called record linkage, or if you are managing this within a single dataset then deduplication. The entire approach is probabilistic. We can never be 100% sure that two records are the same person, but instead we calculate a probability of the two records being the same person. If the probability of our two records is higher than a preset threshold, they will be matched off and deduplicated. The end result is a golden record, which acts as one clean and trusted profile per person, built from the best pieces of all the scattered copies. The system that produces it is the GRR - Golden Record Registry.


Step 1 - Clean and Standardize our data

Before we can compare anything, the data needs to be put in the same shape. Different systems format their data differently: one might store my name as " Jake " but another could store it as "JAKE". These differences need reconciled because a computer cannot tell that these are the same. Our first job is called grrMAP which takes all of our raw records and rewrites them into a clean, uniformed table. Steps:

We run grrMAP once per source system to compile the results into one shared table, which I will call the map table. At this point, there is one extra column called GRR_ID which is currently left empty. The purpose of this column is to eventually map it as an answer to "which real person is this?", which we don't know yet. Here is a quick code example: 

def grr_map(raw, system, id_col):
    out = {}
    out['xref']   = raw[id_col]                  # the record's unique id
    out['system'] = system                       # where it came from
    out['grr_id'] = None                         # the solution, filled in later
    out['name']   = raw['name'].strip().lower()  # standardize text
    out['zip']    = raw['zip'].zfill(5)          # pad to 5 digits
    return out


Step 2 - Don't compare everyone to everyone (blocking)

Now we want to compare records against each other to find duplicates. The easiest and most obvious solution? Compare every record against each other! Unfortunately, this is the most expensive and least feasible solution, at least computationally. With only 1 million records, we would be looking at 500 billion comparisons, far too many. The fix is a blocking rule. The thought process is that most records are very obviously NOT the same person, so don't bother comparing them. A blocking rule groups records together based on some cheap shared values, and we only compare records contained within the same group. A blocking key should be something that true duplicates will basically always agree on, but still splits the data into manageable chunks. Common choices are:

For example, blocking on the first letter of the last name would put every "Sanders" and "Sanderson" in the same group and they would be compared to each other. They would never get compared to other entries such as those with last names of "Highlands" or "Kyne" because the first letter of the last name does not match. Even with a few simple rules, we can drastically reduce the number of comparisons we are making into much more practical chunks.

Note of caution: a blocking rule sets a ceiling on what you can find. If two records of the same person happen to fall through and land in different blocks, you'll never compare them, so you can never match them off. Pick keys carefully.

The output of this step is a list of candidate pairs, which are pairs of records worth comparing to each other.


Step 3 - Compare each pair, by field

For each candidate pair, we iterate by field and ask a simple question of do these agree or not? We can use a simple binary table of 1s (agreements) and 0s (disagreements). The result is what we call a comparison vector table which is a compacted summary of which data is matching. For the example walkthrough, let's assume we have the data below:

name_1 name_2 zip_1 zip_2 gender_1 gender_2 name_match zip_match gender_match
Jake Sanders Jake Sanders 45202 45202 Male Male 1 1 1
Jake Sanders Bryce Dugar 45202 45123 Male Male 0 0 1
Jacob Sanders Jake Sanders 45202 45201 Male Male 0 0 1
Jake Sanders Jacob Sanderson 45202 45123 Male Male 0 0 1
Jake Sanders Jenna Sandington 45202 12345 Male Female 0 0 0
Jake Sanders Jake Sanders 45202 12345 Male Male 1 0 1

But asking "do they agree?" is tricky for human input text, because people notoriously make typos.


Handling typos: similarity instead of exact match

Sanders and Sanderz are very likely meant to refer to the same last name with a human making a typo. If we matched off last name on exact-matches only, this (and thousands of real-world examples) would immediately get thrown out, and we don't want that. Instead of demanding exact equality, we can measure how similar two strings are on a scale from 0 (nothing alike) to 1 (identical). Then, we can set a passing threshold to where if the similarity score between two strings is above the threshold, they can be matched off. There are two common ways to measure similarity between strings:

For step 3, a field "agrees" if it is an exact match or its similarity score clears a preset threshold (if, for that field, we want to use similarity). For most scenarios, we set a threshold of 0.85. After changing the algorithm to use Jaro-Winkler rather than direct matching, notice how our vector table changes:

name_1 name_2 zip_1 zip_2 gender_1 gender_2 jaro-winkler score name_match zip_match gender_match
Jake Sanders Jake Sanders 45202 45202 Male Male 1.0 1 1 1
Jake Sanders Bryce Dugar 45202 45123 Male Male 0.56 0 0 1
Jacob Sanders Jake Sanders 45202 45201 Male Male 0.89 1 0 1
Jake Sanders Jacob Sanderson 45202 45123 Male Male 0.86 1 0 1
Jake Sanders Jenna Sandington 45202 12345 Male Female 0.63 0 0 0
Jake Sanders Jake Sanders 45202 12345 Male Male 1.0 1 0 1

Step 4 - How much does each agreement actually mean?

This step is the core of the whole system: Not all agreements are equally meaningful. If two records agree on last name, that's strong evidence the two records indicate the same person, as last names are reasonably distinctive. But if two records agree on gender, that's much weaker evidence that two records indicate the same gender: you have a 50/50 chance of sharing a gender with a random stranger, so it barely tells us anything. We need a way to mathematically express how much an agreement on each field is worth. Insert m/u probabilities.

Read them as a pair: a field where the match rate (m) is high and coincidence rate is low is a powerful field for telling people apart. A field where m and u are close together (like gender, where m = 0.99 but u = 0.5) carries far less weight, because agreement is nearly as common among strangers as it is true matches. So, where do our M and U numbers come from? We need both for every field, which we can store in a config table. There are two ways to get them:

If you have labeled examples where historical pairs have already been labeled "matching" or "non-matching" then you can just count:

If you have no labels (usual), we can use an algorithm called Expectation Maximization. The thought process is that even though we don't know the true matches, we can start with an educated guess for our m/u probability, use that number to estimate the m/u probability, then recursively plug the results back into the algorithm. We would continually repeat this process until the numbers stop changing, or we reach a point of diminishing return. The algorithm in question is looping over 2 steps:

  1. Expectation: with the current m/u numbers, estimate the probability that each pair is a match
  2. Maximization: use the estimated probability results to recompute better m/u numbers


Step 5 - Turn our evidence into a score

Now, we can combine the clues into a single score for each pair. We will be using Bayes Factor, which indicates how much a single clue tips the scale. A Bayes Factor is a ratio that says how much more (or less) likely an observation is if the records match versus if they don't. For a field that agrees, it's m_prob / u_prob. For example, if we have a matching field where the m_prob = 0.95 and u_prob = 0.01, that would give us Bayes Factor = 0.95 / 0.01 = 95. The interpretation is that seeing this agreement is 95 times more likely if the records are the same person than if they aren't, which is very strong evidence indicating a true match. We can use this strategy in the reverse as well. Suppose two records disagree on gender. Among true matches, a gender mismatch is very rare (say m = 0.01), but among strangers gender coincidentally matches about half the time (u = 0.5). The Bayes Factor for this situation would be Bayes Factor = 0.01 / 0.5 = 0.02, or 1/50. This is 50 times less likely if they're a match, strong evidence against this pairing. Each field within a single comparison gives us a Bayes Factor, standardized at 1: above 1 pushes towards "same person", below 1 pushes toward "different entities".


To score a pair, we want to combine all the Bayes Factors together into one final result, with one caveat. Multiplying these numbers together is numerically fiddly and can get unstable. We resolve for this by taking the logarithm of each Bayes Factor in log base 2, because we can then turn our multiplication between Bayes Factors into an addition between logarithms, which is much faster, easier, and stable. The log2(Bayes Factor) of each field is its match weight. A positive number supports a match while a negative number will argue against one. Ideally, you would have some sort of lookup table where values are already calculated for you. To do this, run the previously discussed Expectation-Maximization algorithm for each field to find the Bayes Factor, then convert that to a match weight, which again is the logarithm of the Bayes Factor. The exact m/u probabilities should ideally be learned from labeled data or estimated with EM. The values below are illustrative only. In general, a standardized full-name match should contribute more evidence than a ZIP-code match, because names are usually more discriminative than ZIPs. We will use the below table for our lookup values:

column name scenario m_prob u_prob bayes factor match weight
name match 0.90 0.0002 4500 12.14
name non-match 0.10 0.9998 0.1 -3.32
zip code match 0.55 0.005 110 6.78
zip code non-match 0.45 0.995 0.45 -1.14
gender match 0.99 0.50 1.98 0.99
gender non-match 0.01 0.50 0.02 -5.64

However, we need to have some sort of baseline to compare our new information to. Before looking at any fields, what is the baseline chance that two randomly chose records are the same person? This is our prior probability and if your gut tells you it's going to be tiny, you'd be correct. If you have 1 million records in a database, you might expect 1 in 20,000 pairs to be a duplicate. In that case, our prior probability would be 1 / 20,000 = 0.00005. We express the prior in the same logarithm units as everything else (called the prior_log) so that it too can be added straight into the score as our starting offset. For our working example, we will use a highly forgiving number of 1 in 100, or 0.01, or -6.64. This prior score indicates our initial skepticism: pairs start out as assumed to be different, and our field evidence needs to overcome that. We express the prior in the same logarithm units as everything else (called the prior_log) so that it too can be added straight into the score as our starting offset. This prior score indicates our initial skepticism: pairs start out as assumed to be different, and our field evidence needs to overcome that.

Putting it together

The match score for a pair is:

A pair that agrees on lots of distinctive fields piles up positive weights and ends with a high score, vice versa for a pair that disagrees will pile negatively. Let's walk through the examples we have:

This is the same table from above with the Jaro-Winkler column taken out. I have added a simple ID column as well so we can keep track of the rows across tables:

PAIR ID name_1 name_2 zip_1 zip_2 gender_1 gender_2 name_match zip_match gender_match
1 Jake Sanders Jake Sanders 45202 45202 Male Male 1 1 1
2 Jake Sanders Bryce Dugar 45202 45123 Male Male 0 0 1
3 Jacob Sanders Jake Sanders 45202 45201 Male Male 1 0 1
4 Jake Sanders Jacob Sanderson 45202 45123 Male Male 1 0 1
5 Jake Sanders Jenna Sandington 45202 12345 Male Female 0 0 0
6 Jake Sanders Jake Sanders 45202 12345 Male Male 1 0 1

All we need to take from this table is the final 3 columns in which we have calculated whether the fields from the two entries are matches or not. That would transform it to:

PAIR ID name_match zip_match gender_match
1 1 1 1
2 0 0 1
3 1 0 1
4 1 0 1
5 0 0 0
6 1 0 1

Now, we can add in our lookup values for the corresponding match weights.

PAIR ID prior_log name_match name_weight zip_match zip_weight gender_match gender_weight
1 -6.64 1 12.14 1 6.78 1 0.99
2 -6.64 0 -3.32 0 -1.14 1 0.99
3 -6.64 1 12.14 0 -1.14 1 0.99
4 -6.64 1 12.14 0 -1.14 1 0.99
5 -6.64 0 -3.32 0 -1.14 0 -5.64
6 -6.64 1 12.14 0 -1.14 1 0.99

Take away the match boolean columns, and add together all the values to get the match weight:

PAIR ID prior_log name_weight zip_weight gender_weight match_weight
1 -6.64 12.14 6.78 0.99 13.27
2 -6.64 -3.32 -1.14 0.99 -10.11
3 -6.64 12.14 -1.14 0.99 5.35
4 -6.64 12.14 -1.14 0.99 5.35
5 -6.64 -3.32 -1.14 -5.64 -16.74
6 -6.64 12.14 -1.14 0.99 5.35

Additionally, we can actually get the exact probability from these match weights! The formula is p = (2^w) / (1+2^w) with w as the match weight:

PAIR ID match_weight probability
1 13.27 0.9998
2 -10.11 0.0009
3 5.35 0.9760
4 5.35 0.9760
5 -16.74 0.0000
6 5.35 0.9760

Step 6 - Making a Decision

And that is all the math required! But now we need to make a decision. A score is just a number, and a probability is just a percentage. We need to set thresholds:

Any scores that fall below the review threshold are confidently non-matches and assumed to be different people. You could technically compute statistically optimal thresholds, but we can reasonably pick values ourselves. In my scenario, we decided on ~95% for the Match Threshold and ~85% for the Review Threshold. The pairs that fall through and land in the review section are called fallout, and we have 2 types:

Step 7 - Group records together and assign a GRR_ID

So far we've compared records two at a time. But, consider: A matches to B, and B matches to C. We can denote that A matches to C, and we don't have to waste time running it through the calculation algorithm. How can we extrapolate this? The way to solve this is setting up a node structure. Picture every record as a dot, and draw a line between any two dots that we deem are a match. Any dots that are connected whether it be directly together or through a chain will form a cluster, and each cluster is one person. For each cluster, we can assign it a unique GRR_ID and label every record contained within the cluster with that ID. Remember the empty column from Step 1? This step is filling that in, and we will call it grrScore.

An important note here is that we need to pick a stable, canonical ID for each cluster and reuse it every time this step is ran. If IDs get shuffled every re-run, anything that relies on GRR_ID (such as a master table acting as a source of truth) would break. This would be an upsert approach where we "update if it exists, insert if it doesn't" rather than completely re-writing. A simplified version of the function could look like this: 

# matches are "edges"; records are "nodes"; each connected cluster = one person
import networkx as nx

G = nx.Graph()
G.add_nodes_from(all_record_ids)          # everyone, even loners
G.add_edges_from(matched_pairs)           # a line per confident match

for cluster in nx.connected_components(G):
    person_id = min(cluster)              # stable canonical id
    for record in cluster:
        assign(record, grr_id=person_id)

Step 8 - Build out the Golden Record

Each person's cluster may contain several rows that disagree on the details. CRM might have one zip code while the Billing table has another. There needs to be some sort of rule put in place where we assemble one trustworthy profile per person. The key to this is we set these rules up by field, not by row. For each output field, we decide which source system to trust the most, using a ranking table. Maybe CRM is most reliable for names, but Billing is more reliable in getting addresses. The golden record would take the name from CRM and the ZIP code from Billing, cherry-picking the best source for each field rather than copying the entire row from the upstream source whole. We called this step hierarchy, and a simple example would look like this:

field source
name CRM
zip Billing
email Account Creation
email Account
last login Account
company Account
phone CRM
[social media address] CRM

After that, we would have another job which collapses everything into one row for the specific user's GRR_ID and writes the golden records into our final table which we can use as a source of truth. Like before, it will use an upsert process so in the event of re-runs it will not reset a person's GRR_ID or create duplicated. And finally, we have arrived at our goal: one clean, deduplicated, best-of-all-sources record per real person.

TLDR

  1. Clean and Standardize our data
  2. Act on a blocking rule to limit computations
  3. Compare our pairs by each field
  4. Calculate m/u probability for each field
  5. Turn those probabilities into scores
  6. Run the scores through a threshold to get matches
  7. Chain our matches into clusters
  8. Build out our master table for each cluster

Every concept is equally important and relied upon by the other steps.