DELTALAKE AZURE CLOUD DATABRICS -PYSPARK SNOWFLAKE

Informatica Interview questions

1) How will you access a flat file which is not in Informatica server location?

A:- USING FTP

2) Does informatica recognizes Excel data (Ext .XLS not .csv ) as flat file or Table and how can we fetch excel data?

A: - As a table...we select data in excel and create range for that. and create odbc connection for that range and we import that range

We select the data in excel and create a NAME for that" that name is treated as Table by Informatica.

3) What is remote file name and what is its significance in flat file loading.

A:- Remote file name: When we define a ftp connection for any flat file source we need to edit the FTP connection to add remote file name otherwise session will be Invalid. Remote file name is like flat file name only. In the ftp connection also we need to mention Flatfile path and name.

4) What is the easy way of debugging a flat file which have say 1000 records and problem is around 413 records.

A: - Debugging 413 rows: In the session flat file properties (Advanced section) mention number of rows to skip as 412. So that when you run Debugger it reads from 413 rows.

5) Is look up active or passive and how? (From 9.1 onwards it is active!!!)

A:- In Informatica 9.1 Lookup is active because of property - "Lookup policy on multiple match", this can be chosen as first, last, any value or all values. So now we can configure the Lookup transformation to return all rows that match a lookup condition. Hence, a Lookup transformation is an active transformation when it can return more than one row for any given input row.

Guidelines for Returning Multiple Rows:

Follow the below guidelines when you configure the lookup transformation to return multiple rows:

· You can cache all the rows from the lookup source for cached lookups.

· You can customize the SQL Override for both cached and uncache lookup that return multiple rows.

· You cannot use dynamic cache for Lookup transformation that returns multiple rows.

· You cannot return multiple rows from an unconnected Lookup transformation.

· You can configure multiple Lookup transformations to share a named cache if the Lookup transformations have matching caching lookup on multiple match policies.

· Lookup transformation that returns multiple rows cannot share a cache with a Lookup transformation that returns one matching row for each input row.

However this property is available only for connected lookup, unconnected lookups are still passive.

6) I have created a reusable sequence generator can i demote it to Non- Reusable? What is the significance of CYCLE property in sequence generator...if i disabled that what is the problem?"

A: - CYCLE:-

If disabled, the Integration Service stops the sequence at the configured end value. The Integration Service fails the session with overflow errors if it reaches the end value and still has rows to process.

7) We have 3 pipelines each one contains 10 rows out of which 3 rows from each of the pipelines have same values. Can we remove duplicates and load unique set using Union transformation? what is the process?

Ans:- UNION: Union transformation does not remove duplicate rows.

1) Yes, Union t/f does not remove duplicates,

To remove duplicate rows, you must add another transformation sorter but i can suggest A

Aggregator with group by (columns as per ur requirment )

2) Yes ,we cannot use sequence generator or update strategy transformation upstream for union transformation,The reason behind is :

We can not use Sequence generator before Union transformation because

union transformation used to combined two sets of same attributes data into one set. If you use Sequence transformation for two sets of data before Union then the output of union will give duplicate values and if you are trying load this attribute values in target as primary key it will reject data.

Update Strategy: Union transformation used to change row type. If any update Strategy transformation used before Union and any record flagged as Update or Reject, that record type may change in Union transformation.

8) How many ports can i make as rank and is it possible to fetch top 4th to 13 th ranked employees based on condition?

A:-

RANK: We can only make one port as RANK and we can achieve only number of ranks from either top or bottom. Like top 5 or bottom 10 etc. If tha data has 100 rows we can’t achieve top 4th to 13 using single rank transformation. If we use two rank transformations ...first one top 13 and second one bottom 9 will suffice our requirement.

9) I have 3 rows in the Source field1 as A, B and C.I specified in the router 4 conditions Field1=A , Field1=A OR B ,Field1=A OR C and Field1= B OR C. How many rows will be loaded in to target.

A: - 7

Why need staging area database for DWH?

Answer: Staging area needs to clean operational data before loading into data warehouse.

cleaning in the sense your merging data which comes from different source .