ETL Testing – Informatica with DWH Concepts
Course
Content
I. Database/SQL
· RDBMS Concepts
o Data Models
o Entity Relationship Model
o Normalization
o DBMS, RDBMS, ORDBMS
· SQL Overview
o Introduction to SQL
o Data types
o SELECT Statement
o Operators
o Pseudo Column
· SQL Statements
o Data Definition Language (DDL)
o Data Manipulation Language (DML)
o Transaction Control Language (TCL)
o Data Control language (DCL)
· Join
o Inner/Equi Join
o Non-Equi/Cross Join
o Self Join
o Outer Join
· Constraints
o Primary Key Constraint
o Foreign Key Constraint
o Unique Key Constraint
o Not Null Constraint
o Check Constraint
· Set Operator
o Union
o Union All
o Intersection
o Minus
· Sub Query
o Sub/Inner Query
o Correlated Sub Query
· Object
o Index
o Synonym
o View
· Built-in Functions
o Aggregate Functions
o Character Function
o String Functions
o Case Manipulation Functions
o Date Functions
o Analytical Functions
II. Datawarehousing/ETL
· Datawarehousing Fundamental
o Datawarehousing Overview
o DWH Characteristics
o Types of Datawarehousing
o Data Mart
o Database vs Data Warehouse
o Data Mart vs Data Warehouse
o Data Warehouse benefits
o DWH Architecture
· Datawarehousing Terminologies
o Data Cleansing, Partitioning
o Staging area, Metadata
o OLTP, OLAP, ROLAP, MOLAP
o Surrogate Key
o Snapshot, View and Materialized View
o Meta Data, Data Mining & Data Cube
· Dimension Modeling
o Facts and Dimensions
o Hierarchies and Levels
o Measures & DWH Schema
o Star, Snow-flake and Galaxy Schema
o Additive, Semi Additive, Non Additive Fact/Measure
· ETL Overview
o Process Flow of ETL
o Types of Sources and Target
o ETL Data Load types
o Active vs Passive transformation
o Extraction Methods in ETL
o Tracing Level
o Types of Data used for ETL Process
· Slowly Changing Dimension
o What is Slowly Changing Dimension?
o SCD Types : Type-1, Type-2, Type-3
· ETL Testing Process
o Categories of ETL Testing
o Types of ETL Testing
o How to create ETL Test Case