An upsert write pattern is the way to load SCADA readings into a database so that loading the same reading twice corrects the row rather than duplicating it. Upsert means insert-or-update: if a row with that key does not exist, insert it; if it does, update it in place. For telemetry, where messages get retried, replayed, and sometimes arrive late, this idempotent behavior is what keeps the table clean without a nightly deduplication pass. This page explains how to key an upsert on tag plus timestamp, how it handles replays and late-arriving data, and why it beats blind inserts that need a downstream cleanup.
Upsert write pattern for SCADA data in one line: An upsert write pattern loads each SCADA reading with an insert-or-update operation keyed on a unique combination such as tag plus timestamp, so a repeated or corrected reading overwrites the existing row instead of creating a duplicate. This makes the load idempotent, meaning replays and late-arriving data land correctly without duplicating rows or needing a separate dedup step.
An upsert only works if the database has a way to tell whether an incoming reading is new or a repeat, and that comes from a key that uniquely identifies a single reading. For time-series telemetry the natural key is the combination of the tag identifier and the timestamp: one specific point at one specific instant should have exactly one value. Declaring that pair as the primary key or a unique constraint gives the database the anchor it needs, so a write can ask insert this reading, but if a row already exists for this tag at this timestamp, update it instead.
In SQL this is expressed with a MERGE statement or a dialect-specific insert-on-conflict clause: the engine matches the incoming rows against existing ones on the key and routes each to an insert or an update accordingly, all in one atomic operation. The key choice has to reflect the data's real grain. If a source can legitimately produce more than one reading per tag per timestamp, perhaps distinguished by a sub-second component or an event sequence, that discriminator has to be part of the key too, or genuine distinct readings will collide and overwrite each other. Getting the key to match the true uniqueness of the data is the crux of a correct upsert.
It is worth being deliberate about what an update does on conflict. For a plain overwrite, the incoming value simply replaces the stored one, which is right when a later delivery of the same reading is a correction. Where quality or provenance matters, the update can be conditional, for example only overwriting if the new reading has better quality or a later processing time, so a good value is not clobbered by a re-delivered bad one. The key decides whether rows collide; the update logic decides who wins when they do.
Telemetry pipelines are full of situations that deliver the same reading more than once. A network hiccup makes a producer unsure whether its last batch landed, so it resends. A consumer restarts and reprocesses from its last committed offset, replaying the tail of a stream. An operator reruns a failed load. In every case the same tag-timestamp readings arrive again, and with an upsert this is harmless: each repeat matches the existing row on the key and updates it to the identical value, leaving the table exactly as it was. That property, that reprocessing changes nothing, is what idempotency means, and it is precisely what makes a pipeline safe to retry.
Late-arriving data is the other case upsert handles gracefully. A reading may be delayed by a buffered field device coming back online, a store-and-forward queue draining after an outage, or a clock correction, and it arrives with an old source timestamp long after later readings were already loaded. Because the upsert keys on tag plus timestamp rather than on arrival order, the late reading slots into its correct historical position, updating the row for that instant if a placeholder existed or inserting it if not, without disturbing anything around it. The stored history reflects when readings were true, not the order in which they happened to arrive.
This is a meaningful contrast with append-only ingestion for correctable data. A blind insert of every message is fast and simple, but a retried or late message becomes a second row for the same instant, so the table accumulates duplicates that every downstream query must then account for. Upsert pushes the correctness to write time, where the key naturally collapses repeats, so readers always see one row per reading. The two approaches can coexist, raw append for maximum ingestion speed feeding an upserted curated table, but wherever the served data must be clean, upsert is what keeps it that way as messages replay and arrive late.
The alternative to upserting is to insert everything blindly and clean up afterward, and it is worth being clear about that trade-off. Blind inserts are the fastest possible write because they skip the match step, so for pure high-volume landing of raw data they are attractive. The cost is deferred, not avoided: duplicates and late corrections now live in the table, and something downstream, a scheduled dedup query, a view that picks the latest row per key, a batch cleanup job, has to resolve them before the data can be trusted. Until that pass runs, every consumer sees duplicates.
Upsert moves that work to the moment of writing, so the table is correct the instant the load commits and no cleanup pass is needed. This costs a little more per write, since the engine must check the key and possibly update rather than just append, and on some warehouses a MERGE is heavier than a bare insert. For most SCADA integrations, where the served table needs to be query-ready and the volume is manageable per batch, that per-write cost is well spent to avoid a fragile, easily forgotten dedup step that becomes a silent source of double-counted totals when it fails.
For a cloud SCADA platform like Merobix loading readings from many field sites into SQL and warehouse tables, upsert keyed on tag plus timestamp is what lets the ingestion be aggressively retried without fear. A gateway that resends after an uncertain delivery, a consumer that replays after a restart, and a device that flushes buffered data hours late all resolve to the same clean, single row per reading, so dashboards, reports, and alerts read a table that is right by construction rather than one that depends on a cleanup job having run recently enough.
Use a key that uniquely identifies a single reading, which for time-series telemetry is normally the tag identifier plus the timestamp. Declaring that pair as a primary key or unique constraint lets the database detect whether an incoming reading is new or a repeat. If a source can legitimately emit multiple readings per tag per timestamp, add a discriminator so genuine distinct readings do not collide.
Because an upsert matches on tag plus timestamp rather than arrival order, a late reading slots into its correct historical position regardless of when it shows up. It updates the row for that instant if one already exists or inserts it if not, without disturbing the readings around it. The stored history reflects when values were true, not the order in which they were received.
A blind insert is faster per write but leaves duplicates and corrections in the table that a downstream dedup pass must resolve before the data can be trusted. An upsert does that reconciliation at write time using the key, so the table is query-ready the moment the load commits. That avoids a fragile cleanup step whose failure silently produces double-counted results.
Merobix reads your field devices into a cloud SCADA - the real thing behind these terms, live in days from any browser.