ETL Testing Interview Questions

 Q.What is ETL? Ans:  ETL - extract, transform, and load. Extracting data from outside source systems.Transforming raw data to make it fit for use by different departments. Loading transformed data into target systems like data mart or data warehouse. Q.Why ETL testing is required? Ans: • To verify the correctness of data transformation against the signed off business requirements and rules. • To verify that expected data is loaded into data mart or data warehouse without loss of any data. • To validate the accuracy of reconciliation reports (if any e.g. in case of comparison of report of transactions made via bank ATM – ATM report vs. Bank Account Report). • To make sure complete process meet performance and scalability requirements • Data security is also sometimes part of ETL testing • To evaluate the reporting efficiency Q.What is Data warehouse? Ans:Data warehouse is a database used for reporting and data analysis. Q. What are the characteristics of a Data Warehouse? Ans: Subject Oriented, Integrated, Time-variant and Non-volatile Q. What is the difference between Data Mining and Data Warehousing? Ans. Data mining - analyzing data from different perspectives and concluding it into useful decision making information. It can be used to increase revenue, cost cutting, increase productivity or improve any business process. There are lot of tools available in market for various industries to do data mining. Basically, it is all about finding correlations or patterns in large relational databases. Data warehousing comes before data mining. It is the process of compiling and organizing data into one database from various source systems where as data mining is the process of extracting meaningful data from that database (data warehouse). Q.What Is The Difference Between Etl Tool And Olap Tools? Answer : 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 Q.Can We Lookup A Table From Source Qualifier Transformation. Ie. Unconnected Lookup? Answer : 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. Q.What Is Ods (operation Data Source)? Answer :

  • 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.

Q.Where Do We Use Connected And Unconnected Lookups? Answer :

  • 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 .

Q. What are the main stages of Business Intelligence? Ans: Data Sourcing –> Data Analysis –> Situation Awareness –> Risk Assessment –> Decision Support Q. What tools you have used for ETL testing? Ans. 1. Data access tools e.g., TOAD, WinSQL, AQT etc. (used to analyze content of tables) 2. ETL Tools e.g. Informatica, DataStage 3. Test management tool e.g. Test Director, Quality Center etc. ( to maintain requirements, test cases, defects and traceability matrix) Benefits of ETL Testing Production Reconciliation IT Developer Productivity Data Integrity Q.What is a Data Warehouse? Ans: A Data Warehouse is a collection of data marts representing historical data from different operational data source (OLTP). The data from these OLTP are structured and optimized for querying and data analysis in a Data Warehouse. Q.What is a Data mart? Ans: 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 are sometimes also called as HPQS (Higher Performance Query Structure). Q.What is OLAP? Ans: OLAP stands for Online Analytical Processing. It uses database tables (Fact and Dimension tables) to enable multidimensional viewing, analysis and querying of large amount of data. Q.What is OLTP? Ans: OLTP stands for Online Transaction Processing Except data warehouse databases the other databases are OLTPs. These OLTP uses normalized schema structure. These OLTP databases are designed for recording the daily operations and transactions of a business. Q.What are Dimensions? Ans:  Dimensions are categories by which summarized data can be viewed. For example a profit Fact table can be viewed by a time dimension. Q.Give Some Etl Tool Functionalities? Answer : While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it's not a must. When you evaluate ETL tools, it pays to look for the following characteristics:

  • Functional capability:This includes both the 'transformation' piece and the 'cleansing' piece. In general, the typical ETL tools are either geared towards having strong transformation capabilities or having strong cleansing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleansing capabilities. If you know there are going to be a lot of different data transformations, it then makes sense to pick a tool that is strong in transformation.
  • Ability to read directly from your data source:For each organization, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.
  • Metadata support:The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that works with your overall metadata strategy.

contact for more on Etl Testing Online Training