ETL Testing Interview Questions Part -1

Typical first round interview questions:

Give me details about your QA back ground

What is fact table and dimension table

What validation you do when data passes from source to target

What is slowly changing dimension

What are the different kinds of SCDs (slowly changing dimensions) we have. Which one did you follow in your project?

List of questions previously asked our hires:

They will be asking about schemas (star and snowflake)

They will ask about what volume of data you are used to working with

They will ask how you validate from source to target

The will be testing your knowledge of SQL

They will be asking your opinion about relational databases vs. Hadoop

They will ask about the functions related the relational databases and then how they would change in a Hadoop environment

They might do a resume walk through or ask about specific roles you have had

What kind of data have you worked with?

What kind of method she is conducting the data validation

(1) Can you write SQL query to retrieve 2nd latest coverage from claim table, containing member name from member table?

SELECT DISTINCT COVERAGE
  FROM (SELECT COVERAGE, DENSE_RANK () OVER (ORDER BY COVERAGE DESC) RNK FROM CLAIM C, MEMBER M WHERE C.MEMBER_NAME=M.MEMBER_NAME)
     WHERE RNK = 2;

(2) Difference between Rownum(), Denserank() and rank() functions?

The ROW_NUMBER() function always generates a unique ranking even with duplicate records.

The RANK function will give the non-sequential ranking.

The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.
i.e DENSE_RANK function will give the sequential ranking.

SQL> WITH emp_sal
  2       AS (SELECT 101 id, 5000 AS salary FROM DUAL
  3           UNION ALL
  4           SELECT 102 id, 4000 AS salary FROM DUAL
  5           UNION ALL
  6           SELECT 103 id, 4000 AS salary FROM DUAL
  7           UNION ALL
  8           SELECT 104 id, 3000 AS salary FROM DUAL
  9           UNION ALL
 10           SELECT 105 id, 2000 AS salary FROM DUAL)
 11  SELECT id,
 12         salary,
 13         ROW_NUMBER () OVER (ORDER BY salary DESC) AS rnm,
 14         DENSE_RANK () OVER (ORDER BY salary DESC) AS d_rnk,
 15         RANK () OVER (ORDER BY salary DESC) AS rnk
 16    FROM emp_sal;

        ID     SALARY        RNM      D_RNK        RNK
---------- ---------- ---------- ---------- ----------
       101       5000          1          1          1
       102       4000          2          2          2
       103       4000          3          2          2
       104       3000          4          3          4
       105       2000          5          4          5

SQL>

(3) Real time example of Informatica DDO?

Data Validation Option for PowerCenter (DVO) is used for Data Validation Option, automatic test generation, reporting, and trouble-shooting.

(4) Did you use IBM WebSphere Datastage?

Yes/No.
Datastage is an ETL tool which extracts data, transform and load data from source to the target.
The data sources might include sequential files,relational databases, external data sources.
Datastage is an interface between different systems. It takes care of extraction, translation, and loading of data from source to the target destination.

(5) How did you execute jobs in Abnitio?

(6) How do you delete duplicate records from a table?

we can remove duplicates using rowid.
Below query is used to delete duplicates from EMP_COPY table based duplicate column i.e sal
DELETE FROM EMP_COPY WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM EMP_COPY GROUP BY SAL);
(or)
DELETE FROM EMP_COPY A WHERE ROWID <> (SELECT MAX(ROWID) FROM EMP_COPY B WHERE A.SAL=B.SAL);

(7) Data is coming from multiple source files, and data is loaded from files to tables? What is the validation steps to validate data between file and table?

we can create a temp table and import file data into it .
will perform validations b/w temp (source and actual table (target) -- count,duplicate,data validation

(8) how do you count number of records in a file in unix?

wc -l filename

Example:
oracle@ODIGettingStarted ~]$ cat student.txt
101|Raj|Bangalore
102|Siva|Hyderabad
103|Ram|Chennai
[oracle@ODIGettingStarted ~]$ wc -l student.txt
3 student.txt
[oracle@ODIGettingStarted ~]$


(9) How to check width of a record in a file in unix?

awk '{ print (length($0))}' student.txt

Example:
[oracle@ODIGettingStarted ~]$ cat student.txt
101|Raj|Bangalore
102|Siva|Hyderabad
103|Ram|Chennai
[oracle@ODIGettingStarted ~]$ awk '{ print (length($0))}' student.txt
17
18
15
[oracle@ODIGettingStarted ~]$ awk '{ print (length($0)); exit;}' student.txt
17
[oracle@ODIGettingStarted ~]$

(10) Do you have experience in code deployment?

No. But am aware of the deployment process.
Once developers finish coding for a project, they need to place or move that code to stage box for QA. It is called code deployment.
It can include code that fixes bugs, adds new features, or upgrades the underlying platform.
Code deployment flow: DEV -> STAGE -> UAT -> PROD

(11) Share your experience in Agile?

Agile methodology is a practice that promotes continuous iteration of development and testing throughout the software development lifecycle of the project.
Both development and testing activities are concurrent unlike the Waterfall mode.
Agile focuses on untangling the complications of Software development and break it down to simple, clean and smaller user stories.

(12) Give an example of Left outer join?

Suppose, we want to join two tables A and B. SQL left outer join returns all rows in the left table (A) and all the matching rows found in the right table (B).
It means the result of the SQL left join always contains the rows in the left table.

(13) Give an example of complex SQL you have written for Data validation?

SCD Type - 2 data validation