Sentinel Values Cleanup — Solution & ExplanationProblems / Sentinel Values Cleanup / EditorialApproach

Sentinels → NULLs in a CTE. NULLIF(NULLIF(station, 'N/A'), '') handles both string sentinels; NULLIF(value, -999) the numeric one. COALESCE(..., 'unknown') then gives the NULL stations a reportable name.

Aggregate the cleaned relation. COUNT(*) intentionally counts every row — a failed reading is still an event — while AVG(value) automatically excludes the NULLified failures. The all-failed station surfaces as readings > 0, avg_value NULL, which is the honest summary.

Key InsightSentinel values are lies told in-band: -999 is a perfectly valid float to every aggregate that touches it. NULL is SQL's out-of-band "absent" marker, and NULLIF is the purpose-built converter — cleaning is therefore *type-honesty restoration*, done once in a CTE so every downstream aggregate inherits correct semantics for free. The pattern generalizes: find the domain's magic values (-999, 9999-12-31, 'N/A', empty string), NULLIF them at the earliest layer, and let COUNT/AVG/MIN/MAX behave the way their authors intended.Hints

NULLIF(x, sentinel) returns NULL when x equals the sentinel, else x — chain two of them for the two string sentinels, then COALESCE the result into the 'unknown' bucket.

Clean BEFORE aggregating: averaging raw values lets every -999 drag a station's average through the floor, which is precisely how sentinel bugs reach dashboards.

The two aggregates deliberately see different things: COUNT(*) counts all rows (a failed reading still happened), while AVG(value) only sees the NULLified-clean values.

A station whose every reading failed averages over nothing — NULL. Leave it: 'no valid data' must stay distinguishable from 'average is 0'.

Solve Sentinel Values Cleanup yourself →