ETL Testing / Data Warehousing Testing

Introduction on ETL Testing:

ETL stands for Extraction, Transformation and Loading of data. In data warehousing, data from multiple data sources is extracted, transformed (as per the business logic and data definition of target database) and loaded into cohesive database. Data warehouse efficiently handles day to day data along with organization’s historic data which may be very useful in creating reports for audits and compliance’s, future market predictions, etc. Since data loaded is very critical to the organization therefore it is very important for ETL process to undergo testing whether the extraction and transformation logic of data is as expected and also the loaded data is meaningful and as per requirements. Therefore ETL testing is new testing field for testers to pursue their career and this is the correct time to gain knowledge about ETL and Data warehousing testing.
 

Importance of Data Warehousing:

As organization businesses are growing day by day which is creating more and more data therefore there is a need to retain data in well-organized manner so that it is available for company’s day to day business expectations. Thus organizations are required to practice best extraction, transformation and loading technologies to integrate historical, day to day and other business data into centralized target database. In order to achieve this, many companies are creating their own database using various ETL tools to make the process very efficient.ETL tools make the process of extractions from multiple data sources very comprehensive and then transformation is done based on the required business rules. This transformed data is persisted into cohesive database known as Data warehousing and process is known as ETL data warehousing.

In order to make sure that the scripts applied in the ETL tools for extraction, business rules for transformation and data loaded are correct, there is a need to prepare the required test plan and test cases. Organizations may gain confidence in their business after Smooth ETL process which can only be achieved by efficient testing of entire ETL process, which is well planned and executed by the team of test experts who are proficient in ETL testing.
 

ETL Testing Classification:

Irrespective of using ETL tools, ETL testing can be classified into following types:

• New Data warehouse project: This scenario involves testing of DW from scratch. Required test plan is created and functional points are identified in ETL functional documents. Accordingly test plans are prepared and executed by ETL testers.
• Adding new source to existing Data warehouse: DW has multiple data sources. Data from these sources is transformed and persisted in cohesive DW database. Adding new data source involves addition of new data source to existing DW. Here testing expectations are to feed data from new source that is compatible with existing business rules and DW.
• Migration of Data warehouse: Here underlying database is migrated, for example, say from Oracle to Teradata depending on clients trust on vendor. Here ETL test team is expected to run all the existing test cases and make sure all test pass without any roadblock. Moreover test team conducts the performance test to justify the fact that the migration project has benefits over existing database.
• Change of Business Logic: Sometimes there is the need to change the existing business logic or add new business logic in data transformation. Test team here builds new test cases according to the new logic and make sure that old functionality is not affected by this new change (Regression).
• New or Existing Report testing: DW is the best source to generate new reports for audits, compliance’s, etc. Thus testing of report generation procedure as well as report data is conducted by ETL expert testers to deliver the quality end product.
• Platform Migration: Sometimes entire DW is migrated from one machine to another machine, for example, migration of database from low performance to high performance hardware boxes. In such scenario ETL tester are expected to execute existing test cases with additional performance tests to make sure ETL process is not broken and there are no roadblocks for business as usual.

ETL Testing procedure:

Testing procedures for ETL as follows:

• ETL test team is provided with functional requirements documents.
• Test team does the validations and come up with test estimates.
• Test planning is started based on above information.
• Test cases are developed according to the given ETL test scenarios.
•  After test cases are approved by senior member, they are executed by test team till the expected output is obtained.
• Performance tests are also executed by ETL expert test team.
• After successful completion of ETL testing, test summary report is prepared and handed over to the senior project team and testing tasks are signed off by ETL test team.

Over to you on ETL Testing / Data Warehousing Testing:

Like any other software application testing, ETL testing follows same testing principles and since this ETL testing is the niche skill required in the market therefore such resources are on high demand in the market. ETL testers are expected to have good knowledge of ETL procedures, SDLC, SQL queries and system performance testing. A human resource with all of these skill set is an expert ETL tester.

Refer : http://www.softwaretestingclass.com/etl-testing-data-warehousing-testing/