Telemetry lake & SCADA historian
Water utility, 30 years of depot SCADA historians. One telemetry lake with gap backfill, calibration-drift alerts and a 10-year replay.
The situation.
A water utility with 30 years of fragmented SCADA historians across depots.
ScopeA single telemetry lake for flows, pressures and levels: protocol adapters to site RTUs, gap detection and backfill, calibration-drift alerts, and 10-year replay for model calibration and regulator queries.
10-year telemetry online; drift flagged before field crews notice.
Data enters at the depot edge. Site RTUs publish over MQTT into per-generation protocol adapters, which normalise tag names, units and timestamps before anything is stored. Adapters write into TimescaleDB hypertables, one family per signal, flows, pressures and levels, with ingestion-time sequencing applied on arrival and the source clock preserved alongside. A completeness index drives gap detection and backfill jobs that pull from depot historians to close holes. Calibration-drift alerts evaluate stored series on a schedule and land in Grafana beside the live operations views. The replay layer reads the same hypertables and serves 10-year windows to model-calibration workflows and regulator queries.
Considered: Migrating historic series into canonical units at load time
Chose: Statistical step-change detection with per-series unit lineage
Because: Source upgrades had changed units without notification, and one canonicalisation rule applied across 30 years would have rewritten whole spans of history wrongly.
Considered: Trusting source clocks for event ordering
Chose: Ingestion-time sequencing with source-clock preservation
Because: RTU clock drift corrupted ordering across depots, and operations and replay questions needed one answer, with the original reading still visible for audit.
Considered: Swapping depot SCADA for a modern platform
Chose: Leaving depot SCADA in place and pulling from it through adapters
Because: The control layer was not the risk; the fragmented record was, and a replacement programme would have delayed the record by years.
Considered: A generic wide-table schema for every signal
Chose: One hypertable family per signal with unit-lineage columns
Because: Flows, pressures and levels share query patterns while their replay windows and quality expectations differ, and lineage belongs beside the data it describes.
TimescaleDB carries the load: interval scans across decades and continuous aggregates for daily operations. MQTT was already the lingua franca of the RTU fleet across generations, so every adapter subscribes. Grafana was chosen because duty engineers already read it; drift alerts land right there, on the surface they watch every shift.
Two verified figures anchor this engagement: 30 years of fragmented historians consolidated, and 10 years of telemetry online in the lake. Both come from the delivered scope. No throughput, latency or completeness percentages were certified on delivery, and none are claimed here. The verified acceptance outcome is qualitative: calibration-drift alerts fire on statistical departures before field crews notice the symptoms on site. That was the acceptance criterion.
Discovery found three problems stacked on one record
A water utility with 30 years of SCADA history across depots asked for one telemetry lake. The historians themselves were not the finding. Three findings were. Historic series changed units when source equipment was upgraded, and nothing recorded the change, so the units behind a tag could shift silently. RTU clocks drifted, and event sequences assembled from source timestamps interleaved wrongly across depots. And no depot could answer a question about a decade ago without an archaeology exercise, which is exactly what regulator queries and model calibration demanded. Sensor networks also shared linear-network models, which mattered for later grouping and comparison. Epcotec wrote the brief around the record, and the dashboard followed from it.
The hard problem was the history itself
Live ingestion is a solved problem; making 30 years of history trustworthy is the open one. The unit changes hid in step changes: a series that moves sharply overnight may be an event or a rescaling, and only statistics on the transition plus the source paperwork settle it. Step-change detection ran over the historic corpus and mapped unit lineage per series, so every point carries the unit regime it was recorded under. Clock drift attacked ordering. Ordering by source timestamp interleaved a drifting RTU against a true one, producing cross-depot sequences out of physical order. Ingestion-time sequencing fixed the ordering operations questions needed, while source-clock preservation kept the original reading visible, so the corrected record still carries the evidence it fixes.
The build kept the control layer and unified the record
Depot SCADA stayed in place. Per-generation protocol adapters subscribe over MQTT and normalise tags, units and timestamps at ingestion, one adapter per historian generation. Series land in TimescaleDB by family, each carrying unit lineage and both clocks. A completeness index drives gap detection; where the lake is thinner than the depot record, backfill jobs pull the missing span. Calibration-drift alerts run on a schedule over stored series and land in Grafana next to the views duty engineers already watch. The replay layer serves 10-year windows off the same hypertables. Operations and analysis stopped being two jobs over two stores.
Verification started at the worst depot
We proved the record on the depot with the messiest history. Gap detection and backfill were exercised there first, because a pipeline that closes gaps on clean series proves nothing. Step-change detection was checked against every flagged transition; unit lineage had to explain each one or the detection was wrong. Replay windows were pulled for model-calibration extraction and regulator-format queries from the same store operations reads. The acceptance criterion was explicit: drift alerts arrive before field crews notice the symptoms on site, and the engagement closed against it with 10-year telemetry online.
Handover, and what the lake bought
Handover followed the studio's standard shape: the utility's operations team took the adapters, the alert thresholds and the replay extraction, with documentation, a walkthrough and 30 days of post-launch support. What the lake bought was a new class of question. A regulator question about any point in a decade became a query, and model calibration stopped waiting on manual extraction. Calibration drift moved from field symptom to reported condition. And the two failure classes discovery found, silent unit changes and drifting clocks, became first-class categories in the record with checks behind them. Sensor grouping followed the linear-network models the nets already share, so comparisons ran along each network, following the physical line.