Alarm Data Is Not Time Series Data
An alarm is an event with a lifecycle, not a sampled signal. Store it as time series and short alarms vanish and counts stop being true.
By John Wassilak
Ask your warehouse how many times a compressor tripped last month. If alarm data landed the same way pressure readings did, the number you get back is wrong, and it is wrong in a direction nobody notices.
The reason is that an alarm is not a measurement. It is an event with a lifecycle, and storing it as though it were a sampled signal destroys the part you actually wanted.
A boolean sampled on scan is not an event
Pressure is a continuous quantity. Sample it every minute and you have a reasonable picture of what it did, because between two samples it moved smoothly and the samples bracket the movement.
An alarm bit is discrete. It went true at some instant, stayed true for some duration, and went false at another instant. Sample that every minute and three things break at once.
Short alarms vanish. A trip that raises and clears inside one scan interval leaves no trace at all. On a one-minute scan, an alarm that lasted forty seconds never happened as far as your data is concerned.
Counting stops being a count. “How many alarms” over a column of booleans means counting false-to-true transitions, so every consumer ends up writing some version of this:
-- how many times did this alarm actually raise?
select tag_id, count(*) as activations
from (
select tag_id, ts, state,
lag(state) over (partition by tag_id order by ts) as prev
from alarm_scan
) t
where state and not coalesce(prev, false) -- false-to-true edge only
group by tag_id
That is not hard, and it is not the point. The point is that every consumer has to write it, some of them will get the coalesce wrong at the partition boundary, and a wrong version returns a plausible number rather than an error.
Duration becomes an estimate. The alarm was active for somewhere between four and six minutes, depending on where the scan boundaries fell. That is fine for a trend and useless for reporting how long a unit was in an alarm state.
None of this shows up as a failure. Every query runs. The dashboard renders. The numbers are simply not the numbers.
What an alarm actually carries
Model it as what it is: an occurrence with a start, an end, and some state in between.
A single alarm instance has a raise timestamp, a clear timestamp, and usually an acknowledge timestamp somewhere between them. It has a priority, a source that resolves to equipment and then to a facility or a well, and a condition saying which limit was violated. Often it carries an operator action, because somebody acknowledged it and occasionally recorded why.
The lifecycle is the part that a time-series column cannot express:
stateDiagram-v2
[*] --> Normal
Normal --> UnackedActive: limit violated
UnackedActive --> AckedActive: operator acknowledges
UnackedActive --> UnackedCleared: returns to normal, still unacknowledged
AckedActive --> Normal: returns to normal
UnackedCleared --> Normal: operator acknowledges
UnackedCleared --> UnackedActive: re-raises before acknowledgement
That path from unacknowledged-and-cleared back to unacknowledged-and-active is the one that breaks naive models, and it is why the overlap test further down needs a qualifier.
That is a row per occurrence, not a row per scan. One alarm that stood for six hours is one row, and its duration is arithmetic rather than inference.
The shape is the same shape you would use for any event log, which is the useful realization here. Operational teams often treat alarm data as a special OT problem. It is a special OT problem in what it means, and an ordinary event-modeling problem in how it should be stored.
Where it goes in the stack
Bronze takes the raw stream exactly as the source provides it, same as everything else. If the source only exposes alarm state on scan rather than an event feed, bronze gets the scan data, because bronze is the replay layer and you cannot reconstruct what you did not keep.
Silver is where the transformation matters. The scan stream gets collapsed into events: detect the transitions, pair each raise with its clear, carry the acknowledge if the source has one, and emit one row per occurrence. Where the source offers a proper alarm and event feed, silver takes that directly and skips the reconstruction entirely. OPC UA Alarms and Conditions is the modern interface for this, the older OPC A&E still turns up, and most SCADA platforms expose an alarm journal of their own. Either way silver’s output is an event table, and every downstream consumer reads that instead of the raw bits.
This is the same silver-layer principle that governs quality flags and unit conversion. The opinion gets encoded once, by someone who knows what the right answer is, rather than reimplemented per dashboard.
Two tests belong on that table from the first day, and both are cheap to write as singular dbt tests: no alarm may clear before it raised, and no source may have two simultaneously active and unacknowledged instances of the same condition. That second one needs the qualifier, because the ISA-18.2 state model allows a condition to return to normal while still awaiting acknowledgement and then re-raise, so a naive no-overlap test fires falsely against a spec-compliant source. Both catch transition pairing going wrong, which is the failure to expect when a source restarts mid-alarm.
Chattering alarms will ruin your counts
One failure mode is specific to this data and shows up early.
A sensor sitting exactly at its limit raises and clears repeatedly. ISA-18.2 puts a number on it: three or more activations in a minute makes it a chattering alarm, which is 180 an hour from one instrument. Operators know these points and mentally filter them. Your event table does not.
Left alone, a handful of chattering points will dominate every count you produce. The “top ten alarms by frequency” report becomes a list of instruments that need calibration, which is useful exactly once and then never again, while the alarms that actually matter sit below the fold.
Handle it explicitly rather than by accident. Collapse repeat occurrences of the same condition on the same source within a short window into one occurrence with a repeat count. Keep the raw events in bronze so the chatter itself stays analyzable, because a chattering instrument is a maintenance signal in its own right. Then report on the collapsed table.
The alarm management field has thought about this considerably more than data teams have; ISA-18.2 is the relevant standard if you want the operational framing. The data-side lesson is narrower: deduplicate deliberately, in a documented place, or your counts belong to your worst instrument.
What the right model buys you
Once alarms are events, a set of questions becomes trivial that were previously projects.
Alarm rate per operator position per hour, which the standards put hard numbers against: EEMUA 191 established fewer than six per hour as the target and more than thirty per hour as seriously deficient, and ISA-18.2 carries the same framing. More than ten alarms in a ten-minute window on one position is a flood. Those thresholds measure directly whether the people watching the screens can respond at all, and you cannot report against them without an event model.
Standing alarms, meaning conditions that have been active for days and that everyone has stopped seeing. These are invisible in a scan-sampled model and obvious in an event model, and they are frequently the first thing an operations lead acts on.
Bad actors by asset rather than by tag, which requires the source-to-equipment resolution you built for everything else and immediately tells you which units are consuming attention.
Correlation with production events. The compressor tripped four times and the well went down each time, and the timestamps line up. That is a question about two event streams, and it is answerable in one join once both are modeled as events.
Alarm bits were around 6 percent of the tags on the one platform we inventoried, as the tag taxonomy post sets out, and that post is clear the figure describes one estate rather than an industry. Whatever the exact share, it is a small fraction of the tag count and a large fraction of what operations will ask you for, because alarms map directly onto things that went wrong.
Decide before you ingest
The practical point is timing. Once alarm bits have landed in a general time-series table alongside pressures and temperatures, recovering the event structure means reconstructing transitions from samples, and everything lost to scan-rate aliasing stays lost.
Separate them at classification time, before the first row lands. Route them to an event model. Let the pressures be a time series and let the alarms be what they are.
Do this at classification time and it costs nothing. Do it after a year of alarm bits have landed in the tag table and you are reconstructing state transitions from samples, with everything shorter than the scan interval permanently gone.
The dashboard looks about the same either way. That is the awkward part: nothing about a wrong alarm count announces itself.