Top 11 Essential Considerations for Performing ETL Testing

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:

• Verification of logical data model with design documents.
• Verification of Many – Many attribute relationship
• Verification of the type of keys used
• All transformation rules must be clearly specified
• Target data type must be specified in data model or design document
• Purpose and overview of the reports must be clearly specified
• Report design should be available
• All report details such as grouping, parameters to be used, filters should be specified
• Technical definitions such as data definitions and details about the tables and fields would be used in reports
• All details for header, footer and column heading must be clearly specified
• Data sources and parameter name and value must be clearly specified
• Verification of technical mapping in terms of report name, table name, column name and description of each report must be documented

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:

• Verification of logical data model as per design documents
• Verification of all the entity relationships as mentioned in design document
• All the attributes, keys must be defined clearly
• Ensure that the model captures all requirements
• Ensure that the design and actual physical model are in sync
• Ensure naming conventions
• Perform schema verification
• Ensure that the table structure, keys and relationship are implemented in the physical model as per the logical model.
• Validation of Indexes and Partitioning

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:

• All transformation logic should work as designed from source till target
• Surrogate keys have been generated properly
• NULL values have been populated where expected
• Rejects have occurred where expected and log for rejects is created with sufficient details
• Auditing is done properly
• All source data that is expected to be loaded into target, actually is loaded− compare counts between source and target
• All fields are loaded with full contents− i.e. no data field is truncated while transforming

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:

• ETL packages with Initial Load
• ETL packages with Incremental Load
• Executing ETL packages in sequential manner
• Handling of rejected records
• Exception handling verification
• Error logging

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:

• Data comparison between source and target
• Data flow as per business logic
• Data type mismatch
• Source to target row count validation
• Data duplication
• Data correctness
• Data completeness

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:

• Unauthorized user access
• Role based access to the reports

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:

• Report drill down, drill up and drill through
• Report navigation and embedded links
• Filters
• Sorting
• Export functionality
• Report dashboard
• Dependent reports

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)

• Verify that the expected data is returned
• Verify that the performance of the report is within an acceptable range
• Report data validation (Correctness, Completeness and integrity)
• Verify required security implementation
• Automating processes whenever possible will save tremendous amounts of time
• Verify that the business rules have been met

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:

• Compare the SQL query execution time on Report UI and backend data
• Concurrent access of the reports with multiple users
• Report rendering with multiple filters applied
• Load the high volume of production like data to check the ETL process and check whether ETL process does it in an expected timeframe
• Validate the OLAP system performance by browsing the cube with multiple options
• Analyze the maximum users load at peak and off-peak time that are able to access and process BI reports

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:

• Randomization: Generate random data within the specified data range
• Substitution: The data presented in columns will be replaced completely or partially with artificial records.
• Scrambled: The data type and size of the fields will be intact, but the records would be scrambled.

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.

Write to us with your thoughts about ETL testing and visit us at Nitor Infotech if you’d like to learn more about our services.