ETL Testing Data Warehouse Testing Tutorial (A Complete Guide)

ETL Testing / Data Warehouse Process and Challenges:

Today let me take a moment and explain my testing fraternity about one of the most demanding and upcoming skills for my tester friends i.e. ETL testing (Extract, Transform, and Load).

This tutorial will present you with a complete idea about ETL testing and what we do to test the ETL process.

Complete List Tutorials in this series: 

  • Tutorial #1: ETL Testing Data Warehouse Testing Introduction Guide
  • Tutorial #2: ETL Testing Using Informatica PowerCenter Tool
  • Tutorial #3: ETL vs. DB Testing
  • Tutorial #4: Business Intelligence (BI) Testing: How to Test Business Data
  • Tutorial #5: Top 10 ETL Testing Tools

It has been observed that Independent Verification and Validation is gaining huge market potential and many companies are now seeing this as a prospective business gain.

ETL Testing Data Warehouse Testing (1)

Customers have been offered a different range of products in terms of service offerings, distributed in many areas based on technology, process, and solutions. ETL or data warehouse is one of the offerings which are developing rapidly and successfully.

ETL process

Through ETL process, data is fetched from the source systems, transformed as per business rules and finally loaded to the target system (data warehouse). A data warehouse is an enterprise-wide store which contains integrated data that aids in the business decision-making process. It is a part of business intelligence.

Why do Organizations Need Data Warehouse?

Organizations with organized IT practices are looking forward to creating the next level of technology transformation. They are now trying to make themselves much more operational with easy-to-interoperate data.

Having said that data is the most important part of any organization, it may be everyday data or historical data. Data is the backbone of any report and reports are the baseline on which all vital management decisions are taken.

Most companies are taking a step forward in constructing their data warehouse to store and monitor real-time data as well as historical data. Crafting an efficient data warehouse is not an easy job. Many organizations have distributed departments with different applications running on distributed technology.

ETL tool is employed in order to make a flawless integration between different data sources from different departments.

The ETL tool will work as an integrator, extracting data from different sources; transforming it into the preferred format based on the business transformation rules and loading it into a cohesive DB known as Data Warehouse.

Well planned, well defined and effective testing scope guarantees smooth conversion of the project to production. A business gains real buoyancy once the ETL processes are verified and validated by an independent group of experts to make sure that the data warehouse is concrete and robust.

ETL or Data warehouse testing is categorized into four different engagements irrespective of the technology or ETL tools used:

  • New Data Warehouse Testing: New DW is built and verified from scratch. Data input is taken from customer requirements and different data sources and a new data warehouse is built and verified with the help of ETL tools.
  • Migration Testing: In this type of project, customers will have an existing DW and ETL performing the job, but they are looking to bag new tools in order 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 customers need to change their existing business rules or they might integrate the new rules.
  • Report Testing: Report is the end result of any Data Warehouse and the basic propose for which DW builds. The report must be tested by validating the layout, data in the report and calculation.

ETL Process

ETL testing

ETL Testing Techniques

1) Data Transformation Testing: Verify if data is transformed correctly according to various business requirements and rules.

2) Source to Target Count Testing: Make sure that the count of records loaded in the target is matching with the expected count.

3) Source to Target Data Testing: Make sure that all projected data is loaded into the data warehouse without any data loss or truncation.

4) Data Quality Testing: Make sure that ETL application appropriately rejects, replaces with default values and reports invalid data.

5) Performance Testing: Make sure that data is loaded in the data warehouse within the prescribed and expected time frames to confirm improved performance and scalability.

6) Production Validation Testing: Validate the data in the production system & compare it against the source data.

7) Data Integration Testing: Make sure that the data from various sources has been loaded properly to the target system and all the threshold values are checked.

8) Application Migration Testing: In this testing, ensure that the ETL application is working fine on moving to a new box or platform.

9) Data & constraint Check: The datatype, length, index, constraints, etc. are tested in this case.

10) Duplicate Data Check: Test if there is any duplicate data present in the target system. Duplicate data can lead to incorrect analytical reports.

Apart from the above ETL testing methods, other testing methods like system integration testing, user acceptance testing, incremental testing, regression testing, retesting and navigation testing are also carried out to make sure that everything is smooth and reliable.

ETL/Data Warehouse Testing Process

Similar to any other testing that lies under Independent Verification and Validation, ETL is also going through the same phase.

  • Requirement Understanding
  • Validating
  • Test Estimation is based on a number of tables, the complexity of rules, data volume and performance of a job.
  • Test Planning is based on the inputs from test estimation and business requirements. We need to identify here as what is in scope and what is out of scope. We will also look out for dependencies, risks and mitigation plans during this phase.
  • Designing Test cases and Test scenarios from all the available inputs. We also need to design mapping documents and SQL scripts.
  • Once all the test cases are ready and approved, the testing team will proceed to perform pre-execution checks and test data preparation for testing.
  • Lastly, execution is performed until exit criteria are met. So, the execution phase includes running ETL jobs, monitoring job runs, SQL script execution, defect logging, defect retesting and regression testing.
  • Upon successful completion, a summary report is prepared and the closure process is done. In this phase, sign off is given to promote the job or code to the next phase.

The first two phases i.e., requirement understanding and validation can be regarded as pre-steps of ETL test process.

So, the main process can be represented as below:

ETL main process

It is necessary to define a test strategy which should be mutually accepted by stakeholders before starting actual testing. A well-defined test strategy will ensure that the correct approach has been followed to meet the testing aspirations.

ETL/Data Warehouse testing might require writing SQL statements extensively by the testing team or maybe tailoring the SQL provided by the development team. In any case, a testing team must be aware of the results that they are trying to get using those SQL statements.

Difference Between Database and Data Warehouse Testing

There is a popular misunderstanding that database testing and data warehouses are similar while the fact is that both hold different directions in testing.

  • Database testing is done using a smaller scale of data normally with OLTP (Online transaction processing) type of databases while data warehouse testing is done with large volume with data involving OLAP (online analytical processing) databases.
  • In database testing, normally data is consistently injected from uniform sources while in data warehouse testing most of the data comes from different kind of data sources which are sequentially inconsistent.
  • We generally only perform CRUD (Create, read, update and delete) operations during database testing while in data warehouse testing we use read-only (Select) operation.
  • Normalized databases are used in DB testing while demoralized DB is used in data warehouse testing.

There are a number of universal verifications that have to be carried out for any kind of data warehouse testing.

Given below is the list of objects that are treated as essential for validation in this testing:

  • Verify that data transformation from source to destination works as expected.
  • Verify that the expected data is added to the target system.
  • Verify that all DB fields and field data are loaded without any truncation.
  • Verify data checksum for record count match.
  • Verify that for rejected data proper error logs are generated with all the details.
  • Verify NULL value fields
  • Verify that duplicate data is not loaded.
  • Verify data integrity

=> Know the difference between ETL/Data warehouse testing & Database Testing.

ETL Testing Challenges

This testing is quite different from conventional testing. Many challenges are faced while performing data warehouse testing.

Here are a few challenges that I experienced on my project:

  • Incompatible and duplicate data
  • Loss of data during ETL process.
  • Unavailability of the inclusive testbed.
  • Testers have no privileges to execute ETL jobs on their own.
  • The volume and complexity of the data is huge.
  •  Fault in business processes and procedures.
  • Trouble acquiring and building test data
  • Unstable testing environment
  • Missing business flow information

Data is important for businesses to make critical business decisions. ETL testing plays a significant role in validating and ensuring that the business information is accurate, consistent and reliable. It also minimizes the hazard of data loss in production.

Hope these tips will help you ensure that your ETL process is accurate and the data warehouse built by this is a competitive advantage for your business.

Complete List of ETL Testing Tutorials:

  • Tutorial #1: ETL Testing Data Warehouse Testing Introduction guide
  • Tutorial #2: ETL Testing Using Informatica PowerCenter Tool
  • Tutorial #3: ETL vs. DB Testing
  • Tutorial #4: Business Intelligence (BI) Testing: How to Test Business Data
  • Tutorial #5: Top 10 ETL Testing Tools

This is a guest post by Vishal Chhaperia who is working in an MNC in a test management role. He has extensive experience in managing multi-technology QA projects, Processes and teams.

Further Reading =>> Best ETL Test Automation Tools

Have you worked on ETL testing? Please share your ETL/DW testing tips and challenges below.

Recommended Reading

205 thoughts on “ETL Testing Data Warehouse Testing Tutorial (A Complete Guide)”

  1. hi, i am MBA Graduate and i want to learn ETL testing can i know which is the best training center in ameerpet and 100% placement institution.
    mobile no9849497976
    jai krishna

  2. Very nice article. Thanks for posting.

  3. Hi all, I Want to learn ETL testing , Can you please suggest me the best institutes in hyderabad

    • If you want to learn ETL testing .
      In Bangalore have one Institution name is NSR Technologist at BTM Layout.

    • hi, i am Bcom Graduate and i want to learn ETL testing can i know which is the best training center in ameerpet and 100% placement institution.
      mobile no 9916108712


  4. Hi all, I Want to learn ETL testing , Can you please suggest me the best institutes in hyderabad. Please mail me

  5. The above post regarding ETL testing is really good that helps me to enhance my theory knowledge. Can anyone share some useful documents that I can use to improve my practical knowledge.

  6. Hi All,

    I have 9 years of experience in Manual testing . I would like to know which tool is better to learn now ETL/Data Warehouse testing tool or Selenium Webdriver. Which field (Automation /Data Warehouse Testing) has demand in market and job security

    • Hi Vani,

      Selenium and phython either of them is better

  7. Very informative in simple working…thanks Vishal Chhaperia for the post.

  8. Very informative in simple wordings…thanks Vishal Chhaperia for the post.

  9. Hi can anyone send me materials of etl testing basics

  10. Hi Vishal,

    Very good column.

    You have written a statement in Difference between Database testing and DWH testing like this:

    Normalized databases are used in DB testing while demoralized DB is used in data warehouse testing.

    Here is that demoralized or de-normalized?


  11. Hi,

    Good Post.

    Currently i am in Manual testing and interested in ETL testing i have some good knowledge on SQL concept. With SQL concept i can survive ETL testing?

  12. Hi All,

    I have 8 years of experience in Manual testing . I would like to know which tool is better to learn now ETL/Data Warehouse testing tool or Selenium Webdriver. Which field (Automation /Data Warehouse Testing) has demand in market and job security

  13. I have one year exp in automation testing and 7 months of exp as as a data analyst (back end work) in ms SQL with this knowledge can I switch my career in etl testing n let me know the scope for etl testing

  14. It’s really provides good idea to the ETL Testers .– Thanks…

  15. what are different ETL Testing tools available in the industry?
    whether etl tools and etl testing tools are same or not.

  16. Hi,

    I am sandeep working in an MNC as ETL Test Professional.

    You can Reach out to me for Online classes on ETL Testing .
    I use to cover ETL,Data warehosue and BI Testing.

    contact me at for Demo Classes


  17. Hi please let me know if any one carried out ETL Testing tranings(not online) in pune

  18. Hi
    we can have different kinds of dataware housing testing based my consern.
    like …..
    1.ETL testing
    2.Report testing
    3.Data Reconciliation testing.

    ETL testing is table level testing . we will have different sorts of source from BU.i mean they will come up with DB table,excel etc and provide BU logic .we need to check based on businees rule its getting loaded or not and then all the source data is getting loaded into target dataware house or not.we have to check the source table matched with target database table .the etl load may be happen based schedule that might be consider incremental load(transation data have been loaded in incremental load ).
    Report testing would be format level and data level.
    dataware house data published or displayed in reporting.we have to check the BU expecting format.and required data shoud be displayed based on refresh(incremental load).
    Reconciliation testing is nothing but back tracking.
    thanks ,

  19. Hi All, any one suggest me how was the market for ETL testing. I am zaffar i have experience in siebel admin. I want to learn pls suggest me any online training institue.

  20. I want to learn ETL testing in Noida location. Anyone?

  21. Is SQL requiered for DWH/ETL testing? SInce i wish to pursue career in same i would like to know the criteria required for DWH/ETL training..

  22. Hi all

    Is anyone working as a Etl tester or informatica Qa tester
    In USA please respond me back with ur email id
    I need some tips n confidence from you guys
    As I started searching a job.
    My email id

  23. Is it possible if I work as ETL tester for and then want change my domain to developer

  24. what are different ETL tools available in the industry?
    whether ETL tools like Informatica and ETL tools are same or not.

  25. Hello Friend
    I am working in MNC in Delhi and I have 5 years Experience in QTP . If anyone is interested in QTP.
    Please feel free to contact on the below numbere


  26. Hi,

    I have 4.2 years of experience in Manual testing. I am planning to move to ETL.Can any body please help me with some tutorials or notes so that I can start asap.

  27. An informative one. ETL topics coverage and the order created this article is good. It gives a single learning point to start with ETL basics which would make a reader to understand easily.

    I am also starting off creating a similar kind of knowledge, share base especially ETL. My goal is, I would like to share across my knowledge whatever I have learnt in DWBI testing.

  28. I want to move into ETL. HAving experience of 6.5 years in manual testing. have small experience in ETL testing but want to explore more. plz help me with institute name.

  29. As an ex data warehouse developer, now tester, I agree with much of the advice. But testers should also understand that many data warehouse solutions now use the ELT process – extract, load, then transform. Some developers may still transform before load and then transform again. So it’s important to speak with the developers and understand what methods they are using to get the data into the data marts.

  30. Do a ETL tester need to be an expert in Informatica/QLIKVIEW or any BI tool or Testing Knowledge with strong SQL/Unix concepts will suffice?

  31. Hi STH Team,

    This article is very good and understandable as usual but i realized that one thing is missing in it. Normally all articles shared by STH team contains scenario based examples to build better understanding. These examples are core of STH articles and should not be missed.

    Thank you
    Farrukh Shahzad Ahmed Islamabad Pakistan

  32. Awful English. Grammatically wrong, poor sentence structure, clumsily worded. It’s like an out of tune piano being played by a drunk. Should we feel sorry for the “demoralized” DB?
    —excerpt from article below—-
    “Normalized databases are used in DB testing while demoralized DB is used in data warehouse testing.”

  33. We had many issue with ETL testing, primarily because production data do not always cover all business rules in ETL and manual preparation of test data is quite expensive process.
    At the end of the day, we had to developed a GTL QAceGen: Business Logic Driven Data Generator. Description is on informatica market place in case you are interested

  34. Please send test strategy for data migration testing (common scenario) for windows application

  35. i need your help, i facing problem to clear a informatica Developer Interview, i always rejected after first round, and not getting exactly reason. so please share to me about how to prepare and what is flow of preparation.
    please share all things .Email ID –
    please share study material.

  36. Interesting perspective on this issue, in my understanding ETL is the operation that connects OLTP and OLAP, basically collects data from several OLTP sources and puts it in OLAP data allowing cross system analysis

  37. for DIM tables the easiest way to test source to target is using an SQL minus operation. For a fact table this approach is generally nor possible, you can either write your own SQL code (which may be very long and complex) or compare the FACT table or MI report to the source data at the relevant point in time.

  38. How do I access the Tutorial? Please let me know. When I click on Tutorials link, it is bringing me to this Intro page.

  39. i want test cases for security functionality related to data protection, identification and authetication of mysql server.
    can anyone help me.

  40. I completed ETL course, can any one help me to get job. Cell no, 7259422660

  41. Hi,

    I am Sravan having 8 Years of working experience in MNC organization as ETL Tester Professional.

    You can Reach out to me for Online ETL Testing classes below Email Id.

    Duration : 45 Hours
    Fees : 3500


  42. what are performance testing checks one should do in etl testing and cloud based etl architecture?
    please help with this topic or give me a link which could help me

  43. check the basic parameter as described in eth testing.
    No tool can check Performance of the ELT. Only you can test.


Leave a Comment