SMART ETL Test

 

In any ETL (Extract, Transform & Load) Testing, it is very important to focus on specific type of tests than to just compare the source & target tables. To uncover specific issues related to data extraction process, transformation logic and loading process, we need to test smartly by focusing on specific type types. Below is the guideline on SMART Test Approach for performing ETL testing.

Test Type	Scope of Validation
Meta Data Test	·      Validate based on ETL mapping sheets, DB Schema of Source & Target ·      Verify that data field types and formats are specified appropriately ·      Verify that source data type and target data type are same ·      Verify that length of data types in both source and target are equal ·      Verify the name of columns & naming standards in the table against mapping doc ·      Verify that the columns that cannot be null have the ‘NOT NULL’ constraint ·      Verify that the unique key and foreign key columns are indexed as per the requirement ·      Compare table and column metadata across environments to ensure that changes have been migrated appropriately
Data Quality Test	·      Validate data of source table to target (dimension) table ·      Validate row creation date for accuracy ·      Validate for null check in target columns with not null constraints ·      Validate for duplicate check based on unique key, primary key and any other constraints as per the business requirements ·      Validate for duplicate check in target columns generated by combining values of multiple source columns ·      Validate number columns of source table has only numbers ·      Validate date format in source table for correctness & uniformity with other date columns ·      Validate data is not truncated in the column of target tables ·      Check for any rejected records
Data Cleansing Test	·      Validate deletion of unnecessary columns before loading into the target area as per the business rules ·      Validate replacement of incorrect/invalid data by default value and reporting invalid data as per the business rules ·      Validate the accuracy of any rejected records as per business rules
Data Integrity Test	·      Validate the data integrity between dimension and fact tables ·      Validate that the Fact table shouldn’t contain the keys that doesn’t exist dimension tables ·      Schema data integrity validation (Star schema/ Snowflake/Galaxy) ·      Validate count of records with null foreign key values in the child table ·      Validate count of invalid foreign key values in the child table that do not have a corresponding primary key in the parent table ·      Validate that the data conforms to reference data standards - values in certain columns should adhere to the values in a domain ·      Compare domain values across environments – Validate reference data values from the development environments has been migrated properly to the test and production environments ·      Track reference data changes - Baseline reference data and compare it with the latest reference data so that the changes can be validated
Data Transformation Test	·      Validate the accuracy of computed/generated data in target table ·      Validate the accuracy of computed values based on the boundary conditions given in the transformation business logic/rules ·      Validate the accuracy of computed values based on the equivalence class conditions given in the transformation business logic/rules
Data Completeness Test	·      Validate all expected data is loaded accurately into target table ·      Validate record count between source & target tables ·      Check for any rejected records ·      Validate based on aggregate value (ex: total sales, total claims, total premium paid) ·      Validate source & target table based on descriptive statistics (Min, Max, Avg, sum, count of null) ·      Validate complete data set in source and target table using Minus query ·      Validate matching rows in source and target table using Intersect query
Incremental ETL Test	·      Validate new inserts in source table are getting processed and reflected in target tables as per ETL process & business rules ·      Validate new updates in source table are able to lookup the existing record in the target table and update it. ·      Validate that the changed data values in the source are reflecting correctly in the target data ·      Validate by comparing all the records that got updated in the last few days in source & target on the incremental ETL run frequency ·      Validate that the denormalized values of target tables are updated based on the changes in source data
ETL Performance Testing	·      Validate the ability of ETL jobs/system to handle multiple users & transactions ·      Validate that the response time of the various data load into data warehouse are within prescribed and expected time frames ·      Validate the scalability of ETL system with addition of more loads based on the business expectations