Which Sensor Records Should Reach the Training Table?

Botie builds a feature table without a SQL error. Later we discover a repeated sensor message, a reading linked to an absent pump and a temperature with the wrong scale. Successful query execution did not establish useful training data.

We will inspect a raw staging table, report every failed contract per reading identity, and keep rejected data available for repair. The rules are explicit so a reader can see what a clean report does and does not establish.

Check known assets, required readings, range and timing, then expose the failing identities.

A raw table preserves what arrived and its source identity. A curated table contains records that satisfy the declared contract after any justified transformation. A quarantine or issue table records what failed and why.

Do not silently drop failed rows and report the remaining count as if the original batch were complete. Record accepted and rejected counts, source batch IDs and the rule version. A downstream feature query should know whether a missing interval means no events or an unresolved ingestion failure.

The audit output is part of the dataset, not an incidental debug message.

Duplicate event identities can count the same physical observation twice. Distinct identities with equal temperatures are different observations and should not automatically collapse. Define which source fields identify an event before applying deduplication.

A duplicate ID with conflicting payloads is a different problem from an exact replay. Choose a policy based on source revisions or quarantine the conflict. Taking the first row the database happens to return is not a reproducible rule.

The exercise reports a duplicate identity once, while retaining all issue categories it violates.

Our audit returns id and issue. Separate SELECT branches identify duplicates, unknown assets, missing values, out-of-range values and future events. UNION makes each identity/reason pair appear once. A single CASE expression would stop after one branch and could hide a second problem on the same record.

The range is an invented accepted interval in one declared unit. It is inclusive at both ends. NULL is checked separately because ordinary numeric comparisons with NULL do not return true.

Explain the boundary values and clock assumptions beside each rule.

CREATE TABLE assets(id TEXT PRIMARY KEY NOT NULL); INSERT INTO assets VALUES('P7'),('P8');
CREATE TABLE raw_readings(id TEXT,asset_id TEXT,value REAL,event_seq INTEGER,ingest_seq INTEGER);
INSERT INTO raw_readings VALUES('R1','P7',20,1,2),('R2','P99',25,2,3),('R3','P8',NULL,3,4),('R4','P7',200,6,5),('R1','P7',20,1,2);

SELECT DISTINCT r.id,r.asset_id FROM raw_readings r
LEFT JOIN assets a ON a.id=r.asset_id WHERE a.id IS NULL ORDER BY r.id;

Event time describes when something happened. Ingestion time describes when the pipeline received it. Prediction time determines whether the information was available for a training example. A historical event that arrived later can be valid raw data while being unavailable to an earlier prediction.

The simple future-event check only verifies consistency under a shared-clock assumption. It is not a point-in-time feature join. Use the earlier feature-table lesson’s availability conditions when assembling model inputs, and keep label availability separate from feature availability.

A clean range-and-identity audit does not rule out target leakage.

A batch can satisfy every row constraint while losing half the expected sensors. Monitor freshness, volume, null rates and distribution changes at meaningful groups such as site or device family. Compare these with documented expectations and investigate changes rather than automatically declaring every shift wrong.

A real equipment upgrade can change the measurement distribution. A stale ingestion job can leave a perfectly shaped but obsolete table. SQL summaries provide evidence for these checks; deciding what action to take requires the application’s context.

Choose thresholds from observed operating requirements, and record the uncertainty behind them.

Store the SQL or rule version, source snapshot, accepted units and data cutoffs with the validation report. Rebuilding a model from today’s corrected table may differ from the data used by the original run. Preserve a versioned snapshot or a reproducible change history when that distinction matters.

Test the audit with simultaneous failures, boundary values and an empty batch. A no-issue output for zero records should not automatically be labelled a healthy delivery. The pipeline also needs a separate completeness expectation.

The incremental lesson will preserve source change position so repairs and replays can be reconciled.