DEV Community: GenerateRealData Team

377 SQL checks on 4.6 million generated rows: what they caught

GenerateRealData Team — Thu, 05 Nov 2026 14:40:21 +0000

In part 1 I showed the method: write every rule about your data as a sentence, then one SQL query that counts the rows breaking it. The rule holds when the query returns 0.

I promised to run it at scale. So I did: 377 checks across 13 generated databases, 4,594,497 rows in total. Today every one of those queries returns 0. Getting there took several fixes, and this post covers the ones I'd never have found by looking at the data.

The setup

Thirteen databases, each generated from a schema plus rules, each with a checks.sql file holding one query per rule:

Dataset	Tables	Rows	Rules
E-commerce database	7	499,385	48
Auto insurance policies and claims	5	192,843	35
Hospital records	6	300,000	32
SaaS subscriptions and churn	6	249,113	31
HR, workforce and attrition	6	155,186	31
App users with edge cases	5	200,000	30
Fleet GPS tracking	4	404,052	30
Card transactions with fraud labels	5	500,000	29
AI jobs and salaries	3	510,000	26
Web analytics funnel events	4	425,918	23
Black Friday and Cyber Week sales	1	408,000	23
Smart meter readings	3	500,000	20
Dirty e-commerce data	4	250,000	19

The rules are the same kinds as in part 1: keys, arithmetic inside a row, arithmetic across tables, "only when" existence rules, time order, and a few rules about the shape of a whole table. A runner executes the file and prints one line per rule:

python verify.py ecommerce ecommerce.sqlite
...
48 of 48 rules hold.

All 13 databases, their check files and the runner are public, so you don't have to take my word for any of this.

What the checks caught

1. Half the cancellations happened on the last day

The SaaS database covers a period ending 30 September. The first full run passed every per-row rule: no subscription ended before it started or after 30 September, and every cancelled subscription had an end date.

Then I counted cancellations per day:

SELECT ended_on AS day, COUNT(*) AS cancellations
FROM subscriptions
WHERE status = 'cancelled'
GROUP BY day
ORDER BY cancellations DESC
LIMIT 3;

1,718 of the 3,015 cancellations, 57%, were dated 30 September. The HR database had the same problem: 1,242 of 2,998 leavers, 41%, left on the last day.

The cause was a rule doing exactly what it said. End dates were drawn after the start date, then capped at the end of the period. Every date that would have fallen after 30 September was moved onto 30 September. Each row was valid. The table as a whole was nonsense, and any churn chart built on it would show a cliff on the final day.

The fix was to draw end dates inside the period instead of capping them afterwards. The lesson for the check file: per-row rules can't see a pile-up. Add one "shape" check for any date that's computed from another date: the busiest day shouldn't hold a big share of the rows.

2. A shop with one product

The engine sizes tables relative to each other: ask for a large dataset and the products table gets hundreds of rows. Ask for a small one, as a quick demo does at 100 rows, and the same proportions gave the shop one product. Every order bought the same thing.

Nothing was broken in the per-row sense. Keys resolved and totals added up. I only noticed when I looked at small samples, and that's the point: a check of the form "the products table has at least N rows" would have caught it on the first run. The fix was a minimum row count per table, so small runs keep a believable catalogue.

3. A column whose name started with "check"

Not every catch was in the data. While building a health dataset, the schema reader met a column called checkup_id and treated the line as a CHECK constraint, because it starts with the word check.

No query on the data would explain that, but the habit is the same one: write the rule down and test it. "Every declared column reaches the generated table" is now a test that runs with every build.

Consistency isn't realism

377 zeros prove the data is consistent. They don't prove it's interesting. A table where every customer returns exactly 21% of orders would pass every check and teach nothing.

So each dataset also gets a realism test that fits its purpose, and the number goes in its data card:

Black Friday sales: a gradient-boosting model predicts which purchases are returned with ROC AUC 0.74 on held-out rows. Learnable, not trivial.
AI jobs and salaries: a gradient-boosting salary model reaches R² 0.86 on held-out rows.

And each card has a Good to know section listing what the data does not model. For example, in the Black Friday set customer_id isn't a consistent customer, so per-customer features won't work. Writing those down is part of the method too: a limit you state can't mislead anyone.

How the data was made

I build the tool that generated these datasets, GenerateRealData, so here's what it does, briefly:

Relational by design. It generates whole multi-table databases in dependency order, so every foreign key points at a row that exists, and totals, balances and dates agree across tables.
Rules in plain terms. "A cancelled order has no shipment" or "discharge comes after admission" are kept on every row, at any size.
A large type library. 232 data types and 222 built-in generators, one of the largest libraries of any test data generator: real airport, ICD-10 and MCC codes, VINs, IBANs and card numbers with valid check digits, real cities with matching postcodes and time zones. Over 200 automatic links keep fields in a row consistent: the city sits in the row's country, the email follows the name.
Scale. Up to 500,000 rows in one run. The AI jobs dataset, 510,000 rows, came from a single API request in about three minutes.
Your schema or a description. Upload CREATE TABLE statements for PostgreSQL, MySQL, SQL Server or SQLite, or describe the database in plain English. Download as a SQL script, CSV, Excel or JSON.

The checks in this post are how I hold it to that.

Run the checks yourself

Every dataset with its checks.sql: github.com/generaterealdata/synthetic-datasets. Download a database from its Kaggle link and run python verify.py <dataset> <file>.
The full data: all 13 are free on Kaggle under CC BY 4.0.
The challenge from part 1 is still open: the dirty e-commerce dataset has 11,577 planted defects and an answer key. Write checks, run them, and post your score.

Your turn: which shape check would you add to your own data? A last-day pile-up was mine. Tell me yours in the comments. Next in the series I'll collect readers' rules, the ones no constraint could catch, with a query for each.

It passed every constraint. It was still wrong.

GenerateRealData Team — Thu, 29 Oct 2026 12:58:15 +0000

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?

An order item points at an order that doesn't exist.
A line total isn't quantity × unit price.
An order's subtotal isn't the sum of its line totals.
A cancelled order has a shipment.
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:

A number, not an opinion. "Looks fine" becomes 0 or 4,317.
A pointer to the problem. Change COUNT(*) to * and you're looking at the offending rows.
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.