Test Data Generation for Manual Testing: A Faster Setup

Manual testing still dominates many release cycles, especially when exploratory work, usability checks, or regulatory sign‑off are required. The bottleneck is rarely the test execution itself—it’s the time spent creating realistic, repeatable data before a tester can even start. This post walks through a practical, repeatable workflow for generating test data that keeps manual testers productive without sacrificing data quality.

---

1. Why the Current Setup Drags

Symptom	Root cause	Impact
Testers copy‑paste rows from production dumps	No isolated data‑generation pipeline	Data leaks, privacy violations, flaky results
Spreadsheets grow to hundreds of tabs	Ad‑hoc “one‑off” scripts that never get versioned	Hard to reproduce, impossible to audit
New environments need weeks of seeding	Manual SQL scripts, no parameterisation	Delayed test cycles, missed release windows
Edge‑case data (negative values, max lengths) missing	Generators only cover happy‑path	Defects escape to production

If any of these look familiar, the problem isn’t “manual testing is slow”—it’s test data preparation is manual.

---

2. Decision Criteria: Choose the Right Generation Approach

Before writing a single line of code, decide which generation style fits your context. Use the table below as a quick decision matrix.

Criteria	Static CSV/JSON files	Parameterised SQL scripts	Programmatic generators (Python/JS/Java)	SaaS / free online generators
Data volume	< 10 k rows	10 k–1 M rows	> 1 M rows or complex relationships	Any (limited by UI)
Schema churn	Low (stable schema)	Medium (DDL changes)	High (frequent model changes)	Low (fixed templates)
Referential integrity	Manual FK handling	Native FK support	Full control via code	Limited
Team skill set	Spreadsheet‑savvy	DBA / SQL‑comfortable	Developers / SDETs	Anyone with browser
Audit / version control	Git‑trackable files	Git‑trackable scripts	Git‑trackable code	Not versioned
Speed to first usable set	Minutes	Hours (script dev)	Hours–days (framework)	Seconds
Cost	Free	Free (DB licences)	Free (open‑source)	Free tier / paid

Rule of thumb

• Start with static files for a single feature or a quick smoke‑test.
• Move to parameterised SQL when you need to seed multiple environments repeatedly.
• Invest in a programmatic generator once you have > 3‑4 inter‑dependent entities or you need data‑driven test‑case variation.
• Use a free online generator (e.g., QA3’s test data generator at /tools/test-data-generator) for one‑off prototyping or to validate a schema before committing to code.

---

3. End‑to‑End Workflow

Below is a repeatable, low‑overhead workflow that works for most teams. Each step can be automated later, but the manual version is fast enough to start today.

3.1. Capture the Data Contract

1. List every table / entity the test will touch.
2. For each column note:
   • Data type & length
   • Nullability
   • Constraints (PK, FK, CHECK, UNIQUE)
   • Business rules (e.g., “email must be unique”, “status ∈ {NEW, ACTIVE, CLOSED}”)
3. Store this contract in a Markdown file (data-contract.md) next to the test plan.

Why? It becomes the single source of truth for generators, reviewers, and auditors.

3.2. Define Data Profiles

A profile is a named set of values that satisfies a specific test scenario.

Profile	Purpose	Key columns & rules
happy_path	Standard functional flow	All required fields populated, valid enums, realistic dates
boundary	Max length, min/max numeric	varchar(255) → 255 chars, int → INT_MAX
negative	Invalid input handling	Missing required fields, out‑of‑range enums, malformed emails
privacy	GDPR / PII masking	Realistic but synthetic names, hashed SSNs, dummy addresses

Create a profile matrix (CSV or spreadsheet) that maps each profile to the tables/columns it touches. This matrix drives the generator.

3.3. Pick the Generation Tool

Situation	Recommended tool	Quick start command
One‑off, < 5 tables, static schema	QA3 free test data generator (browser)	Open /tools/test-data-generator, paste DDL, select profiles
Recurring CI seeding, SQL‑centric team	dbt seed + custom macros	dbt seed --select my_schema.*
Complex object graphs, need code control	Python Faker + SQLAlchemy	python generate.py --profile happy_path
Need to share with non‑technical testers	Google Sheets + Apps Script	=GENERATE_TEST_DATA("happy_path")

Tip: Keep the generator itself under version control (Git). Even a 30‑line Python script is easier to audit than a spreadsheet macro.

3.4. Generate & Validate

1. Run the generator against a clean schema (or a dedicated test DB).
2. Automated validation checklist (run after every generation):

• Row counts match profile expectations
• All PKs are unique
• All FKs resolve (no orphan rows)
• CHECK constraints pass
• Business‑rule queries return expected counts (e.g., SELECT COUNT(*) FROM users WHERE status='ACTIVE')
• PII columns contain only synthetic values (regex check)

3. Manual spot‑check – open 5‑10 rows per table, verify realism (date ranges, formatting).

If any check fails, adjust the profile matrix or generator logic, then re‑run.

3.5. Package for Testers

• Export each profile as a SQL dump (profile_happy_path.sql) and a CSV bundle (profile_happy_path/ folder).
• Store in an artefact repository (Nexus, Artifactory, Git LFS) with a semantic version (v1.2.0-happy_path).
• Document the load command in the test plan:

# Load happy‑path data into local test DB


psql -d test_db -f artefacts/v1.2.0-happy_path/profile_happy_path.sql

Testers now have a one‑liner to get a fresh, consistent dataset.

---

4. Worked Example: E‑Commerce Order Flow

Assume a minimal schema:

CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       VARCHAR(255) NOT NULL UNIQUE,
  full_name   VARCHAR(120) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  price_cents INT NOT NULL CHECK (price_cents > 0)
);


CREATE TABLE orders (
  id              BIGSERIAL PRIMARY KEY,
  customer_id     BIGINT NOT NULL REFERENCES customers(id),
  status          VARCHAR(20) NOT NULL CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED')),
  placed_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);


CREATE TABLE order_items (
  id          BIGSERIAL PRIMARY KEY,
  order_id    BIGINT NOT NULL REFERENCES orders(id),
  product_id  BIGINT NOT NULL REFERENCES products(id),
  quantity    INT NOT NULL CHECK (quantity > 0),
  unit_price_cents INT NOT NULL CHECK (unit_price_cents > 0)
);

4.1. Data Contract (excerpt)

Table	Column	Type	Null?	Constraints	Business rule
customers	email	varchar(255)	NO	UNIQUE	Must look like *@*.*
products	price_cents	int	NO	CHECK >0	Multiples of 100 (whole dollars)
orders	status	varchar(20)	NO	CHECK IN (...)	Only NEW for fresh orders
order_items	quantity	int	NO	CHECK >0	Max 99 per line

4.2. Profiles

Profile	Customers	Products	Orders	Order Items
happy_path	100 rows, realistic names, unique emails	50 rows, price_cents 1000‑50000	200 rows, status NEW, placed_at last 30 days	1‑5 items per order, quantity 1‑3
boundary	1 row, email 254 chars, name 120 chars	1 row, price_cents = 2147483647	1 row, placed_at = now()	1 row, quantity = 99
negative	5 rows, duplicate email, missing name	5 rows, price_cents = -10	5 rows, status = 'INVALID'	5 rows, quantity = 0

4.3. Generator (Python + Faker) – 70 lines

# generate.py


import argparse, csv, random
from faker import Faker
from datetime import datetime, timedelta


fake = Faker()
PROFILES = ["happy_path", "boundary", "negative"]


def gen_customers(n, profile):
    rows = []
    for _ in range(n):
        email = fake.unique.email() if profile != "negative" else "dup@example.com"
        name = fake.name()[:120] if profile != "boundary" else "A"*120
        rows.append((email, name, fake.date_time_between("-2y", "now")))
    return rows


def gen_products(n, profile):
    rows = []
    for _ in range(n):
        sku = fake.bothify("SKU-????-###")
        name = fake.word().title() + " " + fake.word().title()
        if profile == "boundary":
            price = 2_147_483_647
        elif profile == "negative":
            price = -10
        else:
            price = random.choice(range(1000, 50001, 100))
        rows.append((sku, name, price))
    return rows


def gen_orders(cust_ids, n, profile):
    rows = []
    statuses = ["NEW"] if profile == "happy_path" else ["INVALID"]
    for _ in range(n):
        cid = random.choice(cust_ids)
        status = random.choice(statuses)
        placed = fake.date_time_between("-30d", "now")
        rows.append((cid, status, placed))
    return rows


def gen_items(order_ids, prod_ids, profile):
    rows = []
    for oid in order_ids:
        for _ in range(random.randint(1, 5)):
            pid = random.choice(prod_ids)
            qty = 99 if profile == "boundary" else (0 if profile == "negative" else random.randint(1,3))
            # unit price mirrors product price
            rows.append((oid, pid, qty, None))  # price filled later
    return rows


def write_csv(name, header, data):
    with open(f"{name}.csv", "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(header)
        w.writerows(data)


if __name__ == "__main__":
    p = argparse.ArgumentParser()
    p.add_argument("--profile", choices=PROFILES, required=True)
    args = p.parse_args()


# 1️⃣ Customers
    cust_cnt = 100 if args.profile == "happy_path" else 1
    customers = gen_customers(cust_cnt, args.profile)
    write_csv(f"customers_{args.profile}", ["email","full_name","created_at"], customers)
    cust_ids = list(range(1, len(customers)+1))


# 2️⃣ Products
    prod_cnt = 50 if args.profile == "happy_path" else 1
    products = gen_products(prod_cnt, args.profile)
    write_csv(f"products_{args.profile}", ["sku","name","price_cents"], products)
    prod_ids = list(range(1, len(products)+1))


# 3️⃣ Orders
    order_cnt = 200 if args.profile == "happy_path" else 1
    orders = gen_orders(cust_ids, order_cnt, args.profile)
    write_csv(f"orders_{args.profile}", ["customer_id","status","placed_at"], orders)
    order_ids = list(range(1, len(orders)+1))


# 4️⃣ Order items (need product price for unit_price_cents)
    # map product_id -> price_cents
    price_map = {i+1: products[i][2] for i in range(len(products))}
    items = []
    for oid in order_ids:
        for _ in range(random.randint(1,5)):
            pid = random.choice(prod_ids)
            qty = 99 if args.profile == "boundary" else (0 if args.profile == "negative" else random.randint(1,3))
            items.append((oid, pid, qty, price_map[pid]))
    write_csv(f"order_items_{args.profile}", ["order_id","product_id","quantity","unit_price_cents"], items)

Run it

python generate.py --profile happy_path
python generate.py --profile boundary
python generate.py --profile negative

You now have three CSV bundles ready for import.

4.4. Validation Script (SQL)

-- validation_happy_path.sql
\copy customers FROM 'customers_happy_path.csv' CSV HEADER;
\copy products FROM 'products_happy_path.csv' CSV HEADER;
\copy orders FROM 'orders_happy_path.csv' CSV HEADER;
\copy order_items FROM 'order_items_happy_path.csv' CSV HEADER;


-- PK uniqueness
SELECT 'customers pk dup' WHERE EXISTS (
  SELECT id FROM customers GROUP BY id HAVING COUNT(*) > 1
);
-- FK integrity
SELECT 'orders bad customer' WHERE EXISTS (
  SELECT 1 FROM orders o LEFT JOIN customers c ON o.customer_id=c.id WHERE c.id IS NULL
);
-- CHECK constraints (PostgreSQL will raise on load, but double‑check)
SELECT 'negative price' FROM products WHERE price_cents <= 0;
SELECT 'zero qty' FROM order_items WHERE quantity <= 0;
-- Business rule: only NEW orders in happy_path
SELECT 'unexpected status' FROM orders WHERE status <> 'NEW';

Run the script against a fresh test DB. Any output means the generator needs tweaking.

4.5. Packaging

artefacts/
└─ v1.0.0/
   ├─ happy_path/
   │   ├─ customers_happy_path.csv
   │   ├─ products_happy_path.csv
   │   ├─ orders_happy_path.csv
   │   └─ order_items_happy_path.csv
   ├─ boundary/
   │   └─ …
   └─ negative/
       └─ …

Add a README.md with the load command (see 3.5). Commit the whole artefacts/ folder to Git LFS or your artefact store.

---

5. Common Failure Modes & Mitigations

Failure mode	Symptom	Root cause	Mitigation
FK orphan rows	order_items load fails	Generator created items before orders existed	Generate in topological order; validate FK after each step