Module 1 – Introduction to Data
Warehousing

Database management and tools

How to implement data warehousing?

Why data warehousing is important?

OLTP and OLAP database

Introduction to OLTP, ETL and OLAP
Systems

Data Warehouse Architecture

ODS [Operational Data Store]

Star Schema and Snowflake Schema

What is fact table

What is Dimension table

Normalization and De-Normalization

Module 2 – Overview of ETL

Data Acquisition

Data extraction

Data transformation

Data Loading

Data mart

Data Modeling

Module 3 – Database management
System

Bid Data (Hadoop)

Oracle

SQL in RDBMS

DDL, DML and working with SQL
Queries

Hadoop Distributed file system

Hbase

Apache Pig

Sqoopsss

Microsoft Excel and VBA

Module 4 – ETL Testing

ETL architecture.

What is ETL and importance of ETL
testing

Incompatible and duplicate data.

Loss of data during ETL process.

Fault in business process and
procedures.

ETL Testing Work flow activities
involved

Execute system testing and
integration testing.

Best practices to Create quality
documentations (Test plans, Test Scripts and Test closure summaries)

How to detect the bugs in the ETL
testing

How to co-ordinate with developer
team for resolving the defects

Module 5 – Types of ETL Testing

Production validation testing

Metadata testing

Data completeness testing

Data accuracy testing

Data transformation testing

Data quality testing

GUI/Navigation Testing

Module 6 – Data completeness

Data transformation and Data
quality.

Performance and scalability.

Integration testing.

User-acceptance testing.

SQL Queries for ETL Testing

Incremental load testing

Initial Load / Full load testing

Different ETL tools available in
the market

Duplicate check

Date validation

ETL Bugs

Difference between ETL Testing and
Database testing

Module 7 – Responsibilities of
an ETL Tester

Business transformation logic

ETL software

Test components of ETL warehousing

Create, design and execute test
cases

Identify problems and provide
solutions

Write SQL queries for different
scenarios

 

Section 1: DWH: Data
Ware Housing Concepts

 What is Data
Warehouse?

 Need of Data
Warehouse

 Introduction to OLTP,
ETL, and OLAP Systems

 Difference between
OLTP and OLAP

 Data Warehouse
Architecture

 Data Marts

 ODS [Operational Data
Store]

 Dimensional Modelling

 Difference between
relation and dimensional modeling

 Star Schema and
Snowflake Schema

 What is a fact table

 What is the Dimension
table

 Normalization and De-Normalization

Section 2: ETL
Testing

 ETL architecture.

 What is ETL and the
importance of ETL testing

 How DWH ETL Testing
is different from the Application Testing

 SDLC/STLC in the ETL
Projects (ex: V Model, Waterfall model)

Section 3: Challenges
in DWH ETL Testing compare to another testing

 Incompatible and
duplicate data.

 Loss of data during
the ETL process.

 Testers have no
privileges to execute ETL jobs on their own.

 The volume and
complexity of data are huge.

 Fault in business
process and procedures.

 Trouble acquiring and
building test data.

Section 4: ETL
Testing Workflow activities involved

 Analyze and interpret
business requirements/ workflows to Create

 estimations

 Approve requirements
and prepare the test plan for the system testing

 Prepare the test
cases with the help of design documents provided by the

 developer team

 Execute system
testing and integration testing.

 Best practices to
Create quality documentation (Test plans, Test Scripts, and Test closure
summaries)

 How to detect the
bugs in the ETL testing

 How to report the
bugs in the ETL testing

 How to co-ordinate
with the developer team for resolving the defects

Section 5: Types of
ETL Testing

 Data completeness.

 Data transformation.

 Data quality.

 Performance and
scalability.

 Integration testing.

 User-acceptance
testing.

 SQL Queries for ETL
Testing

 Incremental load
testing

 Initial Load / Full
load testing

Section 6: Different
ETL tools available in the market

 Informatica

 Ab Initio

 IBM Data stage

Section 7: Power Center
Components

 Designer

 Repository Manager

 Workflow Manager

 Workflow Monitor

 Power Center Admin
Console

Section 8:
Informatica Concepts and Overview

 Informatica
Architecture.

Section 9: Sources

 Working with
relational Sources

 Working with Flat
Files

Section 10: Targets

 Working with
Relational Targets

 Working with Flat
file Targets

Section 11:
Transformations Active and Passive Transformations

 Expression

 Lookup Different
types of lookup Caches

 Sequence Generator

 Filter

 Joi

 Stored Procedure

Section 12: Slowly
Changing Dimension

 SCD Type1

 SCD Type2

 Date, Flag and
Version

 SCD Type3

Section 13: Workflow
Manger

 Creating Reusable
tasks

 Workflows, Worklets
& Sessions

 Tasks

 Session

 Decision task

 Control Task

 Event wait task

Section 14: Timer
task

 Monitoring workflows
and debugging errors

 Indirect Loading

 Constraint-based load
ordering

 Target Load plan

 Work

 Migration ML
migration and Folder Copy.

 Scheduling Workflow

 Parameter and
variables

 XML Source, Target,
and Transformations

Section 15:
Performance Tuning

 Pipeline Partition

 Dynamic Partition

 Pushdown optimization

 Preparation of Test
Cases

 Executing Test case

 Preparing Sample data

 Data validation in
Source and target

Section 16: Real-time
project

 ETL Testing project
environment setup

 Real-time ETL Testing
project

 Project demonstration

 Expert evaluation and
feedback

Section 17: You made
it!!

 Spark Databox ETL
Testing certification

 Interview preparation

 Mock interviews

 Resume preparation

 Knowledge sharing
with industry experts

 Counselling to guide
you to a right path in ETL Testing career

ETL Testing VS Manual Testing

Datawarehousing and ETL in Testing perspective

SQL in ETL Testing perspective

Unix in ETL Testing perspective

Documentation

ETL testing specifications

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