How to Create Test Data Without Copying Production Records

Generating realistic, safe, and maintainable test data is a core skill for any QA engineer. This guide walks through the decisions, tooling, and validation steps you need to build a repeatable data‑creation pipeline—without ever pulling a row from production.

---

1. Why “Just Copy Production” Is a Bad Idea

Risk	What Happens	Typical Impact
Data‑privacy breach	PII, payment info, health records leave the controlled environment	Legal fines, loss of customer trust
Schema drift	Production adds columns, changes types, drops tables	Tests break silently or pass with stale assumptions
Volume mismatch	Production holds millions of rows; test env holds thousands	Performance tests give false confidence
State coupling	Tests depend on a specific order ID that later disappears	Flaky tests, hard‑to‑debug failures
Regulatory non‑compliance	GDPR, CCPA, HIPAA forbid using real data in non‑prod	Audit findings, mandatory remediation

Bottom line: Copying production is a shortcut that creates long‑term technical debt and legal exposure. The goal is synthetic data that mimics the shape, distribution, and relationships of real data—without any real records.

---

2. Prerequisites Before You Generate Anything

Prerequisite	How to Verify	Why It Matters
Schema source of truth	Version‑controlled DDL (SQL, Prisma, Django migrations, OpenAPI)	Guarantees generators stay in sync with the DB
Data‑model documentation	ER diagram, data‑dictionary, or a living markdown file	Helps you decide which columns need realistic values vs. placeholders
Constraint inventory	Primary keys, foreign keys, unique indexes, check constraints, triggers	Prevents generation of rows that violate DB rules
Distribution baselines	Histograms or quantiles for key columns (e.g., order_total, user_age)	Lets you tune generators to realistic spreads
Environment parity	Same DB engine, version, and extensions as production	Avoids “works on MySQL 8, fails on Postgres 13” surprises
CI/CD hook point	A stage that can run a script or container before test execution	Makes data generation part of the pipeline, not a manual step

Tip: Store the schema and constraint inventory in a single repo (e.g., db/). Treat it like code—review, test, and version it.

---

3. Decision Matrix: Choosing a Generation Strategy

Strategy	When It Shines	Tooling Examples	Main Trade‑offs
Pure synthetic (random + rules)	Greenfield projects, unit/integration tests, need full control	factory_boy, Faker, go-faker, QA3 Test Data Generator (/tools/test-data-generator)	Requires explicit rule authoring; may miss hidden correlations
Statistical modeling (copulas, GANs, CTGAN)	Large existing datasets where you can train a model offline	sdv, ydata-synthetic, MOSTLY AI	Heavy compute, model drift, black‑box debugging
Data masking / anonymization	You must keep some production shape (e.g., exact FK graph) but cannot expose PII	pg_anonymizer, DataVeil, Tonic	Still touches production data; requires secure pipeline
Subset + synthetic enrichment	Legacy systems where a small production slice is acceptable after masking	Custom scripts + masking tools	Complexity of keeping referential integrity across subsets
Fixture / snapshot files	Very small, static test suites (e.g., contract tests)	JSON/YAML fixtures, pytest-fixture	Not scalable; brittle when schema changes

Rule of thumb: Start with pure synthetic generation. Add statistical modeling only when you have a proven need for production‑like distributions (e.g., load‑testing a recommendation engine).

---

4. Workflow: From Schema to Ready‑to‑Run Test Data

flowchart TD
    A[Schema & Constraints] --> B[Define Generation Rules]
    B --> C[Run Generator (CI)]
    C --> D[Validate Output]
    D -->|Pass| E[Load into Test DB]
    D -->|Fail| B
    E --> F[Run Test Suite]

4.1 Define Generation Rules

1. Map each column → generator

   • Primitive types → Faker providers (name, email, uuid, date_time).
   • Enums / status fields → weighted choice ([('new',0.6),('shipped',0.3),('cancelled',0.1)]).
   • Numeric ranges → distribution (uniform, normal, log‑normal).
   • Foreign keys → reference another generated table (see 4.2).

2. Express cross‑column constraints

   • order_total = sum(line_items.price * qty) → post‑generation calculator.
   • shipping_date >= order_date → conditional generator.

3. Version the rule file (YAML/JSON) alongside schema migrations.

4.2 Preserve Referential Integrity

Approach	Description	Example
Topological order	Generate parent tables first, then children using the IDs just created.	users → addresses → orders → order_items
Deferred FK resolution	Generate all rows with placeholder UUIDs, then run a second pass to replace placeholders with real IDs.	Useful when cycles exist (e.g., manager_id on employees).
Deterministic IDs	Use a hash of a natural key (md5(email+role)) so the same logical row always gets the same PK across runs.	Guarantees repeatable test data for snapshot testing.

4.3 Run Generator in CI

# .github/workflows/test-data.yml


name: Generate Test Data
on:
  push:
    paths:
      - 'db/**'
      - 'test-data/rules/**'
jobs:
  generate:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_PASSWORD: pwd
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      - name: Install generator
        run: pip install qa3-test-data-generator   # <-- QA3 free tool
      - name: Generate
        run: |
          qa3-test-data-gen \
            --schema db/schema.sql \
            --rules test-data/rules.yaml \
            --out test-data/generated.sql
      - name: Load into test DB
        run: psql -h localhost -U postgres -d testdb -f test-data/generated.sql
      - name: Run test suite
        run: pytest -q

Why CI?

• Guarantees the same data for every PR.
• Catches schema‑rule drift early (failed validation stops the pipeline).

4.4 Validate Output

Validation Layer	Tool / Technique	What It Catches
Schema conformance	pg_dump --schema-only diff, or sqlfluff lint	Missing columns, type mismatches
Constraint satisfaction	Run SET CONSTRAINTS ALL IMMEDIATE; then SELECT * FROM information_schema.table_constraints	FK violations, duplicate PKs
Statistical sanity	Custom script: compare histograms of generated vs. baseline (Kolmogorov‑Smirnov test)	Skewed distributions, out‑of‑range values
Business rule checks	Assert order_total == sum(line_items) in a post‑load SQL script	Logic bugs in generators
Performance smoke	EXPLAIN ANALYZE SELECT * FROM orders WHERE status='shipped';	Missing indexes, data volume issues

Automate all of the above as a single validation job that fails fast.

---

5. Worked Example: E‑Commerce Order Domain

5.1 Schema (PostgreSQL)

CREATE TABLE users (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT NOT NULL UNIQUE,
    full_name     TEXT NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    role          TEXT NOT NULL CHECK (role IN ('customer','admin'))
);


CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    price_cents INT NOT NULL CHECK (price_cents > 0),
    active      BOOLEAN NOT NULL DEFAULT true
);


CREATE TABLE orders (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES users(id),
    status        TEXT NOT NULL CHECK (status IN ('new','paid','shipped','cancelled')),
    total_cents   INT NOT NULL CHECK (total_cents >= 0),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    shipped_at    TIMESTAMPTZ
);


CREATE TABLE order_items (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id    UUID NOT NULL REFERENCES orders(id),
    product_id  UUID NOT NULL REFERENCES products(id),
    qty         INT NOT NULL CHECK (qty > 0),
    unit_price_cents INT NOT NULL CHECK (unit_price_cents > 0)
);

5.2 Generation Rules (YAML)

# test-data/rules.yaml


tables:
  users:
    count: 500
    columns:
      email:        "{{unique.email}}"
      full_name:    "{{name}}"
      role:         "{{choice: [customer:0.95, admin:0.05]}}"
      created_at:   "{{date_time_between: ['-2y', 'now']}}"


products:
    count: 200
    columns:
      sku:          "{{bothify: 'SKU-####-??'}}"   # unique enforced by DB
      name:         "{{commerce.product_name}}"
      price_cents:  "{{random_int: [100, 50000]}}"   # $1 – $500
      active:       "{{choice: [true:0.9, false:0.1]}}"


orders:
    count: 1000
    columns:
      user_id:      "{{ref: users.id}}"               # FK reference
      status:       "{{choice: [new:0.2, paid:0.5, shipped:0.25, cancelled:0.05]}}"
      created_at:   "{{date_time_between: ['-1y', 'now']}}"
      shipped_at:   "{{conditional: 
                         if: 'status == \"shipped\"'
                         then: '{{date_time_between: [created_at, now]}}'
                         else: 'NULL'}}"
      # total_cents will be calculated in post‑process step


order_items:
    count: 3000
    columns:
      order_id:     "{{ref: orders.id}}"
      product_id:   "{{ref: products.id}}"
      qty:          "{{random_int: [1,5]}}"
      unit_price_cents: "{{ref: products.price_cents}}"

5.3 Post‑Process Script (Python)

# test-data/post_process.py


import sqlalchemy as sa
from sqlalchemy.orm import Session


engine = sa.create_engine("postgresql://postgres:pwd@localhost/testdb")
with Session(engine) as sess:
    # 1. Compute order totals
    sess.execute(sa.text("""
        UPDATE orders o
        SET total_cents = sub.total
        FROM (
            SELECT order_id, SUM(qty * unit_price_cents) AS total
            FROM order_items
            GROUP BY order_id
        ) sub
        WHERE o.id = sub.order_id;
    """))
    # 2. Enforce shipped_at only for shipped orders
    sess.execute(sa.text("""
        UPDATE orders
        SET shipped_at = NULL
        WHERE status <> 'shipped';
    """))
    sess.commit()

5.4 Validation Checks (SQL)

-- 1. No orphan order_items
SELECT COUNT(*) FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;   -- expect 0


-- 2. Order total matches line items
SELECT o.id
FROM orders o
JOIN (
    SELECT order_id, SUM(qty * unit_price_cents) AS calc_total
    FROM order_items
    GROUP BY order_id
) li ON o.id = li.order_id
WHERE o.total_cents <> li.calc_total;   -- expect 0 rows


-- 3. Status distribution sanity
SELECT status, COUNT(*) FROM orders GROUP BY status;

Running the CI pipeline with the above artifacts produces a fully referentially‑intact, statistically‑reasonable dataset in ~30 seconds on a modest GitHub Actions runner.

---

6. Common Failure Modes & How to Detect Them Early

Failure Mode	Symptom	Detection Point	Mitigation
Rule drift (schema changed, rules not updated)	INSERT errors on new NOT NULL column	CI validation job (schema conformance)	Enforce schema‑rule PR gate: any migration must include a rule diff
FK cycles (e.g., manager_id on employees)	Generator hangs or produces NULL FK	Topological sort step logs “cycle detected”	Use deferred resolution or deterministic UUIDs
Distribution collapse (all status = 'new')	Load test shows unrealistic throughput	Statistical sanity check (KS test)	Add weighted choices; periodically re‑sample from production histograms (offline)
Data volume explosion (10 M rows generated for unit tests)	CI timeout, OOM	Row‑count guard in generator config (max_rows_per_table)	Separate unit vs load data profiles
Non‑deterministic IDs causing flaky snapshot tests	Snapshot diff on every run	Deterministic ID rule (hash(email+role))	Lock IDs for snapshot suites; keep random for exploratory tests
Masking leakage (real email appears in synthetic data)	Security audit flags PII	Post‑generation PII scanner (regex for email, SSN)	Never feed production data into generator; use only schema + rules

---

7. Scaling the Approach

Scale	Recommended Adjustments
< 10 tables, < 5 k rows	Single YAML rule file, run in CI container
10‑100 tables, 100 k‑1 M rows	Split rules per domain, parallelize generation with make -j or airflow DAG
> 100 tables, > 1 M rows	Use a dedicated data‑factory service (e.g., a small Flask app that streams rows via COPY), store generated CSV/Parquet in object storage, load with COPY FROM
Multi‑region test clusters	Generate once, snapshot the DB (pg_basebackup / RDS snapshot), share snapshot across regions

---

8. Checklist: Before You Ship a New Data‑Generation Pipeline

• Schema and constraints are version‑controlled and reviewed.
• Generation rules cover every column (no “default” fall‑backs).
• Referential integrity strategy documented (topological vs deferred).
• Deterministic IDs for any snapshot‑tested tables.
• Post‑generation validation job runs every CI build.
• Statistical sanity thresholds defined (e.g., KS‑p > 0.05).
• PII scanner integrated in validation step.
• Separate unit and load data profiles with row‑count limits.
• Documentation includes “how to add a new table” and “how to tweak a distribution”.
• [