Top 10 ETL Testing Interview Questions and Answers

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 −

  1. Informatica − Power Centre
  2. IBM − Websphere DataStage (Formerly known as Ascential DataStage)
  3. SAP − Business Objects Data Services BODS
  4. IBM − Cognos Data Manager (Formerly known as Cognos Decision Stream)
  5. Microsoft − SQL Server Integration Services SSIS
  6. Oracle − Data Integrator ODI (Formerly known as Sunopsis Data Conductor)
  7. SAS − Data Integration Studio
  8. Oracle − Warehouse Builder
  9. ABInitio
  10. Open source Clover ETL.
  11. Elixir Repertoire for Data ETL.
  12. 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:
  1. By Informatica data is distributed evenly among all partitions
  2. In each partition where the number of rows to process is approximately same, this partitioning is applicable
Hash Partitioning:
  1. For the purpose of partitioning keys to group data among partitions, Informatica server applies a hash function
  2. It is used when ensuring the processes groups of rows with the same partitioning key in the same partition need to be ensured

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
Jobs in Bangalore
Best Training

Quick Enquiry