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 much in demand 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 ETL process.

Complete List Tutorials in this series: 

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

ETL Testing _ Data Warehouse Testing - Tips, Techniques, Process and Challenges

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 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 the vital management decisions are taken.

Most of the companies are taking a step forward for 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. 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 in cohesive DB known are Data Warehouse.

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

ETL or Data warehouse testing is categorized into four different engagements irrespective of 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 new data warehouse is built and verified with the help of ETL tools.
  • Migration Testing – In this type of project customer will have an existing DW and ETL performing the job but they are looking to bag new tool 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 customer needs to change their existing business rule or they might integrate the new rule.
  • 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 layout, data in the report and calculation.

ETL process

(Note: Click on the image for enlarged view)

ETL testing

ETL Testing Techniques

1) Data transformation Testing: Verify that 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 and 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 data warehouse within prescribed and expected time frames to confirm improved performance and scalability.

6) Production Validation Testing: Validate the data in 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, it is ensured 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 systems. Duplicate data can lead to wrong 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 is also carried out to make sure everything is smooth and reliable.

ETL/Data Warehouse Testing Process

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

  • Requirement understanding
  • Validating
  • Test Estimation based on a number of tables, the complexity of rules, data volume and performance of a job.
  • Test planning based on the inputs from test estimation and business requirement. We need to identify here that what is in scope and what is out of scope. We also look out for dependencies, risks and mitigation plans in this phase.
  • Designing test cases and test scenarios from all the available inputs. We also need to design mapping document and SQL scripts.
  • Once all the test cases are ready and are approved, testing team proceed to perform pre-execution check and test data preparation for testing
  • Lastly, execution is performed till exit criteria are met. So, 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 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:

main process

It is necessary to define test strategy which should be mutually accepted by stakeholders before starting actual testing. A well-defined test strategy will make sure that correct approach has been followed meeting the testing aspiration.

ETL/Data Warehouse testing might require writing SQL statements extensively by testing team or maybe tailoring the SQL provided by the development team. In any case, a testing team must be aware of the results 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 warehouse is similar while the fact is that both hold different direction 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 perform the only CRUD (Create, read, update and delete) operation in 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 is a number of universal verifications that have to be carried out for any kind of data warehouse testing.

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 expected data is added to the target system
  • Verify that all DB fields and field data is loaded without any truncation
  • Verify data checksum for record count match
  • Verify that for rejected data proper error logs are generated with all 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. There are many challenges we faced while performing data warehouse testing.

Here are few challenges 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 by their own
  • Volume and complexity of data are very huge
  •  Fault in business process and procedures
  • Trouble acquiring and building test data
  • Unstable testing environment
  • Missing business flow information

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

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

Complete List of ETL Testing Tutorials:

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

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

193 Comments on “ETL Testing Data Warehouse Testing Tutorial (A Complete Guide)

  1. If anybody learn ETL testing ,Database Testing,BI Testing in Pune

    pls contact +91-9885320101

  2. Nice Article.Thanks, Now i got basic idea abt DWH.But i want to knw some ratcial tips also, as in interview, they are asking for hands on experience. Also in SQL what all i need to learn.Please guide

  3. hi friends this is rajesh i have knowledge on java and 3 frame works past 8 months i am searching job in banglore
    . Someone suggested me to change the profession and go for ETL or WareHousing. As i don’t know anything about ETL and WH also.

    compare to java and etl testing which is the best in future salary and secure.please suggested me

    thank u

  4. Hi,
    I do have an excellent opening for Database Test Lead.
    Database Testing Test Analyst with minimum 4 year(s) experience at Gurgaon location. Location- Gurgaon , Telephonic Interviews on this saturday i.e 8th June 2013

    Job Description:

    Experience required 2 to 5 years Domain: Investment Baking (Preferred) Essential Experience: (Must have skills) • 2 to 5 years of experience in a Software organization on testing (Web technologies, Client/ Server, Database). Some experience in development may also be considered. • Expertise in database testing and should be well versed with complex SQL queries. • Good experience of manual/database testing throughout the testing life cycle. • Understand the application and write test scripts/conditions using a tool (e.g. Test Director) and its review. • Experience to carry out Test execution, logging of defects, preparation of daily test logs, updating Requirements Traceability Matrix. • Should be well versed with UNIX commands and knowledge in shell scripting is preferred. • Experience of using tools such as Test Director for test planning and defect management. • Experience in adapting to testing processes and provide inputs for test process improvements. • Experience in the financial services industry would be an added advantage. Desirable : • Experience in the financial services industry would be an added advantage. • Domain Knowledge in investment banking preferred.(Bonds & Equities, Credit, Collaterals , Exotics) etc • Certifications like ISTQB will be an added advantage.

  5. Any good training centers for etl testing in hyderabad or bangalore . pls let me know

  6. Hi,
    I Provide Online Training on ETL Testing.
    I am working in a MNC in Hyderabad.
    ETL TESTING
    ============================================

    what is Testing ?
    what is the need for Testing?
    Testing Methodologies
    Software development life cycle
    software Testing life cycle
    Types of Testing

    Data Ware Housing Concepts:
    • What is Data Ware House?
    • Difference between OLTP and Data Ware Housing
    • Data Extraction
    • Data Transformation
    • Data Loading

    Data Marts
    • Dependent Data Mart
    • Independent Data Mart

    Data ware housing Approaches
    Top Down Approach
    bottom up Approach

    Data Base Design
    • Star Schema
    • Snow Flake Schema

    SCD(slowly changing dimension)
    • Type-1 SCD
    • Type-2 SCD
    • Type-3 SCD

    Basic Concepts in SQL
    • SQL Concepts(A-Z) – execute the pl/sql procedures,functions….
    • Overview of ETL Tool Architecture
    • Testing Functionality on Different Transformation Rules

    Data Ware House Life Cycle
    Different Types of Testing Techniques in ETL
    ETL Testing Introduction
    • What is use of testing
    • What is quality & standards
    • Responsibilities of a ETL Tester

    Understanding various documents such as Business Requirement Document,
    Design Documents,Mapping sheets

    • ETL Testing Work Flow Process
    • How to Prepare the ETL Test Plan
    • How to design the Test cases in ETL Testing.
    • How to reporting the Bugs in ETL Testing ?
    • ETL Testing Responsibilities in DataStage, Informatica, Abinitio etc;
    • How to detect the bugs through database queries
    • ETL Performing Testing & Performing Tuning

    ETL Test Cases
    etl testing life cycle
    day to day process

    UNIX

    Thanks,
    Bunny(bunny.friendly@gmail.com,(HYD-+91-9550580658)

  7. Want to join in this network..

  8. Swetha,
    ETL is not a tool its a process to test DWH domain. As it explains as Extract, Transform and Load. Extract data from multiple sources like system at various OS, flat files etc.. apply business logic to such extracted data to transform and than load those transformed data into the system for generating reports and analyzing.

    Whereas, Selenium is a functional automation tool. That requires to have knowledge of JAVA specially javascript and not and easy tool to master as compared to QTP.

  9. Development is better than testing in IT. So,for a long time career prospect ,growth and for stable job,development is far better. Development in Java is one having many opportunities.

  10. Looking for an ETL Testing opportunity anywhere in India..I already have worked on ETL Related Projects and I have around 7 years of Experience.

    Kindly let me know for any openings

    Thanks
    Kranthi
    9676016000

  11. Hi All,
    I’m working for a Top MNC in PUNE and if any one is interested in learning ETL Testing, BI testing, DB Testing through ClassRoom Training or Online. Please feel free to contact on the below numbers.
    I have a good track record on ETL testing training. I will be teaching the real time scenarios and how to play with the tools. Will provide the demo session on weekends.Interested people can call me on +91-8237320101 for online or class room trainings .
    Note : Class Room training will be conducted on daily & weekend basis
    Drop me a mail if you have any queries : SANDEEPREDDY.MANEM@GMAIL.COM
    call me on +91-8237320101
    If any one is interested to ETL testingin PUNE please contact me on the above mentioned number

  12. Hi,
    I am looking for ETL Testing Training in Hyderabad.
    mail id:gangiraghu4u@gmail.com

  13. Hi,
    I am looking for ETL Testing Training in Hyderabad.
    Please let me know the good institutes in hyd…..

  14. Hello Guys,

    I heard most of the people looking in to ETL These days..People dont know where they need to grasp knowlege.It is the platform to those who really want to learn What going on in ETL..

  15. I heard most of the people looking in to ETL These days..People dont know where they need to grasp knowlege.It is the platform to those who really want to learn What going on in ETL..furthur details:dwbi.qa@gmail.com

  16. hay give me the etl projects interview queations with answers

  17. Hello, i want to learn ETL testing. IF any one knows best institute in Bangalore, please let me know.

  18. Contact through mail if want to know best training providers in Chennai for ETL Testing

  19. hai pls give me the etl projects interview questions with answers

  20. can you plz send me the interview questions and answers for etltesting and database(Teradata)

  21. Any suggestions on free data validation tools? Data in my case is being dumped from source db to a flat file from which it is loaded to the interim db. From here data is transformed to a xml file readable by the destination system which loads it back in to the db.

  22. I want more detailed about etl testing i.e how the process with be going on banking domain using cognos tool

  23. i want best institute for etl testing in bangalore

  24. Can anyone give solution to the problem as soon as possible

    2) Analyze the below business scenario and design an ETL process from source systems all the way to STAR schema.
    a. Business users maintain/insert customer master records in source_system1, customer data will them get propogated onto source_system2, source_system3 and source_system4.
    b. Vendor/supplier data is maintained/inserted in source_system2 and this information is then propogated onto source_system1, source_system3 and source_system4.
    c. Promotion details is maintained on source_system2.
    d. Material details is maintained on source_system3 and it is then propogated onto system_system1, source_system2 and source_system4.
    e. Sales information is captured on Source system4.

  25. I am looking for a training institute to learn ETL testing with some ETL tool in Chennai. Could you any one suggest me any good institute here. I have 2 years exp. in testing and also have good knowledge of SQL

  26. Iam looking for a ETL testing freshers job any one plz inform to my mail:chandrareddy.marri@gmail.com

  27. any one can send the ETL testing material and real time etl process we are doind and how to check the data by using queris

  28. Hi,

    I am currently working as a manual tester having 3+ years of experience. I want to learn ETL testing with ETL tool. So I am looking for a training institute to learn ETL testing with ETL tool. Could anyone please suggest me any good training institute in chennai?

    Please reply me to this Mail Id – mailwithjeeva@gmail.com

    Contact Number – 9994179578

  29. Hi All,
    I have an experience of ELT tetsing for past 2.5 yrears. Well skilled in Informatica, Oracle and OBIEE testing. Please contact me incase of any help needed @ your work.
    Email Id: learnetltesting@gmail.com

  30. I need to Learn ETL/DWH Testing and Informatica Tool, kindly suggest me any Best Training Centre in Chennai.. ? am expecting Full Time Class Room Training n assistance till the Placement.

  31. Hi,
    I am interested to learn ETL and make my carrier in the same. Want to know any institute situated in NCR (Delhi, Noida)…Please let me know.

    Mukul Singh
    +91-9717933844

  32. Hi,
    I am interested to learn ETL and make my carrier in the same. Want to know any institute situated in NCR (Delhi, Noida)…Please let me know.

    Amit Sharma
    +91-9997891385 / 9891210017

  33. Its really usefull.I am working as manual tester in Health care domain .I want o know where i can get hands of information in SQL comands .

  34. Hi This is Teja working as a Testing Team Lead in top most mnc company in pune,
    I have 5 + Experience in ETL and BI(Reports) Testing if any one interested to learn course please contact me.
    daily and weekends batches available in pune Technologies: Oracle,Unix,Informatica,Testing Concepts and dwh Concepts
    Course duration: 45 days
    Fee: 6000 (Phone: 8149173089 )
    (Email Id: tejaetltester9@gmail.com)

    daily mock interviews with real time scenarios…only limited students in each batch..

  35. Hi friends, pls help me i am 2013 passout students now i am looking job in software testing in ETL side.pls help me. i don’t know anything in ETL testing, anybody having study material pls send the material to in this mail id prem.reh@gmail.com.

  36. I am from chennai if you know any good course center in near to DLF IT park please let me know to this mail id prem.reh@gmail.com.

  37. Hi All

    I have been working in DWH project since last one year.
    I am working as a QA in the project.
    ETL testing is quite challenging but good job to do.
    we run various jobs and make sure that data is getting loaded to our DWH properly and if not we investigate the cause. in simple words i love being an ETL tester

  38. Can you send information about
    “Role of ETL in Banking domain”
    Or
    “How does ETL works?”
    Urgent
    Mail id:prashanth.rock9@gmail.com

  39. Hi All,

    I have been working in DWH project for 6 years. I have gone through so many interviews and taken also..

    If anybody wants to go through ETL testing , unix and sql interview questions then follow the below link :

    http://techloverforum.blogspot.com/?view=magazine

    Hope you will like the posts.

    Thanks!

  40. Hi
    I am working in a mnc as a etl tester.
    Having a good knowledge of etl testing,SQL writing, report testing and test data creation(most critical task) in data warehouse project.
    For any help you can reach me out at ksandeep.srm@gmail.com

  41. Hii frnds..I m susmita..I am a mechanical engineer. .I m doing job in my field..but now I m interest to switch my field..I want to learn this etl testing course. .plz help me..is my decision is current. Can it be possible.

  42. Could anyone please reply with best answer?

    Given a source table of 500K rows, Informatica ETL in the middle (treat as a black box) and a resulting row count at the target of only 495K, please share how you would identify the rows which were not migrated at all. Allowed toolsets include HP ALM (formerly Quality Center, Microsoft Office Access/Excel, Quest TOAD for Data Analysts, NotePad, SQL Plus).

  43. Hi All

    I want to learn about ETL and Data Migration Testing.

    Can anybody please help me ?

    I am looking for online classes or Regular Batches in Chandigarh.

    My Email ID is PartapGosain@gmail.com

  44. I am looking to find a job in ETL testing. Please anyone send me ETL Testing Interview questions.My email id is aarthy1984@gmail.com

  45. Hi all,

    If any one please send me a ETL testing interview questions.
    My email id : kalanithi.p@gmail.com

    Thanks in advance

Leave a Comment