ETL Testing Course syllabus

Module 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 modelling
• Star Schema and Snowflake Schema
• What is fact table
• What is Dimension table
• Normalization and De-Normalization

Module 2 : ETL Testing

• ETL architecture.
• What is ETL and importance of ETL testing
• How DWH ETL Testing is different from the Application Testing
• SDLC/STLC in the ETL Projects (ex: V Model, Water fall model)

Module 3 : Challenges in DWH ETL Testing compare to other testing

• Incompatible and duplicate data.
• Loss of data during ETL process.
• 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.

Module 4 : ETL Testing Work flow 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 documentations (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 developer team for resolving the defects

Module 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

Module 6 : Different ETL tools available in the market

• Informatica
• Ab Initio
• IBM Data stage

Module 7 : Power Center Components

• Designer
• Repository Manager
• Workflow Manager
• Workflow Monitor
• Power Center Admin Console

Module 8 : Informatica Concepts and Overview

• Informatica Architecture.

Module 9 : Sources

• Working with relational Sources
• Working with Flat Files

Module 10 : Targets

• Working with Relational Targets
• Working with Flat file Targets

Module 11 : Transformations – Active and Passive Transformations

• Expression
• Lookup –Different types of lookup Caches
• Sequence Generator
• Filter
• Joiner
• Sorter
• Rank
• Router
• Aggregator
• Source Qualifer
• Update Strategy
• Normalizer
• Union
• Stored Procedure
• Slowly Changing Dimension
• SCD Type1
• SCD Type2 — Date, Flag and Version
• SCD Type3

Module 12 : Workflow Manger

• Creating Reusable tasks
• Workflows, Worklets & Sessions
• Tasks
• Indirect Loading
• Constraint based load ordering
• Target Load plan
• Worklet ,Mapplet ,Resuable transformation
• Migration ?ML migration and Folder Copy.
• Scheduling Workflow
• Parameter and variables
• XML Source, Target and Transformations

Module 13 : Performance Tuning

• Pipeline Partition
• Dynamic Partition
• Pushdown optimization
• Preparation of Test Cases
• Executing Test case
• Preparing Sample data
• Data validation in Source and target
• Load and performance testing
• Unit testing Procedures.
• Error handling procedures.