Synthetic Test Data: The Values Were Never the Hard Part
By Mikhail Shytsko, Founder at Seedfast ·
Synthetic test data generation is the process of producing database records from a schema or a description instead of copying them from production. For a relational database that means two jobs at once — inventing column values that read like the real thing, and arranging those values into rows that satisfy the schema's foreign keys and constraints, so the result both looks plausible and inserts without error. The first job makes a good demo. Whether the data is usable comes down to the second.
TL;DR: Synthetic test data generation splits into two problems of very unequal difficulty. Producing believable column values like names and amounts is the easy part, handled well by faker libraries, distribution samplers, and language models alike. Producing rows that satisfy the schema's foreign keys and constraints, and keep satisfying them after the next migration, is the part that separates usable data from data that merely looks right.
- Value generation and structural validity are separate problems; most tools solve the first well and quietly hand you the second
- Four approaches make up the generation spectrum: rule-based value synthesis, distribution-aware generation, LLM-based generation, and schema-aware generation, differing mainly in how much of the schema they actually read
- Even data that inserts cleanly can hide the bugs that only production-shaped skew surfaces, like a query plan that flips once a column stops being uniform
- Schema drift is the quiet failure, the data generated against last month's schema or by a generator whose column config predates the last migration
- Where you run generation changes what you need from it, since a quick local reseed asks far less than an unattended CI step that has to fail loudly
Every method of generating synthetic test data answers one question (where do the values come from) and quietly ducks a second: does anything make the rows connect? Sorting the approaches by how they handle both is more useful than ranking them, because each is genuinely the right tool somewhere.
Rule-based value synthesis is the floor, and the usual first stop. A library like Faker maps each column to its own provider and calls it once per field, independently. Run two loops, one for users and one for orders, and you get tables that each look right on their own:
// Two independent Faker passes: every value is plausible, the link is fiction
const users = Array.from({ length: 5 }, (_, i) => ({
id: i + 1,
email: faker.internet.email(),
}));
const orders = Array.from({ length: 20 }, () => ({
id: faker.string.uuid(),
user_id: faker.number.int({ min: 1, max: 1000 }), // references nothing in particular
total: faker.commerce.price(),
}));
Every user_id is a well-formed integer. Almost none of them point at one of the five users that exist. Nothing looks wrong until a real REFERENCES users(id) stands between the row and the table, at which point the first orphaned insert aborts and the seed script dies half-finished. Faker did exactly what it was asked to do — generate values — and never saw the question that actually mattered.
Distribution-aware generation keeps the field-at-a-time model and fixes what the values look like in aggregate. Rather than spreading an amount column evenly across a range, it samples from a shape that resembles real money, a long tail of small values beneath a few large ones, and clusters timestamps the way real activity clusters. This matters because your database reads those shapes. The planner picks a different plan against skewed data than against a uniform smear, and any code that ranks or thresholds behaves differently too. For the specific distributions and why they bite, see realistic test data.
LLM-based generation raises the ceiling on individual values. Ask a model for a product description and it writes one that fits the product rather than lorem ipsum; ask for a support-ticket body and it reads like a person typed it. Semantic coherence inside a single value is what language models are unusually good at. Their limit is memory. A model works only from the schema you paste in, and a wide one with all its keys and constraints stops fitting in that window, so by the time it reaches the tables deep in the dependency chain it has lost track of what it set up at the top. The values come out excellent and the structure drifts, which is why the durable pattern keeps the model on values and hands the connecting work to deterministic code. For which AI-based tool does that best, the best AI test data generator comparison ranks them.
Schema-aware generation answers the second question head-on. Before it produces a value, the generator reads the target schema (the tables, the column types, the foreign keys) and writes rows that fit those relationships by construction rather than by luck. The values can still come from any approach above, a language model included; what changes is that the structure is read from the live schema instead of assumed. It is the one point on the spectrum where a child row is guaranteed to reference a parent that exists, because the generator knew the reference was there before writing the row.
Line the four up and the shape of the problem is hard to miss. Generating values — the plausible names, the believable amounts — is the easy 80% of the work, and every approach handles it competently. The other 20%, making a row satisfy the schema's foreign keys and constraints so the database accepts it on insert, is what separates data you can test against from data that only survives in a CSV. A generator that produces flawless values and orphaned references hasn't done the hard part. It skipped it.
The failures cluster into four kinds, and a project tends to meet them in that order.
Broken references are the first and loudest failure. A row points at a parent that was never inserted, the constraint rejects it, and the whole load halts. It is the orphaned-key problem from the Faker snippet scaled to a real schema, and past a handful of related tables, keeping every reference valid by hand stops being something a person does reliably. The database has a name for what it is enforcing, referential integrity, and clearing it is the floor beneath everything else a generator claims to do.
Flatter and quieter is the distribution failure, where the data inserts fine and the suite stays green. Uniformly random values give a column a shape no real column has, and that shape leaks downstream. The planner reads its statistics off it and picks a plan it would never choose against skewed production data, while a load test on the same flat spread exercises none of the paths real skew would light up. Small uniform datasets are where the bugs that only appear once the data has real volume and real shape go to hide.
The third failure arrives with elapsed time. Schema drift is data generated against last month's schema (or by a generator whose column mapping predates the last migration), and it is wrong in the way that is hardest to spot: a NOT NULL column lands on Tuesday, and by Thursday every seed either errors outright or quietly fills that column with a value that means nothing. A generator that reads the schema once and caches it carries this risk for good; one that reads it fresh on every run never has it.
That leaves volume, the mildest miss and still a routine one. Ten rows prove the code runs and say nothing about how it behaves once pagination turns slow or an unindexed query drifts toward a timeout. More rows alone will not fix it, though, since they have to carry the right shape to be worth anything, which lands right back on the distribution problem a couple of paragraphs up.
The same generator gets pointed at very different jobs, and each one asks something slightly different of it.
The tightest loop is local development. You have changed a model, you want a database that reflects it, and you want it now. Speed matters more than polish here; the data can be rough as long as it is valid. One step out is standing up a fresh environment, a new clone or a preview database spun up for a pull request. Both start empty and want a believable dataset fast, without a copy of production and the questions that come with it. A staging environment filled with generated data instead of a masked dump sits outside the production-access conversation entirely, which is often the real reason a team generates rather than copies.
Furthest out is CI, where the generator stops being interactive and has to run unattended on every push, fail loudly when it fails, and cost the same whether it fires once a day or eighty times across a job matrix. Synthetic data for CI/CD is its own topic with its own constraints. Across all three, the through-line is simple. The further from your keyboard the generation runs, the less you are around when it breaks, and a generator that reads the current schema and produces valid rows without a human watching is the only kind that survives that.
Seedfast is the schema-aware end of that spectrum, packaged as a tool you run. Point it at a live PostgreSQL database and describe the data you want in plain English; it reads the current schema and writes valid, connected rows that fit it:
seedfast seed --scope "500 customers with orders, payments, and a realistic spread of account ages"
That run answers the spectrum's second question on one screen. The row counts fan out the way a real schema demands, five hundred users carrying twelve hundred orders and twice that many order items, every one of them resolving. There is no seed file to maintain and no column mapping to keep current, because the schema read happens every run. Add a table in next week's migration and the following seed picks it up with nothing to reconfigure. The scope is a sentence, so the same command seeds a handful of rows for a unit test or a hundred thousand for a load test depending only on how you phrase it, not on any code you rewrite.
Because the rows are invented from the schema and never sampled from production, no real customer record is involved at any point, which is what lets a generated staging or preview environment stay clear of the masking and PII questions a production copy drags along. That is a side effect of generating rather than copying, not a compliance product in its own right.
The 30-day free trial needs no card and covers up to 50 tables and 25 seeds, with flat plans after that. Run your first seed takes a couple of minutes, or read the pricing first.
No. The oldest form of it, rule-based value synthesis of the kind Faker does, predates modern language models and produces correctly typed values with no model in the loop. A model earns its place on one narrow axis, the semantic realism of an individual value, so a product description matches its category instead of reading as filler. The structural side stays deterministic either way. If your real question is which AI-based tool does this best, the best AI test data generator comparison sorts them out.
Use a generator that reads the schema's relationships before it writes any rows, not one that fills columns in isolation and hopes the numbers line up. What catches people is a table that references itself, or two tables that reference each other, because there is no plain parent-first order for those; a generator has to write one side with the link left empty and fill it in a second pass, or lean on a constraint the database checks only at commit. Faker and web-based column tools leave that to you; schema-aware generators fold it into the read. The property being protected is referential integrity.
Yes, by feeding the generator a static description of the schema instead: a schema.sql, a set of migration files, or an ORM's model definitions. That helps in a locked-down sandbox where nothing reaches the database directly. The tradeoff is drift. A static file is only as current as the last export, so anything generated from it inherits whatever the file has fallen behind on. Reading the live catalog at run time removes that gap, which is why connection-based generation is the default when the data has to match the tables as they stand now.
Partly, and the deterministic half is the structure. A schema-aware generator produces the same shape of valid, connected rows on every run because the schema fixes that; the values riding on top are another matter. Rule-based libraries can reproduce a run exactly when you fix their random seed (Faker and drizzle-seed both expose one), while model-generated values make no byte-identical promise at all. Treat exact-value repeatability as a separate requirement from structural validity, and check which of the two your tests actually need before you shop for it.
Whatever approach you reach for, try it on a schema that has real foreign keys, not the single flat table the demos lean on. Two tools can produce output that looks equally clean and behave nothing alike the moment a child row has to resolve to a parent, where one completes the load and the other stops on the first orphan. Watching which one happens tells you, faster than any feature list will, whether a generator does the whole job or only the easy part of it.
- Test Data Generation: 7 Ways to Fill Your Database — the methods hub, where synthetic generation sits next to SQL files, factories, dumps, and masking
- Best AI Test Data Generator — the tool-shopping counterpart, for picking a product once you have chosen the approach
- Synthetic Data for CI/CD — running generation unattended inside the pipeline, with exit codes and per-run cost
- Realistic Test Data: Beyond Random Values — the deep version of the distribution and coherence problem this page only samples
- Referential Integrity — the constraint under "broken references," explained on its own terms
- Get started with Seedfast — connect a PostgreSQL database and generate your first valid, connected dataset