At Prolifics, our team have significant experience in ETL (Extract, Transform, Load) testing, using a structured approach and our own IP to map data types, catalogue transformations and validate output data in target systems.
ETL is a technique often used in data warehouses, bringing together data from different source systems, performing conversion operations mid way and loading harmonised data in target databases / systems. There is often a huge amount of data generated when testing ETL processes, which can get complex very quickly, especially when multiple source systems are involved.
Challenges
Here are the main challenges testers face during ETL testing:
- Acquiring and building huge volumes of test data
- Accommodating dynamic data rules
- Unavailability of source to target mapping documents
- Slow response times when running SQL queries on large datasets
- Unstable testing environments
- Lack of automated test reporting
Our approach
Huge amounts of business information, often including Financial, CRM and ERP data are collated in a Staging Database, before being moved to a Data Warehouse and processed for reporting.
Our team's mission is to:
- Understand requirements and analyse the situation, identifying data sources, data types and volumes
- Catalogue the in-scope transformations expected for the input data types, so that tests can be targeted to provide good coverage
- Identify data sets to prove that the ETL process is working for the agreed scope of source data and in scope transformations
- Execute a set of repeatable tests and provide feedback on the tests run, including passed / failed tests
Using our custom-made functional testing accelerator, Effecta, our expert consultants are able to quickly:
- Validate the data required and the data sources
- Determine data quality and performance acceptance criteria, as well as data transformation rules
- Review data dictionary (metadata)
- Validate the source to target mapping
- Validate the Data model (dimensional modelling and normalised approach)
- Ensure error login / exception handling/recoverability
- Parallel execution and precedence
- Measure performance and output from both ETL pull logic-full / incremental (a.k.a. Delta pull)
- Validate output from full ETL Test (End-to-End scenarios)
- Report testing (drill down / drill through)
Data Validation
We validate the technicalities as Mapping, Data Types and Precision, Stage Variables, Constraints, Functions at Transformer Stage, and Job Parameters to check for connectivity of different data sources within the Job Design.
Regression Testing
Our consultants make sure the existing Business process is not impacted due to new functionality changes. Prolifics use a scheduler, such as TWS Maestro, to line up multiple jobs in a QA environment, instead of executing the jobs manually.
System Integration Testing
The next stage is the end-to-end integration testing. We extract the data from the source system, validate the business rules in data stage jobs, and provision the data for various down-stream system consumption. All the processes can be automated, end-to-end.
Advantages of Effecta
Key Validations
- Excel validations include CSV format
- Flat file validations
- Sequential file validations
- Able to validate any type of file with result accuracy
Database Validations
- Database validations include Oracle, SQL Server, SAP S/4HANA
- Validate the data required and data sources
- Parallel processing feature is implemented for performance improvement
Test Reporting
- Automated test reporting
- Consolidated summary
- Chunk wise summary report
- Field level detail validation report
