This is a guest post by Vishal Chhaperia. If you want to publish your article please read our article guidelines.
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 article will present you with a complete idea about ETL testing and what we do to test ETL process.
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. Customers have been offered 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.
Why do organizations need Data Warehouse?
Organizations with organized IT practices are looking forward to create a 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 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 in 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 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 build 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 are the end result of any Data Warehouse and the basic propose for which DW is build. Report must be tested by validating layout, data in the report and calculation.
ETL Testing Techniques:
1) Verify that data is transformed correctly according to various business requirements and rules.
2) Make sure that all projected data is loaded into the data warehouse without any data loss and truncation.
3) Make sure that ETL application appropriately rejects, replaces with default values and reports invalid data.
4) Make sure that data is loaded in data warehouse within prescribed and expected time frames to confirm improved performance and scalability.
Apart from these 4 main ETL testing methods other testing methods like integration testing and user acceptance testing is also carried out to make sure everything is smooth and reliable.
ETL Testing Process:
Similar to any other testing that lies under Independent Verification and Validation, ETL also go through the same phase.
- Business and requirement understanding
- Validating
- Test Estimation
- Test planning based on the inputs from test estimation and business requirement
- Designing test cases and test scenarios from all the available inputs
- 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
- Upon successful completion summary report is prepared and closure process is done.
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 testing might require writing SQL statements extensively by testing team or may be tailoring the SQL provided by development team. In any case 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 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 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 are 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 ETL testing:
- Verify that data transformation from source to destination works as expected
- Verify that expected data is added in 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
ETL Testing Challenges:
ETL testing is quite different from conventional testing. There are many challenges we faced while performing data warehouse testing. Here is the list of few ETL testing challenges I experienced on my project:
- Incompatible and duplicate data.
- Loss of data during ETL process.
- Unavailability of inclusive test bed.
- Testers have no privileges to execute ETL jobs by their own.
- Volume and complexity of data is very huge.
- Fault in business process and procedures.
- Trouble acquiring and building test data.
- 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 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.
This is a guest post by Vishal Chhaperia who is working in a MNC on 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.
98 comments ↓
further simplified —
In simple words it is collection of data from different departments of a company or from different technologies to one warehouse.
Extract from source, transform in a suitable format and then load into destination data warehouse.
thanks for sharing this resource..
Informative post.
How to get into this profile? As no matter what exp or knowledge one has it requires exp of ETL testing.
Hai frnds, I am new to testing.Did any one know best coaching center for learning ETL testing in chennai.PLZ reply me asap.
Very informative post on ETL testing. Specially the section which talks about the difference in DB Testing and DW Testing. Thanks.
Hi Bishal its very useful info shared by you, I was looking for some docs related to ETL testing got them here. I just checked by Anindita di in Lotus notes the link that u shared and goin thru same, would like to learn more about ETL testing.
Thanks
Sanjay
Its a very good information shared by you .
Thanks for sharing this .
Super article.
very helpful
Please let me know any coaching center in bangalore.
I have around 6 months of exp in ETL testing .I want to learn more in this area.
I think to Start with ETL Testing One Need a Very Hands On SQL concepts specially Conversion Funcions as like when you want to change date in different format or Numeric to string etc
@Kishore: There is no prerequisite as such, may not be one need to have knowledge of any ETL tool still fair understanding of SQL concepts are much desirable.
@Aarthi & Bhavya: I believe there is no institute which provides training on ETL testing, still you can check with institutes those are proving training in ETL tools.
@Chaitu: Yes absolutely, one need to be very conversant in SQL as to map the SQL vs actual business transformation rule that applies. Clear Understanding of Conversion Functions are also much required.
Hi
Thanks for sharing such a useful information..
i am very confused about the difference between the database testing and data warehouse testing..now it is clear..Thanks..
Coming to challenges the major thing is to create a test data for the scenarios which we found in planning phase …….
Thanks for sharing the more info of DWH TESTING
and could please share the oracle queries in general asked ,
anyway thanks for sharing the info..
Thanks,
Purna,
hello friends,
can someone explain any example , Backend Testing using SQL queries??
Thanks
JD
Hi,
S this is a good study on a warehouse testing. I feel it parallel o my approach on testing a warehouse
HI,
I have a good understood with this information.
HI,
This is lakshmi, Please tell me about execution of ETL Testing.
Hello Frns,
There is an institute in HYD for ETL testing:
Naresh Technologies
this is really informative and appreciate Vijay’s crisp explanation about ETL testing and really liked the way he gave the difference between Database testing & Data Warehouse testing.
sorry to provide wrong name of author as Vijay whereas it should be VISHAL in my earlier post
The problems generally found in ETL testing comprising the graphics transformations is the unavailability of tools or language(symbol) gap. The article however gives a detail of ETL testing.
@ Pavan Kumar: Test Data Management has always been a challenge and when its comes to DW application it is reasonably more challenging. Its very tough to get correct, clean and secure data considering the fact that data is coming from multiple sources.
On contrary there are many good Test Data Management tools which are potentially capable to overcome these problems.
@Purna: Talking about SQL that are asked in any QA interview are mostly from Joins, Referential Integrity concepts, some basic SQL like sorting, grouping and query related to where clause.
@JD: Could you please make your question more precise? Backend testing is done using SQL testing and we test many things such as SQL injection, Data Correctness etc
Data warehouse — information delivery system of an organization.
Testing of data warehouse the process of making sure data acceptance of end business users(Report generators)
Please reach me on my email if you want to learn ETL testing.
my email is Jagadish_happy18@yahoo.com
Hi , I am working in ETL project since 4 years and having overall 9 years experience. Currently working in one of Top MNC in India and presently working from client place(London) since 2.5 years in banking project. If you have any questions on ETL or any banking domain then you can reach me at sreenivasulu.borra@gmail.com
Best Regards
Sreenivas Reddy
Hi,
Can You Please explain landing area, staging area and workflows
waste
Hi All,
I provide online trainings. I cover Database Testing and ETL Testing.
To get good amount of knowledge in both Database Testing and ETL Testing, one should have sufficient knowledge in SQL Queries.
Please do contact me for more information.
Thanks,
Durga.
9848805111
Hi.. very informative and clear.
I am working on ETL testing and i do provide training on ETL testing.. please reach me on jagadish_happy18@yahoo.com if anyone interested.Thanks.
I am currently in manual testing.I want to learn informatica and move to datawarehouse testing. how feasible is it. Will I have any opportunity to move.
As you are currently working in Manual testing, its very easy for u to learn ETL testing and it would be easy for u to work on ETL testing projects. but going to informatica development may not be a good idea. this is just my opinion.
Hi. I have 12 months of experince in manual testing.I know the basics of JAVA and SQL.
Now i got opprtunity in 1.ETL Testing and 2.Automation testing ( SELENIUM ).
Please advice which one to choose for better career growth.
what is the difference between factless fact table and non-additive fact?
Very good information and detailed diff between DB and DW testing. Appriciate your efforts in doing this one and Thanks a lot for sharing such a nice post.
Can anyone please tell me which is best institute for ETL testing. I’m new to this tool.
Very informative article
i wanna to know the list of data warehouse products (like db2, ibm data stage2…) if u know the latest products for this means pls rply….
i have been offered a part time trainer position in a company for DWH testing.. what is the pay they generally offer per session?
Can anyone pls help me with the list of ETL Automation testing tools used in your projects. open source or commercial both will be fine. Thanks.
Hi,
I am looking for a part time software testing job in bangaore or full time testing in chennai with 2 years of experence.
Do let me know ,if you have the same
Email : sangit.kumar93@gmail.com
Mobile no: +91-9480321532.
I have 3 year of Experience in Java and Oracle and i want to work in ETL tools. Can anyone help me to know its future prospects and salary.and where i get the good knowledge of ETL tools.
+91-9250110356
Very nice article, and very informative too..Thanks
Plenty of ETL testing jobs are available in almost all Fortune 100 companies.
Hi,
I am working in ETL Testing. Please do not always go for tools like Informatica, Web Intelligence 11 etc. First and foremost thing that comes into action is “Writing the queries”. In case to know more please do contact :
meet16ravi@gmail.com
ITs very good one thanks fopr ur post…
Thanks for information
keep on sharing
Thank you for sharing your knowledge. You have helped me understanding ETL testing.
A very good article. Also check out on Youtube the video “What is a data warehouse and how do I test it?”
This article is nice one . I just want to know is it in scope of testing if different short of data loading method testing .
as my project experience delta data loading and bulk data loading these two have generate two separate scenarios and corresponding issues mainly data loss and duplicate data and null value .
Does ETL testing has any proper path to solve this case ?
Thank you for sharing your knowledge. You have helped me understanding ETL testing.
I have written a book on the subject of data warehouse testing… available at Amazon and others as well as an ebook. Let me me know of any questions.
I’m Working in a software company in Chandigarh as a DotNet Developer. Someone suggested me to change the profession and go for ETL or WareHousing. As i don’t know anything about ETL and WH also.
And i found lots of people here who having knowledge about both terms.So anyone can please guide me.
aman_wadhwa88@yahoo.com
Thank you Vishal, really its very useful information.
Hi All,
At present I am as a manual test engineer in one of the MNC, Bangalore. I want to change my domain from manual to ETL testing, so can anyone suggest me that what are the basic skills required to switch from manual to ETL.
Also are there any centres or institutes that provide training on ETL. Incase there are no such centres how to prepare myself.
Please suggest me.
Thanks in advance.
+91 9008078349
My Mail ID:
rsdesaiglb@gmail.com
Anyone please suggest on my above post.
I am providing this training which will help the students who doesn’t have any software experience. We will take scenarios and execute them and guide them when they get into job.
I have experience of 12+ years in the industry. I worked as a Test manager, Business Analyst, project manager.
I Worked with the companies like ITC Infotech, Honeywell, Accenture, TCS.
I provide corporate training also.
After this training the student will scale up to 3+ years experience level.
Contact: 9886991978
Syllabus:
DWH/Datawarehouse Concepts.
Manual Testing.(Web Applications Architecture)
Why ETL Testing.(Datawarehouse Architecture)
Difference between manual/automation and ETL Testing/BI testing.
SQL required for ETL Testing – Easy and only required SQL s for interviews.
ETL Transformations.
ETL test case documentation
TOAD.
Quality Center.
ETL Test Life Cycle.
Realtime Scenarios of Interaction between Business Analyst, Developers and Testers.
Test Planning(Useful for people trying for lead Level)
Discussion of Interview Questions.
Resume Preparation.
How to Accept and handle the work after joining the company.
Hello Friends ,
IBM openings for ETL Testing & QTP
If anyone willing to join kindly send me ur resume on
preetideshmukh08@gmail.com
Desired Skills & Experience
Total & Relevant 3 to 4 Years of Experience for ETL Testing & QTP ( NO Contractual Employee )
16 Years Education Mandatory ( BE, B.Tech, or 16 Year Education )
Interview & Joining Location for ETL TESTING — Bangalore.
Interview & Joining Location for QTP — Chennai.
Interview Date : 8 Dec 2012
Hi if any want to learn ETL testing in pune please contact on my mobile +91-8888095648,+91-9036238979
Hi if any one want to learn ETL testing in pune please contact on my mobile +91-8888095648,+91-9036238979
Hi,
If anybody interested in learning Dataware house Testing with Real time data please contact me @ 9886137833 – Bangalore
Hi all please the above all comments seems like blaming unemployed people. i am not understanding why people coming to etl testing side. you should go development side
Hi,
I had seen that many persons wanted to learn ETL Testing. I am not aware about the institute but i can teach each and everything about ETL testing or you can mail me your questions if you stuck somewhere, i will reply to all the answers. This will help me also to gain knowledge. I am having 6 years exp in this technologies and working for No. 1 product based (database) organization.
Thanks
Siraj
@Siraj:- Thanks for giving some assurance for me to master ETL Testingconcepts.If you don’t mind can I know your email id inorder to contact you?My email id is ramgopalcse2009@gmail.com
Thanks
Ramgopal
this is really useful material..to learn ETL testing
Very helpful and accurate information. Thanks for sharing.
Hi if any body wanted to learn Practical ETL testing online please contact on my mobile +60-107767499 (Malaysia) .
I have experience of 8+ years in the industry and worked in various domains as ETL test lead.
Regards
Krishna
Hi i would like to say my self maniteja working as a Etl and Bi testing in wipro pune. if any body want to learn Etl and Bi testing contact me. classes available only weekends(pune) and Online.
Could you plz support me to find ETL testing tool to :-
- Verify that expected data is added in 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
I work for a top MNC in pune as an ETL and BI tester. If anyone want to learn ETL testing through online or daily Batches will be conducted
Please feel free to contact me :
+91-8888095648, +91-9036238979
Quite informative
Hi,only by knowing etl testing doesn’t make sense because you need to know atleast one etl tool to understand and to work on etl testing.So make sure to learn etl tool as well.If any body wants to learn etl testing with etl tool through ONLINE we are giving ETL testing training and we give support untill you got the job.
Base on batch schdules we are giving etl testing and etl tool traing on daily basis as well as weekend batches.
We have successfull track record.call @ +91-8237320101
Hi, can anyone pls tell me which is the best to chose as a career in on of these, ETL , Performance, Data Base or Mobile testing. I feel performance testing is the best because it has lot onsite opportunity, well paid and less resources in market. I would like to here from some experienced teters. Pls help me to choose the right career for my life, ur comments n replies are very impt to me, pls guide me properly…. thanks…..
@ etl testing trainer : I am interested to learn. Can you let me know in which location r u based in?
I am taking training on ETL tool..DW Testing..I ll post the doughts and clarifications ..
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)
eKnowledge Infotech, a Leading providers of ETL Testing Training in Pune, Qlikview Training Institute in Bangalore. People can have benefit of this.
Thanks,
Nimisha
Good to read as a beginer.
Good Artcile for testers who want to advance their levels in ETL.
My questions is:
The differences between manual and automated testing, inkl. some examples. Is there practical example of test strategies?.
Thanks
If you are using source and target SQL to do your manual testing then you are more then likely using the minus function to aggregate the results. If this is the case then there is a new tool on the market to automate the testing process for you. Google ‘QuerySurge’
Thanks
ok this is a great info thanks or sharing but i need a guide on how to get a job by learning this and the step by step procedure thank u
Hi all,am in production support from 3 years and I am desparate for a move..Know the concepts of manual testing and sql queries…will I be able to get into ETL testing,can anyone guide me.
Hi
Anyone know, who can teach ETL class in Vadodara(Gujarat).
Hi,
which is the best institute for trainig etl testing.
Friends,
Let me know any ETL/DW testing opening in Singapore/Malaysia. Please reach me @ +91-9840219089 (india-mobile).
Good article.
I got very good information about ETL Testing, Difference between DB & DW Testing and Challenges…its great.
Any good training institutes for learning etl testing in Bangalore.
If any body is looking for group preparation or search then contact me.
Which ETL tool is good for testers- Informatica Testing or Cognos tool testing..
Pls suggest
Hi all,
I am working as a manual tester in a MNC.
Which one is better among QTP and ETL testing.
Career wise and job security wis.
Thanks in advance
Hi,
Can any one advise me ETL is best or Selenium is best.
Which tool have more openings in Market. Plz advise me ASAP…….
Reply for #91 Chandu
I suggest you to learn QTP as automation
Know SQL to work in ETL Tool
Thanks a lot for the information
I came to know a lot.
I have been working in ETL Tool without knowing the differences between Database testing and DataWarehouse testing
Thanks a lot for the info that you had given very useful info for the ETL testers.
Any good training institutes to learn ETL Testing in hyderabad.pls help.
Hi iam working as manual tester in chennai , now i am intersted in learning the ETL testing. please help me how to go ahead with ETL testing. Now i am in chennai , trainer in chennai please contact to my mobile number.
Ph:9962850556
Hi, This is a very nice and Informative post. Currently I am working as a manual tester and testing a BI application. I am pretty much interested to learn DWH testing. Please lemme know if anyone is willing to teach this in Pune (Near Wakad).
My mail ID is – chourasiasourabh@gmail.com
Leave a Comment