- ETL Testing Tutorial
- ETL Testing - Home
- ETL Testing - Introduction
- ETL Testing - Tasks
- ETL vs Database Testing
- ETL Testing - Categories
- ETL Testing - Challenges
- ETL - Tester's Roles
- ETL Testing - Techniques
- ETL Testing - Process
- ETL Testing - Scenarios(Test Cases)
- ETL Testing - Performance
- ETL Testing - Scalability
- ETL Testing - Data Accuracy
- ETL Testing - Metadata
- ETL Testing - Data Transformations
- ETL Testing - Data Quality
- ETL Testing - Data Completeness
- ETL Testing - Backup Recovery
- ETL Testing - Automation
- ETL Testing - Best Practices
- ETL Testing - Interview Questions
- ETL Testing Useful Resources
- ETL Testing - Quick Guide
- ETL Testing - Useful Resources
- ETL Testing - Discussion
ETL Testing – Data Completeness
Checking Data Completeness is done to verify that the data in the target system is as per expectation after loading.
The common tests that can be performed for this are as follows −
Checking Aggregate functions (sum, max, min, count),
Checking and validating the counts and the actual data between the source and the target for columns without transformations or with simple transformations.
Count Validation
Compare the count of number of records in the source and the target tables. It can be done by writing the following queries −
SELECT count (1) FROM employee; SELECT count (1) FROM emp_dim;
Data Profile Validation
It involves checking the aggregate functions such as count, sum, and max in the source and target tables (fact or dimension).
Column Data Profile Validation
It involves comparing the distinct values and the count of rows for each distinct value.
SELECT city, count(*) FROM employee GROUP BY city; SELECT city_id, count(*) FROM emp_dim GROUP BY city_id;
Duplicate Data Validation
It involves validating the primary key and the unique key in a column or in combination of columns that should be unique as per the business requirements. You can use the following query to perform duplicate data validation −
SELECT first_name, last_name, date_of_joining, count (1) FROM employee GROUP BY first_name, last_name HAVING count(1)>1;