Top 10 ETL Testing Interview Questions and Answers

1. What is ETL?

ETL means extract, transform and load, three database functions that are combined into one tool to pull data out of one database and place it into another database. Extract is the process of reading data from a database.

2. What are the popular ETL tools available in the market?

The popular ETL tools available in the market are −

Informatica − Power Centre
IBM − Websphere DataStage (Formerly known as Ascential DataStage)
SAP − Business Objects Data Services BODS
IBM − Cognos Data Manager (Formerly known as Cognos Decision Stream)
Microsoft − SQL Server Integration Services SSIS
Oracle − Data Integrator ODI (Formerly known as Sunopsis Data Conductor)
SAS − Data Integration Studio
Oracle − Warehouse Builder
ABInitio
Open source Clover ETL.
Elixir Repertoire for Data ETL.
Data Migrator (IBI)

3. What is a Data Mart?

A data mart is a simple form of data warehouse and it is focused on a single functional area. It usually gets data only from a few sources.

Example − In an organization, data marts may exist for Finance, Marketing, Human Resource, and other individual departments which store data related to their specific functions.

4. Explain what is partitioning, hash partitioning and round-robin partitioning?

To improve performance, transactions are subdivided, this is called as Partitioning. Partitioning enables Informatica Server for creating multiple connections to various sources

The types of partitions are

Round-Robin Partitioning:

By Informatica data is distributed evenly among all partitions
In each partition where the number of rows to process is approximately same, this partitioning is applicable

Hash Partitioning:

5. How can you extract SAP data using Informatica?

With the power connect option you extract SAP data using Informatica
Install and configure the PowerConnect tool
Import the source into the Source Analyzer. Between Informatica and SAP PowerConnect act as a gateway. The next step is to generate the ABAP code for the mapping then only Informatica can pull data from SAP
To connect and import sources from external systems Power Connect is used.

Are You Looking for ETL Testing Training in Bangalore?

6. Explain what staging area is and what is the purpose of a staging area?

Data staging is an area where you hold the data temporary on data warehouse server. Data staging includes following steps
Source data extraction and data transformation (restructuring)
Data transformation (data cleansing, value transformation)
Surrogate key assignments

7. Explain these terms Session, Worklet, Mapplet, and Workflow?

Mapplet: It arranges or creates sets of transformation
Worklet: It represents a specific set of tasks given
Workflow: It’s a set of instructions that tell the server how to execute tasks
Session: It is a set of parameters that tell the server how to move data from sources to target

8. Why ETL testing is required?

To keep a check on the Data which are being transferred from one system to the other
To keep a track on the efficiency and speed of the process
To be well acquainted with the ETL process before it gets implemented into your business and production.

9. What are ETL tester responsibilities?

Requires in-depth knowledge of the ETL tools and processes
Needs to write the SQL queries for the various given scenarios during the testing phase
Should be able to carry our different types of tests such as Primary Key, defaults and keep a check on the other functionality of the ETL process.
Quality Check

10. What is an Operator in SQL? Explain common operator types.

Operators are used to specifying conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement. The common operator types are −

Arithmetic Operators
Comparison/Relational Operators
Logical Operators
Set Operators
Operators used to negate conditions

Interview Questions

Top 10 ETL Testing Interview Questions and Answers