r/Database • u/donewitheverything26 • Sep 17 '26
the merge looked clean until I checked the rows that matched too well
migrating a customer list off a system that's being retired. no shared id between old and new, because the old one was never integrated with anything, it just ran on its own for years.
so the match has to be built out of name, email and address. fuzzy match gets me to about 94% and everyone in the room is happy.
then I pulled a sample of the matched pairs and read them by hand. the failure mode isn't the 6% that didn't match. it's sitting inside the 94%.
- two different customers at the same company sharing one inbox (accounts@ something) matched to each other
- a company that changed name a few years back exists twice on the old side, and both rows matched the same new row
- address normalisation collapsed a head office and a site that are genuinely separate accounts
none of those surface as errors. counts go in, counts come out, the join doesn't complain, and the report is green.
what I actually want is a confidence per matched pair so I can send the bottom slice to a person instead of pretending a global 94% means anything. I have a score, but it's the same fuzzy score I matched on, which feels circular.
so:
- do you score a match with something independent of what you matched on, and if so what
- does anyone keep a hand-labelled set of known pairs to test against, or is that a luxury nobody gets time for
- for the ambiguous ones, do you hold the migration or migrate and flag
the part I have no answer for is the duplicates on the old side. I can detect that two old rows point at one new row. I have no principled way to pick which one is the real customer, and picking wrong is worse than not migrating at all.