Four Ways Generated Test Data Stops Being Test Data

Test data that will not load is a good outcome. The import fails, you see it in the first minute, you fix it.

The dangerous outcome is the data that loads perfectly. The right number of rows, the right column names, the right file size, no warnings from anything. And every query your load test runs against it takes a path your production database will never take.

I found four of these in my own dataset catalogue. All four produced files that passed every check I had, and every generation job reported success.

None of them is specific to any tool. Whether you generate data with a script, mask a production extract, or use a product, you can hit all four. Here they are, with the check for each.

1. Foreign keys that are present but empty

The column exists. It has the right name and the right type. It is null in every single row.

This was mine, and it was total: orders.user_id, order_items.order_id, order_items.product_id, products.category_id, reviews.user_id. Blank in 100% of rows, across 4,277 published files.

What that does to a load test is worse than it first sounds. A join against an all-null column returns no rows, so the query is the fastest it will ever be — you have benchmarked an empty result set. And because the planner works from column statistics, a column holding one distinct value (null) produces cardinality estimates that bear no relation to production. You did not measure a slow join. You measured the absence of one, and then wrote the number down.

The cause is worth more than the symptom. The code that builds relationships was correct, and had passing referential-integrity tests. The pipeline that builds the catalogue never called it. It looped over tables one at a time, each with its own generation context, so every child looked for its parent’s IDs in a pool that was empty, got nothing back, and wrote a blank column. Tested code that production bypassed.

The check. Take the parent and child files you actually load, and count the child rows whose foreign key finds a match:

SELECT count(*) AS total,
       count(p.id) AS matched
FROM order_items c
LEFT JOIN orders p ON p.id = c.order_id;

matched should equal total, less exactly the number of nulls you deliberately asked for. If matched comes back zero, your load test has never once exercised that join.

2. Keys that can never match, because they are not the same thing

This one is nastier than the first, because the column is full. Every row has a plausible-looking value in it. None of them points anywhere.

Two versions, and I shipped both.

Wrong range. enrollments.student_id was an integer drawn from 1 to 50,000. The students table it referred to held 1,000 rows. About 98% of enrolments pointed at students who did not exist — and at no size did the two line up, because the range was hardcoded and the row count was not.

Wrong type. The same students table also had its own student_id, a formatted sequence rendering as STU00000560. So anyone joining enrolments to students on the obvious column was comparing an integer to a string, and would never match a single row at any size, in any direction, forever.

Both look completely fine in a preview. Both survive a schema check, because the types are internally consistent within each file. The mismatch only exists between two files, and nothing was looking between two files.

The check. Do not eyeball the column — join it, using the query above. Then check the types on both sides of every join your application performs. A join key that is an integer on one side and a formatted string on the other is not an edge case; it is what happens whenever a table has both a surrogate id and a business key, and the child references the wrong one.

3. Values that do not reconcile with each other

Two of these, found by looking at the first row of the flagship dataset rather than at the logs.

The en_GB catalogue held AUD, CAD, EUR and USD in roughly equal measure — currency was drawn at random and never consulted the locale, so the locale dimension meant nothing for anything monetary. Separately, price and cost were independent draws, which put 23% of products below cost.

For a load test the first one matters more than it looks. If your application filters or groups by currency, the selectivity of that column determines the plan, and a column that is 20% GBP behaves nothing like production data that is 98% GBP. Index or sequential scan, hash or nested loop — that choice gets made on the distribution you fed it.

The second matters for a different reason. Data that is visibly absurd to a human is data nobody trusts, so nobody looks at it, so the real failures hide in it undisturbed.

The check. Run the aggregate your application runs, over the whole file rather than a sample:

SELECT count(*) FROM products WHERE cost > price;

SELECT currency, count(*)
FROM products
GROUP BY currency
ORDER BY 2 DESC;

You are checking that the distributions are the shape of production, not merely that the columns are populated.

4. The table that quietly was not written at all

Unique columns are usually generated by rejection sampling: draw a value, keep it if it is new, try again if it is not. That cannot beat the pigeonhole principle. Faker’s username vocabulary is far smaller than 100,000, so at 100,000 rows every candidate collided, and generation gave up after a thousand attempts and raised.

The pipeline caught the exception, logged it, and carried on — without writing that table.

The users table is a parent. Losing it empties its ID pool, so every child’s foreign key comes out blank, which is defect one arriving again by a completely different route. The job exited zero. The output directory had one fewer file in it than it should have, and nothing said so.

This is the one of the four that never reached anybody, and the reason it did not is worth more than the defect. It only bites when one process has to produce every unique value on its own — and generation was single-threaded because of the fix for defect one. Sharing an ID pool across parallel workers is not possible, so referential integrity cost me the parallelism that had been hiding the collision. Fixing the first defect is what would have introduced the fourth.

I caught it before it shipped by checking the live 100,000-row file for duplicates rather than trusting that the fix was an improvement. That is the habit worth stealing: a correctness fix changes the conditions the rest of the pipeline runs under, and the thing it breaks will not be the thing you were fixing.

The check. Count the files you got against the files you asked for, and the rows in each against the size you requested. Then re-run check one, because a missing parent and a broken relationship look identical downstream.

Why these are all the same bug

Every one of them produced a file that was the right shape and the wrong content, and that combination defeats the checks people actually run.

Schema validation passes: the columns are there, correctly named and correctly typed. Row counts pass, except in the fourth case, where nobody was counting. A smoke query passes, because SELECT * FROM orders LIMIT 10 returns ten perfectly convincing orders. The generation job exits zero and reports success.

What none of those do is compare one file against another. All four defects live in the relationship between files — and that relationship is exactly what a load test spends most of its time exercising, and exactly what a per-file check cannot see.

A job that reports success tells you nothing about what it wrote. Verify the artefact, not the exit code.

The ten-minute version

If you do one thing from this post, do this. Take the parent and child files you actually load into your test environment. Count the child rows whose foreign key matches a row in the parent, and divide.

It should be 100%, less any nulls you asked for. If it is 0%, none of your recorded response times for any query involving that join describe anything your application will ever do in production.

How I know

I found all four in my own product, which is sold on being ready-made relational data — and none of it related to anything. That is the least comfortable sentence in this post and the reason the rest of it is specific.

Three of the four were in data people had already downloaded. The fourth, as above, was caught on the way past.

They are fixed, and the catalogue regenerated. The part I would keep if I could keep only one thing is not the fix; it is that the proof is now a count, and a count taken from the published files rather than from the job that wrote them.

Reading the shipped data off disk: 59 declared relationships, 56,668 references at the 1,000-row tier and 5,669,913 at 100,000 rows, none dangling, in every locale I checked. Every null is a declared null probability. Currency follows locale — 100,000 of 100,000 GBP in the en_GB catalogue, where it used to be four currencies in roughly equal measure — and no product costs more than it sells for, where 23% used to.

I could not have written that paragraph from the generation log, because the generation log said everything was fine throughout the entire period in which nothing was.

The tests were not missing, either. The relational code had passing integrity tests before any of this happened. What was missing was a test that ran the entry point customers actually run — that function was referenced nowhere in the suite, which is exactly why this shipped and stayed shipped.

If you want data that already joins

DummyDataGenPro generates related datasets across eleven domains, with the relationships declared rather than hoped for, and the free tier goes up to 10,000 rows.

I would rather you ran the check above on whatever you are using now. If it comes back at 100%, you have lost ten minutes and gained a number you can point at. If it comes back at 0%, you have just found out that a set of results you have been trusting describes a query plan that has never run.