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

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 - 

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

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

Basic ETL Testing Interview Questions - Part 1

Hi Guys, Hope you all are doing well. I have come up with my new post on ETL / Data Warehouse testing questions which I came across during my interview with various companies. I believe that this blog will be helpful to experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. I am not posting the answer here, if required I will post it later. Please let me know if you like the post, so you can see more post on ETL.. 

Keep Sharing,Keep Learning guys :)
21 Basic ETL Testing Interview Questions and Answers:


1. What's the different between OLTP and OLAP system? Please provide one example for both.

================================================================================

2. What's the advantage with Snowflake compared to Star Schema?

================================================================================

3. What is dimensional modelling?

================================================================================

4.Let's suppose you are working on a tech refresh project i.e. (from VB code to .net code). Which type of testing you will be doing in this project ??

================================================================================

5.Why OLAP system are not normalized while OLTP are highly normalized system?

================================================================================

6. Which SCD type is used to store all historical data along with current data?

================================================================================
 
7.What's the difference between active and passive transformation?

================================================================================

8. How ETL testing is different from conventional testing?

================================================================================

9. What do you understand by ETL ? Why we need ETL ?

================================================================================

10. What is the difference between data mart and data warehouse?

================================================================================

11. What are the different architectures of data warehouse systems?

================================================================================

12. Let's suppose in a test scenario one column is added in a table. What kind of test cases you will write?

================================================================================

13. If a requirement keeps changing, then how will you test?




14. What do you understand by business process testing ?

================================================================================

15. Which are the important steps carried out in ETL after extracting the data from source system and before transforming the data??

================================================================================

16. What do you understand by fact and dimension table?

================================================================================


17. What do you understand by slowly changing dimensions?

================================================================================

18. How would you validate that junk data is not loaded into the target table ?


================================================================================

19. How do you check the logs if any workflow (Informatica) failed while executing?  


================================================================================

20. Have you worked on Multi file systems? How ab-initio graph process those files??


================================================================================

21. 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 properly? Tell me the test scenario and test cases you will write to test this requirement. ??

                                COPYRIGHT ©  2013 TECH BRAINS

101 UNIX INTERVIEW QUESTIONS

Hi Guys, I am writing this new post to put all interview questions which I came across during my interview with various companies. I believe that this post will be helpful to freshers as well as experience candidate in interview. These interview questions are mostly straight forward. I will come up with more tricky questions later. Please post your comments and suggestions ,which will make learning better for all. Some questions are having answer and some are not in below list of questions .You can post queries and doubts related with questions below, so I can answer them.Please let me know if you like the post, so you can see more post on Unix. 

Keep Sharing,Keep Learning guys :)

101 UNIX INTERVIEW QUESTIONS AND ANSWERS -

1.      How do you change ownership of a file?    chown

2.      How do you change the ownership of the files recursively?  Chown -R

3.      How do you change access of a file? Chmod 777 filename

4.      How do you remove duplicate entries from a file? Uniq filename

5.      How do you restrict the file size to a particular value? Ulimit size

6.      How to truncate a file (say you want only last 20 line of a file) tail –20 filename

7.      What are wild characters? These are the characters which gives special meaning to the shell. Ex: *, ?, ^ etc.

8.      What is sleep? What does it do?
     If you want some process to deactivate for a while then we can use sleep. It deactivates the process for the specified seconds.

9.      Regular expression to fit the US  phone number of format 999-999?

10.  How do you view the .a files? Dump –H filename.a

11.  What is a library? What is the extension  of a library file? 
      Library is a directory file where functions provided by unix for programmers are stored. Extension is .a.

12.  How to get information from a library file? Dump –H filename.a | grep

13.  What is the command used to output to both the console and the file?  Tee or tee with filename

14.  How to count the no.of files in the current directory? Wc

15.  Replace the blank characters with ~(tilde) in a file? Sed ‘s/ /~/’ filename

16.  How to print the contents of a file? Cat filename.

17.  How to list the process of a particular user?(And everything about ps)?  Ps –u <username>

18.  What is a pipe? What are named pipes?

Pipe concatenates two commands where the output of the first command acts as the input to the second command and so on.

Name pipes create inter process communication. It is a special file.

Named pipes are similar to pipes where two main files will be there as fields[0] and fields[1] which contains stream of files where the input one file destination stream act as the reading and the other file stream act as write.

19.  How to mail a file to a user? Mail username

20.  How to read the telephone number from a file(say the no. is in the format ### - ####)?

21.  How to execute a process with lower priority? Nice (options –n)                                                           

22.  How do you see the process consuming more CPU time? Top ps

23.What these operator  $, $$, # , | , ? , > , <, >> [], ` ,$?, $*    does??

24.  What is an awk?

25.  How to start a background process? What happens when you exit a shell before the process completes? By using &. It runs.

26.  Which is the first process that is started in Unix? Inittab

27.  How to get the values of control characters(say ctrl-c)?

28.  How to change the date and time of a file? Touch filename

29.  What is a semaphore?       
      A semaphore is a non-negative integer count and is generally used to co-ordinate access to resources.  The initial semaphore count is set to the number of free resources, then threads slowly increment and decrement the count as resources are added and removed. If the semaphore count drops to zero, which means no available resources, threads attempting to decrement the semaphore will block until the count is greater than zero.

30.  How do you remove the semaphores and shared memory? Semdestroy

31.  What are .b files? Batch files.

32.  How do you take a process already started as a foreground process to background? Give cntrl Z bg

33.  What is daemon? How do you write a daemon program? Daemon is a thread running all the time. It started only once. We can write by giving nohups.

34.  What is a cron daemon? Job scheduler

35.  How do you list the semaphores and shared memory?
      Semaphores are listed with ls –s. Shared memories are listed with ls –m

36.  How to connect to another terminal? rlogin or telnet.

37.  Difference between http and https?'
       http is a non secured protocol. https is secured protocol.

38.  What is SSL? Secured socket layer.

39.  What is the standard port no of HTTP? 80

40.  What is the standard port no of HTTPS?  443

41.  What are plugins? 
       Additional features to a browser/ server. You can call your own c++ code here.

42.  What is CGI? Common Gateway Interface.

43.  What is $?  ?
       ‘$?’ returns code from the previous executed command.

44.  Everything about sed and awk.

45.  All deamons, specially cron. How will you schedule your command using cron deamon? 
      Store the command in commandfile(cmdfile) .  crontab cmdfile.

46.  What are the first 3 fields in the cron tab file? Time (i.e. minutes hour dayof the month month of the year day of the week)

47.  What will cat<file1>file2 do give another command for it? 
      Copies the content of file1 to file2.            Cp file1 file2

48.  How do you check if the process running is taking a long time? Top

49.  What are background and foreground processes?
     Background processes are the one which runs even after exiting from the shell. Foreground processes are interactive.

50.  How do you choose a column from a file? Cut –c                                                                                                                                                                                                   

51.  What are the different types of shell? Bourne shell, Korn shell, C-shell, Bash-shell

52.  How do you inverse the occurrence of a regular expression? Grep –v

53.  How would you swap fields in a file with ‘ ‘(blank) as a delimeter? Awk f ‘{print $3 $2 }’ filename

54.  How do you compress a file? What is the syntax? What does pack –f do? 
      By giving the command called compress. Compress filename.

55.  What is nohup? What is syntad? Where does the output go, if you do not mention the output filename Nohup command. EX:- nohup sort emp.     If we are not redirecting the output of our background proces then the result is stored in nohup.out

56.  What is mail? How do you automate to send a mail after the end of a process? 
      Mail is a way to circulate memos and other written information to  your co-workers if you and they use networked computers.

57.  What is the internal value associated with the standard error device?  2

58.  How do you execute a command on a different machine without loging on to it? rlogin

59.  What does this meand: ${variable-value}?

60.  What does this meand: ${variable? Value}?

61.  How will you archive a file?  Tar cvf

62.  Which signal is sent to the kernel, when you kill a process?  Terminate.

63.  What is the difference between TCP/IP and OSI model?

OSI contains 7 layers

TCP/IP has 5 layers

64.  what is ARP (Address resolution protocol)?

65.  How do you find out which version of UNIX you are working with?  Uname –v

66.  What are environment variables? $HOME, $PATH                                                                   

67. What is the significance of $HOME and $PATH?

       Searches in all the specified set PATH for the given thing 

Takes to the root directory where you are logged in

68.  How do you display the environment variables? Env

69.  How will you know what version of a program is being picked up from among the paths described in the $PATH variable? 

70.  What is crontab? 
    It is a command which is used to carry out jobs everyday for years together without needing any prompting for us. This will excel over at and batch.

71.  What is inittab?   
      This file is  called when we start the machine which in turn calls the necessary processes or files.

72.  What is memory leakage?  
      Not releasing the memory or acquiring extra memory.

73.  How do you replace the occurrence of a string with  some other value using sed? 
      Sed ‘s/string/value/’ filename.

74.  What would you do if you want zero mapped to a and one mapped to b in a string? Tr[ab][01]

75.  What is archiving? Compressing

76.  What are signals?  Interrupts

77.  What are the five basic IPCs?

        Interprocess communications

        Pipe

        Fifo

78.  What is shared segment memory?

79.  What is  a web server?  Where browser requests can be met.

80.  How will you determine the path of travel between two hosts in a n/w?  tracert

81.  What are the seven layers of  TCP/IP?

82.  What is a mac address?

83.  Given a table containing several columns, write a shell program to sum up 2^nd and 3^rd column data.

84.  What is the difference between TCP/IP & UDP sockets? Write the series of steps showing the lifecycle of a socket.

85.  Which Shell your working and how do you find  your in which Shell?

86.  How do you see the process  consuming more CPU time?

87.  How do you find out which version of UNIX you are working with?

88.  How do you find out the Average Disk used by the all the files in the current directory?

89.  Difference between thread and process

90.  How to find process-> CPU consumption on SMP environment(multi processor environment)

91.  List various options of netstat

92.  How to find swap space consumption for each process

93.  Difference between vmstat and top

94.  Why threads are listed as a separate processes,  when we do ps –el

95.  What are the different types of shells that you have used?

96.  Where the major configuration files stored in UNIX ?

97.  Explain some of the shell scripts that you have written?

98.  Explain sed command?

99.  What is the regular expression for a number of length 15 digits?

100.  What is the regular expression for a String of length 15 which does not have digits.

101.    How to get no of records and no of fields in awk ?

                                            
                                                                COPYRIGHT © 2013 TECH BRAINS