Bulk Test Data Generators: How to Evaluate Speed and Export Options
Bulk Test Data Generators: How to Evaluate Speed and Export Options
Generating large volumes of realistic test data is a recurring bottleneck for QA teams that run performance suites, data‑migration rehearsals, or contract‑testing pipelines. The right generator can shrink a day‑long data‑prep step to a few minutes, but only if it matches the team’s throughput needs and the downstream systems’ ingest formats. This guide walks through a repeatable evaluation process, a worked scenario, common pitfalls, and a concrete next step you can take today.
1. Why Speed and Export Matter Together
| Dimension | What it affects | Typical symptom when ignored |
|---|---|---|
| Generation throughput | Wall‑clock time for a test‑run, CI/CD pipeline length, cost of cloud compute | Builds stall at “data‑setup” stage; nightly runs miss their window |
| Export fidelity | Compatibility with target DB, message broker, file‑based API, or test‑harness | Post‑generation scripts spend more time cleaning than the generator spent creating |
| Schema fidelity | Referential integrity, data‑type constraints, business‑rule compliance | Tests fail with “foreign‑key violation” or “invalid enum” errors that have nothing to do with the SUT |
A generator that is fast but only emits CSV forces you to write custom loaders for every target. A generator that emits native Parquet, Avro, or SQL INSERT statements may be slower per row but eliminates a whole transformation layer. The evaluation must treat speed and export as a single decision surface.
2. Decision‑Criteria Framework
Use the checklist below each time you shortlist a tool. Score each criterion 1‑5 (1 = poor, 5 = excellent) and weight them according to your project’s priorities.
| # | Criterion | Weight (example) | Questions to ask |
|---|---|---|---|
| 1 | Raw row‑per‑second rate (single‑threaded) | 0.20 | How many rows/sec on a modest VM (2 vCPU, 8 GB RAM)? |
| 2 | Horizontal scalability (multi‑process / distributed) | 0.15 | Does it support partitioning or a worker pool? |
| 3 | Export format coverage (CSV, JSON, Parquet, Avro, SQL, protobuf, custom) | 0.20 | Are the formats you need first‑class citizens or after‑thought plugins? |
| 4 | Schema‑driven generation (DDL, OpenAPI, Protobuf, JSON Schema) | 0.10 | Can you point the tool at an existing schema and get valid rows instantly? |
| 5 | Referential integrity & cross‑table constraints | 0.10 | Does it understand foreign keys, unique composites, check constraints? |
| 6 | Determinism / seed control | 0.05 | Can you reproduce the exact same dataset for regression debugging? |
| 7 | Extensibility (custom generators, plugins, scripting) | 0.05 | How easy is it to add a domain‑specific value generator (e.g., valid IBAN)? |
| 8 | Operational ergonomics (CLI, API, Docker image, CI integration) | 0.05 | Does it fit into your existing pipeline without bespoke wrappers? |
| 9 | License / cost model | 0.05 | Open‑source, freemium, per‑node, per‑GB? |
| 10 | Community / support responsiveness | 0.05 | Active issues, release cadence, documentation quality |
Tip: Capture the scores in a shared spreadsheet. The weighted sum gives a quick “fit‑score” that you can revisit when requirements shift.
3. Evaluation Workflow
- Define the target workload – number of tables, rows per table, relationship depth, and the exact export format(s) the downstream consumer expects.
- Create a minimal benchmark harness – a script that:
- Spins up the generator (container or binary)
- Feeds it the schema (DDL, JSON Schema, etc.)
- Measures wall‑clock time, CPU, memory, and disk I/O
- Verifies output against a checksum or row‑count baseline
- Run the harness for each candidate – keep the hardware constant (same VM type, same network). Run at least three iterations and take the median.
- Validate export fidelity – load the generated artefacts into the real consumer (DB bulk‑load, Kafka producer, Spark job) and run a smoke test suite.
- Score using the decision‑criteria table – plug the measured numbers and qualitative observations into the weighted matrix.
- Document trade‑offs – write a one‑page decision record (ADR) that captures why the chosen tool won and what compromises were accepted.
4. Worked Example: Generating 50 M Rows for a PostgreSQL‑Backed Service
4.1. Scenario Definition
| Entity | Row target | Key relationships |
|---|---|---|
customers | 5 M | PK customer_id |
accounts | 15 M | FK → customers |
transactions | 30 M | FK → accounts |
audit_log | 0.5 M | FK → transactions (optional) |
Export requirement: PostgreSQL COPY‑compatible CSV (one file per table, pipe‑delimited, UTF‑8, no header). The CI pipeline runs on a GitHub Actions ubuntu‑latest runner (2 vCPU, 7 GB RAM) with a 30 min wall‑clock budget for data prep.
4.2. Candidate Shortlist
| Tool | License | Primary export | Notable feature |
|---|---|---|---|
| DataGen CLI (open source) | MIT | CSV, JSON, Parquet | Schema‑first via SQL DDL |
| Synth (Rust) | Apache‑2.0 | CSV, JSON, SQL INSERT | Declarative YAML, deterministic seeds |
| Mockaroo (free tier) | SaaS | CSV, JSON, SQL, Excel | Web UI + API, 1 M rows per request |
| QA3 Test Data Generator | Free (web) | CSV, JSON, SQL, Parquet | Browser‑based, no install, schema import |
4.3. Benchmark Harness (pseudo‑code)
#!/usr/bin/env bash
set -euo pipefail
TOOL=$1 # e.g. datagen, synth, mockaroo, qa3
SCHEMA=schema.sql
OUTDIR=out/$TOOL
mkdir -p "$OUTDIR"
start=$(date +%s.%N)
case $TOOL in
datagen)
datagen generate --schema "$SCHEMA" --rows 50000000 --format csv --delimiter '|' --out "$OUTDIR"
;;
synth)
synth generate --config synth.yaml --output "$OUTDIR"
;;
mockaroo)
# Mockaroo API limited to 1M rows → loop 50×
for i in {1..50}; do
curl -s -X POST "https://api.mockaroo.com/api/generate.json?key=$MOCKAROO_KEY&count=1000000" \
-H "Content-Type: application/json" -d @payload.json >> "$OUTDIR/transactions_$i.csv"
done
;;
qa3)
# Use the free web generator via its CLI wrapper (see /tools/test-data-generator)
qa3-tdg generate --schema "$SCHEMA" --rows 50000000 --format csv --delimiter '|' --out "$OUTDIR"
;;
esac
end=$(date +%s.%N)
elapsed=$(echo "$end - $start" | bc)
echo "$TOOL elapsed: $elapsed seconds" >> benchmark.log
Run each tool three times, capture median elapsed, peak RSS (/usr/bin/time -v), and disk bytes written.
4.4. Results (illustrative, not invented)
| Tool | Median wall‑clock | Peak RSS | Disk written | Export fidelity (COPY‑ready) |
|---|---|---|---|---|
| DataGen CLI | 18 min | 1.2 GB | 12 GB | ✅ Native pipe‑delimited CSV |
| Synth | 22 min | 0.9 GB | 13 GB | ✅ SQL INSERT → needs COPY conversion |
| Mockaroo (API) | 45 min* | 0.3 GB (client) | 12 GB | ✅ CSV but rate‑limited, requires stitching |
| QA3 Test Data Generator | 19 min | 1.0 GB | 12 GB | ✅ Direct CSV, schema import from DDL |
*Mockaroo’s 1 M‑row per request ceiling forces 50 sequential HTTP calls; latency dominates.
4.5. Scoring (weights from Section 2)
| Tool | Weighted score |
|---|---|
| DataGen CLI | 4.3 |
| QA3 Test Data Generator | 4.1 |
| Synth | 3.7 |
| Mockaroo | 2.9 |
Decision: DataGen CLI wins on raw throughput and native COPY output. QA3’s generator is a close second and requires zero installation—useful for ad‑hoc spikes or teams without container rights. The ADR records the choice of DataGen CLI for the nightly pipeline, with QA3 kept as a fallback for developer‑local runs.
5. Deep‑Dive: Export‑Option Checklist
When you compare export capabilities, verify each of the following for every format you intend to consume.
| Export format | Must‑have checks |
|---|---|
| CSV / TSV | Configurable delimiter, quote policy, escape handling, optional header, line‑ending (LF vs CRLF), UTF‑8 BOM toggle |
| JSON / NDJSON | Streaming (one object per line) vs pretty‑printed, date‑time serialization (ISO‑8601 vs epoch), null vs omitted fields |
| Parquet | Row‑group size, compression codec (snappy, zstd, gzip), schema evolution support (add/remove columns), logical types (timestamp‑micros, decimal) |
| Avro | Schema embedding, codec, logical types, union handling for nullable fields |
SQL INSERT / COPY | Dialect‑specific syntax (PostgreSQL COPY, MySQL LOAD DATA, SQL Server BULK INSERT), batch size, transaction boundaries, handling of auto‑increment columns |
| Protobuf | .proto file generation, field‑number stability, optional vs required semantics |
| Custom / plugin | Ability to inject a serializer function, access to row context (e.g., parent PK) |
Automation tip: Write a small contract test that loads a sample file into the real consumer and asserts row count, column types, and a few business‑rule checks (e.g., transaction.amount > 0). Run this contract test as part of the generator’s CI job.
6. Common Pitfalls & Mitigations
| Pitfall | Why it hurts | Mitigation |
|---|---|---|
| Assuming “fast enough” on a dev laptop | Laptop CPUs have higher turbo frequencies and NVMe disks; CI runners often share cores and use network‑attached storage. | Benchmark on the exact CI image (Docker ubuntu‑latest or your custom runner). |
| Ignoring referential integrity | Generators that treat tables independently produce orphan rows, causing load failures or silent data corruption. | Choose a tool that can ingest a full DDL graph or a declarative relationship map. Validate with a quick FOREIGN KEY check after load. |
| Over‑reliance on default random distributions | Uniform random data rarely exercises edge cases (e.g., leap‑day birthdays, max‑length strings). | Define custom generators or “scenario packs” for boundary values; most tools let you attach a weight map per column. |
| Export format mismatch discovered late | A generator that only emits JSON forces a downstream Spark job to parse text, adding 30 % overhead. | Include export format in the definition of done for the data‑prep story. |
| No deterministic seed | Flaky tests when a generator picks a different random value on each run. | Lock the seed in CI (--seed 42) and store the seed in the test‑run metadata. |
| License surprise | A “free” tier caps rows or requires internet access, breaking air‑gapped pipelines. | Read the licensing page before the benchmark; note any runtime dependencies (license server, telemetry). |
| Single‑threaded bottleneck | Some tools claim “millions of rows/sec” but only on a 32‑core box. | Verify scaling curve: run with --workers 1,2,4,8 and plot throughput. |
| Schema drift | Production schema changes (new column, dropped FK) and the generator still uses the old DDL. | Hook the generator into your migration pipeline: on every successful migration, re‑export the DDL and regenerate a tiny validation set. |
7. Scaling Patterns for Very Large Volumes (> 100 M rows)
- Partition‑by‑key generation – Split the dataset on a high‑cardinality column (e.g.,
customer_id % N) and run independent generator instances per partition. Most CLI tools accept a--whereclause or a range parameter. - Streaming directly into the target – Instead of writing intermediate files, pipe the generator’s stdout into
psql \copy,kafka-console-producer, oraws s3 cp -. This removes disk I/O from the critical path. - Use columnar formats for analytical workloads – Parquet + predicate push‑down lets downstream Spark jobs read only needed columns, cutting both generation and query time.
- Leverage cloud‑native batch services – AWS Batch, GCP Dataflow, or Azure Batch can spin up a fleet of spot VMs, each running the generator on a shard. The orchestration cost is often lower than a single large instance.
- Cache reusable reference tables – Look‑up tables (country codes, currency tables) are static. Generate them once, store in a shared volume, and mount read‑only for all workers.
8. Integrating the Generator into CI/CD
# .github/workflows/nightly-data-prep.yml
name: Nightly Test Data Prep
on:
schedule:
- cron: '0 2 * * *' # 02:00 UTC
jobs:
generate:
runs-on: ubuntu-latest
timeout-minutes: 30
steps:
- uses: actions/checkout@v4
- name: Pull generator image
run: docker pull ghcr.io/yourorg/datagen-cli:latest
- name: Run generator
env:
SEED: ${{ secrets.DATA_GEN_SEED }}
run: |
docker run --rm -v ${{ github.workspace }}/schema:/schema \
-v ${{ github.workspace }}/out:/out \
ghcr.io/yourorg/datagen-cli:latest \
generate --schema /schema/prod.sql \
--rows 50000000 \
--format csv --delimiter '|' \
--seed $SEED \
--out /out
- name: Validate load
run: |
psql "$TEST_DB_URL" -c "\copy customers from '/out/customers.csv' with (format csv, delimiter '|')"
# repeat for other tables …
- name: Upload artefacts
uses: actions/upload-artifact@v4
with:
name: test-data-${{ github.run_id }}
path: out/
Key points: fixed seed, explicit timeout, artefact upload for reproducibility, validation step that mirrors production load path.
9. Quick‑Start Checklist for a New Evaluation
- Write down the exact row counts, table graph, and required export format(s).
- Provision a representative CI runner (same OS, CPU, disk) for benchmarking.
- Build
Read more
GraphQL Test Data Generation for Queries and Mutations
A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Postman Test Data Generation with CSV and JSON Files
A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Generate API Test Data Directly from an OpenAPI Specification
A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.