Syntel interview - ETL Developer

Syntel Interview

Date - 14/04/2018
Post - ETL Developer(Informatica)

There were 2 rounds of interview.
L1  and L2 followed by HR duscussion  .

L1 round - Few of the technical Questions asked -
1. Draw informatica architecture and explain the same
2. Transaction Control -explain with example
3. Can we cantrol the way target are loaded in mapping and how --> Question was regarding target load plan option present in informatica.
4.what are nodes, Grid  and why do we use with greed ?
5. 2 sources present say A and B having employeeid as common attribute . Scenario is to obtain below 3 results -
Target1 - records matching in both of them.
Target2 - all those records which are present in A and not present in B
Target3 - all those records which are present in B and not present in A
6. Write a shell script which will below file validations -
1.Check the file name
2.Check the file extension
3.check the file delimiter
4.check the file header
Send a mail if any of above fails.

7. Few questions on grep and cut comands


L2  was more of mangerial round.
Basic questions. Not much technical.

Hope these questions help you to be prepared. Do let me know if u need answers to these questions.

😊😊

Viewics INC (A ROCHE COMPANY) Interview

Interview date - 28/04/2018
Post - ETL Developer
Located - Besides Rahul theatre,Shivajinagar




Appeared for a technical test -
1 hour test.
Paper comprised of questions from informatica scenarios, SQl analytical queries, Unix shell scripting and DWH concepts.
Sharing below the questions asked. Will soon post the answers to them.

Informatica Scenario Questions-

1.Design a mapping to create 2 target table - one with unique and other with duplicates.
Input     Target1     Target2
A                 A             C
B                 B              C
C                 D              C
C                                  E
D                                  E
E
E

2. Design a mapping to get salary of next employee in currrent record. If no next employee present set next Salary as NULl.

Input                                      Output
id  Salary                  Id  Salary Next_Salary
10 10000.                    10  10000  20000
20  20000.                   20  20000  30000
30  30000.                   30  30000   NULL

3. Design a mapping to generate a fibonaccu series. Source as id from 1-2000 .

Source
1
2
3
.
.
.
2000

Target
1
2
3
5
8
13
.
.

4. Design a mapping for below-
There arr only 3 Product type . But we dont know how many product id as available for each product type. Create a mapping so that output as only 3 records from audio 3 from video and 3 from movie. In case if any of the product type is less than 3 then  either of the other product type can be sent to output . Output must have only 9 records.


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


5 Design a mapping for below

Input

id     val
1      a,b,c
2      pq,m,n
3      asz,ro,liqt

Output

id     val
1      a
1      b
1      c
2      pq
2      m
2      n
3      asz
3      ro
3      liqt

Shell scripting Questions-
1. How to list all the files names present  in a folder to a file.
2.How to search which filenames has a particular string or pattern.
3.What is nohup in unix
4.file contains name age salary
How fo find the sorted distinct names ans ita count from that file
5. How to get 10-20 th charactet from a file .


DWH concepts questions -
1. what are junk dimensions
2.What are degenerate dimensions
3.Difference between star and snowflake schema.


Hope it helps everyone.😊😊

Target Update Override - Ways to Update the target without any primary key defined

When we have to perform update on the target table there are 2 ways to perform it :-

1. Using a UPDATE STRATEGY transformation(DD_UPDATE)

2. Configuring Session and defining properties to "Treat all rows as UPDATE"

But both these approach works only when there is a Primary Key defined in the target table. 

The session will fail when there will not be any key defined in the target.

Alternatively, if you want to perform TARGET updates,  with putting condition on COLUMNs not included as PRIMARY KEY in target table, it will not be feasible with out changing the Keys on target table. i.e. If PRIMARY KEY is defined on COLUMN_A in TABLE_A and you want to Update TABLE_A with conditions on or including COLUMN_B

In such cases the UPDATE_OVERRIDE property of TARGET Object can be useful.

• Where to loacte UPDATE_OVERRIDE property?

1. Go to Mapping

2. Double Clink on the concerned TARGET or edit the Target

3. Click on Properties Tab

4. The second Transformation Attribute is the property we are looking for

• Syntax for UPDATE_OVERIDE SQL:

UDATE      <TARGET TABLE>
SET            <COLUMN NAME to be updated> = :TU.<TARGET COLUMN PORT NAME (As in Designer)>   

                   , [Other columns need to be updated]
WHERE      <COLUMN NAME to be treated as KEY > = :TU.<corresponding TARGET COLUMN PORT NAME (As in Designer)>
AND          [other conditions]

• Example: consider u want to update the post and date in employee table based on the name of the employee which is not the primary key in the target.

UPDATE    EMPLOYEE

SET            POST = :TU.POST

                , UPDATE_DATE = :TU.UPDATE_DATE

WHERE EMPLOYEE_NAME = :TU.EMPL_NAME

Following is an example showing Problem statement and step by step Instructions, of two approaches, of suggested solution.

Problem Scenario:

1. Target table has not defined Primary key

  

1. Informatica target object has no Keys - Since the Database table has not Keys the Imported Object in Informatica will not have any Key in the Target definition

2. Original Mapping :  The mapping created using this very Target definition

3. Defining update logic: configuring session for UPDATE Logic 

4. Configure session for Treat All Source Row as "UPDATE"

5. Configure in the session target property to UPDATE as UPDATE - optional

6. The error you will get while execution - the session will fail

Solution:

Approach 1: Using Update in the Target

1. Go to properties tab of Target in Mapping:

2. Edit the "Update Override" attribute - generate SQL and add where Clause with the column name on which you want to update:

3. Please note that you need to remove the very column, used in WHERE clause from the SET Clause list 

4. Go to Workflow and refresh changes in the mapping and save them 

5. Execute the Workflow 

6. Now it has successfully updated the rows:

Approach 2: Forcing Keys in the Target object definition in designer

1. Edit the Target in the target designer

2. Define your Primary key column. Please note that in this case the Database need not have keys defined on the same table:

3. Save the changes 

4. Execute the workflow 

5. The workflow will update the records in the target table: