Top 20 ETL Testing Interview Questions & Answers
The following are frequently asked ETL Testing Interview Questions & Answers (Data Warehousing Interview Questions & Answers) for freshers as well as experienced ETL tester and developer.
Data Warehousing Testing Interview Questions
Let’s get started with this ETL Testing Interview Questions.
What is ETL?
ETL stands for Extract Transform and Load. Extract, Transform, & Load are the three database functions that are combined into one tool.
Extract:Â It is the process of reading data from a database
Transform:Â It is the process of converting the extracted data from its original form into the form it needs to be stored into another database.
Load:Â It is the process of loading the data into the target database
In other words, the ETL process produces data from multiple sources to make a Data Mart, Operational Data Store, Data Warehouse, Data Hub, or Data Lake.
What is ETL Testing?
ETL testing validates the data movement from source to destination and verifying the data count in both source and destination and verifying data extraction, transformation, and also verifying the table relations.
What is Data Warehousing?
Collecting, storing, and managing various kinds of data from ‘n’ no. of different sources and providing meaningful perceptions and prediction to the business is called Data Warehousing.
What is Database Testing?
It is AKA back-end testing or data testing.
Database testing involves in verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves in updating records in a database and verifying the same on the front end.
Database testing includes the following:
- Data validity testing
- Data integrity testing
- Database performance testing
- Testing of procedures, triggers, and functions
Mention a few differences between ETL & Database Testing?
Here are some differences between ETL Testing & Database Testing.Â
| ETL Testing | DB Testing |
|---|---|
| ETL testing is more focused on Data movement from multiple sources to data warehouse. | DB testing is focused on database rules and constraints testing of an applications database. |
| Verify data count in source and targeted databases. | Verify every column in the table has valid data values. |
| Testing is done to verify that data extraction, transformation and loading has done as expected. | It is done to check if application is performing CRUD operations correctly, and business needs are met. |
| Works on huge data, historical data and multiple sources are involved. | It is done on business transactional data. |
| In ETL, online analytical processing (OLAP) is used which execute complex queries to analyse historical aggregated data from OLTP systems. | DB uses Online transaction processing (OLTP), which works on real time database operations like creating, processing and storing of data on transactional basis. |
| Data type is not normalized, with more Indexes and aggregations, with less joins. | In DB testing data is normalized with more joins. |
| ETL testing is involved in data extraction, transformation, data load and finally verification of reports on the dashboard. | Database testing is to verify application logic is working correct, data operations are working correctly. |
| ETL testing is multidimensional testing. | DB testing is limited to business and application logic. |
Mention some ETL bugs?
- UI/Interface related bugs like – Usability, spelling mistakes, navigation, placement of UI objects, font style, font size, colors, alignment, etc.
- Issues related to Boundary value analysis – minimum and maximum value check.
- Valid and invalid data type issues
- Calculation bugs
- Performance or speed related issues.
- Business rule violation related issues.
- Record duplication bugs.
- Loss of data in some operation
Mention some ETL test cases?
- The source and target table structure should match with the ETL mapping sheet.
- Data type and data length at source and target should be the same.
- The Data field type format should match on both source and target tables.
- Column names should map with the ETL sheets on both source and target
- Constraints are defined as expected on the target as defined on the source.
- The record count is the same on source and target after data load happens.
- The date format is the same on the source and target tables.
- Check no duplicate records are found on target tables.
What is the ETL mapping document?
An ETL mapping document is the most important document to design and develop ETL jobs. It contains the source, target, and business rules information. It helps in writing the SQL queries for the ETL tools testing.
What is a Data Staging Area (DSA)?
A data staging area (DSA) or lading zone is a temporary intermediate storage area between the data sources and a data warehouse. It is used for data processing during the extract, transform, and load (ETL) process.
What are ETL tester responsibilities?
ETL testers are responsible for the following
- Validating the data sources, extraction of data, applying transformation logic, and loading the data in the destination.
- In-depth knowledge of ETL Tools and ETL processes.
- In-depth knowledge in writing the SQL queries.
What are the various tools used in ETL?
- AWS Glue
- Alooma
- Stitch
- Fivetran
- Xplenty
- Matillion
- Streamsets
- Talend
- Informatica
- Oracle Data Integrator
- Etleap
Check detailed post on the list of ETL Software Tools
What is BI?
BI(Business Intelligence) is the process of collecting raw data and transforming it into meaningful information that will be helpful for business.
What is the difference between data mining and data warehousing?
Data mining can be defined as the process of extracting the hidden information from the large databases and interpreting it for future forecasting.
Whereas data warehousing may make us of a data mine for analytical processing of the data to generate detailed reports in a faster way.
What are the differences between ETL tools and BI tools?
The difference between ETL and BI tools is that
ETL Tools: Used for extraction of the data from the legacy systems, transform the data, and load it into the specified database with some process of cleansing data.
Examples: Informatica, Data Stage, etc.
BI Tools: Used to collect raw data and transforming it into interactive reports for end-users to help businesses.
Examples: Informatica, Microsoft SSIS, SAP BO data service, Oracle Data Integrator (ODI) Clover ETL Open Source, etc.Â
What are the types of Data Warehouse systems?
- Online Analytical Processing (OLAP)
- Predictive Analysis
- Online Transactional Processing
- Data Mart
What are the differences between OLTP and OLAP?
OLTP (Online Transactional Processing) is a relational database and is used to manage the day to day transactions.
OLAP (Online Analytical Processing) is a multi-dimensional analytical (MDA) system that provides the capability for complex calculations, data discovery.
What is the difference between ETL tools and OLAP tools?
The difference between ETL and OLAP tool is that
ETL Tools: ETL Tools are meant for the extraction of the data from the legacy systems, transform the data, and load it into the specified database with some process of cleansing data.
Examples: Informatica, Data Stage, etc.
OLAP Tools: OLAP (Online Analytics Processing) tools are used for reporting purposes in OLAP data available in the multidirectional model.
Examples: Cognos, Business Objects, etc.
What are the ETL bugs?
- UI/Interface related bugs like – Usability, spelling mistakes, navigation, placement of UI objects, font style, font size, colors, alignment, etc.
- Issues related to Boundary value analysis – minimum and maximum value check.
- Valid and invalid data type issues
- Calculation bugs
- Performance or speed related issues.
- Business rule violation related issues.
- Record duplication bugs.
- Loss of data in some operation.
Hope we have listed ETL Testing interview questions and answers which are more frequently asked in the ETL testing interviews.
Related posts:
