Most Important …Live Project Scenarios
As we have understanding of all the modules required for ETL testing i.e. Data Warehouse , UNIX , SQL , ETL Concepts , Now its time to integrate the knowledge gained so far and share some hands – on from the real project scenarios.
Project 1 :
Domain : Insurance
Process : Suppose the process is divided into various releases ( 1 release = 2 – 3 weeks ) for a year and each release has a ‘Release Date ‘, Both Development and testing are carried out release wise and there are separate environments for both. Development is carried out in ‘Dev’ environment and Testing is carried out in test environment. Just prior to every release, data is moved from ‘Dev’ to ‘Test’ Environment and Testing begins for that release.
Role of ETL Tester :
The Role of ETL Tester , testing strategies and approach varies from project to project and organization to organization. In general , for loading data from source to target mappings are created. For each mapping test cases are designed to test which are created and tested by ETL testers. Suppose in our case , the ETL tester is assigned two mappings. One for moving data from source to staging database , which is a direct dump and second mapping for moving data from staging database to data warehouse.After data is loaded into data warehouse, it is truncated (deleted) from staging area called as ‘ truncate and load’. The test data is dummy data but is similar to the production data.
Requirement :
- First requirement is to dump the data from the source to staging database .
Staging Table name = PolicyEnDetails_Source. This will be a direct load and will not involve much transformations . The data includes policy details. - Second requirement is to load the data from ‘tbl_PolEnDetails’ to ‘tbl_PolEnDetails_T ‘.
If the policy is renewed then its effective date should be updated .
Whenever effective date is updated ,the existing value in current effective date should move to the previous effective date and it should be updated with the new effective date.
With reference to the zip code , the state should be picked up from the lookup table tbl_StateDetails.
While loading state into target table , the state should be decoded to full name.
If policy is terminated , its status should change from ‘Active ‘ to ‘Terminated’.
The developer develops a Source Target Mapping document ( STMP) containing the details of the mappings and the process. This STMP Document is transferred to the Testers. The Tester refers to the Source target mapping document , checks the target table in the database and prepare the test cases covering all the possible scenarios.
Source Target Mapping Document :
Source And Target Table Structure :
