Skip to main content

ETL-Testing types- Bugs and Tools

Types of ETL Testing

Types ETL Bugs

ETL Tools

Some Basic SQL commands for ETL Testing

Types of ETL testing

1)       Constraint Testing:

In the phase of constraint testing, the test engineers identifies whether the data is mapped from source to target or not.

The Test Engineer follows the below scenarios in ETL Testing process.

  1. a) NOT NULL
  2. b) UNIQUE
  3. c) Primary Key
  4. d) Foreign key
  5. e) Check
  6. f) Default
  7. g) NULL

 

2)      Source to Target Count Testing:

In the Source to Target data is matched or not. A Tester can check in this view whether it is ascending order or descending  order it doesn’t matter .Only count is required for Tester. Due to lack of time a tester can follow this type of Testing.

 

3)      Source to Target Data Validation Testing:

In this Testing, a tester can validate the each and every point of the source to target data. Most of the financial projects, a tester can identify the decimal factors.

 

4)      Threshold/Data Integrated Testing:

In this Testing, the Ranges of the data, A test Engineer can usually identifies the population calculation and share marketing and business finance analysis (quarterly, half yearly, yearly)

MIN       MAX     RANGE

4              10           6

 

5)      Field to Field Testing:

In the field to field testing, a test engineer can identify that how much space is occupied in the database. The data is integrated in the table cum datatypes. Check the order of the columns and source column to target column.

 

6)      Duplicate Check Testing:

In this phase of ETL Testing, a Tester can face duplicate value very frequently so, at that time the tester follows database queries why because huge amount of data is present in source and Target tables.

Select ENO, ENAME, SAL, COUNT (*) FROM EMP GROUP BY ENO, ENAME, SAL HAVING COUNT (*) >1;

Note:

  • There are no mistakes in Primary Key or no Primary Key is allotted then the duplicates may arise.
  • Sometimes, a developer can do mistakes while transferring the data from source to target at that time duplicates may arise.
  • Due to Environment Mistakes also duplicates arise (Due to improper plugins in the tool).

7)      Error/Exception Logical Testing:

  • Delimiter is available in Valid Tables
  • Delimiter is not available in invalid tables(Exception Tables)

8)      Incremental and Historical Process Testing:

In the Incremental data, the historical data is not corrupted. When the historical data is corrupted then this is the condition where bugs raise.

9)      Control Columns and Defect Values Testing:

This is introduced by IBM

10)   Navigation Testing:

Navigation Testing is the End user point of view testing. An end user cannot follow the friendly of the application that navigation is called as bad or poor Navigation.

At the time of Testing, A tester can identify this type of navigation scenarios to avoid unnecessary navigation.

11)   Initialization testing:

A combination of hardware and software installed in platform is called the Initialization Testing

12)    Transformation Testing:

At the time of mapping from source table to target table, Transformation is not in mapping condition, then the Test Engineer raises bugs.

13)   Regression Testing:

Code modification to fix a bug or to implement a new functionality which makes us to to find errors. These introduced errors are called regression. Identifying for regression effect is called regression testing.

14)   Retesting:

Re executing the failed test cases after fixing the bug.

15) System Integration Testing:

Integration testing: After the completion of programming process . Developer can integrate the modules there are 3 models

  1. a) Top Down
  2. b) Bottom Up
  3. c) Hybrid

 

Types of ETL Bugs

  1. User interface bugs/cosmetic bugs:-
  •      Related to GUI of application
  •      Navigation, spelling mistakes, font style, font size, colors, alignment.
  1. BVA Related bug:-
  •      Minimum and maximum values
  1. ECP Related bug:-
  •      Valid and invalid type
  1. Input/output bugs:-
  •      Valid values not accepted
  •      Invalid values accepted
  1. Calculation bugs:-
  •      Mathematical errors
  •      Final output is wrong
  1. Load condition bugs:-
  •      Does not allows multiple users
  •      Does not allows customer expected load
  1. Race condition bugs:-
  •      System crash & hang
  •      System cannot run client plat forms
  1. Version control bugs:-
  •      No logo matching
  •      No version information available
  •      This occurs usually in regression testing
  1. H/W bugs:-
  •      Device is not responding to the application
  1. Source bugs:-
  •      Mistakes in help documents

 

ETL tools:

The ETL tools were created to simplify the data management with simultaneous reduction of absorbed effort.

The first task is data extraction from internal or external sources. After sending queries to the source system data may go indirectly to the database. However usually there is a need to monitor or gather more information and then go to Staging Area . Some tools extract only new or changed information automatically so we don’t have to update it by our own.

The second task is transformation which is a broad category:

  • transforming data into a structure which is required to continue the operation (extracted data has usually a structure typical to the source)
  • sorting data
  • connecting or separating
  • cleansing
  • checking quality

The third task is loading into a data warehouse. ETL Tools have many other capabilities (next to the main three: extraction , transformation and loading) like for instance sorting , filtering , data profiling , quality control, cleansing , monitoring , synchronization and consolidation.

ETL Tools providers:

Here is a list of the most popular comercial and freeware(open-sources) ETL Tools.

Comercial ETL Tools:

  • IBM Infosphere DataStage
  • Informatica PowerCenter
  • Oracle Warehouse Builder (OWB)
  • Oracle Data Integrator (ODI)
  • SAS ETL Studio
  • Business Objects Data Integrator(BODI)
  • Microsoft SQL Server Integration Services(SSIS)
  • Ab Initio

Freeware, open source ETL tools:

  • Pentaho Data Integration (Kettle)
  • Talend Integrator Suite
  • CloverETL
  • Jasper ETL

 

List of ETL tools

List of ETL Tools ETL Vendors
Oracle Warehouse Builder (OWB) Oracle
Abinitio Ab initio Software Corporation
Data Services SAP Business Objects
IBM Information Server (Datastage) IBM
SAS Data Integration Studio SAS Institute
PowerCenter Informatica Informatica
Elixir Repertoire Elixir
Data Migrator Information Builders
SQL Server Integration Services Microsoft
Talend Studio for Data Integration Talend
DataFlow Manager Pitney Bowes Business Insight
Pervasive Data Integrator Pervasive Software
Open Text Integration Center Open Text
Oracle Data Integrator (ODI) Oracle
Data Manager/Decision Stream IBM (Cognos)
Clover ETL Javlin
Centerprise Astera
DB2 Infosphere Warehouse Edition IBM
Pentaho Data Integration Pentaho
Adeptia Integration Suite Adeptia
DMExpress Syncsort
Expressor Data Integration QlikTech

 

Some Basic SQL commands for ETL Tester –

SQL SELECT Statement:

SELECT column1, column2….columnN

FROM   table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2….columnN

FROM   table_name;

SQL WHERE Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  CONDITION;

SQL AND/OR Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  CONDITION-1 {AND|OR} CONDITION-2;

SQL IN Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  column_name IN (val-1, val-2,…val-N);

SQL BETWEEN Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  column_name LIKE { PATTERN };

SQL ORDER BY Clause:

SELECT column1, column2….columnN

FROM   table_name

WHERE  CONDITION

ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name;

SQL COUNT Clause:

SELECT COUNT(column_name)

FROM   table_name

WHERE  CONDITION;

SQL HAVING Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name

HAVING (arithematic function condition);