ETL Testing Interview Questions

ETL Testing Interview Questions: ETL (Extract, Transform, Load) testing is a critical process in data warehousing and business intelligence systems that involves testing the accuracy, completeness, and timeliness of data movements from source systems to target systems. ETL testing ensures that data is correctly extracted, transformed, and loaded from various sources into a centralized repository.

As the importance of data-driven decision-making continues to grow, there is an increasing demand for professionals with ETL testing skills. If you are preparing for an ETL testing interview or ETL questions, it is essential to have a strong understanding of ETL concepts, tools, and techniques. ETL testing interview questions are designed to evaluate your knowledge and proficiency in these areas.

In this article, we will explore some common ETL testing interview questions or ETL questions and provide tips on how to approach them. By mastering these questions, you can demonstrate your expertise and increase your chances of landing your dream ETL testing job.

What Is ETL Testing?

ETL testing is the process of verifying the accuracy, completeness, and timeliness of data movements from source systems to target systems in data warehousing and business intelligence systems. ETL testing ensures that data is correctly extracted from various sources, transformed into a format suitable for analysis, and loaded into a centralized repository.

ETL testing involves a range of activities, including data validation, reconciliation, integration, and error handling. ETL testing is critical in ensuring the quality and reliability of data for decision-making and reporting purposes. It helps organizations identify and rectify any issues with their ETL processes, ensuring that data is consistent, accurate, and trustworthy.

ETL Testing Interview Questions

• How to get duplicate values?
• What is the difference between an aggregator and an expression transformer?
• How to perform structure validations?
• If a source has 1000 records and a target has 100 records, then how can you validate?
• If a source has 20000 targets and 25000 records, then how can you get an extra 5000 records?
• How to retrieve only matched records?
• I have the phone number column, but some customers didn’t give their phone numbers. How do you get the count of customers who didn’t give their phone numbers?
• What requirements do you have in your project?
• After completion of the validations on DWH, in which format do you give the data to your client?
• Write a query to update male with female and female with male records.
• Explain about grep and count commands in UNIX.
• What is normalization explained with an example?
• Find the manager’s salary in the table.
• Find empno’s salary?
• How do find out duplicate records from a table?
• How do you delete the duplicate records from the table?
• What is the difference b/w union and union all?
• Difference b/w rank and dense_rank?
• What is the difference b/w ETL testing and DB testing?
• What is the difference b/w ETL testing and data mining?
• Why do we need testing?
• What is testing?
• What is UAT?
• Type of joins?
• Type of constraints?
• What is the composite primary key?
• What are STLC and DLC?
• What is your ETL process?
• What is UI testing?
• What is system integrity testing?

ETL Testing Interview Questions

Some common ETL questions are coming from Informatica. If the candidate knows all of them, then I can predict a successful ETL testing interview with a very high probability.

• Why do we use lookup transformations?
• Where should U place the flat file to import the flat file definition to the designer?
• To provide support for Mainframes source data, which files r used as source definitions?
• Which transformation should u need while using the COBOL sources as source definitions?
• How can U create or import flat file definitions into the warehouse designer?
• What is the maplet?
• What is a transformation?
• What are the designer tools for creating transformations?
• What are the active and passive transformations?
• What are the connected or unconnected transformations?

ETL Developer Interview Questions

• How many ways did u create ports?
• How can U improve session performance in aggregator transformation?
• What is the difference between joiner transformation and source qualifier transformation?
• In which conditions we cannot use joiner transformation (Limitations of joiner transformation)?
• What are the settings that u use to configure the joiner transformation?
• What are the join types in joiner transformation?
• What is the look-up transformation?
• Why use the lookup transformation?
• What are the types of lookups?
• Which transformation should we use to normalize the COBOL and relational sources?
• How does the Informatica server sort the string values in Rank transformation?
• What is the Rank index in Rank transformation?
• What is the Router transformation?
• What are the types of groups in Router transformation?
• What is source qualifier transformation?
• What are the tasks that the source qualifier performs?
• What is the default join that the source qualifier provides?
• What are the basic needs to join two sources in a source qualifier?
• What is update strategy transformation?
• What is the default source option for update strategy transformation?

ETL SQL Interview Questions

• What is Data-driven?
• What are the options in the target session of update strategy transformation?
• What is batch, and describe types of batches?
• Can u copy the batches?
• In a sequential batch, can u run the session if the previous session fails?
• Can u start a session inside a batch individually?
• Performance tuning in Informatica?
• What is the difference between maplet and reusable transformation?
• Define Informatica repository.
• What r the types of metadata stored in the repository?
• What is a power centre repository?
• If a session fails after loading 10,000 records into the target. How can u load the records from
• If I have done any modifications to my table in the back end, does it reflect in the Informatica warehouse or mapping
• What is Data Cleansing?
• Explain the Informatica Architecture in detail
• At the max, how many transformations can be used in a mapping?
• What is the difference between Normal load and Bulk load?
• Can we look up a table from a source qualifier transformation
• Discuss which is better among incremental load, Normal Load, and Bulk load
• How do you join two tables without using the Joiner Transformation?
• What is the limit to the number of sources and targets you can have in a mapping
• What is the difference between IIF and DECODE Function
• What is the procedure to load the fact table? Give in detail?
• What is worklets, what use of work, and in which situation can we use it
• Can Informatica load heterogeneous targets from heterogeneous sources?
• What is rank transformation? Where can we use this transformation?
• What is the surrogate key? In your project, in which situation have you used? explain with an example.
• Why is sorter transformation an active transformation?
• How is the union transformation active transformation?

ETL Questions

• How can you improve the performance of Aggregate transformation?
• What are the common errors that you face daily?
• While Running a Session, what are the two files it will create?
• How many types of flat files are available in Informatica?
• What are the Client and Server components in Informatica?
• What is Metadata, and where will it be stored in Informatica?
• What is the Data acquisition process?
• What are GUI and Code-based ETL tools?
• What are the types of Metadata stored in the Repository?
• What are the tasks performed by Sequence Generator Transformation?
• Which join is not supported by Joiner transformation?
• How many ways can we create ports in Informatica?
• How many ways can we control Transactions in Informatica?
• How many ways can we perform Data-Driven operations in Informatica?
• Which transformation can we use for data scrubbing?
• Which transformation can we use for data cleansing?

ETL Testing Questions

There are also some other ET concepts in the interview questions.

• Explain your project.
• Explain your project architecture.
• Explain your roles and responsibilities.
• How many tables are you maintaining in your project? For how many tables do you do testing?
• What all are the challenges you faced while doing ETL testing in your project?
• Tell me different scenarios that you validated in your project.
• How will you validate data for Incremental and Initial load data?
• How will you validate data for History load and Full load?
• Which techniques do you follow while doing ETL testing?
• Tell me about different test cases for the new table.
• How will you validate data for Enhancement work?
• How to validate data in the target table if the source table is present in a different database?
• How do we validate data for the flat file in the table?
• How much data is maintained in your project warehouse?
• Tell me about your client’s details and explain their business.
• How do you find duplicate records in a table?
• Why do we need to perform Source minus Target and Target MinusSource?
• Explain any complex query you wrote while testing.
• What are the Entry Criteria and Exit Criteria for your project?
• What are your responsibilities in your project?
• How can you execute more test cases within a one-day span of time? On what basis will you pick test cases?
• What are the different tools you used in your project?
• What is a data completeness check, and what kind of checks do you perform in this?
• Defect life cycle?
• What are different ETL testing techniques?
• Tell me about yourself.
• Tell me about your professional experience.