Modern business management is based on Business Intelligence or BI and you should have a clear idea about business intelligence before learning ETL testing or Database Testing.
What is BI
It is a fact that you need to analyze the data or the details of daily business transactions thoroughly to take strategic decisions. BI or Business Intelligence is the process of collecting raw data and transform it into useful information and also to make it easier for analysis.
What is Data Warehouse
Data Warehouse is a database designed for query and analysis of data and not for the transaction of data. A data warehouse is created by collecting data from multiple heterogeneous sources. Data from different sources and databases will be collected and transformed into the desired format and will be stored at a single location called a data warehouse. For example, in an industry, there will be production details, expenditure details, salary accounts , sales details etc. Everything will be stored at different sources. This data will be brought together in the data warehouse.
What is ETL
ETL or Extraction Transfer and Loading are the processes involved in data warehousing. Data in different databases will be in different formats. In ETL the data will be extracted from different databases and will be transformed to match the data warehouse schema and then load into it.
ETL Testing Processes
As in the case of any other testing, ETL testing too has several phases. Actually, it has five phases.
- Identification of data source and requirements
- Acquisition of data
- Business logics and dimensional modeling implementation
- Building and populating data
- Building reports
Different Types of ETL Testing
- Production Validation Testing
- Validation Testing
- Application Upgrades
- Metadata Testing
- Data completeness Testing
- Data Accuracy Testing
- Data Transformation Testing
- Data Quality Testing
- Incremental ETL Testing
- GUI / Navigation Testing
How to Create ETL Test Case
The major objective of ETL testing is to verify whether the data transformed and loaded are accurate and this concept can be applied to different tools and databases. It can verify the data in the middle stage also. ETL Mapping sheets and DB Schema of the source, target are the two important documents the tester will always use while performing ETL Testing.
ETL Test Scenarios and Test Cases
As in the case of any other testing, there are different test scenarios in ETL testing and different test cases also. Here is a list of different test scenarios and test cases
- Mapping doc validation for which the test case will be verification of mapping doc to see whether the corresponding ETL information is provided or not.
- Validation is related to verification of source database for its different aspects.
- Constraints validation to ensure constraints are defined for specific tables as desired.
- Data consistency issues
- Completeness issues
- Correctness issues
- Data Quality
- Null validate
- Duplicate Check
- Date validation
- Complete data validation
- Data cleanliness
Similarly, there are different types of ETL bugs also about which you will be learning in details in ETL tutorials.
Difference between Database Testing and ETL Testing
- ETL testing is to ensure whether the data is moved properly, whereas database testing is aimed at checking whether the data is in tune with the standards defined for the data model in that database.
- ETL testing is aimed at finding duplicate data entered while database testing is aimed at finding missing data.