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.
Why ETL Testing is Important?
Once 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 a responsibility of ETL Testers to ensure Data is not lost during the extraction and transformation process.
Extraction is a process of extracting some relevant data from the 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 above two process is done, It is used to load data to the target systems.
ETL Testing Types-
- Production Validation Testing-
This type of ETL Testing process is performed to ensure data is accurate and meets the requirement of business which is being transferred to production systems.
- Source of target Testing-
This type of testing is performed to ensure that source data values are transformed to expected values.
- Metadata Testing-
This type of testing is performed to check data types, data constraints, data length etc.
- 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.
- Data Accuracy Testing-
This type of testing is performed to ensure that the data is accurately loaded and transformed as expected.
- 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.
- Incremental ETL Testing-
This type of testing is performed to ensure the data integrity when new source data is added to the existing data. It ensures that updates and inserts are done as expected.
- GUI/Navigation Testing-
This Type of testing is performed in front end to check the navigation in UI.
- 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 ordering. In this, we check data based on the data model.
Originally published at https://www.devstringx.com on Oct 12, 2021.