Testing Data Pipelines With Realistic Synthetic Fixtures

CUT Most inherited pipeline test suites fall into one of two camps: no tests at all, or a wall of tests that mock the warehouse so completely that every assertion checks whether the mock was called, not whether the SQL does what it claims. The second camp is worse than the first. A missing test suite at least announces its own risk; a suite full of mocked assertions announces false confidence, passes on every commit, and gives a team no warning before the incident that reveals how little it actually proved.

The confusion driving both failure modes is a conflation of two different kinds of check. One proves that a transform, given known input, produces the output the logic promises. The other proves that the data flowing through production actually matches what the transform assumes. Treating them as interchangeable is how a team ends up with a slow, bloated suite that still misses the incident that takes down a dashboard three weeks after a schema change nobody flagged.

What makes a fixture realistic enough to be trustworthy

Size is not the measure of a good fixture. A fixture earns trust by preserving three properties that real production data has and that hand-built test data almost never does on its own: referential integrity across tables, statistical fidelity to the real shape of the data, and coverage of the specific edge cases that have already caused problems. These three sit in a hierarchy, not a checklist, and the order determines which failures the next layer can even detect.

Referential integrity comes first because it's the precondition for everything downstream to mean anything. A missing parent record, an orphaned child row, or an ID field that's a string in one table and an integer in another is enough to make a join silently drop rows, and a test running against that fixture will pass while testing a join that was never actually exercised.

Distribution comes second, after integrity, because distribution problems only matter once the joins themselves are trustworthy. Real data clusters and skews in ways uniform test data never will, and a transform that only has ever seen tidy rows has never been tested against the conditions most likely to break it.

Edge-case coverage sits third, and teams are tempted to reach for it first because it feels concrete: the customer name with an emoji in it, the order with a null amount, the duplicate key from a past incident. The hierarchy exists because each layer depends on the one beneath it holding.

Where Faker and static SQL dumps fail

The two shortcuts teams reach for most often each solve part of this problem and break another part structurally, not because anyone applied them carelessly. What they don't do is understand relationships between tables. And because these tools default to producing a plausible-looking value for every field, they also skip the second layer entirely: every field comes back populated unless a developer explicitly coded it to be null, and every value is drawn to look individually reasonable, so distributions stay uniform and never skew the way real data does.

Static SQL dumps and straight copies of production data solve distribution completely, because the data is production data, by definition shaped exactly like production. But that fix creates two new problems of its own. The first is compliance. GDPR fines can run past €20 million, and a staging database that leaks real customer data is treated by regulators the same way a production leak would be. A dump of real records sitting in a test environment isn't a convenience; it's a liability waiting for the wrong person to find it.

Staleness is the second problem: schemas change, columns get added or renamed, and a static dump has no way to track that drift. It describes a version of the schema that existed at the moment of the snapshot, and as the pipeline evolves past that moment, tests built on the dump start passing against data the pipeline will never actually receive. Factories, by contrast, generate data against the current schema definition every time they run. A factory is a contract between the test suite and the application logic rather than a frozen photograph of a day that's already gone.

Separating I/O from transformation as the prerequisite for any fixture strategy to work

No fixture, however well built, fixes code that was never structured to accept one. The reason so much pipeline code resists testing isn't a tooling gap; it's that the transform logic and the I/O operations around it are tangled into a single function. A function that reads from a source, applies business logic, and writes to a destination in one body can't be tested against a fixture at all, because running it requires a live database connection no matter how good the test data is.

The fix is structural and applies regardless of language or framework: pull the transform out into a pure function. It takes a data frame as input and returns a data frame as output, with no database connection, no clock, no filesystem call buried inside it. Once that separation exists, a fixture can be handed directly to the function, and the test becomes a straightforward comparison between expected and actual output. This single move is what unlocks everything else in this piece. A factory composition pattern, where a parent record and its related child records are built together with consistent keys, only becomes useful once there's a pure function willing to accept that composed object as an argument rather than a tangled function demanding a live connection to feed it.

Building a fixture set that covers the hierarchy: integrity first, then distribution, then edge cases

With transforms separated from I/O, building a trustworthy fixture set becomes a three-step process, done in order, where each step depends on the one before it holding.

The first step is referential integrity, built through schema-driven factories. A factory should wire foreign keys at the moment it constructs a record, not assign IDs independently and hope they line up. Libraries that formalize this pattern, such as Fishery for JavaScript and TypeScript, or FactoryBot.js, remove the manual bookkeeping of tracking these relationships by hand.

The second step is distribution fidelity. Once the joins in a fixture are structurally sound, the values inside those rows need to reflect how real data actually clusters: null-heavy segments, days with zero transactions, timestamps that bunch around a DST boundary, amounts that sit at zero or go negative. None of that is visible in a fixture built from uniformly plausible values, so this step has to come after integrity rather than before it; a skewed distribution laid over broken joins just produces a different flavor of meaningless test. Platforms built for this problem, such as Tonic Fabricate, connect to live data sources and model generation on the actual statistical profile of that data rather than generating from scratch with uniform distributions; some enterprise-grade tools in this category report accuracy as high as 97.8% in preserving statistical relationships while still maintaining privacy protections on the underlying fields.

The third and final step is the committed ugly sample: a small set of real-shaped rows capturing the specific edge cases that have already caused incidents. The customer whose name has an emoji in it. The duplicate key from a past outage. These fields should be anonymized or fully synthetic where sensitive data is involved, since the goal is to preserve the shape of the anomaly rather than the real personal information attached to it, and these rows belong in version control alongside the code they test so they evolve with the schema instead of rotting the way a static dump would. Skipping straight to this step without the first two in place produces a fixture that happens to contain an interesting row sitting on top of a broken foundation, exercising the wrong code paths while looking, superficially, like thorough coverage.

Using DuckDB and property-based tests to pressure-test fixtures without a live warehouse

Building a good fixture set is only half the job; running it fast and systematically against real SQL matters just as much, and two tools do that work without requiring a live warehouse connection. DuckDB runs real SQL in-process, with no server to stand up, and its dialect is based closely enough on Postgres that the large majority of analytical SQL written against a Postgres-flavored warehouse runs in DuckDB unchanged. That makes it a genuine stand-in for a warehouse in CI, not a simulation of one. A mock can only confirm that it was called with the arguments a test expected. DuckDB runs the actual query and confirms the SQL itself is correct, which is a categorically different and more useful kind of proof.

Property-based testing fills a gap that even a well-built fixture leaves open. Tools in this category, such as Hypothesis in Python, generate inputs that satisfy a set of declared rules and then actively search for the inputs that break those rules. DuckDB and property-based testing aren't interchangeable, and one shouldn't be treated as a replacement for the other: DuckDB confirms that SQL behaves correctly against a known fixture, while property-based testing expands the space of inputs tested beyond anything a fixture, however carefully built, could anticipate on its own. Running both together, against a pipeline that starts from a clean, isolated data state on every run (through transaction rollback, database truncation, or containerized test databases seeded fresh each time) turns a fixture set from something fragile into something durable: fast enough to run on every commit, and thorough enough to catch the anomaly nobody wrote down.

The limits of fixture-based CI testing

A fixture-based test suite proves one thing clearly: that a pipeline handles the data it was shown. It cannot prove that the data the pipeline will actually receive in production conforms to any of the assumptions baked into that fixture, and that boundary marks exactly where a different kind of tool has to take over. A test that asserts revenue is never negative is a data quality check, and a data quality check belongs in production, running against live data on a schedule, with an alert attached to it. Running that same assertion in CI against a fixture proves only that the fixture itself contains no negative revenue values; it says nothing about what's flowing through the pipeline this morning.

This boundary matters most for systems of record, CRMs and RevOps tools in particular, where data keeps changing outside the pipeline's control. Contacts decay, fields go unpopulated over time, duplicates accumulate as records get created by different teams through different paths, and a pipeline's fixture-based assumptions go stale faster than any CI run will ever reveal. A test suite checks code against a snapshot; production keeps moving after that snapshot was taken.

AI workloads raise the cost of ignoring that boundary. Clean upstream data stops being a nice-to-have once a model is making decisions off it continuously, and becomes a prerequisite for the system to be trusted.

A practical fixture strategy for teams starting from a wall of mocks or no tests at all

Teams starting from the worst positions, either a wall of mocks that validate nothing about the SQL or no tests whatsoever, get the most improvement fastest from two specific moves: separating transforms from I/O, and committing a small, ugly fixture containing at least three rows drawn from real historical incidents.

The committed ugly sample should grow incrementally. Every production incident that surfaces a new edge case is an opportunity to add that specific row to the fixture and write the regression test that would have caught it the first time. Static SQL dumps, where they still exist, should be replaced by schema-driven factories on a rolling basis, and the replacement test is simple: the day a schema migration breaks a static dump but a factory keeps working, the dump has earned its removal.

For teams generating data at a larger scale, for AI model training, for load testing, or in regulated environments where production data can't be copied at all, AI-powered generation platforms that model production distributions while maintaining referential integrity across many connected databases are the right class of tool. They do at the scale of an entire data estate what a factory and a DuckDB test do at the scale of a single pipeline. Tonic Fabricate, for instance, offers agentic generation where a team describes the data it needs in plain language and gets back a referentially intact dataset, along with automated validation that checks the fidelity of the output without requiring every field to be tuned by hand. ING Belgium used the Synthetic Data Vault to generate thousands of synthetic payment records in two minutes, which let the bank expand test coverage for its SEPA payment processing system by a large multiple while cutting the manual effort that coverage used to require. GoDaddy reported a 90% reduction in the time spent creating test data after adopting a synthetic data generator, and eliminated the use of production data in test environments.

None of this replaces production monitoring, and no fixture strategy, however disciplined, changes that. Fixture quality determines how much a team can trust its CI suite. Production data quality determines how much it can trust what the pipeline actually outputs once it's running. Those are two different problems, they require two different sets of tools, and solving one well does nothing to solve the other.