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
| Characteristic | Impact on Test Data |
|---|---|
| Star / snowflake schema | Fact 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 & clustering | Data distribution influences query plans; synthetic data must respect partition keys (e.g., event_date). |
| Volume | Regression suites often need millions of rows to surface performance regressions, not just logical bugs. |
| Determinism | CI pipelines require the exact same dataset on every run; random generators must be seeded. |
| Data‑privacy rules | PII 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:
- Model the schema (tables, columns, constraints, SCD metadata).
- Produce deterministic, seeded data for each run.
- Enforce referential integrity across fact and dimension tables.
- Apply domain‑specific rules (e.g., “order_total = sum(line_amount)”).
- Version the dataset alongside the warehouse code (Git‑friendly).
2. Decision Criteria: Choosing a Generation Approach
| Approach | When It Shines | Main 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 plugins | Most 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
| Construct | Meaning |
|---|---|
seq | Auto‑incrementing surrogate key (deterministic per seed). |
date_range | Generates 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.1 | Applies a uniform ±10 % variation to the referenced numeric column. |
poisson(3) + 1 | Realistic 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
| Pitfall | Symptom | Mitigation |
|---|---|---|
| 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 table | fk() resolves to empty set → NULLs or errors. | Order tables in spec topologically; generator should enforce dependency graph. |
| SCD “current” rows missing | is_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 spec | New 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 expressed | line_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 conditions | Two CI jobs write to same output directory. | Include git SHA or build ID in output path (generated/<sha>/). |
| Data‑privacy audit failure | Real customer names appear in test warehouse. | Run a PII scanner (e.g., pii-scanner) on generated files before load. |
7. Tooling Landscape (2024)
| Tool | License | Strength | Weakness |
|---|---|---|---|
QA3 Test Data Generator (/tools/test-data-generator) | Free (CLI + web UI) | Declarative YAML, seeded deterministic output, plugin API, Parquet/CSV/SQL export | Newer ecosystem; fewer community plugins |
| DBT‑test‑data | Open‑source (Apache‑2) | Native DBT integration, Jinja templating | Limited to DBT projects; no built‑in SCD helpers |
| Synth | Open‑source (MIT) | Powerful relational spec, supports foreign‑key graphs | Steeper learning curve; Python‑only runtime |
| DataGenerator (Java) | Commercial | High‑throughput, enterprise support | License cost; heavier CI footprint |
| Custom Python + Faker + SQLAlchemy | DIY | Full control, any rule expressible | Maintenance 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.