ETL Testing Checklist: 50 Checks Before You Sign Off

Use this ETL testing checklist to make sure every release gets the same coverage, whoever is testing. It has 50 checks grouped in the order you normally run them. Each check is a yes/no question; anything you cannot tick is either a test you still need to run or a risk to note in the sign-off.

For the detailed test cases behind each check, see ETL testing test cases.

Before Testing (1–6)

• ☐ 1. Mapping document reviewed and questions closed
• ☐ 2. Business rules and reports that use the data are known
• ☐ 3. Load type known for every table (full, incremental, SCD)
• ☐ 4. Test plan with entry and exit criteria agreed
• ☐ 5. Test data contains the agreed edge cases
• ☐ 6. Read access to source, staging and target confirmed

Metadata (7–12)

• ☐ 7. All target tables and columns exist as per mapping
• ☐ 8. Data types match the mapping
• ☐ 9. Column lengths and precision are not smaller than the source
• ☐ 10. NOT NULL, primary key and unique constraints in place
• ☐ 11. Default values set where specified
• ☐ 12. Naming conventions followed

Details and SQL: metadata testing in ETL.

Completeness (13–19)

• ☐ 13. Source and target record counts match (after filters)
• ☐ 14. No records missing in target
• ☐ 15. No extra records in target
• ☐ 16. Rejected and filtered rows accounted for
• ☐ 17. Sum of key amounts reconciles per day or month
• ☐ 18. All mapped columns populated
• ☐ 19. No truncated values

Transformation (20–27)

• ☐ 20. Each business rule has a passing test case
• ☐ 21. Data type conversions correct (dates, numbers)
• ☐ 22. Rounding and precision per rule
• ☐ 23. Lookups return the right value
• ☐ 24. Lookups with no match get the default or are rejected per spec
• ☐ 25. Aggregations match at target grain
• ☐ 26. Derived and concatenated columns correct
• ☐ 27. Time zone and business date logic correct

Data Quality (28–34)

• ☐ 28. No nulls in mandatory columns
• ☐ 29. No duplicate business keys
• ☐ 30. Values within valid ranges
• ☐ 31. Codes in the allowed list
• ☐ 32. Formats valid (emails, phone numbers, postcodes)
• ☐ 33. Leading/trailing spaces and case handled
• ☐ 34. Column profile in target matches source (min, max, distinct, null %)

How to profile columns: data profiling in ETL testing.

Integrity (35–38)

• ☐ 35. Every fact row has a valid dimension key (no orphans)
• ☐ 36. Unknown members (-1 key) used per spec
• ☐ 37. Surrogate keys unique and never reused
• ☐ 38. Parent-child relationships preserved

Incremental Loads and SCD (39–44)

• ☐ 39. Only new and changed rows picked up in a delta load
• ☐ 40. Re-running the same load creates no duplicates
• ☐ 41. Deleted source rows handled per spec (soft or hard delete)
• ☐ 42. SCD Type 1 columns overwritten
• ☐ 43. SCD Type 2 changes create a new version and close the old one
• ☐ 44. Exactly one current row per business key

Full SCD scenarios with SQL: SCD testing.

Performance and Operations (45–48)

• ☐ 45. Load completes within the agreed window at production volume
• ☐ 46. Job restarts cleanly after a failure without duplicates
• ☐ 47. Audit columns (load date, batch ID) populated
• ☐ 48. Errors logged and alerts raised on failure

See ETL performance testing.

Sign-off (49–50)

• ☐ 49. Regression suite passes on a fresh load
• ☐ 50. Test summary shared: results, open defects, risks

Here is a quick query that covers checks 13, 28 and 29 for one table in a single pass:

SELECT COUNT(*)                                          AS total_rows,
       COUNT(*) - COUNT(DISTINCT order_id)               AS duplicate_keys,
       SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_ids
FROM dw.fact_orders
WHERE order_date = '2026-10-01';

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

Frequently Asked Questions

What is an ETL testing checklist?

An ETL testing checklist is a list of yes/no checks that every ETL release must pass, covering metadata, completeness, transformations, data quality, integrity, incremental loads, performance and sign-off.

What should be checked in ETL testing?

At minimum: table structure, record counts, missing and extra records, every transformation rule, nulls, duplicates, valid values, orphan keys, incremental and SCD behaviour, and load performance.

How is a checklist different from test cases?

A checklist says what must be covered. Test cases say exactly how: the query, the test data and the expected result. Use the checklist to plan and review coverage, and test cases to execute.

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