Data quality assertion framework — SQL-native expect_* rules, profiling, and persisted quality reports
Maintainer(s):
alitrack
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL dq FROM community;
LOAD dq;
Example
-- Single assertion → result table (0 rows = pass)
SELECT * FROM expect_not_null('sales', 'amount');
-- rule | table_name | column_name | passed | row_count | failed_count | error
-- Enumerated values, regex, foreign keys, custom SQL
SELECT * FROM expect_accepted_values('customers', 'status', 'active,inactive,suspended');
SELECT * FROM expect_match_regex('customers', 'email', '^[^@]+@[^@]+\\.com$');
SELECT * FROM expect_relationship('orders', 'customer_id', 'customers', 'id');
SELECT * FROM expect_custom_sql('orders', 'amount < 0');
-- Profiling
SELECT * FROM profile_table('sales');
-- Batch assertions (GX-style JSON rule set)
SELECT * FROM validate_expectations('sales', '{
"expect_table_row_count_between": {"min": 100, "max": 10000000},
"expect_column_values_not_null": {"column": "order_id"},
"expect_column_values_unique": {"column": "order_id"},
"expect_column_values_in_range": {"column": "amount", "min": 0, "max": 100000},
"expect_column_values_match_regex": {"column": "email", "pattern": "^[^@]+@[^@]+$"},
"expect_column_relationship": {"column": "customer_id", "to_table": "customers", "to_column": "id"},
"expect_custom_sql": {"sql": "{table}.amount < 0"}
}');
-- Run + persist a named quality check, then view report history
SELECT dq_run('daily_sales', 'sales', '{"expect_table_row_count_between": {"min": 100, "max": 10000000}}');
SELECT * FROM dq_reports();
-- Profile-driven suggestions: what should I be checking? (recall-first)
SELECT * FROM dq_suggest('sales');
About dq
duckdb_dq
Data quality assertion framework for DuckDB — SQL-native expect_* rules, profiling, and persisted quality reports. Zero Python, zero external services: everything runs inside DuckDB on a persistent secondary connection, so DuckDB's vectorized engine does all the counting.
Functions
| Function | Type | Description |
|---|---|---|
expect_not_null(table, column) |
table | Fails if any NULL in column |
expect_unique(table, column) |
table | Fails if duplicate values in column |
expect_in_range(table, column, lo, hi) |
table | Fails on NULL / below lo / above hi |
expect_row_count_between(table, lo, hi) |
table | Fails if row count outside [lo, hi] |
expect_accepted_values(table, column, 'a,b,c') |
table | Fails on NULL / values outside allowed set |
expect_match_regex(table, column, pattern) |
table | Fails on NULL / values not matching regex |
expect_relationship(table, column, to_table, to_column) |
table | Fails on orphan values (broken FK) |
expect_custom_sql(table, where_clause) |
table | Fails on rows returned by custom WHERE (supports {table}) |
expect_min_between / expect_max_between / expect_mean_between / expect_stddev_between / expect_sum_between |
table | Statistical bounds on aggregate |
expect_distinct_count_between(table, column, lo, hi) |
table | Fails if COUNT(DISTINCT column) outside [lo, hi] |
expect_column_type(table, column, type) |
table | Fails if column's logical type ≠ expected |
expect_table_column_count_between(table, lo, hi) |
table | Fails if table column count outside [lo, hi] |
expect_null_proportion_between / expect_unique_proportion_between |
table | Fails if ratio outside [lo, hi] |
expect_quantile_between(table, column, q, lo, hi) |
table | Fails if quantile_cont(col, q) outside [lo, hi] |
expect_columns_unique_together(table, col1, col2) |
table | Fails if tuple has duplicates |
expect_column_length_between / expect_null_count_between / expect_row_count_to_equal |
table | Length / null count / exact count |
expect_not_in_set / expect_not_match_regex / expect_match_date_format / expect_sorted / expect_median_between |
table | 27 assertions total |
profile_table(table) |
table | Per-column profiling (count, null %, distinct, min, max) |
validate_expectations(table, json_rules) |
table | Batch assertions from a JSON rule set |
dq_run(name, table, json_rules) |
scalar | Run rule set, persist one report row, return summary |
dq_reports() |
table | Persisted run history (name, table, summary, passed, run_at) |
dq_dashboard(table, json_rules) |
table | dash-compatible dashboard JSON + checks/passed/failed/pass_rate |
dq_federated(sources_json, table, json_rules) |
table | Cross-DB quality baseline via duckdb_universal (per-source results) |
dq_suggest(table) |
table | Profile-driven candidate rules (rule, column, severity, reason, params) — recall-first: surfaces checks worth running instead of waiting for you to invent them; suggestions feed straight into validate_expectations |
Design
- Assertions compile to SQL —
expect_in_range('t','c',0,100)becomesSELECT COUNT(*) FROM t WHERE c IS NULL OR c < 0 OR c > 100. DuckDB counts, Rust does zero per-row work. - Persistent secondary connection — DuckDB forbids querying the main connection from function callbacks;
engine.rskeeps ONE connection created in the init callback (early connect, required on macOS ARM64) and reuses it for every assertion query. - Result is a first-class table — assertions can be
WHERE NOT passed'd, joined, aggregated, or fed into dashboards/CI. - Rule JSON = data contract seed — a
validate_expectationsrule set is a machine-verifiable contract.
Build
make configure # once: venv + platform stamp
make release # → build/release/dq.duckdb_extension
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| expect_not_null | table | Fails if any NULL in column | NULL | [select * from expect_not_null('sales','amount')] |
| expect_unique | table | Fails if duplicate values in column | NULL | [select * from expect_unique('sales','order_id')] |
| expect_in_range | table | Fails on NULL / below lo / above hi | NULL | [select * from expect_in_range('sales','amount',0,100)] |
| expect_row_count_between | table | Fails if row count outside [lo, hi] | NULL | [select * from expect_row_count_between('sales',1,1000000)] |
| expect_accepted_values | table | Fails on NULL / values outside allowed set | NULL | [select * from expect_accepted_values('customers','status','active,inactive,suspended')] |
| expect_match_regex | table | Fails on NULL / values not matching regex | NULL | [select * from expect_match_regex('customers','email','^[^@]+@[^@]+.com$')] |
| expect_relationship | table | Fails on orphan values (broken FK) | NULL | [select * from expect_relationship('orders','customer_id','customers','id')] |
| expect_custom_sql | table | Fails on rows returned by custom WHERE (supports {table}) | NULL | [select * from expect_custom_sql('orders','amount < 0')] |
| expect_min_between | table | Fails if MIN(column) outside [lo, hi] | NULL | [select * from expect_min_between('sales','amount',0,100)] |
| expect_max_between | table | Fails if MAX(column) outside [lo, hi] | NULL | [select * from expect_max_between('sales','amount',0,1000)] |
| expect_mean_between | table | Fails if AVG(column) outside [lo, hi] | NULL | [select * from expect_mean_between('sales','amount',0,500)] |
| expect_stddev_between | table | Fails if STDDEV(column) outside [lo, hi] | NULL | [select * from expect_stddev_between('sales','amount',0,100)] |
| expect_sum_between | table | Fails if SUM(column) outside [lo, hi] | NULL | [select * from expect_sum_between('sales','amount',0,10000000)] |
| expect_distinct_count_between | table | Fails if COUNT(DISTINCT column) outside [lo, hi] | NULL | [select * from expect_distinct_count_between('sales','order_id',1,100000)] |
| expect_column_type | table | Fails if column's logical type != expected | NULL | [select * from expect_column_type('sales','amount','DOUBLE')] |
| expect_table_column_count_between | table | Fails if table column count outside [lo, hi] | NULL | [select * from expect_table_column_count_between('sales',1,100)] |
| expect_null_proportion_between | table | Fails if NULL ratio outside [lo, hi] | NULL | [select * from expect_null_proportion_between('sales','email',0,0.5)] |
| expect_unique_proportion_between | table | Fails if distinct ratio outside [lo, hi] | NULL | [select * from expect_unique_proportion_between('sales','order_id',0.5,1)] |
| expect_quantile_between | table | Fails if quantile_cont(col, q) outside [lo, hi] | NULL | [select * from expect_quantile_between('sales','amount',0.5,0,500)] |
| expect_columns_unique_together | table | Fails if (col1, col2) tuple has duplicates | NULL | [select * from expect_columns_unique_together('sales','order_id','amount')] |
| expect_column_length_between | table | Fails if any non-null value length outside [lo, hi] | NULL | [select * from expect_column_length_between('customers','email',3,255)] |
| expect_null_count_between | table | Fails if NULL row count outside [lo, hi] | NULL | [select * from expect_null_count_between('sales','email',0,100)] |
| expect_row_count_to_equal | table | Fails if table row count != n | NULL | [select * from expect_row_count_to_equal('sales',3)] |
| expect_not_in_set | table | Fails on values in forbidden set | NULL | [select * from expect_not_in_set('customers','status','deleted,blocked')] |
| expect_not_match_regex | table | Fails on values matching forbidden regex | NULL | [select * from expect_not_match_regex('customers','email','test@')] |
| expect_match_date_format | table | Fails on values not parsing per strptime format | NULL | [select * from expect_match_date_format('orders','created_at','%Y-%m-%d')] |
| expect_sorted | table | Fails on adjacent inversions (physical order) | NULL | [select * from expect_sorted('sales','order_id','asc')] |
| expect_median_between | table | Fails if median outside [lo, hi] | NULL | [select * from expect_median_between('sales','amount',0,500)] |
| profile_table | table | Per-column profiling (count, null %, distinct, min, max) | NULL | [select * from profile_table('sales')] |
| validate_expectations | table | Batch assertions from a JSON rule set | NULL | [select * from validate_expectations('sales','{"expect_table_row_count_between": {"min": 1, "max": 100}}')] |
| dq_run | scalar | Run rule set, persist one report row, return summary | NULL | [select dq_run('daily_sales','sales','{"expect_table_row_count_between": {"min": 1, "max": 100}}')] |
| dq_reports | table | Persisted run history (name, table, summary, passed, run_at) | NULL | [select * from dq_reports()] |
| dq_dashboard | table | dash-compatible dashboard JSON + checks/passed/failed/pass_rate | NULL | [select * from dq_dashboard('sales','{"expect_table_row_count_between": {"min": 1, "max": 100}}')] |
| dq_federated | table | Cross-DB quality baseline via duckdb_universal (per-source results) | NULL | [select * from dq_federated('[{"path": "a.duckdb"}]','sales','{"expect_table_row_count_between": {"min": 1, "max": 100}}')] |
| dq_suggest | table | Profile-driven candidate rules (rule, column, severity, reason, params) | NULL | [select * from dq_suggest('sales')] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |