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

Test Data Generation for Data Warehouse Regression Tests

Test Data Generation for Data Warehouse Regression Tests

When a data warehouse changes—new dimensions, altered grain, revised ETL logic—regression testing is the only reliable way to prove that existing reports and downstream consumers still behave as expected. The bottleneck is rarely the test scripts themselves; it’s the test data. Production‑scale fact tables, slowly changing dimensions, and referential integrity constraints make hand‑crafted datasets brittle, slow to refresh, and hard to version.

This guide walks through a repeatable workflow for generating realistic, version‑controlled test data for data‑warehouse regression suites. It covers design decisions, tool selection, a worked example, validation checks, common pitfalls, and a concrete next step you can take today.


1. Why Test Data Generation Is a Distinct Problem for Warehouses

CharacteristicImpact on Test Data
Star / snowflake schemaFact tables reference many dimension keys; a single fact row can require dozens of dimension rows.
Slowly Changing Dimensions (SCD Type 2)Historical rows must coexist with current rows; surrogate keys and effective‑date ranges must be consistent.
Partitioning & clusteringData distribution influences query plans; synthetic data must respect partition keys (e.g., event_date).
VolumeRegression suites often need millions of rows to surface performance regressions, not just logical bugs.
DeterminismCI pipelines require the exact same dataset on every run; random generators must be seeded.
Data‑privacy rulesPII columns (email, SSN) must be masked or synthesized, yet retain referential integrity.

Because of these constraints, a generic “INSERT … SELECT FROM source” script rarely satisfies regression needs. You need a generation pipeline that can:

  1. Model the schema (tables, columns, constraints, SCD metadata).
  2. Produce deterministic, seeded data for each run.
  3. Enforce referential integrity across fact and dimension tables.
  4. Apply domain‑specific rules (e.g., “order_total = sum(line_amount)”).
  5. Version the dataset alongside the warehouse code (Git‑friendly).

2. Decision Criteria: Choosing a Generation Approach

ApproachWhen It ShinesMain Trade‑offs
SQL‑only scripts (CTE + window functions)Small schemas, team comfortable with SQL, no external tooling allowed.Hard to maintain as schema grows; limited expression of cross‑table rules.
Declarative YAML/JSON + engine (e.g., DBT‑test‑data, Synth, DataGenerator)Teams already using DBT or similar; want version‑controlled specs.Learning curve for the spec language; engine may not support every warehouse dialect.
Programmatic generation (Python/Java + SQLAlchemy / JDBC)Complex business rules, need to call external APIs (e.g., address validation), or generate massive volumes.More code to maintain; requires CI runtime for the language.
Hybrid: Spec + custom pluginsMost real‑world projects; core data described declaratively, edge cases coded.Requires a framework that supports plugins (e.g., QA3’s free test data generator at /tools/test-data-generator).

Rule of thumb: Start with a declarative spec. If you hit a rule that the spec language cannot express, fall back to a small plugin written in the host language. This keeps the bulk of the data definition readable and diff‑able.


3. End‑to‑End Workflow

┌─────────────────────┐
│ 1. Schema snapshot  │   (export DDL + constraints + SCD metadata)
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 2. Define spec      │   (tables, row counts, distributions, rules)
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 3. Generate data    │   (seeded run → CSV/Parquet/SQL files)
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 4. Load into test   │   (TRUNCATE + COPY / INSERT)
│    warehouse        │
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 5. Validate         │   (row counts, FK checks, business rule asserts)
└───────┬─────────────┘
        ▼
┌─────────────────────┐
│ 6. Run regression   │   (dbt test, Great Expectations, custom SQL)
└─────────────────────┘

Each step is idempotent and versioned. The spec lives in test-data/spec.yaml; the generated artefacts live in test-data/generated/<git‑sha>/. CI pipelines check out the spec, run the generator, load the data, and then execute the regression suite.


4. Worked Example: Sales‑Order Star Schema

4.1 Schema Snapshot (simplified)

-- dim_date
CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,
    full_date       DATE NOT NULL,
    day_of_week     SMALLINT,
    month           SMALLINT,
    quarter         SMALLINT,
    year            SMALLINT
);


-- dim_customer (SCD Type 2)
CREATE TABLE dim_customer (
    customer_sk     BIGINT PRIMARY KEY,
    customer_id     VARCHAR(20) NOT NULL,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    effective_from  DATE NOT NULL,
    effective_to    DATE,
    is_current      BOOLEAN NOT NULL
);


-- dim_product
CREATE TABLE dim_product (
    product_sk      BIGINT PRIMARY KEY,
    product_code    VARCHAR(30) NOT NULL,
    category        VARCHAR(40),
    list_price      NUMERIC(12,2)
);


-- fact_sales_order
CREATE TABLE fact_sales_order (
    order_sk        BIGINT PRIMARY KEY,
    order_number    VARCHAR(30) NOT NULL,
    order_date_key  INT NOT NULL REFERENCES dim_date(date_key),
    customer_sk     BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
    product_sk      BIGINT NOT NULL REFERENCES dim_product(product_sk),
    quantity        INT NOT NULL,
    unit_price      NUMERIC(12,2) NOT NULL,
    line_total      NUMERIC(14,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

4.2 Spec (YAML) – test-data/spec.yaml

version: 1
seed: 2024-06-15          # deterministic seed for the whole run
tables:
  dim_date:
    rows: 3650            # 10 years
    columns:
      date_key: "seq"
      full_date: "date_range(start='2015-01-01', days=rows)"
      day_of_week: "dow(full_date)"
      month: "month(full_date)"
      quarter: "quarter(full_date)"
      year: "year(full_date)"


dim_customer:
    rows: 5000
    scd_type: 2
    columns:
      customer_sk: "seq"
      customer_id: "format('CUST-{:06d}', seq)"
      first_name: "fake.first_name()"
      last_name: "fake.last_name()"
      email: "fake.unique.email()"
      effective_from: "date_range(start='2015-01-01', days=rows)"
      effective_to: "null_if(random() < 0.9, date_add(effective_from, days=random_int(30, 1000)))"
      is_current: "effective_to IS NULL"


dim_product:
    rows: 1200
    columns:
      product_sk: "seq"
      product_code: "format('PRD-{:05d}', seq)"
      category: "choice(['Electronics','Apparel','Home','Sports','Books'])"
      list_price: "uniform(5.00, 500.00, 2)"


fact_sales_order:
    rows: 2_000_000
    columns:
      order_sk: "seq"
      order_number: "format('ORD-{:09d}', seq)"
      order_date_key: "fk(dim_date.date_key, weight='year')"
      customer_sk: "fk(dim_customer.customer_sk, where='is_current')"
      product_sk: "fk(dim_product.product_sk)"
      quantity: "poisson(3) + 1"
      unit_price: "fk(dim_product.list_price, jitter=0.1)"   # ±10 %

Explanation of key constructs

ConstructMeaning
seqAuto‑incrementing surrogate key (deterministic per seed).
date_rangeGenerates a contiguous date series.
fk(table.column, …)Picks a foreign‑key value from the referenced table; optional where clause filters (e.g., only current SCD rows).
jitter=0.1Applies a uniform ±10 % variation to the referenced numeric column.
poisson(3) + 1Realistic order‑line quantities (mean ≈ 3).
weight='year'Skews date selection toward recent years (common in sales data).

4.3 Generation Run



# Using QA3’s free test data generator (CLI)


qa3-test-data generate \
  --spec test-data/spec.yaml \
  --out test-data/generated/$(git rev-parse --short HEAD) \
  --format parquet

Output (per table) – partitioned Parquet files ready for bulk load.

4.4 Load Into Test Warehouse (Snowflake example)

-- Truncate in FK order
TRUNCATE TABLE fact_sales_order;
TRUNCATE TABLE dim_customer;
TRUNCATE TABLE dim_product;
TRUNCATE TABLE dim_date;


-- Bulk load (Snowflake STAGE + COPY)
PUT file://test-data/generated/abc1234/dim_date/*.parquet @~/stage/dim_date;
COPY INTO dim_date FROM @~/stage/dim_date FILE_FORMAT = (TYPE = PARQUET);
-- repeat for other tables

4.5 Validation Checks (run immediately after load)

-- 1️⃣ Row‑count sanity
SELECT 'dim_date' AS tbl, COUNT(*) FROM dim_date
UNION ALL SELECT 'dim_customer', COUNT(*) FROM dim_customer
UNION ALL SELECT 'dim_product', COUNT(*) FROM dim_product
UNION ALL SELECT 'fact_sales_order', COUNT(*) FROM fact_sales_order;


-- 2️⃣ FK integrity (should return 0 rows)
SELECT 'orphan_customer' AS chk, COUNT(*)
FROM fact_sales_order f
LEFT JOIN dim_customer c ON f.customer_sk = c.customer_sk
WHERE c.customer_sk IS NULL;


-- 3️⃣ SCD current‑flag consistency
SELECT COUNT(*)
FROM dim_customer
WHERE is_current = TRUE AND effective_to IS NOT NULL;


-- 4️⃣ Business rule: line_total = quantity * unit_price
SELECT COUNT(*)
FROM fact_sales_order
WHERE line_total <> quantity * unit_price;

All checks must return zero for the error queries and the expected row counts for the sanity query. Fail the CI job if any check deviates.

4.6 Regression Suite Execution

dbt test --select tag:regression   # or Great Expectations suite

Because the dataset is deterministic, a flaky test indicates a genuine regression, not data drift.


5. Validation Checklist (Copy‑Paste Into Your Repo)



# Test‑Data Generation Validation Checklist


- [ ] **Spec versioned** – `spec.yaml` lives in Git, reviewed with schema changes.
- [ ] **Seed recorded** – Seed value stored in spec; CI prints it on each run.
- [ ] **Row‑count targets** – Each table’s `rows` matches the regression‑suite contract.
- [ ] **FK coverage** – Every foreign key column uses `fk()` with appropriate `where` filters.
- [ ] **SCD correctness** – `effective_from / effective_to / is_current` logic validated.
- [ ] **Distribution realism** – Numeric columns use `uniform`, `normal`, `poisson`, or `jitter` rather than constants.
- [ ] **PII masking** – No real emails, names, SSNs; use `fake.unique.*` or hashing.
- [ ] **Deterministic output** – Two runs with same seed produce byte‑identical Parquet files (checksum).
- [ ] **Load script idempotent** – `TRUNCATE` + `COPY` works repeatedly without manual steps.
- [ ] **Post‑load validation** – Automated SQL checks (row counts, FK, business rules) run in CI.
- [ ] **Performance baseline** – Capture query‑plan timings on first successful load; alert on > 20 % drift.
- [ ] **Documentation** – README in `test-data/` explains how to regenerate, change seed, add tables.

6. Common Pitfalls & Mitigations

PitfallSymptomMitigation
Seed drift – developer changes seed locally but forgets to commit.CI passes locally, fails on server (different data).Enforce seed in spec.yaml; add a pre‑commit hook that rejects uncommitted seed changes.
FK generation before referenced tablefk() resolves to empty set → NULLs or errors.Order tables in spec topologically; generator should enforce dependency graph.
SCD “current” rows missingis_current = TRUE rows have effective_to set.Add a post‑generation rule: UPDATE dim_customer SET effective_to = NULL WHERE is_current; or express directly in spec (effective_to: "null_if(is_current, …)").
Volume explosion – rows: 10_000_000 on a laptop CI runner.OOM / timeout.Use partitioned generation: generate per‑month files, load incrementally; or spin up a dedicated CI node with enough RAM.
Non‑deterministic fake data – fake.email() without unique.Duplicate emails break unique constraints.Always call fake.unique.* for columns with uniqueness constraints.
Schema drift not reflected in specNew column added to dim_product but spec unchanged → load fails.Add a CI step that diffs information_schema.columns against spec column list; fail on mismatch.
Business rule not expressedline_total computed column mismatches because unit_price jitter not applied to list_price.Encode rule in spec (unit_price: "fk(dim_product.list_price, jitter=0.1)") and add a validation query.
Parallel generation race conditionsTwo CI jobs write to same output directory.Include git SHA or build ID in output path (generated/<sha>/).
Data‑privacy audit failureReal customer names appear in test warehouse.Run a PII scanner (e.g., pii-scanner) on generated files before load.

7. Tooling Landscape (2024)

ToolLicenseStrengthWeakness
QA3 Test Data Generator (/tools/test-data-generator)Free (CLI + web UI)Declarative YAML, seeded deterministic output, plugin API, Parquet/CSV/SQL exportNewer ecosystem; fewer community plugins
DBT‑test‑dataOpen‑source (Apache‑2)Native DBT integration, Jinja templatingLimited to DBT projects; no built‑in SCD helpers
SynthOpen‑source (MIT)Powerful relational spec, supports foreign‑key graphsSteeper learning curve; Python‑only runtime
DataGenerator (Java)CommercialHigh‑throughput, enterprise supportLicense cost; heavier CI footprint
Custom Python + Faker + SQLAlchemyDIYFull control, any rule expressibleMaintenance burden; easy to drift from spec

Recommendation: Start with the QA3 free test data generator because it gives you a version‑controlled spec, deterministic seeding, and a plugin hook for the few custom rules every warehouse inevitably needs. Migrate to a heavier framework only when you hit a hard limitation (e.g., need to call an external address‑validation service for every generated row).


8. Sc

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.