Test Data Management Strategies

Test data advice usually arrives as a list of six strategies with their pros and cons, which leaves you exactly where you started: holding six options and no way to choose. There are only two questions, and they decide it per test.

Tolvern Freight's API suite took fourteen minutes. It truncated every table between every test — a strategy chosen once, early, applied to all 340 tests, and never revisited. It worked, in the sense that tests didn't interfere with each other. It just cost fourteen minutes a run, several times a day, for two years.

The fix wasn't a faster truncation. It was noticing that about eight tests in ten had no need for it at all.

The strategies aren't a menu, they're an output

Per-test creation, factories, transaction rollback, truncation, fresh database, pre-loaded fixtures — these get presented as alternatives to weigh. They aren't. Each is the right answer to a specific pair of conditions, and you can determine which pair applies by asking a test two questions:

1. Does this test need to see data it didn't create? Reference data, lookup tables, a seeded admin account, a rate card that lives in migrations.

2. Can this test run inside a transaction that gets rolled back? Which is really: does anything in the path under test commit, open its own connection, or hand work to another process?

Two binary questions, four cells:

	Can roll back	Cannot roll back
Creates all its own data	Transaction per test. Fastest by a wide margin. This is the default and should be most of your suite.	Per-test creation + targeted cleanup. Truncate only the tables you touched, not all of them.
Needs pre-existing data	Transaction per test over a migrated database. Reference data lives in migrations and is never cleaned up, because it's never modified.	Template database, cloned per test. The expensive cell. Reserve it for tests that genuinely earn it.

Tolvern's fourteen minutes came from putting all 340 tests in the bottom-right cell when about 270 of them belonged in the top-left. Same isolation guarantee; a fraction of the cost.

The test you can run: take the last test you wrote and answer both questions. If you can't answer the second one without reading the application code, that's the finding — the strategy your suite uses was picked for the hardest test in it and then applied to all the others.

The rollback question is the one people get wrong

Question 1 is easy — you can see what a test needs. Question 2 is where suites end up in the wrong cell, because "can this roll back?" is not a property of the test. It's a property of everything the test calls.

A test cannot use transaction rollback if the code under test:

• Commits explicitly. Common in anything that does its own retry or batching.

• Opens its own connection. A background worker, a job queue, a second session in the ORM. Your rollback covers your connection, not theirs — this is the single most common cause of a test that mysteriously sees no data.

• Hands work to another process. Anything that shells out, calls a real HTTP endpoint on itself, or enqueues to a broker that a separate worker drains.

• Depends on ON COMMIT behaviour. Deferred constraints, triggers, or LISTEN/NOTIFY fire at commit and simply never happen inside a rolled-back transaction.

That last category is subtle enough to be worth a specific warning: a test can pass under rollback while never exercising the constraint the production database would enforce. It's green and it proves less than it appears to.

Everything else — the large majority of API and service tests — can roll back, and the setup is a few lines:

@pytest.fixture
def db_session(connection):
    tx = connection.begin()
    session = Session(bind=connection)
    yield session
    session.close()
    tx.rollback()          # every row this test created disappears

The test: pick five tests currently using truncation. For each, list what in the call path commits or opens its own connection. Any test where the list is empty belongs in the top-left cell and is being charged for isolation it isn't using.

"Needs pre-existing data" is not the same as "needs fixtures"

Question 1 has a trap. Teams answer "yes, it needs a customer and a rate card" and reach for a fixtures file — a YAML or SQL blob loaded before the suite, holding a cast of characters every test shares.

Shared fixture files are the most reliable way to build an order-dependent suite, which is the most common cause of flaky tests. One test mutates fixture customer 7; every later test that reads customer 7 now depends on running first. Nothing announces this. It surfaces months later as a test that only fails in CI.

The distinction that matters:

• Reference data — currencies, country codes, carrier definitions, permission rows. Immutable, shared by everything, belongs in migrations. Never cleaned up, because nothing modifies it.

• Entity data — the customer, the shipment, the booking this test is about. Mutable, specific, created by the test through a factory, and never shared.

If a test mutates it, the test creates it. That single rule collapses most of the fixtures debate, and it's what makes the top-right cell survivable: the reference data doesn't need cleaning because nobody writes to it.

The test: open your fixtures file. For each entity in it, find a test that modifies it. Every one you find is a latent order dependency that is currently passing by luck.

Where the two questions don't decide: data you didn't invent

The 2×2 handles isolation. It has nothing to say about production-derived data, which is a different problem with a different failure mode.

The pull toward production data is real and legitimate: synthetic data is too clean, and bugs live in the messy rows nobody would think to generate — the customer with an apostrophe in their name, the shipment with a null destination from a 2019 import, the address that's 400 characters long.

The cost is that a copy of production in a test database is a copy of production, with all the obligations that carries, in an environment with weaker access controls than the one it came from.

The workable middle is narrower than "anonymise it":

• Take the shapes, not the rows. Mine production for the distribution — field lengths, null rates, character sets, the edge cases that actually occur — then generate synthetic data matching it. You get the mess without the obligation.

• If you must copy, transform at extraction, never after loading. A masking job that runs after the data lands in the test database has already put the data in the test database.

• Treat any real-data environment as production for access purposes. If that's too expensive, that cost is telling you something about whether the copy is worth it.

The test: ask whether a leak of your test database would be a notifiable event. If yes, it's a production system wearing a test label, and it should be governed like one.

What to change this week

Don't migrate the suite. Take the ten slowest tests and answer both questions for each. Move the ones that can roll back, and measure.

Then look at where your current strategy came from. In most suites it was chosen for the hardest test in the codebase, at a moment when there were about twelve tests, and inherited by everything since. It is worth asking whether the test that justified it still exists.

Tolvern's suite runs in about three minutes now. The bottom-right cell still has eleven tests in it — the ones with background workers and deferred constraints, which genuinely need a cloned database. They cost what they cost. The other 329 stopped paying for them.

The factory mechanics that make the top-left cell practical — building an object graph a test can create in one line — are in Build a Test Data Factory. And when the isolation problem shows up as intermittent failures rather than slow ones, start with diagnosing the flake instead.