Thursday, March 20, 2014

Basic ETL Testing Interview Questions and Answers - Part 2

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 :)

Data Warehouse QA
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 - 
No or records in input != No of records in output (like - filter, router, source qualifier)
Passive transformation-
No or records in input = No of records in output (like - Expression, look-up, stored procedure)


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
select empno, sal, ROW_NUMBER() OVER (order by sal desc) RN
from emp
order by sal desc
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


  1. Please post your questions and doubts here.


  2. Etl testing online training Etl testing uses in Three cases Production Validation Testing generally known as “table balancing” or “production reconciliation,”this type of ETL testing is completed on information because it is being enraptured into production systems.Source to focus on Testing,this type of ETL testing validates that values when a metamorphosis ar the expected data values. Application Upgrades. this type of ETL testing validates that coming back from associate older application or repository is strictly constant because the data within the new application or repository Etl testing online training ETL Testing online training.

  3. hderabadsysOnline training provides online training for the oracle adf, datastage, etl testing, sap crm, sap security ,web design. ETL testing online training nowadays etl testing is performed by SQL scripting or “eyeballing” of info on spreadsheets. etl testing online training .These approaches to ETL testing are extremely period-consuming, problem-susceptible, and seldom give total examination coverage. Informatica Data Validation Solution has an ETL assessment instrument that automate and may increase ETL testing online training assessment in both manufacturing conditions and growth & exam. This means that you are able to deliver auditable, repeatable and total examination protection in less time without any development skills required.
    INDIA +91 9030400777
    USA +1-347-606-2716

  4. It was so nice article.I was really satisified by seeing this article and we are also giving QA online training.The QA online training is one of the best QA online training institute in USA.

  5. brillant piece of information,this blog is fantastic...for more details

  6. Appreciation for nice Updates,nice blog article.for more details

  7. Very nice and informative blog.Nice piece of knowledge!For more details

  8. Nice piece of information. After reading this article I felt I am facing realtime interview. Thanks for sharing such informative QnA..

  9. Thanks for giving Information. We are providing Online Training Classes. Who wants search ETL Testing Online Training

  10. Very good collection of questions thank you for sharing this article. Know more about ETL Testing Training