IoT anomaly detection with streaming SQL means every sensor reading is evaluated the moment it arrives against device thresholds, window statistics, and rate-of-change baselines, with duplicates from at-least-once delivery removed first, and the whole pipeline scales to 100,000 sensors because the computation is incremental and the state lives in object storage. This guide builds the horizontal IoT patterns every vertical reuses on RisingWave, a PostgreSQL-compatible streaming database: a verified anomaly detection tutorial you can run locally, deduplication strategies, and the current-state, liveness, and fleet rollup views that make a large sensor estate manageable.
Why Warehouse-Then-Query Fails for Sensor Data
A compressor on the factory floor runs hot. Its temperature crosses 96 degrees Celsius at 08:04:00. In the typical architecture the reading lands in a data lake, a scheduled job scores it minutes or hours later, and an alert arrives after the bearing is already damaged. The same lag hides a duplicate reading inflating an average, a sensor that went silent an hour ago, and a facility whose quality rate quietly fell to 80 percent.
Three SQL primitives replace that architecture. Sources ingest from Kafka, MQTT, or any connector; for development a table with direct inserts has the same shape. Tumbling and hopping windows slice the stream into intervals. Materialized views are the detection layer: continuously maintained query results that update only the rows affected by each new event. RisingWave is open source under Apache 2.0, written in Rust, and keeps view state in S3, so query latency is independent of how much raw data has arrived and state size is bounded by the number of sensors rather than the number of readings.
Most IoT anomalies do not need a machine learning model. Threshold checks, z-scores, interquartile ranges, and rate-of-change limits catch the majority of equipment anomalies and are trivially expressible in SQL.
Tutorial: Thresholds, Z-Scores, and a Device Health View
Start with a device table holding each sensor's normal operating range and a readings table. In production the readings table becomes a Kafka source and nothing downstream changes.
CREATE TABLE iot_devices (
device_id VARCHAR PRIMARY KEY,
device_name VARCHAR,
location VARCHAR,
sensor_type VARCHAR,
unit VARCHAR,
normal_min DOUBLE PRECISION,
normal_max DOUBLE PRECISION
);
INSERT INTO iot_devices VALUES
('dev-001', 'Compressor-A1', 'Plant Floor 1', 'temperature', 'celsius', 60.0, 90.0),
('dev-002', 'Pump-B2', 'Plant Floor 2', 'pressure', 'psi', 80.0, 120.0),
('dev-003', 'Motor-C3', 'Warehouse', 'vibration', 'mm/s', 0.0, 5.0);
CREATE TABLE iot_readings (
reading_id VARCHAR PRIMARY KEY,
device_id VARCHAR,
sensor_value DOUBLE PRECISION,
event_time TIMESTAMPTZ
);
INSERT INTO iot_readings VALUES
('r001', 'dev-001', 75.2, '2026-04-01 08:00:00+00'),
('r002', 'dev-001', 76.5, '2026-04-01 08:01:00+00'),
('r003', 'dev-001', 78.1, '2026-04-01 08:02:00+00'),
('r004', 'dev-001', 77.8, '2026-04-01 08:03:00+00'),
('r005', 'dev-001', 96.4, '2026-04-01 08:04:00+00'), -- anomaly
('r006', 'dev-002', 95.0, '2026-04-01 08:00:00+00'),
('r007', 'dev-002', 97.3, '2026-04-01 08:01:00+00'),
('r008', 'dev-002', 130.1, '2026-04-01 08:02:00+00'), -- anomaly
('r009', 'dev-002', 101.5, '2026-04-01 08:03:00+00'),
('r010', 'dev-002', 99.8, '2026-04-01 08:04:00+00'),
('r011', 'dev-003', 2.1, '2026-04-01 08:00:00+00'),
('r012', 'dev-003', 2.4, '2026-04-01 08:01:00+00'),
('r013', 'dev-003', 8.7, '2026-04-01 08:02:00+00'), -- anomaly
('r014', 'dev-003', 3.0, '2026-04-01 08:03:00+00'),
('r015', 'dev-003', 2.9, '2026-04-01 08:04:00+00');
A five-minute tumbling window gives the baseline per device: count, average, minimum, maximum. The max column already hints at the anomalies, and when readings from a new window arrive the view grows without any rewrite.
CREATE MATERIALIZED VIEW iot_tumble_avg_5min AS
SELECT device_id, window_start, window_end,
COUNT(*) AS reading_count,
ROUND(AVG(sensor_value)::NUMERIC, 2) AS avg_value,
ROUND(MIN(sensor_value)::NUMERIC, 2) AS min_value,
ROUND(MAX(sensor_value)::NUMERIC, 2) AS max_value
FROM TUMBLE(iot_readings, event_time, INTERVAL '5 MINUTES')
GROUP BY device_id, window_start, window_end;
Threshold alerts join each reading to its device's range. Changing a range is a one-row update in iot_devices and every future reading is evaluated against the new value:
CREATE MATERIALIZED VIEW iot_threshold_alerts AS
SELECT r.reading_id, r.device_id, d.device_name, d.location, d.sensor_type, d.unit,
r.sensor_value, d.normal_min, d.normal_max, r.event_time,
CASE WHEN r.sensor_value > d.normal_max THEN 'HIGH'
WHEN r.sensor_value < d.normal_min THEN 'LOW' END AS alert_level
FROM iot_readings r
JOIN iot_devices d ON r.device_id = d.device_id
WHERE r.sensor_value > d.normal_max OR r.sensor_value < d.normal_min;
Querying it returns r005, r008, and r013, and a reading inserted after the view exists, such as a 105.3 degree reading at 08:10, appears immediately. Thresholds work for known limits and fail for sensors whose normal range shifts with load. A z-score within the window catches statistical outliers regardless of absolute value:
CREATE MATERIALIZED VIEW iot_zscore_anomalies AS
WITH windowed_stats AS (
SELECT device_id, window_start, window_end,
AVG(sensor_value) AS window_avg, STDDEV_POP(sensor_value) AS window_stddev
FROM TUMBLE(iot_readings, event_time, INTERVAL '5 MINUTES')
GROUP BY device_id, window_start, window_end
)
SELECT r.reading_id, r.device_id, r.sensor_value, r.event_time, ws.window_start,
ROUND(ws.window_avg::NUMERIC, 2) AS window_avg,
ROUND(ws.window_stddev::NUMERIC, 2) AS window_stddev,
ROUND((ABS(r.sensor_value - ws.window_avg) / NULLIF(ws.window_stddev, 0))::NUMERIC, 2) AS z_score
FROM iot_readings r
JOIN windowed_stats ws
ON r.device_id = ws.device_id AND r.event_time >= ws.window_start AND r.event_time < ws.window_end
WHERE ws.window_stddev > 0
AND ABS(r.sensor_value - ws.window_avg) / NULLIF(ws.window_stddev, 0) > 1.5;
All three anomalies score near 1.98 standard deviations from their window average, caught without knowing in advance that 96.4 degrees or 130.1 psi is bad for that device. STDDEV_POP is the right choice when every reading in the interval is available, NULLIF guards against a window of identical readings after a restart, and a threshold of 1.5 suits correlated sensor data while 2.0 or higher suits noisy environments.
| Approach | Catches | Misses | Best for |
|---|---|---|---|
| Threshold | Values outside fixed bounds | Gradual drift inside bounds | Safety limits, compliance |
| Windowed z-score | Statistical outliers in a window | Slow trends across windows | Spikes, sensor faults |
Running both gives defense in depth. A device health view consolidates reading and alert counts per device for the dashboard:
CREATE MATERIALIZED VIEW iot_device_status AS
SELECT r.device_id, d.device_name, d.location, d.sensor_type, d.unit,
COUNT(r.reading_id) AS total_readings,
ROUND(AVG(r.sensor_value)::NUMERIC, 2) AS overall_avg,
ROUND(MAX(r.sensor_value)::NUMERIC, 2) AS overall_max,
COUNT(*) FILTER (WHERE r.sensor_value > d.normal_max OR r.sensor_value < d.normal_min) AS alert_count
FROM iot_readings r
JOIN iot_devices d ON r.device_id = d.device_id
GROUP BY r.device_id, d.device_name, d.location, d.sensor_type, d.unit;
Baselines, Short-Term Averages, and Rate of Change
Comparing a point to its own window is one pattern. Comparing a short-term average to a longer baseline is more robust to single noisy samples and is the standard for production anomaly detection. A ten-minute tumbling baseline, a five-minute hopping short-term average sliding every minute, and a LAG-based rate of change cover the three common failure shapes: level shift, slow drift, and sudden jump.
CREATE SOURCE machine_sensors (
machine_id VARCHAR,
sensor_id VARCHAR,
metric VARCHAR,
value DOUBLE PRECISION,
quality INT, -- 1 good, 0 bad
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'machine-telemetry',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
CREATE MATERIALIZED VIEW sensor_baseline_10min AS
SELECT machine_id, sensor_id, metric, window_start, window_end,
AVG(value) AS mean_value, STDDEV(value) AS stddev_value, COUNT(*) AS sample_count
FROM TUMBLE(machine_sensors, event_time, INTERVAL '10 MINUTES')
WHERE quality = 1
GROUP BY machine_id, sensor_id, metric, window_start, window_end;
CREATE MATERIALIZED VIEW sensor_short_avg AS
SELECT machine_id, sensor_id, metric, window_start, window_end,
AVG(value) AS short_avg, MAX(value) AS max_val, MIN(value) AS min_val
FROM HOP(machine_sensors, event_time, INTERVAL '1 MINUTE', INTERVAL '5 MINUTES')
WHERE quality = 1
GROUP BY machine_id, sensor_id, metric, window_start, window_end;
CREATE MATERIALIZED VIEW anomaly_alerts AS
SELECT s.machine_id, s.sensor_id, s.metric,
s.short_avg AS observed_value, b.mean_value AS baseline_mean, b.stddev_value AS baseline_stddev,
ABS(s.short_avg - b.mean_value) / NULLIF(b.stddev_value, 0) AS z_score,
s.window_end AS detected_at
FROM sensor_short_avg s
JOIN sensor_baseline_10min b
ON s.machine_id = b.machine_id AND s.sensor_id = b.sensor_id AND s.metric = b.metric
AND s.window_start >= b.window_start AND s.window_start < b.window_end
WHERE ABS(s.short_avg - b.mean_value) / NULLIF(b.stddev_value, 0) > 3.0;
CREATE MATERIALIZED VIEW sensor_rate_of_change AS
SELECT machine_id, sensor_id, metric, value, event_time,
value - LAG(value, 1) OVER (PARTITION BY machine_id, sensor_id, metric ORDER BY event_time) AS delta,
EXTRACT(EPOCH FROM (event_time - LAG(event_time, 1) OVER (PARTITION BY machine_id, sensor_id, metric ORDER BY event_time))) AS seconds_since_prev
FROM machine_sensors
WHERE quality = 1;
A delta per second above a metric-specific limit, joined from a reference table, is the rate-of-change alert. The quality flag on the source is worth keeping: filtering bad-quality samples before any statistic keeps a flaky sensor from poisoning its own baseline.
Deduplication Before Anything Else
IoT pipelines run on at-least-once delivery. MQTT brokers, edge Kafka producers, and cellular modems all retry on dropped connections, and a reading that arrives three times inflates the average, triggers a false spike alert, and double-counts in any sum. Deduplication belongs upstream of every view above, and the cleanest place for it is a materialized view that the detection views read instead of the raw source.
Three strategies cover the usual cases. Deduplicate by a message ID generated at the sensor, by the device's own monotonic sequence number when there is no message ID, or semantically on identical device, type, timestamp, and value when the device offers neither.
CREATE SOURCE raw_sensor_events (
message_id VARCHAR, -- UUID generated at the sensor
device_id VARCHAR,
sequence_num BIGINT, -- monotonic counter per device
sensor_type VARCHAR,
value DOUBLE PRECISION,
unit VARCHAR,
quality SMALLINT,
event_time TIMESTAMPTZ, -- sensor-local timestamp
ingested_at TIMESTAMPTZ -- broker ingestion timestamp
) WITH (
connector = 'kafka',
topic = 'raw-sensor-events',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
CREATE MATERIALIZED VIEW deduplicated_by_id AS
SELECT DISTINCT ON (message_id) message_id, device_id, sequence_num, sensor_type, value, unit, quality, event_time, ingested_at
FROM raw_sensor_events
ORDER BY message_id, ingested_at ASC;
CREATE MATERIALIZED VIEW deduplicated_by_seq AS
SELECT DISTINCT ON (device_id, sequence_num) device_id, sequence_num, sensor_type, value, unit, quality, event_time, ingested_at
FROM raw_sensor_events
ORDER BY device_id, sequence_num, ingested_at ASC;
CREATE MATERIALIZED VIEW semantically_deduplicated AS
SELECT device_id, sensor_type, event_time, value, unit, quality, MIN(ingested_at) AS first_seen
FROM raw_sensor_events
GROUP BY device_id, sensor_type, event_time, value, unit, quality;
Keeping the earliest ingestion of each message means the detection views see the reading as soon as it first arrives and retries are ignored. The duplicate rate is itself a health signal: a device whose duplicate rate climbs above 20 percent has a connectivity or firmware problem.
CREATE MATERIALIZED VIEW device_dedup_stats AS
SELECT device_id, window_start, window_end,
COUNT(*) AS total_events,
COUNT(DISTINCT message_id) AS unique_events,
COUNT(*) - COUNT(DISTINCT message_id) AS duplicates,
ROUND((COUNT(*) - COUNT(DISTINCT message_id)) * 100.0 / NULLIF(COUNT(*), 0), 2) AS dup_rate_pct
FROM TUMBLE(raw_sensor_events, ingested_at, INTERVAL '10 MINUTES')
GROUP BY device_id, window_start, window_end;
CREATE MATERIALIZED VIEW high_dup_rate_alerts AS
SELECT device_id, dup_rate_pct, duplicates, total_events, window_end AS detected_at, 'HIGH_DUPLICATE_RATE' AS alert_type
FROM device_dedup_stats
WHERE dup_rate_pct > 20.0;
A sink of deduplicated_by_id to a clean-sensor-events topic gives every other consumer in the organization the same clean stream without each one reimplementing deduplication.
Scaling to 100,000 Sensors: Current State, Liveness, and Fleet Rollups
A refinery, a utility grid, or a smart city network deploys tens of thousands of sensors publishing every second or two, millions of events a minute. A single-node time series database saturates well below that, and a dashboard cannot plot 100,000 series. Three design decisions make the estate manageable.
Partition ingestion. A high-partition-count Kafka topic is consumed in parallel across RisingWave compute nodes automatically. Separate topics per facility, unioned into one view, work equally well and let facilities scale independently.
CREATE SOURCE all_sensors (
sensor_id VARCHAR,
facility_id VARCHAR,
zone VARCHAR,
metric VARCHAR,
value DOUBLE PRECISION,
quality SMALLINT,
event_time TIMESTAMPTZ
) WITH (
connector = 'kafka',
topic = 'all-industrial-sensors',
properties.bootstrap.server = 'kafka:9092',
scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;
Keep the current state incremental. The latest value per sensor and metric is a DISTINCT ON, and liveness is the gap since the last reading, both bounded by the number of sensors rather than the number of readings:
CREATE MATERIALIZED VIEW sensor_current_state AS
SELECT DISTINCT ON (sensor_id, metric) sensor_id, facility_id, zone, metric, value, quality, event_time AS last_seen
FROM all_sensors
WHERE quality > 0
ORDER BY sensor_id, metric, event_time DESC;
CREATE MATERIALIZED VIEW sensor_liveness AS
SELECT sensor_id, facility_id, zone,
MAX(event_time) AS last_seen,
NOW() - MAX(event_time) AS silence_duration,
COUNT(*) FILTER (WHERE quality > 0) * 1.0 / NULLIF(COUNT(*), 0) AS quality_rate
FROM all_sensors
WHERE event_time > NOW() - INTERVAL '24 HOURS'
GROUP BY sensor_id, facility_id, zone;
CREATE MATERIALIZED VIEW silent_sensor_alerts AS
SELECT sensor_id, facility_id, zone, last_seen, silence_duration, 'SENSOR_SILENT' AS alert_type
FROM sensor_liveness
WHERE silence_duration > INTERVAL '5 MINUTES';
Roll up for the dashboard. One-minute aggregates per zone and metric, with percentiles and bad-quality counts, let a fleet dashboard render thousands of sensors as a few hundred rows, and a facility health view summarizes the share of sensors reporting good data.
CREATE MATERIALIZED VIEW zone_metrics_1min AS
SELECT facility_id, zone, metric, window_start, window_end,
COUNT(DISTINCT sensor_id) AS active_sensors,
AVG(value) AS fleet_avg,
MIN(value) AS fleet_min,
MAX(value) AS fleet_max,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value,
PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY value) AS p95_value,
COUNT(*) FILTER (WHERE quality = 0) AS bad_quality_count
FROM TUMBLE(all_sensors, event_time, INTERVAL '1 MINUTE')
GROUP BY facility_id, zone, metric, window_start, window_end;
CREATE MATERIALIZED VIEW facility_health_1min AS
SELECT facility_id, window_start, window_end,
COUNT(DISTINCT sensor_id) AS total_sensors,
COUNT(DISTINCT sensor_id) FILTER (WHERE quality > 0) AS healthy_sensors,
ROUND(100.0 * COUNT(DISTINCT sensor_id) FILTER (WHERE quality > 0) / NULLIF(COUNT(DISTINCT sensor_id), 0), 2) AS health_pct
FROM TUMBLE(all_sensors, event_time, INTERVAL '1 MINUTE')
GROUP BY facility_id, window_start, window_end;
Per-sensor thresholds at this scale live in a table keyed by sensor and metric, and the alert view is a join:
CREATE TABLE sensor_thresholds (sensor_id VARCHAR, metric VARCHAR, low_threshold DOUBLE PRECISION, high_threshold DOUBLE PRECISION, PRIMARY KEY (sensor_id, metric));
CREATE MATERIALIZED VIEW fleet_alerts AS
SELECT s.sensor_id, s.facility_id, s.zone, s.metric, s.value, s.event_time,
CASE WHEN s.value > t.high_threshold THEN 'ABOVE_HIGH' ELSE 'BELOW_LOW' END AS alert_type, 'HIGH' AS severity
FROM all_sensors s
JOIN sensor_thresholds t ON s.sensor_id = t.sensor_id AND s.metric = t.metric
WHERE s.quality > 0 AND (s.value > t.high_threshold OR s.value < t.low_threshold);
Routing matters as much as detection at this volume. Threshold alerts and silent-sensor alerts union into one sink partitioned by facility so each site's operations team receives only its own.
CREATE SINK fleet_alerts_sink AS
SELECT sensor_id, facility_id, zone, metric, alert_type, severity, event_time AS ts FROM fleet_alerts
UNION ALL
SELECT sensor_id, facility_id, zone, 'liveness', alert_type, 'WARNING', last_seen FROM silent_sensor_alerts
WITH (connector = 'kafka', topic = 'fleet-alerts', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
CREATE SINK anomaly_sink FROM anomaly_alerts
WITH (connector = 'kafka', topic = 'anomaly-events', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
CREATE SINK clean_sensor_events_sink FROM deduplicated_by_id
WITH (connector = 'kafka', topic = 'clean-sensor-events', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;
Putting the Layers Together
In production the pipeline is layered in this order. Raw events from Kafka or MQTT enter a deduplication view. Detection views, thresholds from a device table, windowed z-scores, baseline versus short-term averages, and rate of change, read the deduplicated view. Current-state and liveness views serve point lookups and silent-sensor alerts. Zone and facility rollups serve the fleet dashboard. Alerts union into per-facility sinks. Every layer is a CREATE MATERIALIZED VIEW, and swapping the development table for a production source is the only change needed to go live. Set event_time to the sensor's embedded timestamp rather than the broker's so windows align with when the reading was taken, and define a watermark on it so late readings from a reconnecting gateway land in the right window.
Batch IoT Analytics vs Streaming SQL
| Concern | Data lake plus scheduled job | Streaming SQL (RisingWave) |
|---|---|---|
| Detection latency | Minutes to hours | Sub-second, per reading |
| Thresholds | In job code | Device table joined per reading, update one row |
| Statistical outliers | Separate ML job | Windowed z-score or baseline comparison in SQL |
| Duplicates | Application-side ID cache | DISTINCT ON view, with duplicate rate as a health metric |
| Current state for 100,000 sensors | Full scan | DISTINCT ON, state bounded by sensor count |
| Silent sensors | Noticed when a report is empty | Liveness view, alert after 5 minutes |
| Fleet dashboard | Query 100,000 series | Zone and facility rollups per minute |
| Scaling | Vertical | Horizontal compute, state on S3 |
Frequently Asked Questions
Should I deduplicate before or after windowing? Before. Build every detection and rollup view on the deduplicated view so a retried message cannot inflate an average or trigger a spike alert. The duplicate rate views read the raw source on purpose, because they measure the duplicates.
How do I ingest directly from MQTT? RisingWave has an MQTT source connector, so devices or an edge broker can publish straight into a source with the same schema as the Kafka examples. Kafka in between adds replay and fan-out, which most production fleets want.
What z-score threshold should I use? Start at 1.5 for correlated, well-behaved sensors in the within-window pattern and 3.0 for the short-term-versus-baseline pattern, then tune per metric from the false positive rate. Put the threshold in a table joined per metric rather than hard-coding it.
How do I handle sensors with very different reporting rates?
Windows are defined by event time, so a one-second sensor and a one-minute sensor aggregate correctly in the same view. Use the sample_count column to require a minimum number of readings before a window produces an alert.
Does state grow without bound?
Views keyed by sensor, such as current state and liveness, are bounded by the sensor count. Windowed views retain state only for open windows plus the allowed lateness. Add a temporal filter such as the 24-hour bound on sensor_liveness to any view that would otherwise aggregate all history.
