Avoiding Entity Key Drift in a Data Lake: Step 1, Normalization

treating an observed string as an identity is a shortcut that comes with a hidden cost. Assigning a primary key to an identifier string the first time you observe it is a reasonable starting point. An auto-increment id or a UID minted on first encounter is handy, easy to implement, and holds up well in demos and staging where data is clean and controlled.

But that approach breaks when real data arrives. This is where identifier strings start to drift through spacing differences, case variations, typos, or re-provisioning, with a single physical entity splitting into multiple keys. When that happens, every count, average, and threshold alert the dashboard produces goes wrong — without exceptions or red flags. As these accumulate, the dashboard eventually reaches a point where it can no longer be trusted.

In this piece, I will demonstrate that failure across 719 real environmental sensors, drawing the same pattern in three commonly found systems in production — SNMP, Kubernetes, and Prometheus. Additionally, I’ll introduce the fix the rest of this series will be built around: deriving a natural key from the observed string rather than minting identity directly from it. This architecture showcases the implementation on openSenseMap API and the full code (available on GitHub and Zenodo) is included below so you can rerun everything yourself.

This is relevant if you analyze telemetry across a device fleet, join data from more than one source, or need to seal records for audit. It is less relevant, however, if every entity in your system carries a single, stable, authoritative identifier that is never retyped, re-provisioned, or reconciled from an external source. If that describes your environment, this piece will not offer much. For everyone else, the problem is worth understanding before it becomes a mitigation.

One sensor, four identities

The Nova Fitness SDS011 is one of the most common cheap particulate sensors in the market. In a pull of 719 stations, that one model shows up under four distinct sensorType strings:

Now consider the obvious ingestion design. A sensors table whose identity is the observed model string:

-- The latent bug, in three lines.
INSERT INTO sensor_models (id, model)            -- id = bigserial / uuid
VALUES (DEFAULT, :observed_model_string)
ON CONFLICT (model) DO NOTHING;

You now have four rows for the SDS011. Because of this, every query that groups by sensor_models.id— the number of SDS011s deployed, mean PM2.5 by model, alerting when a model’s error rate spikes — computes its answer over rows that don’t correspond to the real entity. As the model catalog expands 4×, the per-model mean will split four ways. The error-rate baseline for SDS011 never sees the readings filed under SDS 011, rendering the dashboard wrong.

What’s worth pausing on here is that the surrogate id is not the problem. You would have the exact same bug with no surrogates at all. The real mistake is treating the observed string as identity because the UIDs minted for new strings end up inheriting the drift. So the fix later is not to stop using surrogate keys, but to stop deriving identity from the string the observer happened to have keyed in.

This is not an openSenseMap problem either. openSenseMap gives every box a perfectly stable internal ID that bites the moment you analyze data across the fleet because cross-fleet questions force you to key on the descriptor strings, and the descriptor strings drift. The same 719 stations do it everywhere:

• the InvenSense accelerometer appears as MPU-6050 (749) and MPU6050 (8). One hyphen.
• a sound sensor appears as soundlevelmeter (14) and SOUNDLEVELMETER (8). One shift key.
• across all stations there are 114 distinct sensorType strings, 214 distinct sensor title strings, and 88 distinct unit strings, for a domain that has maybe a few dozen real models and a handful of real units.

A quick caveat before we go further: this is citizen-science data. An open submission system that accepts whatever a contributor types will of course be messier than a locked-down enterprise schema. The magnitude here is amplified by that openness. The mechanism to fix it, though, is not crowdsourced at all, which is what the next section is about.

You have already shipped this bug (just not in IoT)

If the sensor example feels niche, here is the same failure in three systems you’re probably already running in production.

SNMP ifIndex. Identity is derived from a combination of ifName, ifAlias, and ifIndex. But ifIndex by itself is not stable. Pulling a card, rebooting or reconfiguring a device can change the ifIndex. For example, the monitoring graph for “port 3” can start charting a different physical port after a maintenance window with no indication that anything has changed. This is documented in RFC 2863 which added ifName and ifAlias precisely for this reason as the index is intended for bookkeeping, not identity.

Kubernetes pod UID. In Kubernetes, every pod gets a UID at creation. However the UID by itself is not the identity as pods get disposed once tasks are completed. And as new pods are rolled out, the same workloads get fresh UIDs. This resets the baseline, firing a “new workload” alert each time. To avoid that, if you key your metrics on the pod name instead, it will lead to collisions across namespaces. Kubernetes addresses this issue with a label set which derives identity from a composite of attributes —app, namespace and version — and not the UID.

Prometheus. Prometheus gets this right by design which makes it a great counter-example. A time series is its label set, a natural composite key, which is why it can survive restarts and reschedules. So, adding a restart-scoped id or a pod UID to disambiguate would be the wrong choice. Applying the additional labels will cause the metrics to break down because now you have split the actual identity in an effort to wrongfully disambiguate. In all of the above, the SDS011 pattern remains consistent. This goes to show that whenever there is a lifecycle churn in an entity (rebooting, redeployment, re-provisioning, or a human retyping a model name), the identifier (the SNMP agent, the kubelet, your ingestion job) will drift. To bypass this issue, a stable identity needs to come from attributes intrinsic to the entity, and not from the observer’s counter.

Two ways it breaks

There are two failure modes here that mirror one another.

Fragmentation: one entity, many keys. As seen in the SDS011 and the pod-UID case, the real entity picks up multiple keys through string drift (spacing, case, punctuation, typos, re-provisioning, firmware bumps). Symptoms include inflated counts, split history, alert storms after fleet updates, and baselines that never converge as data keeps arriving under “new” identities.

Collision: many entities, one key. This is the reverse problem, which is even worse because it is silent. Here, two genuinely different entities get mapped to the same key, usually by an over-eager “clean the data” step. The openSenseMap titles show the raw material: PM1 and PM10 differ by one character. Furthermore, PM2.5 is written as PM25 (124 vs 22 occurrences), and a careless “strip non-alphanumerics” pass merges PM2.5 into PM25, one rule away from also clobbering PM2.

Fragmentation inflates the counts while collision averages two real signals into one, hiding any anomalies. Of the two, the latter is a worse problem as it silently corrupts the data and is difficult to diagnose.

This begs the question, which tokens actually carry identity? For model-numbered gear, a decent heuristic is that the digit-bearing pieces (011, 6050, 2.5, v2) carry most of it while the words around them tend to be just noise. Not universal though, as some products have no digits. Others put the identity in a word — think “Pro” versus “Pro Max” — which makes it the attribute that matters. Where the heuristic does hold, a workable rule matches the model tokens verbatim and treats the surrounding words loosely with an explicit exclusion list so that a case or a cable never gets merged onto the device it fits. That matcher is the subject of the following deep dive; here we are marking the line between “drift of one identity” and “two identities.”

The blast radius could become permanent

Many data lakes make records immutable once written with append-only snapshots, audit tables, anything that guarantees a row cannot be quietly edited after the fact. That machinery seals whatever grouping you hand it. So if the identity is wrong at ingestion, you do not just get a wrong number; you get it sealed in the data lake as a fact. We can now prove, beyond dispute, that nobody touched a record that was wrong the day it was written. Integrity and correctness are not synonymous, and confusing the two can enable a pipeline to launder a data-model bug into a permanent, immutable fact.

“We’ll just dedupe it later,” and why that never pays off

The standard reassurance is that drift is a data-quality problem for the warehouse. Let it land, and it will be cleaned downstream. That reasoning has some serious holes. Here’s why. A surrogate key acquires dependents the instant it is written. Other rows take foreign keys against it immediately: readings, alarms, ownership records. By the time your “clean it later” job runs, merging SDS 011 and SDS011 into one identity means re-parenting every dependent row, reconciling aggregates that were computed against both, and resolving conflicts you no longer have the context to resolve. If the records were sealed, you cannot even rewrite them; the best you can do is append a correction and pray every reader honors it.

Compare that to getting it right at the door: one pure function, applied on ingestion, before a single foreign key exists. The asymmetry is measured in orders of magnitude. Pennies now, a migration-with-data-loss later. Deferring it does not save the work; it multiplies it and adds data loss on top.

The fix is a natural key at the door

Instead of minting an identity from the observed string, derive a natural key from it instead. It is a deterministic, canonical form that collapses meaningless variation while keeping the meaningful distinctions.

Concretely, identity-on-ingest is one normalization function, applied the same way everywhere:

NFKC  →  strip control/format chars  →  casefold
      →  NFKC again  →  strip meta-characters  →  strip whitespace

When you run the SDS011’s four faces through it, SDS 011, SDS011, and sds011 all converge to sds011. The typo SDS1001 stays separate, correctly, because it is a genuinely different string and catching typos is a fuzzy-match job, not a normalization one (covered in the next deep dive, leaning on the record-linkage tradition of Fellegi and Sunter [1969] and Christen [2012]).

The corollary is one physical sensor, one key, for three of the four, with zero fuzzy matching. Those exact steps are important because real drift is Unicode-deep, and the 88 units prove it. Look at the unit µg/m³:

Those two strings are pixel-for-pixel identical and byte-for-byte different. A GROUP BY unit, a dictionary keyed on the raw string, a DISTINCT count, all treat them as two units forever. That is exactly what NFKC (Unicode Normalization Form KC, defined in Unicode Standard Annex #15) is for. It maps the compatibility character U+00B5 to the canonical U+03BC, so after normalization both become the same key. The same step fixes CO₂ (subscript ₂, U+2082, 48 occurrences) versus CO2 (8). The failure and the fix land on the same real data point, 1027 times one way and 4 times the other.

Two rules keep this from becoming a problem

One normalizer, one definition, everywhere. You want to run the exact same function on the write path in the domain model, and in any warehouse. If two call sites normalize even slightly differently, you will have forked one entity into two again, and once the warehouse job runs, you will have sealed a value that differs from the one your uniqueness constraint deduplicated on.

The normalizer is a single-source-of-truth invariant. The output has to be idempotent, f(f(x)) == f(x), that is not automatic. Casefolding can emit sequences that need re-normalizing, so the pipeline repeats until the output stops changing (this is Unicode’s NFKC_Casefold, essentially), pinned by a property test so re-normalizing stored keys is always safe.

One thing to call out here because it is a real judgment and not a free lunch is that “strip meta-characters” step also removes joining marks like hyphens and underscores. That rule makes MPU-6050 and MPU6050 converge as well. Great for sensor model names, but in a namespace where X-100 and X100 are genuinely different parts, that same step over-merges and you would have to retune it. The decimal point is kept deliberately, and is the whole reason PM2.5 and PM25 stay apart. Normalization is conservative on purpose; where you draw the line is a domain call you should make consciously. Demote the surrogate; bind identity to the natural key. Keep a UID if you like, it is handy plumbing for foreign keys. But the entity’s identity should commit to the natural key, not the UID. The payoff is because identity is a function of intrinsic content instead of an auto-increment counter that depends on insertion order. Your records become reproducible across a database re-seed and verifiable from an export.

The drop is real but deliberately modest. It collapses the straightforward spelling issues. 15 sensorType spellings and 10 unit spellings fold into their canonical forms, at zero risk, purely from fixing case, spacing, separators, and look-alike Unicode. It does not close the gap between 99 keys and the roughly two dozen real models, and it must not. Normalization is kept modest by design so the leftover issues can be handled in the distance matching step.

The honest limits

Full disclosure: normalization fixes spelling drift such as spacing, case, Unicode, and punctuation, but it does not resolve cross-source identity matching, the same physical device seen through two gateways under two unrelated names, or the SDS1001 typo that no canonicalization will ever fold into sds011.

That is a similarity-and-distance problem, and pretending normalization covers it is how collisions sneak back in. The right move is to keep the raw observed strings next to the natural key as a retro-matchable signal, and run matching as an explicit, auditable step.

Five things to take away

1. Never mint identity from an observed string. An observed string is essentially the observer’s guesswork, recorded at the least-informed moment. Derive a natural key instead.
2. Your normalizer must be idempotent and singular. One function, one definition, across every path that touches identity. Pin idempotency with a test.
3. Identity is decided at ingestion, not in the warehouse. A key acquires dependents the instant it is written. Deduping later is a migration step that comes with data loss.
4. Integrity is not correctness. Sealing a wrong grouping makes it permanently, provably wrong. So, get the identity right before you seal.
5. Keep a surrogate for plumbing, anchor identity to the natural key. Reproducibility and external verifiability fall out of binding identity to intrinsic content.

Summary

In real world, identity drift is a wide-spread problem unless the source of data is fully authoritative. This article has covered the step 1 of resolving this problem which is the normalization layer. Although the reference implementation is showcased on an IoT API, it is relevant to multiple other domains. If you’d like to explore the subject in depth, my following deep-dive articles on this series include similarity matching that handles what normalization cannot (SDS1001, cross-source duplicates); dynamic cadence, adaptive polling, and noise filtering for high-volume feeds; and finally snapshots built in the data lake after an identity is corrected so the seal protects a true grouping.

Here is the reference implementation anchorkey and open-source framework that sits underneath device-identity layers like Eclipse Ditto and Eclipse Hono, both of which assume a stable device identity already exists.

You can run it on the real openSenseMap sample in under a minute:

pip install -e .
python examples/quickstart.py from anchorkey import normalize
normalize("SDS 011") == normalize("sds011")  # spacing/case gone
normalize("µg/m³")   == normalize("μg/m³")    # look-alikes merged
normalize("PM2.5")   != normalize("PM25")     # "." kept

If you have hit this in your own pipelines, I would love to hear how it showed up.

Reproducing the data

Every number here comes from one re-runnable pull against the public openSenseMap API (data 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 pull_drift_sample.py script in the repo fetches that response, snapshots the raw JSON, and emits the distinct-string tables used above. This capture: 2026-06-26 UTC, 719 boxes, 114 distinct sensorType / 214 distinct title / 88 distinct unit strings. Live counts drift over time, which is fitting, so the figures are built from the archived snapshot, deposited at DOI 10.5281/zenodo.20989076, not from live calls.

References

• RFC 2863, The Interfaces Group MIB (ifIndex stability)
• Kubernetes docs, Object Names and IDs
• Prometheus docs, Data Model
• E. F. Codd, A Relational Model of Data for Large Shared Data Banks, CACM 13(6), 1970.
• I. P. Fellegi & A. B. Sunter, A Theory for Record Linkage, JASA 64(328), 1969.
• P. Christen, Data Matching, Springer, 2012.
• Unicode Standard Annex #15, Unicode Normalization Forms
• openSenseMap

Web sources accessed 2026-06-27.