On-Demand Test Data: Architecture and Tooling Options

On‑Demand Test Data: Architecture and Tooling Options

Testing modern services without realistic data is like trying to drive a car with a blindfold. Production‑like data uncovers schema drift, performance regressions, and edge‑case bugs that synthetic stubs never reveal. Yet provisioning that data on demand—fast, repeatable, and safe—remains a recurring pain point for QA teams. This post walks through the architectural patterns that make on‑demand test data possible, evaluates the tooling landscape, and gives you a concrete workflow you can adopt today.

---

1. Why “On‑Demand” Matters

Symptom	Root cause	On‑demand benefit
Tests wait hours for a refreshed staging dump	Centralised, infrequent data refresh cycles	Data spun up in seconds per test run
Flaky tests caused by stale references	Shared mutable datasets across teams	Isolated, version‑controlled snapshots per suite
Compliance blockers (PII, GDPR)	Production copies shipped to lower environments	Synthetic or masked data generated per policy
Capacity limits on shared test DBs	One large instance serving many pipelines	Ephemeral instances or in‑memory stores per job

The common thread: decouple data provisioning from environment provisioning. When data can be requested programmatically, CI/CD pipelines, local dev loops, and exploratory testing all gain the same fidelity without the operational drag.

---

2. Core Architectural Patterns

2.1. Generator‑Centric (Pure Synthetic)

• Idea – A stateless service or library produces rows on the fly from schemas, constraints, and business rules.
• Typical stack – Open‑source libraries (Faker, Hypothesis, Datagen), custom DSLs, or SaaS generators.
• Strengths – Zero storage, instant start‑up, easy to version‑control alongside code.
• Weaknesses – Hard to model deep referential integrity (e.g., order → line‑items → shipments) without a graph engine; statistical realism often limited.

2.2. Subset‑And‑Mask (Production‑Derived)

• Idea – Pull a statistically representative slice of production, then apply masking / tokenisation.
• Typical stack – Change‑data‑capture (CDC) pipelines → secure staging bucket → masking engine (Delphix, Tonic, custom scripts).
• Strengths – Real‑world distribution, foreign‑key fidelity, performance characteristics close to prod.
• Weaknesses – Requires access to production (or a recent replica), latency to refresh, governance overhead for PII handling.

2.3. Hybrid “Seed‑Then‑Grow”

• Idea – Load a minimal, masked seed set (core reference tables, a handful of master records) and let a generator expand it per test scenario.
• Typical stack – Seed DB (SQLite, Postgres, or in‑memory) + generator library + orchestrator (Airflow, Tekton, GitHub Actions).
• Strengths – Balances realism with speed; seed can be versioned; generator only needs to handle transactional data.
• Weaknesses – Requires maintaining seed snapshots; generator must understand domain constraints.

2.4. Service‑Virtualisation / Data‑As‑API

• Idea – Expose a thin HTTP/gRPC façade that returns canned or generated payloads. Tests hit the façade instead of a DB.
• Typical stack – WireMock, MockServer, or custom Go/Node services backed by a generator.
• Strengths – Decouples test execution from DB schema changes; works for contract testing across services.
• Weaknesses – Does not exercise persistence layer; limited for performance or migration testing.

---

3. Decision Matrix: Choosing a Pattern

Criteria	Generator‑Centric	Subset‑And‑Mask	Hybrid Seed‑Grow	Service‑Virtualisation
Speed to first row	< 1 s	Minutes‑hours (refresh)	< 5 s (seed load)	< 1 s
Referential integrity	Manual / graph engine	Native (prod)	Seed + generator rules	Mocked
PII compliance	By design (synthetic)	Masking required	Seed masked, rest synthetic	By design
Storage footprint	None	Full subset size	Seed size only	None
Maintenance effort	Schema‑driven code	CDC + masking pipelines	Seed versioning + generator	Contract maintenance
Best fit	Unit / contract / CI fast loops	Performance / load / migration	End‑to‑end functional suites	Contract / consumer‑driven tests

Use the matrix as a conversation starter with your platform team. Most organisations end up combining two patterns: a masked seed for reference data and a generator for transactional volume.

---

4. Tooling Landscape (2024‑2025 Snapshot)

Category	Representative Tools	Licensing	Notable Features
Open‑source generators	Faker.js, go-faker, Factory Boy, Hypothesis, Datagen	MIT/Apache	Language‑native, extensible via plugins
Schema‑driven DSLs	Schemathesis, OpenAPI Generator + custom templates, Prisma Seed	MIT/Apache	Generates from OpenAPI/GraphQL/Protobuf
Commercial synthetic platforms	Tonic, Synthesized, Gretel, Mockaroo (paid tier)	Proprietary	ML‑based distribution learning, UI for non‑coders
Subset‑and‑mask	Delphix, Tonic (masking mode), Redgate Data Masker, Custom CDC + pg_dump + data-masker	Proprietary / OSS	Policy‑driven masking, referential integrity preservation
Hybrid orchestrators	dbt + dbt-seed, Airflow + Great Expectations, Tekton + Kustomize	Apache / OSS	Version‑controlled seed, data contracts, lineage
Service virtualisation	WireMock, MockServer, Hoverfly, Pact Broker + provider stubs	Apache / MIT	Record/playback, stateful scenarios, contract verification
Free QA3 utility	Test Data Generator – https://qa3.io/tools/test-data-generator	Free (no auth)	Browser‑based, schema upload, instant CSV/JSON/SQL output, supports custom regex & foreign‑key graphs

Tip: The QA3 generator is handy for quick spikes—paste a CREATE TABLE DDL, hit Generate, and you get a downloadable file in seconds. It does not replace a pipeline‑grade solution but removes the “write a one‑off script” friction.

---

5. Worked Example: E‑Commerce Order Service

5.1. Domain Snapshot

Table	Key Columns	Business Rules
customers	id PK, email UNIQUE, tier ENUM('standard','premium')	Email format, tier distribution 80/20
products	id PK, sku UNIQUE, price_cents, stock_qty	Price > 0, stock ≥ 0
orders	id PK, customer_id FK, status ENUM, created_at	Status flow pending → paid → shipped → delivered
order_items	id PK, order_id FK, product_id FK, qty, unit_price_cents	qty ≤ product stock at order time
shipments	id PK, order_id FK, carrier, tracking_no, shipped_at	Exists only when order status = shipped

5.2. Chosen Pattern – Hybrid Seed‑Grow

1. Seed (version‑controlled, 200 rows)

   • 150 customers (masked emails, tier split)
   • 80 products (real SKUs, realistic price range)
   • 0 orders, order_items, shipments – generated per test run

2. Generator (Python + factory_boy + Faker)

   • Reads seed via a lightweight SQLite file mounted in the CI container.
   • For each test case:
     • Pick a random customer (weighted by tier).
     • Build a cart of 1‑5 products respecting stock.
     • Emit order + order_items + optional shipment.
   • Writes to a temporary Postgres instance spun up by testcontainers (or an in‑memory sqlite for unit tests).

3. Orchestration (GitHub Actions)

jobs:
  integration:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_DB: testdb
          POSTGRES_PASSWORD: pwd
        ports: ["5432:5432"]
        options: >-
          --health-cmd="pg_isready -U postgres"
          --health-interval=5s
          --health-timeout=3s
          --health-retries=5
    steps:
      - uses: actions/checkout@v4
      - name: Load seed
        run: |
          psql -h localhost -U postgres -d testdb -f seed/schema.sql
          psql -h localhost -U postgres -d testdb -f seed/data.sql
      - name: Run generator
        run: python -m tests.data_gen --target-db postgresql://postgres:pwd@localhost:5432/testdb
      - name: Execute test suite
        run: pytest -q tests/integration

Result: Each workflow run gets a fresh, referentially‑consistent dataset in ~12 s (seed load + generation). No production data leaves the CI network.

5.3. Validation Checklist (run after generation)

• Row counts match expected ranges (e.g., orders ≈ 1 000 per run).
• Foreign‑key integrity – SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id=c.id WHERE c.id IS NULL; returns 0.
• Business rule enforcement – No order_items.qty > products.stock_qty at order creation timestamp.
• Distribution sanity – tier split within ±5 % of 80/20.
• PII scan – Run grep -E "[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}" on exported CSV; expect only masked patterns (user+<id>@example.com).
• Performance baseline – EXPLAIN ANALYZE on the hottest query stays under 30 ms.

Automate the checklist as a post‑generation test (pytest -m data_validation) so a broken generator fails the pipeline early.

---

6. Common Pitfalls & Mitigations

Pitfall	Symptom	Mitigation
Generator drift – schema changes not reflected in code	Tests start failing with “column not found” or silent data corruption	Add a schema‑diff gate (e.g., sqlfluff + CI) that fails if DDL changes without a matching generator bump.
Over‑masking – masking destroys referential links	Foreign keys become NULL or duplicate	Mask only PII columns; keep surrogate keys untouched. Use deterministic tokenisation (HMAC‑SHA256 with a per‑run salt).
Seed bloat – seed grows to hundreds of MB	CI container start‑up > 2 min, disk pressure	Prune seed to minimal viable reference set; archive historic seeds in an artifact store, not in the repo.
Stateless generator + stateful DB – duplicate primary keys on parallel runs	Primary‑key violations when two jobs share a DB	Use per‑job databases (testcontainers, Kubernetes ephemeral pods) or generate UUIDs instead of sequences.
Performance blind spot – synthetic data too uniform	Load test passes but production spikes	Feed the generator with production histograms (e.g., order size distribution) – many commercial tools can ingest a CSV of stats and replay them.
Governance gap – developers copy production dump locally	Accidental PII exposure	Enforce policy‑as‑code (OPA/Gatekeeper) that blocks pg_dump/mysqldump from non‑approved namespaces.

---

7. Extending the Architecture

7.1. Data Contracts

Treat the generated dataset as a contract between producers (generator) and consumers (tests). Store a JSON Schema / Avro / Protobuf definition of each table’s shape, version it, and run great_expectations or schemathesis against the live DB after generation. This catches silent type changes (e.g., price_cents widened from int4 to int8) before they surface as flaky tests.

7.2. Self‑Service Portal

If multiple squads need on‑demand data, wrap the generator behind an internal API:

POST /api/v1/datasets
{
  "template": "ecommerce-order",
  "params": { "orderCount": 5000, "premiumRatio": 0.3 },
  "output": "postgres://ci-db:5432/testdb"
}

The portal can enforce quotas, audit logs, and automatically spin up a short‑lived DB (via cloudsqlproxy or kind cluster). Start small—one endpoint, one template—and evolve.

7.3. Continuous Data Quality

Add a nightly job that:

1. Generates a full‑size dataset (e.g., 100 k orders).
2. Runs the entire regression suite.
3. Publishes a dashboard (Grafana + Loki) with metrics: generation time, validation pass‑rate, query latency percentiles.

Trends reveal generator regressions (e.g., a new rule that accidentally creates circular references) before they hit a release branch.

---

8. Selecting Your First Toolchain

Situation	Recommended starter kit
Team of 2‑4, mostly unit/contract tests	Factory Boy + Faker + pytest + QA3 Test Data Generator for ad‑hoc CSV/JSON
Micro‑service fleet, need CI‑level integration data	Seed SQL + dbt seed + testcontainers + custom Python generator (or go-faker if Go‑centric)
Heavy load / performance testing	Subset‑and‑mask pipeline (CDC → S3 → Delphix/Tonic) + k6/Locust against masked clone
Regulated domain (PCI, HIPAA)	Hybrid seed‑grow with deterministic tokenisation + policy‑as‑code gate; avoid any production copy in lower envs
Cross‑team contract testing	Service virtualisation (WireMock) + Pact + generator for provider stub data

Start with the simplest pattern that satisfies your highest‑risk test tier. You can always add a second pattern later; the architecture is deliberately modular.

---

9. Practical Next Action

1. Audit your current test data flow – list every place a test suite reads data (shared DB, local dump, hard‑coded fixtures).
2. Pick one suite (preferably the flakiest integration test) and prototype a seed‑plus‑generator run in a branch.
3. Add the validation checklist as a pytest marker (@pytest.mark.data_validation).
4. Measure: generation time, test‑run time, flakiness rate before vs. after.
5. Document the pattern in a short ADR (Architecture Decision Record) and share with the platform team.

If you hit a wall on synthetic realism, spin up the **QA3 Test