Browse Prior Art Database

ETL Testing Framework

IP.com Disclosure Number: IPCOM000249194D
Publication Date: 2017-Feb-09
Document File: 8 page(s) / 76K

Publishing Venue

The IP.com Prior Art Database

Abstract

The rationale of the SQL Script approach is not just simply to test the Transformation output, but to validate and test that all design components (mapping documentation, model design, physical model, and deployment and data definition) all integrate correctly and will be therefore a key static testing exercise. This will be done in a modular way, which will be easy to maintain and will be transparent. It also confirms that the output of the Extract,Transform and Load (ETL) processes will be correct Further can be implemented for BIG data Testing too; needs further investigation and analysis.

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 23% of the total text.

1

ETL Testing Framework

7

Disclosed below is a concept for “Extract Transform Load” (ETL) testing specific to transformation testing.

1. Background: What is the problem solved by your invention? There is not a single structured approach to for ETL Testing and hence there are challenges in planning and execution, at the same time there is no pre-defined approach towards regression ETL Testing. Based on discussion and investigation in multiple projects across industries below has been observed.

A. Multiple kickoff triggers like a. Often start with Data identification. b. Sometime start with ETL mapping Docs. c. Sometime start with Selection and Joint criteria. (Very few) d. Sometime start when Code is delivered.

B. Validation approach: Usually design multiple SQL scripts which is basically based on below.

a. Number of Fields in the Target (usually Mart) table. b. Sometime groping the fields of target table for which have same source

table. In all the above approach, it has been identified that every time (Structured Query Language) SQL Scripting skilled recourse for execution. Whenever there is a need of an enhancement in the existing Transformation/Mapping it always start with scratch of Test planning as there is no proper framework to handle multiple changes or enhancements in test model. Usually in Agile development environment its disaster.

At the end automation are not usually achievable.

There are few framework, available but are for unit testing; are not efficient and effective enough to handle System (ST) and System Integration testing (SIT).

2. Related Work/Prior Art: Please list and briefly describe the products, publications, patents, and other works that are most closely related to your invention. If any of these works solve or address the same problem, what are the drawbacks of the known solution(s)? https://marketplace.informatica.com/solutions/automated_testing_powercenter Above is suitable for unit and component integration testing.

https://www.infoq.com/presentations/etl-test-framework-tsqlt This can cover unit testing of database tasting usually difficult to implement in ETL System Testing and System Integration (ST/SIT) testing

3. Summary of Invention: Briefly describe the core idea of your invention (saving

2

the details for question #3 below). Describe the advantage(s) of using your invention instead of the known solutions described above. The rationale of the SQL Script approach is not just simply to test the Transformation output, but to validate and test that all design components (mapping documentation, model design, physical model, and deployment and data definition) all integrate correctly and will be therefore a key static testing exercise. This will be done in a modular way, which will be easy to maintain and will be transparent. It also confirms that the output of the ETL processes will be correct

Further can be implemented for BIG data Testing too; needs further investigation and analysis.

4. Description: Describe...