Top 65 Informatica Interview Questions & Answers 2024

Informatica stands out as a dominant force in the ELT (Extract, Load, Transform) arena, widely adopted by enterprises for their data warehousing needs. The surge in data warehousing usage creates a prime opportunity to build a rewarding career in this field. As a highly sought-after skill, mastering Informatica can significantly enhance your employability.

Contents

1 Informatica Interview Questions
2 Informatica Interview Questions Scenario Based
3 Frequently Asked Informatica Interview Questions And Answers
4 Top Informatica Interview Questions With Answers
- 4.1 Conclusion:

Also check- Spring boot interview questions / AWS interview questions

Informatica Interview Questions

Q1.What are the differences between connected lookup and unconnected lookup?

Ans-

Connected vs Unconnected Lookups

–

Connected Lookup Unconnected Lookup

1. It receives input from the pipeline & participates in the data flow. 1. It receives input from the result of an LKP.

–

2. It can use both, dynamic and static cache. 2. It can’t be dynamic.

–

3. It can return more than one column value i.e. output port. 3. It can return only one column value.

–

4. It caches all lookup columns. 4. It caches only the lookup output ports in the return port & lookup conditions.

–

5. It supports user-defined default values. 5. It doesn’t support user-defined default values.

Q2.What is Lookup transformation?

Ans-

Lookup transformation is used to look up a source, source qualifier, or target in order to get relevant data.

It is used to look up a ‘flat file’, ‘relational table’, ‘view’ or ‘synonym’.

Lookup can be configured as Active or Passive as well as Connected or Unconnected transformation.

When the mapping contains the lookup transformation, the integration service queries the lookup data and compares it with lookup input port values. One can use multiple lookup transformations in a mapping.

The lookup transformation is created with the following type of ports:

Input port (I)

Output port (O)

Look up Ports (L)

Return Port (R)

Q3.How many input parameters can exist in an unconnected lookup?

Ans-

Any number of input parameters can exist. For instance, you can provide input parameters like column 1, column 2, column 3, and so on. But the return value would only be one.

Q4.Name the different lookup cache(s)?

Ans-

Informatica lookups can be cached or un-cached (no cache). Cached lookups can be either static or dynamic. A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after completing session run or if it deletes it.

–

Static cache

Dynamic cache

Persistent cache

Shared cache

Recache

Q5.Is ‘sorter’ an active or passive transformation?

Ans-

It is an active transformation because it removes the duplicates from the key and consequently changes the number of rows.

Q6.What are the types of groups in router transformation?

Ans-

Input group

Output group

Default group

Q7.What is the difference between STOP and ABORT options in Workflow Monitor?

Ans-

On issuing the STOP command on the session task, the integration service stops reading data from the source although it continues processing the data to targets. If the integration service cannot finish processing and committing data, we can issue the abort command.

–

ABORT command has a timeout period of 60 seconds. If the integration service cannot finish processing data within the timeout period, it kills the DTM process and terminates the session

Q8.Why is sorter an active transformation?

Ans-

When the Sorter transformation is configured to treat output rows as distinct, it assigns all ports as part of the sort key. The integration service discards duplicate rows that were compared during the sort operation. The number of input rows will vary as compared to the output rows and hence it is an active transformation.

Q9.When do you use SQL override in a lookup transformation?

Ans-

You should override the lookup query in the following circumstances:

–

Override the ORDER BY clause. Create the ORDER BY clause with fewer columns to increase performance. When you override the ORDER BY clause, you must suppress the generated ORDER BY clause with a comment notation.

Note: If you use pushdown optimization, you cannot override the ORDER BY clause or suppress the generated ORDER BY clause with a comment notation.

A lookup table name or column names contains a reserved word. If the table name or any column name in the lookup query contains a reserved word, you must ensure that they are enclosed in quotes.

Use parameters and variables. Use parameters and variables when you enter a lookup SQL override. Use any parameter or variable type that you can define in the parameter file. You can enter a parameter or variable within the SQL statement, or use a parameter or variable as the SQL query. For example, you can use a session parameter, $ParamMyLkpOverride, as the lookup SQL query, and set $ParamMyLkpOverride to the SQL statement in a parameter file. The designer cannot expand parameters and variables in the query override and does not validate it when you use a parameter or variable. The integration service expands the parameters and variables when you run the session.

A lookup column name contains a slash (/) character. When generating the default lookup query, the designer and integration service replace any slash character (/) in the lookup column name with an underscore character. To query lookup column names containing the slash character, override the default lookup query, replace the underscore characters with the slash character, and enclose the column name in double quotes.

Add a WHERE clause. Use a lookup SQL override to add a WHERE clause to the default SQL statement. You might want to use the WHERE clause to reduce the number of rows included in the cache. When you add a WHERE clause to a Lookup transformation using a dynamic cache, use a Filter transformation before the Lookup transformation to pass rows into the dynamic cache that match the WHERE clause.

Note: The session fails if you include large object ports in a WHERE clause.

Other. Use a lookup SQL override if you want to query lookup data from multiple lookups or if you want to modify the data queried from the lookup table before the Integration Service caches the lookup rows. For example, use TO_CHAR to convert dates to strings.

Q10.What are data driven sessions?

Ans-

When you configure a session using update strategy, the session property data driven instructs Informatica server to use the instructions coded in mapping to flag the rows for insert, update, delete or reject. This is done by mentioning DD_UPDATE or DD_INSERT or DD_DELETE in the update strategy transformation.

–

“Treat source rows as” property in session is set to “Data Driven” by default when using a update strategy transformation in a mapping.

Q11.What are mapplets?

Ans-

•A Mapplet is a reusable object that we create in the Mapplet Designer.

•It contains a set of transformations and lets us reuse that transformation logic in multiple mappings.

Q12.Explain sessions. Explain how batches are used to combine executions?

Ans-

A teaching set that needs to be implemented to convert data from a source to a target is called a session. Session can be carried out using the session’s manager or pmcmd command. Batch execution can be used to combine sessions executions either in serial manner or in a parallel. Batches can have different sessions carrying forward in a parallel or serial manner.

Q13.How many number of sessions can one group in batches?

Ans-

One can group any number of sessions but it would be easier for migration if the number of sessions are lesser in a batch.

Q14.Explain the difference between mapping parameter and mapping variable?

Ans-

When values change during the session’s execution it’s called a mapping variable. Upon completion the Informatica server stores the end value of a variable and is reused when session restarts. Moreover those values that do not change during the sessions execution are called mapping parameters. Mapping procedure explains mapping parameters and their usage. Values are allocated to these parameters before starting the session.

Q15.What is complex mapping?

Ans-

Following are the features of complex mapping.

–

Difficult requirements

Many numbers of transformations

Complex business logic

Informatica Interview Questions Scenario Based

Q16.How can one identify whether mapping is correct or not without connecting session?

Ans-

One can find whether the session is correct or not without connecting the session is with the help of debugging option.

Q17.Can one use mapping parameter or variables created in one mapping into any other reusable transformation?

Ans-

Yes, One can do because reusable transformation does not contain any mapplet or mapping.

Q18.Explain the use of aggregator cache file?

Ans-

Aggregator transformations are handled in chunks of instructions during each run. It stores transitional values which are found in local buffer memory. Aggregators provides extra cache files for storing the transformation values if extra memory is required.

Q19.Briefly describe lookup transformation?

Ans-

Lookup transformations are those transformations which have admission right to RDBMS based data set. The server makes the access faster by using the lookup tables to look at explicit table data or the database. Concluding data is achieved by matching the look up condition for all look up ports delivered during transformations.

Q20.What does role playing dimension mean?

Ans-

The dimensions that are utilized for playing diversified roles while remaining in the same database domain are called role playing dimensions.

Q21.How can we delete duplicate rows from flat files?

Ans-

We can make use of sorter transformation and select distinct option to delete the duplicate rows.

Q22.What are the different ways to filter rows using Informatica transformations?

Ans-

Source Qualifier

Joiner

Filter

Router

Q23.What are the different transformations where you can use a SQL override?

Ans-

Source Qualifier

Lookup

Target

Q24.Why is it that in some cases, SQL override is used?

Ans-

The Source Qualifier provides the SQL Query option to override the default query. You can enter any SQL statement supported by your source database. You might enter your own SELECT statement, or have the database perform aggregate calculations, or call a stored procedure or stored function to read the data and perform some tasks.

Q25.What do you mean by Enterprise Data Warehousing?

Ans-

When the organization data is created at a single point of access it is called as enterprise data warehousing. Data can be provided with a global view to the server via a single source store. One can do periodic analysis on that same source. It gives better results but however the time required is high.

Q26.What the difference is between a database, a data warehouse and a data mart?

Ans-

Database includes a set of sensibly affiliated data which is normally small in size as compared to data warehouse. While in data warehouse there are assortments of all sorts of data and data is taken out only according to the customer’s needs. On the other hand datamart is also a set of data which is designed to cater the needs of different domains. For instance an organization having different chunk of data for its different departments i.e. sales, finance, marketing etc.

Q27.What is meant by a domain?

Ans-

When all related relationships and nodes are covered by a sole organizational point, its called domain. Through this data management can be improved.

Q28.What is the difference between a repository server and a powerhouse?

Ans-

Repository server controls the complete repository which includes tables, charts, and various procedures etc. Its main function is to assure the repository integrity and consistency. While a powerhouse server governs the implementation of various processes among the factors of server’s database repository.

Q29.What are the types of metadata that stores in repository?

Ans-

The types of metadata includes Source definition, Target definition, Mappings, Mapplet, Transformations.

Q30.Explain the code page compatibility?

Ans-

When data moves from one code page to another provided that both code pages have the same character sets then data loss cannot occur. All the characteristics of source page must be available in the target page. Moreover if all the characters of source page are not present in the target page then it would be a subset and data loss will definitely occur during transformation due the fact the two code pages are not compatible.

Frequently Asked Informatica Interview Questions And Answers

Q31.How can you validate all mappings in the repository simultaneously?

Ans-

All the mappings cannot be validated simultaneously because each time only one mapping can be validated.

Q32.Briefly explain the Aggregator transformation?

Ans-

It allows one to do aggregate calculations such as sums, averages etc. It is unlike expression transformation in which one can do calculations in groups.

Q33.Describe Expression transformation?

Ans-

Values can be calculated in single row before writing on the target in this form of transformation. It can be used to perform non aggregate calculations. Conditional statements can also be tested before output results go to target tables.

Q34.What do you mean by filter transformation?

Ans-

It is a medium of filtering rows in a mapping. Data needs to be transformed through filter transformation and then filter condition is applied. Filter transformation contains all ports of input/output, and the rows which meet the condition can only pass through that filter.

Q35.What is Joiner transformation?

Ans-

Joiner transformation combines two affiliated heterogeneous sources living in different locations while a source qualifier transformation can combine data emerging from a common source.

Q36.What is Lookup transformation?

Ans-

It is used for looking up data in a relational table through mapping. Lookup definition from any relational database is imported from a source which has tendency of connecting client and server. One can use multiple lookup transformation in a mapping.

Q37.How Union Transformation is used?

Ans-

Ans: It is a diverse input group transformation which can be used to combine data from different sources. It works like UNION All statement in SQL that is used to combine result set of two SELECT statements.

Q38.What do you mean Incremental Aggregation?

Ans-

Option for incremental aggregation is enabled whenever a session is created for a mapping aggregate. Power center performs incremental aggregation through the mapping and historical cache data to perform new aggregation calculations incrementally.

Q39.What is the difference between a connected look up and unconnected look up?

Ans-

When the inputs are taken directly from other transformations in the pipeline it is called connected lookup. While unconnected lookup doesn’t take inputs directly from other transformations, but it can be used in any transformations and can be raised as a function using LKP expression. So it can be said that an unconnected lookup can be called multiple times in mapping.

Q40.What is a mapplet?

Ans-

A recyclable object that is using mapplet designer is called a mapplet. It permits one to reuse the transformation logic in multitude mappings moreover it also contains set of transformations.

Q41.Briefly define reusable transformation?

Ans-

Reusable transformation is used numerous times in mapping. It is different from other mappings which use the transformation since it is stored as a metadata. The transformations will be nullified in the mappings whenever any change in the reusable transformation is made.

Q42.How many repositories can be created in informatica?

Ans-

There can be any number of repositories in informatica but eventually it depends on number of ports.

Q43.What does update strategy mean, and what are the different option of it?

Ans-

Row by row processing is done by informatica. Every row is inserted in the target table because it is marked as default. Update strategy is used whenever the row has to be updated or inserted based on some sequence. Moreover the condition must be specified in update strategy for the processed row to be marked as updated or inserted.

Q44.What is the scenario which compels informatica server to reject files?

Ans-

This happens when it faces DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in the rows was condensed.