← All Kits · SQL CASE Expression · SQL Kit

Entity Resolution: One Real Thing, Many Messy Names

The messiest everyday skill in data work, on real data · Part of the Analyst Prep Kit

Count the distinct artists in Billboard's public chart history and you get a number that is simply wrong — because "Elvis Presley", "Elvis Presley With The Jordanaires", and five other credit strings are all the same man. One real-world entity, seven database strings. Every dataset that touches human-entered names has this disease: customers who signed up twice, "IBM" vs "I.B.M." vs "International Business Machines", the same supplier in two systems with two spellings. The discipline of curing it is called entity resolution (also record linkage when matching across two datasets, or deduplication within one), and it's among the most common real-world analyst tasks that beginner courses never teach. This guide teaches it the way it actually goes: on real, uncooperative data, mistakes included.

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 honesty number
  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 successfully found their counterpart — the number that keeps the whole exercise honest
Clerical reviewHuman eyes on the records the rules couldn't decide — a formal stage of the classic framework, not an admission of failure

Step 1: measure the fragmentation before fixing anything

The worked example running through this guide: Billboard Hot 100 history, 1958 to present, where the goal is one clean row per artist. The amateur move is diving into fixes. The professional move is sizing the problem first:

Those two numbers decided everything downstream: fragmentation this widespread means normalization rules are mandatory, and any rule will touch thousands of rows, so the rules must be tested, not trusted.

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

The extraction rule — "the primary artist is the text before the joiner word" — was built as a CASE ladder and proven on real rows before anything was built from it. The iteration 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 "Patti Austin A Duet With James Ingram" → "Patti Austin A". 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 on the first try, and the data will not send an error message when your rule invents "2Pac Duet." Preview on real rows, catch, add a rung, re-check the exact targets — 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? Reading the highest-volume "&" credits before writing the rule settled it: the list is dominated by permanent duos and bands — Kool & The Gang, Earth, Wind & Fire, Simon & Garfunkel — with only a few temporary collaborations. Splitting on "&" would shred real bands into artists that don't exist. Decision: don't split. The cost (a duo member's solo career never merges with their duo work) was written down as a limitation instead.

This is the half of entity resolution beginners miss: over-merging is as destructive as under-merging, and the mature output is sometimes a documented refusal. Related discoveries in the same project made the point again at other scales: the same duo credited as "¥$: Ye & Ty Dolla $ign" AND "¥$: Kanye West & Ty Dolla $ign" (aliases — no rule can know those are one act without a lookup dictionary), and three artists split purely by capitalization ("Tyler, The Creator" vs "Tyler, the Creator"), cured by lowercased match keys.

Step 4: match rates — the honesty number

The project's payoff required linking the chart data to a second dataset (listener statistics from a music API) by artist + title text — classic record linkage. The discipline: measure the match rate before and after every cleaning rule.

Note what the match rate is for. In this project it was never supposed to reach 100% — unmatched tracks were the whole point (songs that never charted). The rate's job is separating signal from defect: rules should recover the spelling casualties without inventing false matches, and the rate quantifies each rule's contribution so the cleaning stops when improvements do.

Step 5: clerical review — reading the leftovers

The classic record-linkage framework classifies candidate pairs three ways: confident matches, confident non-matches, and a middle zone that goes to human review (Fellegi & Sunter, 1969). In everyday analyst practice that third bucket is simpler and cheaper: after the rules run, read the unmatched rows — sorted so the most consequential come first. If the top of the unmatched 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 the residual becomes a documented, quantified limitation. Ten minutes of reading, and it's 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, and the free SQL Kit teaches the LIKE, CASE, LOWER, and JOIN moves every step above uses.

Open the SQL Kit →

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