Data Quality Checks in Pure SQL: Auditing 3 Million Taxi Rides in 115 Milliseconds

Three million taxi rides, nine quality checks, 115 milliseconds. The dataset failed three of them.

The NYC yellow-taxi data is probably the most-analyzed public dataset in existence. It has starred in a thousand benchmarks and ten thousand tutorials. It also contains 124 exact duplicate rides, a fare of −$899, and a trip that claims to have happened in 2002 — in the January 2024 file.

None of that is a criticism of the TLC. It's what real data looks like. The interesting question is how fast you can find it.

AnoFox Tabular v2026.08.22 ships a data-quality check suite for DuckDB: 13 rule primitives plus a runner, run_checks, that executes a whole table of check definitions in one call. No Python sidecar, no YAML engine, no orchestration framework — the checks are SQL, the config is a table, the results are rows. Everything below is measured on a single desktop CPU (Ryzen 9 3950X) against the TLC yellow-taxi January 2024 parquet, 2,964,624 rows. The setup script is in the post's repo folder.

One Check, One Row

Every check in Tabular is a table function: it takes the table name as a string, scans it, and returns exactly one row with a status, the measured value, and a message a human can read at 3am.

INSTALL anofox_tabular FROM community;
LOAD anofox_tabular;

CREATE TABLE trips AS SELECT * FROM 'yellow_tripdata_2024-01.parquet';
CREATE TABLE zones AS SELECT * FROM read_csv('taxi_zone_lookup.csv');

SELECT * FROM duplicate_count('trips',
  'tpep_pickup_datetime,tpep_dropoff_datetime,PULocationID,DOLocationID,fare_amount');

status	duplicate_count	total_count	message
fail	124	2,964,624	124 duplicate(s) in 2964624 rows exceeds maximum 0

That's 124 rides that exist twice — same pickup second, same dropoff second, same zones, same fare. Here's one of them: January 18, 21:16:30 → 21:21:41, zone 237 to zone 237, $7.20, recorded twice. Runtime for the five-column distinct over 3M rows: 71 ms.

The other primitives follow the same shape. The fare column:

SELECT * FROM agg_check('trips', 'fare_amount', 'min', 0.0, NULL);
-- status: fail, agg: min, value: -899.0

There are 38,341 rides with a non-positive fare, and the worst one is −$899 (these are refund/dispute artifacts, but your revenue dashboard doesn't know that). And the timestamps:

SELECT * FROM compliance('trips',
  'tpep_pickup_datetime >= TIMESTAMP ''2024-01-01''
   AND tpep_pickup_datetime < TIMESTAMP ''2024-02-01''');
-- status: fail, out-of-range rows: 18

Eighteen rows in the January 2024 file are not from January 2024. The oldest claims a pickup on 2002-12-31 22:59:39. compliance takes any SQL boolean expression, so this is the check you reach for when no named rule fits — 870 rides also manage to end before they begin (tpep_dropoff_datetime <= tpep_pickup_datetime).

Credit where due: the referential side is spotless. Every one of the 2,964,624 pickup locations resolves against the official zone table:

SELECT * FROM match_rate('trips', 'zones', 'PULocationID', 'LocationID');
-- status: pass, match_rate: 1.0, in 9 ms

And the busiest single pickup zone, found by occurrence? Zone 132 — JFK Airport, 145,240 rides.

The Problem With Ad-Hoc Checks

Each query above is fine on its own. The failure mode is organizational: the duplicate check lives in someone's notebook, the fare check in a dbt test, the timestamp rule in a Slack message from March. Nobody can answer "what do we check, and what happened last night?"

So v2026.08.22 adds the missing piece: the checks are data too. You define them as rows in a table:

CREATE TABLE dq_checks (
    check_name VARCHAR, check_type VARCHAR, table_name VARCHAR, column_name VARCHAR,
    params VARCHAR,               -- type-specific JSON
    lower_threshold DOUBLE, upper_threshold DOUBLE,
    monitor_only BOOLEAN,         -- true => 'warn' instead of 'fail'
    identifier_column VARCHAR,    -- partition: one result row per value
    filter_expr VARCHAR           -- optional pre-filter, supports ${today} tokens
);

INSERT INTO dq_checks VALUES
  ('trips_volume',       'volume',          'trips', NULL, NULL, 1000000, 10000000, false, NULL, NULL),
  ('passenger_nulls',    'null_rate',       'trips', 'passenger_count', NULL, NULL, 0.05, false, NULL, NULL),
  ('fare_positive',      'compliance',      'trips', NULL, '{"expression": "fare_amount > 0"}', 0.99, NULL, true, NULL, NULL),
  ('zone_fk',            'match_rate',      'trips', 'PULocationID',
     '{"right_table": "zones", "left_keys": "PULocationID", "right_keys": "LocationID"}', 1.0, NULL, false, NULL, NULL),
  ('trip_dupes',         'duplicate_count', 'trips',
     'tpep_pickup_datetime,tpep_dropoff_datetime,PULocationID,DOLocationID,fare_amount', NULL, NULL, 0, false, NULL, NULL),
  ('flag_in_set',        'values_in_set',   'trips', 'store_and_fwd_flag',
     '{"allowed_values": ["Y", "N"]}', 0.99, NULL, false, 'VendorID', NULL),
  ('avg_fare_by_payment','agg',             'trips', 'fare_amount',
     '{"agg": "avg"}', 0.0, 60.0, false, 'payment_type', NULL),
  ('daily_count_stable', 'rel_count_change','trips', NULL,
     '{"date_column": "tpep_pickup_datetime", "window_days": 7}', -0.5, 0.5, false, NULL, NULL);

SELECT check_name, identifier, ROUND(value, 4) AS value, status
FROM run_checks('dq_checks') ORDER BY check_name, identifier;

check_name	identifier	value	status
avg_fare_by_payment	1	18.5574	pass
avg_fare_by_payment	2	17.8660	pass
avg_fare_by_payment	3	6.7526	pass
avg_fare_by_payment	4	1.3349	pass
daily_count_stable		0.0063	pass
fare_positive		0.9871	warn
flag_in_set	1	1.0	pass
flag_in_set	2	1.0	pass
passenger_nulls		0.0473	pass
trip_dupes		124.0	fail
trips_volume		2,964,624.0	pass
zone_fk		1.0	pass

The whole suite — including the partitioned checks that fan out per vendor and per payment type — runs in 115 ms wall clock over the 3M rows. run_checks expands the table into one query plan at bind time, so DuckDB parallelizes the whole thing like any other SQL.

Three details in that output that matter in practice:

monitor_only separates measuring from enforcing. fare_positive fails its 99% threshold (98.71%), but it's marked monitor_only, so it reports warn instead of fail. That's how you introduce a new rule against messy reality: watch it for two weeks, then flip it to enforcing.

Partitioning finds what averages hide. avg_fare_by_payment returns one row per payment_type. The averages tell a story on their own: credit-card rides average $18.56, cash$17.87 — but "no charge" rides ($6.75) and disputes ($1.33) have completely different distributions. A single whole-table average would have flattened that into a number nobody questions.

Missing tables don't kill the run. A check against a table that doesn't exist yields a single status = 'error' row with the reason, and every other check still runs. Your 3am pipeline reports a broken check; it doesn't crash on it.

Checks Become History, History Becomes a Signal

The result rows have one uniform schema, so persistence is not a feature — it's an INSERT:

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');

Run that nightly and dq_results becomes a time series of every metric you assert on. Which unlocks the trick we like most: running anomaly detection on the checks themselves.

-- has any monitored value drifted outside its own 30-day IQR band?
SELECT * FROM metric_anomaly_iqr('dq_results', 'run_ts', 'value', 30, 1.5, 'both');

metric_anomaly_iqr aggregates a metric per day and flags the latest day when it falls outside Q1/Q3 ± 1.5·IQR of the trailing window. Pointed at the taxi data directly, it does volume monitoring without any configured threshold: January 15 (77,033 rides) sits comfortably inside its band of [46,749, 136,813], computed from the preceding days. A synthetic 95% volume collapse — we seeded one in our test suite — lands far outside the band and fails immediately.

The same idea powers rel_count_change (today vs. the rolling average: January 31 came in at +0.63% against its 7-day baseline — a quiet, healthy feed) and rolling_values_in_set, which catches categorical drift in the trailing week instead of averaging it away over years of history.

What This Replaces

If you run Great Expectations or a dbt test suite today, you have a version of this — plus a Python environment, a YAML dialect, and an orchestration dependency. The Tabular check suite is a deliberately smaller bet: if your data is in DuckDB (or reachable from it), the entire quality workflow is three SQL statements — INSERT your rules, SELECT FROM run_checks, INSERT the results. It runs in CI, in a cron job, in a notebook, or interactively in the CLI, because it's just SQL.

The practical rules:

1. Start with run_checks and five checks: volume, a null rate, a duplicate key, one match_rate for your most important join, one compliance rule for the invariant everyone assumes. That's the suite that would have caught everything we found above.
2. New rules start as monitor_only: true. Enforce only after you've seen a week of values.
3. Persist to a results table from day one. The history costs nothing and turns into your anomaly baseline.
4. Partition checks by the column you'd group an incident postmortem by — vendor, region, payment type.

Try It

INSTALL anofox_tabular FROM community;
LOAD anofox_tabular;
SELECT * FROM duplicate_count('your_table', 'your_key');

The full function reference lives in the Checks documentation, the release notes in the v2026.08.22 release, and the Python wrappers ship as anofox-tabular on PyPI. If your favorite check primitive is missing, tell us — the list above exists because real pipelines needed each one.