What is ETL testing

What is ETL testing. Please some body give the full explanation or provide a good material on it.

Tags: ETL, testing

3 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Kannan Chandrasekaran on October 30, 2012 at 10:53am

Hi ,,

ETL :

ETL stands for Extract Transformation and Load, It collect the different source data from Heterogeneous System (DB), Transform the data into Data warehouse (Target)

At the Time of Transformation, Data are first transform to Staging Table (temporary table)

Based on Business rules the data are mapped into target table, this process are manually mapped / we configure using ETL Tool

ETL not transformed the Duplicate data

Data Transformation process speed based on Source and Target Data ware House

We need to consider the OLAP(Online Analytic Processing) Structure .Data warehouse Model

Source data consist of (XML,Flat file ,Database.Excel Report.Dataware House

We need to set the validation at time of data transformation like ‘Avoid the ‘NULL’ values in the table, validate the data type as using Tiny int instead of integer .etc

Based on the user requirement, ETL process starts

ETL :

ETL stands for Extract Transformation and Load, It collect the different source data from Heterogeneous System (DB), Transform the data into Data warehouse (Target)

At the Time of Transformation, Data are first transform to Staging Table (temporary table)

Based on Business rules the data are mapped into target table, this process are manually mapped / we configure using ETL Tool

ETL not transformed the Duplicate data

Data Transformation process speed based on Source and Target Data ware House

We need to consider the OLAP(Online Analytic Processing) Structure .Data warehouse Model

Source data consist of (XML,Flat file ,Database.Excel Report.Dataware House

We need to set the validation at time of data transformation like ‘Avoid the ‘NULL’ values in the table, validate the data type as using Tiny int instead of integer .etc

Based on the user requirement, ETL process starts

Let me know any queries

▶ Reply

Permalink Reply by tarique ahmed khan on October 30, 2012 at 1:19pm

Thanks Kannan Chandrasekaran for sharing knowledge

▶ Reply

Permalink Reply by bathwalshaili on October 30, 2012 at 12:02pm

Hi,

ETL ( Extract Transformation and Load)testing is different from Database testing or backend testing both in terms of complexity and scope and hence requires a different approach as against conventional testing. The methodology
primarily aims at identifying and mitigating existing data defects and general errors occurring prior to processing of data for analytics and reporting.

The ETL Testing Methodology includes following steps:

Understanding of data to be reported
Understanding and review of data model
Understanding and review of source to target mappings (transformations)
Data Quality Assessment of Source data
Packages testing
Schema testing (source and target)
Verify Data completeness
Verification of transformation rules
Comparison of sample data between source and target
Checking of referential integrities and relations (primary key foreign key)
Data Quality checks on target warehouse
Performance tests

Let me know if you require more details.

Regards,

Shaili

sbathwal@infocepts.com

▶ Reply

Permalink Reply by tarique ahmed khan on October 30, 2012 at 1:19pm

Thanks for reply

▶ Reply

Permalink Reply by Wayne Yaddow on October 31, 2012 at 2:41am

Hello Tarique,

I have written a book on the subject ("Testing the Data Warehouse", www.testingdatawarehouse.com ). Also, while conducting research for the book, I collected may fine papers on the subject. Send me your email address and I will send several fine papers on the subject.

Regards,

Wayne Yaddow (wyaddow@gmail.com)

▶ Reply

Permalink Reply by tarique ahmed khan on November 1, 2012 at 6:21pm

Thanks Wayne,

tariqueahmed89@gmail.com

It would be very help.....Thanks in advance

▶ Reply

Permalink Reply by rajesh lagishetty on November 5, 2012 at 9:32am

Hi Wayne,

plz send me ETL Testing material

rajeshlagishetty@gmail.com

▶ Reply

Permalink Reply by snehasish on November 19, 2012 at 7:12pm

Hi Wayne,

It will be great if you please send me material at snehasish.career@gmail.com.

Thanks in advance.

Thanks & Regards,

Snehasish Saha

▶ Reply

Permalink Reply by GSK Guptha on November 23, 2012 at 3:53pm

hi, plz send it to me also @gskguptha@gmail.com

▶ Reply

Permalink Reply by bathwalshaili on October 31, 2012 at 11:55am

Hi Wayne,

It would be great if you can send those papers to me as well.

My e-mail id is sbathwal@infocepts.com.

Regards,
Shaili

▶ Reply

Permalink Reply by Rohit Agrawal on November 2, 2012 at 11:22am

Hi Tarique ,

ETL testing is the heart of Data warehouse testing. It includes validations of data Extracted from source, transformations (functions) applied to data and lastly the target data warehouse(loading).Source could be a relational database(Oracle or SQL Server) or an ERP system or a non relational data sources like Mainframes and Unix.

Unit and integration test scripts are created to validate that the ETL is performed accurately from different sources.

The Referential Integrity checks are done to ensure that relationships between tables is not ambiguous. Surrogate testing is required to check and prevent any orphan records in child tables.

The performance of ETL should be tested with data that resembles closely to Production wrt quality and quantity.

Performance and stress tests are complementary in assessing the efficiency of ETL procedures. Performance tests evaluate the behavior of ETL with reference to a routine workload, i.e., when a domain-typical amount of data has to be extracted and loaded; in particular, they check that the processing time be compatible with the time frames expected for data-staging processes. On the other hand, stress tests simulate an extraordinary workload due to a significantly larger amount of data.

The recovery test of ETL checks for robustness by simulating faults in one or more components and evaluating the system response. For example, you can cut of the power supply while an ETL process is in progress or you can set a database online while an OLAP (Online Analytical Processing) session is in progress to check for restore policies' effectiveness.

▶ Reply

Permalink Reply by eKnowledge Infotech on January 11, 2013 at 1:30pm

Dear Candidates,

We are pleased to announce New Weekend Training Batch for ETL Testing Expert the details of which are as given below :

Training: ETL Testing Expert Training

Contents:  Basic Datawarehousing Plus BI
                    Unix,
                   Tools - Talend and Datastage
                  SQL
                   Live Scenarios
                  Interview Preparation