---
title: 'Test Data Management'
source: 'https://academia.sh/en/courses/integration-testing/test-data-management'
course: 'Integration, Contract and End-to-End Testing'
language: en
updated: '2026-08-23T14:25:15+00:00'
license: 'CC BY-SA 4.0'
---

# Test Data Management

Measuring which property production-like test data has to preserve: a report defect caught by the distribution's tail rather than volume, the constraint, distribution, and relationship properties masking breaks, and the defect class the masked value destroys.

The previous lesson's environment was set up with a single-row seed: one member, one
branch. The tests passed because the questions asked could be answered with a single
row. The catalog in production is not like this, and the difference is not volume
alone.

This lesson's question is: **which property** does test data have to preserve? The
answer is not "resemble production," because that cannot be measured. There are three
properties that can be measured — distribution, constraint, and relationship — and each
makes a different defect class visible. Whichever of these three properties the masking
of personal fields breaks, it takes along with it the defect class bound to that
property.

## The Data Generator

The generator derives catalog data from a seed with the linear congruential generator
carried over from the previous course; as long as the seed is visible, the same data
can be reproduced. The `mode` argument changes only one thing: whether a member can
have more than one overdue loan.

```js
// generator.mjs — catalog data derived from a seed; mode decides the distribution's tail
export const SCHEMA = `
CREATE TABLE member (
  member_no TEXT PRIMARY KEY, name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE, branch TEXT NOT NULL);
CREATE TABLE loan (
  book_no TEXT PRIMARY KEY, member_no TEXT NOT NULL REFERENCES member(member_no),
  borrowed_day INTEGER NOT NULL, due_day INTEGER NOT NULL, returned_day INTEGER);`;

export function createGenerator(seed) {
  let state = seed;
  return () => { state = (state * 1103515245 + 12345) % 2147483648; return state / 2147483648; };
}

const NAMES = ['Alice', 'Marcus', 'Elena', 'Kevin', 'Sylvia', 'Grace', 'Owen', 'Nadia'];
const SURNAMES = ['Kane, Marsh', 'Reyes', 'Ashford', 'Renner', 'Doyle'];
const BRANCHES = ['central', 'campus', 'arts'];

export function generateData(seed, memberCount, mode = 'tailed') {
  const random = createGenerator(seed);
  const members = [];
  const loans = [];
  let bookNo = 0;
  for (let i = 0; i < memberCount; i += 1) {
    const id = `U-${String(i).padStart(4, '0')}`;
    members.push({
      id,
      name: `${NAMES[Math.floor(random() * NAMES.length)]} ${SURNAMES[Math.floor(random() * SURNAMES.length)]}`,
      email: `member${i}@example.invalid`,
      branch: BRANCHES[Math.floor(random() * BRANCHES.length)],
    });
    const tailed = random() > 0.85;
    const count = tailed ? 6 + Math.floor(random() * 5) : 1 + Math.floor(random() * 2);
    let overdueCount = 0;
    for (let k = 0; k < count; k += 1) {
      const borrowed = 900 + Math.floor(random() * 80);
      // in flat mode each member has at most one overdue loan
      const late = random() > 0.55 && (mode === 'tailed' || overdueCount === 0);
      if (late) overdueCount += 1;
      loans.push({
        bookId: `K-${String(bookNo += 1).padStart(5, '0')}`,
        memberId: id,
        borrowedDay: borrowed,
        dueDay: borrowed + 14,
        returnedDay: late ? null : borrowed + 5,
      });
    }
  }
  return { members, loans };
}
```

The work under test is the report that counts members with an overdue loan. The buggy
version counts loan rows, not members. The expected result is computed from the
generated data, independent of the query.

```js
// load.mjs — loads the generated data into node:sqlite and queries the overdue-member report in two versions
import { DatabaseSync } from 'node:sqlite';
import { SCHEMA, generateData } from './generator.mjs';

export const TODAY = 1014;

export function createDatabase({ members, loans }, path = ':memory:') {
  const db = new DatabaseSync(path);
  db.exec('PRAGMA foreign_keys = ON;');
  db.exec(SCHEMA);
  const insertMember = db.prepare('INSERT INTO member VALUES (?, ?, ?, ?)');
  const insertLoan = db.prepare('INSERT INTO loan VALUES (?, ?, ?, ?, ?)');
  let queries = 0;
  for (const m of members) { insertMember.run(m.id, m.name, m.email, m.branch); queries += 1; }
  for (const l of loans) {
    insertLoan.run(l.bookId, l.memberId, l.borrowedDay, l.dueDay, l.returnedDay);
    queries += 1;
  }
  return { db, queries };
}

const BUGGY = `SELECT COUNT(*) AS member_count FROM member m
  JOIN loan l ON l.member_no = m.member_no
  WHERE l.returned_day IS NULL AND l.due_day < ?`;
const FIXED = BUGGY.replace('COUNT(*)', 'COUNT(DISTINCT m.member_no)');

export function overdueMemberCount(db, today, version) {
  return db.prepare(version === 'fixed' ? FIXED : BUGGY).get(today).member_count;
}

// test oracle: the count is computed from the generated data, independent of SQL
export function expectedOverdueMembers({ loans }, today) {
  const set = new Set();
  for (const l of loans) if (l.returnedDay === null && l.dueDay < today) set.add(l.memberId);
  return set.size;
}

export const DATA_SETS = {
  minimal: () => ({
    members: [{ id: 'U-0000', name: 'Alice Kane', email: 'member0@example.invalid', branch: 'central' }],
    loans: [{ bookId: 'K-00001', memberId: 'U-0000', borrowedDay: 900, dueDay: 914, returnedDay: null }],
  }),
  flat: () => generateData(20260731, 200, 'flat'),
  tailed: () => generateData(20260731, 200, 'tailed'),
};
```

## Not Volume, the Tail

The two generated sets are exactly equal in volume. The only place they diverge is the
distribution of overdue loans per member.

```js
// distribution.mjs — two data sets of the same volume: one tailed, one flat
import { DATA_SETS, TODAY } from './load.mjs';

for (const label of ['flat', 'tailed']) {
  const data = DATA_SETS[label]();
  const counter = new Map();
  for (const l of data.loans) {
    if (l.returnedDay === null && l.dueDay < TODAY) counter.set(l.memberId, (counter.get(l.memberId) ?? 0) + 1);
  }
  const overdueLoans = [...counter.values()].reduce((a, b) => a + b, 0);
  const max = Math.max(...counter.values());
  console.log(`${label.padEnd(8)}: ${data.members.length} members, ${data.loans.length} loans, ` +
    `${counter.size} overdue members, ${overdueLoans} overdue loans, at most ${max} per member`);
}
```

```
flat    : 200 members, 572 loans, 143 overdue members, 143 overdue loans, at most 1 per member
tailed  : 200 members, 572 loans, 143 overdue members, 297 overdue loans, at most 8 per member
```

The same test is run on three sets.

```js
// report.test.mjs — the same report test on three data sets; picked by the DATA and REPORT variables
import test from 'node:test';
import assert from 'node:assert/strict';
import { createDatabase, overdueMemberCount, expectedOverdueMembers, DATA_SETS, TODAY } from './load.mjs';

const dataName = process.env.DATA ?? 'minimal';
const version = process.env.REPORT ?? 'buggy';

test(`overdue member count is correct (${dataName})`, () => {
  const data = DATA_SETS[dataName]();
  const { db } = createDatabase(data);
  assert.equal(overdueMemberCount(db, TODAY, version), expectedOverdueMembers(data, TODAY));
});
```

```sh
for v in minimal flat tailed; do
  DATA=$v REPORT=buggy node --test --test-reporter=tap report.test.mjs | grep -E '^ *(ok|not ok)'
done
DATA=tailed REPORT=fixed node --test --test-reporter=tap report.test.mjs | grep -E '^ *(ok|not ok)'
```

```
ok 1 - overdue member count is correct (minimal)
ok 1 - overdue member count is correct (flat)
not ok 1 - overdue member count is correct (tailed)
ok 1 - overdue member count is correct (tailed)
```

The second line is this lesson's central measurement. The flat set, though two hundred
times the size of the minimal set, could not see the defect; what caught it was not
volume, but a member having more than one overdue loan. The name of the caught defect
class is **distribution-dependent defect**, and the property that has to be preserved
is the distribution's tail. The fix is writing `COUNT(DISTINCT m.member_no)` in place of
`COUNT(*)`; the fourth line shows the fixed version passing on the same set.

## The Property Masking Breaks

Personal fields are masked when production data is carried into the test. The masking
form determines which property survives. Four forms are tried on the same source set.

```js
// masking.mjs — four masking forms and the property each one breaks
import { createDatabase, DATA_SETS } from './load.mjs';

const source = DATA_SETS.tailed();

const FORMS = {
  'fixed email  ': (v) => ({ ...v, members: v.members.map((m) => ({ ...m, email: 'hidden@example.invalid' })) }),
  'fixed branch ': (v) => ({ ...v, members: v.members.map((m) => ({ ...m, branch: 'hidden' })) }),
  'new member id': (v) => ({ ...v, members: v.members.map((m) => ({ ...m, id: `M-${m.id.slice(2)}` })) }),
  'derived      ': (v) => ({
    ...v,
    members: v.members.map((m) => ({
      ...m, name: `Member-${m.id.slice(2)}`, email: `u${m.id.slice(2)}@example.invalid`,
    })),
  }),
};

const branchCount = (db) => db.prepare('SELECT COUNT(DISTINCT branch) AS n FROM member').get().n;

console.log(`source        : loaded, distinct-branch count ${branchCount(createDatabase(source).db)}`);
for (const [label, mask] of Object.entries(FORMS)) {
  try {
    const { db } = createDatabase(mask(source));
    console.log(`${label} : loaded, distinct-branch count ${branchCount(db)}`);
  } catch (error) {
    console.log(`${label} : failed to load, ${error.message}`);
  }
}
```

```
source        : loaded, distinct-branch count 3
fixed email   : failed to load, UNIQUE constraint failed: member.email
fixed branch  : loaded, distinct-branch count 1
new member id : failed to load, FOREIGN KEY constraint failed
derived       : loaded, distinct-branch count 3
```

Three breakages correspond to three separate properties. The fixed-email mask breaks
the **constraint**: the uniqueness constraint even blocks loading into the database.
The fixed-branch mask breaks the **distribution**: the data loads, but every
branch-broken-down report now sees a single branch, and no branch-dependent defect
class can be tested. Changing the member id in only one table breaks the
**relationship**: the foreign key constraint stops the load; had the constraint been
off, the join would have silently returned empty. The derived mask preserves all three,
because the masked value is derived from the source value's uniqueness and is applied
in the same form across every table.

## What This Data Does Not See

The derived mask preserved all three properties, but it did not preserve the masked
field's **content**. When the report is exported, fields are joined with a comma and
not quoted.

```js
// export.mjs — turns the overdue-member report into comma-separated rows; fields are not quoted
import { createDatabase, DATA_SETS, TODAY } from './load.mjs';

const REPORT = `SELECT m.member_no, m.name, m.branch, COUNT(*) AS overdue FROM member m
  JOIN loan l ON l.member_no = m.member_no
  WHERE l.returned_day IS NULL AND l.due_day < ?
  GROUP BY m.member_no, m.name, m.branch ORDER BY m.member_no`;

export function exportRows(rows) {
  return rows.map((r) => [r.member_no, r.name, r.branch, r.overdue].join(','));
}

export function reportRows(format) {
  const source = DATA_SETS.tailed();
  const data = format === 'masked'
    ? { ...source, members: source.members.map((m) => ({ ...m, name: `Member-${m.id.slice(2)}` })) }
    : source;
  const { db } = createDatabase(data);
  return db.prepare(REPORT).all(TODAY);
}
```

```js
// export.test.mjs — each exported row must carry four fields; the data format is picked with a variable
import test from 'node:test';
import assert from 'node:assert/strict';
import { exportRows, reportRows } from './export.mjs';

const format = process.env.DATA_FORMAT ?? 'masked';

test(`exported rows carry four fields (${format})`, () => {
  const rows = exportRows(reportRows(format));
  const broken = rows.filter((r) => r.split(',').length !== 4);
  assert.deepEqual(broken.slice(0, 1), []);
});
```

```sh
DATA_FORMAT=masked node --test --test-reporter=tap export.test.mjs | grep -E '^ *(ok|not ok)'
DATA_FORMAT=source node --test --test-reporter=tap export.test.mjs | grep -E '^ *(ok|not ok)'
node -e "const m = await import('./export.mjs'); console.log(m.exportRows(m.reportRows('source')).find((s) => s.split(',').length !== 4));" --input-type=module
```

```
ok 1 - exported rows carry four fields (masked)
not ok 1 - exported rows carry four fields (source)
U-0004,Sylvia Kane, Marsh,arts,1
```

The test is green with masked data; the same test is red with source data. The name of
the missed defect class is **content-dependent defect**: what triggers the defect is
the comma inside the name field, and the derived mask destroyed that comma. This is
masking's unavoidable cost — a personal field's content cannot be kept in the test. The
counterpart is testing the content shape with a separate rule list: unmasked, generated
names that carry a separator are added to the export test by hand.

## Cost

```js
// cost.mjs — the cost of production-like data: rows, queries, file size, and relative time
import { mkdtempSync, rmSync, statSync } from 'node:fs';
import { tmpdir } from 'node:os';
import { join } from 'node:path';
import { createHash } from 'node:crypto';
import { createDatabase, DATA_SETS } from './load.mjs';

const root = mkdtempSync(join(tmpdir(), 'data-'));
const digest = (data) => createHash('sha256').update(JSON.stringify(data)).digest('hex').slice(0, 12);

function measure(label) {
  const data = DATA_SETS[label]();
  const path = join(root, `${label}.db`);
  const start = performance.now();
  const { queries } = createDatabase(data, path);
  const time = performance.now() - start;
  return { rows: data.members.length + data.loans.length, queries, time, size: statSync(path).size, digest: digest(data) };
}

const minimal = measure('minimal');
const tailed = measure('tailed');
rmSync(root, { recursive: true, force: true });

console.log(`minimal : ${String(minimal.rows).padStart(3)} rows, ${String(minimal.queries).padStart(3)} load queries, ${minimal.size} bytes`);
console.log(`tailed  : ${tailed.rows} rows, ${tailed.queries} load queries, ${tailed.size} bytes`);
console.log(`seed 20260731 gives the same digest  : ${digest(DATA_SETS.tailed()) === tailed.digest}`);
console.log(`tailed set took longer to load       : ${tailed.time > minimal.time}`);
```

```
minimal :   2 rows,   2 load queries, 24576 bytes
tailed  : 772 rows, 772 load queries, 77824 bytes
seed 20260731 gives the same digest  : true
tailed set took longer to load       : true
```

Seven hundred seventy-two load queries and a seventy-eight-kilobyte file are paid again
on every run. This cost's counterpart is a single defect class: distribution-dependent
defect. Paying the same cost with the flat set did not buy that class; volume alone is
not a measure. The seed being visible fixes the cost — instead of a data set stored in a
file, a twelve-digit number is carried, and the same data is reproduced.

## Summary

- Test data has three measurable properties: distribution, constraint, and
  relationship; each makes a separate defect class visible.
- Of two equal-volume sets, only the tailed one caught the report defect; the flat set,
  two hundred times the size, could not see it.
- A fixed-value mask breaks the uniqueness constraint, a fixed-field mask breaks the
  distribution, renumbering in a single table breaks the relationship; the derived mask
  preserves all three.
- The derived mask cannot preserve a field's content: the separator-dependent export
  defect inside the name stayed green with masked data and red with source data.
- The cost is seven hundred seventy-two load queries and seventy-eight kilobytes; as
  long as the seed is visible, the data set is not carried, it is reproduced.

## Next Step

Every measurement here assumed a single schema: tables ready, columns in place,
constraints defined. The schema is itself a product piece and changes over time — a
column is added, a column is split, a constraint is tightened. These changes are
applied through ordered steps called a **migration**, and each step has an up
direction and a down direction. The next lesson takes the migration itself under test:
what happens when the step order breaks, whether the down migration can preserve data,
and how the schema is verified to be in the expected shape.