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

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 dataSource‑to‑target test data
Isolated to a single function or stored procedureSpans the entire pipeline: extract, staging, transformation, load
Often synthetic, minimal rowsMirrors production cardinality, distribution, and edge cases
Validates logic onlyValidates logic and data‑type fidelity, referential integrity, and business rules
Easy to generate with a few INSERTsRequires 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.

DimensionQuestions to answerTypical choices
ScopeWhich pipelines? One‑off or regression suite?Full regression suite for core pipelines; ad‑hoc for new sources
Refresh cadenceHow often must the data reflect production?Nightly snapshot, weekly, or on‑demand for a release
Data sensitivityPII, PHI, financial data?Masked / synthetic for non‑prod; tokenized for prod‑like
VolumeRow counts per table, growth rate1 % of prod for functional, 10 % for performance
Platform heterogeneitySource: Oracle, SQL Server, flat files; Target: Snowflake, Redshift, BigQueryCross‑platform generator or per‑platform scripts
Automation levelCI/CD integration, manual run?Fully automated in CI, with a manual “refresh” button
Team ownershipQA 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)

TableKey columnsNotable constraints
customerscust_id PK, email UNIQUE, status NOT NULLstatus ∈ ('ACTIVE','INACTIVE','BLOCKED')
ordersorder_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_linesline_id PK, order_id FK → orders, product_sku, qty INT CHECK (qty > 0), unit_price NUMERIC(10,4)

4.2 Target Warehouse (Star Schema)

TableGrainColumns
dim_customerOne row per customercust_sk PK, cust_id, email, status, valid_from, valid_to, is_current
dim_dateOne row per daydate_sk PK, full_date, year, quarter, month, day_of_week
fact_orderOne row per order lineorder_sk PK, cust_sk FK, date_sk FK, product_sk FK, qty, unit_price, line_total, load_ts

4.3 Mapping Rules (excerpt)

Source → TargetRule
customers.cust_id → dim_customer.cust_idDirect copy
customers.email → dim_customer.emailLower‑case, trim
orders.order_dt → dim_date.full_dateDate‑only, surrogate key lookup
order_lines.qty * order_lines.unit_price → fact_order.line_totalRound to 2 decimals
orders.total_amt → not storedValidation only: SUM(line_total) = total_amt per order

4.4 Edge‑Case Catalogue

CategoryExample rows (source)Expected target behaviour
Null FKorders row with cust_id = NULLReject / route to error table; fact_order missing
Duplicate PKTwo customers rows with same cust_idLoad fails – primary‑key violation in dim_customer
Overfloworder_lines.unit_price = 99999999.9999 (exceeds NUMERIC(10,4))Load error or truncation – must be caught
Negative qtyorder_lines.qty = -5Check constraint violation → error table
Future dateorders.order_dt = '2099-12-31'dim_date entry created; fact_order loads – business rule may flag
Email case variancecustomers.email = 'John.Doe@EXAMPLE.COM'Stored as john.doe@example.com
Surrogate‑key gapdim_customer.cust_sk sequence jumps from 1000 → 2000No functional impact; verify no FK break
Slowly changing dimensionSame cust_id appears twice with different status on different datesTwo 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

  1. Spin up a clean target schema (dbt run --target test or equivalent).
  2. Load the CSVs into the source tables (COPY FROM).
  3. Execute the pipeline (Airflow DAG, dbt models, stored procedures).
  4. 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)

CheckSQL / PseudocodePass 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_idCounts match for every order_id that has a valid cust_id
Amount reconciliationSELECT 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_amtABS(total_amt - SUM(line_total)) < 0.01
Email normalisationSELECT email FROM dim_customer WHERE email != lower(trim(email))Zero rows
FK integritySELECT COUNT(*) FROM fact_order f LEFT JOIN dim_customer c ON f.cust_sk = c.cust_sk WHERE c.cust_sk IS NULLZero rows
SCD overlap detectionSELECT cust_id, COUNT(*) FROM dim_customer WHERE is_current = true GROUP BY cust_id HAVING COUNT(*) > 1Zero rows
Error‑table captureSELECT * FROM etl_error_log WHERE run_id = :run_idOnly rows that correspond to injected edge cases (null FK, negative qty, overflow)
Performance baselineEXPLAIN ANALYZE SELECT * FROM fact_order WHERE date_sk BETWEEN :d1 AND :d2Execution 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 classStrengthsWeaknessesWhen to pick
Pure SQL scripts (as above)Zero dependencies, version‑controlled, runs on any RDBMSManual maintenance of edge‑case logic, limited data‑type coverage across platformsSmall‑to‑medium pipelines, homogeneous stack
Python + Faker / pandasRich distributions, easy to express complex relationships, can write to multiple sinksRequires runtime environment, slower for >10 M rowsHeterogeneous sources, need for realistic name/address data
Commercial test‑data platforms (e.g., Delphix, Tonic)Subset‑preserving cloning, built‑in masking, referential integrity across schemasCost, lock‑in, often require DB‑level agentsLarge 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 installLimited to CSV/SQL output, no direct DB cloningTeams 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, lineageLearning curve, may need extra infraWhen 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

PitfallSymptomMitigation
Static data becomes staleNew columns added to source break the load; tests still pass because they never exercise the new columnAdd a schema‑drift detection step (compare information_schema.columns hash) to the CI pipeline; fail the build on mismatch
Edge cases only cover happy pathProduction 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 dataFK violations cause load errors unrelated to ETL logicGenerate parent tables first, capture generated PKs, then feed them into child‑table generators (as shown in the SQL scripts)
Masking leaks PIIReal emails appear in non‑prod environmentApply 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 bugsPipeline runs fast in CI but times out in prodKeep 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 dataTeam overwrites CSVs, loses reproducibilityStore every generated CSV in Git LFS or an artifact store (S3 + versioned bucket) keyed by pipeline version tag
Validation logic lives only in CIDevelopers cannot run the same checks locallyPackage the checks as a standalone script (run_checks.sh) that accepts a target connection string; document usage in README

7. Scaling the Approach

Scale factorAdaptation
More pipelinesCreate 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 platformsParameterise 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.