How to Generate Correlated Data Across Multiple Tables
How to Generate Correlated Data Across Multiple Tables
Generating test data for a single table is straightforward. Generating data that respects foreign keys, check constraints, and business rules across five, ten, or fifty tables is where most test-data efforts break down. The symptom is usually the same: tests pass locally but fail in CI because an order references a user that doesn’t exist, or a payment row points to an invoice that was never created.
This guide walks through a repeatable process for producing correlated, referentially intact datasets across multiple tables. It covers the prerequisites you need before writing a single INSERT, the architectural choices that determine whether your approach scales, a worked example on a realistic schema, validation techniques that catch drift early, and the failure modes that appear once the data volume grows.
Why Correlated Data Is Hard
Relational databases enforce integrity at the row level. A foreign key guarantees that orders.user_id exists in users.id. A check constraint guarantees that orders.total equals the sum of its line items. When you generate data table-by-table, you lose those guarantees unless you explicitly coordinate the generators.
Typical pain points:
- Referential integrity: Child rows inserted before parents, or parent IDs that never get generated.
- Business-rule consistency: An order’s
status = 'shipped'requires a non-nullshipped_atand a relatedshipmentrow. - Cardinality control: You need 3–7 line items per order, not a Poisson distribution that occasionally produces 0 or 500.
- Temporal logic:
created_aton a child must be ≥created_aton its parent;cancelled_atmust be >created_at. - Uniqueness and indexes: Email addresses, SKUs, and transaction IDs must be unique across the whole dataset, not just per batch.
If you solve these with ad-hoc scripts, you end up with a fragile codebase that only the original author can maintain. The goal is a repeatable, version-controlled pipeline that produces deterministic datasets for any environment—local, CI, staging, or load-test.
Prerequisites: Know Your Schema Before You Generate
Before choosing a tool or writing code, capture the following metadata. Store it in a machine-readable format (JSON, YAML, or a dedicated schema registry) so your generator can consume it directly.
| Artifact | Why It Matters | Example Capture Method |
|---|---|---|
| ER diagram / foreign-key graph | Determines insertion order and join paths | pg_dump --schema-only + parser, or ORM metadata |
| Column-level constraints | NOT NULL, CHECK, UNIQUE, DEFAULT | information_schema.columns + information_schema.check_constraints |
| Business-rule catalog | Rules not encoded in the DB (e.g., “premium users get free shipping”) | Markdown docs, ADRs, or a lightweight rule DSL |
| Target cardinalities | How many rows per table, and per parent-child relationship | Capacity plan or test-scenario spec |
| Data-sensitivity map | Which columns contain PII, PCI, or secrets | Data-classification policy |
| Partitioning / sharding keys | Affects how you batch inserts | DDL or infrastructure docs |
Checklist: Schema Readiness
- All foreign keys are declared in the database (not just enforced in application code).
- Every
CHECKconstraint is documented and understood. - No “soft deletes” or logical-delete columns that change referential semantics.
- You have a canonical list of lookup / reference tables (countries, currencies, status codes).
- You know the maximum batch size your DB can ingest in a single transaction without locking issues.
If any item is missing, stop. Fix the schema knowledge gap first; otherwise your generator will encode incorrect assumptions.
Decision Criteria: Choose Your Generation Strategy
Three broad approaches exist. Each has a different cost/benefit profile.
| Approach | Best For | Weakness | Typical Tooling |
|---|---|---|---|
| SQL scripts / stored procedures | Small schemas (<20 tables), one-off datasets, teams fluent in SQL | Hard to parameterize, brittle when schema changes, no built-in faker library | Plain .sql files, psql variables, dbt seeds |
| ORM / application-code generators | Teams that already have rich domain models, need to reuse validation logic | Couples test data to application runtime; slow for large volumes | FactoryBot (Ruby), Factory Boy (Python), go-faker + GORM, Entity Framework Bogus |
| Dedicated synthetic-data platforms | Large schemas, strict compliance, need for subsetting / masking / versioning | Learning curve; may require licensing for advanced features | Tonic, Synthesized, Gretel, QA3 free test data generator (/tools/test-data-generator) |
Decision Flowchart (Text Version)
- Is the schema stable and < 30 tables? → SQL scripts or ORM factories are fine.
- Do you need to subset production data while preserving correlations? → Dedicated platform with subsetting.
- Must you generate PII-free data that still passes application validation? → Platform with built-in transformers or custom rule engine.
- Is the team small and the test suite fast? → ORM factories keep everything in one language.
- Do you need to version datasets alongside schema migrations? → Platform with dataset versioning or SQL scripts stored in the same repo as migrations.
Rule of thumb: Start with the simplest approach that satisfies your current constraints. Migrate upward only when you hit a hard limit (volume, compliance, multi-team coordination).
Workflow: From Schema to Validated Dataset
A robust pipeline has five stages. Each stage should be idempotent and produce artifacts you can inspect.
1. Model the Dependency Graph
Parse foreign keys into a directed acyclic graph (DAG). Topological sort gives you the insertion order. Cycles (e.g., users.manager_id → users.id) require a two-pass insert: create rows with NULL FK, then update.
Artifact: insertion_order.json — array of table names in dependency order.
2. Define Generation Rules per Table
For each table, specify:
- Row count: Fixed number, range, or function of parent count (e.g.,
orders = users * 3..7). - Column generators: Map each column to a faker, a reference lookup, a computed expression, or a fixed value.
- Cross-column constraints: Express as predicates the generator must satisfy (e.g.,
shipped_at IS NULL OR shipped_at > created_at). - Uniqueness scope: Global vs. per-parent (e.g.,
order_numberunique globally;line_numberunique perorder_id).
Artifact: generation_rules.yaml — declarative, version-controlled.
3. Generate in Topological Order
Execute generators table by table. For each table:
- Pull required parent IDs from already-generated tables (cache in memory or temp tables).
- Emit rows respecting cardinality rules.
- Write to a staging schema or temp tables—never directly to the target test schema until validation passes.
4. Validate Before Commit
Run a validation suite against the staged data:
- Referential integrity: All FKs resolve.
- Check constraints: Every
CHECKpasses. - Business rules: Custom SQL or code asserts.
- Statistical profiles: Distributions match expectations (e.g., 80% orders
completed, 15%cancelled, 5%pending). - Uniqueness: No duplicate keys.
Artifact: validation_report.json — pass/fail + metrics.
5. Promote / Snapshot
If validation passes, promote staging → target (rename schema, swap synonyms, or INSERT INTO target SELECT * FROM staging). Snapshot the dataset (export to Parquet, CSV, or SQL dump) for reproducibility.
Worked Example: E-Commerce Core Schema
We’ll generate data for a simplified shop schema:
users (id, email, tier, created_at)
addresses (id, user_id, type, line1, city, country_id, is_default)
products (id, sku, name, price_cents, active)
orders (id, user_id, address_id, status, total_cents, created_at, shipped_at)
order_items (id, order_id, product_id, qty, unit_price_cents)
shipments (id, order_id, carrier, tracking_number, shipped_at, delivered_at)
countries (id, code, name) -- reference table
Step 1: Dependency Graph & Insertion Order
countries → users → addresses → products → orders → order_items → shipments
(No cycles. addresses.user_id and orders.address_id both point to users and addresses respectively.)
Step 2: Generation Rules (YAML Snippet)
tables:
countries:
count: 250
columns:
code: "unique:iso3166_alpha2"
name: "lookup:country_name_from_code"
users:
count: 10000
columns:
email: "unique:email"
tier: "weighted_choice: {basic: 0.7, premium: 0.2, vip: 0.1}"
created_at: "date_between: '-5y', 'now'"
addresses:
count_per_parent: "users: 1..3"
columns:
user_id: "parent_id:users"
type: "choice: [billing, shipping]"
line1: "street_address"
city: "city"
country_id: "random_fk:countries"
is_default: "bool: {true: 0.4}"
products:
count: 5000
columns:
sku: "unique:sku_pattern: 'SKU-#######'"
name: "commerce_product_name"
price_cents: "random_int: 100, 500000"
active: "bool: {true: 0.9}"
orders:
count_per_parent: "users: 0..15"
columns:
user_id: "parent_id:users"
address_id: "fk_from:addresses WHERE user_id = parent_id AND type='shipping'"
status: "weighted_choice: {pending: 0.1, paid: 0.3, shipped: 0.4, delivered: 0.15, cancelled: 0.05}"
created_at: "date_after: users.created_at"
shipped_at: "conditional: {if: status IN ('shipped','delivered'), then: date_between: created_at, 'now', else: null}"
total_cents: "computed: sum(order_items.unit_price_cents * order_items.qty)"
order_items:
count_per_parent: "orders: 1..7"
columns:
order_id: "parent_id:orders"
product_id: "random_fk:products WHERE active=true"
qty: "random_int: 1, 5"
unit_price_cents: "copy_from: products.price_cents WHERE id = product_id"
shipments:
count_per_parent: "orders: {if: status IN ('shipped','delivered'), then: 1, else: 0}"
columns:
order_id: "parent_id:orders"
carrier: "choice: [UPS, FedEx, DHL, USPS]"
tracking_number: "unique:tracking_pattern: '1Z#########'"
shipped_at: "copy_from: orders.shipped_at"
delivered_at: "conditional: {if: orders.status='delivered', then: date_between: shipped_at, 'now', else: null}"
Key patterns used:
parent_id:table— pulls a random ID from the already-generated parent table.fk_from:table WHERE …— filtered foreign key (e.g., only shipping addresses).computed:— expression evaluated after child rows exist (requires two-pass or post-update).conditional:— value depends on another column in the same row or parent row.
Step 3: Generation Script (Pseudocode)
def generate_dataset(rules, target_schema):
staging = create_staging_schema()
id_cache = {} # table -> list of generated PKs
for table_name in topological_order(rules):
rule = rules[table_name]
parent_ids = resolve_parent_ids(rule, id_cache, staging)
rows = []
for _ in range(calculate_count(rule, parent_ids)):
row = {}
for col, spec in rule.columns.items():
row[col] = evaluate_spec(spec, row, parent_ids, staging)
rows.append(row)
bulk_insert(staging, table_name, rows)
id_cache[table_name] = [r['id'] for r in rows]
# Second pass for computed columns that need child aggregates
update_computed_columns(staging, rules, id_cache)
return staging
Step 4: Validation Queries
-- 1. Orphan check
SELECT 'orders' AS table, COUNT(*) AS orphans
FROM staging.orders o
LEFT JOIN staging.users u ON o.user_id = u.id
WHERE u.id IS NULL;
-- 2. Business rule: shipped orders have shipment
SELECT COUNT(*) FROM staging.orders o
WHERE o.status IN ('shipped','delivered')
AND NOT EXISTS (SELECT 1 FROM staging.shipments s WHERE s.order_id = o.id);
-- 3. Total consistency
SELECT o.id, o.total_cents, SUM(oi.unit_price_cents * oi.qty) AS calc_total
FROM staging.orders o
JOIN staging.order_items oi ON oi.order_id = o.id
GROUP BY o.id, o.total_cents
HAVING o.total_cents <> SUM(oi.unit_price_cents * oi.qty);
-- 4. Distribution check
SELECT status, COUNT(*) AS cnt, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS pct
FROM staging.orders
GROUP BY status;
Step 5: Promotion
BEGIN;
DROP SCHEMA IF EXISTS test_data CASCADE;
ALTER SCHEMA staging RENAME TO test_data;
COMMIT;
Validation Strategies That Scale
Validation is where most pipelines either become trustworthy or become a maintenance burden. Adopt these layers:
Layer 1: Database-Enforced Constraints (Zero Cost)
- Declare every FK,
CHECK,UNIQUE,NOT NULLin the target schema. - Run generation against a schema that has all constraints enabled.
- If the generator violates a constraint, the
INSERTfails—fast feedback.
Layer 2: Automated Assertion Suite (CI Gate)
Write a parameterized test suite that runs after every generation. Example structure (pytest + SQLAlchemy):
@pytest.mark.parametrize("query,description", [
("orphan_check.sql", "No orphaned orders"),
("business_rules.sql", "Business rules satisfied"),
("distribution_check.sql", "Status distribution within tolerance"),
])
def test_data_quality(db_connection, query, description):
with open(f"validation/{query}") as f:
sql = f.read()
result = db_connection.execute(sql).fetchall()
assert not result, f"{description} failed: {result}"
Store expected distributions in a thresholds.yaml:
orders.status:
pending: {min: 0.05, max: 0.15}
paid: {min: 0.25, max: 0.35}
shipped: {min: 0.35, max: 0.45}
delivered: {min: 0.10, max: 0.20}
cancelled: {min: 0.02, max: 0.08}
The test reads thresholds and fails if actuals drift.
Layer 3: Statistical Drift Detection (Optional)
For long-lived test environments,
Read more
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.
JSON Test Data Generator Guide for QA Engineers
A practical guide to “JSON Test Data Generator Guide for QA Engineers,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.