Avoiding Entity Key Drift in a Data Lake: Step 2, When Fuzzy Matching Stops Working

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 sds1001 and sds011) as one mistake instead of two.

In a pull of 719 environmental sensor stations I was working with, 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 GitHub 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. Also, it’s worth saying upfront that this is a negative result. If you’re looking for a matcher to copy, I don’t have one, and the reason I don’t turns out to matter more than a matcher would have.

What normalization left behind

Step 1 handled the easy cases. Across 719 stations, 114 distinct sensorType strings collapsed down to 99 natural keys once NFKC normalization, case folding, and separator stripping were applied. SDS 011, SDS011 and sds011 all converged to become sds011. The merge carried no risk at all since the differences were purely stylistic and the function doing the collapsing was deterministic.

99 keys is still a lot more than what the domain actually contains. My best estimate is that there are around two dozen real hardware models represented here (although that is only an estimate). Pinning down the exact number would require a parts catalog which the dataset doesn’t carry (more on that later).

The shape of that gap matters before you try to close it. 52 of the 99 keys are part-number codes like bme280 and sds011, and together they account for 94 percent of the 4,301 observations, with a median length of six characters. The rest is a mix of descriptive words like gps and regenmesser, one literal “???”, and one key that jams two part numbers together as “tsl45315&veml6070”.

That population matters more than it looks. Approximate string matching wasn’t really built for it. Cohen, Ravikumar, and Fienberg’s 2003 comparison of string metrics benchmarks against name matching, where a shared prefix is evidence: “Jonathan” and “Jonathon” look alike because a human transcribed the same person. Device identifiers don’t work that way. bme280 and bmp280 share five of the six characters and are still two different products.

I went ahead and built the matcher anyway, and tried to do it conservatively. It compares identifiers in two stages: the digit core of each key has to match exactly before the alphabetic remainder gets judged on similarity at all. That gives the advantage of the numeric part not getting smoothed away by a tolerance setting; bme280 and bme680 stay apart by construction rather than by a threshold that a tuning pass could loosen later. This way whatever safety it has is built into the logic rather than sitting in a weighted setting.

That structure however doesn’t solve the comparison that it still lets through. Once two keys share a digit core, the letters get judged by similarity, and similarity needs a threshold. So the whole design comes down to one question: is there a threshold on any reasonable metric that catches every typo without also merging two genuinely different products?

Building a ground truth you can actually check

That question can’t be answered with the data on hand. The snapshot tells us how often each string shows up, but it doesn’t tell us which strings actually name a real shipping part, and that question needs answering.

So I built the column manually before running a single similarity score. That order was deliberate because if you classify strings after you’ve already seen how they score, you tend to classify them to fit the scores without even meaning to.

For each of the 16 strings, I looked up the manufacturer’s datasheet and recorded a verdict, the official part name, source URL, lifecycle status, and access date. All of that lives in the repo as ground_truth_catalog.csv, so none of it is taken on trust.

Doing this by hand turned up three things I didn’t expect that were notably more interesting than the matcher results.

• First, one of the assumptions I had turned out to be wrong. I’d assumed bmp200 was a typo of bmp280. Bosch’s pressure sensor line runs BMP085, BMP180, BMP280, BMP388 — there’s no BMP200 in it. But upon further investigation, I found out that BMP200 is a PM10 particulate analyzer from Focused Photonics, an entirely different manufacturer, although I couldn’t locate a spec sheet or evidence of real distribution for it. So that pair might either be really a typo or a collision between a Bosch pressure sensor and a Chinese air-quality analyzer; I couldn’t tell which even after spending considerable time on it. So I dropped the pair, which is worth flagging because it weakens my results (taking the confirmed-typo count from four pairs down to three makes the pattern harder to demonstrate), but it held up anyway.

• Second, an early confirmation turned out to be incorrect. An earlier pass through the data had recorded SDS 011 with a space as canonical and marked CONFIRMED, sourced from a third-party mirror site. But Nova Fitness’ own documentation reads SDS011 without space on the cover and in page headers. The mirror had it wrong, and that error had been sitting there masked with a CONFIRMED label. Mirrors aren’t manufacturers, and that distinction should have been treated more carefully from the start.

• Lastly, one part wasn’t named what everyone calls it. The dataset uses dht22, but Aosong’s own technical manual says it not once. DHT22 shows up on every storefront and reseller mirror online, but in the manufacturer’s own documents it’s AM2302. The part itself is real, so the row stands, but the name the entire field uses for it isn’t what the manufacturer uses. That’s the third kind of failure underneath the two I was actually trying to measure.

After all that verification, what was left? 12 strings naming real parts, 3 naming nothing, and 1 excluded — which comes out to 10 scored pairs: 3 real typos (the reason I was building the matcher in the first place) and 7 pairs where both sides are real products that a merge would silently average together.

Five metrics, and not one of them separates

A metric works if some threshold on it merges all 3 typo pairs and none of the 7 real-product pairs. I tested five spanning the main families: plain edit distance, transposition-aware (Damerau-Levenshtein), prefix-weighted (Jaro-Winkler), set-based (q-gram Jaccard), and my two-stage digit-core matcher.

Set the threshold loose enough to catch all three misspellings, then count what comes with it. Plain edit distance merges all seven real pairs. Damerau-Levenshtein: seven. Two-stage: seven. q-gram Jaccard: three. Jaro-Winkler, the best of the five, merges one.

Not one of them merges none.

The pair that settles the question is hdc1008/hdc1080, the two real Texas Instruments parts mentioned earlier. They aren’t variants of each other: different packages, different supply ranges, and ±4 %RH accuracy against ±2 %RH. Therefore, merging them would fold a sensor with twice the error into one with half of it. Jaro-Winkler scores this pair 0.971, higher than the best genuine typo scored at 0.963. q-gram Jaccard puts it at 0.714, above two of the three. On plain edit distance and on the two-stage matcher, it ties the worst-scoring typo. On every metric tested, this pair of genuinely different products scored in the same band as at least one real misspelling.

Table 1. Every scored pair under all five metrics, against datasheet-verified ground truth. Bold marks the row that defeats the board. Pairs use the data as-is instead of the official part names: dht22 is what the data contains, though the manufacturer calls that part AM2302. A matcher only ever sees the left column, and that gap is the real problem.

It’s worth being precise that one pair is enough here. The natural objection that n=10 pairs is too small a sample applies to a different kind of claim than the one made here. If the goal was to prove that a metric works, a large sample would have been required to bound its error rate with any confidence. But proving that no threshold can separate the two groups only requires a single pair that scores in the typo range with the opposite ground truth. There are several such pairs here, across every metric tested. Collecting more data could add more counterexamples, but not remove the ones already there.

The two-stage matcher deserves fair accounting as well. Its digit-core rule refuses to consider sds011 and sds1001 at all because 011 and 1001 are different digit strings, so it never even gets a chance to catch the typo it was built for. And it still merges bme280 with bmp280 at a distance of one, because they share the digit core 280. The structural safety I built into it held where I aimed it but failed elsewhere. It missed the typo it was designed to catch, and made the collision it was designed to prevent.

Why a sixth metric doesn’t save this

Full disclosure: there’s a version of this argument that isn’t falsifiable, and that version isn’t worth much. I didn’t test every possible matcher — nobody could — and it’s entirely possible some untested metric would have looked like it worked on this particular sample. But the claim I want to make isn’t empirical in that sense. It’s structural — the fact that decides identity here isn’t contained in the string at all.

Consider the pair I scrapped earlier. If Bosch ships a BMP200 next year, the relationship between bmp280 and bmp200 flips from typo to distinct, and neither string changes by a single character. What does is a decision made inside a company that has nothing to do with either string’s characters.

This is not just a hypothetical designed for effect — it’s the actual reason that pair isn’t there. I couldn’t rule out that a BMP200 exists, and that’s exactly why I set the pair aside instead of scoring it. The scenario turned out to be the reason why I ended up with three confirmed typo pairs instead of four.

No function of two strings can read a manufacturer’s catalog. Not the five metrics I tested, or a sixth one I didn’t try, and not a learned model either. Learned entity matching is genuinely good at what it does — Konda’s Magellan from 2016, or the pre-trained language model approach from Li et al. in 2020 — but both assume you have labeled training data and some tolerance for models whose reasoning you can’t fully interrogate. A trained model would fail here for the same reason the hand-built ones do: the signal it would need just isn’t in the input. If you hand it the catalog directly, you’ve basically rebuilt the architecture described below, just with less transparency and no audit trail, which answers “did you try X” for every value of X, once and for all.

A catalog isn’t a fixed reference either. New keys entered in every year of the window, 15 in the first and 3 in the last, accumulating to 99 (Figure 2 below). So the claim that this string doesn’t name anything real is in a specific moment in time, and a part that ships next quarter can flip that without a single stored byte changing.

One more admission, and it is perhaps the strongest point in the whole piece: to build the ground-truth column, I had to consult the manufacturer datasheets — an external catalog checked by hand — precisely because the dataset itself couldn’t answer the question. Setting up the experiment required conceding its own conclusion before I’d even run it. 

None of this is news to the record-linkage literature. Fellegi and Sunter formalized probabilistic linkage back in 1969; Christen’s 2012 treatment lays out the limits of approximate matching. That string similarity alone is insufficient is established fact for names and addresses. What this piece adds to that is that practitioners still reach for fuzzy matching on short alphanumeric device codes where the failure is of kind rather than degree. Identity isn’t in the codes at all, and the catalog holding it is external and changes over time.

The architecture that survives

Once you strip out what the measurement ruled out, here is what remains.

1. Merge only what normalization proves identical. The only sound automatic merge, and Step 1 already does it, is 114 keys to 99, zero risk, zero threshold.

2. Fuzzy auto-merge is rejected by evidence, not caution. Conservatism implies that a bolder option exists for the risk-tolerant. There actually isn’t one: the information required is absent.

3. Everything else defers to a human, who consults the catalog the strings don’t carry. A human can look at hdc1008 and hdc1080 and correctly tell them apart and record that decision as a label — something applied at query time, so the identity stored in the data stays a pure function of what was actually observed.

More than just being a convenience for edge cases, the label layer is essential, because if a matcher could safely do this work, labels wouldn’t need to exist at all. Three things need to be true of them for the system to hold together.

Labels need to be effective-dated

Each version carries a validity interval, and a query resolves against the version valid as of its declared time. Whether something is a real part is genuinely time-varying. The catalog picked up new keys in every year of the window, which means a label can be overturned by an event in the world rather than by new data arriving in the dataset. If you edit a label in place instead, you silently regroup history. Aggregates from last quarter stop reproducing, and a record that was correct when it was written ends up looking like it always said something different. The resolution is not to correct the past but to date it.

The cost of that is charged to everybody, and it’s easy to let the mechanism’s tidiness hide it. Once labels are dated, a question like “how many sds011 units are deployed?” doesn’t have one truth anymore; it has an answer as of a given date, which the caller has to supply. Interfaces that never needed a temporal parameter now need one. That is the price of reproducibility.

Labels never go back into the matcher as training signal

There are three separate reasons for this, and any one of them would be enough on its own. Identity needs to stay a function of the record’s own content. If a matcher gets tuned on past labeling decisions, identity becomes a function of processing history instead, and the same input can stop producing the same key over time. A matcher like that also stops being something you can point to in an audit and explain. And a system that consumes its own labels to improve matching has, without really meaning to, built the learned matcher that was already ruled out above.

Labels are data too, and data drifts

That would be a genuinely awkward outcome for a piece about identifier drift if the layer meant to fix it introduces its own version of the same problem. In order to sidestep that, labels must come from a controlled vocabulary, get natural-keyed and normalized with the same Step 1 function, and carry a record of who made the decision, when, and on what evidence. A label without an author attached is just a claim without an explanation.

To be sure, I tested this rule while writing. My ground-truth CSV has a verdict column that a validation script reads as a controlled vocabulary. At one point, I “improved” a row by changing it to CONFIRMED REAL, PART NAME CORRECTED. It was valid CSV, parsed fine, and it silently dropped that row out of the confirmed set because the new string wasn’t in the vocabulary. So the rule caught its own author within roughly four hours of being written.

What the deferral actually costs

Deferring everything else to a human is practical, provided the resulting queue stays small. On measuring it, here’s what I found. Comparing all 99 keys pairwise comes out to 4,851 comparisons, but nobody is reviewing that many by hand. Blocking cuts that further down to 30 candidate pairs, which is only 0.62 percent of the full space, and after the initial pass, it settles into roughly four new pairs a year as new keys enter the catalog. So essentially it amounts to a morning of work upfront, and a handful of decisions annually.

Blocking itself isn’t new; sorted-neighborhood blocking goes back to Hernández and Stolfo in 1995, canopy clustering to McCallum et al. in 2000, locality-sensitive hashing to Broder in 1997. What’s less standard — at least in my experience — is choosing among these rules by actually measuring their tradeoffs rather than just picking the one that feels familiar.

How fast that recurring queue grows depends on how fast the catalog itself grows.

Which specific rule to block on is itself a thing to measure rather than inherit.

Table 2. Blocking rules measured against the three verified misspellings. Three of the cheaper rules miss a real typo.

Three of the rules I tested are cheaper than the one I ended up using. All three of them miss sds011/sds1001. It’s not a tuning accident; it follows directly from how digit-signature blocking works. 011 and 1001 are different digit strings, so those two keys never land in the same block in the first place, which is the exact same property that correctly keeps bme280 and bme680 apart. The takeaway: the property that protects you from one kind of mistake is the same one that blinds you to another.

The volume triage trap

There’s an optimization that will probably occur to anyone looking at this setup, and it’s more a trap than a shortcut. You could review only pairs where both keys carry meaningful observation volume on the reasoning that a pair affecting two observations doesn’t deserve much attention. That cuts the queue from 30 down to 6, and on the surface, it looks like a free win.

It also removes every single verified typo from consideration.

Misspellings are rare almost by definition. They’re mistakes certain contributors happened to make, not something most people typed. The minority side of my three confirmed typo pairs shows up 1, 2, and 2 times respectively. So any volume threshold set at five or above would have discarded all three before a human ever saw them.

The general version of this is worth keeping in mind outside of sensors too. A frequency filter will systematically remove whatever population one is actually trying to find, any time that population is rare by construction. In this particular case, the queue was already small enough that the shortcut wasn’t needed anyway.

What the review actually buys

Here is the uncomfortable fact. Working all 30 pairs by hand moves a total of 5 observations out of 4,301 — about 0.12 percent of the data.

That naturally invites a shortcut: why not just merge all 30 candidates automatically and skip the review? Except that would move 217 observations instead of 5, because most of the 30 pairs turn out to be real products that just happen to look alike. scd30 and sps30 differ by two characters and measure completely different things. The queue is mostly made up of pairs that resolve to no merge. That is why it needs a person looking at it rather than a threshold deciding automatically.

So the review is cheap relative to what it protects against, and it’s necessary because there’s no way to find the 5 observations that do need fixing without looking at all 30.

This reframes what the review is actually buying. Before it, 2,430 observations (56 percent of the dataset) sit on a key that has an unresolved neighbor, which means there’s no way to say with confidence whether the counts for that key are complete. Following the review, that question has an answer.

What you get out of this process isn’t corrected data exactly. It’s knowing that the data is correct.

The honest limits

This architecture doesn’t close the gap it opened with. 99 keys went in, the domain probably has around two dozen real models, and the review process resolves 30 pairs and confirms three typos. Those numbers don’t reconcile, and it would be easy to let a reader come away thinking otherwise.

The reason is consistent with everything else here. Blocking only ever surfaces pairs that are already close together in string space. Every rule in Table 2 is some version of a distance or overlap rule, so each one finds near-duplicates and nothing beyond that. Two keys that name the same part but share no characters at all never make it into the queue in the first place.

My own ground truth offers an example of this. dht22 and AM2302 name the same sensor under two completely different names, sharing nothing as strings — an edit distance of 5, zero bigrams in common, and different digit signatures entirely. Run both through every rule in Table 2 and none of them proposes the pair. So the person who could resolve it in a second never gets asked the question.

Precision matters here, since that is the whole point. AM2302 never actually appears in this particular snapshot, while dht22 shows up 22 times. This is not an observed miss. It is what the rules do when handed both spellings, which is what happens the moment a second gateway reports the manufacturer’s name instead of the market one. In this dataset, the pair isn’t just failing to get flagged; it is missing entirely from the all-pairs comparison space. There are two independent reasons that a human reviewer never gets asked, and no threshold adjustment fixes them either. The alias problem is genuinely harder than the typo problem, and this architecture doesn’t solve it. It declines to guess — which is better than guessing wrong — but it isn’t the same as being finished. Closing that gap would need a catalog with alias tables in it, not a better blocking rule.

Two smaller limitations worth naming here are: the scope is identifier fields only, not the free-text descriptions of what a sensor measures, which is a related but separate problem; and this is one snapshot of one crowdsourced network whose open submission form probably produces more variations than a controlled schema would. The mechanism should generalize reasonably well. The specific percentages probably shouldn’t be treated as a benchmark.

Six things to take away

• Build ground truth from outside the data, before you score. If you classify after scoring, you tend to classify to fit the scores you already have. Mine ended up killing one of my own claims and correcting two others, which is the method working rather than a setback

• The sophisticated metric is the more dangerous one. Damerau-Levenshtein’s awareness of transpositions is exactly the kind of tolerance real product families exploit because manufacturers often number related products by permuting a digit.

• For short alphanumeric codes, the deciding fact is outside the string. Whether two codes name the same product is settled by a manufacturer’s catalog, which is external, changes over time, and isn’t reachable from the characters themselves. No metric, and no learned model either gets around that.

• A frequency filter tends to delete the exact rare population you’re trying to find. Typos are rare, so volume triage looks like an obvious win and it ends up removing every real one.

• Date your labels; don’t retrofit them. Whether something is real is often only true as of a particular moment. So effective dating lets a label change over time without a sealed historical record quietly becoming false. The tradeoff is that every downstream consumer of that label now has to provide an as-of date.

• The deliverable is not corrected data. It is knowing that the data is correct. The review moved 5 observations out of 4,301, and took 56% of the data from possibly fragmented to verifiably whole. The second is what’s worth paying a person for.

Summary

This matcher was originally built with the intention to finish what normalization started. However, the measurement says that it cannot exist in a safe form. Five different metrics overlap once checked against the datasheet-verified ground truth and one pair of real TI parts ended up looking more like a typo than actual typos do. The underlying reason is structural: the fact that decides identity here lives in a manufacturer’s catalog, instead of the characters of the string. What’s left? Automatic merging only where normalization proves it safe, plus a human-adjudicated, effective-dated label layer that stays out of the model entirely — at 30 pairs upfront and roughly four a year afterward.

Part 3 covers dynamic cadence and noise filtering — how fast new identifiers show up to be judged. Following that, Part 4 covers idempotent storage and immutable snapshots, where versioned labels meet sealed records.

The reference implementation is anchorkey, Apache-2.0, sitting underneath device-identity layers like Eclipse Ditto and Eclipse Hono, both of which assume that a stable device identity already exists.

pip install -e .python examples/quickstart.pyfrom anchorkey import normalize, same_entity, needs_reviewsame_entity("SDS 011", "sds011")     # True  — normalization proves itsame_entity("SDS1001", "SDS011")     # False — a typo, but NOT auto-mergedneeds_review("SDS1001", "SDS011")    # True  — routed to a person insteadsame_entity("bme280", "bmp280")      # False — two real Bosch parts

If you have fought this in your own pipelines — especially if you found a matcher that did hold up on short codes — I would genuinely like to hear about it.

Reproducing the data

Every number comes from one re-runnable capture of the public openSenseMap API, released under the Public Domain Dedication and License 1.0:

GET https://api.opensensemap.org/boxes?bbox=7.58,51.93,7.66,51.99&format=json

The capture is 719 boxes taken at 2026-06-26 UTC, yielding 114 distinct sensorType strings and 4,301 observations. Live counts drift, which is the subject of this series; so every figure derives from the archived snapshot, deposited at DOI 10.5281/zenodo.20989076.

Four scripts regenerate every number quoted above, each cross-checking its output against the values asserted here and exiting non-zero on a mismatch: collapse_sample.py (the 114 → 99 reduction), keyshape_sample.py (population figures), separability_sample.py (every score in Table 1), and review_queue_sample.py (queue, blocking, cost, and the alias blind spot). A fifth, make_figures.py, draws Figures 1 and 2.

The ground truth is a checked-in artifact, not an appendix claim. ground_truth_catalog.csv records for each of the 16 strings — the manufacturer, official part name, datasheet URL, lifecycle status, access date and verdict — including the string I could not resolve, and the three rows whose only obtainable datasheet is a mirror rather than a manufacturer-hosted copy. Those limits are recorded per row rather than smoothed over because a catalog assertion sourced from a mirror is exactly the error this article is about.

References

Every entry is cited at a specific claim above, with the section bracketed.

• I. Fellegi and A. Sunter, A Theory for Record Linkage, JASA 64(328), 1969. [Why a sixth metric doesn’t save this]

• W. Cohen et al., A Comparison of String Distance Metrics for Name-Matching Tasks, IIWeb, 2003. [What normalization left behind]

• P. Christen, Data Matching, Springer, 2012. [Why a sixth metric doesn’t save this]

• P. Konda et al., Magellan: Toward Building Entity Matching Management Systems, PVLDB 9(12), 2016. [Why a sixth metric doesn’t save this]

• Y. Li et al., Deep Entity Matching with Pre-Trained Language Models, PVLDB 14(1), 2020. [Why a sixth metric doesn’t save this] Also called Ditto; unrelated to Eclipse Ditto below.

• M. Hernández and S. Stolfo, The Merge/Purge Problem for Large Databases, SIGMOD, 1995. [What the deferral actually costs]

• A. McCallum et al., Efficient Clustering of High-Dimensional Data Sets with Application to Reference Matching, KDD, 2000. [What the deferral actually costs]

• A. Broder, On the Resemblance and Containment of Documents, SEQUENCES, 1997. [What the deferral actually costs]

• Unicode Standard Annex #15, Unicode Normalization Forms [What normalization left behind]

• Eclipse Ditto · Eclipse Hono [Summary]

• Manufacturer datasheets for the classified parts (Bosch Sensortec, TI, Sensirion, Aosong, Nova Fitness), per row in ground_truth_catalog.csv. [Building a ground truth]

• openSenseMap, public citizen-science sensor network. [Reproducing the data]

Web sources accessed 2026-08-14. Manufacturer datasheets carry per-row access dates in ground_truth_catalog.csv.