Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 38 ยท Patterns and Pipelines

Data Quality and Testing Pipelines

Protect a DuckDB pipeline with SQL data-quality checks: nulls, duplicates, ranges, referential integrity, row-count reconciliation, constraints and pytest unit tests.

A pipeline that runs without errors can still produce wrong numbers. A column quietly turns NULL, a source file arrives twice, a join drops rows, a currency changes. Data quality checks are queries that return zero rows when the data is healthy, and the rows that explain the problem when it is not. Because DuckDB runs SQL on files and tables alike, writing checks is cheap.

The real DuckDB does not run here, so Python and DuckDB-only statements are read-only. Every check query is plain SQL and runs below on small tables.

The check-as-query idea

Write each rule so that any row returned is a violation. The pipeline then fails if the count is above zero:

SQL
-- Rule: every order has a customer
SELECT o.order_id
FROM staging.orders o
LEFT JOIN staging.customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

This shape is easy to automate, easy to read, and the failing rows are your debugging clues.

A toolbox of checks

SQL Run a battery of checks on a table

Press Run (or Ctrl + Enter) to see the result here.

The usual categories:

Check What it catches Example condition
Not null missing required values WHERE amount IS NULL
Unique duplicate keys GROUP BY id HAVING count(*) > 1
Accepted values typos, new codes WHERE status NOT IN (...)
Range impossible values WHERE amount < 0 OR amount > 1e6
Referential integrity orphan foreign keys LEFT JOIN ... WHERE parent.id IS NULL
Freshness stale loads max(loaded_at) < now() - INTERVAL 1 DAY
Volume partial or doubled loads row count within 50% to 150% of last run
Format malformed text NOT regexp_matches(email, '^[^@]+@[^@]+$')

Reconciliation

Totals should agree across layers. Compare counts and sums between raw and staging, or between a source and a mart:

SQL Reconcile two layers

Press Run (or Ctrl + Enter) to see the result here.

A non-zero difference should either be explained (rows deliberately filtered) or fail the run. Show which rows disappeared with an anti-join: SELECT * FROM raw_orders r WHERE NOT EXISTS (SELECT 1 FROM stg_orders s WHERE s.id = r.id).

Constraints as guardrails

Constraints in the database enforce rules at write time, which is stronger than a check afterwards:

SQL
CREATE TABLE staging.orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount      DECIMAL(12,2) CHECK (amount >= 0),
    status      VARCHAR CHECK (status IN ('paid','refunded','pending'))
);

An offending insert fails the whole statement. DuckDB supports PRIMARY KEY, UNIQUE, NOT NULL and CHECK; it also parses FOREIGN KEY and enforces it (with some limits on updating or deleting parents, so read the docs for your version). Remember that primary-key indexes cost memory on huge tables; for large append-only fact tables you may prefer check queries over constraints.

Try it with a deliberate good row and a guard that shows what would be rejected:

SQL Find rows a CHECK constraint would reject

Press Run (or Ctrl + Enter) to see the result here.

With real constraints the offending INSERT simply fails, protecting the table.

Profiling before you check

If you do not yet know what is wrong, profile. In DuckDB:

SQL
SUMMARIZE staging.orders;

shows per-column min, max, approximate unique count, average, percentiles and null percentage. A column whose null percentage jumped from 1% to 40% is a finding on its own. SELECT approx_count_distinct(col) and histogram(col) help investigate suspicious fields.

Automating with Python and pytest

Keep checks as SQL files and run them from a test:

Python
import duckdb, pytest

CHECKS = {
    "no_duplicate_orders": "SELECT order_id FROM staging.orders GROUP BY 1 HAVING count(*) > 1",
    "no_negative_amounts": "SELECT * FROM staging.orders WHERE amount < 0",
    "all_orders_have_customer": """
        SELECT o.order_id FROM staging.orders o
        LEFT JOIN staging.customers c ON c.id = o.customer_id
        WHERE c.id IS NULL""",
}

@pytest.fixture(scope="module")
def con():
    c = duckdb.connect("warehouse.duckdb", read_only=True)
    yield c
    c.close()

@pytest.mark.parametrize("name", CHECKS)
def test_quality(con, name):
    bad = con.execute(CHECKS[name]).fetchall()
    assert bad == [], f"{name}: {len(bad)} violations, e.g. {bad[:3]}"

For unit tests of transformation logic, build tiny input tables in an in-memory DuckDB, run the SQL, and compare against expected rows:

Python
def test_dedupe_keeps_latest():
    con = duckdb.connect()
    con.execute("CREATE TABLE raw AS SELECT * FROM (VALUES (1,'old',1),(1,'new',2)) t(id, v, ts)")
    got = con.execute(open("sql/dedupe.sql").read()).fetchall()
    assert got == [(1, "new", 2)]

Because DuckDB starts instantly, hundreds of such tests run in seconds.

Where to run checks

  • After staging: block bad data from reaching marts.
  • After marts: catch logic errors and reconcile totals.
  • On a schedule: freshness and volume against the live warehouse.
  • Record results in a table (dq_results(run_at, check_name, violations)) so you can chart trends and alert on regressions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the check-as-query pattern, what does a returned row mean?

    Checks are written so a healthy dataset returns no rows.
  2. 2. Which query finds orphan foreign keys?

    Rows with no matching parent come out of a left join with NULL parent columns.
  3. 3. What does SUMMARIZE give you?

    SUMMARIZE profiles a table and is a quick way to spot suspicious columns.
  4. 4. Why test transformations with tiny in-memory tables?

    Small hand-made inputs make fast, precise unit tests for each SQL step.

Summary

  • Write checks as queries that return violating rows; fail the run when any appear.
  • Cover nulls, uniqueness, accepted values, ranges, foreign keys, freshness, volume and reconciliation.
  • Constraints enforce rules at write time; SUMMARIZE profiles unknown data.
  • Automate with pytest and keep results in a table to track trends.

Next: Project: Sales Analytics