There is a kind of bug that never shows up in a test and reliably happens in production. A name with an apostrophe in it. An order with zero line items, which the model says cannot exist. A customer whose account was created nine years ago, back when the mandatory field was not mandatory.
These bugs share one cause, and it is not in the code. The test data is too clean. It was invented by people who understood the system, so it contains exactly the cases somebody thought of.
The obvious conclusion is to copy the production database. That is the right instinct and the wrong route. This article describes the route in between: a test data set that carries the shapes of reality without its content.
Why the dump is not a solution
A production dump on a developer's machine is everyday practice in many companies, and it brings four problems, each of them sufficient on its own.
It does not hold up legally. Personal data in a development environment is processing without a purpose, and the environment it sits in has neither the access control nor the retention limits of production.
It is too large. Four hundred gigabytes do not run on a laptop, and a test run that takes forty minutes to load is a test run that does not get executed.
It sends email. The unpleasant version of this problem is not hypothetical: a batch job in the test environment, real addresses in the database, and two thousand customers receive a three-year-old invoice for the second time.
It contains secrets. Credentials for third-party systems, session keys, payment data. A dump travels from one machine to the next and is in seven places two years later.
What is actually needed
The mistake behind the dump is the assumption that you need the data. What you need are the shapes the data comes in.
Nobody needs two million customers to test a payment feature. What is needed is the customer without a billing address, the one with three addresses, the one with an umlaut in their surname, the one from before the 2019 migration, the one with an open credit note, and the one whose account is blocked.
That changes the task. It is no longer "how do we get production data down here" but: which cases exist, and how do we get a few of each? That is a selection problem, and its result is a data set of a few hundred megabytes instead of four hundred gigabytes.
The entry point is a query against the production database that copies nothing and counts instead: how many customers have no billing address, how many have more than one, how many have an account from before 2019. The list that comes out of it is worth more than the dump it replaces, because it is the list of cases the system actually contains.
The anchor and everything hanging off it
The selection itself happens along an anchor. In most business systems that is the customer, in retail sometimes the order, in a platform the tenant.
You pick a few hundred anchors so that every known shape appears more than once, and then take everything that hangs off them:
-- The anchor: deliberately mixed rather than random. A random
-- slice is exactly what misses the rare case.
CREATE TEMP TABLE anchor AS
(SELECT id FROM customer WHERE billing_address_id IS NULL LIMIT 50)
UNION
(SELECT id FROM customer WHERE created_at < '2019-01-01' LIMIT 50)
UNION
(SELECT id FROM customer WHERE blocked = true LIMIT 20)
UNION
(SELECT id FROM customer ORDER BY random() LIMIT 300);
-- And then everything hanging off it, in foreign key order.
COPY (SELECT * FROM orders WHERE customer_id IN (SELECT id FROM anchor))
TO '/export/orders.csv' CSV;The random part at the end is deliberate: it brings in the cases nobody thought of, and those are the entire reason for the exercise.
Where the silent references that surface here come from is covered in The real legacy is your database schema.
The genuinely hard problem
Anyone building this for the first time underestimates one thing: a slice of a database is almost never internally consistent.
The hard foreign keys are the easy part; the database reports those on load. The silent references are the problem, and a grown system has more of them than the diagram shows: an id inside a JSON column, a reference spread across a type field and an id field, a table pointing at another one through a string, and the classic, a reference into a table owned by another application.
The approach against that is not thinking, it is trying: load the slice, walk the application through its main paths, and turn every dangling reference that surfaces into a rule in the selection script. After three rounds the data set is consistent, and the list of silent references is incidentally the best documentation of the data model that project has.
The alternative, doing all of it through a layer that knows about these references, sounds cleaner and is not: the selection script would then be exactly as good as the application's model, and its gaps are precisely the gaps at issue here. The route through the ETL idea holds up better: extract, transform, load, and the transform step is where reality gets bent into shape.
Masking, not deleting
Only then comes the part most people take for the whole job. There is one rule for it and it matters more than the choice of tool: masking is deterministic.
If the same name turns into two different names in two places, every join falls apart, and the data set loses exactly the property it was created for. If it turns into the same replacement in both places, the structure survives:
-- Same input, same replacement, everywhere in the data set.
-- The secret lives in the environment, not in the script.
CREATE FUNCTION mask(value text, field text) RETURNS text AS
'SELECT substr(encode(hmac(value || field,
current_setting(''mask.key''), ''sha256''), ''hex''), 1, 12)'
LANGUAGE sql IMMUTABLE;
UPDATE customer SET
email = mask(email, 'email') || '@example.invalid',
lastname = mask(lastname, 'name'),
phone = '+49 30 ' || substr(mask(phone, 'tel'), 1, 8);Two things in there are not details. The .invalid domain is reserved and undeliverable, so no batch job can reach it by accident. And about the format: a phone number that is no longer a phone number will not find the bug in the format check that you were after in the first place.
Three kinds of field are not masked but never exported at all: credentials for third-party systems, payment data, and anything falling under a special category under Article 9. Those get a fixed value on load.
Outbound belongs to the environment
A separation that regularly gets blurred and is expensive: whether an email gets delivered is not a property of the data.
Trying to prevent delivery through the data gives you a safeguard that springs a new leak with every new field and every new table. The place that holds is the environment: a mail server that accepts everything and forwards nothing, a payment provider in test mode, outbound webhooks pointing at a receiver inside your own network.
The check for that belongs in the setup of the environment, not in a written procedure: if the application starts and the mail server is not the local one, it does not start.
The rehearsal of a restore needs the same slice: Backups nobody has ever restored.
Refreshing, and the way back
A test data set goes stale, and not through the data but through the schema. Four weeks is a good rhythm: close enough to pick up new fields, rare enough not to turn the run into a burden.
More important than the rhythm is the direction back. Every production bug that rests on a data shape missing from the test set leaves two things behind: the fix, and a new rule in the selection script. That way the data set grows along the bugs that actually happened rather than along the cases somebody imagined.
The effort for all of this is one to two weeks, and the limit deserves stating plainly: load testing stays a different topic, because two thousand customers do not behave like two million. For everything to do with correctness, the shaped slice beats the full dump, and it is allowed to sit on a laptop. How I rebuild grown systems is described on its own page.
This article belongs to a series about systems that already exist. The retrospective orders every article in it by situation.
