ETL Testing is done to ensure that the data has been loaded from a source or from multiple sources to the destination after business transformation is accurate and successful. It also involves the verification of data followed by Data Completeness Test, Data Transformation Test and Data Quality Test at various stages that used between source and destination.

                            Data Flow

• Data Warehousing Concepts with complete E2E architecture. (5hrs approx.)

• ETL Testing on various types of Sources, Targets, Mappings, different Transformation, Workflows, Task with complete ETL testing life cycle.

• ETL Testing Life cycle, All 8 types of strategy with multiple examples
• Challenges in DWH ETL Testing compare to other testing

• Difference between DB and DWH. Different schemas of DWH, Functional Testing based on various transformations.
• Testing on each phase of Informatica Workflow Manager –Special ETL testing sessions (Based on real-time Scenarios and Workflows, Recovery, Error Handling and more on practicals).
• Running jobs by Informatica and doing RCA for failed jobs w.r.t DB.
• SCD1, SCD2, SCD3– Testing on each phase with the correct project example.
• Basic and complex SQL commands required for data validation based on actual mapping.
•  Manual Testing concepts – Smoke, Sanity, Regression, retest etc .with the help of Agile model.
•  Test case and defect management toll like HP ALM, JIRA with the steps etc.
•  Writing test cases and test scripts based on Mapping doc, BRD and TSD.
•  Real time projects, scenarios discussion and E2E architecture Analysis on each type of transformation.
•  Unix commands used for File handling and other functioning in ETL Testing  (3 hrs)
• Study material will be provided for DWH basics, complete ETL transformations, Informatica Power center (with practical steps), Queries with scenarios and unix commands used.
• Real time Test Strategy, Test cases and mapping sheet and BRD will be shown in Live project. Step by step description will be provided to students.
• A set of mock interview question will be provided and discussed, which were prepared from our older students interviews, which they attended in previous interviews.

• Assistance to get interviews and Resume preparation, posting on Job sites with correct Job agents, Will refer for outside jobs. Once candidate gets job and need support for initial days also provided.

Detailed Technical Course Cntent

Data-warehouse Introduction

• Define Data-warehouse
• Data Warehouse Architecture
• Normalization and De-Normalization with
• The Top-Down Approach
• The Bottom-up Approach
• Differences between OLAP & OLTP
• OLTP and OLAP Architecture
• OLTP and OLAP Operations

Types of Data Warehouse

• Enterprise Data Warehouse – EDWH
• Data Marts –DM
• ODS (Operational Data Store)

Dimensional Modeling and its difference from ER modeling

• Data base and Data Warehouse difference
• Dimension & Facts , Types of Dimensions ,Types of Facts and Fact Tables
• Star Schema Architecture vs Snow-Flake Schema
• Pictorial Representation of Star & Snow Flake schemas
• Slowly Changing Dimensions [SCDI, SCDII and SCDIII] & their advantages and disadvantages
• Different types of data loadings – Full Load, Incremental Load and History Load

Data Extraction,Acquisition and loading

• Define Data Acquisition [ETL] with a complex ETL architecture.
• ETL Validation – Sources v/s Targets (Source vs Landing, Landing vs Dim and Dim vs Fact).
• ETL Architecture with real time examples w.r.t any ETL tools – Informatica MDM, BDM , AbInitio,SAS etc
• Insert and Update Strategy based on SCD during and after load .

ETL testing concepts based on  Manual Testing point of view which a tester must know

• What is software Testing & why it is important
• Testing Strategies need to be used while doing ETL testing.
• Testing Models – concepts of Waterfall , V-model and Agile model.
• ETL / DWH testing project category based on Real word Scenarios, which we really worked on.
• Application Testing (Functional Testing & TypesNon-Functional Testing & Types)
• Datacentric testing by using business transformations.
• ETL SDLC & flow of data based on direct pull or transformation r.t a real time complex architecture.
• ETL STLC & testers roles and responsibility on a day to day basis.
• Understanding an ETL Test plan & Test Strategy based on actual practical examples.
• HP ALM – Test case writing and upload,Defect logging,defect linking and tracking.
• Required complex SQL queries, Unix and other Realtime project practical Exposures .

Linux(UNIX) Commands used in ETL testing

• File Operations (Listing, View, Copy, Rename, Delete, Move, Create)
• File Operation Commands -ls (ls –lrt/ ls –ltr), cat ,cp , rm , mv , touch
• Directory Operations (Listing, Rename, Delete, Move, Create)
• Directory Operation Commands – cd , pwd ,mkdir , rmdir
• Permissions Using “chmod” command [rwx]
• Search Commands – find , locate , grep (grep -i <keyword> filename)
• Pipers and Filters
• wc (count of records, words etc)
• Other usefull commands on day to day use – more , sort ,tail , head
• vi editor , script running (./ script name)

ETL Tool [Informatica] Complete steps will be provided.

• Introduction to Informatica Powercenter
• Client Tools – Power Center Designer, Work Flow Designer, WF Monitor
  • Power Center Components – Designer, Repository Manager , Workflow Manager , Workflow Monitor
• -Sources vs Targets – Based on a realtime archetecture.
  • Working with Relational Targets and Flat file Targets
• -Transformations – Active and Passive Transformations
  • Aggregator,Expression, Filter , Sorter , Lookup ,Sequence Generator,Joiner ,Router
  • Insert and Update Strategy based on SCD and type of loads.
• -Workflow Manger
  • Workflows, Worklets & Sessions and  Tasks
• -Workflow Monitor
  • Workflow Monitoring, debugging errors and log validations (Ex- Error Logs, Session log, workflow log)

Basic understanding of Business Intelligence (Report) Testing (OLAP)

Process of BI Testing   (Front end vs Backend testing and OLTP vs OLAP)

BI Testing Tools (Cognos, SAS,SSRS, Micro strategy)

Writing BI Test Cases and Execution of BI Test Cases

How to generate reports based on filter conditions

Dashboard, header, footer, prompt,graphs,picharts,drilldown,histogram etc Validation Generating Reports and validating the same data in Database.

Note – Test Case writing, Defect logging is done by test management tool called HP ALM. Complete document will be provided. ETL tool specific Complete step by step study materials will be provided. Other practical’s like complex SQL query writing based on the ETL transformations will be clearly discussed with number of Scenario based testing keeping real time project in mind.Realtime projects will be discussed with test cases and mapping sheets,BRD and TSD.