Extract, Transform, Load (ETL) is the process of gathering data (Extract) from an unlimited number of sources, converting it (Transform) into a format that can be analyzed, and centralizing it into a single repository (Load). ETL is an alternative, but related approach designed to push processing down to the database for improved performance.
ETL testing refers to the process of validating, verifying, and qualifying data while preventing duplicate records and data loss. It ensures the transfer of data from different sources to a target occurs with strict adherence to transformation rules and remain compliant with all validity checks.
ETL Testing Life Cycle
Similar to other testing processes, ETL Testing also passes through various phases as listed below.
Analyzing the Requirements and Mapping document
This phase helps in understanding the following:
- How “Source Data” is structured
- How the mappings work
- What are the transformation rules from Source to Target
- The gaps (if any)
Effort Estimation and Test Planning
This phase helps in coming up with the required time to perform the testing (including different phases: test design, test execution, re-testing and regression testing, etc). Test planning is documenting the scope of the test, start & end points of testing, testing approach, etc.
Test Design
The Test Design phase starts where test scenarios and test cases are designed. This covers all the requirements listed in the specifications and the transformation rules mentioned in the mapping documents. Various test design techniques are used to create the test suite, which handles all positive and negative sides of the testing.
Test Execution and Test Data Creation
These are one of the most crucial phases of testing. All the test cases designed covering the requirements and mapping rules are executed here with appropriate test data. Any deviations will be tracked as defects.
Defect Retest and Regression Testing
These phases focus on re-validating the defects once fixed and make sure the defect fix is not impacting the functionality that was working as expected earlier. This complete exercise is repeated till defined exit criteria are met.
Test Reporting
Test Reporting is the final destination of testing where all the test results are published to the stakeholder with associated key metrics and then signed off the testing cycle.
ETL Test Prerequisites
Though the ETL testing cycle is conceptually similar to other testing cycles, there are certain prerequisites any ETL tester should meet:
- Strong SQL (database) knowledge
- Access to the required folders/databases
- Test suite is reviewed, accepted, and ready for execution
- Test data available in test environment
- Test environment is ready to start the testing
- All unit tests are completed and summarized
- Knowledge on basic data validation steps between source and target
- Knowledge on UNIX commands.
- Elementary knowledge of Data Warehousing concepts
Challenges
Recognizing difficulties early in the ETL process can prevent expensive delays and hindrances. Designing a source-to-target mapping document and defining clear business requirements from inception is necessary. Constant and multiple revisions to requirements can substantially impact the progress. ETL testers need to have a clear understanding of the data transformation requirements to clearly understand the end-user requirements.
Some of the challenges to look out for from the earliest point include:
- Restricted accessibility of source information
- Incorrect, incomplete, or duplicate data
- Huge amounts of historical data that make the data volume vast and extremely complex to perform ETL testing in the target system
- Complex SQL concepts for data validation
- Unavailability of inclusive test bed
- Lack of understanding of the business requirements and data flow.
- Existence of many ambiguous software requirements
- Trouble acquiring and building test data
- Sample data extracted from production does not represent all the requirements and testing scenarios
- Loss of data during ETL process
Some Best Practices
- Verify all
- Verify the data loss and data truncations
- Verify how invalid data, duplicates, exceptions, and data rejections are handled
- Test the performance
- Create automated test scripts for much of the ETL process, including validations
XTIVIA’S Extensive Competence with respect to ETL/MDM Testing Services
XTIVIA utilizes best practices in ETL testing and helps clients go live to production on time with zero defect leakage. If you have questions or need help with ETL Testing, please engage with us via comments on this blog post or reach out to us here.