ETL Testing Interview Questions for freshers and experienced :-
1. What Is Etl?
ETL stands for extraction, transformation and loading.
ETL provide developers with an interface for designing source-to-target mappings, transformation and job control parameter.
Extraction :
Take data from an external source and move it to the warehouse pre-processor database.
Transformation:
Transform data task allows point-to-point generating, modifying and transforming data.
Loading:
Load data task adds records to a database table in a warehouse.
2. What Is The Difference Between Etl Tool And Olap Tools?
ETL tool is meant for extraction data from the legacy systems and load into specified database with some process of cleansing data.
ex: Informatica, data stage ….etc
OLAP is meant for Reporting purpose in OLAP data available in Multidirectional model. so that you can write simple query to extract data from the data base.
ex: Business objects, Cognos….etc
3. Can We Lookup A Table From Source Qualifier Transformation. Ie. Unconnected Lookup?
You cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.
4. What Is Ods (operation Data Source)?
- ODS – Operational Data Store.
- ODS Comes between staging area & Data Warehouse. The data is ODS will be at the low level of granularity.
- Once data was populated in ODS aggregated data will be loaded into EDW through ODS.
5. Where Do We Use Connected And Unconnected Lookups?
If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.
If you require dynamic cache i.e where your data will change dynamically then you can go for connected lookup. If your data is static where your data won’t change when the session loads you can go for unconnected lookups .
6. Where Do We Use Semi And Non Additive Facts?
Additive: A measure can participate arithmetic calculations using all or any dimensions.
Ex: Sales profit
Semi additive: A measure can participate arithmetic calculations using some dimensions.
Ex: Sales amount
Non Additive: A measure can’t participate arithmetic calculations using dimensions.
Ex: temperature
7. What Are Non-additive Facts In Detail?
- A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.
- Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.
- A non additive fact, for eg; measure height(s) for ‘citizens by geographical location’ , when we rollup ‘city’ data to ‘state’ level data we should not add heights of the citizens rather we may want to use it to derive ‘count’.
8. what is Grain of Fact?
Grain fact can be defined as the level at which the fact information is stored. It is also known as Fact Granularity
9. what factless fact schema is and what is Measures?
A fact table without measures is known as Factless fact table. It can view the number of occurring events. For example, it is used to record an event such as employee count in a company.
The numeric data based on columns in a fact table is known as Measures
10. What is transformation?
A transformation is a repository object which generates, modifies or passes data. Transformation are of two types Active and Passive
11. Explain the use of Lookup Transformation?
The Lookup Transformation is useful for
- Getting a related value from a table using a column value
- Update slowly changing dimension table
- Verify whether records already exist in the table
12. What is partitioning, hash partitioning and round robin partitioning?
To improve performance, transactions are sub divided, this is called as Partitioning. Partioning enables Informatica Server for creationg of multiple connection 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 are approximately same this partioning is applicable
Hash Partitioning:
- For the purpose of partitioning keys to group data among partitions Informatica server applies a hash function
- It is used when ensuring the processes groups of rows with the same partitioning key in the same partition need to be ensured
13. Mention what is the advantage of using DataReader Destination Adapter?
The advantage of using the DataReader Destination Adapter is that it populates an ADO recordset (consist of records and columns) in memory and exposes the data from the DataFlow task by implementing the DataReader interface, so that other application can consume the data.
14. Using SSIS ( SQL Server Integration Service) what are the possible ways to update table?
To update table using SSIS the possible ways are:
- Use a SQL command
- Use a staging table
- Use Cache
- Use the Script Task
- Use full database name for updating if MSSQL is used
15. In case you have non-OLEDB (Object Linking and Embedding Database) source for the lookup what would you do?
In case if you have non-OLEBD source for the lookup then you have to use Cache to load data and use it as source
16. In what case do you use dynamic cache and static cache in connected and unconnected transformations?
Dynamic cache is used when you have to update master table and slowly changing dimensions (SCD) type 1
For flat files Static cache is used
17. Name the three approaches that can be followed for system integration.
The three approaches are − top-down, bottom-up, and hybrid.
18. What are the different ETL Testing categories as per their function?
ETL testing can be divided into the following categories based on their function −
- Source to Target Count Testing − It involves matching of count of records in source and target system.
- Source to Target Data Testing − It involves data validation between source and target system. It also involves data integration and threshold value check and Duplicate data check in target system.
- Data Mapping or Transformation Testing − It confirms the mapping of objects in source and target system. It also involves checking functionality of data in target system.
- End-User Testing − It involves generating reports for end users to verify if data in reports are as per expectation. It involves finding deviation in reports and cross check the data in target system for report validation.
- Retesting − It involves fixing the bugs and defects in data in target system and running the reports again for data validation.
- System Integration Testing − It involves testing all the individual systems, and later combine the result to find if there is any deviation.
19. Explain the key challenges that you face while performing ETL Testing.
- Data loss during the ETL process.
- Incorrect, incomplete or duplicate data.
- DW system contains historical data so data volume is too large and really complex to perform ETL testing in target system.
- ETL testers are normally not provided with access to see job schedules in ETL tool. They hardly have access on BI Reporting tools to see final layout of reports and data inside the reports.
- Tough to generate and build test cases as data volume is too high and complex.
- ETL testers normally doesn’t have an idea of end user report requirements and business flow of the information.
- ETL testing involves various complex SQL concepts for data validation in target system.
- Sometimes testers are not provided with source to target mapping information.
- Unstable testing environment results delay in development and testing the process.
20. What are your responsibilities as an ETL Tester?
The key responsibilities of an ETL tester include −
- Verifying the tables in the source system − Count check, Data type check, keys are not missing, duplicate data.
- Applying the transformation logic before loading the data: Data threshold validation, surrogate ky check, etc.
- Data Loading from the Staging area to the target system: Aggregate values and calculated measures, key fields are not missing, Count Check in target table, BI report validation, etc.
- Testing of ETL tool and its components, Test cases − Create, design and execute test plans, test cases, Test ETL tool and its function, Test DW system, etc.
21. What do you understand by the term ‘transformation’?
A transformation is a set of rules which generates, modifies, or passes data. Transformation can be of two types − Active and Passive.
22. What do you understand by Active and Passive Transformations?
In an active transformation, the number of rows that is created as output can be changed once a transformation has occurred. This does not happen during a passive transformation. The information passes through the same number given to it as input.
23. What is Partitioning? Explain different types of partitioning.
Partitioning is when you divide the area of data store in parts. It is normally done to improve the performance of transactions.
If your DW system is huge in size, it will take time to locate the data. Partitioning of storage space allows you to find and analyze the data easier and faster.
Parting can be of two types − round-robin partitioning and Hash partitioning.
24. What is the difference between round-robin partitioning and Hash partitioning?
In round-robin partitioning, data is evenly distributed among all the partitions so the number of rows in each partition is relatively same. Hash partitioning is when the server uses a hash function in order to create partition keys to group the data.
25. Explain the terms − mapplet, session, mapping, workflow − in an ETL process?
- A Mapplet defines the Transformation rules.
- Sessions are defined to instruct the data when it is moved from source to target system.
- A Workflow is a set of instructions that instructs the server on task execution.
- Mapping is the movement of data from the source to the destination.
26. What is lookup transformation and when is it used?
Lookup transformation allows you to access data from relational tables which are not defined in mapping documents. It allows you to update slowly changing dimension tables to determine whether the records already exist in the target or not.
27. What is a surrogate key in a database?
A Surrogate key is something having sequence-generated numbers with no meaning, and just to identify the row uniquely. It is not visible to users or application. It is also called as Candidate key.
28. What is the difference between surrogate key and primary key?
A Surrogate key has sequence-generated numbers with no meaning. It is meant to identify the rows uniquely.
A Primary key is used to identify the rows uniquely. It is visible to users and can be changed as per requirement.
29. If there are thousands of records in the source system, how do you ensure that all the records are loaded to the target in a timely manner?
In such cases, you can apply the checksum method. You can start by checking the number of records in the source and the target systems. Select the sums and compare the information.
30. What do you understand by Threshold value validation Testing? Explain with an example.
In this testing, a tester validates the range of data. All the threshold values in the target system are to be checked to ensure they are as per the expected result.
Example − Age attribute shouldn’t have a value greater than 100. In Date column DD/MM/YY, month field shouldn’t have a value greater than 12.
31. Write an SQL statement to perform Duplicate Data check Testing.
Select Cust_Id, Cust_NAME, Quantity, COUNT (*)
FROM Customer GROUP BY Cust_Id, Cust_NAME, Quantity HAVING COUNT (*) >1;
32. How does duplicate data appear in a target system?
When no primary key is defined, then duplicate values may appear.
Data duplication may also arise due to incorrect mapping, and manual errors while transferring data from source to target system.
33. What is Regression testing?
Regression testing is when we make changes to data transformation and aggregation rules to add a new functionality and help the tester to find new errors. The bugs that appear in data which comes in Regression testing are called Regression.
34. What are the common ETL Testing scenarios?
The most common ETL testing scenarios are −
- Structure validation
- Validating Mapping document
- Validate Constraints
- Data Consistency check
- Data Completeness Validation
- Data Correctness Validation
- Data Transform validation
- Data Quality Validation
- Null Validation
- Duplicate Validation
- Date Validation check
- Full Data Validation using minus query
- Other Test Scenarios
- Data Cleaning
35. What is data purging?
Data purging is a process of deleting data from a data warehouse. It removes junk data like rows with null values or extra spaces.
36. What do you understand by a cosmetic bug in ETL testing?
Cosmetic bug is related to the GUI of an application. It can be related to font style, font size, colors, alignment, spelling mistakes, navigation, etc.
37. What do you call the testing bug that comes while performing threshold validation testing?
It is called Boundary Value Analysis related bug.
38. I have 50 records in my source system but I want to load only 5 records to the target for each run. How can I achieve this?
You can do it by creating a mapping variable and a filtered transformation. You might need to generate a sequence in order to have the specifically sorted record you require.
39. Name a few checks that can be performed to achieve ETL Testing Data accuracy.
Value comparison − It involves comparing the data in the source and the target systems with minimum or no transformation. It can be done using various ETL Testing tools such as Source Qualifier Transformation in Informatica.
Critical data columns can be checked by comparing distinct values in source and target systems.
40. Which SQL statements can be used to perform Data completeness validation?
You can use Minus and Intersect statements to perform data completeness validation. When you perform source minus target and target minus source and the minus query returns a value, then it is a sign of mismatching rows.
If the minus query returns a value and the count intersect is less than the source count or the target table, then duplicate rows exist.
55. What is the difference between shortcut and reusable transformation?
Shortcut Transformation is a reference to an object that is available in a shared folder. These references are commonly used for various sources and targets which are to be shared between different projects or environments.
In the Repository Manager, a shortcut is created by assigning ‘Shared’ status. Later, objects can be dragged from this folder to another folder. This process allows a single point of control for the object and multiple projects do not have all import sources and targets into their local folders.
Reusable Transformation is local to a folder. Example − Reusable sequence generator for allocating warehouse Customer ids. It is useful to load customer details from multiple source systems and allocating unique ids to each new source-key.
56. What is Self-Join?
When you join a single table to itself, it is called Self-Join.
58. What do you understand by Normalization?
- Database normalization is the process of organizing the attributes and tables of a relational database to minimize data redundancy.
- Normalization involves decomposing a table into less redundant (and smaller) tables but without losing information.
59. What do you understand by fact-less fact table?
A fact-less fact table is a fact table that does not have any measures. It is essentially an intersection of dimensions. There are two types of fact-less tables: One is for capturing an event, and the other is for describing conditions.
60. What is a slowly changing dimension and what are its types?
Slowly Changing Dimensions refer to the changing value of an attribute over time. SCDs are of three types − Type 1, Type 2, and Type 3.