IoT Data Quality Checks Before Database Ingestion

Scope and where each check belongs

This page is for engineers who own the path from field devices to the readings table: BMS integrations, metering platforms, IoT gateways. It assumes the storage design in how to store IoT sensor data (narrow readings table, point metadata, a quality column) and concentrates on validating data on the way in. Duplicate deliveries are a separate problem with its own page, handling duplicate telemetry events, and statistical anomaly detection on stored data is covered in SQL anomaly detection using rolling baselines.

A data quality check here is a rule about a single reading or a short run of readings that decides whether the value is trustworthy enough to store and use. They belong at different layers:

A quality model: good, suspect, bad

Before writing checks, decide what a failed check does. Deleting data hides problems; storing everything unmarked pollutes reports. A small quality code on each reading, plus a reject table, covers most needs:

• 0 good: passed every check; used everywhere.
• 1 suspect: stored, but excluded from roll-ups, energy totals and fault rules by default (spike, stuck value, estimated).
• 2 bad: stored only if the source itself marked it bad and you need an audit trail; never used in calculations.
• Rejected: not stored in the readings table at all, but kept in reading_rejected with a reason (unknown point, unconvertible unit, impossible value, future timestamp).

These three codes are our own scheme for this page. They are deliberately similar to how some industrial protocols report quality, but the correspondence is our analogy, not something a protocol defines. OPC UA, for example, attaches a StatusCode whose severity bits indicate a good, bad or uncertain condition, and its specification says results with a bad status shall never be used and uncertain results should be used with care. Reading OPC UA good, uncertain and bad as our 0, 1 and 2 is a reasonable starting point, not a standard mapping; decide per integration (some uncertain sub-codes may deserve rejection, others may be fine to use). When the source supplies a status, map it into your code instead of discarding it; a device-reported bad value should not become "good" just because it is in range. OPC UA also distinguishes the source timestamp (from the device) from the server timestamp, which matters for the timestamp checks below.

Constraints: the last line of defence

CHECK constraints are cheap and absolute: no code path can bypass them. Use them only for rules that depend on the row alone. Two details matter for sensor data. First, double precision accepts the special values NaN, Infinity and -Infinity, which some gateways emit for a failed reading; reject them explicitly. Second, a CHECK constraint is satisfied when its expression is null, so pair it with NOT NULL where a value is required.

CREATE TABLE point (
    point_id    integer PRIMARY KEY,
    point_name  text NOT NULL,
    unit        text NOT NULL,             -- the unit stored in reading.value
    min_valid   double precision NOT NULL, -- physically possible range
    max_valid   double precision NOT NULL,
    max_step    double precision NOT NULL, -- largest believable change between readings
    stuck_after integer NOT NULL,          -- identical readings before "stuck"
    CHECK (min_valid < max_valid)
);
INSERT INTO point VALUES
    (101, 'AHU-01 supply_air_temp', 'degC', -10, 60, 3, 5),
    (102, 'L3 zone_co2',            'ppm',  300, 5000, 400, 30);

CREATE TABLE reading (
    point_id  integer NOT NULL REFERENCES point (point_id),
    ts        timestamptz NOT NULL,
    value     double precision NOT NULL
              CHECK (value NOT IN ('NaN', 'Infinity', '-Infinity')),
    quality   smallint NOT NULL DEFAULT 0 CHECK (quality IN (0, 1, 2)),
    PRIMARY KEY (point_id, ts)
);

INSERT INTO reading VALUES (101, '2026-10-27 09:00+00', 'NaN', 0);
INSERT INTO reading VALUES (101, '2026-10-27 09:00+00', 13.2, 7);

ERROR:  new row for relation "reading" violates check constraint "reading_value_check"
DETAIL:  Failing row contains (101, 2026-10-27 09:00:00+00, NaN, 0).
SQLSTATE 23514

ERROR:  new row for relation "reading" violates check constraint "reading_quality_check"
DETAIL:  Failing row contains (101, 2026-10-27 09:00:00+00, 13.2, 7).
SQLSTATE 23514

What not to put in a CHECK constraint

• Per-point limits. A constraint such as "value within this point's range" needs the point table. The PostgreSQL documentation states that CHECK constraints referencing other table data are not supported; they may appear to work but can be violated later and break dump and restore. Use the validation query below.
• "Not in the future". CHECK (ts <= now() + interval '5 minutes') was accepted without error in the same run, which makes it tempting. But the PostgreSQL documentation states that it "assumes that CHECK constraints' conditions are immutable, that is, they will always give the same result for the same input row", and that this assumption is what justifies examining them only when rows are inserted or updated. A condition based on now() does not meet that assumption, so a time-dependent rule is outside what the constraint system is designed for. (The 5-minute tolerance is an illustrative value.) Check the timestamp against the arrival time in the ingest step instead.
• NaN and ranges. PostgreSQL treats NaN as equal to itself and greater than every other value (so it can be sorted and indexed), unlike IEEE 754. A BETWEEN range test therefore classes NaN as above the maximum. The explicit check above is clearer.

Validate a staging batch in SQL

Land each batch in a loosely typed staging table (nullable columns, the sender's unit, the arrival time recorded by the ingest service), then classify every row in one query. The view below applies the checks in a fixed order: hard failures first, then range, then the soft checks that compare neighbouring readings among the rows that survived.

The per-point values in the point table above (valid range -10 to 60 degC and 300 to 5000 ppm, a maximum step of 3 degC and 400 ppm, stuck after 5 and 30 identical readings) and the time tolerances in the view (5 minutes into the future, 7 days into the past) are illustrative assumptions picked so the test batch shows each outcome. Choose real values per point type from the sensor's specified range and resolution, its normal noise and reporting interval, how much clock skew your devices show, how long gateways can buffer during an outage, and how many suspect flags the people reviewing them can handle; then check the choice against a few weeks of real history.

CREATE TABLE unit_conversion (
    from_unit text NOT NULL, to_unit text NOT NULL,
    factor double precision NOT NULL, offset_value double precision NOT NULL,
    PRIMARY KEY (from_unit, to_unit)
);
INSERT INTO unit_conversion VALUES ('degF', 'degC', 5.0/9.0, -32.0 * 5.0/9.0);

CREATE TABLE staging_reading (
    point_id    integer,
    ts          timestamptz,
    value       double precision,
    unit        text,
    received_at timestamptz NOT NULL      -- set by the ingest service, not the device
);
CREATE TABLE reading_rejected (
    point_id integer, ts timestamptz, value double precision, unit text,
    received_at timestamptz NOT NULL, reason text NOT NULL
);

INSERT INTO staging_reading VALUES
    (101, '2026-10-27 09:00+00', 13.2,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:01+00', 13.4,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:02+00', 13.4,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:03+00', 13.4,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:04+00', 13.4,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:05+00', 13.4,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:06+00', 19.9,  'degC', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:07+00', 56.3,  'degF', '2026-10-27 09:10+00'),
    (101, '2026-10-27 09:08+00', 85.0,  'degC', '2026-10-27 09:10+00'),
    (102, '2026-10-27 09:00+00', 640,   'ppm',  '2026-10-27 09:10+00'),
    (102, '2026-10-28 09:00+00', 650,   'ppm',  '2026-10-27 09:10+00'),
    (999, '2026-10-27 09:00+00', 1.0,   'kW',   '2026-10-27 09:10+00'),
    (102, '2026-10-27 09:01+00', NULL,  'ppm',  '2026-10-27 09:10+00');

CREATE VIEW staging_checked AS
WITH normalised AS (
    SELECT s.point_id, s.ts, s.received_at, s.unit AS sent_unit, s.value AS sent_value,
           p.min_valid, p.max_valid, p.max_step, p.stuck_after,
           CASE WHEN s.unit = p.unit THEN s.value
                ELSE s.value * c.factor + c.offset_value END AS value,
           CASE
             WHEN p.point_id IS NULL                          THEN 'unknown point'
             WHEN s.value IS NULL OR s.ts IS NULL             THEN 'missing value or timestamp'
             WHEN s.unit <> p.unit AND c.from_unit IS NULL    THEN 'unit mismatch'
             WHEN s.ts > s.received_at + interval '5 minutes' THEN 'timestamp in the future'
             WHEN s.ts < s.received_at - interval '7 days'    THEN 'timestamp too old'
           END AS hard_fail
    FROM staging_reading s
    LEFT JOIN point p ON p.point_id = s.point_id
    LEFT JOIN unit_conversion c ON c.from_unit = s.unit AND c.to_unit = p.unit
), ranged AS (
    SELECT n.*,
           coalesce(hard_fail,
                    CASE WHEN value NOT BETWEEN min_valid AND max_valid
                         THEN 'out of range' END) AS reject_reason
    FROM normalised n
), runs AS (                         -- soft checks only look at rows that survived
    SELECT r.*,
           lag(value)  OVER w AS prev_value,
           lead(value) OVER w AS next_value,
           CASE WHEN value IS DISTINCT FROM lag(value) OVER w THEN 1 ELSE 0 END AS new_run
    FROM ranged r
    WHERE reject_reason IS NULL
    WINDOW w AS (PARTITION BY point_id ORDER BY ts)
), runs2 AS (
    SELECT r.*, sum(new_run) OVER (PARTITION BY point_id ORDER BY ts) AS run_no
    FROM runs r
), soft AS (
    SELECT r.*, row_number() OVER (PARTITION BY point_id, run_no ORDER BY ts) AS run_pos
    FROM runs2 r
)
SELECT point_id, ts, sent_value, sent_unit, value, received_at, reject_reason,
       NULL::text AS suspect_reason
FROM ranged WHERE reject_reason IS NOT NULL
UNION ALL
SELECT point_id, ts, sent_value, sent_unit, value, received_at, NULL,
       CASE
         WHEN abs(value - prev_value) > max_step
          AND abs(value - next_value) > max_step THEN 'spike'
         WHEN run_pos >= stuck_after             THEN 'stuck'
       END
FROM soft;

SELECT point_id, ts, sent_value, sent_unit, round(value::numeric, 2) AS value,
       coalesce(reject_reason, suspect_reason, 'ok') AS verdict
FROM staging_checked ORDER BY point_id, ts;

 point_id |           ts           | sent_value | sent_unit | value  |          verdict
----------+------------------------+------------+-----------+--------+----------------------------
      101 | 2026-10-27 09:00:00+00 |       13.2 | degC      |  13.20 | ok
      101 | 2026-10-27 09:01:00+00 |       13.4 | degC      |  13.40 | ok
      101 | 2026-10-27 09:02:00+00 |       13.4 | degC      |  13.40 | ok
      101 | 2026-10-27 09:03:00+00 |       13.4 | degC      |  13.40 | ok
      101 | 2026-10-27 09:04:00+00 |       13.4 | degC      |  13.40 | ok
      101 | 2026-10-27 09:05:00+00 |       13.4 | degC      |  13.40 | stuck
      101 | 2026-10-27 09:06:00+00 |       19.9 | degC      |  19.90 | spike
      101 | 2026-10-27 09:07:00+00 |       56.3 | degF      |  13.50 | ok
      101 | 2026-10-27 09:08:00+00 |         85 | degC      |  85.00 | out of range
      102 | 2026-10-27 09:00:00+00 |        640 | ppm       | 640.00 | ok
      102 | 2026-10-27 09:01:00+00 |            | ppm       |        | missing value or timestamp
      102 | 2026-10-28 09:00:00+00 |        650 | ppm       | 650.00 | timestamp in the future
      999 | 2026-10-27 09:00:00+00 |          1 | kW        |        | unknown point

The checks, one by one

• Range. Use the physically possible range of the sensor and process (a supply-air sensor cannot read 85 degC in a working AHU), not the comfort band. Operational limits such as "zone too warm" are alarms, not data quality failures. Keep limits per point in metadata, maintained with the point list, so commissioning changes do not need code changes.
• Stuck sensor. The query counts consecutive identical values per point (a run) and flags readings from position stuck_after onwards. Choose the threshold per point type and resolution: a CO2 sensor in an empty floor at night, or a valve at 0 percent, can legitimately be flat for hours, while a supply-air temperature with 0.1 degree resolution rarely is. The demo uses 5 readings only to keep the output short; it is not a suggested value.
• Spike. A reading that differs from both its neighbours by more than max_step (3 degC in the demo, an illustrative value; base yours on the largest change the process can really make in one interval) is treated as a single-sample spike; a step change (the new level persists) is not flagged, which is usually what you want for setpoint changes. In a stream, the next reading is not available yet, so either delay the decision by one interval or flag one-sided jumps as suspect and re-evaluate later.
• Timestamps. Compare the device timestamp with the arrival time recorded by the ingest service. A reading further in the future than your clock-skew tolerance suggests a wrong device clock (or a time zone applied twice); readings days old often mean a backfill, which may be fine but should be routed deliberately. The tolerances (5 minutes and 7 days in the demo) are assumptions to set from your devices' observed clock drift and your gateways' buffering capacity. Store timestamptz and convert to UTC at the edge. Note that now() in PostgreSQL returns the start time of the current transaction; use the recorded received_at or clock_timestamp() when the difference matters.
• Units. The Project Haystack conventions require a unit on every numeric point and advise one unit system per site. Convert known units at ingest (the degF row above became 13.50 degC) and reject unknown ones rather than guessing. A wrong unit that happens to fall in range (kW vs W on a small fan) is not caught by range checks; compare against the point's expected unit, not just its limits.
• Missing and unknown. A null value or an unknown point is rejected with a reason. A burst of unknown points from one gateway usually means a renamed BMS point list, which is a configuration issue to fix, not data to keep.

Load good rows, quarantine the rest

Move rows out of staging in one transaction: accepted rows (good or suspect) into reading, failures into reading_rejected, then clear the staging rows that were processed.

BEGIN;
INSERT INTO reading (point_id, ts, value, quality)
SELECT point_id, ts, value, CASE WHEN suspect_reason IS NULL THEN 0 ELSE 1 END
FROM staging_checked WHERE reject_reason IS NULL
ON CONFLICT (point_id, ts) DO NOTHING;

INSERT INTO reading_rejected (point_id, ts, value, unit, received_at, reason)
SELECT point_id, ts, sent_value, sent_unit, received_at, reject_reason
FROM staging_checked WHERE reject_reason IS NOT NULL;

DELETE FROM staging_reading;
COMMIT;

SELECT quality, count(*) AS readings FROM reading GROUP BY quality ORDER BY quality;
SELECT reason, count(*) AS rejected FROM reading_rejected GROUP BY reason ORDER BY reason;

 quality | readings
---------+----------
       0 |        7
       1 |        2

           reason           | rejected
----------------------------+----------
 missing value or timestamp |        1
 out of range               |        1
 timestamp in the future    |        1
 unknown point              |        1

In production: the race, and a safer variant

The load above was run in a single session only; concurrent loaders and concurrent ingest writers were not run here, because PGlite has one session. The known race: under PostgreSQL's default Read Committed level, the documentation notes that two successive commands in one transaction can see different data if other transactions commit in between, so staging rows committed by the ingest service after the INSERT ... SELECT statements began but before the DELETE began would be deleted without being loaded. Two loaders running at once could also load and quarantine the same rows twice (the readings insert skips duplicates, the reject table does not).

A safer variant relies only on documented lock behaviour rather than on timing. Take a SHARE ROW EXCLUSIVE lock on the staging table at the start of the load transaction. The PostgreSQL documentation states that this mode protects a table against concurrent data changes and is self-exclusive, so only one session can hold it at a time; INSERT takes ROW EXCLUSIVE, which conflicts with it; and a lock is normally held until the end of the transaction. So the lock is granted only after in-progress ingest transactions on the staging table have finished, no new ingest row can be written until the load commits, a second loader waits, and the classify, load and delete statements all see the same rows. It ran in a single session with the same results as above; the blocking between sessions follows from the documented lock conflicts and was not run here.

BEGIN;
-- one loader at a time, and no ingest INSERT can commit into staging until this transaction ends
LOCK TABLE staging_reading IN SHARE ROW EXCLUSIVE MODE;

INSERT INTO reading (point_id, ts, value, quality)
SELECT point_id, ts, value, CASE WHEN suspect_reason IS NULL THEN 0 ELSE 1 END
FROM staging_checked WHERE reject_reason IS NULL
ON CONFLICT (point_id, ts) DO NOTHING;

INSERT INTO reading_rejected (point_id, ts, value, unit, received_at, reason)
SELECT point_id, ts, sent_value, sent_unit, received_at, reject_reason
FROM staging_checked WHERE reject_reason IS NOT NULL;

DELETE FROM staging_reading;
COMMIT;

SELECT quality, count(*) AS readings FROM reading GROUP BY quality ORDER BY quality;
--  quality | readings
-- ---------+----------
--        0 |        7
--        1 |        2

SELECT count(*) AS staging_left FROM staging_reading;
--  staging_left
-- --------------
--             0

LOCK TABLE staging_reading IN SHARE ROW EXCLUSIVE MODE;   -- outside BEGIN ... COMMIT
-- ERROR:  LOCK TABLE can only be used in transaction blocks
-- SQLSTATE 25P01

Limits of the safer variant

The lock makes ingest writers wait for the whole load, so keep each load short (small staging batches, frequent runs) and watch for ingest timeouts. If ingest must never wait, the alternative is to scope everything to a batch: the ingest service writes a batch ID with every staging row and commits a batch completely before the loader may take it, the view and every statement filter on that batch ID, and the delete removes only that batch; one loader at a time is still needed (for example the same lock on a small batch-control table, or a scheduler that never overlaps runs). Neither variant was run with concurrent sessions here. Downstream, roll-ups should filter quality = 0, as in the roll-up example on the storage page. For how batch and streaming ingestion differ in where these checks run, see batch vs stream ingestion.

Adding a constraint to a large existing table

Adding a CHECK constraint normally scans the whole table while blocking writes, which is unwelcome on a readings table that receives data every second. NOT VALID adds the constraint for new rows immediately without the scan; VALIDATE CONSTRAINT checks existing rows later under a lighter lock. The documentation also notes this lets you fix pre-existing violations at leisure while no new ones get in.

ALTER TABLE reading ADD CONSTRAINT reading_ts_after_2000
    CHECK (ts >= '2000-01-01 00:00+00') NOT VALID;
ALTER TABLE reading VALIDATE CONSTRAINT reading_ts_after_2000;

SELECT conname, convalidated FROM pg_constraint
WHERE conrelid = 'reading'::regclass AND contype = 'c' ORDER BY conname;
--         conname        | convalidated
-- -----------------------+--------------
--  reading_quality_check | t
--  reading_ts_after_2000 | t
--  reading_value_check   | t

Operations, limitations and when not to reject

Monitor the checks. Track rejected and suspect counts per point, per gateway and per reason each day. A sudden rise for one gateway points at an integration fault; a single point that is persistently stuck or spiking is a maintenance job. In a CAFM or CMMS setting, a rule such as "stuck for 24 hours" (an illustrative duration) can raise a sensor calibration work order against the asset, with a unique key on point and fault type so the rule does not open a new ticket on every run.

Review thresholds. Limits set at commissioning drift out of date when plant is replaced or sequences change. Treat the point metadata as configuration with an owner and a change history.

When not to reject. For billing or regulatory data, keep the raw value exactly as received (in a raw table or the reject table) even when you correct or estimate it elsewhere; corrections need an audit trail. And do not use these rules as anomaly detection: they catch broken data, not unusual but genuine behaviour, which is the job of the methods in time-series anomaly detection.

Limitations. The window checks assume each batch contains enough history per point; for the first reading of a batch, lag() returns null, so carry the last accepted value per point into the batch (or read it from reading) if spikes at batch boundaries matter. Readings that arrive out of order need the same care; see late-arriving and out-of-order events.

Frequently asked questions

Sources

• PostgreSQL 18 documentation: Constraints
• PostgreSQL 18 documentation: ALTER TABLE
• PostgreSQL 18 documentation: Window Functions
• PostgreSQL 18 documentation: Numeric Types
• PostgreSQL 18 documentation: Date/Time Functions and Operators
• OPC UA Part 4 v1.05: 7.38 StatusCode
• OPC UA Part 4 v1.05: 7.39 TimestampsToReturn
• Project Haystack: Units
• PostgreSQL 18 documentation: Transaction Isolation
• PostgreSQL 18 documentation: Explicit Locking
• PostgreSQL 18 documentation: LOCK

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.