ETL Testing

MODULE1- INTRODUCTION TO ETL TESTING AND INDUSTRY STANDARDS

What is Data Warehousing?

Differences between OLTP and Data Warehousing

Why do we need two separate solutions for real-time and warehousing

DATA EXTRACTION

How to extract data from several sources and load it into a same target table

DATA LOADING How to load data into final target table

Data Marts

Need and usage of data marts and types of data marts

Dependent DM

Independent DM

DATA WAREHOUSING LIFE CYCLE

Types of Tables:

Fact Table

Dimension table

Schemas:

Fact Table

Star Schema

Snowflake Schema

Fact Constellation Schema

MODULE- 2 ETL TOOL IMPLEMENTATION AND TESTING CONCEPTS

Basic Concepts In SQL (Select, Update, Insert and Delete)

How to design frequently used testing queries to validate the data in source and target for both of the reasons Data Quality and Correctness to ensure that expected data came into the final and mediator target tables or not.

Overview Of ETL Tool Architecture

How ETL tool works and what is the basic need of ETL tools in the market

Why do we use ETL Tools if SQL is the only standard which any Database or Data Warehouse can use

How ETL tools work at the back end

Different ETL Tools in the market

What types of tasks tester performs on ETL for testing purpose

Informatica PowerCenter (Leading tool in the market)

MODULE-3 CODING AND TESTING PROCESS USING DIFFERENT TRANSFORMATION RULES

ul>

SAMPLE Loading from source to target

SORTER Transformation

FILTER Transformation

EXPRESSION Transformation

JOINER Transformation

UNION Transformation

AGGREGATOR Transformation

SEQUENCE GENERATOR Transformation

SQL Transformation

LOOKUP Transformation and Its types

Source Qualifier Transformation

Source Qualifier Transformation

UPDATE STRATEGY Transformation

ROUTER Transformation

MODULE – 4 TESTING TECHNIQUES IN ETL

SQL Query

MINUS Query

COUNT Query

JOIN Query etc as per the logic applied.

MODULE-5 SCDS (SLOWLY CHANGING DIMENSIONS)

Different types of SCD’s which developer develops and a tester has to test as per different

ul>

types of data needs of clients

We implement SCD-1 and SCD-2 in class to show the implementation part more transparently and explain SCD-3 to define how to work on limited historical data as well which is used sometimes.

Types: SCD-1 and SCD-2 Implementation and Testing

MODULE-6 COMMON ETL TESTING SCENARIOS

What are the different and important points that need to be checked thoroughly while doing ETL Testing

More than 40 points will be explained and challenges for ETL Testing new Project/Migration Project will also be discussed.

MODULE-7 UNIX COMMANDS USED IN INFORMATICA (BASIC COMMANDS AND BASIC SCRIPTING KNOWLEDGE)

Unix Commands Covered

cp,mkdir,rm,rmdir,grep,sed,cut,awk,wc,chmod,vi,sort,uniq,mv,touch,cat etc.

MODULE-8 INFORMATICA POWERCENTER

How developers work on PowerCenter

Different components of PowerCenter

Tasks developed in different components