Roadmap › Industry 4.0 & IIoT — Smart Manufacturing Software › Module: Manufacturing KPIs and Data
Lessons in this course (10)
7. Time-series data at scale
IntermediateMediumLesson 7 of 10
Machine data is time-series: (timestamp, tag, value, quality), arriving continuously — thousands of tags × 1 Hz = hundreds of millions of rows per month.
| Technique | Why |
|---|---|
| Time-series databases (ADX/KQL, InfluxDB, TimescaleDB, PI) | Fast ingest, compression, time-bucket queries |
| Report by exception / deadband | Store only changes beyond a threshold — reduces volume 10–100× |
| Downsampling & retention tiers | Raw for weeks, 1-min aggregates for years |
| Partitioning by time (SQL Server partitioning, columnstore) | Efficient ranges and archiving |
| UTC timestamps + source time vs ingest time | Correct ordering across time zones and late data |
sql
-- SQL Server 2022+: one-minute average per tag with DATE_BUCKET
SELECT TagId, DATE_BUCKET(MINUTE, 1, Ts) AS Minute, AVG(Value) AS AvgValue, MAX(Value) AS MaxValue
FROM iot.Telemetry
WHERE Ts >= DATEADD(HOUR, -8, SYSUTCDATETIME())
GROUP BY TagId, DATE_BUCKET(MINUTE, 1, Ts);