ETL Concepts And ETL Testing Tutorial

etl testing guide

ETL Data warehouse Concepts Tutorial:

  1. Why data warehouse?
  2. Data warehouse Architecture
  3. Data Modelling concepts
  4. Star Schema and Snowflake Schema
  5. Surrogate Key
  6. Dimension Table
    1. Slowly Changing Dimension (SCD) Types
    2. Rapidly Changing Dimension (RCD)
    3. Junk Dimension
    4. Degenerated dimension
    5. Confirmed dimension
  7. Fact Table
    1. Fact types – Additive, Semi-Additive and Non-Additive
    2. Fact Table Types
    3. Factless fact table
  8. Operational Data Store (ODS)
  9. Data Retention Policy
  10. Data Cleansing (data scrubbing)
  11. Data masking
  12. Difference between a data warehouse and OLTP
  13. OLAP database vs OLTP database

ETL Concepts & ETL TOOLS :

  1. Extract Transform Load
  2. Data Load Types
  3. Data Transformation types
  4. Informatica Tasks
  5. Data Partitioning
  6. Step by step job development steps in Informatica
  7. Tools List
  8. Tool Selection Criteria

data warehouse testing concepts:

  1. Testing process or  life Cycle
  2. Testing Techniques
  3. Test Cases
  4. Mapping Document
  5. Roles and Responsibilities of a tester
  6. The Characteristics of test cases
  7. Isolation Testing
  8. Exploratory Testing
  9. Performance Testing
  10. Challenges faced
  11. Best Practices
    1. Best Practices to follow
    2. Best Practices to follow during Defect Logging
  12. Lessons Learnt
    1. Never repeat this Mistake in test data
    2. Why does a tester must check data type selection?
  13. Leadership
    1. How to become master
    2. The characteristics of a good test lead

To understand more about all keywords, this would be best place end to end ETL concepts with an example project.

Best way to utilize this tutorial:

Some of you may struggle to start are you one of them? If so, then these words would definitely help you out. I would like to say a few words about how we can learn easily or the best way to grasp the key concepts.

First, start from the concepts of data warehousing in the order and make sure that below are the must take away at the end of this course.

Are you familiar with these terminologies now?

Data warehouse, data mart, data model, staging database, star schema, snowflake schema, dimension, dimension types, fact, fact types, fact table, fact table types, surrogate key, Hierarchy.

If yes, then ask below 3W questions yourself. Example for surrogate key,

                        What is a surrogate key?

                        Why is it required?

                        When is it required?

If you are able to answer then say well to yourself and start the next one.

The second step is, understanding the concepts and testing process.

Here you must learn the below topics.

Extract transform load, load types, transformation types, testing life cycle, responsibilities of a tester, test scenarios, types of defects, best practices to follow, mapping document, challenges.

Now all set to start the next one SQL, practice more for below features,

Finding duplicate records, finding nth highest value, Inner join, left join, right join, full join, self-join, union, union all, minus, intersect.

Time to start the glance of interview questions to make sure that good for interview,

Oh! I thought everything is over but still I can see few words what are those for?

It is not over, till now we have learnt everything for performing manual testing. Now, we need to start the hunting of value added stuff which reduces effort and makes faster to market with best-in-class quality.

What is it? How to reduce the manual effort? or how to ensure the data quality?

Don’t stop with this functional ETL testing alone, I am sure that it won’t be enough to survive for a long run in this competitive digital world.

Yes, in this dynamic world we need to showcase our different skills. One of the skills in ETL testing is creating automation utility/framework or using existing tools such as “Query surge”, “Data gaps” and “DVO”.

Create yourself passion about learning new tools and creating some small framework which helps your regular manual activities.

For doing this any scripting knowledge is required. Same time it is not mandatory to know everything about scripting languages. If you ask me about what language to choose, I would say that Java since I am good in Java alone.

Learn Java basics and start this same level of interest in automation before distract.

You can follow about ETL testing automation stuff at,

How to achieve automation for data warehouse testing?