Skip to content
Français

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.

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 notebookColab · Kaggle · GitHub

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 readyAbout 40 minutes

What you need to know

What you need on your computer

  • The project larkfield-match with the files of Module 7. Or use the notebook only.

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.

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:

RecordSourceName, as typedPhone, as typedPostcodeDate of birth
S-009232shopValérie Tremblay+1 514 555 8296H6P 3H11975-01-31
L-010072loyaltyValérie Tremblay5145558296H6P 3H731/01/1975
G-000001gardenTREMBLAY, VAL5145558296(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.

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”.

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”
A table for golden record LK-G-000001. Columns: the field, the three source records from newest to oldest, the merge rule and the golden value. Name: valerie tremblay, from S-009232, by the most common name. Email: tremblay.valerie88@example.net, from L-010072, the newest record that has one. Phone: 5145558296, from L-010072. Address: 6760 rue des herons, st eloi du lac QC, H6P3H7, from G-000001, the newest record with a street address. Date of birth: 1975-01-31, from L-010072, the most common one. The winning value in each row is green.
Valérie Tremblay's golden record LK-G-000001, built field by field from her three synthetic records. Values after Module 2's cleaning. The winning value of each field is green.
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.

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.

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.

Terminal window
match golden
38,908 entities from 60,108 records (16,513 with more than one record); 0 with two dates of birth
Saved outputs/entities.csv and outputs/golden.csv

38,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:

Terminal window
match golden --show LK-G-000001
LK-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 QC

Each field says where it came from. The three source records are below, as the sources typed them.

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.

Before: entity LK-G-000001 with three records, G-000001, L-010072 and S-009232. The command match undo S-009232. After: entity LK-G-000001 keeps G-000001 and L-010072, and the new entity LK-S-009232 has S-009232 alone. Below, the history line: 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.
Undo a merge: the record gets its own entity, the history gets one line, and two cannot-link pairs stop the records from joining again.
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.

Terminal window
match undo S-009232
S-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:

  1. 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.

  2. 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.jsonl

    In 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"}
  3. A cannot-link pair for each other record. outputs/cannot_link.csv now 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:

Terminal window
match golden
38,909 entities from 60,108 records (16,513 with more than one record); 0 with two dates of birth
Saved outputs/entities.csv and outputs/golden.csv

There 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.

  • 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.

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.

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.

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.

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 golden makes 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 undo takes 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