Monday 25 February 2013

Basic ETL Concepts

Recently, I assigned to Data warehousing project to test the ETL (Extract,Transform,Load).

Lot of testers interested to know about ETL testing. So that i would like to share the article.

Here we go..

I hope this will give you some ideas to improve your ETL Knowledge and what we do to test ETL process.

ETL testing (Extract, Transform, and Load) means Extract the data from different sources table and transform by using some business logic. Finally load the data into target table.

Now we can see the different type of category:

Data warehouse testing is categorized into four different types:
  • New Data Warehouse Testing – New DW is built and verified from scratch. Getting input from customer and new data warehouse is build and verified with the help of ETL tools.
  • Migration Testing – Project customer will have an existing DW and the ETL is performing the job. However they are looking for new ETL tool in order to improve efficiency.
  • Change Request – In this type of project new data is added from different sources to an existing DW. However customer needs to change their existing business rule or they might integrate the new rule.
  • Report Testing – Report is main key factor for any Data Warehouse project. Report must be tested by validating layout, data in the report and calculation.
Lot of testers, misunderstanding that the Database testing and Data Warehouse is similar while the fact is that both hold different direction.

Now we can see how Database testing differs from Data Ware house testing
  • Database testing is done using smaller scale of data whereas Data ware house testing is done large volume of data.
  • Database testing is normally using with OLTP (Online transaction processing) type of databases whereas data warehouse testing normally with OLAP (online analytical processing) databases.
  • Database testing is consistently injected from uniform/Single sources whereas Data ware house testing inconsistently injected the data from different/Multiple sources.
  • Database testing performs only CRUD (Create, read, update and delete) operation whereas Data Ware house testing performs only SELECT operation (Read Only).
  • Database testing used Normalized databases whereas Data ware house testing used the DE Normalized databases.
  • Database uses only .sql files (Based on the DB platform) whereas Data Ware house testing can use any type of file (.txt, .xlsx) to import data. 
List of ETL Testing Techniques that are treated when we testing the ETL package:
  • Make sure that Data transformed correctly to Target table.
  • Make sure data is loaded into the DW table without any data loss and truncation.
  • Make sure that Rejects the invalid data if any and generates the reports/Logs for invalid data for reference.
  • Make sure that data is loaded within expected time frames. (Performance)
List of points to be considered as essential when we testing the ETL package:
  • Data transformation from source to destination works 
  • Expected data is added in target system
  • No of Records count should match.
  • Error Log should create for rejected data
  • NULL Value fields 
  • Duplicate data is not loaded
  • Focus is to ensure Data validation and integrity mainly.
  • Verify that data getting loaded without truncation.
ETL Testing Process:
All testing types like unit testing, integration testing, System testing and UAT testing lies under verification and validation process as same like ETL testing also lies under verification and validation process.
Now we can see the process of Data Warehouse /ETL process.
  •  Business and requirement understanding.
  • Validating the requirement.
  • Estimate for testing.
  • Preparing test plan based on test estimation and Requirement.
  • Preparing Use cases (Scenarios) and test case as per the inputs.
  • Once all the test cases are freezed and approved, testing team move forward to execute the test cases.
  • Execution is performed once the exit criteria are met.
  • Finally, prepared the completed summary report and Closure process is done.
ETL Testing Challenges which faced in my project: 

ETL testing is quite different from other testing. There are many challenges we faced while performing data warehouse testing.
Here I have listed few ETL challenges which most of the testers faced on the project:
  • Out of Memory exception occurs while executing ETL.
  • Taking more memory while writing huge volume of Data into the table.
  • Package goes to No response state when no memory to execute the package.e
  • Incompatible and duplicate data.
  • Loss of data during ETL process
  • Unfreeze data format
  • Don’t have access to run the ETL jobs.
  • Volume of data is huge. 





23 comments:

  1. it's a Nice article, very helpful for us and thank's for sharing. we are providing ETL Testing online training

    ReplyDelete
    Replies
    1. IntelliMindz is the best IT Training in Bangalore with placement, offering 200 and more software courses with 100% Placement Assistance.

      ETL Testing Online Training
      ETL Testing Course In Bangalore
      ETL Testing Course In Chennai
      ETL Testing Course In Coimbatore

      Delete
  2. Good post!! Thanks for putting the efforts on gathering useful content and sharing here. You can find more ETL/DW testing related question and answers in the below forum.

    ETL/DW testing question and answers

    ReplyDelete
  3. This is nice article for who is interested in learning Etl Testing.Thanks for sharing Etl testing online training information with us.

    ReplyDelete
  4. This is the information that I was looking for.. Thanks for the efforts put to gather such a nice content and posted here. Wonderful job!! let me tell you one thing that is it is very useful for who is looking for ETL Testing Online Training

    ReplyDelete
  5. This information which you provided is very much useful for us.It was very interesting and useful for ETL testing online training learners.We also providing ETL testing online training .

    ReplyDelete
  6. Thanks for your support,I am very interested in learning ETL testing Right now i am learning ETL testing in 123 trainings.They will provide the Best ETL testing Online Training at hyderabad.

    ReplyDelete
  7. hi you have gathered a valuable information on ETL testing...., I am looking for content like this and i am much impressed with the information and nice course content, thanks a lot for the Information regarding ETL testing Online Training.

    ReplyDelete
  8. Thanks for gathering information regarding ETL testing,I have gathered 123trainings the best ETL testing ONLINE TRAINING provider from hyderabad.

    ReplyDelete
  9. Thanks for providing your information,i am looking for the web page like this and i am much impressed with the information and nice course content, i came to know about this site from a friend, thanks a lot for providing ETL Testing Online Training..

    ReplyDelete
  10. Thanks for sharing valuable information and it is useful for 123 trainings provides the best ETL Testing Online Training classes.To see free demo classes ETL Testing Online Training in Uk,CANADA.

    ReplyDelete
  11. Thanks for sharing valuable information and it is useful for BigInfosys provides the best ETL Testing Online Training classes.

    ReplyDelete
  12. It was nice article it was very useful for me as well as useful for ETL TESTING learners .thanks for providing this valuable information.

    ReplyDelete
  13. When you yourself have multiple programs working in the same moment manual testing isn't a sensible option. The recognition of flaws becomes quite difficult such circumstances. etl-testing online training Whenever a new bug is fixed, another occurs. Robot is very useful in such circumstances and as such might have an optimistic effect on time-management and business revenue. It merely makes testing better and successful.

    ReplyDelete
  14. ETL testing and data stores is a superb method to handle the huge data groups existing within an organization. This article will allow you to discover more information regarding both these phrases.
    You most likely found out about datawarehouses & research instruments previously in database relevant discussions or in career meetings. The truth is that everyday more and more corporations are beginning to think (and develop) data warehouses or other data analysis and mathematical methods smartmind online training provides best online training for Etl testing.http://smartmindonlinetraining.com/etl-testing-online-training/

    ReplyDelete
  15. Thanks for sharing the information. we are providing best
    ETL TESTING Online Training

    ReplyDelete
  16. Thanks for Sharing such a useful information....

    Learn SAP IS RETAIL through Online for Details Please go through the Link...

    Online ETL TESTING Training in INDIA | USA | UK | AUSTRALIA | CANADA | SINGAPORE | JAPAN

    ReplyDelete
  17. Thanks for the notes that you have published here. Though it looks familiar, it's a very good approach you have implemented here. Thanks for posting content in your blog. I feel awesome to be here.

    Best Institute for Cloud Computing in Chennai
    Cloud computing training in chennai
    hadoop training in chennai

    ReplyDelete
  18. Thank you for sharing this useful information.
    It is very useful to me and who are searching for ETL Testing online training .

    ReplyDelete
  19. Thanks for giving Information. We are providing Online Training Classes. Who wants search ETL Testing Online Training

    ReplyDelete
  20. Excellent information regarding datawarehouse testing.This blog is really useful for etl tester learners.Thanks for sharing great post.

    ReplyDelete