8/25/16

ETL TESTING REAL TIME INTERVIEW QUESTIONS & ANSWERS

1.      What is Data warehouse
Ans: A Data warehouse is a subject oriented, integrated ,time variant, non volatile collection of data in support of management's decision making process.
Subject oriented : means that the data addresses a specific subject such as sales, inventory etc.
Integrated : means that the data is obtained from a variety of sources.
Time variant : implies that the data is stored in such a way that when some data is changed.
Non volatile : implies that data is never removed. i.e., historical data is also kept.


2.      What is the difference between database and data warehouse
Ans: A database is a collection of related data. Where as Data Warehouse stores historical data, the business users take their decisions based on historical data only.

3.      What is the difference between dimensional table and fact table
Ans: A dimension table consists of tuples of attributes of the dimension. A fact table can be thought of as having tuples, one per a recorded fact. This fact contains some measured or observed variables and identifies them with pointers to dimension tables.

4.      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). 


5.      What is Data Mart
Ans : A data mart is a simple form of a data warehouse that is focused on a single subject (or functional area), such as Sales, Finance, or Marketing. Data marts are often built and controlled by a single department within an organization. Given their single-subject focus, data marts usually draw data from only a few sources. The sources could be internal operational systems, a central data warehouse, or external data.

6.      Difference between OLTP and OLAP
Ans: Online transactional processing (OLTP) is designed to efficiently process high volumes of transactions, instantly recording business events (such as a sales invoice payment) and reflecting changes as they occur.
Online analytical processing (OLAP) is designed for analysis and decision support, allowing exploration of often hidden relationships in large amounts of data by providing unlimited views of multiple relationships at any cross-section of defined business dimensions.

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

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

9.      What are ETL tester responsibilities
Ans :An ETL tester is responsible for writing SQL queries for various scenarios. They run a number of tests including primary key, duplicate, default, and attribute tests of the process. In addition, they are in charge of running record count checks as well as reconciling records with source data. They also confirm the quality of the data and the loading process overall.


10.   What are the Key benefits of ETL Testing
Ans: Minimise the risk of Data loss
Data Security
Data Accuracy
Reporting effciency

11.   To get the list of tables and views in Database
Ans : SELECT * FROM information_schema.tables (will display both tables,views)
SELECT * FROM information_schema.views (will display on views)

12.   List the details about “SMITH”
Ans: Select * from employee where last_name=’SMITH’;

13.   List out the employees who are working in department 20
Ans: Select * from employee where department_id=20

14.   List out the employees who are earning salary between 3000 and 4500
Ans: Select * from employee where salary between 3000 and 4500

15.   List out the employees who are working in department 10 or 20
Ans: Select * from employee where department_id in (20,30)

16.   Find out the employees who are not working in department 10 or 30
Ans :Select last_name, salary, commission, department_id from employee where department_id not in (10,30)

17.   List out the employees whose name starts with “S”
Ans:  Select * from employee where last_name like ‘S%’

18.   List out the employees whose name start with “S” and end with “H”
Ans:  Select * from employee where last_name like ‘S%H’

19.   List out the employees whose name length is 4 and start with “S”
Ans : Select * from employee where last_name like ‘S___’

20.   List out the employees who are working in department 10 and draw the salaries more than 3500
Ans:  Select * from employee where department_id=10 and salary>3500

21.   List out the employees who are not receiving commission.
Ans:  Select * from employee where commission is Null

22.   List out the employee id, last name in ascending order based on the employee id.
Ans:  Select employee_id, last_name from employee order by employee_id
23.   List out the employee id, name in descending order based on salary column
Ans : Select employee_id, last_name, salary from employee order by salary desc
24.   list out the employee details according to their last_name in ascending order and salaries in descending order
Ans:  Select employee_id, last_name, salary from employee order by last_name, salary desc

25.   list out the employee details according to their last_name in ascending order and then on department_id in descending order.
Ans:  Select employee_id, last_name, salary from employee order by last_name, department_id desc

26.   How many employees who are working in different departments wise in the organization
Ans : Select department_id, count(*), from employee group by department_id

27.   List out the department wise maximum salary, minimum salary, average salary of the employees
Ans:  Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id

28.   List out the job wise maximum salary, minimum salary, average salaries of the employees.
Ans:  Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id

29.   List out the no.of employees joined in every month in ascending order.
Ans:  Select to_char(hire_date,’month’)month, count(*) from employee group by to_char(hire_date,’month’) order by month

30.   List out the no.of employees for each month and year, in the ascending order based on the year, month.
Ans:  Select to_char(hire_date,’yyyy’) Year, to_char(hire_date,’mon’) Month, count(*) “No. of employees” from employee group by to_char(hire_date,’yyyy’), to_char(hire_date,’mon’).