Handling Duplicate Sensor Readings in a Building Time-Series Store

Duplicate telemetry is an unglamorous problem with expensive consequences. A reading stored twice does not look wrong; it looks like a reading. But an average over duplicated data is weighted wrongly, a count is inflated, and a consumption total built from interval values is simply doubled for the affected period. The error is silent, it is usually confined to particular periods or particular points, and it is nearly always introduced by something that was done for a good reason.

The message up front: decide the identity of a reading and enforce it at the point of storage. For building telemetry that identity is almost always the point plus the event time, and making it a uniqueness constraint rather than a convention is what turns retries and replays into harmless no-ops. Then handle the genuinely hard case deliberately: two different values claiming the same identity, which is not a duplicate but a conflict, and which means something upstream is wrong.

1. Where duplicates come from

• Retries after a partial failure. A write that succeeded but whose acknowledgement was lost gets re-sent. Correct behaviour from the sender, and the most common source.
• Buffer replay. A gateway unsure what it delivered re-sends an overlapping range, which is the designed behaviour described in store-and-forward buffering.
• Trend log re-reads. A collector that loses its read position re-reads the device buffer from the start, per BACnet data logging.
• Two collection paths for one point. A gateway and a head end both forwarding, usually left over from a phased project. The most insidious source because it is continuous rather than episodic.
• Backfill overlapping live data. A manual import to fill a gap that extends past the gap.
• A point mapped twice. The same physical point appearing under two identities in the map, which is a mapping defect rather than a storage one and is covered in point-to-asset mapping.

Note that only the last two are defects. The first four are normal operation, which is why the receiving end must absorb them rather than the sending end being asked to stop.

2. What makes two readings the same reading

The key question, and the candidate answers are not equivalent.

So: point identity plus event time, with the point identified structurally rather than by name, for the reasons in BACnet objects and addressing. A message identifier alongside it is worth carrying for diagnostics but should not be the key.

3. Conflicts, which are not duplicates

Two arrivals with the same identity and different values are a different problem, and treating it as a duplicate loses information.

It means one of a small number of things, all worth knowing:

• Two collection paths disagreeing, usually because they are reading at slightly different moments or applying different scaling. The cause is the duplicate path, not the data.
• A scaling or multiplier change applied between the two collections, which is the discontinuity problem in sensor units and scaling.
• A clock error making two genuinely different moments look like one, per timestamps and clock synchronisation.
• A corrected backfill, where somebody deliberately replaced historical values. Legitimate, and it should be recorded as a correction rather than arriving as a conflict.

The handling: reject the write and flag the conflict rather than silently keeping either value. Last-write-wins is the common default and it is the worst option, because it makes the outcome depend on arrival order and leaves no trace that two sources disagreed.

4. Finding duplicates already in the data

• Count rows per point per interval against the expected one. Anything above one is a duplicate, and the query is trivial once event time is stored.
• Look for periods with double the expected row count. These cluster, usually around an outage or a reconfiguration, and the cluster boundaries identify the cause.
• Check interval sums against register differences on meters. A consumption total exactly double the register movement is unambiguous, and this is the check that most often first reveals the problem.
• Compare points that should be identical. Where the same physical point is mapped twice, the two series will be suspiciously similar, which identifies the mapping defect.
• Trend the duplicate rate. A steady low rate suggests retries, which are benign once the key is enforced. A step suggests a second path was added.

5. Cleaning up without making it worse

• Fix the cause first. Enforce the key, then remove the duplicate collection path. Deduplicating historical data while the source continues producing duplicates is work you will repeat.
• Deduplicate to a rule, recorded. Where values agree, collapsing is safe. Where they differ, the choice is a judgement that should be written down rather than embedded in a script somebody ran once.
• Keep the removed rows somewhere until the cleanup has been verified. A deduplication that turns out to have been keyed wrongly is unrecoverable otherwise.
• Recompute every aggregate over the affected periods, which is the same invalidation requirement as for late data.
• Mark the affected periods, because any figure previously reported over them was wrong and somebody may have quoted it.

6. What to require and test

• A uniqueness constraint on point plus event time in the store, demonstrated rather than asserted.
• A forced replay producing no new rows. Re-send a known range and confirm the row count is unchanged. Two minutes, and it proves the whole mechanism.
• A conflict surfacing as a flagged event, not as a silent overwrite. Test by writing a different value for an existing key.
• One declared collection path per point, recorded in the register described in the BMS point dictionary, so a second path is visibly an exception.
• Duplicate rate as a monitored measure, alongside the others in BMS ingestion monitoring.

The replay test belongs in the acceptance set in the BACnet integration acceptance test checklist, where it sits naturally alongside the outage recovery test.

The idea to walk away with

A reading has an identity, and in building telemetry it is the point and the moment. Declare that, enforce it where the data lands rather than in one application path, and every retry and replay becomes harmless.

Then treat a conflicting value for an existing identity as the finding it is, rather than resolving it by arrival order. Two sources disagreeing about one moment is always worth knowing about, and last-write-wins guarantees you never will.

Final thoughts

The cheapest check available: count rows per point per interval for the last month and look for anything above one. It takes one query and it either reassures you or identifies the periods and the points affected.

On estates that have been through an outage, a gateway replacement or a phased integration, the result is rarely entirely clean, and the periods it flags usually line up neatly with something in the change history.

Related reading: Late and out-of-order readings, Store-and-forward buffering, BMS ingestion monitoring, BMS point-to-asset mapping, BACnet data logging, Energy meter reset and rollover.