/*----- Common Test Cases for ETL Testing-------*/

 

Pipeline and Activities Verification:
 

• Verify Pipelines and Activities Exist for loading the data from Source to Destination
• Verify Pipelines and Activities are executed Successfully and data is loaded into Source to Destination

 

Database, Schema and Table Verification:

 

• Verify Database Connections exist for both Source and Destination
• Verify Database Schema exist for both Source and Destination
• Verify Table Exist in the same Database Schema from both Source and Destination

 

Table and Columns Verification:
 

• Verify Record Count of table matches in both Source and Destination
• --Verify Column to Column Mapping of table matches in both Source and Destination

 

• Verify Table -> Column Exist in the same Database Schema from both Source and Destination
• Verify Table -> Column Datatype and Size is same in both Source and Destination
• Verify Record Count of table -> Column matches in both Source and Destination
• Verify Data present in the Table -> Column matches in both Source and Destination

 

Data Cleansing Verification:

 

• Verify Formatting of Table -> Column (Format such as Date/Integer/Character) in the Destination
• Verify Data Truncation for Table -> Column in the Destination table
• Verify Mandatory Columns for Table -> Column in the Destination table
• Verify Duplication of data for Table -> Column in the Destination table for Primary Key/Unique Columns
• Verify Dependency if we have Parent and Child table relationship among them.

 

History and Recent Record Identification: (Incremental Load using Key Columns)

 

• Verify Insertion of new record in the Destination table, if the record coming from the Source doesn't exist in the Destination (IsDelete=0)
• Verify Insertion of Existing record in the Destination table, if the existing records has any Column Changes coming from Source which need to be updated in the Destination Table. (For Recently Changed record which is inserted the ISDelete=0 and for the Old Record the IsDelete=1)

 

Data loaded multiple times in the Destination from Source:

 

• Verify NewId/LoadId is created/generated if data is loaded multiple times on the same Destination table
• Verify that the record count gets increased when data is loaded multiple times on the same Destination table
• Verify data is getting inserted (Appended) in to Destination table when data is loaded multiple times from the Source for the same Destination table
• Verify Insertion of new record in the Destination table, if the record coming from the Source doesn't exist in the Destination (IsDelete=0)
• Verify Insertion of Existing record in the Destination table, if the existing records has any Column Changes coming from Source which need to be updated in the Destination Table. (For Recently Changed record which is inserted the IsDelete=0 and for the Old Record the IsDelete=1).

 

Business Rules:

 

• Verify data present in the Destination (Table -> Column) is based on the Business Rules specified