The golden record
- Lesson 24
- 40 min
- Module 7: From pairs to entities
Where we are
We now have entities that we trust. Each entity still has several source records with different values. In this lesson, we build a golden record with merge rules per field, keep the history, and undo a merge.
Lesson 24 of 29 · Entity Resolution and Record Matching: Is This the Same Thing?The golden record
1. The matching problem
2. Clean first, then match exactly
3. Blocking: do not compare everything
4. Compare records: similarity measures
5. Probabilistic linkage
6. Learned matching: classifiers, embeddings and language models
7. From pairs to entities
8. Run matching in production
What you will be able to do
Give each entity one golden record: one best value for each field, with the record it came from. Choose a merge rule for each field. Keep the source records unchanged and write every change to a history, so that a wrong merge can be undone.
NotebookBuild golden records in a notebook
The notebook clusters the pairs of Module 2’s rules safely, then builds a golden record for every entity with more than one record. You write the rule “the newest value wins”. Then you count which source wins each field, and you undo one merge. Its setup cells download the data, so it needs no local setup.
Colab and Kaggle open your own copy. They need a free Google or Kaggle account. Stuck? See the solution notebook.
Before you startWhat to know and to have ready
What you need to know
- Safe clustering, from Safer clustering.
- The three sources and their columns, from Meet the records.
What you need on your computer
- The project
larkfield-matchwith the files of Module 7. Or use the notebook only.
One card for the catalogue
Section titled “One card for the catalogue”A library has three cards for one book. One card has the full title but an old shelf number. One card has the new shelf number but a short title. One card has a spelling mistake in the author’s name.
The librarian writes one new card for the catalogue. For each line, she picks the best source. The title comes from the card with the full title. The shelf number comes from the newest card, because books move. The author’s name comes from the two cards that agree.
She does not throw the old cards away. She puts them in a drawer behind the new card, with a note: “made from these three cards”. If a reader later says, “That third card is a different book,” she can take it out again.
TermLarkfieldA made-up Canadian online shop for home and garden products. It is not a real company. All Nextia courses use its help desk and data as the example.Learn more Meet Larkfield needs the same thing for every customer.
The problem: which value do we use?
Section titled “The problem: which value do we use?”Here is Valérie Tremblay, the customer we met in Module 1. After safe clustering, her three records are one entity, {ex.entity_id}. But they do not agree:
| Record | Source | Name, as typed | Phone, as typed | Postcode | Date of birth |
|---|---|---|---|---|---|
| S-009232 | shop | Valérie Tremblay | +1 514 555 8296 | H6P 3H1 | 1975-01-31 |
| L-010072 | loyalty | Valérie Tremblay | 5145558296 | H6P 3H7 | 31/01/1975 |
| G-000001 | garden | TREMBLAY, VAL | 5145558296 | (in the address) | (empty) |
TermOmarLarkfield’s product manager. He asks the business questions: what a change costs, and what it gives the shop. wants to send her one email. Ivana wants to print one address label. Which name, phone and postcode do they use? The shop’s postcode H6P 3H1 has a typing error. The garden centre’s name is short and has no accent. The loyalty record has no street address.
Picking one whole record does not work. Each record is the best for some fields and the worst for others.
The idea: one merge rule per field
Section titled “The idea: one merge rule per field”A golden record is one record per entity, built from all of its records. People also call it a “master record”, a “best record” or the “single customer view”. It holds one value for each field, and it says which record each value came from.
A merge rule decides which value wins for one field. Data stewards also call these rules survivorship rules: they decide which value survives. Four kinds of rule are common:
- Newest: the value from the most recent record. Good for things that change: an email, a phone, an address.
- Most common: the value that most records agree on. Good for things that never change, where a different value is a typing error: a date of birth.
- Most complete: the longest or fullest value. Good for a name with a middle name, or an address with a unit number. Careful: longer is not always right.
- Most trusted source: the value from the source with the best data entry. Good when one source checks its data, for example a form that confirms the email.
Rules can be combined: “most common; on a tie, the most trusted source”.
Larkfield’s rules
Section titled “Larkfield’s rules”The project uses these rules. They are our choice, written in golden.py:
| Field | Merge rule | Why |
|---|---|---|
| name | the most common full name; on a tie, the most trusted source (shop, loyalty, garden centres) | names are typed in many ways, and the shop’s form is the most careful |
| email, phone | the newest record that has one | people change them, so the newest is most likely to work |
| address and postcode | the newest record that has a street address; else the newest postcode | people move |
| date of birth | the most common one (a day and month swap counts as the same date) | it never changes |
“Newest” uses the record’s creation date: when the customer opened the shop account, joined the loyalty programme or first visited a garden centre.
Valérie’s golden record, field by field
Section titled “Valérie’s golden record, field by field”Text description of the diagram
The picture is a table with one row per field. Its columns are the field, the three source records from the newest to the oldest, the merge rule, and the golden value. The loyalty record L-010072 is the newest; the garden-centre record G-000001 is next; the shop record S-009232 is the oldest. Name: the shop and loyalty records both say “valerie tremblay”, and the garden centre says “val tremblay”. The most common name wins. The shop and loyalty records tie, and the shop is the more trusted source, so the name comes from S-009232. Email: all three are the same; the newest record, L-010072, gives it. Phone: all three are 5145558296 after cleaning; it comes from L-010072. Address: the loyalty record has no street address, so the newest record with one, the garden centre G-000001, gives “6760 rue des herons, st eloi du lac QC” and the postcode H6P3H7. Date of birth: two records say 1975-01-31 and the garden centre has none; it comes from L-010072.
Look at the address row. The shop’s postcode has a typing error. The rule did not need to know that. The garden centre’s record is newer and has a street address, so it wins, and it has the right postcode. A rule does not find the truth. It picks a value by a reason that you can explain. Sometimes the reason picks a wrong value.
Keep the sources, keep the history
Section titled “Keep the sources, keep the history”The golden record is a new row. The three source records never change. This matters for three reasons:
- Undo. If the merge was wrong, you can take a record out and build the golden record again.
- Audit. You can answer “where did this phone number come from?” with a record ID.
- New rules. If you change a merge rule, you rebuild every golden record from the sources.
Every merge, split and deletion goes into a history: a file with one line per event. The project writes it to outputs/history.jsonl, in the JSON Lines format: one JSON object per line.
Stable entity IDs
Section titled “Stable entity IDs”Each entity needs an ID that other systems can store: the email system, the loyalty system, the fraud team. The project names a new entity LK- plus its smallest record ID, so Valérie is {ex.entity_id}. When the project builds the entities again, a record keeps the entity ID that it had before. So an ID does not change only because we ran the job again. Module 8 needs this when new records arrive every day.
Build the golden records
Section titled “Build the golden records”match golden38,908 entities from 60,108 records (16,513 with more than one record); 0 with two dates of birthSaved outputs/entities.csv and outputs/golden.csv38,908 entities from 60,108 records, against 37,500 true customers. 16,513 entities have more than one record, so they needed merge rules. No entity has two dates of birth: safe clustering removed those clusters before the golden records were built.
Look at one golden record and its sources:
match golden --show LK-G-000001LK-G-000001: 3 records from garden,loyalty,shop name valerie tremblay from S-009232 email tremblay.valerie88@example.net from L-010072 phone 5145558296 from L-010072 address 6760 rue des herons, st eloi du lac QC from G-000001 postcode H6P3H7 from G-000001 dob 1975-01-31 from L-010072 G-000001 TREMBLAY, VAL | tremblay.valerie88@example.net | 5145558296 | 6760, r. des Hérons, Saint-Éloi-du-Lac QC H6P3H7 L-010072 Valérie Tremblay | tremblay.valerie88@example.net | 5145558296 | H6P 3H7 S-009232 Valérie Tremblay | tremblay.valerie88@example.net | +1 514 555 8296 | 6760, r. des Hérons, Saint-Éloi-du-Lac QCEach field says where it came from. The three source records are below, as the sources typed them.
Undo a merge
Section titled “Undo a merge”Ivana reviews a merge and finds it wrong. She takes one record out. The project calls this a split. Here we take Valérie’s shop record out, only to show how it works. Her merge was right.
Text description of the diagram
The picture has a “before” box and two “after” boxes. Before: entity LK-G-000001 holds three records, G-000001, L-010072 and S-009232. An arrow labelled “match undo S-009232” leads to two boxes. The first, LK-G-000001, keeps G-000001 and L-010072. The second, a new entity LK-S-009232, holds S-009232 alone. Below the boxes are two text lines. The history line says: event split, record S-009232, from entity LK-G-000001, new entity LK-S-009232. The cannot-link file gets two pairs: G-000001 with S-009232, and L-010072 with S-009232.
match undo S-009232S-009232 is now entity LK-S-009232; the other 2 record(s) stay in LK-G-000001.Recorded in outputs/history.jsonl and outputs/cannot_link.csv (safe clustering will keep them apart).Three things happened:
-
The record moved. S-009232 is now its own entity,
{u.new_entity}. The other two records keep the old ID,{u.others_stay_in}, so nothing that stores that ID breaks. -
The history has one more line. Read the last line of the history. The first field is the time when you ran the command; we replaced ours with “…”:
Terminal window tail -n 1 outputs/history.jsonlIn PowerShell, use
Get-Content outputs\history.jsonl -Tail 1.{"time": "…", "event": "split", "record": "S-009232", "from_entity": "LK-G-000001", "new_entity": "LK-S-009232", "others_now": "LK-G-000001"} -
A cannot-link pair for each other record.
outputs/cannot_link.csvnow says that S-009232 may never be in one cluster with G-000001 or L-010072. Safe clustering reads this file: it is the third check of the previous lesson. A link between two records of a cannot-link pair is removed before any other check.
Build the golden records again, as the nightly job would:
match golden38,909 entities from 60,108 records (16,513 with more than one record); 0 with two dates of birthSaved outputs/entities.csv and outputs/golden.csvThere is one entity more: 38,909. The model still links S-009232 to the other two records, but the cannot-link pairs win. match golden --show LK-G-000001 now shows two records, from the garden centre and the loyalty programme. The name now comes from L-010072, because the shop record is gone from the entity. The undo survives a new run. Without the cannot-link pairs, the next run would merge the records again, and Ivana’s work would be lost.
When to use it, and what it costs
Section titled “When to use it, and what it costs”- Build golden records for the people who use the data: the marketing team, the support team, the reports. Keep the source records for the matcher and for audits.
- Choose a rule per field, and write down why. There is no best rule for every field. A rule is a business decision: Omar and Ivana should agree with it.
- A rule can pick a wrong value. “Newest” trusts a new typing error. “Most common” fails when a wrong value was copied twice. Check a sample of golden records by hand, and measure how often each source wins.
- Never overwrite the sources. Storage for three records instead of one is cheap. A merge that cannot be undone is not.
- Keep IDs stable. Every system that stored an entity ID depends on it.
Common mistake
Overwrite the source records with the golden values
Symptom: TermTomásLarkfield’s AI engineer. He often suggests a quick shortcut, and the lesson shows why it fails. wants to save space. “We have the golden record now. Let us write its values back into the three source records and delete the duplicates.” A month later, Ivana finds a wrong merge of two brothers. She cannot split it: the second brother’s email, phone and date of birth are gone. Only the golden values are left.
Cause: the merge destroyed information. A golden record is a view made from the sources by rules. When the sources are overwritten, the rules cannot run again, a wrong merge cannot be undone, and nobody can say where a value came from.
Fix: keep the source records unchanged. Build golden records as new rows. Write every merge and split to a history. Remember every split as a cannot-link pair, so that the next run does not repeat the mistake.
Consequence in AI work: keep the raw data and the steps that made each derived table, not only the result. Then you can fix a step and run it again. Track lineage in SQL and Data Preparation for AI shows how to record where each value came from.
Knowledge check
Section titled “Knowledge check”Check your understanding
An entity has three records. Their emails, from the newest record to the oldest: (empty), anna.silva@example.com, anna.s@example.com. The rule is “the newest record that has one”. Which email wins, and why not the empty one?
Show the answer
anna.silva@example.com. The rule takes the newest record that has an email. The newest record has none, so it is skipped. An empty value is not information, so it never wins over a real value. The golden record also stores the ID of the record that gave the email.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Check your understanding
Why does the project take the most common date of birth, but the newest phone number?
Show the answer
A date of birth never changes. If two records disagree, one of them has a typing error, and the value that more records agree on is more likely right. A phone number does change: people move and change providers. The newest record is the most likely to have the number that works now, even if older records agree on an old one.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Check your understanding
After match undo S-009232, the next run of match golden still has the record in its own entity. What makes this happen?
Show the answer
The cannot-link pairs. match undo writes one pair for each other record of the entity to outputs/cannot_link.csv. Safe clustering reads them on every run. It removes the model’s links between those records first. A cluster that still holds a cannot-link pair through other records fails the check, and its weakest link is cut. So the split survives.
Does your answer have the main points of ours?
Sign in and enroll in the course to save your answers. Every check counts toward the certificate.
Recap
- A golden record holds one value for each field of an entity, and the record each value came from.
- A merge rule (or survivorship rule) picks the value: newest, most common, most complete or most trusted source. Larkfield: the most common name and date of birth; the newest email, phone and address.
match goldenmakes 38,908 entities from 60,108 records (true: 37,500), 16,513 with more than one record.- Never change the source records. Write every merge and split to a history, keep entity IDs stable, and remember every split as a cannot-link pair.
match undotakes one record out of its entity. It stays out on the next run.
Next, we measure the entities themselves, not only the pairs.
Next: Cluster measures