Please can someone list what are all the testing types performed on ETL/DW testing?
The below given testing types are most essential for most of business sensitive ETL processes.
1. Constraint Testing
2. Source to Target Count Testing
3. Source to Target Data Validation Testing
4. Threshold/Data Integrated Testing
5. Field to Field Testing
6. Duplicate Check Testing
7. Error/Exception Logical Testing
8. Incremental and Historical Process Testing
9. Control Columns and Defect Values Testing
10. Navigation Testing
11. Initialization testing
12. Transformation Testing
13. Regression Testing
14. Retesting and
15. System Integration Testing.
1. Constraint Testing: This is sometimes called as source and target's definition testing. in this phase of constraint testing, the test engineers identifies whether the data is mapped from source to target or not. The Test Engineer follows the below scenarios in ETL Testing process.
a) NOT NULL b) UNIQUE c) Primary Key d) Foreign key e) Check f) Default and g) NULL.
These tests can be performed directly with the good grip on SQL.
2. Source to Target Count Testing: In this testing type, we verify the Source to Target data is matched or not. A Test engineer can check in this view whether it is ascending order or descending order based on the requirement. Here tester concentrates more on the high level counts. Due to lack of time a tester can follow this type of Testing. This is a kind of smoke test in ETL processes.
3. Source to Target Data Validation Testing: In Source to Target Data Validation Testing, a tester can validate the each and every point of the source to target data in depth. Most of the financial projects, a tester can identify the decimal factors. This is a kind of core testing with the intention to test the data quality
4. Threshold/Data Integrated Testing: In this Testing, the Ranges of the data, A test Engineer can usually identifies the population calculation and share marketing and business finance analysis (quarterly, half-yearly, Yearly). This is a kind of boundary value analysis in regular website testing.
MIN MAX RANGE
4 10 6
5. Field to Field Testing: In the this testing, a test engineer can identify that how much space is occupied in the database. The data is integrated in the table cum data types.
NOTE: To check the order of the columns and source column to target column.
6. Duplicate Check Testing: This is a crucial test type in ETL testing since the process deals with millions of records, they have to be extra careful when checking the duplicate records. In this phase of ETL Testing, a Tester can face duplicate value very frequently so, at that time the tester follows database queries why because huge amount of data is present in source and Target tables.
Select ENO, ENAME, SAL, COUNT (*) FROM EMP GROUP BY ENO, ENAME, SAL HAVING COUNT (*) >1;
a) There are no mistakes in Primary Key or no Primary Key is allotted then the duplicates may arise.
b) Sometimes, a developer can do mistakes while transferring the data from source to target at that time duplicates may arise.
c) Due to Environment Mistakes also duplicates arise (Due to improper plugins in the tool).
7. Error/Exception Logical Testing: This testing type can be devided into two types again.
a. Delimiter is available in Valid Tables.
b. Delimiter is not available in invalid tables(Exception Tables).
8. Incremental and Historical Process Testing: In the Incremental data, the historical data is should not be corrupted with the increase in the new data. When the historical data is corrupted then this is the condition where bugs raise.
9. Control Columns and Defect Values Testing: I'm not exactly sure about this testing. This was introduced by IBM for their ETL testing process.
10. Navigation Testing: The Navigation Testing is the End user point of view testing. An end user is may not be technical expect to follow the friendlyness of the application. This navigation is called as bad or poor Navigation. So, while doing testing, A tester can identify this type of navigation scenarios to avoid unnecessary navigation.
11. Initialization testing: A combination of hardware and software installed in platform is called the Initialization Testing. Its a kind of acceptance testing in web applications.
12. Transformation Testing: While testing the mappings, verify what kind of mappings used and verify their functionality is being working fine or not.
13. Regression Testing: Testing the system after code modification to fix a bug or to implement a new functionality which makes us to to find errors. Identifying for regression effect is called regression testing.
14. Retesting: Re executing the failed test cases after fixing the bug is called Re testing. This is pretty much similar to the web application testing.
15. System Integration Testing: We do this testing when all the functionality of individual pieces is over and combined all the modules together and performing the testing is called System Integration Testing (SIT). Developer can integrate the modules there are 3 models.
a) Top Down
b) Bottom Up
Please add more if anyone has more idea on the above said things.