How to Subset Production Data for Safer Database Testing
How to Subset Production Data for Safer Database Testing
When a test suite runs against a full‑size production dump, two things usually happen: the test environment chokes on storage and runtime, and the team discovers that a handful of rows are enough to expose the bug they were chasing. Subsetting—creating a smaller, referentially‑intact slice of production data—lets you keep the realism of real data while staying within the limits of CI pipelines, local dev databases, and compliance rules.
Below is a practical, end‑to‑end workflow you can adopt today. It covers the prerequisites you need to verify, the implementation choices you’ll face, a worked example you can copy, the most common failure modes, and a concrete next step you can take right now.
1. Why Subsetting Beats Full Copies
| Concern | Full Production Copy | Subset (≈1‑5 % of rows) |
|---|---|---|
| Storage | 100 GB + (often > TB) | 1‑5 GB |
| Restore time | 30‑120 min | 2‑10 min |
| CI/CD latency | Blocks pipelines | Fits in parallel jobs |
| PII exposure | High | Reduced (you can mask after subset) |
| Referential integrity | Automatic | Must be enforced explicitly |
The table isn’t a benchmark—it reflects typical ratios observed in teams that have moved from “dump‑and‑restore” to “subset‑and‑mask.” Your exact numbers will differ, but the direction is consistent.
2. Prerequisites – What You Need Before You Start
| Item | Why It Matters | Quick Check |
|---|---|---|
| Schema metadata (FK definitions, NOT NULL, triggers) | Subsetting tools need to know the dependency graph. | pg_dump --schema-only or mysqldump --no-data |
| Data classification (PII, PCI, secret columns) | Determines which columns you must mask or drop. | Data‑catalog tagging or a simple spreadsheet. |
| Target environment limits (disk, RAM, max connections) | Guides the size ceiling for the subset. | CI runner spec sheet or local Docker limits. |
| Deterministic seed (optional) | Makes the subset reproducible for flaky‑test debugging. | Fixed random seed or hash‑based sampling. |
| Automation hook (CI job, Makefile, Gradle task) | Subsetting must be repeatable, not a one‑off script. | Existing pipeline stage for “test‑data‑prep”. |
If any of these are missing, pause and fill the gap. A subsetting run that ignores a foreign‑key chain will produce orphan rows and cause cryptic test failures later.
3. Decision Criteria – Choosing a Subsetting Strategy
| Strategy | Description | When It Shines | Trade‑offs |
|---|---|---|---|
| Row‑level random sampling | Pick N rows per table using TABLESAMPLE SYSTEM (PostgreSQL) or ORDER BY RAND() LIMIT N (MySQL). | Very large tables where uniform distribution is acceptable. | Breaks FK chains unless you also sample parent tables. |
| Key‑based “seed” sampling | Choose a set of primary‑key values (e.g., 1 % of customer_id) then pull all related rows via joins. | Business‑centric tests (order flow, user journey). | Requires a well‑defined “root” entity; may miss edge‑case rows. |
| Time‑window slicing | Keep rows from the last X days/months. | Time‑series workloads, partitioning by date. | Older reference data (lookup tables) may be omitted. |
| Hybrid (seed + time) | Combine a seed set with a recent‑window filter. | Most real‑world scenarios. | Slightly more complex SQL, but still scriptable. |
| Tool‑assisted (e.g., QA3 test‑data‑generator) | Declare entities, relationships, and desired row counts; the tool emits a consistent subset. | Teams that want a repeatable, version‑controlled definition. | Adds a dependency; learning curve for the DSL. |
Rule of thumb: start with a seed‑entity approach (usually users or accounts). It gives you a natural “anchor” for referential integrity and mirrors the way most applications query data.
4. Workflow – From Production Dump to Test‑Ready Subset
Below is a concrete, scriptable pipeline you can drop into a CI job. The example uses PostgreSQL, but the same steps translate to MySQL, SQL Server, or any RDBMS with comparable utilities.
4.1. Export Schema‑Only Dump
pg_dump -h $PROD_HOST -U $PROD_USER -d $PROD_DB \
--schema-only --no-owner --no-privileges \
-f schema.sql
Why: You’ll need the DDL to recreate an empty target database that matches production constraints.
4.2. Create an Empty Target DB
createdb -h $TEST_HOST -U $TEST_USER test_subset
psql -h $TEST_HOST -U $TEST_USER -d test_subset -f schema.sql
4.3. Define the Seed Set
Create a temporary table that holds the primary keys you want to keep.
-- 1% of customers, deterministic via hash
CREATE TEMP TABLE seed_customers AS
SELECT id
FROM customers
WHERE md5(id::text || '2025-07-01')::bit(64)::bigint < 184467440737095516; -- ~1%
Explanation: The md5 hash gives a repeatable pseudo‑random distribution without a RANDOM() call that would differ each run. Adjust the constant to hit your desired percentage.
4.4. Pull Related Rows – Recursive CTE
WITH RECURSIVE related AS (
-- Anchor: seed customers
SELECT id AS cust_id FROM seed_customers
UNION ALL
-- Orders belonging to those customers
SELECT o.customer_id
FROM orders o
JOIN related r ON o.customer_id = r.cust_id
UNION ALL
-- Order items for those orders
SELECT oi.order_id
FROM order_items oi
JOIN related r ON oi.order_id = r.cust_id
)
SELECT * INTO temp_keep_ids FROM related;
Now temp_keep_ids holds every PK you need to retain across the three tables. Extend the CTE for each FK hop in your schema.
4.5. Copy Data Into Target
# Customers
psql -h $PROD_HOST -U $PROD_USER -d $PROD_DB -c \
"COPY (SELECT * FROM customers WHERE id IN (SELECT cust_id FROM temp_keep_ids WHERE cust_id IS NOT NULL)) TO STDOUT" \
| psql -h $TEST_HOST -U $TEST_USER -d test_subset -c "COPY customers FROM STDIN"
# Orders
psql -h $PROD_HOST -U $PROD_USER -d $PROD_DB -c \
"COPY (SELECT * FROM orders WHERE customer_id IN (SELECT cust_id FROM temp_keep_ids WHERE cust_id IS NOT NULL)) TO STDOUT" \
| psql -h $TEST_HOST -U $TEST_USER -d test_subset -c "COPY orders FROM STDIN"
# Order items
psql -h $PROD_HOST -U $PROD_USER -d $PROD_DB -c \
"COPY (SELECT * FROM order_items WHERE order_id IN (SELECT cust_id FROM temp_keep_ids WHERE cust_id IS NOT NULL)) TO STDOUT" \
| psql -h $TEST_HOST -U $TEST_USER -d test_subset -c "COPY order_items FROM STDIN"
Tip: Wrap each COPY … TO STDOUT | COPY … FROM STDIN pair in a Bash function to avoid repetition.
4.6. Mask / Redact Sensitive Columns
UPDATE customers SET email = md5(email || 'salt') || '@example.com';
UPDATE customers SET ssn = NULL; -- drop if not needed
UPDATE orders SET card_last4 = '****';
Run these statements after the data lands in the test DB. Keeping masking separate from extraction makes the pipeline easier to audit.
4.7. Verify Referential Integrity
SELECT 'customers→orders' AS fk, COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
SELECT 'orders→order_items' AS fk, COUNT(*) AS orphan_items
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
Both queries must return zero. If not, revisit the CTE in step 4.4.
4.8. Snapshot the Subset for Re‑use
pg_dump -h $TEST_HOST -U $TEST_USER -d test_subset \
--data-only --no-owner --no-privileges \
-f subset_$(date +%F).sql
Commit subset_YYYY-MM-DD.sql to your repo (or an artifact store). Future CI runs can restore from this file instead of re‑running the whole extraction, saving minutes per build.
5. Worked Example – End‑to‑End Script
Below is a single Bash script that strings the steps together. Save it as subset_prod.sh, make it executable, and add it to your CI pipeline.
#!/usr/bin/env bash
set -euo pipefail
# -------------------------------------------------
# CONFIG – adjust once per project
# -------------------------------------------------
PROD_HOST="prod-db.internal"
PROD_USER="readonly"
PROD_DB="acme_prod"
TEST_HOST="localhost"
TEST_USER="tester"
TEST_DB="acme_test_subset"
SEED_PERCENT=1 # percent of root entity to keep
ROOT_TABLE="customers"
ROOT_PK="id"
SEED_SALT="2025-07-01" # change when you want a new deterministic slice
# -------------------------------------------------
# 1. Schema dump & recreate empty test DB
# -------------------------------------------------
pg_dump -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" \
--schema-only --no-owner --no-privileges -f /tmp/schema.sql
dropdb -h "$TEST_HOST" -U "$TEST_USER" --if-exists "$TEST_DB"
createdb -h "$TEST_HOST" -U "$TEST_USER" "$TEST_DB"
psql -h "$TEST_HOST" -U "$TEST_USER" -d "$TEST_DB" -f /tmp/schema.sql
# -------------------------------------------------
# 2. Build seed set (deterministic hash)
# -------------------------------------------------
psql -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" <<SQL
CREATE TEMP TABLE seed_${ROOT_TABLE} AS
SELECT ${ROOT_PK}
FROM ${ROOT_TABLE}
WHERE (md5(${ROOT_PK}::text || '${SEED_SALT}')::bit(64)::bigint)
< (18446744073709551615 * ${SEED_PERCENT} / 100);
SQL
# -------------------------------------------------
# 3. Recursive CTE to collect all dependent PKs
# -------------------------------------------------
# Extend this CTE for each FK hop in your schema.
psql -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" <<'SQL'
WITH RECURSIVE related AS (
SELECT id AS cust_id FROM seed_customers
UNION ALL
SELECT o.customer_id FROM orders o JOIN related r ON o.customer_id = r.cust_id
UNION ALL
SELECT oi.order_id FROM order_items oi JOIN related r ON oi.order_id = r.cust_id
)
SELECT * INTO TEMP temp_keep_ids FROM related;
SQL
# -------------------------------------------------
# 4. Stream each table into the test DB
# -------------------------------------------------
copy_table() {
local src_table=$1
local fk_col=$2 # column that links to temp_keep_ids.cust_id
psql -h "$PROD_HOST" -U "$PROD_USER" -d "$PROD_DB" -c \
"COPY (SELECT * FROM ${src_table} WHERE ${fk_col} IN (SELECT cust_id FROM temp_keep_ids)) TO STDOUT" \
| psql -h "$TEST_HOST" -U "$TEST_USER" -d "$TEST_DB" -c "COPY ${src_table} FROM STDIN"
}
copy_table customers id
copy_table orders customer_id
copy_table order_items order_id
# -------------------------------------------------
# 5. Mask PII
# -------------------------------------------------
psql -h "$TEST_HOST" -U "$TEST_USER" -d "$TEST_DB" <<'SQL'
UPDATE customers SET email = md5(email || 'mask_salt') || '@example.com';
UPDATE customers SET ssn = NULL;
UPDATE orders SET card_last4 = '****';
SQL
# -------------------------------------------------
# 6. Integrity check
# -------------------------------------------------
psql -h "$TEST_HOST" -U "$TEST_USER" -d "$TEST_DB" <<'SQL'
SELECT 'customers→orders' AS fk, COUNT(*) AS orphan_orders
FROM orders o LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
SELECT 'orders→order_items' AS fk, COUNT(*) AS orphan_items
FROM order_items oi LEFT JOIN orders o ON oi.order_id = o.id
WHERE o.id IS NULL;
SQL
# -------------------------------------------------
# 7. Snapshot for reuse
# -------------------------------------------------
SNAPSHOT="subset_$(date +%F).sql"
pg_dump -h "$TEST_HOST" -U "$TEST_USER" -d "$TEST_DB" \
--data-only --no-owner --no-privileges -f "$SNAPSHOT"
echo "Subset snapshot written to $SNAPSHOT"
What you get:
- A deterministic 1 % slice of customers plus every order and order item that belongs to them.
- PII masked, referential integrity verified, and a portable
subset_YYYY-MM-DD.sqlyou can version‑control.
6. Common Failure Modes & How to Detect Them Early
| Symptom | Root Cause | Detection Point | Fix |
|---|---|---|---|
| Orphan rows after copy | Missing FK hop in recursive CTE or wrong column name. | Step 6 (integrity queries) | Add the missing join to the CTE; re‑run copy. |
| Subset too large for CI disk | Seed percentage not tuned; time‑window not applied. | CI job fails with “no space left on device”. | Reduce SEED_PERCENT or add a WHERE created_at > now() - interval '30 days' filter in the CTE. |
| Flaky tests due to data drift | Snapshot not refreshed after schema migration. | Tests pass locally but fail on CI after a migration. | Add a CI gate that regenerates the snapshot whenever schema.sql changes (hash compare). |
| Masking breaks NOT NULL constraints | UPDATE … SET column = NULL on a column defined NOT NULL. | Migration script errors on apply. | Either keep a synthetic non‑null value ('REDACTED') or alter the column to nullable in the test DB only. |
| Deterministic seed produces same rows every run, hiding bugs | Seed salt never changes. | Same test data for months; new edge cases never exercised. | Rotate SEED_SALT on a schedule (e.g., per sprint) or hash the current git commit SHA. |
| Tool‑assisted generator diverges from hand‑rolled SQL | DSL version mismatch or missing relationship definition. | Subset size differs dramatically from expectations. | Pin the generator version in package.json/requirements.txt and run a diff against the hand‑rolled snapshot. |
Monitoring tip: Add a tiny Prometheus metric (or a simple log line) that emits subset_rows_total{table="customers"} 12345 after each run. Alert if the count deviates > 20
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.