Anonymising Production Data for Test Fixtures

Synthetic data is clean, cheap and often misleading: it has the shape of your schema but not the shape of reality, so the failures it misses are exactly the ones production finds — the customer with an apostrophe in their name, the order with two hundred line items, the address with no postcode. A sanitised production snapshot fixes that, provided the sanitisation is thorough and provable. This guide covers classifying fields, pseudonymising in a way that keeps relationships intact, handling free text, and proving no identifier survived. It is for engineers who need realistic fixtures and cannot ship personal data into a test environment, and it sits under test data management.

Root Cause Analysis

Naive anonymisation fails in two opposite directions. Replace every field with random values and the data becomes synthetic again — the distributions are gone, the relationships are broken, and the fixture no longer represents anything. Replace too little, and personal data leaks into a test environment with weaker controls, which is a genuine incident regardless of intent.

The middle path requires distinguishing three kinds of field. Direct identifiers — names, emails, phone numbers — must be replaced. Quasi-identifiers — postcode, date of birth, job title — can identify someone in combination even when each is innocuous alone, and need generalising rather than replacing. Everything else — order totals, timestamps, status codes — is what makes the fixture realistic and should be preserved exactly.

The subtlety that catches most first attempts is referential integrity. If the same customer appears in three tables, replacing their email independently in each produces a dataset where joins fail and tests break for reasons that have nothing to do with the code. Deterministic pseudonymisation — the same input always producing the same output — preserves every relationship while replacing every value.

Reproducible Setup

Work from a schema-driven classification so that a new column cannot silently pass through unclassified.

// scripts/anonymise/classification.ts
export type Treatment = 'pseudonymise' | 'generalise' | 'preserve' | 'drop';

export const CLASSIFICATION: Record<string, Record<string, Treatment>> = {
  customers: {
    id: 'preserve',
    email: 'pseudonymise',
    full_name: 'pseudonymise',
    phone: 'pseudonymise',
    postcode: 'generalise',
    date_of_birth: 'generalise',
    created_at: 'preserve',
    notes: 'drop',
  },
  orders: {
    id: 'preserve',
    customer_id: 'preserve',     // an internal key, not an identifier
    total_pence: 'preserve',
    shipping_address: 'pseudonymise',
    placed_at: 'preserve',
  },
};

pg_dump --data-only --table=customers --table=orders "$PROD_READONLY_URL" > snapshot.sql

Implementation

Step 1 — Pseudonymise with a keyed hash so the mapping is stable and irreversible. The same email always becomes the same fake email, so joins survive; without the key nobody can reverse it.

// scripts/anonymise/pseudonymise.ts
import { createHmac } from 'node:crypto';

const KEY = process.env.ANON_KEY!;          // held only by the sanitising job

const digest = (value: string, salt: string) =>
  createHmac('sha256', KEY).update(`${salt}:${value}`).digest('hex');

export const fakeEmail = (email: string) => `user-${digest(email, 'email').slice(0, 12)}@example.test`;

export const fakeName = (name: string) => {
  const first = FIRST_NAMES[parseInt(digest(name, 'first').slice(0, 8), 16) % FIRST_NAMES.length];
  const last = LAST_NAMES[parseInt(digest(name, 'last').slice(0, 8), 16) % LAST_NAMES.length];
  return `${first} ${last}`;
};

Step 2 — Generalise quasi-identifiers instead of replacing them. A postcode district and a birth year keep the distribution useful while removing the ability to single someone out.

// scripts/anonymise/generalise.ts
export const postcodeDistrict = (postcode: string) => postcode.trim().split(/\s+/)[0];      // "SW1A 1AA" → "SW1A"
export const birthYear = (iso: string) => `${iso.slice(0, 4)}-01-01`;
export const ageBand = (iso: string) => {
  const age = new Date().getFullYear() - Number(iso.slice(0, 4));
  return age < 25 ? '18-24' : age < 45 ? '25-44' : age < 65 ? '45-64' : '65+';
};

Step 3 — Drop free text rather than trying to clean it. A notes field can contain anything — a phone number, a full name, a card number someone pasted — and no regular expression will reliably find all of it.

// keep the shape, lose the content
const sanitiseNotes = (notes: string | null) =>
  notes === null ? null : `[redacted ${notes.length} chars]`;

Step 4 — Fail on any unclassified column. This is the step that keeps the pipeline safe as the schema evolves; without it, a new column added next month ships straight into the fixture.

// scripts/anonymise/run.ts
import { CLASSIFICATION } from './classification';

export function transformRow(table: string, row: Record<string, unknown>) {
  const rules = CLASSIFICATION[table];
  if (!rules) throw new Error(`No classification for table "${table}"`);

  const unknown = Object.keys(row).filter((col) => !(col in rules));
  if (unknown.length) {
    throw new Error(`Unclassified column(s) in ${table}: ${unknown.join(', ')}`);
  }
  // …apply each rule
}

Step 5 — Run the sanitisation where the raw data already lives. The snapshot must never leave the production boundary in raw form, so the transform belongs inside it and only the sanitised output travels.

Step 6 — Treat the output as an artifact with a retention policy. A sanitised fixture is still a dataset about real behaviour, so version it, control who can read it, and refresh it on a schedule rather than letting a two-year-old copy become the team’s idea of realistic data.

Verification

Scan the output for anything that looks like an identifier. This is a safety net rather than the primary control, but it catches the column somebody classified as preserve without thinking.

// scripts/anonymise/scan.ts
const PATTERNS: Array<[string, RegExp]> = [
  ['email', /[\w.+-]+@(?!example\.test)[\w-]+\.[a-z]{2,}/i],
  ['uk phone', /\b(?:0|\+44)\s?\d{3,4}\s?\d{3}\s?\d{3,4}\b/],
  ['card-like', /\b(?:\d[ -]*?){13,16}\b/],
  ['postcode', /\b[A-Z]{1,2}\d[A-Z\d]?\s?\d[A-Z]{2}\b/],
];

let failures = 0;
for (const line of fixture.split('\n')) {
  for (const [name, re] of PATTERNS) {
    if (re.test(line)) { console.error(`${name} survived: ${line.slice(0, 80)}`); failures++; }
  }
}
process.exit(failures ? 1 : 0);

Then verify referential integrity, because a broken join produces test failures that look like application bugs.

-- every order still points at a customer that exists
SELECT COUNT(*) FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;
-- 0

Finally, verify the fixture is still representative by comparing distributions against the source — order sizes, status frequencies, the proportion of records with optional fields absent. If the sanitised data is far tidier than production, the fixture will miss the messy cases it was created to capture.

psql -c "SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY 2 DESC" # production
psql -c "SELECT status, COUNT(*) FROM orders GROUP BY status ORDER BY 2 DESC" # fixture
# the two distributions should match closely

Troubleshooting

Symptom: joins fail in the sanitised fixture. Diagnosis: a key was pseudonymised non-deterministically, or was pseudonymised in one table and preserved in another. Fix: internal keys should be preserve; only human-meaningful identifiers get replaced, and always with the deterministic function so the same value maps identically everywhere.

Symptom: the scan flags addresses that were replaced. Diagnosis: the fake address generator emits realistic postcodes, which the pattern then matches. Fix: generate pseudonymised values from an obviously non-real space — example.test domains, a reserved postcode prefix — so real and fake are distinguishable by the scan and by a human reading the fixture.

Symptom: tests behave differently against the sanitised data. Diagnosis: generalisation changed something the code branches on, such as an age band or a postcode region. Fix: check which fields the logic reads, and generalise those more carefully — or preserve them and reduce the row count instead, since a smaller sample is often a better privacy control than a coarser one.

Symptom: the pipeline is too slow to run regularly. Diagnosis: the whole database is being transformed when the fixture needs a representative sample. Fix: sample first and transform second — a few thousand customers with their related rows is usually more useful as a fixture than a full copy, and it runs in seconds.

FAQ

Related

• Back to Test Data Management
• Generating realistic fake data with Faker — the synthetic alternative and when it fits.
• Keeping large fixture files out of the repo — where the sanitised artifact should live.
• Seeding a test database for integration tests — loading the fixture once it exists.