Database Test Data for Migration Testing

Database Test Data for Migration Testing

Migrating a database schema—or moving data between platforms—breaks things in ways unit tests rarely surface. The only reliable way to catch those breaks before production is to run the migration against data that looks, behaves, and stresses the system like the real thing. This guide walks through the decisions, tooling, and validation steps needed to build a repeatable test‑data pipeline for migration testing.

---

1. Why Migration Testing Needs Realistic Data

Symptom	Typical Cause	What Realistic Data Reveals
Constraint violations after cut‑over	Hidden NOT NULL, UNIQUE, or CHECK constraints	Edge‑case values (empty strings, max‑length, duplicate keys)
Performance regression	Missing indexes, partition pruning, or statistics	Data volume, distribution, and cardinality that match production
Application‑level bugs	ORM mapping assumptions, default values, triggers	Nullable vs. non‑nullable columns, generated columns, cascade rules
Data‑loss bugs	Truncation, encoding mismatches, timezone shifts	Unicode characters, timestamps with offsets, large BLOBs

If your test suite only runs against an empty schema or a handful of hand‑crafted rows, you will miss the majority of the above. The goal is representative, repeatable, and controllable data.

---

2. Prerequisites

Before you generate a single row, make sure the following are in place.

Prerequisite	Why It Matters	Quick Verification
Version‑controlled schema (DDL scripts, Flyway/Liquibase changelogs)	Guarantees the exact target schema for every test run	git log --oneline -n 5 db/migrations
Source‑system data profile (column stats, value distributions, referential integrity)	Drives realistic generators and constraints	ANALYZE TABLE …; or export pg_stats / sys.dm_db_stats_properties
Isolated test environment (container, VM, or dedicated schema)	Prevents cross‑test contamination	docker run --rm -e POSTGRES_DB=test_migration …
Automation hook (CI job, Make target, Gradle task)	Makes data generation part of the pipeline, not a manual step	ci.yml includes generate-test-data step
Data‑masking policy (if production data is used as a seed)	Legal / compliance	Documented in SECURITY.md

If any of these are missing, stop and fix them first. A migration test that runs against a drifting schema or a shared database is a false sense of security.

---

3. Data‑Generation Strategies

Choose a strategy (or a mix) based on schema complexity, data volume, and team familiarity.

Strategy	Description	When It Shines	Drawbacks
Schema‑aware synthetic generation	Tools read DDL, infer types, constraints, FK graph, then emit rows that satisfy them.	Greenfield projects, CI‑first teams, need for deterministic seeds.	May need custom plugins for exotic types (e.g., PostGIS, JSONB).
Production‑snapshot + masking	Dump a recent production snapshot, run masking (PII scrubbing, value perturbation).	Legacy systems where synthetic data cannot capture hidden business rules.	Requires secure handling, storage, and masking pipelines.
Hybrid: seed + synthetic augmentation	Load a small masked snapshot (core reference tables) and synthesize high‑volume fact tables.	Large fact tables where only distribution matters.	Two‑step pipeline, more moving parts.
Rule‑based generators (DSL)	Write declarative rules (e.g., “order_date between customer.created_at and now()”).	Complex business invariants that pure random data cannot satisfy.	Authoring effort; rules must be maintained alongside schema.

3.1 Selecting a Primary Strategy

1. Start with schema‑aware synthetic generation – it gives you a clean, version‑controlled baseline.
2. Add a masked snapshot only for reference tables (countries, currencies, product catalogs) that are expensive to synthesize realistically.
3. Layer rule‑based augmentations for any invariant the synthetic engine cannot express (e.g., “every order must have at least one order_item”).

---

4. Tooling Landscape

Category	Tool (Open‑Source / Free)	Strength	Typical Integration
Schema‑aware synthetic	QA3 Test Data Generator (/tools/test-data-generator)	Reads DDL, respects FK, generates deterministic CSV/SQL/Parquet	CLI, Docker, GitHub Action
	DataFactory (Java)	Programmatic API, good for JVM projects	Maven/Gradle plugin
	go-faker + sqlx	Go‑centric, fast	Go test suite
Masking / Anonymization	pg_anonymizer, DataMasker (Redgate), ARX	Policy‑driven, supports regex, differential privacy	Pre‑load step in CI
Rule‑based DSL	Synthesized (commercial), Mockaroo (free tier)	Visual rule builder, export to SQL/CSV	Manual or API‑driven
Orchestration	dbt seed, Airflow DAG, GitHub Actions matrix	Versioned, testable, parallelizable	CI/CD pipeline

Tip: The QA3 generator can output a manifest (manifest.json) that lists every table, row count, and seed used. Store that manifest as an artifact; it makes debugging a failed migration reproducible.

---

5. Building a Repeatable Test‑Data Pipeline

Below is a concrete, end‑to‑end pipeline you can copy into a GitHub Actions workflow (or adapt to GitLab, Azure Pipelines, etc.).

5.1 Directory Layout

repo/
├─ db/
│  ├─ migrations/          # Flyway/Liquibase scripts (V1__init.sql …)
│  └─ seed/
│     ├─ reference/        # Small masked CSVs (countries, currencies)
│     └─ synthetic/        # Generated by QA3 (orders, order_items, …)
├─ tools/
│  └─ qa3-test-data/       # Docker image or binary
├─ ci/
│  └─ generate-test-data.yml
└─ tests/
   └─ migration/
      ├─ run_migration.sh
      └─ validate.sh

5.2 ci/generate-test-data.yml (GitHub Actions)

name: Generate Test Data


on:
  workflow_dispatch:
  push:
    paths:
      - 'db/migrations/**'
      - 'db/seed/reference/**'


jobs:
  generate:
    runs-on: ubuntu-latest
    timeout-minutes: 30
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: test_migration
          POSTGRES_PASSWORD: postgres
        ports: ["5432:5432"]
        options: >-
          --health-cmd "pg_isready -U postgres"
          --health-interval 5s
          --health-timeout 3s
          --health-retries 10


steps:
      - uses: actions/checkout@v4


- name: Install QA3 CLI
        run: |
          curl -fsSL https://qa3.io/cli/install.sh | bash
          echo "$HOME/.qa3/bin" >> $GITHUB_PATH


- name: Load reference seed
        run: |
          for f in db/seed/reference/*.csv; do
            table=$(basename "$f" .csv)
            psql "postgres://postgres:postgres@localhost:5432/test_migration" \
              -c "\copy $table FROM '$f' CSV HEADER"
          done


- name: Generate synthetic data
        env:
          QA3_SEED: ${{ github.sha }}   # deterministic per commit
        run: |
          qa3-test-data generate \
            --ddl db/migrations \
            --ref-db "postgres://postgres:postgres@localhost:5432/test_migration" \
            --out db/seed/synthetic \
            --rows-factor 10   # 10× the row‑count estimates from stats
          # The command writes a manifest.json next to the CSVs


- name: Upload seed artifact
        uses: actions/upload-artifact@v4
        with:
          name: synthetic-seed
          path: db/seed/synthetic/
          retention-days: 7

Key points

• Deterministic seed – QA3_SEED tied to the commit SHA guarantees the same data for the same schema version.
• Reference data first – Foreign‑key tables must exist before synthetic generation.
• rows-factor – Scale factor; start with 1 for fast feedback, bump to 10‑100 for performance runs.
• Artifact upload – Downstream jobs (migration run, validation) pull the same CSVs, eliminating drift.

5.3 Running the Migration

# tests/migration/run_migration.sh


set -euo pipefail


DB_URL="postgres://postgres:postgres@localhost:5432/test_migration"


# 1. Apply baseline schema (pre‑migration)


flyway -url="$DB_URL" -locations=filesystem:db/migrations baseline


# 2. Load synthetic seed


for f in db/seed/synthetic/*.csv; do
  table=$(basename "$f" .csv)
  psql "$DB_URL" -c "\copy $table FROM '$f' CSV HEADER"
done


# 3. Run the migration under test


flyway -url="$DB_URL" -locations=filesystem:db/migrations migrate


# 4. Dump post‑migration schema + row counts for validation


pg_dump --schema-only "$DB_URL" > /tmp/post_migration_schema.sql
psql "$DB_URL" -c "
  SELECT table_name,
         (SELECT COUNT(*) FROM information_schema.columns WHERE table_name = t.table_name) AS col_cnt,
         (SELECT COUNT(*) FROM $table_name) AS row_cnt
  FROM information_schema.tables t
  WHERE table_schema = 'public'
  ORDER BY table_name;
" > /tmp/post_migration_counts.txt

5.4 Validation Suite

Validation Type	Implementation	Failure Signal
Schema diff	diff -u pre_migration_schema.sql post_migration_schema.sql	Unexpected ALTER TABLE … DROP COLUMN
Row‑count sanity	Compare pre_migration_counts.txt vs post_migration_counts.txt (allow ±0 % for immutable tables, ±5 % for tables with ON DELETE CASCADE)	Large drift → data loss
Constraint check	SET CONSTRAINTS ALL IMMEDIATE; SELECT * FROM pg_constraint WHERE NOT convalidated;	Deferred constraints left invalid
Business rule queries	Parameterized SQL files under tests/migration/rules/ (e.g., order_total_matches_items.sql)	Non‑zero rows = rule violation
Performance baseline	EXPLAIN ANALYZE on a handful of critical queries; compare to stored baseline (JSON)	>20 % regression in total cost

All validation steps return non‑zero exit codes on failure, so the CI job fails fast.

---

6. Worked Example: Migrating orders from a Monolith to a Micro‑service Schema

6.1 Source Schema (simplified)

CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE orders (
  id            BIGSERIAL PRIMARY KEY,
  customer_id   BIGINT NOT NULL REFERENCES customers(id),
  status        TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
  total_cents   BIGINT NOT NULL CHECK (total_cents >= 0),
  placed_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE order_items (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  sku         TEXT NOT NULL,
  qty         INT NOT NULL CHECK (qty > 0),
  unit_price  BIGINT NOT NULL CHECK (unit_price >= 0)
);

6.2 Target Schema (micro‑service)

• orders table gains currency CHAR(3) NOT NULL DEFAULT 'USD'.
• order_items splits unit_price into unit_price_cents BIGINT and currency CHAR(3).
• New table order_events (append‑only audit log) with FK to orders.

6.3 Generation Steps

1. Reference data – customers (10 k rows) seeded from masked production dump.
2. Synthetic orders – QA3 reads the DDL, sees FK to customers, generates 1 M orders with realistic status distribution (70 % paid, 15 % shipped, 10 % new, 5 % cancelled).
3. Order items – For each order, generate 1‑5 items; sku drawn from a 5 k‑row product catalog (also masked).
4. Manifest – manifest.json records: orders: 1_000_000, order_items: 3_400_000, seed a1b2c3d4.

6.4 Migration Script (Flyway V10__split_prices.sql)

-- 1. Add new columns
ALTER TABLE orders ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';
ALTER TABLE order_items ADD COLUMN unit_price_cents BIGINT;
ALTER TABLE order_items ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';


-- 2. Backfill
UPDATE order_items
SET unit_price_cents = unit_price,
    currency = (SELECT currency FROM orders WHERE id = order_id);


-- 3. Drop old column
ALTER TABLE order_items DROP COLUMN unit_price;


-- 4. Create audit table
CREATE TABLE order_events (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  event_type  TEXT NOT NULL,
  payload     JSONB NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

6.5 Validation Queries

-- tests/migration/rules/order_total_matches_items.sql
SELECT o.id,
       o.total_cents,
       SUM(oi.qty * oi.unit_price_cents) AS calc_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total_cents
HAVING o.total_cents <> SUM(oi.qty * oi.unit_price_cents);

-- tests/migration/rules/currency_consistency.sql
SELECT o.id
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.currency <> oi.currency;

Both queries must return zero rows after migration.

6.6 Expected Outcome

Metric	Pre‑migration	Post‑migration	Pass/Fail
orders row count	1 000 000	1 000 000	✅
order_items row count	3 400 000	3 400 000	✅
order_events row count	0	0 (populated later by app)	✅
order_total_matches_items violations	0	0	✅
currency_consistency violations	0	0	✅
EXPLAIN ANALYZE on SELECT * FROM orders WHERE status='paid' LIMIT 100	12 ms	13 ms	✅ (≤20 % regression)

If any row appears in the violation queries, the CI job fails and the manifest lets you replay the exact data set locally.

---

7. Common Failure Modes & Mitigations

Failure Mode	Symptom	Root Cause	Mitigation
FK violation during synthetic generation	ERROR: insert or update on table "orders" violates foreign key constraint	Generator produced `customer