Custom Search

ETL Testing – Basics

ETL Testing – Basics


ETL basically stands for Extract Transform Load – which simply implies the process where you extract data from Source Tables, transform them in to the desired format based on certain rules and finally load them onto Target tables. There are numerous tools that help you with ETL process – Informatica, Control-M being a few notable ones.

In ETL Testing, the following are validated:

  • Data File loads from Source System on to Source Tables
  • Transform Process that is designed to extract data from Source tables and move them to Staging tables
  • Data Validation of all Mapping Rules/Transformation Rules within the Staging tables
  • Data Validation within Target tables to ensure data is present in required format and there is no data loss from Source to Target tables

So ETL Testing implies – Testing this entire process using a tool or at table level with the help of test cases and Rules Mapping document.

Typically, data that is loaded into a data warehouse is derived from diverse sources of operational data, which may consist of data from databases, feeds, application files or flat files. The data must be extracted from these diverse sources, transformed to a common format, and loaded into the data warehouse. Typically, it is further aggregated into a data mart for efficient reporting. The ETL (Extract, transform and load) process is a critical step in any data warehouse implementation, and continues to be an area of major significance whenever the ETL code is updated.

An effective data warehouse testing strategy focuses on the main structures within the data warehouse architecture:

1. The ETL layer

2. The data warehouse itself

3. Associated data marts

4. The front-end business intelligence/reporting applications

ETL Testing is categorized into four different engagements:

  • New Data Warehouse Testing- a new data warehouse is built from ground up, gathering inputs from customer, extracting different data sources.  This is verified with the help of ETL tools
  • Migration Testing – In this type of engagement, migrating from the current ETL tool to a better option to improve efficiency
  • Change Request – In this type of project new data is added from different sources to an existing DW. Also, there might be a condition where customer needs to change their existing business rule or they might integrate the new rule.
  • Report Testing- Validating report layout, data in the report and calculation

 

ETL Testing Challenges

Some of the ETL Testing Challenges include:

  • Environment Instability
  •  Response time from the query executed, the failure of the jobs, the data set up required for the FIT testing, volume testing
  • Data selection from multiple source systems and analysis that follows pose great challenge
  • Volume and the complexity of the data
  • Inconsistent and redundant data in a data warehouse
  • Inconsistent and Inaccurate reports
  • Non-availability of History data

8 comments:

kiran m said...


Thanks for Sharing this valuble information and itis useful for me and ETL TESTING learners.We also provides the best Online ETL Testing Training classes.

Pratheek Kumar said...

Thanks for sharing valuable information and it is useful for Sun it labs provides the best ETL Testing Online Training classes.

naveen kumar said...


Thank you provide valuable informations and iam seacrching same informations,and saved my time SAS Online Training

Anusha said...

Nicely put.
Thank you

Qa Tools said...

It was so nice article.I was really satisified by seeing this article and we are also giving QA online training.The QA online training is one of the best QA online training institute in USA.

Cub Training said...

It was very nice article and it is very useful to Testing tools learners.We also provide Testing tools online training

Emergers Technologies said...

Just found your post by searching on the Google, I am Impressed and Learned Lot of new thing from your post. I am new to blogging and always try to learn new skill as I believe that blogging is the full time job for learning new things day by day.
"Emergers Technologies"

akhil varma said...

Great information about testing, currently am looking for  ETL testing Jobs in Hyderabad location. can you please share me more information about ETL like this.