Worked example

An analytics.orders table where every order needs an ID, a customer and an order time; IDs are unique; the status comes from a fixed list; quantities and amounts are never negative; orders are not dated in the future; and nothing ships before it was ordered:

order_id bigint
customer_id bigint
status text
quantity integer
amount numeric(12,2)
ordered_at timestamptz
shipped_at timestamptz

1. Run all checks at once

Download all .sql

Returns one row per check with the number of failing rows. Read-only: it changes nothing.

-- Data quality summary: one row per check; 0 failing rows means it passed.
-- Read-only: these queries do not change any data.
SELECT check_name, failing_rows
FROM (
    SELECT 1 AS check_no, 'not_null(order_id)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE order_id IS NULL) AS failing_rows
    UNION ALL
    SELECT 2 AS check_no, 'not_null(customer_id)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE customer_id IS NULL) AS failing_rows
    UNION ALL
    SELECT 3 AS check_no, 'not_null(ordered_at)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE ordered_at IS NULL) AS failing_rows
    UNION ALL
    SELECT 4 AS check_no, 'unique(order_id)' AS check_name,
           (SELECT coalesce(sum(n), 0)::bigint FROM (SELECT count(*) AS n FROM analytics.orders WHERE order_id IS NOT NULL GROUP BY order_id HAVING count(*) > 1) AS d) AS failing_rows
    UNION ALL
    SELECT 5 AS check_no, 'accepted(status)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE status IS NOT NULL AND status NOT IN ('pending', 'paid', 'shipped', 'cancelled')) AS failing_rows
    UNION ALL
    SELECT 6 AS check_no, 'non_negative(quantity)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE quantity < 0) AS failing_rows
    UNION ALL
    SELECT 7 AS check_no, 'non_negative(amount)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE amount < 0) AS failing_rows
    UNION ALL
    SELECT 8 AS check_no, 'not_future(ordered_at)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE ordered_at > now() + interval '5 minutes') AS failing_rows
    UNION ALL
    SELECT 9 AS check_no, 'ordered(shipped_at >= ordered_at)' AS check_name,
           (SELECT count(*) FROM analytics.orders WHERE shipped_at < ordered_at) AS failing_rows
) AS results
ORDER BY check_no;

2. Look at the failing rows

order_id is required

Rows where the value is missing (NULL). Empty strings are not NULL; add an accepted-values or custom check if those also count as missing.
```
SELECT *
FROM analytics.orders
WHERE order_id IS NULL
LIMIT 100;
```
customer_id is required

Rows where the value is missing (NULL). Empty strings are not NULL; add an accepted-values or custom check if those also count as missing.
```
SELECT *
FROM analytics.orders
WHERE customer_id IS NULL
LIMIT 100;
```
ordered_at is required

Rows where the value is missing (NULL). Empty strings are not NULL; add an accepted-values or custom check if those also count as missing.
```
SELECT *
FROM analytics.orders
WHERE ordered_at IS NULL
LIMIT 100;
```
order_id is unique

Key values that appear on more than one row, with how many rows share each. Rows with a NULL in the key are skipped, matching how a UNIQUE constraint treats NULLs.
```
SELECT order_id, count(*) AS duplicate_rows
FROM analytics.orders
WHERE order_id IS NOT NULL
GROUP BY order_id
HAVING count(*) > 1
ORDER BY duplicate_rows DESC
LIMIT 100;
```
status has an accepted value

Rows whose value is not one of the 4 accepted values. Matching is exact and case-sensitive; NULLs are left to the required check.
```
SELECT *
FROM analytics.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'cancelled')
LIMIT 100;
```
quantity is not negative

Rows with a value below zero. Zero and NULL pass.
```
SELECT *
FROM analytics.orders
WHERE quantity < 0
LIMIT 100;
```
amount is not negative

Rows with a value below zero. Zero and NULL pass.
```
SELECT *
FROM analytics.orders
WHERE amount < 0
LIMIT 100;
```
ordered_at is not in the future

Rows dated later than now plus 5 minutes of clock skew. A timestamp without time zone is compared in the session time zone.
```
SELECT *
FROM analytics.orders
WHERE ordered_at > now() + interval '5 minutes'
LIMIT 100;
```
shipped_at is on or after ordered_at

Rows where shipped_at is not on or after ordered_at. Rows where either is NULL pass (an unshipped order has no shipped date yet).
```
SELECT *
FROM analytics.orders
WHERE shipped_at < ordered_at
LIMIT 100;
```

3. Optional: enforce the rules as constraints

This changes your table. Unlike the checks above, these statements alter the schema and fail if existing rows break a rule. Run them only once the checks return 0, and in a maintenance window on large tables.

-- CHANGES THE TABLE. Run only after the checks above return 0, and in a
-- maintenance window on large tables (adding constraints scans and locks it).
ALTER TABLE analytics.orders ALTER COLUMN order_id SET NOT NULL;
ALTER TABLE analytics.orders ALTER COLUMN customer_id SET NOT NULL;
ALTER TABLE analytics.orders ALTER COLUMN ordered_at SET NOT NULL;
ALTER TABLE analytics.orders ADD CONSTRAINT orders_order_id_key UNIQUE (order_id);
ALTER TABLE analytics.orders ADD CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'));
ALTER TABLE analytics.orders ADD CONSTRAINT orders_quantity_check CHECK (quantity >= 0);
ALTER TABLE analytics.orders ADD CONSTRAINT orders_amount_check CHECK (amount >= 0);
ALTER TABLE analytics.orders ADD CONSTRAINT orders_shipped_at_ordered_at_check CHECK (shipped_at >= ordered_at);

ordered_at is not in the future: No constraint: the rule depends on the current time, so schedule this query instead.

Good to know

Each check scans the table. On very large tables, try them first on a sample by adding TABLESAMPLE SYSTEM (1) after the table name, then run them in full off-peak.

What each check means

Required: Finds rows where the column IS NULL. An empty string or a placeholder like 'N/A' is a value, not a NULL; catch those with an accepted-values rule or a custom check.
Unique: Groups by the key and returns every key value that appears more than once, with its count. Rows with a NULL in any key column are left out, because a PostgreSQL UNIQUE constraint treats NULLs as distinct (unless it is declared NULLS NOT DISTINCT).
Accepted values: Finds non-NULL values outside your list. Matching is exact and case-sensitive: 'Shipped' is not 'shipped'. Values are written as properly escaped SQL strings, so quotes in them are safe.
Not negative: Finds values below zero. Zero passes; so does NULL, which the required check covers.
Not in the future: For timestamps, finds values later than now() plus an allowance for clock skew between systems. For dates, values after current_date. Both depend on the session time zone, and timestamps without time zone are interpreted in it.
Date order: Finds rows where one date or timestamp comes before another, such as shipped before ordered. Rows where either value is NULL pass, because "not shipped yet" is not an error.

Checks versus constraints

A check is a query: it reads the table and reports problems, and you can run it as often as you like without changing anything. A constraint (NOT NULL, UNIQUE, CHECK) is part of the table definition: once added, the database rejects bad rows as they arrive.

The usual path is to run the checks, fix or quarantine the failing rows, then add constraints where the rule must always hold. Adding a constraint scans the table and takes locks, so on large tables plan it for a quiet period; a CHECK can be added NOT VALID first and validated later to shorten the lock. Rules that depend on the current time cannot be constraints, so keep those as scheduled checks.

How the SQL is kept safe

Table and column names are quoted when they need it (capitals, spaces, reserved words such as order), with embedded double quotes escaped. Names over PostgreSQL's 63-byte limit or with line breaks are rejected.
Accepted values become standard SQL string literals with single quotes doubled, so a value such as it's cannot end the string early.
Column types are only used to validate rules (you cannot ask a text column to be non-negative) and are never copied into the SQL.
The tool never connects to a database and runs nothing. Review the SQL before running it.

Limitations

PostgreSQL only. Other engines differ in quoting, time functions and how UNIQUE treats NULLs.
Each check scans the table once. On very large tables, add TABLESAMPLE SYSTEM (1) after the table name to try a 1% sample first.
Five rule types for now: no regular expressions, ranges, foreign-key or freshness checks yet.
Names must match your table exactly; the tool cannot see your schema.

Frequently asked questions

How do I run these checks every day?

Save the summary query and schedule it with whatever you already use: pg_cron inside PostgreSQL, a cron job with psql, an Airflow or Dagster task, or a dbt test. Alert when any failing_rows value is above zero.

Why are NULLs ignored by the unique check?

Because that is how PostgreSQL's UNIQUE constraint works: two NULLs are not considered equal, so they do not conflict. If NULLs must not repeat either, add a required check for the key columns, or use UNIQUE NULLS NOT DISTINCT (PostgreSQL 15 and later).

Can I use this with dbt or Great Expectations?

The rules map directly: required is not_null, unique is unique, accepted values is accepted_values. The generated SQL is a quick way to investigate failures or to run the same checks outside those frameworks.

Does the tool see my data?

No. It never connects to your database. It only sees the table and column names and the rules you type, uses them to write SQL, and keeps nothing afterwards.

SQL Data Quality Check Generator

Create a free account to use the generator