ETL Testing
– hrs Theory Sessions
– hrs of Practice Sessions
– hrs of Hands-on Project
Introduction
ETL Testing is a methodology enabling a user to test an ETL process by validating and comparing source data to destination data using business rules or transformation rules . Thus, ETL testing is essentially a form of black-box testing without the bells and whistles of a full blown graphical user interface
Course Objective
Data Transformation
The process of converting the data from one format or granularity to another is Data Transformation. ETL processes use filters, aggregators, lookup, and other transforms for effecting Data Transformations. .Testing and identifying data transformation issues by comparing and testing input and output data is the core activity constituting ETL testing.
Business Rules
In spite of a technically correct ETL transformation, devoid of any technical defects or issues, the likelihood of negating or violating business rules remains highly probable. This necessitates an extra step of business rules validations over and above the technical ETL testing. Thus, technical ETL testing is a part of the larger ETL testing umbrella and must be complemented by business rules testing for a comprehensive ETL test coverage.
Data Mapping
An ETL process is developed by mapping the source data attributes to the target attributes. This mapping of information to load the data from source to destination may result in data malformations, data corruption and defects. Thus, it is essential to test an ETL process for data mapping issues.
Data Quality
Amongst other things, data quality checks after an ETL process is completed includes checking that the ETL process is loading the data with correct formats, reference values, or not generating any duplicates is part of testing an ETL process.
Course Curriculum
Data Warehousing concepts
- Dataware house architecture
- Datamart
- DW Schema – Star Schema – Snow Flake Schema
- Objects in DWH – Dimension table – Fact table
- SCD(SLOWLY CHANGING DIMENSIONS) – SCD TYPE 1 – SCD TYPE 2 – SCD TYPE 3
Differences between OLTP and OLAP SDLC And STLC
- Agile Methodologies
- Waterfall Model
SQL (Structured Query Language)
- DDL (Data definition language)
- DML (Data manipulation language)
- DQL (Data query language)
Data WareHousing Life Cycle (ETL Testing Types)
- Data migration testing
- Sample Testing and Scenario Testing
- BATCH JOB Testing
- Inbound Testing
- OutBound Testing
ETL Testing Tasks
- Spec analysis
- Designing Testcases
- ETL Test scripts preparation
- Environment Preparation
- Test Execution
- Peer Reviews of QA deliverables
ETL Testing Validations
- Structure validation
- Count validation
- TRANSFORMATION Logic Check
- Data validation
- MINUS Query
- Duplicate Check
- Error logic Check
- RI Validation( Referential integrity constraint check)
- Scenario testing
- Derived checks
- Default values check
Data Integrity check
Basic checks at ETL Phases
- Extraction phase testing
- Transformation phase testing
- Loading phase testing
Additional Tools
Tools used for testing
- AnyDbTest.
- DBFit.
- DBUnit.
- NDbUnit.
- SQLUnit.
- TSQLUnit (for testing T-SQL in MS SQL Server)
- Visual Studio Team Edition for Database Professionals includes testing capabilities.