← All Kits · SQL CASE Expression · SQL Kit

Entity Resolution: One Real Thing, Many Messy Names

How to work out which records are the same real thing, in five steps, on real uncooperative data · Part of the Analyst Prep Kit

By , data analyst · Updated

This guide walks through five steps for working out which records are the same real thing, and merging them without wrecking your data. It runs on real chart data, and it includes the two times the rules came out wrong.

Here is the problem in one example. Count the distinct artists in Billboard's public chart history and the number is wrong. "Elvis Presley" and "Elvis Presley With The Jordanaires" are the same man, and so are five other credit strings. One real-world entity, seven database strings.

Every dataset with human-entered names has this. Customers who signed up twice. "IBM" against "I.B.M." against "International Business Machines". The same supplier in two systems, spelled two ways.

The work of fixing it is called entity resolution. Matching across two datasets is record linkage. Removing duplicates inside one is deduplication. They are the same skill pointed at different situations, and it is one of the most common tasks an analyst actually gets handed.

What's here
  1. The vocabulary map
  2. Step 1: measure the fragmentation before fixing anything
  3. Step 2: build rules by preview, catch, fix, re-check
  4. Step 3: know when NOT to merge (the false-positive check)
  5. Step 4: match rates, the number that keeps you honest
  6. Step 5: clerical review — reading the leftovers
  7. The principles, distilled
  8. References

The vocabulary map

TermMeaning
EntityThe real-world thing: one artist, one customer, one company
Entity resolutionFiguring out which records refer to the same entity
Record linkageThe same problem across two datasets. "Is row 5 in file A the same person as row 90 in file B?" Formalized by Fellegi & Sunter (1969)
DeduplicationThe same problem inside one dataset
Normalization / standardizationTransforming values toward a canonical form (lowercasing, trimming, cutting suffixes) so equal things become equal strings
Match keyThe cleaned column(s) you actually join on
Match rateThe share of records that found their counterpart. This is the number that keeps the whole exercise honest
Clerical reviewHuman eyes on the records the rules could not decide. This is a formal stage of the classic framework, not an admission of failure

Step 1: measure the fragmentation before fixing anything

The worked example is Billboard Hot 100 history, 1958 to present. The goal is one clean row per artist. Before writing a single fix, size the problem. Guess the number first: how many different ways do you think one famous artist appears in a chart database? Hold that number.

Those two numbers decided everything after them. Fragmentation this widespread makes normalization rules mandatory. And any rule will touch thousands of rows, so the rules have to be tested rather than trusted.

Step 2: build rules by preview, catch, fix, re-check

The extraction rule was simple to state: the primary artist is the text before the joiner word. It was built as a CASE ladder and proven on real rows before anything else was built from it. What happened next is the lesson:

  1. First preview caught "2Pac Duet With Mopreme" → "2Pac Duet": the credit joins with " Duet With ", the rule only knew " With ". A fake artist, invented by a cleaning rule.
  2. The fix added a " Duet With " rung above " With ". In a CASE ladder, specific patterns must sit above the general patterns they contain. Re-checking the exact rows the fix targeted caught another one: "Patti Austin A Duet With James Ingram" became "Patti Austin A". So it needed one more rung, " A Duet With ".
  3. Re-check again: clean. The ladder was frozen and used to build the artist table: 11,275 raw credits collapsed to 8,896 artists.

Nobody writes the complete rule first time. And the data will not raise an error when your rule invents "2Pac Duet". So the loop is: preview on real rows, catch the defect, add a rung, re-check the exact rows you targeted. Repeat until the exceptions stop. The full ladder, with every defect it caught, is walked through in the CASE expression guide.

Step 3: know when NOT to merge (the false-positive check)

The "&" joiner appeared in 1,994 credits. Should it split too? Decide before you read the next paragraph. The rule is simple and it worked on the other joiners, so the case for splitting is strong.

Reading the highest-volume "&" credits first settled it. The list is dominated by permanent duos and bands: Kool & The Gang, Earth, Wind & Fire, Simon & Garfunkel. Only a few are temporary collaborations. Say what that means for the rule before reading on. Splitting on "&" would shred real bands into artists who do not exist.

So the decision was not to split. That has a cost. A duo member's solo career never merges with their duo work. The cost was written down as a limitation instead of being fixed.

This is the half of entity resolution that is easy to skip. Over-merging destroys as much as under-merging does, and sometimes the right output is a refusal you wrote down.

The same project made the point twice more. One duo appeared as both "¥$: Ye & Ty Dolla $ign" and "¥$: Kanye West & Ty Dolla $ign". Those are aliases, and no rule can know they are one act without a lookup dictionary. Separately, three artists were split purely by capitalization, "Tyler, The Creator" against "Tyler, the Creator". That one was cured by lowercased match keys.

Step 4: match rates, the number that keeps you honest

Picture your own messy column for a moment. Customer names, product codes, hospital sites, whatever you actually work with. How many distinct values does it hold, and how many real things are behind them? If those two numbers are the same, you have no problem here. Most people find they cannot answer the second one at all, and that gap is the job.

The payoff needed the chart data linked to a second dataset, listener statistics from a music API, matched on artist and title text. That is record linkage. The discipline is simple: measure the match rate before and after every cleaning rule.

Note what the match rate is for. In this project it was never meant to reach 100%. Unmatched tracks were the whole point, because they were songs that never charted.

The rate's job is to separate signal from defect. A good rule recovers the spelling casualties without inventing false matches. The rate shows what each rule contributed, so you know to stop cleaning when the improvements stop.

Step 5: clerical review, or reading the leftovers

The classic record-linkage framework sorts candidate pairs three ways: confident matches, confident non-matches, and a middle zone that goes to a human (Fellegi & Sunter, 1969).

In everyday analyst work that third bucket is simpler and cheaper than it sounds. After the rules run, read the unmatched rows, sorted so the most consequential come first.

Then judge what you see. If the top of that list is full of records that obviously should have matched, your rules have a gap. If it reads as genuinely unmatched, the linkage is done, and what is left becomes a limitation you document and quantify.

It takes about ten minutes. It is the difference between "the join ran" and "the join is right".

The principles, distilled

PrincipleOne-line version
Measure firstSize the fragmentation (depth on one entity, breadth across the table) before writing any rule.
Preview every ruleShow original and transformed side by side on real rows; read them.
Specific above generalIn any rule ladder, the narrow pattern outranks the broad pattern it contains.
Check false positivesBefore merging or splitting, read the rows where a wrong rule does the most damage.
Sometimes: don't mergeOver-merging destroys real entities. A documented refusal is a valid output.
Match keys, stored and indexedCompute normalized keys once into real columns; join on those, not on function-wrapped originals.
Match rate per ruleMeasure before and after each rule; stop when the rate stops moving for honest reasons.
Clerical reviewRead the leftovers before declaring victory.
Log everythingEvery decision (including refusals) goes in the data-quality record and the limitations section.
See it run end to end.

The Steam Hidden Gems and Streaming Hidden Gems projects carry these habits through full analyses you can read. The SQL Kit teaches the LIKE, CASE, LOWER and JOIN moves that every step above uses.

Open the SQL Kit →

Or get right into it and learn by writing queries: open SQL Drill, thirteen queries that each add one thing to the last.

You can do the work once somebody tells you what the work is. A vague question is the part that stalls you.

Thinking Like an Analyst is 64 pages on the judgment half of the job: defining a metric, telling a report from an analysis, exploring data before you trust it, and saying out loud what your data cannot tell you.

Thinking Like an Analyst, $19 →

References

  1. Fellegi, I. P., & Sunter, A. B. (1969). A theory for record linkage. Journal of the American Statistical Association, 64(328), 1183–1210. doi:10.1080/01621459.1969.10501049
  2. Wang, R. Y., & Strong, D. M. (1996). Beyond accuracy: What data quality means to data consumers. Journal of Management Information Systems, 12(4), 5–33. (The fitness-for-use standard the merge/don't-merge tradeoffs answer to.)