ETL Testing Interview Questions & Answers - Part 4

Advanced ETL Testing Interview Questions & Answers 

Q1. How can you create BPT test scripts?

A1. Please follow the below steps to create BPT:

First you have to go Business Components section of ALM,

1. create a new component there
2. create component steps with parameters
3. put parameter variables in bracket like <<< input1 >>>
4. check it’s reflecting in parameters
5. add details for business component
6. Make status ready once all the steps are written.

Now go to the Test Plan in ALM,

1. Create a new test script; type will be Business Process for the same.
2. Put all the required details for script
3. In test script tab, you have to pull component now which you have created (you can pull more than one component as required and integrate them in particular test script)
4. After that you can see the parameters which you defined earlier for components here (suppose you have defined 4 parameters for the component then you can see 4 i/o parameters , iteration will be default 1 )
5. If you want to check the same parameters for different iterations then you can increase the iteration here
6. Now, BPT is ready (you can see green box)

Q2. How can you run BPT test scripts?

A2. Please follow the below steps to run BPT test scripts:

First go to Test Plan in ALM,

1. Go to test script tab for BPT test scripts, click on iterations
2. Default 1 iterations is mentioned there, after clicking this, you get the parameters list
3. Define the variable value which you want to pass for all the parameters
4. If you want to add one more iteration then, click on add iteration above
5. Now you can define parameter values for added iterations as well.
6. After defining all the values, we can pull this script in test lab

Now go to Test Lab in ALM,

1. Go to the particular BPT which you want to run, click on run with manual runner
2. Now all the defined parameter values will be substituted
3. You need to follow the steps now
4. check all the iterations are checked for mentioned steps and pass it
5. BPT will be passed.

Q3. How can we link the existing defects with particular test case?

A3. Go to particular test case in Test Lab in ALM, double click on it, windows will appear. Click on Linked Defects tab, then click on Link Existing Defects then we can put defect id and click on Link. That defect will be linked with that particular test case.

Q4. Let’s suppose for making table loading faster, index will be disabled as pre-load activity, and then duplicates got loaded. Again after loading, in pos load activity index will be enabled that time it will fetch error. How to resolve this issue?

A4. You can follow the below steps to mitigate this issue –

1. First we have to disable primary constraint
2. drop the index after that
3. Remove the duplicates using rowid
4. Now create the index on the table
5. At last we can enable the primary constraint

Q5.How can we pass the variable value through command box in teradata?

A5. We can use the below query for this –

SELECT COUNT(*)

FROM <TABLE_NAME>

WHERE <date_field> = CAST('?yyyymmdd' AS DATE FORMAT 'yyyymmdd')

Q6.How can we search particular job in control M?

A6.you can follow the below steps to find out the job in control m –

1. Login to control m
2. Click customizable filter in view point tab of control m
3. Filter based on folder name and server name ( we can give wild characters here *)
4. After filtering we can see all the instances for jobs coming under that folder
5. In particular folder, all the jobs coming under that will be shown

Q7. How can we check the roles assigned to particular tables/view in teradata?

A7. We can use dbc.allrolerights table to find out the roles assigned to table/view

Select * from dbc.allrolerights where tablename= <table_name>

We can see the fields for role names, access rights, and grantor names here.

Q8. In control m, how can we run the job instantly which is in waiting due to dependencies or schedule?

A8. Do right click on that particular job, free that job and click run now.

Q9. In control m, how can we order a new job which is not scheduled?

A9.Click order button on home tab, select control m server, folder, particular job (if all jobs selected then all the jobs for particular folder will be submitted to run). Also check boxes ignore scheduling criteria and order as independent flow.

Q10. How can we modify command for particular job in control m?

A10. Do right click on particular job, go to properties, click on modify job, now we can change the commands and other things like schedule for that job.

Q11. In Tivoli, some job is stuck due to dependencies and resource constraints then how can we run that job?

A11. Right click on job stream, increase the priority to 101, release all dependencies, and remove time restrictions, then job should run. If still it’s stuck then do the same thing at job level; it will definitely run.

Q12. In Tivoli, how can we submit the jobstream which are not scheduled?

A12. Go to default database jobstream , search that particular jobstream , you can submit that jobstream from there. It will be reflected in scheduled job stream.

Note : If jobstream is in draft mode, then we can't submit that job. Jobstream should be active then only we can submit to plan. 

Basic ETL Testing Interview Questions and Answers - Part 3

Basic Data Warehouse Testing Interview Questions and Answers - Part 3

Let's Continue discussing basic Conceptual ETL or Data Warehouse Interview Questions and answers...

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





Q43. How to delete one year older file in Unix?









A43. Find ./your_dir  -mtime +365  -type  f  -delete









Note : f-> file only, -mtime +365 -> 365 days older 


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



Q44.  What's the difference between delete and truncate command?



A44 : Difference are given below -



a) Delete is dml statement (which requires commit after that) while Truncate is ddl statement (which is autocommit) 





b) In Delete statement we can put conditions, so we can delete whatever required; while Truncate deletes all the data in tables ( we can't put conditions here)




c) Delete  activates a trigger as the individual row deletions are logged, while truncate never activates a trigger (no logs maintained) that's why rollback is possible in Delete but not in Truncate.




d) Truncate tables always locks the table while Delete Statement uses rowlock before deleting the row.




e)Truncate is faster than delete as it doesn't keep the logs. 




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




Q45.Write a query for the below -  

           To Find out the name of the employees whose salary is greater than their

           manager salary.



A45 Query-



Select EMP.name from Employee Emp, Employee Mgr

where EMP.managerid = Mgr.empid

and Emp.sal > Mgr.sal ;





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

Q46. What is BPT (Business Process testing) ?





A46. BPT stands for business process testing. It enables SMEs to design test script early in the development cycle. It consist of reusable business components which got converted into business process test.





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



Q47. How can we add partitions in Teradata? Please tell me the query.



A47. Query -



Alter table table_name

modify primary index

Add range between date '2014-04-01' and date '2015-06-01'  Each interval '1' Month;



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



Q48. What is multi-file system (MFS) ? How can we copy a mfs file?



A48. Multi-file system(MFS) is a file which is divided into different files . Using MFS file we can process a big input file in efficient manner as files are divided. So, we can process the divided files parallel and make the processing faster.



We can use the below commands to copy MFS file -



m_cp file1 file2



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



Q48. You worked on Informatica then tell me the difference between FILTER  and ROUTER ?



A48. There are many differences between FILTER and Router, which are given below -



 a) Filter transformation has a single i/p and single o/p while Router has single i/p and multiple o/p

 b) Router is like Case statement in database while filter is like where clause in database

 c) Router transformation doesn't block any row i.e no. of rows equal in i/p and o/p while filter block rows i.e no. of rows in o/p <= no. of rows in i/p



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



Q49. In Abinitio graph , how can we check DML and XFR for the transformation?



A49. Right Click on Transformation Properties -> go to file layout -> check path for dml

        (or embedded DML)



       Right Click on Transformation Properties -> check transformation logic -> check path for       XFR(or embedded logic)



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





Q50. Are you aware of IBM Tivoli Scheduler..How can get the Job stream in plan  ? How can we execute that job stream even though dependencies are there?





A50. IBM TIVOLI is used to schedule the job streams which calls the wrapper script/graph to perform the job.

Go to database of job stream and then submit the particular job stream from there; it will appear in the Plan. We can increase the priority to high(101) and release all the dependencies to run that stream.



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



Q51. What do you understand by full load and incremental load? 



A52. Full load is basically dumping of entire data from source table to target table . Every time when full load is happening then we are truncating the table and loading the entire data again.

      

        Incremental load is basically loading of delta data from source to target table in

        regular interval so that source and target can be synchronized. while applying the delta 

        into target table, Normally we do capture data change (CDC) i.e what is getting inserted,

        updated or deleted.





   

********************************************************************************************************