Data Warehouse and ETL Testing Concepts

This post contains the topics related to Data Warehouse and ETL Testing Concepts, if you want to be a ETL Tester you should know the below terminology. Click on the link to get more information.

BI CONCEPTS

DATA WAREHOUSE (DWH) DESIGN & CONCEPTS:

            Data Warehouse Architecture / Diagram / Components

            WHAT IS DATA WAREHOUSE?

            INTRODUCTION TO OLTP, OLAP, ROLAP, MOLAP

            DIFFERENCE BETWEEN OLTP AND DATA WAREHOUSING
            DIFFERENCE BETWEEN OLTP AND OLAP
            OLTP VS DWH
            OLTP VS OLAP

            DM VS DWH

            METADATA

           

            DATA ACQUISITION:

                        DATA EXTRACTION

                        DATA TRANSFORMATION

                        DATA LOADING

                       

            DATA MARTS AND ODS TYPES:

                        DEPENDENT DATA MART

                        INDEPENDENT DATA MART

                        DATA BASE DESIGN

                        STAR SCHEMA

                        SNOW FLAKE SCHEMA

                        FACT CONSTELLATION SCHEMA

                        SCD (SLOWLY CHANGING DIMENSION)

                        GALAXY SCHEMA

                        FCS

                        TYPE-1 SCD

                        TYPE-2 SCD

                        TYPE-3 SCD

DATA WAREHOUSING LIFE CYCLE (DWH LIFECYCLE)

TERMS To know:

            FACT AND DIMENSION TABLE

            DIMENSIONAL MODELING

            DWH TOOLS

            DATA CLEANZING

            DATA SCRUBING

            DATA MASKING

            NORMALIZATION

            ODS

            STG AREA

            DSSDIMENTION MODELING

            FACTS

            AGGREGATES

            SLOWLY CHANGING DIMENSIONS

            INITIAL LOAD

            INCREMENTAL LOAD

            FULL LOAD

            CDC- CHANGE DATA CAPTURE

ETL TESTING CONCEPTS:
            INTRODUCTION OF ETL - EXTRACT, TRANSFORM, AND LOAD
            ETL ARCHITECTURE/ CYCLE
            COMPONENTS IN ETL
            ETL TOOLS AND DIFFERENT TYPES OF ETL TOOLS
            ETL PROCESS / ETL TESTING LIFE CYCLE
            TEST STRATEGY
            TEST PLANNING
            SDLC METHODS/MODELS
            STLC METHODS/MODELS
            SDLC VS. STLC
            REVERSE ENGINEERING
            QC (QUALITY CENTER AND BUGZILLA)
            ETL TESTING ROLES AND RESPONSIBILITIES
            ETL TESTING RESPONSIBILITIES IN SSIS, ABINITIO, INFORMATICA, SQL-SERVER, DATASTAGE ETC;
            ETL TESTING TEMPLATES (TEST CASE, BUG REPORTING & ETC..)
            TYPES OF ETL BUGS/DEFECTS

ETL TRANSFORMATION TYPE/RULES:
            SAMPLE LOADING FROM SOURCE TARGET
            SORTER
            FILTER
            ROUTER
            EXPRESSION
            JOINER
            UNION
            AGGREGATOR
            RANK
            STORED PROCEDURE
            SEQUENCE GENERATOR
            FLAT FILE
            SQL

ETL TYPES OF TESTING:
            WHITEBOX TESTING
            BLACKBOX TESTING
            GRAY BOX TESTING
            REGRESSION TESTING
            PERFORMANCE
            OPERATIONAL QUALIFICATION
            SMOKE TESTING VS SANITY TESTING
            USER TESTING
            UNIT TESTING
            INTEGRATION TESTING
            MODULE TESTING
            SYSTEM TESTING
            UAT
                       
ETL TOOL AND TESTING:
            DATA EXTRACT
            DATA TRANSFORM
            DATA LOAD
            IMPORT SOURCE
            IMPORT TARGET
            MAPPINGS, MAPLETS
            WORKFLOWS WORKLETS
            TRANSFORMATIONS, FUNCTIONALITY, RULES AND TECHNIQUES
            IMPORT AND EXPORT
            COPING AND RULES
            QUERIES PREPARATION BASED ON TRANSFORMATIONS
            IMPORTANCE OF ETL TESTING
            CREATING OF MAPPINGS, SESSIONS, WORKFLOWS
            RUNNING OF MAPPINGS, SESSIONS, WORKFLOWS
            ANALYZING OF MAPPINGS, SESSIONS, WORKFLOWS
            TASKS AND TYPES
           
RESPONSIBILITIES OF AN ETL TESTER:
            COUNT QUERYING
            MINUS QUERYING
            DUPLICATE QUERYING
            INTERSECTION, ETC
            NO DATA LOSSES
            VALIDATION OF TRANSFORMATION RULES
            BUSINESS PROCESSES TESTING
            TESTING SCENARIOS, CREATION OF TEST CASES AND SCRIPTS
            TEST CASE EXECUTION AND DEFECT TRACKING AND REPORTING
            PREPARATION OF TEST DATA
            DETECT DEFECTS
            DEFECTS LOGGING AND REPORTING
            BUG REPORTING
            TEST PLAN PREPARATION
            TEST CASES PREPARATION
            PREPARATION OF TEST DATA
            MAKE SURE DATA IS TRANSFORMED CORRECTLY
                       
BEST PRACTICES FOR ETL TESTING:

            AUTOMATION OF ETL TESTING
            DESIGN THE TEST CASES
            REPORTING THE BUGS
            DETECT THE BUGS THROUGH DATABASE QUERIES
            PREPARE QUERIES QUICKLY WITH THE HELP OF MAPPING
            PERFORMANCE TUNING AND PERFORMANCE TESTING, REPORT TESTING, UI TESTING

You Should also know:
            ETL INTERVIEW QUESTIONS AND ANSWERS
            REAL TIME EXAMPLE
            SQL
            UNIX  

SDLC (SOFTWARE DEVELOPMENT LIFE CYCLE):
            WATERFALL MODEL
            V-MODEL
            AGILE MODEL & METHODOLOGY
            PROTOTYPE MODEL
            SPIRAL MODEL

+Vivek Kumar