AI Test Data Generation with Schema Constraints

Generating realistic test data is one of the most time‑consuming parts of a QA workflow. When the data must obey a strict schema—foreign‑key relationships, enum values, length limits, custom validation rules—the problem compounds. Traditional approaches (hand‑crafted CSV files, static fixtures, or simple random generators) either break the schema or produce data that never exercises the edge cases that matter.

Large language models (LLMs) and purpose‑built synthetic‑data engines now let you describe the schema in natural language or a formal definition (JSON Schema, OpenAPI, Protobuf, SQL DDL) and ask the model to emit rows that satisfy every constraint. The result is schema‑aware data that can be regenerated on demand, version‑controlled, and tied to a specific test‑case ID.

This post walks through the decision criteria, a repeatable workflow, a worked example, common pitfalls, and a practical next step you can take today.

---

1. Why Schema‑Constrained Generation Matters

Pain point	Traditional fix	Why it falls short
Referential integrity (FK, composite keys)	Hand‑written SQL scripts	Brittle; every schema change rewrites scripts
Business‑rule validation (e.g., start_date < end_date, status = 'closed' → resolution != null)	Custom code generators	Duplicates logic; hard to keep in sync with production code
Data‑privacy compliance (PII masking, GDPR)	Production‑data snapshots + anonymisation scripts	Residual risk; anonymisation often breaks constraints
Test‑case traceability	Static CSV fixtures	No link between a row and the requirement it exercises

When the generator knows the schema, you get:

• Zero‑drift – the same definition drives both the application and the test data.
• On‑demand scale – 10 rows for a unit test, 10 M rows for a load test, same command.
• Deterministic replay – seed the generator, store the seed, reproduce the exact dataset later.

---

2. Decision Criteria: Choosing the Right Tool

Criterion	What to look for	Weight (1‑5)
Schema input format	JSON Schema, OpenAPI, GraphQL SDL, SQL DDL, Protobuf	5
Constraint expressiveness	Conditional rules, cross‑field validation, custom functions	5
Determinism & seeding	Fixed seed → identical output; ability to store seed per test run	4
Extensibility	Plug‑in language (Python, JS, Go) for domain‑specific generators	4
Integration	CLI, CI/CD plugins (GitHub Actions, GitLab CI, Azure Pipelines), API	4
Performance	Parallel generation, streaming output, memory footprint	3
Governance	Audit log, schema versioning, role‑based access	3
Cost / licensing	Open‑source core, paid enterprise features, SaaS pricing model	2

Score each candidate (e.g., Synthesia, Tonic, Mockaroo, DataSynthesizer, QA3 Test Data Generator) against the table. A total > 30 usually indicates a good fit for a mid‑size team; > 38 for enterprise‑grade pipelines.

Quick tip: If you already use a contract‑testing tool (Pact, Spring Cloud Contract), reuse its schema artefacts as the single source of truth for data generation.

---

3. End‑to‑End Workflow

┌─────────────────────┐
│ 1. Capture schema   │  ← JSON Schema / OpenAPI / SQL DDL
└───────┬─────────────┘
        │
        ▼
┌─────────────────────┐
│ 2. Enrich with      │  ← Business rules, enums, value ranges,
│    constraints      │     cross‑field logic, PII policies
└───────┬─────────────┘
        │
        ▼
┌─────────────────────┐
│ 3. Author generator │  ← Prompt / config / code (see §4)
│    definition       │
└───────┬─────────────┘
        │
        ▼
┌─────────────────────┐
│ 4. Validate output  │  ← Automated schema validation + rule engine
│    (CI gate)        │
└───────┬─────────────┘
        │
        ▼
┌─────────────────────┐
│ 5. Store artefacts  │  ← Seed, generated files, checksum, test‑case ID
│    & metadata       │
└───────┬─────────────┘
        │
        ▼
┌─────────────────────┐
│ 6. Consume in tests │  ← Load into DB, feed API, mount as fixtures
└─────────────────────┘

Automation hooks

• Pre‑commit – run a fast “dry‑run” (10 rows) to catch schema drift early.
• CI pipeline – full generation + validation as a gate before deployment.
• Nightly – large‑scale generation for performance suites, archive artefacts to object storage.

---

4. Worked Example: E‑Commerce Order Domain

4.1 Schema (JSON Schema)

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "Order",
  "type": "object",
  "required": ["orderId","customerId","items","status","createdAt"],
  "properties": {
    "orderId":      { "type": "string", "format": "uuid" },
    "customerId":   { "type": "string", "format": "uuid" },
    "items": {
      "type": "array",
      "minItems": 1,
      "maxItems": 20,
      "items": { "$ref": "#/definitions/OrderItem" }
    },
    "status":       { "type": "string", "enum": ["pending","paid","shipped","cancelled","returned"] },
    "createdAt":    { "type": "string", "format": "date-time" },
    "shippedAt":    { "type": ["string","null"], "format": "date-time" },
    "totalAmount":  { "type": "number", "minimum": 0, "multipleOf": 0.01 }
  },
  "definitions": {
    "OrderItem": {
      "type": "object",
      "required": ["productId","quantity","unitPrice"],
      "properties": {
        "productId": { "type": "string", "format": "uuid" },
        "quantity":  { "type": "integer", "minimum": 1, "maximum": 99 },
        "unitPrice": { "type": "number", "minimum": 0.01, "multipleOf": 0.01 }
      }
    }
  },
  "allOf": [
    {
      "if":   { "properties": { "status": { "const": "shipped" } } },
      "then": { "required": ["shippedAt"] },
      "else": { "properties": { "shippedAt": { "const": null } } }
    },
    {
      "if":   { "properties": { "status": { "const": "cancelled" } } },
      "then": { "not": { "required": ["shippedAt"] } }
    }
  ]
}

Key constraints captured

• orderId / customerId / productId are UUIDs.
• items array length 1‑20.
• status enum + conditional shippedAt requirement.
• totalAmount must equal Σ(quantity × unitPrice) – a cross‑field rule not expressible in pure JSON Schema.

4.2 Enriching with Business Rules

Rule	Expression (pseudo‑code)
totalAmount = sum(items[i].quantity * items[i].unitPrice)	total = reduce(items, (acc, it) => acc + it.quantity * it.unitPrice, 0)
createdAt ≤ shippedAt (when present)	if shippedAt then createdAt <= shippedAt else true
No duplicate productId within a single order	unique(items.map(i => i.productId))
PII: customerId must be a synthetic UUID, never a production value	generator.uuid({namespace: 'test'})

4.3 Generator Definition (YAML for QA3 Test Data Generator)

schema: ./order.schema.json
seed: "{{ env.TEST_SEED | default('42') }}"
output:
  format: ndjson
  path: "generated/orders_{{ seed }}.ndjson"
records: 5000
constraints:
  - name: total_amount_match
    type: expression
    language: python
    code: |
      total = sum(i['quantity'] * i['unitPrice'] for i in record['items'])
      assert abs(record['totalAmount'] - total) < 0.005
  - name: shipped_after_created
    type: expression
    language: python
    code: |
      if record.get('shippedAt'):
          assert record['createdAt'] <= record['shippedAt']
  - name: unique_products
    type: expression
    language: python
    code: |
      ids = [i['productId'] for i in record['items']]
      assert len(ids) == len(set(ids))

Why this works

• The schema guarantees structural validity.
• The constraints block adds cross‑field logic in a language the team already knows (Python).
• The seed makes the run reproducible; the seed value is stored alongside the artefacts for traceability.

4.4 Running the Generation

# Install the CLI (once)


pip install qa3-test-data-generator


# Generate


qa3-tdg generate \
  --config order-gen.yaml \
  --seed 20240315 \
  --records 10000 \
  --output generated/orders_20240315.ndjson

Typical output (first 2 lines):

{"orderId":"3f2a1c9e-7b4d-4f1a-9c2e-6d8f3a1b9c4d","customerId":"a1b2c3d4-e5f6-7890-abcd-ef1234567890","items":[{"productId":"d4e5f6a7-b8c9-0123-def4-567890abcdef","quantity":3,"unitPrice":19.99}],"status":"paid","createdAt":"2024-01-12T08:34:12Z","shippedAt":null,"totalAmount":59.97}
{"orderId":"9c8b7a6d-5e4f-3a2b-1c0d-9e8f7a6b5c4d","customerId":"f0e1d2c3-b4a5-6789-0fed-cba987654321","items":[{"productId":"11223344-5566-7788-99aa-bbccddeeff00","quantity":1,"unitPrice":149.99},{"productId":"ffeeddcc-bbaa-9988-7766-554433221100","quantity":2,"unitPrice":9.95}],"status":"shipped","createdAt":"2024-01-13T14:02:45Z","shippedAt":"2024-01-14T09:10:00Z","totalAmount":169.89}

4.5 Validation Gate (CI)

# .github/workflows/test-data.yml


name: Test Data Validation
on: [push, pull_request]
jobs:
  validate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v5
        with: { python-version: '3.11' }
      - name: Install generator
        run: pip install qa3-test-data-generator
      - name: Generate sample (10 rows)
        run: qa3-tdg generate --config order-gen.yaml --records 10 --seed ${{ github.sha }}
      - name: Validate schema & constraints
        run: qa3-tdg validate --config order-gen.yaml --input generated/orders_${{ github.sha }}.ndjson

If any rule fails, the workflow stops—preventing bad data from contaminating downstream test runs.

---

5. Common Pitfalls & Mitigations

Pitfall	Symptom	Mitigation
Over‑constraining – rules that make the space empty	Generator hangs or throws “no solution”	Run a feasibility check (small sample) before full run; relax non‑critical rules.
Schema drift – production adds a column, generator still emits old shape	Tests pass locally but fail in staging	Add a schema‑diff step in CI (e.g., jsonschema-diff) that fails the build on mismatch.
Non‑deterministic LLMs – same prompt yields different rows	Flaky tests, impossible to replay	Pin model version, temperature = 0, and always supply a seed; prefer deterministic engines for CI.
Performance blow‑up – cross‑field constraints evaluated row‑by‑row in Python	Generation of 10 M rows takes hours	Push heavy calculations into the generator’s native expression language (SQL‑like) or pre‑compute lookup tables.
PII leakage – real customer IDs accidentally used	Compliance audit finding	Enforce a namespace flag on every UUID generator; run a nightly scan for known production patterns.
Version‑control bloat – committing massive NDJSON files	Repo size > 2 GB	Store artefacts in an object store (S3, GCS) and commit only the manifest (seed, checksum, row count).

---

6. Tool Landscape Snapshot (2024)

Tool	Schema input	Constraint language	Deterministic?	CI/CD native	License
QA3 Test Data Generator	JSON Schema, OpenAPI, SQL DDL	Python / JS expressions, built‑in functions	Yes (seed)	GitHub Actions, GitLab, Azure Pipelines	Free core, paid enterprise
Tonic.ai	DB introspection, CSV	SQL‑like DSL, custom Python	Yes (seed)	API + webhooks	Commercial
Synthesia	JSON Schema, Protobuf	TypeScript functions	Yes (seed)	CLI, Docker	Open‑source (Apache‑2)
Mockaroo	Web UI, CSV, JSON	Formula syntax, Ruby blocks	Partial (seed optional)	CLI, API	Freemium
DataSynthesizer	CSV + metadata	Differential privacy config	No (probabilistic)	Python lib	MIT

Takeaway: If you already maintain JSON Schema/OpenAPI contracts, QA3 Test Data Generator integrates with zero‑extra‑mapping. For teams with heavy DB‑centric workflows, Tonic’s introspection can save the schema‑authoring step.

---

7. Checklist: Ready‑to‑Run Generation

• Schema source is the single source of truth (version‑controlled).
• All business rules expressed as deterministic constraints (no hidden runtime logic).
• Seed strategy defined (per‑test‑case, per‑pipeline run, or global).
• Validation pipeline runs on every PR (schema + constraints).
• Artefact storage decided (repo for < 10 MB, object store otherwise).
• PII policy enforced in generator config (namespace, masking functions).
• Performance baseline measured (rows/sec) for the