Preparing ETL testing test cases are the very important step in overall data warehousing or integration process since quality cannot be compromised.
Table structure verification
The column name, data type and data length of target table will be verified against the requirement.
Ensure that all required constraints are available.
Ensure that index created with required columns. If the job has a workflow like, drop index -> load data -> create index
then compare the index before job run and after job run.
Source data validation
Record the source table count and ensure that there won’t be any junk or bad data exists.
Data count check
Comparing the target data count against source data count along with major filter or join condition.
Data comparison check
Ensure that source data was moved correctly to the target table by comparing data.
Duplicate data validation
Inject duplicate entries in source table based on unique identifiers and ensure that the duplicate record will be rejected and updated in the log file.
Data with primary key and foreign key check
Test the primary key and foreign key relationship with different test data for parent and child table.
Trim and NULL check
Inject a data with right space and verify the data will be loaded without any space.
Inject a data with NULL for an NOTNULL column and verify that data will be rejected.
Data precision check
Create test data in source table with different precisions and ensure the loaded data has precision as per requirement.
Date format check
All date columns are loaded in defined date format or not.
Number format check
Ensure that the number value needs to be loaded with the right format as per requirement.
Data truncate check
Inject data in source table with more than defined length in the target table and verify that the data need to be loaded with truncate function.
All business transformation rules validation
Every transformation mentioned in TSD needs to be tested, keep separate test cases for every transformation.
Subsequent job run without data changes
Check the job run consistency by running job second time without any data changes. Ensure that there will no changes in the target table.
Subsequent job run with data changes
Ensure that the changes applied in target table after running with changes in the source table. Insert some records, update some records and delete some records in the source table to run this case.
Running job with bad data
Inject some junk data in the source table and verify all bad data rejected and updated in bad file.
ETL job failure case check
Create a failure case and verify the job status and failure notifications. Failure case can be created as below,
- Some job might have explicit fail command as per requirement after decision command. Create test data such a way that the decision command triggers the fail command.
- If job does not have explicit requirement, then
- Delete the source or target table
- Alter the table structure
Rollback scenario check when a job fails
When a job fails the preceded DML actions need to revert it back. Create such case like some logic to be failed after DML operations, and ensure that new changes won’t be available in the target after job run.
Performance of transformation and loading process check
The performance of transformation and loading commands need to be tested.
Log file and content check
The log file name, path, content and create time to be verified for both success and failure cases.
Mail notification and content check
The notification name, content, attachment and receive time to be verified for both success and failure cases as per requirement.
Testing the retention rule
Create test data with satisfy retention rule and ensure the rule requirement meets.
Verify the view query result meets with SQL query prepared based on requirement.