ETL Testing Interview Questions: ETL testing interview questions are technical questions that are asked in an interview for software testing jobs. These are frequently asked because they test the knowledge of the interviewer on certain topics that may be relevant to the role.
ETL testing interview questions may include understanding what data is extracted from a database, how it can be used to identify defects, and how errors should be reported. These questions may be asked because the role involves data extraction and business processes. Some of these questions may include:
Check Also: Software Testing Interview Questions
ETL Testing Interview Questions
- How to get duplicate values?
- Difference between aggregator and expression transformer?
- How to perform structure validations?
- A source having 1000 records and target having 100 records then how can you validate?
- A source having 20000 targets having 25000 records then how can you get extra 5000 records?
- How to retrieve only matched records?
- I have the phone number column but some customers didn’t give phone number, so how do you get the count of customers who didn’t give phone no?
- What are 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?
Before going to the interview you have some must knowledge like ETL process, Data warehouse queries, and if you going for the ETL testing tester role then you must know about what is Data warehouse and how do we create data warehousing project in Oracle.
Some of the technical aspects to consider when asking an ETL testing interview question include:
- Find out 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 duplicates records from the table?
- Difference b/w union and union all?
- Difference b/w rank and dense_rank?
- Difference b/w ETL testing and DB testing?
- 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 Concepts (Informatica)
There are some common questions coming from the 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 definition 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?
- 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 lookup?
- Which transformation should we use to normalize the COBOL and relational sources?
- How the Informatica server sorts 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 source qualifier performs?
- What is the default join that 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?
- 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 center 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 Informatica warehouse or mapping
- What is Data cleansing?
- Explain the Informatica Architecture in detail
- At the max how many transformations can be us 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 to 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 are worklet and what use of worklet and in which situation we can 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 you have used? explain with example?
- Why sorter transformation is an active transformation?
- How is the union transformation active transformation?
- 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 all are the Client and Server components in Informatica?
- What is Metadata, where it will store 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 we can create ports in Informatica?
- How many ways we can control Transactions in Informatica?
- How many ways we can perform Data-Driven operations in Informatica?
- Which transformation we can use for data scrubbing?
- Which transformation we can use for data cleansing?
There are also some other ET concepts interview questions.
- Explain your project?
- Explain your project architecture?
- Explain your roles and responsibilities?
- How many tables you are 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 which you validated in your project?
- How you will validate data for Incremental and Initial load data?
- How you will validate data for History load and Full load?
- Which techniques do you follow while doing ETL testing?
- Tell me different test cases for the new table?
- How you will validate data for Enhancement work?
- How to validate data in the target table if the source table is present in a different database?
- How to validate data for Flat file to Table?
- How much amount data maintain in your project Warehouse?
- Tell me about your client’s details and explain their business?
- How to find out duplicate records in a table?
- Why do we need to perform Source minus Target and Target MinusSource?
- Explain any complex query which you have written while doing testing?
- What are Entry Criteria and Exit Criteria for your project?
- What all are your responsibilities in your project?
- How can you execute more test cases with one day span of time, on what basis you will pick test cases?
- What all are the different tools you used in your project?
- What is a data completeness check, 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?
FAQ about ETL Testing Interview Questions
Here are some of the frequently asked interview questions.
What is ETL in testing?
ETL stands for Extract, Transform, Load. It is a methodology that involves extracting data from various sources and then loading it into a database.
This helps in faster and more accurate testing as the process is automated.
What are ETL Tester’s responsibilities?
ETL Testers are responsible for testing the performance of an ETL system. They are also responsible for making sure that the data being transferred to the server is formatted in a specific way.
This includes ensuring that there is no extra or missing information in the data and that it has been checked for accuracy. The ETL tester will be responsible for identifying any problems with the data, and fixing them before they are sent to production.