SQL, data modeling, data quality, EDA, visualization · insight-1065
Match near-duplicate entities with scored comparisons and a clerical-review band
- Evidence type
- documented fact
- Difficulty
- hard
- Roles
- Data scientist, Data analyst
- Industries
- healthcare, public-sector, aviation, cross-industry
Summary: situation, action, reason, boundary
Situation
Two passenger files should be merged, names and birth dates are keyed by hand, and exact-match deduplication leaves obvious duplicates while a loose rule merges different people.
Action
Block on cheap keys to limit pairs, score field agreement (agreement and disagreement weights per field), define three outcomes (link, review, non-link), and sample each band to estimate error rates. Keep the original identifiers and make merges reversible.
Reason
The classical record-linkage model decides link, possible link or non-link from a likelihood ratio over comparison fields, which exposes the trade-off between error types.
Boundary
Customer, passenger, supplier and product master data and cross-source joins without a shared key.
Symptom or risk
One threshold forces a choice between missed matches that inflate counts and false merges that corrupt histories, and neither error rate is measured.
Exceptions
Sources with reliable shared identifiers, where exact joins are better.
Trade-offs
Review capacity is limited; the band width sets a labour cost.
Pitfalls
Assuming fields are independent, so correlated fields such as city and postal code double-count evidence.
Lessons and concepts
Claims and their sources
Fellegi and Sunter's 1969 theory frames record linkage as a decision between link, non-link and possible link based on comparing fields of record pairs, with an optimal rule given by a likelihood-ratio test under their assumptions.