AI Test Data Generation from Database Schemas

Turning a schema into realistic, referentially‑correct test data without writing a single INSERT statement.

---

Why the schema‑first approach matters

Traditional approach	Schema‑first AI approach
Hand‑crafted CSV/JSON files, often stale	One source of truth – the live DDL
Foreign‑key violations discovered late	Referential integrity enforced by the generator
Data‑shape mismatches (e.g., VARCHAR(20) vs. 200‑char strings)	Column constraints (length, NOT NULL, CHECK) baked in
Hard to spin up a fresh environment for each test run	Deterministic, repeatable data sets on demand

When the database schema is the single source of truth, any change – a new column, a tightened CHECK, a cascading delete – propagates automatically to the test data. That eliminates a whole class of “data‑drift” bugs that only surface in staging.

---

Decision criteria – should you adopt AI‑driven generation?

Criterion	Yes → adopt	No → stay manual
Schema stability – DDL changes < once per sprint	✅	❌
Test‑data volume – > 10 k rows per run	✅	❌
Referential depth – > 3 FK levels	✅	❌
Regulatory constraints – PII must be masked	✅ (AI can apply masking rules)	❌
Team skill‑set – at least one engineer comfortable with prompt engineering / API integration	✅	❌
Budget for tooling – free tier or low‑cost SaaS acceptable	✅	❌

If you tick four or more of the “Yes” boxes, the ROI of an AI generator typically appears within the first two sprints.

---

End‑to‑end workflow

1️⃣  Export DDL (SQL, GraphQL SDL, Prisma, etc.)
2️⃣  Feed DDL + generation policy to the AI engine
3️⃣  Review the generated data‑profile (counts, distributions)
4️⃣  Run validation suite (FK, CHECK, uniqueness, domain rules)
5️⃣  Store artefacts (SQL dump, CSV, Parquet) in CI artefact store
6️⃣  Consume in test pipelines (DB seed, API mock, contract test)
7️⃣  Close the loop – feed test‑run metrics back to policy

Key artefacts

Artefact	Format	Where it lives
DDL snapshot	.sql / .graphql	repo/db/schema/
Generation policy	YAML/JSON	repo/qa/data-gen/policy.yaml
Data profile	JSON	CI artefact data-profile.json
Seed files	.sql / .csv / .parquet	repo/qa/data-gen/seeds/
Validation report	JUnit XML / HTML	CI job validate-data

---

Worked example – an e‑commerce schema

1. DDL (PostgreSQL)

CREATE TABLE customers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(120) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    status      VARCHAR(20) NOT NULL CHECK (status IN ('active','suspended','deleted'))
);


CREATE TABLE addresses (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id  UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    line1        VARCHAR(200) NOT NULL,
    line2        VARCHAR(200),
    city         VARCHAR(100) NOT NULL,
    state        VARCHAR(50) NOT NULL,
    postal_code  VARCHAR(20) NOT NULL,
    country      CHAR(2) NOT NULL,
    is_default   BOOLEAN NOT NULL DEFAULT FALSE
);


CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku         VARCHAR(50) NOT NULL UNIQUE,
    name        VARCHAR(200) NOT NULL,
    price_cents INT NOT NULL CHECK (price_cents > 0),
    currency    CHAR(3) NOT NULL DEFAULT 'USD',
    active      BOOLEAN NOT NULL DEFAULT TRUE
);


CREATE TABLE orders (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id   UUID NOT NULL REFERENCES customers(id),
    address_id    UUID NOT NULL REFERENCES addresses(id),
    placed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    status        VARCHAR(20) NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled','returned')),
    total_cents   INT NOT NULL CHECK (total_cents >= 0)
);


CREATE TABLE order_items (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id    UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    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)
);

2. Generation policy (YAML)

# repo/qa/data-gen/policy.yaml


tables:
  customers:
    rows: 5000
    columns:
      email:
        type: "email"
        unique: true
      full_name:
        type: "person_name"
      status:
        type: "choice"
        weights: {active: 0.85, suspended: 0.10, deleted: 0.05}
  addresses:
    rows_per_parent: 1.2          # ~1.2 addresses per customer
    columns:
      line1:      {type: "street_address"}
      line2:      {type: "secondary_address", nullable: true, null_ratio: 0.4}
      city:       {type: "city"}
      state:      {type: "state_abbr"}
      postal_code:{type: "postcode", locale: "US"}
      country:    {type: "constant", value: "US"}
      is_default: {type: "boolean", true_ratio: 0.3}
  products:
    rows: 2000
    columns:
      sku:        {type: "regex", pattern: "[A-Z]{3}-\\d{4}"}
      name:       {type: "product_name"}
      price_cents:{type: "int_range", min: 100, max: 50000}
      currency:   {type: "constant", value: "USD"}
      active:     {type: "boolean", true_ratio: 0.9}
  orders:
    rows_per_parent: 3                # 3 orders per customer
    columns:
      placed_at: {type: "datetime_range", start: "-2y", end: "now"}
      status:
        type: "choice"
        weights: {pending:0.05, paid:0.6, shipped:0.25, cancelled:0.07, returned:0.03}
      total_cents: {type: "derived", expr: "sum(order_items.qty * order_items.unit_price_cents)"}
  order_items:
    rows_per_parent: 2.5              # 2‑3 lines per order
    columns:
      qty: {type: "int_range", min: 1, max: 5}
      unit_price_cents: {type: "copy_from", table: "products", column: "price_cents"}

3. Running the generator (CLI)

# Using QA3’s free test‑data generator (no auth required)


qa3 generate \
  --ddl repo/db/schema/ecommerce.sql \
  --policy repo/qa/data-gen/policy.yaml \
  --out repo/qa/data-gen/seeds/ \
  --format sql,parquet

The command produces:

• customers.sql – 5 000 INSERT statements
• addresses.parquet – 6 000 rows (1.2 × customers)
• products.sql – 2 000 rows
• orders.parquet – 15 000 rows (3 × customers)
• order_items.parquet – 37 500 rows (2.5 × orders)

4. Validation suite (pytest + SQLAlchemy)

# tests/validate_data.py


import pytest
from sqlalchemy import create_engine, inspect, text


engine = create_engine("postgresql://qa:qa@localhost/qa_db")


FK_CHECKS = [
    ("addresses", "customer_id", "customers", "id"),
    ("orders", "customer_id", "customers", "id"),
    ("orders", "address_id", "addresses", "id"),
    ("order_items", "order_id", "orders", "id"),
    ("order_items", "product_id", "products", "id"),
]


@pytest.mark.parametrize("child,child_col,parent,parent_col", FK_CHECKS)
def test_foreign_keys(child, child_col, parent, parent_col):
    with engine.connect() as conn:
        orphan = conn.execute(text(f"""
            SELECT COUNT(*) FROM {child} c
            LEFT JOIN {parent} p ON c.{child_col}=p.{parent_col}
            WHERE p.{parent_col} IS NULL
        """)).scalar()
    assert orphan == 0, f"{orphan} orphan rows in {child}.{child_col}"


def test_check_constraints():
    with engine.connect() as conn:
        bad_status = conn.execute(text("""
            SELECT COUNT(*) FROM customers
            WHERE status NOT IN ('active','suspended','deleted')
        """)).scalar()
    assert bad_status == 0


def test_unique_email():
    with engine.connect() as conn:
        dup = conn.execute(text("""
            SELECT COUNT(*) FROM (
                SELECT email, COUNT(*) c FROM customers GROUP BY email HAVING COUNT(*)>1
            ) t
        """)).scalar()
    assert dup == 0

Running pytest -q tests/validate_data.py yields a green suite in < 30 seconds on a modest CI runner.

5. Data‑profile snapshot (JSON)

{
  "customers": {"rows": 5000, "unique_email": 5000, "status_distribution": {"active":4250,"suspended":500,"deleted":250}},
  "addresses": {"rows": 6000, "default_ratio": 0.298},
  "products": {"rows": 2000, "active_ratio": 0.902},
  "orders": {"rows": 15000, "status_distribution": {"pending":750,"paid":9000,"shipped":3750,"cancelled":1050,"returned":450}},
  "order_items": {"rows": 37500, "avg_qty": 2.48}
}

The profile is archived as a CI artefact and compared against the policy on every run – any drift > 5 % triggers a failure.

---

Tool considerations

Feature	Open‑source (e.g., DataSynthesizer, Mimesis)	Commercial SaaS (e.g., Tonic, Synthesized)	QA3 free generator (/tools/test-data-generator)
Schema ingestion	SQL DDL, CSV, JSON Schema	DB connection, ORM models, GraphQL SDL	Direct DDL paste or file upload
Policy language	Python code / YAML	UI + YAML	YAML (as shown above)
Referential integrity	Manual FK wiring	Automatic FK graph	Automatic (detects FK from DDL)
PII masking	Plug‑in scripts	Built‑in classifiers	Built‑in type: "email" → synthetic, type: "ssn" → masked
Deterministic seeds	Seed value per run	Seed value per run	--seed 42 flag
Output formats	CSV, Parquet, SQL	CSV, Parquet, Avro, DB dump	SQL, CSV, Parquet
CI/CD integration	CLI only	CLI + GitHub Actions / GitLab CI	CLI + GitHub Action (example in repo)
Cost	Free (maintenance on you)	$0.10‑$0.30 per 1 k rows	Free tier up to 100 k rows / month
Support	Community	SLA, dedicated CS	Community forum, docs

When to pick QA3’s free generator

• You need a zero‑cost proof‑of‑concept before committing budget.
• Your schema lives in a single DDL file (no live DB connection required).
• You want YAML‑only policy – no Python boilerplate.
• You prefer a single CLI that emits both SQL and Parquet for downstream consumers.

If you later need advanced features (e.g., differential privacy, multi‑tenant data isolation), the migration path to a paid platform is straightforward because the policy file is portable.

---

Validation checklist – run before every seed promotion

• FK closure – zero orphan rows (automated test).
• CHECK constraints – all column‑level predicates satisfied.
• UNIQUE columns – no duplicate values (email, SKU, etc.).
• Domain ranges – numeric columns within min/max, dates within window.
• Distribution sanity – status ratios, boolean ratios within ±5 % of policy.
• PII masking – no real email, phone, SSN, credit‑card patterns appear.
• Determinism – same seed → identical row hashes (MD5 of each table dump).
• Size budget – total seed size < CI artefact limit (e.g., 500 MB).
• Schema version tag – seed artefacts labelled with git describe --tags.

Automate the checklist as a GitHub Actions job named validate-seed. Fail the workflow on any red item; the pipeline stops before the seed reaches staging.

---

Common pitfalls & mitigations

Pitfall	Symptom	Root cause	Mitigation
Schema drift	Validation fails after a migration	DDL changed but policy not updated	Add a pre‑commit hook that runs qa3 lint --ddl <file> --policy <policy>
Over‑fitting to policy	Test data looks “too perfect” (e.g., every order has exactly 2 items)	rows_per_parent set to a constant	Use distribution types (poisson, negative_binomial) instead of fixed ratios
Circular FK loops	Generator hangs or emits duplicate rows	Self‑referencing tables (e.g., employee.manager_id)	Declare generation_order in policy; generate parents first, then children
Large BLOB/TEXT columns	Seed size explodes	type: "lorem" with default 5 KB per row	Cap with max_length or switch to type: "null" for non‑essential columns
Non‑deterministic randomness	Same seed produces different data across runs	Library version upgrade changed RNG	Pin generator version in requirements.txt / Docker image
Missing NOT NULL	Nulls appear in mandatory columns	Policy omitted nullable: false	Enforce nullable: false as default; generator raises error if violated
Time‑zone mismatch	placed_at values off by hours	DB session TZ ≠ generator TZ	Explicitly set timezone: "UTC" in policy or pass --tz UTC

---

Scaling the approach

1. Modular policies – split policy.yaml per bounded context (catalog, checkout, loyalty).
2. Parameterised seeds – expose --env dev|-- flag; dev gets 1 k rows, staging 100 k, perf 1 M.
3. Data‑contract testing – feed generated JSON to Pact/Schemathesis to verify API contracts.
4. Feedback loop – capture production