---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: dq
excerpt: |
DuckDB Community Extensions
Data quality assertion framework — SQL-native expect_* rules, profiling, and persisted quality reports
extension:
name: dq
description: Data quality assertion framework — SQL-native expect_* rules, profiling, and persisted quality reports
version: 0.1.0
language: Rust
build: cargo
license: MIT
excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_rtools;windows_amd64_mingw;linux_amd64_musl;linux_arm64_musl;windows_arm64"
requires_toolchains: "rust"
test_config: '{"test_env_variables": {"SKIP_TESTS": "1"}}'
maintainers:
- alitrack
repo:
github: alitrack/duckdb_dq
ref: 3d5e95d24383353a1bd36e20edfa76ee169b805a
ref_next: 3d5e95d24383353a1bd36e20edfa76ee169b805a
docs:
hello_world: |
-- 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');
extended_description: |
# 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)` becomes `SELECT 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.rs` keeps 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_expectations` rule set is a machine-verifiable contract.
## Build
```sh
make configure # once: venv + platform stamp
make release # → build/release/dq.duckdb_extension
```
extension_star_count: 6
extension_star_count_pretty: 6
extension_download_count: 8098
extension_download_count_pretty: 8.1k
image: '/images/community_extensions/social_preview/preview_community_extension_dq.png'
layout: community_extension_doc
---
### Compatibility Matrix
This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), 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
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```
{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}
{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### 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{% raw %}}}{% endraw %}')] |
| 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{% raw %}}}{% endraw %}')] |
| 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{% raw %}}}{% endraw %}')] |
| 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{% raw %}}}{% endraw %}')] |
| 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 |