What is ETL Testing: Importance, Process, and Types | DevstringxHarsha Yadav
What is ETL?
ETL Stands for Extraction, Transformation, and Load. So Basically ETL is a process of how data is loaded from the source system to the target systems. Firstly Data is extracted from the database, transformed into a meaningful schema, and then loaded to the target systems.
Important of ETL Testing
Once the ETL process is done, it becomes important to perform ETL Testing. ETL Testing is done to ensure that data is accurate which is loaded from different sources to the destination after transformation. In Which data verification at multiple stages that are being used between the source and the destination is involved.
It’s the responsibility of ETL Testers to ensure Data is not lost during the extraction and transformation process.
ETL Testing Process
Extraction is the process of extracting some relevant data from multiple resources.
Transformation is a process of transforming extracted data into a specific format according to our requirement it can be the Data warehouse format.
- In this, we define one or more keys that uniquely identify an entity. These different types of keys in SQL can be the primary key, foreign key, alternate key, composite key, or surrogate key, etc. A DW owns these keys and never allows other entities to alter/update them.
- Once the data is extracted, all unwanted data is removed from it. Or we can say data normalization; data cleanliness is done in this phase.
Once the above two processes are done, It is used to load data to the target systems.
Read Also:- Import Excel Data to SQLite DB Using Java
ETL Testing Types-
- Production Validation Testing
This type of ETL Testing process is performed to ensure data is accurate and meets the requirement of the business that is being transferred to production systems.
2. Source of Target Testing
This type of testing is performed to ensure that source data values are transformed to expected values.
3. Metadata Testing
This type of testing is performed to check data types, data constraints, data length, etc.
4. Data Completeness Testing
This type of testing is performed to ensure that that all the expected source data is loaded in target systems from the source systems. In this; data count is done from source to target systems.
5. Data Accuracy Testing
This type of testing is performed to ensure that the data is accurately loaded and transformed as expected.
6. Data Transformation Testing
This type of testing is performed to ensure that data is transformed into the expected format or not. In this, we can run multiple SQL queries together for each row and check the transformation rules.
7. Incremental ETL Testing
This type of testing is performed to ensure data integrity when new source data is added to the existing data. It ensures that updates and inserts are done as expected.
8. GUI/Navigation Testing
This type of testing is performed in the front end to check the navigation in UI.
9. Data Quality Testing
This type of testing is performed to ensure the syntax errors in data based on the invalid characters, patterns, upper or lower cases, etc. It is performed to avoid errors due to date or order. In this, we check data based on the data model.