ETL testing is a crucial part of data warehouse systems. It involves performing end-to-end testing of a data warehouse application. Read on to learn about each important phase of the ETL testing process.
1. Requirements Testing: The objective of requirements testing is to ensure that all defined business requirements are as per the business user’s expectations. During requirements testing, the testing team should perform the analysis of business requirements in terms of requirement test ability and completeness. The following pointers should be considered during requirement testing:
2. Data Model Testing: The objective of this testing is to ensure that the physical model is in accordance with the logical data model. The following activities should be performed during this testing:
3. Unit Testing: The objective of Unit testing is to validate whether the implemented component is functioning as per design specifications and business requirements. It involves testing of business transformation rules, error conditions, mapping fields at staging and core levels. The following pointers should be considered during Unit Testing:
4. System Integration Testing: Once unit testing is done and all exit criteria of unit testing are met, the next phase of testing is integration testing. The objective of integration testing is to ensure that all integrated components are working as expected. The data warehouse application must be compatible with upstream and downstream flows and all the ETL components should be executed with correct schedule and dependency. The following listed pointers should be considered during Integration Testing:
5. Data Validation Testing: The objective of this testing is to ensure that the data flow through the ETL phase is correct and cleansed as per the applied business rules. The following listed pointers should be considered during Data Validation Testing:
6. Security Testing: The objective of this testing is to ensure that only an authorized user can access the reports as per assigned privileges. While performing security testing, the following aspects should be considered:
7. Report Testing: The objective of report testing is to ensure that BI reports meet all the functional requirements defined in the business requirement document. While performing functional testing, the following aspects should be considered:
Verify the report runs with a broad variety of parameter values and in whatever way the users will be receiving the report (e.g. A subscription runs and deploys the report as desired)
8. Regression Testing: The objective of regression testing is to keep the existing functionality intact each time new code is developed for a new feature implementation or if existing code is changed during correction of application defects. Prior to regression testing, impact analysis must be carried out in coordination with developers in order to determine the impacted functional areas of application. Ideally, 100% regression is recommended for each drop/build. In case builds are too frequent and there is a time limitation on test execution, the regression should be planned for execution based on priority of test cases.
9. Performance Testing: The objective of performance testing is to ensure that reports or data on the reports are loaded as per the defined nonfunctional requirements. In performance testing, different types of tests would be conducted such as load test, stress test, volume test etc. While executing performance testing, the following aspects should be considered:
10. Test Data Generation: As test data is very important, in ETL testing, appropriate test data needs to be generated. So, depending on the volume of data, test data will be generated and used by using a test data generation tool or SQL scripts. As a best practice, generated test data would be similar to production like data.
Data masking for test data generation – Data masking is the process of protecting personal sensitive information. Data is scrambled in such a way that sensitive information can be hidden but still usable for testing without being exposed. A few data masking techniques:
11. User Acceptance Testing: The objective of UAT testing is to ensure that all business requirements or rules are met as per business user perspective, and the system is acceptable to the customer.
Subscribe to our fortnightly newsletter!