---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: dqtest
excerpt: |
  DuckDB Community Extensions
  Define and run data-quality tests inside DuckDB

extension:
  name: dqtest
  description: Define and run data-quality tests inside DuckDB
  version: 0.1.1
  language: C++
  build: cmake
  license: MIT
  maintainers:
    - vhe74
    
    
repo:
  github: vhe74/duckdb-dataquality-extension
  ref: 72640ababe51c90daa5383467a8e14e12db85d6c

docs:
  hello_world: |
    -- Initialize the data quality testing framework
    CALL dq_init();

    -- Create a sample table
    CREATE TABLE customers (
        id INTEGER PRIMARY KEY,
        name VARCHAR,
        email VARCHAR,
        status VARCHAR,
        age INTEGER
    );

    CREATE TABLE orders (
        order_id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        amount DECIMAL(10,2),
        status VARCHAR
    );

    -- Insert sample data
    INSERT INTO customers VALUES
        (1, 'Alice', 'alice@example.com', 'active', 25),
        (2, 'Bob', NULL, 'active', 30),
        (3, 'Charlie', 'charlie@example.com', 'inactive', 35);

    INSERT INTO orders VALUES
        (1, 1, 100.00, 'pending'),
        (2, 1, 200.00, 'shipped'),
        (3, 2, 150.00, 'delivered'),
        (4, 99, 50.00, 'pending');

    -- Define and run data quality tests
    -- To pass a test should not return any lines
    -- (Test definition syntax depends on your implementation)
    INSERT INTO dq_tests (test_name, table_name, column_name, test_type)
    VALUES ('customers_id_unique', 'customers', 'id', 'unique');

    INSERT INTO dq_tests (test_name, table_name, column_name, test_type)
      VALUES ('customers_email_not_null', 'customers', 'email', 'not_null');

    INSERT INTO dq_tests (test_name, table_name, column_name, test_type, test_params)
    VALUES ('customers_status_valid', 'customers', 'status', 'accepted_values',
            '{"values": ["active", "inactive", "suspended"]}');

    INSERT INTO dq_tests (test_name, table_name, column_name, test_type, test_params)
    VALUES ('customers_email_format', 'customers', 'email', 'regex',
            '{"pattern": "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\\\.[a-zA-Z]{2,}$"}');

    INSERT INTO dq_tests (test_name, table_name, column_name, test_type, test_params)
    VALUES ('customers_age_range', 'customers', 'age', 'range',
            '{"min": 18, "max": 100}');

    INSERT INTO dq_tests (test_name, table_name, column_name, test_type, test_params)
    VALUES ('orders_customer_fk', 'orders', 'customer_id', 'relationship',
            '{"to_table": "customers", "to_column": "id"}');

    INSERT INTO dq_tests (test_name, table_name, test_type, test_params)
    VALUES ('customers_min_rows', 'customers', 'row_count',
            '{"min": 1, "max": 1000}');

    INSERT INTO dq_tests (test_name, table_name, test_type, test_params)
    VALUES ('orders_orphan_check', 'orders', 'custom_sql',
            '{"sql": "SELECT * FROM {table} WHERE customer_id NOT IN (SELECT id FROM customers)"}');

    -- Run all tests
    CALL dq_run_tests();

    -- View test results
    SELECT t.test_name, r.status, r.executed_at
      FROM dq_tests t 
      LEFT JOIN dq_test_results r ON t.test_id = r.test_id;

    -- View test results of the last execution 
    SELECT t.test_name, r.status, r.executed_at
      FROM dq_tests t 
      LEFT JOIN dq_test_results r ON t.test_id = r.test_id
      QUALIFY ROW_NUMBER() OVER (PARTITION BY t.test_name ORDER BY r.executed_at DESC NULLS LAST) = 1;

    -- view all compiled sql of the last execution 
    SELECT t.test_name, r.status, r.compiled_sql  FROM dq_tests t 
      LEFT JOIN dq_test_results r ON t.test_id = r.test_id
      QUALIFY ROW_NUMBER() OVER (PARTITION BY t.test_name ORDER BY r.executed_at DESC NULLS LAST) = 1;

extended_description: |
  # DuckDB Data Quality Extension

  A DuckDB extension for defining, running, and tracking data quality tests directly within your database.

  ## Description

  The Data Quality Extension (`dqtest`) enables you to implement comprehensive data quality testing in DuckDB. Define tests using SQL expressions, run them against your data, and track results over time - all without leaving your database environment.

  ### Key Features

  - **SQL-based test definitions**: Write data quality tests using familiar SQL syntax
  - **Flexible test framework**: Define expectations, assertions, and validations on any table or query
  - **Test execution engine**: Run individual tests or entire test suites
  - **Results tracking**: View test results, failure details, and execution history
  - **Built-in reporting**: Access test summaries and identify failing tests through convenient views

  ### Core Functions

  - `dq_init()` - Initialize the data quality testing schema
  - `dq_run_tests()` - Execute all defined data quality tests
  - `dq_run_tests(test_id)` - Run a specific test by id
  - `dq_run_tests(table_name)` - Run a specific test for a specific table

  ### Use Cases

  - Validate data integrity after ETL pipelines
  - Monitor data quality in production databases
  - Enforce data contracts and expectations
  - Track data quality metrics over time
  - Catch data anomalies and schema changes early
extension_star_count: 5
extension_star_count_pretty: 5
extension_download_count: 628
extension_download_count_pretty: 628
image: '/images/community_extensions/social_preview/preview_community_extension_dqtest.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.

<div class="extension_compatibility_table"></div>

| 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

<div class="extension_functions_table"></div>

| function_name | function_type | description | comment | examples |
|---------------|---------------|-------------|---------|----------|
| dq_init       | table         | NULL        | NULL    |          |
| dq_run_tests  | table         | NULL        | NULL    |          |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

This extension does not add any settings.