ETL Testing Online Training

Importance of ETL testing

A super market has implemented DWH it has become easy for the high level management to take decision on

• How to increase the market share of the company by 5%?
• Which product is not doing well in the market?
• Which department needs help with selling products?
• What is the quality of the customer service provided and what improvements are needed?
• what products are sold
• what is not selling
• when does the sale go up
• what is the age group of customers who are buying a particular product and several other queries

Scope of ETL testing

In early days of 80s, there were simple applications. With the speed of light, the world progressed from mainframes and batch applications to personal computers and online applications. At the same time, there emerged a class of data applications that are now known as business intelligence and data warehousing.

Data warehousing lets people look at corporate data as never before. Patterns, trends, seeing the forest and the trees — all became a possibility with data warehousing. Business people were able to look at their enterprises as no one had ever been able to previously and entire industries and multi-billion dollar companies grew out of this love of knowing more. Companies saw that business intelligence and data warehousing allowed them to make important corporate decisions based on new perspectives gained from data gathered from all over the corporation.

However, the thirst for more insight into data created by warehousing techniques created a virtual flood of data. Data Warehouses stored detailed data, historical data, and data integrated from a wide variety of sources. There was an inevitable formula that applied to every data warehouse that has ever been built –

Detail x History x Many Sources = Lots of Data

Fortunately, technology grew to the extent that large volumes of data could be handled. However, even today only a few solutions offer true unrestricted ad hoc access to this class of data.

So the corporate or MNC’s requirement in india and abroad is to recruit more and more people with adequate knowledge in Datawarehouse and Business Intelligence. Areas where DWH can be implemented are:

Banking
Retail
Insurance
Telecom
Healthcare etc..

Datawarehouse Concepts

•  DWH architecture
• Charactersitics of DWH
•  DataBase
•  Difference between DWH and DB
•  ETL
•  DATA MART
•  Data Mart vs DWH
•  OLTP vs OLAP
•  Data Modelling
•  Dimensions &Facts tables

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)
• 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.
• 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
• 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
• Different ETL tools available in the market
  • Informatica
  • Ab Initio
  • IBM Data stage
• Power Center Components
  • Designer
  • Repository Manager
  • Workflow Manager
  • Workflow Monitor
  • Power Center Admin Console
• Informatica Concepts and Overview
  • Informatica Architecture.
• SourcesWorking with relational Sources
  • Working with Flat Files
• Targets
  • Working with Relational Targets
  • Working with Flat file Targets
• 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
• Workflow Manger
  • Creating Reusable tasks
  • Workflows, Worklets & Sessions
  • Tasks
• Session
• Decision task
• Control Task
• Event wait task
• Timer task
  • Monitoring workflows and debugging errors
• 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
• 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.

SQL

•  DDL, DML,TCL,DCL Statements
•  Character functions
•  Aggregate/group functions
•  Date functions
•  Distinct, order by , or, between , like, group by conditions
•  Set operators
•  Constraints
•  Joints
•  Rowid, rownum, partition by, case statements, rank and dense rank
•  Creating index, views, back up table

UNIX

• Pwd, ls, ls , ls –l, ls –lrt
•  Cd , cat , head , tail , more ,
•  Wc , wc –l, wc –c, cp, mv, rm, rm –r ,diff,
•  Unzipping a file
•  Sed, grep
•  Vi editor
•  Real time commands like
•  How to convert a flat file to table using unix
•  Find the count of flat file\
• Validating the header of flat file
• Validating the trailer of flat file
•  Find the detailed record of flat file
•  Validating the delimiter of flat file

MANUAL TESTING

SDLC and various models
Functional, integrating, acceptance , system, regression, smoke and sanity testing
How to write test cases, how to prepare test summary reports
Defect life cycle and how to log defects
STLC
Complete real time knowledge on test management tool QC.

INFORMATICA TOOL

• Work flow designer, manager and monitor window
•  Different work flow status
•  Validations in real time and transformations like
•  Aggregator ,Filter , Expression , Router , Lookup transformations
•  Data quality and its rules
•  Slowly changing dimensions
•  How to convert flat to data files and vice versa using informatica tool