Test Data for ETL Validation: Source-to-Target Examples
Test Data for ETL Validation: Source‑to‑Target Examples
When an ETL pipeline moves data from a source system into a data warehouse or lake, the only thing that proves the pipeline works is the data itself. Unit tests on transformation logic, schema‑comparison scripts, and row‑count checks are useful, but they miss the class of bugs that only appear when real‑world values travel through the full stack: truncation, implicit casts, surrogate‑key collisions, and business‑rule drift.
This guide walks through a repeatable approach to building source‑to‑target test data sets, shows concrete scenarios you can copy, and highlights tool choices that keep the effort sustainable.
1. Why Source‑to‑Target Test Data Is Different
| Traditional unit test data | Source‑to‑target test data |
|---|---|
| Isolated to a single function or stored procedure | Spans the entire pipeline: extract, staging, transformation, load |
| Often synthetic, minimal rows | Mirrors production cardinality, distribution, and edge cases |
| Validates logic only | Validates logic and data‑type fidelity, referential integrity, and business rules |
Easy to generate with a few INSERTs | Requires coordinated generation across multiple schemas / platforms |
If you only test the transformation step, you will not catch a source‑system change that adds a new nullable column, a staging‑area truncation that silently drops trailing spaces, or a target‑table partition‑key mismatch that forces a full‑table scan.
2. Decision Criteria for a Test‑Data Strategy
Before you write a single row, decide on the following dimensions. The answers shape the tooling, the volume, and the maintenance model.
| Dimension | Questions to answer | Typical choices |
|---|---|---|
| Scope | Which pipelines? One‑off or regression suite? | Full regression suite for core pipelines; ad‑hoc for new sources |
| Refresh cadence | How often must the data reflect production? | Nightly snapshot, weekly, or on‑demand for a release |
| Data sensitivity | PII, PHI, financial data? | Masked / synthetic for non‑prod; tokenized for prod‑like |
| Volume | Row counts per table, growth rate | 1 % of prod for functional, 10 % for performance |
| Platform heterogeneity | Source: Oracle, SQL Server, flat files; Target: Snowflake, Redshift, BigQuery | Cross‑platform generator or per‑platform scripts |
| Automation level | CI/CD integration, manual run? | Fully automated in CI, with a manual “refresh” button |
| Team ownership | QA owns data, DE owns schema, DBA owns refresh? | Shared ownership with a data‑contract document |
Document the decisions in a Test Data Contract (one‑page markdown) and store it next to the pipeline code. It becomes the reference for every future change.
3. Workflow: From Contract to Executable Test Set
1. Capture source schema + sample rows (profile)
2. Define transformation rules (mapping doc)
3. Identify edge‑case categories (nulls, overflow, duplicates, FK breaks)
4. Choose generation method (SQL scripts, Python, dedicated tool)
5. Generate source‑side data set
6. Run ETL against a clean target environment
7. Capture target output
8. Compare source‑to‑target using validation checks
9. Store artefacts (source CSV, target snapshot, diff report) as test assets
10. Version‑control everything
Each step can be scripted; the only manual gate is step 3 where a domain expert reviews the edge‑case list.
4. Worked Example: Order‑to‑Fact Pipeline
4.1 Source System (OLTP)
| Table | Key columns | Notable constraints |
|---|---|---|
customers | cust_id PK, email UNIQUE, status NOT NULL | status ∈ ('ACTIVE','INACTIVE','BLOCKED') |
orders | order_id PK, cust_id FK → customers, order_dt NOT NULL, total_amt NUMERIC(12,2) CHECK (total_amt >= 0) | Partitioned by order_dt month |
order_lines | line_id PK, order_id FK → orders, product_sku, qty INT CHECK (qty > 0), unit_price NUMERIC(10,4) |
4.2 Target Warehouse (Star Schema)
| Table | Grain | Columns |
|---|---|---|
dim_customer | One row per customer | cust_sk PK, cust_id, email, status, valid_from, valid_to, is_current |
dim_date | One row per day | date_sk PK, full_date, year, quarter, month, day_of_week |
fact_order | One row per order line | order_sk PK, cust_sk FK, date_sk FK, product_sk FK, qty, unit_price, line_total, load_ts |
4.3 Mapping Rules (excerpt)
| Source → Target | Rule |
|---|---|
customers.cust_id → dim_customer.cust_id | Direct copy |
customers.email → dim_customer.email | Lower‑case, trim |
orders.order_dt → dim_date.full_date | Date‑only, surrogate key lookup |
order_lines.qty * order_lines.unit_price → fact_order.line_total | Round to 2 decimals |
orders.total_amt → not stored | Validation only: SUM(line_total) = total_amt per order |
4.4 Edge‑Case Catalogue
| Category | Example rows (source) | Expected target behaviour |
|---|---|---|
| Null FK | orders row with cust_id = NULL | Reject / route to error table; fact_order missing |
| Duplicate PK | Two customers rows with same cust_id | Load fails – primary‑key violation in dim_customer |
| Overflow | order_lines.unit_price = 99999999.9999 (exceeds NUMERIC(10,4)) | Load error or truncation – must be caught |
| Negative qty | order_lines.qty = -5 | Check constraint violation → error table |
| Future date | orders.order_dt = '2099-12-31' | dim_date entry created; fact_order loads – business rule may flag |
| Email case variance | customers.email = 'John.Doe@EXAMPLE.COM' | Stored as john.doe@example.com |
| Surrogate‑key gap | dim_customer.cust_sk sequence jumps from 1000 → 2000 | No functional impact; verify no FK break |
| Slowly changing dimension | Same cust_id appears twice with different status on different dates | Two dim_customer rows with overlapping valid_from/valid_to; is_current flag on latest |
4.5 Generating the Source Test Set
Below is a parameterised SQL script (PostgreSQL dialect) that produces a CSV for each source table. It uses generate_series for volume control and CASE expressions for edge‑case injection. The script is version‑controlled alongside the ETL code.
-- 01_customers.sql
\set rowcount 5000
\set edge_pct 0.02 -- 2 % edge rows
COPY (
SELECT
gs AS cust_id,
lower(md5(random()::text || gs::text)) || '@example.com' AS email,
CASE
WHEN random() < :edge_pct THEN 'BLOCKED' -- edge: blocked status
WHEN random() < 0.1 THEN 'INACTIVE'
ELSE 'ACTIVE'
END AS status
FROM generate_series(1, :rowcount) gs
) TO '/tmp/test_data/customers.csv' CSV HEADER;
-- 02_orders.sql
\set rowcount 20000
\set edge_pct 0.015
COPY (
SELECT
gs AS order_id,
CASE
WHEN random() < :edge_pct THEN NULL -- edge: null FK
ELSE floor(random() * 5000 + 1)::int -- valid cust_id
END AS cust_id,
(date '2023-01-01' + (random() * 365)::int) AS order_dt,
round((random() * 10000)::numeric, 2) AS total_amt
FROM generate_series(1, :rowcount) gs
) TO '/tmp/test_data/orders.csv' CSV HEADER;
-- 03_order_lines.sql
\set rowcount 80000
\set edge_pct 0.01
COPY (
SELECT
gs AS line_id,
floor(random() * 20000 + 1)::int AS order_id,
'SKU-' || lpad((floor(random() * 5000) + 1)::text, 5, '0') AS product_sku,
CASE
WHEN random() < :edge_pct THEN -3 -- edge: negative qty
ELSE floor(random() * 20 + 1)::int
END AS qty,
CASE
WHEN random() < :edge_pct THEN 99999999.9999 -- edge: overflow
ELSE round((random() * 500 + 0.01)::numeric, 4)
END AS unit_price
FROM generate_series(1, :rowcount) gs
) TO '/tmp/test_data/order_lines.csv' CSV HEADER;
Run the three scripts against a fresh source database (or a Docker‑spun instance). The CSV files become the golden source for the test run.
4.6 Running the ETL
- Spin up a clean target schema (
dbt run --target testor equivalent). - Load the CSVs into the source tables (
COPY FROM). - Execute the pipeline (Airflow DAG, dbt models, stored procedures).
- Export the target tables to CSV for comparison:
psql -d warehouse_test -c "\copy dim_customer TO '/tmp/out/dim_customer.csv' CSV HEADER"
psql -d warehouse_test -c "\copy fact_order TO '/tmp/out/fact_order.csv' CSV HEADER"
4.7 Validation Checks (Source‑to‑Target)
| Check | SQL / Pseudocode | Pass criteria |
|---|---|---|
| Row‑count parity (per order) | SELECT order_id, COUNT(*) FROM order_lines GROUP BY order_id vs SELECT order_id, COUNT(*) FROM fact_order GROUP BY order_id | Counts match for every order_id that has a valid cust_id |
| Amount reconciliation | SELECT o.order_id, o.total_amt, SUM(fl.line_total) FROM orders o JOIN fact_order fl ON fl.order_sk = o.order_sk GROUP BY o.order_id, o.total_amt | ABS(total_amt - SUM(line_total)) < 0.01 |
| Email normalisation | SELECT email FROM dim_customer WHERE email != lower(trim(email)) | Zero rows |
| FK integrity | SELECT COUNT(*) FROM fact_order f LEFT JOIN dim_customer c ON f.cust_sk = c.cust_sk WHERE c.cust_sk IS NULL | Zero rows |
| SCD overlap detection | SELECT cust_id, COUNT(*) FROM dim_customer WHERE is_current = true GROUP BY cust_id HAVING COUNT(*) > 1 | Zero rows |
| Error‑table capture | SELECT * FROM etl_error_log WHERE run_id = :run_id | Only rows that correspond to injected edge cases (null FK, negative qty, overflow) |
| Performance baseline | EXPLAIN ANALYZE SELECT * FROM fact_order WHERE date_sk BETWEEN :d1 AND :d2 | Execution time < 2 × baseline (optional) |
All checks are expressed as SQL assertions that can be run by a CI job (e.g., dbt test, Great Expectations, or a simple psql -v ON_ERROR_STOP=1 -f checks.sql). The test suite fails fast and produces a diff report that maps each failing assertion back to the originating edge‑case row.
5. Tool Considerations
| Tool class | Strengths | Weaknesses | When to pick |
|---|---|---|---|
| Pure SQL scripts (as above) | Zero dependencies, version‑controlled, runs on any RDBMS | Manual maintenance of edge‑case logic, limited data‑type coverage across platforms | Small‑to‑medium pipelines, homogeneous stack |
| Python + Faker / pandas | Rich distributions, easy to express complex relationships, can write to multiple sinks | Requires runtime environment, slower for >10 M rows | Heterogeneous sources, need for realistic name/address data |
| Commercial test‑data platforms (e.g., Delphix, Tonic) | Subset‑preserving cloning, built‑in masking, referential integrity across schemas | Cost, lock‑in, often require DB‑level agents | Large enterprises with strict compliance |
Open‑source generators (e.g., QA3 free test data generator at /tools/test-data-generator) | Browser‑based, schema‑aware, exports CSV/SQL, supports custom formulas, no install | Limited to CSV/SQL output, no direct DB cloning | Teams that want a quick, UI‑driven way to produce the same CSVs used above |
| Data‑diff frameworks (Great Expectations, dbt tests, Datafold) | Declarative expectations, CI integration, lineage | Learning curve, may need extra infra | When you already use dbt/Great Expectations for other quality gates |
Practical tip: Start with the SQL‑script approach for the core regression set. When you need to add a new source (e.g., a CSV feed from a partner), spin up the QA3 generator to produce a conforming sample in minutes, then promote the generated CSV into the version‑controlled test‑data repo.
6. Common Pitfalls & Mitigations
| Pitfall | Symptom | Mitigation |
|---|---|---|
| Static data becomes stale | New columns added to source break the load; tests still pass because they never exercise the new column | Add a schema‑drift detection step (compare information_schema.columns hash) to the CI pipeline; fail the build on mismatch |
| Edge cases only cover happy path | Production bug appears from a rare combination (e.g., status='BLOCKED' + future order_dt) | Use a combinatorial matrix (pairwise testing) to generate cross‑product of high‑risk attributes; keep the matrix in the test‑data contract |
| Referential integrity broken in generated data | FK violations cause load errors unrelated to ETL logic | Generate parent tables first, capture generated PKs, then feed them into child‑table generators (as shown in the SQL scripts) |
| Masking leaks PII | Real emails appear in non‑prod environment | Apply deterministic masking (hash + domain replacement) before data leaves the generation script; verify with a regex scan in CI |
| Test data volume too low for performance bugs | Pipeline runs fast in CI but times out in prod | Keep a performance‑scale data set (10‑20 % of prod) that runs nightly on a dedicated perf environment; separate from functional suite |
| No versioning of test data | Team overwrites CSVs, loses reproducibility | Store every generated CSV in Git LFS or an artifact store (S3 + versioned bucket) keyed by pipeline version tag |
| Validation logic lives only in CI | Developers cannot run the same checks locally | Package the checks as a standalone script (run_checks.sh) that accepts a target connection string; document usage in README |
7. Scaling the Approach
| Scale factor | Adaptation |
|---|---|
| More pipelines | Create a template repository (cookiecutter) that scaffolds the contract, generator scripts, and check suite. Each pipeline clones the template and fills in mapping tables. |
| Multiple target platforms | Parameterise the target DDL (partition keys, clustering keys) and generate platform‑specific DDL from a single logical model (e.g., using sqlglot transpilation). |
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.