When analyzing IoT data, 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. Identifier strings start to drift through spacing differences, case variations, typos, or re-provisioning, causing a single physical entity to split into multiple keys. When that happens, every count, average, and threshold alert a dashboard produces goes wrong—without exceptions or red flags. As these errors accumulate, the dashboard eventually reaches a point where it can no longer be trusted. As of 2026, with data lakes becoming increasingly central to enterprise analytics, this problem is more critical than ever, as even small drift can cascade across joins and aggregations at scale.
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 the 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 costly mitigation.
One sensor, four identities
The Nova Fitness SDS011 is one of the most common cheap particulate sensors on the market. In a pull of 719 stations, that one model shows up under four distinct sensorType strings:
| sensorType | count | what it is |
|---|---|---|
| SDS 011 | 239 | SDS011, with a space |
| SDS011 | 16 | SDS011, canonical |
| SDS1001 | 2 | SDS011, transposed digits (a typo) |
| sds011 | 2 | SDS011, lower-cased |
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 specific to openSenseMap; it is a general pattern for any IoT pipeline ingesting heterogeneous sources.