QA Testing
QA Testing for Informatica & PL/SQL ETL Migration to ADF
As part of its initiative to migrate its on-premise ETL application from Informatica and PL/SQL to Azure Data Factory (ADF), a Global Research and Advisory Company required testing and data validation services to ensure timely, available data for Reporting and BI functions.
Client Challenges and Requirements
- Large number (1100+) of tables to be tested.
- Manual testing is limited to a small dataset and is time consuming.
- The right ETL testing automation tools for performing data checks.
- DQ and RI issues in data.
- Data types difference between Oracle and Synapse resulted in ETL error, truncated data, and special char issues.
- Flexible and smart test strategy to test production data for incremental loads each day as the CDC loads for both DB systems run at different times.
Bitwise Solution
- Developed automation scripts to eliminate the need for manual data comparisons. Created DDL mapping script to compare the structure of on-prem and cloud database systems.
- Prioritized DDL validation above Data Validation to address typical issues resulting from erroneous DDL mappings, such as special char conversion, data accuracy, and data truncation, in order to catch faults early in the cycle.
- Adopted optimal data type testing strategy to achieve good performance and save space in DB.
- Grouped test cases in test plans and executed them in parallel to reduce testing time.
- Planned and coordinated testing in lower environments to reduce risks/issues in Production. To avoid slowness in the production environment, testing was done in a production-like setting. Focused on base tables first to correct errors caused by code issues so that they do not spread to underlying tables.
Tools & Technologies We Used
Bitwise Python-based ETL automation framework
JIRA
Oracle SQL Developer
Microsoft SSMS
PL/SQL
TSQL
Power BI
Key Results
Fully tested and defect free tables delivered with 100% test coverage
Reduction in manual testing efforts by 60 - 70% using custom automation tool
Reduction in UAT defects by 98 - 99%
