Data Quality Checks

AnoFox Tabular v2026.08.22 turns data-quality monitoring into plain SQL: 13 check primitives cover the rules teams actually enforce (format conformance, allowed values, aggregates, duplicates, referential integrity, custom predicates, volume baselines over time), and the run_checks suite runner executes a whole checks table in one call — returning one uniform pass/warn/fail row per check, ready to persist as monitoring history.

Define checks once, run them everywhere your data lives: every check is a table function that takes the target table name as a string, so it works on tables, views and DuckDB-attached sources alike.

Quick Reference

Function	Description	SQL Signature
anofox_tab_regex_match	Share of values matching a pattern	(table, column, pattern, min_rate [, max_rate]) -> TABLE
anofox_tab_values_in_set	Share of values in an allowed set	(table, column, allowed [, min_rate]) -> TABLE
anofox_tab_agg_check	Aggregate within bounds	(table, column, agg, lower, upper) -> TABLE
anofox_tab_duplicate_count	Duplicates over a key	(table, columns [, max_duplicates]) -> TABLE
anofox_tab_occurrence	Frequency of the most/least common value	(table, column, mode, lower, upper) -> TABLE
anofox_tab_match_rate	Referential integrity between tables	(left, right, left_keys, right_keys [, min_rate]) -> TABLE
anofox_tab_compliance	Rows satisfying a SQL predicate	(table, expression [, min_rate]) -> TABLE
anofox_tab_rel_count_change	Daily count vs rolling baseline	(table, date_col [, count_col, window, lower, upper, ref_date]) -> TABLE
anofox_tab_metric_anomaly_iqr	Daily metric outside IQR band	(table, date_col [, metric_col, window, k, mode, ref_date]) -> TABLE
anofox_tab_rolling_values_in_set	Value set over a trailing window	(table, column, allowed, date_col [, window, min_rate, ref_date]) -> TABLE
anofox_tab_run_checks	Run a whole checks table	(checks_table) -> TABLE

All functions return assertion-style rows (status, measured value, thresholds, message). The base assertions volume, null_rate, distinct_count, schema_check and freshness are documented on the Quality page and participate in run_checks too.

---

Rule Primitives (7 functions)

anofox_tab_regex_match

Assert that the share of non-NULL values matching a regular expression is within bounds. NULLs are excluded from the rate.

Parameters

Parameter	Type	Required	Default	Description
table_name	VARCHAR	Yes	-	Table or view to check
column_name	VARCHAR	Yes	-	Column to inspect (cast to VARCHAR)
pattern	VARCHAR	Yes	-	Regular expression (RE2, as in regexp_matches)
min_match_rate	DOUBLE	Yes	-	Minimum acceptable match rate (NULL = none)
max_match_rate	DOUBLE	No	NULL	Maximum acceptable match rate

Example

SELECT * FROM regex_match('users', 'email', '^[^@]+@[^@]+\.[^@]+$', 0.99);
-- status: fail, match_rate: 0.97, matched_count: 4850, total_count: 5000

anofox_tab_values_in_set

Assert that the share of non-NULL values contained in an allowed set is at least min_rate (default 1.0). Reports up to 5 distinct offending values in sample_violations — so a failing check tells you what leaked in, not just that something did.

Example

SELECT * FROM values_in_set('orders', 'status', ['completed', 'pending', 'cancelled']);
-- status: fail, in_set_rate: 0.9998, sample_violations: [UNKNOWN]

anofox_tab_agg_check

Assert that an aggregate of a numeric column lies between lower_threshold and upper_threshold (NULL = unbounded). The aggregate is one of 'avg', 'min', 'max', 'sum', 'median', 'stddev' — validated at bind time.

Example

SELECT * FROM agg_check('orders', 'amount', 'min', 0.0, NULL);
-- status: fail, agg: min, value: -899.0  (negative amounts snuck in)

anofox_tab_duplicate_count

Assert that a column — or a comma-separated column combination — has at most max_duplicates duplicates (default 0). Duplicates are COUNT(*) - COUNT(DISTINCT key).

Example

SELECT * FROM duplicate_count('orders', 'order_id');                       -- primary key
SELECT * FROM duplicate_count('orders', 'customer_id,order_date', 10);    -- composite key with allowance

anofox_tab_occurrence

Assert that the highest ('max') or lowest ('min') frequency of any single value stays within bounds. Catches both hot-spot values (one customer id on half the rows) and orphan categories.

Example

SELECT * FROM occurrence('orders', 'customer_id', 'max', NULL, 100);
-- status: fail, occurrence: 4520, extreme_value: '132'

anofox_tab_match_rate

Assert that the share of left-table rows with a join partner in the right table is at least min_rate (default 1.0) — a referential-integrity / foreign-key check without declaring constraints. Keys are comma-separated column lists; the right side is deduplicated so join fan-out cannot inflate the rate.

Example

SELECT * FROM match_rate('orders', 'customers', 'customer_id', 'id');
-- status: fail, match_rate: 0.8, matched_count: 4, total_count: 5

anofox_tab_compliance

Assert that the share of rows satisfying an arbitrary SQL boolean expression is at least min_rate. Rows where the expression evaluates to NULL count as non-compliant. This is the escape hatch for every rule the other primitives don't cover.

Trust model

The expression executes as SQL with the caller's privileges. It is validated at bind time to be a single boolean expression — multi-statement injection payloads are rejected with a binder error — but scalar subqueries remain allowed. Pass only trusted expressions, exactly as you would trust any SQL you run.

Example

SELECT * FROM compliance('orders', 'amount > 0 AND ship_date >= order_date', 0.99);
-- status: pass, compliance_rate: 0.9987

---

Time-Aware Checks (3 functions)

These checks aggregate the target per day (via a date or timestamp column) and judge the reference date — by default the latest date present in the data — against its own history. Missing history passes trivially with an explicit message: not enough data is not a failure.

anofox_tab_rel_count_change

Assert that the daily (distinct) count on the reference date deviates from the rolling baseline average by a relative change within [lower_threshold, upper_threshold] (defaults −0.5/+0.5, 7-day baseline).

SELECT * FROM rel_count_change('orders', 'order_date');
-- status: fail, reference_count: 2, baseline_avg: 10.0, rel_change: -0.8

anofox_tab_metric_anomaly_iqr

Flag the reference date when its daily metric (row count, or the daily AVG of metric_column) falls outside Q1/Q3 ± k·IQR of the trailing window (default 30 days, k = 1.5). mode selects the bounds: 'both', 'upper' (spikes only) or 'lower' (drops only).

SELECT * FROM metric_anomaly_iqr('orders', 'order_date', NULL, 30, 1.5, 'both');
-- status: fail, metric_value: 2.0, lower_bound: 46749.5, upper_bound: 136813.5

Pointed at a persisted run_checks results table, this turns your check history into an anomaly detector: SELECT * FROM metric_anomaly_iqr('dq_results', 'run_ts', 'value', 30, 1.5, 'both');

anofox_tab_rolling_values_in_set

values_in_set restricted to the trailing window_days days ending at the reference date — catches value drift the day it starts instead of averaging it away over the table's whole history.

SELECT * FROM rolling_values_in_set('orders', 'status', ['completed', 'pending'], 'order_date', 7);

---

The Suite Runner (1 function)

anofox_tab_run_checks

Run every check defined in a checks table with one call. The runner reads the table when the query binds, expands each row into the corresponding check, and returns one uniform result row per check (and per partition, if partitioned).

The checks table

Column	Type	Meaning
check_name	VARCHAR	Unique label (required)
check_type	VARCHAR	volume, null_rate, distinct_count, regex_match, values_in_set, agg, duplicate_count, occurrence, match_rate, compliance, freshness, rel_count_change, metric_anomaly_iqr, rolling_values_in_set
table_name	VARCHAR	Target table or view (required)
column_name	VARCHAR	Column to check (NULL where not applicable)
params	VARCHAR (JSON)	Type-specific parameters: pattern, allowed_values, agg, mode, expression, right_table/left_keys/right_keys, date_column, window_days, k, reference_date, reference_time
lower_threshold / upper_threshold	DOUBLE	Value bounds; NULL = unbounded
monitor_only	BOOLEAN	true records warn instead of fail — measure first, enforce later
identifier_column	VARCHAR	Partition column: one result row per distinct value
filter_expr	VARCHAR	SQL pre-filter; supports ${today}, ${yesterday}, ${today-N} tokens

The uniform result schema

Every check — regardless of type — produces: run_ts, check_name, check_type, table_name, column_name, identifier, value (DOUBLE), lower_threshold, upper_threshold, status (pass / warn / fail / error) and message. A missing target table yields a single error row for that check instead of failing the whole run.

Example

CREATE TABLE dq_checks (
    check_name VARCHAR, check_type VARCHAR, table_name VARCHAR, column_name VARCHAR,
    params VARCHAR, lower_threshold DOUBLE, upper_threshold DOUBLE,
    monitor_only BOOLEAN, identifier_column VARCHAR, filter_expr VARCHAR
);

INSERT INTO dq_checks VALUES
    ('orders_volume', 'volume',     'orders', NULL,          NULL, 1000, NULL, false, NULL, NULL),
    ('orders_fk',     'match_rate', 'orders', 'customer_id',
     '{"right_table": "customers", "left_keys": "customer_id", "right_keys": "id"}',
     1.0, NULL, false, NULL, NULL),
    ('amount_pos',    'compliance', 'orders', NULL,
     '{"expression": "amount > 0"}', 1.0, NULL, true, NULL, NULL),
    ('nulls_by_land', 'null_rate',  'customers', 'email',    NULL, NULL, 0.1, false, 'country', NULL);

SELECT check_name, identifier, value, status, message
FROM run_checks('dq_checks')
ORDER BY check_name, identifier;

Persistence and monitoring

Results append with plain SQL — no scheduler integration required beyond running the statement:

CREATE TABLE IF NOT EXISTS dq_results AS SELECT * FROM run_checks('dq_checks') LIMIT 0;
INSERT INTO dq_results SELECT * FROM run_checks('dq_checks');

-- alert on the latest run
SELECT check_name, identifier, message FROM dq_results
WHERE run_ts = (SELECT MAX(run_ts) FROM dq_results) AND status IN ('fail', 'error');

-- detect drifting metrics across runs
SELECT * FROM metric_anomaly_iqr('dq_results', 'run_ts', 'value', 30, 1.5, 'both');

Checks table must be a regular table

run_checks reads the checks table through a separate connection at bind time, so it must be a regular (non-temporary) table. The target tables may be temporary tables or views.

---