Data Integrity Testing in ETL: Completeness and Accuracy, With Examples

Data integrity testing proves that data stays complete, accurate and consistent as it moves through an ETL pipeline. In practice it combines three checks: completeness (nothing lost or extra), accuracy (values correct) and referential integrity (relationships intact).

Examples use one retail-orders load: source src_orders and src_customers, target fact_orders and dim_customer. You can download this exact data, with 8 planted defects, in the free ETL testing end-to-end project and run every query yourself.

What Is Data Integrity Testing?

It is the set of tests that shows the target can be trusted: every expected record is present, every value is right, and every relationship between tables holds. It overlaps with data quality checks (validity of values) and data reconciliation (matching totals).

Data Completeness Testing in ETL

-- counts after applying the same filter as the ETL
SELECT (SELECT COUNT(*) FROM src_orders WHERE status <> 'CANCELLED') AS expected,
       (SELECT COUNT(*) FROM fact_orders) AS actual;

-- missing keys
SELECT order_id FROM src_orders WHERE status <> 'CANCELLED'
EXCEPT SELECT order_id FROM fact_orders;

Also check by load date and by source system, so a missing day or a missing file is visible.

Data Accuracy Testing

-- values match the source for the same key
SELECT f.order_id FROM fact_orders f
JOIN src_orders o ON o.order_id = f.order_id
WHERE f.quantity <> o.quantity OR f.unit_price <> o.unit_price;

-- totals reconcile
SELECT (SELECT ROUND(SUM(quantity * unit_price - discount), 2) FROM src_orders WHERE status <> 'CANCELLED') AS source_total,
       (SELECT ROUND(SUM(net_amount), 2) FROM fact_orders) AS target_total;

Referential Integrity

SELECT order_id FROM fact_orders
WHERE customer_key IS NULL OR TRIM(customer_key) = '';

Every foreign key must exist in its dimension; unknown references should map to a defined “unknown” member, not NULL.

Data Integrity Testing Checklist

• ☐ Counts match after filters, overall and per load date
• ☐ No missing or extra keys (both directions)
• ☐ No duplicates on business keys
• ☐ Field values match source for each key
• ☐ Measures reconcile (sum, count, average)
• ☐ No NULL or orphan foreign keys
• ☐ Mandatory columns populated
• ☐ No truncation or precision loss
• ☐ Reports match a recalculation from source

Save it with your browser’s Print → Save as PDF, or get the free ETL Testing Interview Kit (PDF).

Example Defects Data Integrity Testing Catches

All five are planted in the free ETL testing end-to-end project so you can find them yourself.

Learn It Hands-On

ETL Testing: From Beginners to Advanced covers data quality, ETL testing concepts and hands-on transformation in Python and Power Query, so you can prove data integrity on real projects.

ETL testing fundamentals: ETL testing scenarios · types of ETL testing · ETL testing techniques · ETL testing challenges · data transformation testing · ETL test cases · ETL testing process

Frequently Asked Questions

What is data integrity testing?

Testing that data remains complete, accurate and consistent as it moves through systems, including completeness, accuracy and referential integrity checks.

What is data completeness testing in ETL?

Checking that every eligible source record reached the target and nothing extra was loaded, using counts and MINUS/EXCEPT queries after applying the same filters as the ETL.

How do you test data accuracy?

Compare target values with source values for the same keys, recalculate derived values and reconcile totals.

What is a data integrity testing checklist?

Counts, missing/extra keys, duplicates, field values, measure reconciliation, foreign keys, mandatory columns, truncation and report reconciliation.

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