It passed every constraint. It was still wrong.

My first generated test database loaded without a single error. Every foreign key resolved. Row counts matched. The first twenty rows of every table looked right: sensible names, dates and prices.

Then I asked it one question: how many cancelled orders have a shipment?

The answer was not zero.

No constraint had complained, because no constraint could. This post is the method I've used since to prove a multi-table dataset is consistent. It needs nothing but SQL, and it works on any data: generated, sampled from production, or produced by a pipeline you're testing.

Quick quiz before you scroll

Which of these can a FOREIGN KEY or CHECK constraint catch, without triggers?

1. An order item points at an order that doesn't exist.
2. A line total isn't quantity × unit price.
3. An order's subtotal isn't the sum of its line totals.
4. A cancelled order has a shipment.
5. An order is dated before its customer signed up.

Keep your answer in mind. It's further down.

The method: every rule is a query that counts violations

Write each rule as a sentence. Then write one query that returns the number of rows breaking it. The rule holds when the query returns 0.

That's the whole idea, and it gives you three things:

1. A number, not an opinion. "Looks fine" becomes 0 or 4,317.
2. A pointer to the problem. Change COUNT(*) to * and you're looking at the offending rows.
3. Documentation. The list of rules says what the data promises, and anyone can check it.

Here it is on a seven-table shop database: customers, products, orders, order items, payments, shipments and returns.

Layer 1: keys

-- every order_items.order_id exists in orders
SELECT COUNT(*)
FROM order_items c
LEFT JOIN orders p ON p.order_id = c.order_id
WHERE c.order_id IS NOT NULL AND p.order_id IS NULL;

Your database may enforce this already. Check anyway: test data is often loaded with constraints switched off, or into a warehouse that doesn't enforce them.

Layer 2: arithmetic inside a row

-- line_total = quantity x unit_price
SELECT COUNT(*)
FROM order_items
WHERE ABS(line_total - quantity * unit_price) > 0.011;

The tolerance is deliberate. Comparing rounded money with = reports false failures of one cent.

Layer 3: arithmetic across tables

-- an order's subtotal = the sum of its line totals
SELECT COUNT(*)
FROM orders o
JOIN (SELECT order_id, SUM(line_total) AS s
      FROM order_items GROUP BY order_id) x USING (order_id)
WHERE ABS(o.subtotal - x.s) > 0.011;

One level up, the payment equals the order total:

-- the amount paid = the order total
SELECT COUNT(*)
FROM payments p JOIN orders o USING (order_id)
WHERE ABS(p.amount - o.total_amount) > 0.011;

Layer 4: existence rules (the ones that bit me)

"Only when" and "exactly one" each need two queries, one per direction:

-- shipments exist only for shipped, delivered or returned orders
SELECT COUNT(*)
FROM shipments s JOIN orders o USING (order_id)
WHERE o.status NOT IN ('shipped', 'delivered', 'returned');

-- and every such order has exactly one shipment
SELECT COUNT(*)
FROM orders o
WHERE o.status IN ('shipped', 'delivered', 'returned')
  AND (SELECT COUNT(*) FROM shipments s WHERE s.order_id = o.order_id) <> 1;

The first finds shipments that shouldn't exist. The second finds orders missing one, or with two. A dataset can pass either query and fail the other.

Layer 5: time

-- an order is never placed before its customer signed up
SELECT COUNT(*)
FROM orders o JOIN customers c USING (customer_id)
WHERE date(o.order_date) < date(c.signup_date);

Also check the edges of the period. If the data should end on 30 September, count the rows dated after it. In generated data this fails more often than anything else: a date built as "parent date plus a few days" doesn't know where the period ends.

Layer 6: rules about the whole table

Some rules describe a shape, not a row: "about 1.5% of transactions are fraud", "orders peak in November". They still fit the pattern by returning 0 or 1:

-- the fraud rate is between 1% and 2%
SELECT CASE WHEN AVG(is_fraud) BETWEEN 0.01 AND 0.02 THEN 0 ELSE 1 END
FROM transactions;

Quiz answer

Only 1 (a foreign key) and 2 (a CHECK on the row) can be enforced by constraints. Numbers 3, 4 and 5 compare rows across tables, and plain constraints can't express that. Those are the rules that break dashboards, fail tests that aren't the code's fault, and make the numbers not add up in a demo.

How many did you get right? Tell me in the comments.

Running them: a 16-line runner

Keep every check in one checks.sql file: a -- comment with the rule, then its query. This runs them all against SQLite and exits with an error if any rule fails, so it can run in CI:

import sqlite3
import sys

con = sqlite3.connect(sys.argv[1])
rule, sql, failed = "", "", 0
for line in open(sys.argv[2], encoding="utf-8"):
    if line.startswith("--"):
        rule = line.strip().lstrip("- ")
        continue
    sql += line
    if line.rstrip().endswith(";"):
        n = con.execute(sql).fetchone()[0]
        print(f"{'ok  ' if n == 0 else 'FAIL'} {n:>9,}  {rule}")
        failed += n != 0
        sql = ""
sys.exit(1 if failed else 0)

python run_checks.py shop.db checks.sql prints something like:

ok           0  every order_items.order_id exists in orders
FAIL         1  line_total = quantity x unit_price
ok           0  an order's subtotal = the sum of its line totals
FAIL         1  shipments exist only for shipped, delivered or returned orders

For PostgreSQL, swap sqlite3 for psycopg (version 3) and change the connect line. The queries run as they are.

Two practical notes:

• Index the key columns first on big tables. The "exactly one" queries crawl without them.
• Start with five rules, not fifty. Keys, one arithmetic rule, one existence rule, one time rule and the period edges find most problems.

What the checks found in my own data

I build GenerateRealData, a tool that generates related test databases from a description or a SQL schema. So I badly wanted these numbers to be zero. On the first full-size runs, they weren't. Every one of these looked fine by eye and was caught only by a query:

• A products table with 360 rows where 1,500 were planned. A unique name column ran out of distinct values, and the table was silently capped.
• A table of eight pricing plans that came out with one row. Every subscription pointed at the same plan. The check that found it was the simplest in the file: count the rows and compare with eight.
• Orders dated after the period ended, pushed there by a rule that moved a date to follow its parent.
• Duplicate emails in a column declared unique, in a dataset big enough for random names to collide.

Each became a fix, and the checks are how I know the fixes held. Today the shop database passes all 48 of its rules:

The same queries measure dirt

Point the checks at data you know is broken and they tell you how broken. I made a shop dataset with defects planted on purpose, for cleaning practice. Running the clean version's checks against it gives:

Rule	Rows breaking it
line_total = quantity x unit_price	6,400
order total = sum of its lines	2,149
order country is the customer's country	1,306
order date is never before the customer's signup	472
email is unique	213

That table is a data-quality report, made with queries you already had.

What this doesn't do

• It only checks rules you thought of. The cancelled-order rule exists because I got bitten. A rule nobody writes down is never tested.
• It doesn't judge realism. Data can pass every check and still be flat: the same return rate in every category, customers who reorder like clockwork. Consistency and realism are different questions.
• It overlaps with data-testing frameworks. They add scheduling, reports and alerts around the same idea. Plain queries are the version with zero setup, which is why I start there.

Try it yourself

• The challenge: the dirty e-commerce dataset has 11,577 planted defects and an answer key that lists every one. Write your checks, run them, and post how many you caught.
• Templates: every dataset in github.com/generaterealdata/synthetic-datasets comes with its checks.sql (19 to 48 rules each). Copy one as a starting point for your own tables.

Your turn: what rule has bitten you that no constraint could catch? Drop it in the comments. I'll collect the best ones into a follow-up post, with the query for each.

Next in this series: running hundreds of these checks on millions of generated rows, and what they caught. Follow if you want it in your feed.

---

Comments

[ontomato]

Yeah the cancelled order with a shipment is Exactly the kind of thing that passes every constraint and still breaks a dashboard.

From my experience schema checks aren't enough for chat with data either. What worked for me was writing those cross table rules as named business objects with locked grain, then making the answer point at the source rows that broke the rule. A fluent total that never opens the bad shipments is the scary case.

[GenerateRealData Team GenerateRealData]

I love the idea of locking the grain with named business objects. It's actually the exact same philosophy we had to adopt to generate the synthetic data—you have to bake those cross-table semantic rules directly into the generation pipeline, otherwise the engine just pumps out the exact dashboard-breaking anomalies we're trying to test for.

Thanks for your valuable comment 😁

[ontomato]

Glad that landed. To be honest baking those cross table rules into generation is Exactly the same move as locking grain on named objects for answers. From my experience if the generator and the dashboard both open the Same definition, you stop inventing the anomalies you're trying to catch. One of our users saw cleaner test data once those rules lived Outside the model as named objects with grain.