ETL Test Scenarios and Types of ETL Test bugs

ETL Test Scenarios are used in validating an ETL Test Process.

We will learn below topics in this article

• • Test Scenarios for ETL Testing
  •  ETL Testing Bugs

ETL Testing Scenarios:

Some of the most common scenarios and test-cases used by ETL tester are listed below

1) Structure Validation  

Example:

• • Validation of source and the target table structure as per the mapping  document.
  • Data type , Field, Column name Validation in source and target System

2) Validate Constraints

Example:

• • Validating the constraints and ensuring that they are applied on the expected tables.

3) Validating Mapping document

Example:

• • Validating the mapping document to ensure all the information has been provided.

4) Null Validation         

Example :

• • Verify the Null values where Not Null is mentioned for that field.

5) Data Consistency check

Example:

• • Validating the misuse of integrity constraints like Foreign Key.

6) Date Validation check      

Example :

• • From_Date should not greater than To_Date
  • Data format , NULL values etc

7) Data Completeness Validation

 Example:

• • Count the number of records in the source and the target systems.
  • Boundary value analysis.
  • Primary keys validation

8) Data Cleaning

Example:

• • Un necessary data should be deleted before loading the data to the staging area.

Few other scenarios are given below:

9) Data Transform validation

Example:

• • Validate parent-child relationship in the data
  • Validate data types in the warehouse

10) Data Quality Validation    

Example :

• • Number check, date check, precision check, data check etc

11) Duplicate Validation       

Example:

• • Validating duplicate values in the target system

 Few Others ETL testing scenarios are listed below and we can create ETL test cases based on these

1. 1. Data availability
   2. Data load insert
   3. Data load update
   4. Incremental data load
   5. Data accuracy
   6. Data loss