Hi Guys, this post is based on some basic ETL testing questions which every ETL tester will know if he has worked on it. I believe that this post will be helpful to experience candidate in interview.These interview questions are somewhat related with real time tasks and issues faced by ETL QA during the day to day work. I have also written down answers for them, so it will be very informative for you. Please post your comments and suggestions ,which will make learning better for all. Please let me know if you like the post, so you can see more post on ETL!
Keep Sharing,Keep Learning guys :)
 |
| ETL TESTING INTERVIEW |
21 Basic Data Warehouse Testing Interview Questions and Answers:
Q1. Which basic tasks primarily done by ETL tester?
A1. A ETL Tester primarily test source data extraction, business transformation logic and target table loading . There are so many tasks involved for doing the same , which are given below -
1. Stage table / SFS or MFS file created from source upstream system - below checks come under this :
a) Record count Check
b) Reconcile records with source data
c) No Junk data loaded
d) Key or Mandatory Field not missing
e) duplicate data not there
f) Data type and size check
2) Business transformation logic applied - below checks come under this :
a) Business data check like telephone no cant be more than 10 digit or character data
b) Record count check for active and passing transformation logic applied
c) Derived Field from the source data is proper
d) Check Data flow from stage to intermediate table
e) Surrogate key generation check if any
3. Target table loading from stage file or table after applying transformation - below check come under this
a) Record count check from intermediate table or file to target table
b) Mandatory or key field data not missing or Null
c) Aggregate or derived value loaded in Fact table
d) Check view created based on target table
e) Truncate and load table check
f) CDC applied on incremental load table
g) dimension table check & history table check
h) Business rule validation on loaded table
i) Check reports based on loaded fact and dimension table
========================================================================
Q2. Generally how enevironemt variables and paths are set in Unix?
A2. dot(.) profile , normally while logging this will be executed or we can execute as dot(.) dot(.)profile
========================================================================
3. If a column is added into a table, tell me the test cases you will write for this?
A3. Following test cases you can write for this -
1. Check that particular column data type and size is as per the data model.
2. Check data is getting loaded into that column as per the DEM (data element mapping)
3. Check the valid values , null check and boundary value check for that column
========================================================================
Q4.Let's suppose you are working on a project where requirement keeps changing. How would you tackle this?
A4. If the requirement is getting changed frequently then we need to lot of regression for the same functionality which has been tested. Then you need to be ready with all your input test data and expected result, so after checking changed part , you can run all the test cases and check the results in no time.
========================================================================
Q5. How do you modify your test data while doing the testing?
A5. If input test data is ASCII file, then you can easliy prepare it in notepad+ based on the interface and then ftp it to unix server and if it's table then you can insert the rows into table as per the data model. If file other than ascii format then we can use abinitio graph to covert excel sheet into required format or use other tools are available for doing the same.
========================================================================
Q6. A table has partitions by range on data_dt, suppose it has already defined monthly partitions PTN_01 (values less than (TO_DATE ('01-Feb-2014' , 'dd-mon-yyyy' ))) for january 2014 data only and we are trying to load data for feb 2014 then what will happen? If you find any error then how to solve the same.
A6. It will fetch error - “Inserted partition key does not map to any partition” (ORA -14400) . It means parition is not there for feb data which we are trying to load, so add a new partition in the table for feb month data as below :
Alter table table_name add partition partition_name values less than (TO_DATE ('01-MAR-2014' , 'dd-mon-yyyy' ))
Note : Remember we can only create new partition for higher value than the previous created partition (it means here we can't add partition for dec 2013 as we have higher value is feb 2014 here.
========================================================================
Q7. How will you connect oracle database from unix server?
A7. sqlplus username/password@dbserver
========================================================================
Q8. If one of the Oracle procedure fetches error - “No data found” then what is the issue here?
A8. In that procedure definitely we are retrieving the data from table and passing it to a variable. If that select statement is not fetching any row then we will get this error.
========================================================================
Q9. If one of your wrapper unix script is fetching the error - “not enough memory” then what you will do?
A9. First we can check the disk usage by command df -h , then we can clean up accordingly and run the script again.
========================================================================
Q10. let's suppose we have to two tables, item (Primary Key : item id)and order (Primary Key order id, Foreign Key : item id) . If we try to delete items from order table then will we able to delete? If not then how can we do that?
A10. If we make an attempt to delete or truncate a table with unique or primary keys referenced by foreign keys enabled in another table then we get error : “ORA-02266 unique/primary keys in table referenced by enabled foreign keys”
So, before deleting or truncating the table, disable the foreign key constraints in other tables or delete the data from foreign table item first then from the primary table order here.
========================================================================
Q11. Why do we create index on a table? Please explain
A11. In nutshell I can say - for faster retrieval of data we use indexes, let's suppose I created a table order which will contain billions of data and I know - most of the time I will be querying this table using order id then I should make index on Order table for faster result.
========================================================================
Q12. What will be the default permission of a file created in UNIX? How can we provide all access to all?
A12. When a file is created, the permission flags are set according to the file mode creation mask, which can be set using the "umask" command. If umask value is set as 002 then it means file permission is 664. (-rw-rw-r--). we can chnage the permission of file as below :
chmod 777 filename (4: read , 2: write , 1: execute)
========================================================================
Q13. How we can link a defect with a test script in QC?
A13. First We should fail the test case step in test lab , then we can click on new defect (red color symbol) then enter the defect details there and raise it. Then that defect is linked with that particular step of test case. One more thing as we mention issues in actual result that will come in defect description automatically (no need to put issue details again)
========================================================================
Q14. What are the different methods to load table from files in Oracle? Also tell me methods for teradata.
A14. SQL Loader, External table loading, loading through driver JDBC . In teradata we use multiload or fastload usually.
========================================================================
Q15. What are the things you will check before you start testing? What will be your deliverables?
A15. Before starting the testing, requirement document, functional spec . Technical spec , interface, dem and unit test result should be availble atleast. My deliverables will be – test plan, test spec, test script, defect summary with root causal analysis, test execution or result report and automation script (if created).
========================================================================
Q16. What do you understand by active and passive transformation in informatica?
A16. Active transformation -
========================================================================
Q17. Let's suppose we are having order table which are having duplicate order_id. How we can delete the duplicate rows from the table? Tell atleast two methods you know.
A17. First we can do the below :
create table order_new as select distinct * from order ;
drop table order ;
rename order_new to order ;
Note : This method is faster , but we need to recreate index, partitions, constraints....
Second method
delete from order a where rowid > (select min(rowid) from order b where a.order_id = b.order_id);
Note : here we deleting the duplicate rows based on rowid which is uniquely assigned to each row by Oracle.
========================================================================
Q18. How you will find the second highest salary from the employee table? Tell me two methods atleast.
A18. First method – we can use sub query to find this as below :
select max(sal)
from emp
where sal not in (select max(sal) from emp ) ;
Note : first we fond the hoghest salary here then next highest will be the second salary only
Second method – we can use row_number for the same as below :
SELECT empno, sal
FROM
(
select empno, sal, ROW_NUMBER() OVER (order by sal desc) RN
from emp
order by sal desc
)
WHERE RN = 2;
========================================================================
Q19. How we can find out the last two modified file for a particular file mask abc* in unix?
A19. We can do this using very simple command : ls -lrt abc* | tail -2
Note: To check the last command was successful or not – we can use echo $?
========================================================================
20. How you will delete the last line of a file? Tell atleast two methods
A20 first , we can do using Sed as : Sed -i '$d' file_name
second method -
cp file_main file_bkp
sed '$d' file_bkp > file_main
rm -f file_bkp
Note : In direction > operator is used to move all the contents of file into another, >> used to append the one file data into another file.
========================================================================
Q21. Let's suppose we are migrating the data from legacy file system to oracle database tables. How would you validate that the data is migrated propely? Tell me the imp. test scenario and test cases you will write to test this requirement and how will you test that scenario?
A21 . Following scenario can be written for the same:
1) Check that all table DDL as per data model (desc table name )
2) Check that all records are loaded from source to target (match record count)
3) Check null value or junk data is not loaded into table ( check record count by putting not null conditions)
4) Check valid values for columns ( based on group by find all the counts for particular values for a field)
5) Check same data is loaded ( put a join between source table (if there) stage table and taget table and check the result)
6) Check key fields ( add number fields for target and source and match them)
7) Check business logics (create a sheet with input and output)
8) After initial loading, check incremental loading ( insert/ delete /update check all the CDC cases)
9) check the output file layout (if any)
COPYRIGHT © 2014 TECH BRAINS
