Test Data Management

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.

// 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.

// 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: '[email protected]', 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.

// 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.

// 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));
});

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.

// 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: '[email protected]' })) }),
  '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.

// 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);
}

// 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), []);
});

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

// 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.