SQL for SCADA Engineers: Getting Answers Out of a Historian

The short answer

Historian data is narrow and enormous: typically a tag identifier, a timestamp, a value and a quality flag, with billions of rows. Almost every useful question is answered by filtering a time range first, aggregating into buckets, and only then joining to anything else. Get that order wrong and you will wait an hour for a query that should take a second, or lock a table on a live production server.

The shape of the data

A relational business table is wide and short. A historian table is the opposite:

Two consequences. First, the timestamp column is the index that matters, so the time filter must come first and must be sargable: ts >= '2026-09-01' AND ts < '2026-09-08' uses the index, WHERE YEAR(ts) = 2026 does not, because wrapping the column in a function stops the index being used. Second, quality is not decoration. A value with bad quality is a sensor that was disconnected, and averaging it in will quietly move your answer.

The patterns that answer most questions

A trend, bucketed. Raw one-second data over a week is 600,000 rows per tag and nobody can read it. Aggregate into intervals:

SELECT
  DATEADD(minute, DATEDIFF(minute, 0, ts) / 15 * 15, 0) AS bucket,
  AVG(value) AS avg_value,
  MIN(value) AS min_value,
  MAX(value) AS max_value
FROM history
WHERE tag_id = 4471
  AND ts >= '2026-09-01' AND ts < '2026-09-08'
  AND quality = 192
GROUP BY DATEADD(minute, DATEDIFF(minute, 0, ts) / 15 * 15, 0)
ORDER BY bucket;

Time above a limit, which is the question behind most alarm and compliance reports. Count the samples above the limit and multiply by the sample interval, or use LAG to get the real duration between samples when logging is on change rather than periodic.

Downtime from a run bit, which needs the previous row's value to find the transitions:

SELECT ts AS stopped_at,
       LEAD(ts) OVER (ORDER BY ts) AS restarted_at
FROM (
  SELECT ts, value,
         LAG(value) OVER (ORDER BY ts) AS prev
  FROM history
  WHERE tag_id = 5120 AND ts >= '2026-09-01'
) t
WHERE value = 0 AND prev = 1;

Joining to context. Batch numbers, shift, product and order live in another system. Join them last, after the aggregation has reduced millions of rows to hundreds.

Four ways to hurt a production server

1. Querying the live historian during production without a time filter. A full scan of a billion-row table will affect the SCADA system that plant staff are using.
2. Running reports against the primary rather than a replica or a reporting copy. Ask for one.
3. SELECT \* on a wide historian view, which drags back columns you do not need across the network.
4. Holding a transaction open while a report runs. Use read-committed snapshot or the vendor's read-only interface where one exists.

Historians from AVEVA, Ignition, FactoryTalk and PI each expose their own query layer over the raw tables, and it is usually better optimised than direct SQL. Learn the native interface first and drop to SQL when it cannot express the question.

From query to something people use

A query nobody runs is worth nothing. Put the result somewhere the plant already looks: a scheduled email, a Power BI page on the wall, or a page in the SCADA itself. The Power BI OEE dashboards course covers the reporting layer, SQL for PLC and SCADA engineers covers the query side properly, and Ignition SCADA has the most approachable built-in historian to practise against. Background on the storage side is in the SCADA historian guide.

Frequently asked questions

Do I need to be a database administrator? No. You need SELECT, WHERE, GROUP BY, JOIN and the window functions LAG and LEAD. That is most of it.

Can I write to the historian? Do not. Historians are append-only by design and correcting history destroys the audit value of the record.

Why is my average wrong compared with the SCADA trend? Usually because the SCADA is time-weighting the average and a plain AVG is not, or because bad-quality samples are included. Both differences are large on data logged on change.