Skip to main content

Informatica scenario based interview questions



1. How to generate sequence numbers using expression transformation?

2. We have a target source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1 Col2 Col3
-----------------
a b c

There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col
-----
a
b
c

3. . Design a mapping to load the first 3 rows from a flat file into a target?

4. There is a source table that contains duplicate rows.Design a mapping to load all the unique rows in 1 target while all the duplicate rows (only 1 occurence) in another target.

5. Design a mapping to load the last 3 rows from a flat file into a target?

6. There is a source table containing 2 columns Col1 and Col2 with data as follows:

Col1 Col2
a l
b p
a m
a n
b q
x y

Design a mapping to load a target table with following values from the above mentioned source:

Col1 Col2
a l,m,n
b p,q
x y



7. Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

8. Consider the following products data which contain duplicate rows.
A
B
C
C
B
D
B

Q1. Design a mapping to load all unique products in one table and the duplicate rows in another table.
The first table should contain the following output
A
D
The second target should contain the following output
B
B
B
C
C



9. Design an Informatica mapping to load first half records to 1 target while other half records to a separate target.



10. Scenario: A source table contains emp_name and salary columns. Develop an Informatica mapping to load all records with 5th highest salary into the target table.

11. . Design a mapping to load each product once into one table and the remaining products which are duplicated into another table.
The first table should contain the following output
A
B
C
D
The second table should contain the following output
B
B
C



12. Consider the following employees data as source

employee_id, salary
10, 1000
20, 2000
30, 3000
40, 5000


Q1. Design a mapping to load the cumulative sum of salaries of employees into target table?
The target table data should look like as

employee_id, salary, cumulative_sum
10, 1000, 1000
20, 2000, 3000
30, 3000, 6000
40, 5000, 11000



Q2. Design a mapping to get the pervious row salary for the current row. If there is no pervious row exists for the current row, then the pervious row salary should be displayed as null.
The output should look like as

employee_id, salary, pre_row_salary
10, 1000, Null
20, 2000, 1000
30, 3000, 2000
40, 5000, 3000




13. Consider the following employees table as source

department_no, employee_name
20, R
10, A
10, D
20, P
10, B
10, C
20, Q
20, S



Q1. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, A,B,C,D,P
20, A,B,C,D,P,Q
20, A,B,C,D,P,Q,R
20, A,B,C,D,P,Q,R,S



Q2. Design a mapping to load a target table with the following values from the above source?

department_no, employee_list
10, A
10, A,B
10, A,B,C
10, A,B,C,D
20, P
20, P,Q
20, P,Q,R
20, P,Q,R,S



Q3. Design a mapping to load a target table with the following values from the above source?

department_no, employee_names
10, A,B,C,D
20, P,Q,R,S



14. Consider the following product types data as the source.


Product_id, product_type
10, video
10, Audio
20, Audio
30, Audio
40, Audio
50, Audio
10, Movie
20, Movie
30, Movie
40, Movie
50, Movie
60, Movie

Assume that there are only 3 product types are available in the source. The source contains 12 records and you dont know how many products are available in each product type.


Q1. Design a mapping to select 9 products in such a way that 3 products should be selected from video, 3 products should be selected from Audio and the remaining 3 products should be selected from Movie.



Q2. Design a mapping to convert column data into row data without using the normalizer transformation.
The source data looks like

col1, col2, col3
a, b, c
d, e, f

The target table data should look like

Col
a
b
c
d
e
f



Q3. Design a mapping to convert row data into column data.
The source data looks like

id, value
10, a
10, b
10, c
20, d
20, e
20, f

The target table data should look like

id, col1, col2, col3
10, a, b, c
20, d, e, f




17. Remove footer from your file

How to remove footer from your file ?


For example the file content looks like as below:-


some Header here
col1 col2 col3 col4
data1 data2 data3 data4
data5 data6 data7 data8
data1 data2 data3 data4
data1 data2 data3 data4
footer

Just we have to remove footer from the file.


18. Remove header from your file

  How to remove header from a file ?


19. Segregating rows on group count basis
There are 4 departments in Emp table. The first one with 100,2nd with 5, 3rd with 30 and 4th dept has 12 employees. Extract those dept numbers which has more than 5 employees in it, to a target table.


20. Sending first half record to target
How to send first half record to target?


21. Extracting every nth row
How to load every nth row from a Flat file/ relational DB to the target? Suppose n=3, then in above condition the row numbered 3,6,9,12,....so on, This example takes every 3 row to target table.


22. Sending second half record to target

 How to send second half record to target?


23. Sending records to target tables in cyclic order
There is a source table and 3 destination table T1,T2, T3. How to insert first 1 to 10 record in T1, records from 11 to 20 in T2 and 21 to 30 in T3.Then again from 31 to 40 into T1, 41 to 50 in T2 and 51 to 60 in T3 and so on i.e in cyclic order.


24. Sending alternate record to target
 How to send alternate record to target?
 Or

Sending Odd numbered records to one target and even numbered records to another target.


25. Concatenation of duplicate value by comma separation

Scenario: You have two columns in source table T1, in which the col2 may contain duplicate values.All the duplicate values in col2 of will be transformed as comma separated in the column col2 of target table T2.




Comments

  1. Thanks for sharing wounderfull information and if u had any queries means please visit oue website so that we can approach easily http://suntrainings.com/informatica.html

    ReplyDelete
  2. very well defined stuff thanks for sharing.

    ReplyDelete

Post a Comment

Popular posts from this blog

Contact Me

Do You have any queries ?                   If you are having any query or wishing to get any type of help related Datawarehouse, OBIEE, OBIA, OAC then please e-email on below. I will reply to your email within 24 hrs. If I didn’t reply to you within 24 Hrs., Please be patience, I must be busy in some work. kashif7222@gmail.com

Top 130 SQL Interview Questions And Answers

1. Display the dept information from department table.   Select   *   from   dept; 2. Display the details of all employees   Select * from emp; 3. Display the name and job for all employees    Select ename ,job from emp; 4. Display name and salary for all employees.   Select ename   , sal   from emp;   5. Display employee number and total salary   for each employee. Select empno, sal+comm from emp; 6. Display employee name and annual salary for all employees.   Select empno,empname,12*sal+nvl(comm,0) annualsal from emp; 7. Display the names of all employees who are working in department number 10   Select ename from emp where deptno=10; 8. Display the names of all employees working as   clerks and drawing a salary more than 3000   Select ename from emp where job=’clerk’and sal>3000; 9. Display employee number and names for employees who earn commission   Select empno,ename from emp where comm is not null and comm>0. 10

Informatica sample project

Informatica sample project - 1 CareFirst – Blue Cross Blue Shield, Maryland (April 2009 – Current) Senior ETL Developer/Lead Model Office DWH Implementation (April 2009 – Current) CareFirst Blue Cross Blue Shield is one of the leading health care insurance provided in Atlantic region of United States covering Maryland, Delaware and Washington DC. Model Office project was built to create data warehouse for multiple subject areas including Members, Claims, and Revenue etc. The project was to provide data into EDM and to third party vendor (Verisk) to develop cubes based on data provided into EDM. I was responsible for analyzing source systems data, designing and developing ETL mappings. I was also responsible for coordinating testing with analysts and users. Responsibilities: ·          Interacted with Data Modelers and Business Analysts to understand the requirements and the impact of the ETL on the business. ·          Understood the requirement and develope