In this blog, we will see what the basic test scenarios for ETL testing are. However, before we go to the ETL test scenarios, let me first tell you what ETL is and how it works, along with the process involved with it.

WHAT IS ETL?

ETL stands for Extract-Transform-Load, and it is a process of how data is loaded from the source system to the data warehouse. Data is extracted from an OLTP database, transformed to match the data warehouse schema, and loaded into the data warehouse database. Many data warehouses also incorporate data from non-OLTP systems such as text files, legacy systems, and spreadsheets.

 

Types of data load in ETL

There are two major types of data load available based on the load process.

Full load (bulk load)

The data loading process when we do it the very first time. It can be referred to as bulk load or fresh load.

Incremental load

The modified data alone will be updated in target followed by a full load. The changes will be captured by comparing created or modified date against the last run date of the job.

What is ETL Testing?

ETL testing is done to ensure that the data that has been loaded from a source to the destination after business transformation is accurate. It also involves the verification of data at various middle stages that are being used between source and destination.

ETL Testing Process?

Like other Testing processes, ETL also goes through different phases. The different phases of the ETL testing process are as follows.

 

ODS (Operational Data Store)-: It is nothing but a database that has integrated data from different sources with different rules. It gets data from a transactional database directly. It will have a limited period of history data, hardly 30 to 90 days of data.

 

ETL TESTING LIFE CYCLE/PROCESS

Like other testing processes, ETL also go through different phases

The different phases of ETL testing are as follows.

Requirement analysis
Test planning
Test Design
Test Execution
Defect retesting
Test Closure/Sign off

 

Requirement analysis

The major inputs for the testing team would be the data model and mapping of the document when we start our analysis itself, we need to make sure that the source table or files are correct.

 

Test planning

There is not much difference between functional test plans except for a few items here we need to mention the data flow in both scope and out-scope sections.

 

Test Design

Test cases will be prepared along with the mapping document .in this stage itself we need to find requirement-related defects by doing an analysis of source data and mapping documents such as data type, data length, and relationships.

Test execution

Once all entry criteria are all set initial execution can be performed with the bulk load jobs and all the stages from source to target will be tested one by one.

Defect retesting

Fixed defects will be rested and validated in the case of any rejection. Based on impact analysis the test cases need to be executed as part of a defect fix.

Sign off

Based on the exit criteria of test execution the sign-off mail is to be sent to stakeholders to be proceeded to push the code to the next level.

 

ETL TEST SCENARIOS

Table structure verification

The column name, data type, and data length of the target table will be verified against the requirement

Constraint’s check

We ensure that all required constraints are available

Index check

We ensure that the index created with the required columns

Source data validation

Record the source table count and ensure that there won’t be any junk or bad data exits.

Data count check

Comparing the target data count against the source data count along with major filter or join condition.

Data comparison check

We ensure that source data was moved correctly to the target table by comparing data

Duplicate data validation

inject duplicate entries in the source table based on unique identifiers and ensure that the duplicate record will be rejected.

Data with primary key and foreign key check

Test the primary key and foreign key relationship with different test data for the parent and child table.

Null check

Inject the data with NULL for a NOT NULL column and verify that data will be rejected.

Data precision check

Create test data in the source table with different precisions and ensure the loaded data has precision as per requirement.

Date format check

All date columns are loaded in the defined date format or not

Conclusion

ETL Testing is important to make sure the correctness and completeness of the ETL method. This testing procedure plays a significant role in information storage and helps to make sure information integrity whereas information is being extracted, remodeled, and loaded into the information warehouse. This special testing method validates and verifies information to forestall information loss and duplication of records. Today, ETL Testing is gaining additional significance thanks to the magnified migration of high volumes of knowledge. Businesses ought to leverage ETL testing from a next-gen QA and freelance computer code testing services supplier for seamless information migration from completely different sources.

 

So that’s all about the basic test scenario of ETL testing part 1 we will meet in next blog with ETL test scenario part 2

Happy learning.