ETL Testing Process: The 8 Steps of the ETL Testing Life Cycle

Most ETL defects are not found by clever SQL. They are found by following a disciplined ETL testing process: understand the requirement, read the mapping, plan what to test, prepare the right data, then execute and report. This guide walks through the ETL testing life cycle in eight steps, what each step produces, and the checks you run along the way.

If you are new to the subject, start with what ETL testing is, then come back here.

The ETL Testing Process at a Glance

Step 1: Requirement Analysis

Start with the business question the data must answer. A sales fact table that feeds a revenue dashboard has very different risk from a staging table nobody reports on. During requirement analysis, list:

• The reports, dashboards or downstream systems that consume the target tables
• The business rules (for example "revenue excludes cancelled orders", "amounts are stored in USD")
• The load type for each table: full load, incremental (delta) load, or slowly changing dimension
• Data volumes and the load window the job must finish in

Step 2: Source-to-Target Mapping Review

The mapping document (STM) is the tester's main input. Every target column should have a source column, a transformation rule and a data type. Review it before you write a single test case and raise questions on anything vague:

• Rules like "clean the name" with no definition of clean
• Missing default values for lookups that find no match
• Target data types or lengths that are smaller than the source (truncation risk)
• No rule for nulls, duplicates or late-arriving records

Catching a gap here costs minutes. Catching it after the data is in production costs a re-load.

Step 3: Test Planning

The test plan records scope (which tables, which loads), approach (manual SQL, automated Python checks or a tool), environments, test data strategy, roles, schedule and the entry and exit criteria. Use our ETL test plan template to write one in under an hour.

Step 4: Test Design

Turn each mapping rule into a test case, then add the standard checks every table needs. A typical design for one target table covers:

• Metadata: column names, data types, lengths, constraints (see metadata testing)
• Completeness: record counts, missing and extra records, totals
• Transformation: one test case per business rule
• Data quality: nulls, duplicates, ranges, formats (see data profiling)
• Integrity: every fact row has a valid dimension key
• Incremental and SCD behaviour: see SCD testing

For a ready-made list, see 35 ETL testing test cases.

Step 5: Test Data and Environment Setup

Production copies rarely contain the edge cases you need. Prepare source records on purpose: a null in a mandatory column, a duplicate key, a lookup code with no match, a customer whose address changes (for SCD Type 2), a timestamp right before midnight. Confirm you can query every layer: source, staging and target.

Step 6: Test Execution

Run the ETL job, then run your checks layer by layer. Begin with the cheap checks so you fail fast:

-- 1. Completeness: counts by load date
SELECT 'source' AS layer, COUNT(*) AS cnt FROM src.orders WHERE order_date = '2026-10-01'
UNION ALL
SELECT 'target', COUNT(*) FROM dw.fact_orders WHERE order_date = '2026-10-01';

-- 2. Missing records: in source but not in target
SELECT order_id FROM src.orders WHERE order_date = '2026-10-01'
EXCEPT
SELECT order_id FROM dw.fact_orders WHERE order_date = '2026-10-01';

-- 3. Transformation rule: amount converted to USD
SELECT s.order_id, ROUND(s.amount * s.fx_rate, 2) AS expected, t.amount_usd AS actual
FROM src.orders s
JOIN dw.fact_orders t ON t.order_id = s.order_id
WHERE ROUND(s.amount * s.fx_rate, 2) <> t.amount_usd;

Each query should return the expected result (matching counts, 0 rows). Record the actual result against the test case ID. More patterns are in ETL testing SQL queries.

Step 7: Defect Logging and Retest

A good ETL defect is reproducible by the developer in one step. Include the test case ID, the failing query, a handful of sample rows (source value, expected value, actual value), the load date and the environment. After the fix, re-run the failing test and the checks on any table that shares the same code path.

Step 8: Regression and Sign-off

Before sign-off, re-run the full suite on a fresh load, compare results with the exit criteria in the test plan and write a short test summary: tests run, passed, failed, open defects and risks. This is also the moment to automate the checks you will need again. See ETL automation testing.

STLC vs the ETL Testing Life Cycle

The ETL testing life cycle follows the same Software Testing Life Cycle (STLC) you already know from manual QA. The difference is what you test:

That is why manual testers move into ETL testing quickly: the process is familiar, and the new skill is mainly SQL. See manual tester to ETL tester.

ETL testing process & templates: ETL test plan template · ETL testing checklist · SCD testing · data profiling · metadata testing · ETL test cases

Frequently Asked Questions

What is the ETL testing process?

The ETL testing process is the sequence of steps used to verify an ETL pipeline: requirement analysis, mapping review, test planning, test design, test data and environment setup, test execution, defect logging and retest, and regression and sign-off.

What are the phases of the ETL testing life cycle?

The ETL testing life cycle follows the STLC: requirement analysis, test planning, test case design, environment and test data setup, test execution and test closure. ETL testing adds a mapping document review and data reconciliation at each layer.

What is the first step in ETL testing?

Understanding the business requirements and reviewing the source-to-target mapping document. Every transformation rule you test comes from the mapping, so gaps found there save the most time.

How long does ETL testing take?

It depends on the number of tables and rules. A single table with a clear mapping can be tested in a day; a new data mart with dozens of tables usually needs several weeks across planning, execution and regression.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures