What is a data validity check? Examples in SQL

Present is not the same as right

Take a users.status column with zero nulls. A null-rate check is happy. But if a deploy starts writing actvie, every dashboard that filters on status = 'active' quietly loses those users. Nothing errors, nothing is missing, the numbers are just wrong.

A validity check encodes the rule for a column and counts the rows that break it. Three rule types cover most cases:

• Allowed set. The value is one of a known list.
• Range. The value sits between a minimum and a maximum.
• Format. The value matches a pattern, such as an email or a country code.

One query, several rules

Count violations per rule in a single pass. The output also tells you which rule fired.

SELECT
  count(*) FILTER (WHERE age NOT BETWEEN 0 AND 120) AS bad_age,
  count(*) FILTER (WHERE status NOT IN ('active','archived','suspended')) AS bad_status,
  count(*) FILTER (WHERE email IS NOT NULL
                   AND email !~ '^[^@\s]+@[^@\s]+\.[^@\s]+$') AS bad_email,
  count(*) FILTER (WHERE country !~ '^[A-Z]{2}$') AS bad_country
FROM users;

Two details. NOT IN and NOT BETWEEN are never true for a NULL, so null values are skipped by those rules, which is what you want when nulls are covered by a separate check. And the email pattern is deliberately loose: it flags obvious garbage, not every address a mail server would reject.

Turn it into an alert

A monitor in arcodash reads one column of the first row, so alert on a total. Sum the rules into one number and fire above zero, or above a small tolerance if the source is messy.

SELECT bad
FROM (
  SELECT count(*) FILTER (
           WHERE age NOT BETWEEN 0 AND 120
              OR status NOT IN ('active','archived','suspended')
         ) AS bad
  FROM users
) t
WHERE bad > 0;

This form returns nothing when all is well, which suits any scheduler that alerts on a non-empty result. In arcodash, drop the final WHERE, monitor the bad column with > and 0, and schedule the query. The data observability monitors page covers the same pattern for null rate and duplicates.

Where validity checks go wrong

• Scanning the whole table every run. Limit to recent rows with a created_at filter once the table is large, or the check becomes the slowest query you have.
• Hard-coded lists that go stale. A new legitimate status will fire the alert until you update the list. That is the check working, but make updating it part of shipping the new value.
• One giant rule. Keep a count per rule so the alert says which one broke.

Validity sits beside freshness and volume: arrived, enough, and sane.