Querying historical data in SCADA systems - Deep Dive

Overview - Querying historical data

What is it?

Querying historical data means asking a system to find and show information that was recorded in the past. In SCADA systems, this data comes from sensors and machines that monitor industrial processes. The goal is to look back at this stored information to understand what happened, when, and how. This helps operators and engineers make better decisions based on past events.

Why it matters

Without the ability to query historical data, operators would only see what is happening right now, missing important trends or problems that developed over time. This could lead to poor decisions, equipment failures, or safety risks. Historical data helps find patterns, troubleshoot issues, and improve processes, making industries safer and more efficient.

Where it fits

Before learning to query historical data, you should understand how SCADA systems collect and store data. After mastering querying, you can learn how to analyze and visualize this data for reports and alerts. This topic fits between data collection basics and advanced data analytics in SCADA learning.

Mental Model

Core Idea

Querying historical data is like asking a smart librarian to find specific past records from a huge archive to answer your questions about what happened before.

Think of it like...

Imagine a library full of books where each book is a day of recorded events from machines. Querying historical data is like telling the librarian exactly which book, page, and paragraph you want to read to learn about past events.

┌─────────────────────────────┐
│       SCADA System          │
├─────────────┬───────────────┤
│ Real-time   │ Historical    │
│ Data Stream │ Data Storage  │
│             │ (Database)    │
└─────┬───────┴───────┬───────┘
      │               │
      ▼               ▼
  Current View    Query Engine
                    │
                    ▼
             Retrieved Data

Build-Up - 7 Steps

1

Foundation

Understanding SCADA Data Types

Concept: Introduce the two main types of data in SCADA: real-time and historical.

SCADA systems collect data continuously from sensors and devices. Real-time data shows the current state, like temperature or pressure right now. Historical data is saved over time in databases to keep a record of past values and events.

Result

Learners can distinguish between live data and stored data in SCADA systems.

Knowing the difference between real-time and historical data is essential because querying only applies to stored past data, not live streams.

2

Foundation

How Historical Data is Stored

Concept: Explain the storage methods used for historical data in SCADA systems.

Historical data is stored in databases designed for time-series data, often optimized to handle large volumes efficiently. Data points include a timestamp, value, and sometimes quality or status. Storage can be local or cloud-based, depending on the system.

Result

Learners understand where and how historical data lives inside SCADA.

Understanding storage formats helps learners know what data they can query and how queries retrieve it.

3

Intermediate

Basic Query Syntax and Filters

🤔Before reading on: do you think you can query historical data by time range only, or do you need other filters too? Commit to your answer.

Concept: Introduce how to write simple queries using time ranges and filters to find relevant data.

Queries usually specify a time range, like 'last 24 hours', and can filter by tags or sensor IDs. For example, a query might ask: 'Show temperature readings from sensor A between 8 AM and 10 AM.' Filters help narrow down large datasets to useful information.

Result

Learners can write basic queries to retrieve specific historical data slices.

Knowing how to filter by time and tags is the foundation for effective data retrieval in large datasets.

4

Intermediate

Aggregations and Summaries in Queries

🤔Before reading on: do you think querying historical data can only return raw values, or can it also calculate summaries like averages? Commit to your answer.

Concept: Teach how queries can include calculations like averages, minimums, maximums, and counts over time periods.

Instead of just raw data points, queries can ask for summaries. For example, 'What was the average temperature each hour yesterday?' This reduces data volume and highlights trends. Aggregations are common in SCADA reports and dashboards.

Result

Learners can write queries that produce meaningful summaries from raw historical data.

Understanding aggregations helps learners extract insights without overwhelming detail.

5

Intermediate

Handling Data Quality and Gaps

Concept: Explain how queries deal with missing or bad data points in historical records.

Historical data can have gaps or errors due to sensor faults or communication issues. Queries often include options to ignore bad data, fill gaps with estimates, or flag missing points. This ensures analysis is accurate and reliable.

Result

Learners know how to write queries that handle imperfect data gracefully.

Recognizing data quality issues prevents wrong conclusions from incomplete or faulty data.

6

Advanced

Optimizing Query Performance

🤔Before reading on: do you think querying large historical datasets is always fast, or can it slow down without special techniques? Commit to your answer.

Concept: Show how to write efficient queries and use indexing or data partitioning to speed up retrieval.

Large historical databases can be slow if queries scan too much data. Techniques like indexing by time and tag, limiting query scope, and using pre-aggregated data improve speed. Some SCADA systems support query caching or parallel processing.

Result

Learners can write queries that run quickly even on big datasets.

Knowing optimization techniques is key to maintaining system responsiveness in real-world use.

7

Expert

Complex Queries and Correlation Analysis

🤔Before reading on: can you predict if SCADA queries can compare multiple data streams to find relationships, or are they limited to single streams? Commit to your answer.

Concept: Teach how to write queries that join or correlate multiple historical data streams to find patterns or cause-effect relationships.

Advanced queries can combine data from different sensors or systems to analyze how variables interact. For example, correlating temperature and pressure changes over time can reveal process issues. This requires understanding query languages that support joins, subqueries, or scripting.

Result

Learners can perform sophisticated analysis by querying multiple data sources together.

Mastering complex queries unlocks powerful diagnostics and predictive capabilities in SCADA.

Under the Hood

Historical data querying works by accessing time-series databases optimized for fast retrieval of timestamped records. When a query is made, the system uses indexes on time and tags to quickly locate relevant data blocks. Aggregations and filters are applied during query execution to reduce data volume before returning results. Internally, data compression and partitioning help manage storage size and speed.

Why designed this way?

SCADA systems generate massive amounts of data continuously, so storing and querying must be efficient to avoid delays. Time-series databases and indexing were chosen because they match the data's natural structure and access patterns. Alternatives like relational databases were less efficient for this use case. The design balances speed, storage cost, and query flexibility.

┌───────────────────────────────┐
│       Query Request           │
└─────────────┬─────────────────┘
              │
              ▼
┌───────────────────────────────┐
│   Query Engine / Parser        │
│  - Parses time range           │
│  - Applies filters             │
│  - Plans aggregation steps    │
└─────────────┬─────────────────┘
              │
              ▼
┌───────────────────────────────┐
│ Time-Series Database Storage   │
│  - Indexed by time and tag     │
│  - Data partitions             │
│  - Compression                │
└─────────────┬─────────────────┘
              │
              ▼
┌───────────────────────────────┐
│ Query Results                 │
│  - Raw data or summaries      │
│  - Returned to user interface │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think querying historical data always returns every data point recorded? Commit to yes or no.

Common Belief: Querying historical data always returns all recorded data points for the requested time range.

Reality: Queries often return filtered or aggregated data, not every single point, to improve performance and relevance.

Why it matters: Expecting all data can lead to slow queries and overwhelm users with too much information.

Quick: Do you think historical data in SCADA is stored exactly as it was collected, without any processing? Commit to yes or no.

Common Belief: Historical data is stored exactly as collected, with no changes or processing.

Reality: Data is often compressed, cleaned, or summarized before storage to save space and improve query speed.

Why it matters: Assuming raw data is always available can cause confusion when some details are missing or altered.

Quick: Do you think querying historical data is always fast regardless of dataset size? Commit to yes or no.

Common Belief: Querying historical data is always fast, no matter how much data there is.

Reality: Large datasets can slow queries unless optimized with indexes, partitions, or caching.

Why it matters: Ignoring performance can cause delays and disrupt real-time decision-making.

Quick: Do you think you can only query one sensor's data at a time in SCADA historical queries? Commit to yes or no.

Common Belief: Historical queries can only retrieve data from one sensor or tag at a time.

Reality: Advanced queries can combine multiple sensors' data to analyze relationships and correlations.

Why it matters: Limiting queries to single sensors restricts analysis and misses important insights.

Expert Zone

1

Some SCADA systems use hybrid storage combining relational and time-series databases to balance flexibility and performance.

2

Query languages vary widely; mastering the specific SCADA query syntax is crucial for effective data retrieval.

3

Data retention policies affect what historical data is available; older data may be archived or deleted, impacting queries.

When NOT to use

Querying historical data is not suitable for real-time control decisions where immediate sensor readings are needed; use real-time data streams instead. For very large-scale analytics, specialized big data platforms or cloud analytics services may be better.

Production Patterns

In production, queries are often automated in dashboards and alerts to monitor key metrics continuously. Pre-aggregated data tables and scheduled queries improve performance. Correlation queries help detect anomalies early, and data quality filters prevent false alarms.

Connections

Time-Series Databases

Builds-on

Understanding querying historical data is easier when you know how time-series databases organize and index data for fast retrieval.

Data Visualization

Builds-on

Querying historical data provides the raw numbers that visualization tools turn into charts and graphs, making trends visible.

Library Archiving Systems

Analogy-based connection

Both systems organize large amounts of information for easy retrieval by date and topic, showing how information management principles apply across fields.

Common Pitfalls

#1 Querying without specifying a time range.

Wrong approach:

SELECT * FROM historical_data WHERE sensor_id = 'temp_sensor';

Correct approach:

SELECT * FROM historical_data WHERE sensor_id = 'temp_sensor' AND timestamp BETWEEN '2024-06-01' AND '2024-06-02';

Root cause:

Beginners often forget to limit queries by time, causing huge data retrieval and slow performance.

#2 Ignoring data quality flags in queries.

Wrong approach:

SELECT value FROM historical_data WHERE sensor_id = 'pressure_sensor' AND timestamp > '2024-06-01';

Correct approach:

SELECT value FROM historical_data WHERE sensor_id = 'pressure_sensor' AND timestamp > '2024-06-01' AND quality = 'good';

Root cause:

Not filtering by data quality leads to using faulty or invalid data in analysis.

#3 Requesting raw data for long periods without aggregation.

Wrong approach:

SELECT value FROM historical_data WHERE sensor_id = 'flow_sensor' AND timestamp BETWEEN '2024-01-01' AND '2024-06-01';

Correct approach:

SELECT AVG(value) FROM historical_data WHERE sensor_id = 'flow_sensor' AND timestamp BETWEEN '2024-01-01' AND '2024-06-01' GROUP BY DAY(timestamp);

Root cause:

Beginners may not realize that raw data over long periods is too large and slow to process.

Key Takeaways

Querying historical data lets you explore past events recorded by SCADA systems to understand trends and issues.

Effective queries use time ranges and filters to find relevant data quickly without overwhelming the system.

Aggregations and data quality handling are essential to get meaningful and accurate insights from historical data.

Optimizing queries with indexes and partitions keeps performance high even with large datasets.

Advanced queries can combine multiple data streams to reveal complex relationships and improve decision-making.

Practice

1. What is the main purpose of querying historical data in SCADA systems?

easy

A. To control real-time device operations

B. To review past system behavior and analyze trends

C. To update firmware on sensors

D. To configure network settings

Solution

1. Step 1: Understand the role of historical data

   Historical data stores past readings and events from the system.

2. Step 2: Identify the purpose of querying it

   Querying helps analyze past behavior and detect trends or issues.

3. Final Answer:

   To review past system behavior and analyze trends -> Option B

4. Quick Check:

   Historical data = review past behavior [OK]

Hint: Historical data is for past info, not real-time control [OK]

Common Mistakes:

• Confusing historical data with real-time control
• Thinking it updates devices
• Assuming it changes network settings

2. Which SQL query correctly selects temperature readings from sensor 'S1' recorded after '2024-01-01'?

easy

A. SELECT sensor_id, timestamp FROM readings WHERE type = 'temperature' OR sensor_id = 'S1' AND timestamp > '2024-01-01';

B. SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp < '2024-01-01' AND type = 'temperature';

C. SELECT * FROM readings WHERE sensor_id == 'S1' AND timestamp > '2024-01-01' AND type = 'temperature';

D. SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature';

Solution

1. Step 1: Check correct SQL syntax for conditions

   Use single equals (=) for comparison and AND to combine conditions.

2. Step 2: Verify logical conditions match requirements

   SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; correctly filters sensor_id = 'S1', timestamp > '2024-01-01', and type = 'temperature'.

3. Final Answer:

   SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; -> Option D

4. Quick Check:

   Correct syntax and filters = SELECT * FROM readings WHERE sensor_id = 'S1' AND timestamp > '2024-01-01' AND type = 'temperature'; [OK]

Hint: Use = for comparison and AND to combine filters [OK]

Common Mistakes:

• Using '==' instead of '=' in SQL
• Mixing AND and OR without parentheses
• Using wrong comparison operators
• Filtering with wrong timestamp direction

3. Given this query:
SELECT timestamp, value FROM readings WHERE sensor_id = 'S2' AND timestamp BETWEEN '2024-03-01' AND '2024-03-05' ORDER BY timestamp DESC LIMIT 3;
What will be the output?

medium

A. The 3 latest readings from sensor S2 between March 1 and 5, sorted descending

B. An error because LIMIT cannot be used with ORDER BY

C. All readings from sensor S2 between March 1 and 5, unsorted

D. The 3 earliest readings from sensor S2 between March 1 and 5, sorted ascending

Solution

1. Step 1: Understand the WHERE and BETWEEN clause

   Filters readings from sensor 'S2' between '2024-03-01' and '2024-03-05'.

2. Step 2: Analyze ORDER BY and LIMIT

   ORDER BY timestamp DESC sorts newest first; LIMIT 3 returns top 3 newest readings.

3. Final Answer:

   The 3 latest readings from sensor S2 between March 1 and 5, sorted descending -> Option A

4. Quick Check:

   ORDER BY DESC + LIMIT 3 = latest 3 readings [OK]

Hint: ORDER BY DESC + LIMIT gets newest records first [OK]

Common Mistakes:

• Confusing ascending vs descending order
• Thinking LIMIT returns earliest records
• Assuming LIMIT causes syntax error with ORDER BY

4. You wrote this query to get pressure data:
SELECT * FROM readings WHERE sensor_id = 'P1' AND timestamp > '2024-02-01' AND type = 'pressure'
But it returns no results, even though data exists. What is the likely problem?

medium

A. The query is missing a GROUP BY clause

B. The sensor_id should be numeric, not string

C. The timestamp format is incorrect and does not match stored data

D. The type column does not exist in the readings table

Solution

1. Step 1: Check timestamp format correctness

   Timestamp format must match stored data format exactly to filter correctly.

2. Step 2: Verify other query parts

   Sensor_id as string is valid; GROUP BY not needed; type column likely exists.

3. Final Answer:

   The timestamp format is incorrect and does not match stored data -> Option C

4. Quick Check:

   Timestamp format mismatch = no results [OK]

Hint: Match timestamp format exactly to stored data [OK]

Common Mistakes:

• Assuming sensor_id must be numeric
• Adding unnecessary GROUP BY
• Ignoring timestamp format differences

5. You want to find the average temperature for each of sensors 'T1' and 'T2' during January 2024, but only for readings above 20°C. Which SQL query achieves this?

hard

A. SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id;

B. SELECT AVG(value) FROM readings WHERE sensor_id IN ('T1', 'T2') AND timestamp >= '2024-01-01' AND timestamp <= '2024-01-31' AND value > 20 AND type = 'temperature';

C. SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' AND sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id;

D. SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' OR sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature';

Solution

1. Step 1: Filter sensors correctly

   Use (sensor_id = 'T1' OR sensor_id = 'T2') or sensor_id IN ('T1', 'T2') to include both sensors.

2. Step 2: Apply date and value filters with grouping

   Filter timestamp between January 1 and 31, value > 20, and type = 'temperature'. Group by sensor_id to get averages per sensor.

3. Step 3: Check query correctness

   SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; uses correct syntax with parentheses and GROUP BY; SELECT AVG(value) FROM readings WHERE sensor_id IN ('T1', 'T2') AND timestamp >= '2024-01-01' AND timestamp <= '2024-01-31' AND value > 20 AND type = 'temperature'; misses GROUP BY; SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' AND sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; has impossible condition; SELECT sensor_id, AVG(value) FROM readings WHERE sensor_id = 'T1' OR sensor_id = 'T2' AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature'; lacks parentheses causing wrong logic.

4. Final Answer:

   SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; -> Option A

5. Quick Check:

   Correct filters + grouping = SELECT sensor_id, AVG(value) FROM readings WHERE (sensor_id = 'T1' OR sensor_id = 'T2') AND timestamp BETWEEN '2024-01-01' AND '2024-01-31' AND value > 20 AND type = 'temperature' GROUP BY sensor_id; [OK]

Hint: Use parentheses for OR and GROUP BY for averages per sensor [OK]

Common Mistakes:

• Missing GROUP BY when aggregating by sensor
• Using AND instead of OR between sensor_ids
• Incorrect timestamp filtering logic