Most people who reconcile messy identifiers reach for the same approach: take an edit-distance metric, pick a threshold, and merge anything close enough. Damerau-Levenshtein is the obvious upgrade over plain edit distance. The reasoning for choosing it is sound. It treats a transposition (two swapped characters) as one mistake instead of two. In a pull of 719 environmental sensor stations, one confirmed misspelling was sds1001 for sds011. Two digits were swapped — a slip anyone could make on a keyboard. Plain edit distance treats this as two edits, the same price it would charge for two totally different products. Damerau-Levenshtein on the other hand knows it's a single mistake and from the get-go, scores it as distance 1. So it’s a better model of how typos happen, and it literally costs nothing to adopt, making it the most obvious choice.

However, there is an often-overlooked downside to the metric. Here's an example: hdc1008 and hdc1080 are two real Texas Instruments humidity sensors sold at the same time, documented on separate datasheets. While plain edit distance puts them at 2, Damerau-Levenshtein pulls them down to 1 into the same range as the aforementioned typos. Note how the same property that makes it better at recognizing human errors also causes it to merge two entirely different products that the manufacturer purposely numbered a digit apart.

This is Part 2 of my deep-dive series on entity key drift that starts with normalization. If you haven't read Part 1 yet, it's worth a look first since it covers the deterministic cleanup that this piece picks up from. The code and data for both are on Git Hub and Zenodo, in case you want to check any of the numbers yourself. In this piece, I'll walk through what happened when I measured five string-similarity metrics against the ground truth I verified from manufacturer datasheets, why none of them could be tuned to separate typos from real products — and what that failure means for how the system has to be built instead. This is relevant to anybody reconciling short alphanumeric identifiers — part numbers, SKUs, model codes — from more than one source, and less so if yours come from a single source.