ETL Testing Interview Questions

  • (5.0)
  •   |   4101 Ratings

ETL Interview Questions

Last Updated: May 4th, 2018

If you're looking for ETL Testing Interview Questions & Answers for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research ETL Testing has a market share of about 15%. So, You still have opportunity to move ahead in your career in ETL Testing Analytics. Mindmajix offers Advanced ETL Testing Interview Questions 2018 that helps you in cracking your interview & acquire dream career as ETL Testing Analyst.

Are you interested in taking up for ETL Testing Certification Training? Enroll for Free Demo on ETL Testing Training!

ETL Testing Interview Questions And Answers

Q) ETL Testing Vs DB Testing

Compare ETL Testing and DB Testing
ETL Testing DB Testing
Business Intelligence reporting Goal is to integrate data 
Business flow environment based on earlier data Applicable to business flow systems
Informatica, Cognos and QuerySurge can be used QTP and Selenium tools for automation
Analysing data may have potential impact Architectural implementation involves high impact.
Dimensional model Entity relationship model
Analytics are processed Transactions are processed
Denormalized data is used Data used is normalized

Q1) What exactly do you mean by ETL?

ETL stands for Extract Transform Load and is widely regarded as one of the essential tools in the data warehousing architecture. Its main task is to handle data management for the businesses process which is complex and are useful to the business in many ways. Extracting simply means reading the data from a database. Transformation means converting the data into a form which is suitable for analysis and reporting. The load on the other side handles the process of writing and managing the data into the database which users want to target simply.

Q2) There is a group of parameters that direct the server regarding the movement of the data from the source to the target. What it is called as?

It is called as Session.

Q3) Do you have any idea about the ETL testing and the operations that are a part of the same?

Well, there are certain important tasks that are opted in this. It simply means verifying the data in terms of its transformation in the correct or the right manner as per the needs of a business. It also includes the verification of the projected data. The users can also check whether the data is successfully loaded in the warehouse or not without worrying about the loss of data. The improvement in scalability, as well as the performance can also be assured from this directly. In addition to this, the ETL simply replaces the default values which are not always useful to the users.

Q4) How can you put Power Center different from the Power Mart?

Power Mart is good to be considered only when the data processing requirements are low. On the other side, the Power Center can simply process bulk time in a short span of time. Power Center can easily support ERP such as SAP while no support of the same is available on the ERP. The local repository can be supported by the Mart while the center cannot perform this task reliably. 

Q5) What is partitioning in ETL

The transactions are always needed to be divided for the better performance. The same processes are known as Partitioning. It simply makes sure that the server can directly access the sources through multiple connections. 

Q6) Name a few tools that you can easily use with the ETL

There are many tools that can be considered. However, it is not always necessary that a user needs all of them at the same time. Also, which tool is used simply depends on the preference and the task that needs to be accomplished. Some of the commonly used ones are Oracle Warehouse Builder, Congos Decision Stream, SAS Enterprise ETL server and SAS Business warehouse. 

Q7) What do you understand by the term fact in the ETL and what are the types of the same?

Basically, it is regarded as a federal component that generally belongs to a model which has multiple dimensions. The same can also be used when it comes to considering the measures that belong to analyzation. The facts are generally useful for providing the dimensions that largely maters in the ETL. The commonly used types of facts in ETL are Semi-additive facts, Additive Facts, as well as Non-additive Facts. 

Q8) What exactly do you know about the tracing level and the types of the same?

There are files logs and there is a limit on them when it comes to storing data in them. The Tracing level is nothing but the amount of data that can be easily stored on the same. These levels clearly explain the tracing levels and in a manner that provides all the necessary information regarding the same. There are two types of same and they are:

1. Verbose
2. Normal

Q9) Do you think the data warehousing and the data mining is different from one another. How the same can be associated with the warehousing applications?

Well, the warehousing applications that are important generally include the analytical process, Information processing, as well as Data mining. There are actually a very large number of predictive that needs to be extracted from the database with a lot of data. The warehousing sometimes depends on the mining for the operations involved. The data mining is useful for the analytical process while the other doesn’t. The data can simply be aggregated from the sources that are different through the warehousing approach while the same is not possible in case of mining. 

Q10) Do you have any information regarding the Grain of Fact?

The fact information can be stored at a level that is known as grain fact. The other name of this is Fact Granularity. It is possible for the users to change the name of the same when the need of same is realized. There are multiple files that are associated with the same and the users can use this for changing the name of all of them directly. 

Q11) It is possible to load the data and use it as a source?

Yes, in ETL it is possible. This task can be accomplished simply by using the Cache. The users must make sure that the Cache is free and is generally optimized before it is used for this task. At the same time, the users imply make sure that the desired outcomes can simply be assured without making a lot of efforts. 

Q12) What is Factless fact table in ETL?

It is defined as the table without measures in the ETL. There are a number of events that can be managed directly with the same. It can also record events that are related to the employees or with the management and this task can be accomplished in a very reliable manner. 

Q13) What exactly do you mean by the Transformation? What are the types of same?

It is basically regarded as the repository object which is capable to produce the data and can even modify and pass it in a reliable manner. The two commonly used transformations are Active and Passive. 

Q14) What is the exact purpose of an ETL according to you?

It is actually very beneficial for the extracting of data and from the systems that are based on legacy. 

Q15) Can you define measures in a simple statement?

Well, they can be called as the number data which is generally based on the columns and is generally present in a fact table by default.

Q16) When you will make use of the Lookup Transformation?

It is one of the finest and in fact one of the very useful approaches in the ETL. It simply makes sure that the users can get a related value from a table and with the help of a column value that seems useless. In addition to this, it simply makes sure of boosting the performance of a dimensions table which is changing at a very slow rate. Also, there are situations when the records already remain present in the table. Dealing with such issues can be made possible with the help of Lookup transformation. 

Q17) What do you understand by Data Purging?

There are needs and situations when the data needs to be deleted from the data warehouse. It is a very daunting task to delete the data in bulk. The Purging is an approach that can delete multiple files at the same time and enable users to maintain speed as well as efficiency. A lot of extra space can be created simply with this.

Q18) Can you tell something about Bus Schema?

Dimension identification is something that is very important in the ETL and the same is largely handled by the Bus Schema.

Q19) Are your familiar with the Dynamic and the Static Cache?

When it comes to updating the master table, the dynamic cache can be opted. Also, the users are free to use it for changing the dimensions. On the other side, the users can simply manage the flat files through the Static Cache.  It is possible to deploy both the Dynamic and the Static Cache at the same time depending on the task and the overall complexity of the final outcome. 

Q20) What do you mean by staging area?

It is an area which is used when it comes to holding the information or the data temporary on the server that controls the data warehouse. There are certain steps that are included and the prime one among them is Surrogate assignments. 

Q21) What are the types of Partitioning you are familiar with?

There are two types of Partitioning that is common in ETL and they are:

  1. Hash 
  2. Round-robin

Q22) Can you tell a few benefits of using the Data Reader Destination Adapter?

There are ADO record sets which generally consists of columns and records. When it comes to populating them in a simple manner, the Data Reader Destination Adapter is very useful. It simply exposes the data flow and let the users impose various restrictions on the data which is required in many cases.

Q23) Is it possible to extract the SAP data with the help of Informatica?

There is a power connect option that simply let the users perform this task and in a very reliable manner. It is necessary to import the source code in the analyzer before you accomplish this task. 

Q24) What do you mean by the term Mappet?

This is actually an approach that is useful for creating or arranging the different sets in the transformation. It simply let user accomplish other tasks also that largely matters and are related to the data warehouse. 

Q25) What are commercial ETL tools?

  1. Ab Initio
  2. Adeptia ETL
  3. Business Objects Data Services
  4. Informatica PowerCenter
  5. Business Objects Data Integrator (BODI) Confluent
  6. DBSoftLab

Q26) Can you explain the fact table?

In datawarehouse fact table is the central table of star schema.

Q27) What are the types of measures?

There are 3 types of measures:

  • Additive Measures - Can be joined across any dimensions of fact table.
  • Semi Additive Measures - Can be joined across only some of dimensions of fact table.
  • Non Additive Measures - Cannot be joined across any dimensions of fact table.

Q27) Give an brief on Grain of Fact?

Grain fact functionality defined as a level/stage where the fact information will be stored. Also called as Fact Granularity.

Q28) Define Transformation?

In ETL, Transformation involves, data cleansing, Sorting the data, Combining or merging and appying teh business rules to the data for improvisong the data for quality and accuracy in ETL process.

Q29) What is Lookup Transformation?

The Lookup transformation accomplished lookups by joining information in input columns with columns in a reference dataset. You utilize the lookup to get to extra data in a related table that depends on values in like common columns.

Check Out ETL Testing Tutorials

Q30) Is it possible to update the table using the SQL service?

Yes, it is actually possible and the users can perform this task simply and in fact without worrying about anything. The users generally have several options to accomplish this task easily. The methods that can be helpful in this matter are using a staging cable, using a SQL command, using the MSSQL, as well as using the Cache. 

Q31) How can you define a Workflow?

It is basically a group that contains instructions that let the server perform the executions related tasks. 

Q32) Tell one basic difference between the Connected Lookup and Unconnected ones?

Mapping is common in the connected lookups while it is not so common in the latter. It is only used in the Unconnected lookup only when the function is already defined. . There are several values that can be returned from the Connected Lookup while the Unconnected Lookup has a strict upper limit on the same. 

Q33) Tell something about the Data source view and how it is significant?

There are several analysis services databases that largely depend on the relational schema and the prime task of the Data source is to define the same. They are also helpful in creating the cubes and dimensions with the help of which the users can set the dimensions in a very easy manner.

Q34) What are objects in the Schema?

These are basically considered as the logical structures that are related with the data. They generally contain tables, views, synonyms, clusters as well as function packages. In addition to this, there are several database links which are present in them. 

Q35) What are Cubes in the ETL and how they are different from that of OLAP?

There are things on which the data processing depends largely and cubes are one among them, they are generally regarded as the units of the same that provide useful information regarding the dimensions and fact tables.  When it comes to multi-dimensions analysis, the same can simply be assured from this. On the other side, the Online Analytics Processing stores a large data in the dimensions that are more than one. This is generally done for making the reporting process more smooth and reliable. All the facts in it are categorized and this is exactly what that makes them easy to understand. 

Q36) Unconnected Vs Connected Lookups

Connected Lookup Unconnected Lookup
Either dynamic or Stactic Cache can be used.  Can use only Static Cache.
We can return multiple rows from the same row  Can return only one output port
It supports user-defined values  It wont support user defined values
We can pass any number of values to another tranformation. Can pass one output value to one transformation
Cache has all lookup columns that are used in mapping. Cache has all the lookups or output ports of lookup conditions and return port.

Q37) Define Bus Schema?

In data warehouse, BUs Schema is used for identifying the most common dimensions in business process.  In one word its is definte dimension and a standardized equivalent of facts.

Q38) What data purging means?

Data Purging - Common word that used in data warehousing for deleting or erasing data from the storage.

Q39) Schema Objects means?

Schema objects can be defined as the logical structures, where as DB stores the schema object logically within a database tablespace. Schema Objects can be the tables, clusters or views, sequence or indexes, functions packages and db links.

Q40) Can you brief the terms Mapplet, Session, Workflow and Worklet?

  • Mapplet : Reusable object that contains a set of transformations.
  • Worklet: Represents set of workflow tasks
  • Workflow: Customs the tasks for each record that need to execute.
  • Session: set of instructions that instructs how to flow the data to the target.
Explore ETL Testing Sample Resumes! Download & Edit, Get Noticed by Top Employers!Download Now!

Popular Courses in 2018

Get Updates on Tech posts, Interview & Certification questions and training schedules