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

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:

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.