Avoiding Entity Key Drift in a Data Lake: Step 1, Normalization
Avoiding Entity Key Drift in a Data Lake: Step 1, Normalization
避免数据湖中的实体键漂移：第一步，规范化
Tackling the bedrock problem of maintaining identity in a high-frequency data lake 解决高频数据湖中维护身份标识这一基础性难题
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. 在分析物联网（IoT）数据时，将观察到的字符串直接视为身份标识是一种带有隐形成本的捷径。在首次观察到标识符字符串时为其分配主键是一个合理的起点。使用自增 ID 或在首次遇到时生成的 UID 非常方便，易于实现，并且在数据干净且受控的演示和测试环境中表现良好。
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. 在本文中，我将通过 719 个真实环境传感器展示这种故障，并指出在生产环境中常见的三种系统（SNMP、Kubernetes 和 Prometheus）中存在的相同模式。此外，我将介绍本系列后续文章的核心解决方案：从观察到的字符串中推导出自然键，而不是直接从中生成身份标识。
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. 该架构展示了在 openSenseMap API 上的实现，完整的代码（可在 GitHub 和 Zenodo 上获取）附在文末，以便您可以自行运行。如果您需要分析设备群的遥测数据、关联多个来源的数据，或需要密封记录以供审计，本文将非常适用。然而，如果您的系统中每个实体都拥有单一、稳定且权威的标识符，且从不被重新输入、重新配置或从外部来源协调，那么本文对您可能帮助有限。对于其他人来说，在问题演变成需要补救的灾难之前，理解它非常有价值。
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:
Nova Fitness SDS011 是市场上最常见的廉价颗粒物传感器之一。在对 719 个站点的数据拉取中，该型号以四种不同的 sensorType 字符串出现：
| sensorType | count | what it is |
|---|---|---|
| SDS 011 | 239 | SDS011, with a space |
| SDS011 | 16 | SDS011, canonical |
| SDS100 | 12 | SDS011, transposed digits (a typo) |
| sds011 | 2 | SDS011, lower-cased |
One physical sensor model with four keys. Figure 1. One model, four sensorType strings. The most common spelling is SDS 011 (239), not the canonical SDS011 (16). If you key a fleet count on this string, you are wrong in the majority case, not at the margin.
一个物理传感器型号对应四个键。图 1：一个型号，四个 sensorType 字符串。最常见的拼写是 SDS 011 (239)，而非规范的 SDS011 (16)。如果您基于此字符串进行设备群计数，那么您在大多数情况下都是错误的，而不仅仅是边缘误差。
Now consider the obvious ingestion design. A sensors table whose identity is the observed model string: 现在考虑一种显而易见的摄取设计。一个以观察到的型号字符串作为身份标识的传感器表：
-- The latent bug, in three lines.
-- 潜伏的 Bug，仅需三行。
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.
现在您有了四行 SDS011 的记录。因此，每一个按 sensor_models.id 分组的查询——无论是部署的 SDS011 数量、按型号计算的 PM2.5 平均值，还是在型号错误率激增时触发的警报——其计算结果都基于不对应真实实体的行。随着型号目录扩大 4 倍，每个型号的平均值将被拆分为四份。SDS011 的错误率基准永远无法看到归类在 SDS 011 下的读数，从而导致仪表盘显示错误。
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. 这里值得停下来思考的是，代理 ID（surrogate id）并不是问题所在。即使完全不使用代理 ID，您也会遇到完全相同的 Bug。真正的错误在于将观察到的字符串视为身份标识，因为为新字符串生成的 UID 最终会继承这种漂移。因此，后续的修复方案不是停止使用代理键，而是停止从观察者随意输入的字符串中推导身份标识。
You have already shipped this bug (just not in IoT)
您可能已经发布过这个 Bug（只是不在物联网领域）
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.
SNMP ifIndex。 身份标识源自 ifName、ifAlias 和 ifIndex 的组合。但 ifIndex 本身并不稳定。拔出网卡、重启或重新配置设备都可能改变 ifIndex。例如，在维护窗口之后，“端口 3”的监控图表可能会开始绘制另一个物理端口的数据，而没有任何迹象表明发生了变化。RFC 2863 文档中对此有记录，该文档添加 ifName 和 ifAlias 正是出于这个原因，因为索引旨在用于簿记，而非身份标识。
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. Kubernetes pod UID。 在 Kubernetes 中，每个 Pod 在创建时都会获得一个 UID。然而，UID 本身并不是身份标识，因为 Pod 在任务完成后会被销毁。