Getting realistic test data into ephemeral databases

There's a specific class of bug that only appears with real data volumes and real data shapes. The query that's instant against 50 rows and takes 8 seconds against 500,000, because the planner switches strategies somewhere in between. The pagination that breaks on page 400. The name with a right-to-left character in it. The customer with 40,000 orders when your UI assumed a few dozen.

None of these are caught by fixtures. They're caught by data that resembles production, and getting that into an ephemeral test environment is a genuinely underrated engineering problem.

Three sources of test data

Each is right for something and wrong for others, and most teams need at least two.

Hand-written fixtures

Explicit rows in code or YAML. Unbeatable for unit-level tests because they're precise, readable, and version-controlled — when a test fails you can see exactly what it was operating on.

They don't scale to volume and they encode your assumptions. If you never thought about a customer with no email address, your fixtures don't contain one, and neither does your test coverage.

Generated data

Faker and friends produce plausible records at volume. Good for load and performance testing, and for filling tables so query plans are realistic.

-- Half a million orders with a realistic skew: most customers few orders,
-- a handful with thousands. Uniform distributions hide the bugs.
INSERT INTO orders (customer_id, total_cents, created_at)
SELECT
  -- exponential skew, so some customers are genuinely heavy
  1 + floor(-ln(1 - random()) * 2000)::int % 10000,
  (random() * 50000)::int,
  now() - (random() * interval '730 days')
FROM generate_series(1, 500000);

The trap is distribution. Generated data is usually uniform, and production never is. Uniform data makes indexes look better than they are, hides the pathological customer with a hundred thousand rows, and produces query plans that don't match reality. If you generate, generate with skew.

A copy of production

The only source with genuinely real distributions, real edge cases and real messiness. It also carries genuinely real personal data, which is the entire difficulty.

Anonymising a production copy

Copying production data into a test environment without transformation is a compliance incident waiting for a name. Under GDPR and equivalents, a test database holding customer records is processing personal data — subject to the same obligations as production, in an environment with far more people holding credentials to it.

The workable pattern is restore-then-transform, and the ordering is what makes it safe.

# 1. Clone into an isolated instance — never transform production in place
CLONE=$(pandastack db clone db_prod --label anon-staging --json | jq -r .id)
URL=$(pandastack db get "$CLONE" --json | jq -r .connection_url)

# 2. Scrub inside the clone, before anyone but the pipeline can reach it
psql "$URL" -v ON_ERROR_STOP=1 -f anonymise.sql

# 3. Only now snapshot it as the reusable base for test environments

-- anonymise.sql — deterministic, so referential integrity survives
UPDATE users SET
  email      = 'user' || id || '@example.invalid',
  full_name  = 'Test User ' || id,
  phone      = NULL,
  -- keep the domain: it drives real behaviour in some code paths
  company    = 'Company ' || (hashtext(company) % 1000);

UPDATE payment_methods SET
  last_four  = lpad((hashtext(last_four) % 10000)::text, 4, '0'),
  billing_zip = '00000';

TRUNCATE audit_log, sessions, password_reset_tokens, webhook_deliveries;

-- Prove it worked. Fail loudly rather than shipping a half-scrubbed copy.
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM users WHERE email NOT LIKE '%@example.invalid') THEN
    RAISE EXCEPTION 'anonymisation incomplete: real emails remain';
  END IF;
END $$;

Three details that catch people out: keep transformations deterministic so the same input yields the same output and foreign keys stay consistent; preserve the properties the code depends on, like email domain or string length; and remember that free-text fields — support tickets, notes, addresses — contain personal data that no column-level rule will find. Those usually need truncating rather than transforming.

The setup-cost problem

Suppose you've solved data quality. Now you have a new problem: getting that data in place takes minutes, and if every test environment pays it, your CI is mostly waiting.

restore anonymised dump   ~4 min
run migrations            ~30 s
rebuild indexes           ~2 min
warm the cache            ~1 min
                          --------
per environment           ~8 min      × 20 parallel jobs = untenable

The fix is to stop treating environment setup as something tests do, and treat it as something you did once. Pay the eight minutes on a base environment, snapshot it, and give every test a restored copy.

# Nightly: rebuild the base from production, anonymise, snapshot
./scripts/refresh-test-base.sh

# Per CI job: a private copy of that exact state, in about a second
pandastack db clone db_test_base --label "ci-${GITHUB_RUN_ID}"

# Teardown is a delete, not a cleanup script
pandastack db delete "$CI_DB" --yes

The snapshot captures more than a dump does. It has the schema, the data, the built indexes, the statistics the planner uses, and a warm cache — so the first query against a restored copy performs like the hundredth query against a freshly seeded one. For performance-sensitive tests that difference is the whole point.

Keeping it fresh

A snapshot ages. New migrations land, new columns appear, and a base built three weeks ago doesn't have them — so tests fail against a schema nobody is running any more.

• Rebuild the base on a schedule, nightly or weekly, from a fresh production clone through the same anonymisation pipeline.
• Rebuild it when a migration merges, triggered from the same CI that runs migrations. This is the version that stops the failure mode entirely.
• Apply pending migrations to a restored copy at test time as a cheap fallback — a few seconds of migration beats a full rebuild, and it means an old base still works.
• Version the snapshot with the migration it corresponds to, so a test can fail with 'base is 4 migrations behind' rather than an unexplained column-not-found error.

What to use where

The layering that works, and it's worth being deliberate about which layer a given test belongs in.

1. Unit tests: hand-written fixtures, in-process, no database. Fast, precise, and the failure message points at the data.
2. Integration tests: a small seeded database, restored from a snapshot. Real SQL, real constraints, real transactions — the correctness layer.
3. Performance and query-plan tests: the anonymised production-shaped clone. This is where realistic volume and skew are non-negotiable, because they are the entire subject.
4. End-to-end and preview environments: a full anonymised copy, so a human clicking through sees something that resembles the product.

The single highest-value change most teams can make is adding layer three. Correctness bugs get caught by fixtures; performance bugs get caught by volume, and if nothing in your pipeline has production-shaped data, your first encounter with the 8-second query is a customer's.