Top 20 ETL Testing interview Questions & Answers in 2020 - Upskill with MAGNITIA

1. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

2. 1.What is ETL Testing? In data warehousing architecture, ETL is an important component, which manages the data for any business process. ETL stands for Extract, Transform and Load. Extract does the process of reading data from a database. Transform does the converting of data into a format that could be appropriate for reporting and analysis. While, load does the process of writing the data into the target database. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

3. 2.Explain Trigger testing? Whether the required coding conventions have been followed during the coding phase of the Triggers. Check whether the triggers executed for the respective DML transactions have fulfilled the required conditions. Whether the trigger updates the data correctly once they have been executed. Validation of the required Update/Insert/Delete triggers functionality in the realm of the application under test. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

4. 3.What is difference between ETL Testing and Database Testing? www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

5. 4.What is the three-layer architecture of an ETL cycle? The three layers in the ETL are: Staging Layer: Staging layer is used to store the data which is extracted from the different data source systems. Data Integration Layer: Integration layer transforms the data from the staging layer and moves the data to a database. In the database, the data is arranged into hierarchical groups, which is often called dimension, and into facts and aggregation facts. The combination of facts and dimension table in a data warehouse system is called a schema. Access Layer: Access layer is used by the end-users to retrieve the data for analytical reporting. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

6. 5.What is a mapping, Session, Worklet, and Mapplet? Mapping: Mapping represents workflow from source to target. Workflow: Workflow is a set of instructions which tells the Informatics server how to execute the tasks. Mapplet: Mapplet configures or creates a set of transformation. Worklet: It is an object that represents a set of tasks. Session:Session is a set of instructions that describe how and when to move the data from sources to target. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

7. 6.Who needs Data warehouse? Data warehouse is needed for all types of users like: Decision makers who rely on mass amount of data Users who use customized, complex processes to obtain information from multiple data sources. It is also used by the people who want simple technology to access the data It also essential for those people who want a systematic approach for making decisions. If the user wants fast performance on a huge amount of data which is a necessity for reports, grids or charts, then Data warehouse proves useful. Data warehouse is a first step If you want to discover 'hidden patterns' of data-flows and groupings. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

8. 7.Types of ETL Testing? Production Validation Testing Source to Target Testing (Validation Testing Application Upgrades Metadata Testing Data Completeness Testing Data Accuracy Testing Data Transformation Testing Data Quality Testing Incremental ETL testing GUI/Navigation Testing www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

9. 8.Explain what are Cubes and OLAP Cubes? Cubes are data processing units comprised of fact tables and dimensions from the data warehouse. It provides multi-dimensional analysis. OLAP stands for Online Analytics Processing, and OLAP cube stores large data in multidimensional form for reporting purposes. It consists of facts called as measures categorized by dimensions. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

10. 9.What are your responsibilities as an ETL Tester? 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 key 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. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

11. 10.Explain what is partitioning, hash partitioning and round-robin partitioning? To improve performance, transactions are subdivided, this is called as Partitioning. Partitioning enables Informatics Server for creating multiple connections to various sources The types of partitions are:- Round-Robin Partitioning: By Informatics data is distributed evenly among all partitions. In each partition where the number of rows to process is approximately same, this partitioning is applicable Hash Partitioning: For the purpose of partitioning keys to group data among partitions, Informatics 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 www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

12. 11.What are the various tools of ETL Testing? Oracle Warehouse Builder Cognos Decision Stream Business Objects XI SAS Business warehouse SAS Enterprise ETL Server www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

13. 12.What are data modelling and data mining? Data Modelling is a technique used to define and analyse the requirements of data that supports an organization’s business process. In simple terms, it is used for the analysis of data objects to identify the relationships among these data objects in any business. Data Mining is a technique used to analysed datasets to derive useful insights/information. It is mainly used in retail, consumer goods, telecommunication and financial organizations that have a strong consumer orientation to determine the impact on sales, customer satisfaction, and profitability. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

14. 13.Unconnected Vs Connected Lookups? www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

15. 14.What is a Data mart? A Data Mart is a subset of a data warehouse that can provide data for reporting and analysis on a section, unit or a department like Sales Dept, HR Dept, etc. The Data Mart is sometimes also called as HPQS (Higher Performance Query Structure). [Social locker] www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

16. 15.What are the types of Facts? The types of Facts are as follows. Additive Facts: A Fact which can be summed up for any of the dimension available in the fact table. Semi-Additive Facts: A Fact which can be summed up to a few dimensions and not for all dimensions available in the fact table. Non-Additive Fact: A Fact which cannot be summed up for any of the dimensions available in the fact table. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

17. 17.Explain what is Transformation in ETL testing? Transformation is defined as the repository objects to generate, modify or pass the data. There may be Active Transformation and Passive Transformation. It may be beneficial in different ways: It helps in getting values from tables quickly. It has the capability to update slowly changing dimension tables. It checks or verifies either record exists or not inside the table. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

18. 18.ETL Testing Process? www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

19. 19.What is ETL Pipeline & Data Pipeline? ETL Pipeline refers to a set of processes to extract the data from one system, transform it, and load it into some database or data warehouse. ETL pipelines are built for data warehousing applications, which includes both enterprise data warehouse as well as subject-specific data marts. ETL pipelines are also used for data migration solutions. Data warehouse/ business intelligence engineers build ETL pipelines. Data Pipeline refers to any set of processes elements that move data from one system to another. Data Pipeline can be built for any kind of application which uses data to bring the value. It can be used for integrating the data across the applications, build the data-driven web products and carrying out the data mining activities. Data engineers build the data pipeline. www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

20. 20.What are the differences between data warehousing and data mining? www.magnitia.com | info@magnitia.com |+91 6309 16 16 16

21. Thank you MAGNITIA IT 304 & 305, Paradigm SSS Square, Kothaguda X Roads, Opp to AMB Cinemas, Hyderabad - 500 081. +91 6309 16 16 16 www.magnitia.com FOLLOW US /magnitiaonline /magnitiaonline /magnitia /magnitia /magnitia www.magnitia.com | info@magnitia.com |+91 6309 16 16 16