Quality is not optional. It's our standard. Free QA tools for testers and developers.

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-null shipped_at and a related shipment row.
  • Cardinality control: You need 3–7 line items per order, not a Poisson distribution that occasionally produces 0 or 500.
  • Temporal logic: created_at on a child must be ≥ created_at on its parent; cancelled_at must 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.

ArtifactWhy It MattersExample Capture Method
ER diagram / foreign-key graphDetermines insertion order and join pathspg_dump --schema-only + parser, or ORM metadata
Column-level constraintsNOT NULL, CHECK, UNIQUE, DEFAULTinformation_schema.columns + information_schema.check_constraints
Business-rule catalogRules not encoded in the DB (e.g., “premium users get free shipping”)Markdown docs, ADRs, or a lightweight rule DSL
Target cardinalitiesHow many rows per table, and per parent-child relationshipCapacity plan or test-scenario spec
Data-sensitivity mapWhich columns contain PII, PCI, or secretsData-classification policy
Partitioning / sharding keysAffects how you batch insertsDDL or infrastructure docs

Checklist: Schema Readiness

  • All foreign keys are declared in the database (not just enforced in application code).
  • Every CHECK constraint 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.

ApproachBest ForWeaknessTypical Tooling
SQL scripts / stored proceduresSmall schemas (<20 tables), one-off datasets, teams fluent in SQLHard to parameterize, brittle when schema changes, no built-in faker libraryPlain .sql files, psql variables, dbt seeds
ORM / application-code generatorsTeams that already have rich domain models, need to reuse validation logicCouples test data to application runtime; slow for large volumesFactoryBot (Ruby), Factory Boy (Python), go-faker + GORM, Entity Framework Bogus
Dedicated synthetic-data platformsLarge schemas, strict compliance, need for subsetting / masking / versioningLearning curve; may require licensing for advanced featuresTonic, Synthesized, Gretel, QA3 free test data generator (/tools/test-data-generator)

Decision Flowchart (Text Version)

  1. Is the schema stable and < 30 tables? → SQL scripts or ORM factories are fine.
  2. Do you need to subset production data while preserving correlations? → Dedicated platform with subsetting.
  3. Must you generate PII-free data that still passes application validation? → Platform with built-in transformers or custom rule engine.
  4. Is the team small and the test suite fast? → ORM factories keep everything in one language.
  5. 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_number unique globally; line_number unique per order_id).

Artifact: generation_rules.yaml — declarative, version-controlled.

3. Generate in Topological Order

Execute generators table by table. For each table:

  1. Pull required parent IDs from already-generated tables (cache in memory or temp tables).
  2. Emit rows respecting cardinality rules.
  3. 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 CHECK passes.
  • 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 NULL in the target schema.
  • Run generation against a schema that has all constraints enabled.
  • If the generator violates a constraint, the INSERT fails—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.