Top 50 Informatica Interview Questions and Answers

Informatica Interview Questions and Answers

There has been a wonderful time to explore your career around data warehousing with Companies investing in tools like Informatica Power Center, and along with this, there is a critical need for skilled developers who can leverage these tools for better business insights. At JanBask Training, we have compiled a list of best Informatica interview questions and answers that would help you ace the Informatica interview. If you want to appear for an Informatica interview shortly, then these questions will surely help you in earning a competitive edge among the crowd. So, all the best and let us start with the discussion now.

Q1). How to improve the performance of aggregator in Informatica?

Performance of aggregator improves dramatically when records are sorted well before they are passed to the aggregator. Here, you can use Group by clause to sort records.

Q2). How to delete duplicate rows using Informatica?

For this purpose, you should check the Distinct option of the Source qualifier in the source table and load the target accordingly.

Q3). What is a Lookup Cache in Informatica?

Lookup Cache is either static or dynamic in nature. A static lookup cache cannot be modified once it is built and remains the same during the session run. A dynamic cache can be modified during the session run, and you can modify the database based on the incoming data source. A lookup cache can be either persistent or non-persistent based on whether Informatica retains the Cache even after session run is complete or remains pending.

Q4). Is it possible updating a record in the target table without using update strategy?

Yes, it is possible updating a target table without using the update strategy. For this purpose, we should define the key first then connect the key with the respective field that you want to update.

Q5). How are connected and unconnected lookup differ from each other?

Connected Lookup:

• Connected lookup receives inputs directly from the pipeline.
• It can be both either static or dynamic.
• It may return more than one column value.
• It may cache all lookup columns.
• It supports user-defined default values.

Unconnected Lookup:

• It receives input value from the result of another expression.
• It is only static, not dynamic.
• It may return only one column value.
• It caches only the selected lookup columns.
• It does not support user-defined default values.

Q6). How are the Router and Filter different from each other?

Router:

• Router divides the incoming records into multiple groups based on a certain condition.
• The Router itself does not block any record. If certain records don’t match any condition, then they are routed to the default group.
• The Router acts like “When” Statement in SQL.

Filter:

• A Filter restricts or blocks the incoming recordset based on the given condition.
• The Filter blocks the record if it does not match the specific condition.
• The Filter acts like the “WHERE” statement in SQL.

Q7). What are the different types of repositories created using Informatica Repository Manager?

1. Global Repository: It is the centralized repository that can access shared objects across the repositories in a domain. The objects are generally shared using global shortcuts.
2. Local Repository: Local repository can connect to a global repository with the help of global shortcuts, and it can use objects in its shared folders.
3. Standalone Repository: It is a single repository that works individually and not connected to any other repositories.
4. Versioned Repositories: It may be either local or global but allows version control for the repository. It can store multiple copies or versions of an object.

Q8). How to define a Code Page in Informatica?

The code page contains encoding to specify characters in a set of multiple columns. A code page is selected based on the source of the data. It influences the way how application stores, receives, and send the character data.

Read: Difference Between Clustered and Non-Clustered Index in the SQL

Q9). What all databases can Informatica on Windows connect to?

        

Q10). What all databases can Informatica on Unix connect to?

           

Q11). What is Update Strategy Transformation, and how is it useful?

It is a crucial transformation that is required to maintain the history data or just the most recent changes in the target table. Here, we can set or flag the records using two levels.

• Within a Session: When you configure sessions, you may instruct the Informatica server to treat all the records similarly.
• Within a Mapping: Here, we use the Update Strategy Transformation to flag the records like insert, update, delete, or reject, etc.

Q12). How will you define the measure objects in Informatica?

Aggregate calculations like sum, average, maximum, minimum are the measure objects.

Q13). How to load five flat files having the same structure to a single target? Which transformation should I use?

You can use either file repository or Union operator to copy multiple files to a single target.

Q14). What are the benefits or drawbacks of using Start Schema?

In a star schema, there is no relationship between dimension tables or relational tables. All dimensions are denormalized, and query performance is degraded.

Q15). What are the benefits of using Snowflake Schema?

In the snowflake schema, there is a possible relationship between dimension tables or relational tables. All dimensions are normalized, and query performance is increased.

Q16). What is the top down and bottom up approach in Data Warehousing?

Here is the top-down approach for Data warehouse:

ODS --> ETL --> Datawarehouse --> DataMart --> OLAP

Here is the bottom-up approach for Data Warehouse:

ODS --> ETL --> DataMart --> Datawarehouse --> OLAP

Q17). What is Session Partitioning in Informatica?

Performance can be improved by creating multiple partitions and processing data in parallel. Informatica can achieve high performance by partitioning the pipeline and perform loading, extraction, and transformation for each partition in parallel.

Q18). How to create a mapping using multiple lookup transformation?

You should use unconnected lookup here if the same lookup repeats for multiple times.

Q19). How to differentiate summary filters and Details filters from each other? 

• Summary filters can be applied to records that are grouped together using common values.
• Details filters can be applied to every record in a session.

Q20). Can you consider Informatica as a cleansing tool? Explain with example.

Yes, we can use Informatica for data cleansing. Here, you can use either stages or expressions for data cleaning. You can use constant values or assign space for avoiding the session failures.

Information Interview Questions and answers for Advanced Workforce

Q21). How to import or create flat file definition into the warehouse designer?

It is not possible creating or importing flat files directly to the warehouse designer quickly. Instead, you should analyze files in the source analyzer first and drag those files into the warehouse designer. When these files are dragged, the warehouse designer will create a file definition, not the relational target definition. To load a file, configure the session to write to a flat file. When Informatica server runs the session, it creates and loads the flat file.

Q22). Do you know either a fact table is normalized or denormalized in nature?

A fact table is demoralized in nature that stores the data from the dimension table generally contains foreign keys and measures.

Read: What is Complex SQL Queries? Explain Complex SQL Queries with Examples

Q23). How to create headers and footers in Target using Informatica?

If you are talking about flat files, then you can set the same in file properties. If you want to add headers and footers at the session level, then check the session properties.

Q24). What do you understand by the term Informatica Server Architecture?

The major components of Informatica server architecture are load manager, data transfer manager, temp server, reader, and writer, etc. Load manager sends a request to the reader, if the reader is ready to read the data from the source then it is stored to the temp server, and data transfer manager takes the data from temp server and loads it to the target.

Q25). In a source qualifier, what are the basic needs to join two sources? 

1. Both sources should be in the same database.
2. There should be at least one column in common with the same data types.

Q26). How to configure mapping in Informatica?

These are the common steps to follow for configuring mapping in Informatica.

• Import source from the database.
• Verify if the target table exists in the database or not.
• If it is available then make sure that data is deleted from the table and imported into the designer.
• If it is not available, then the target table can be created using the target wizard.
• In the next step, drag the needed data to the workspace.
• You could use this information when required.

To improve the performance, you should use these tips:

• Sort the records before placing them to the aggregator.
• If the filter is available, then keep it as near to the source as possible.
• It would be great if you can place extra expression before the target to make changes in the future.
• If you need more than one filter, then use router transformation in that case.
• If there is a source filter option, then use filter transformation immediately after the source.
• For routers, if not needed, then you should not connect the default group to any target.

Q27). What are the possible number of dimensions available in Informatica?

The possible number of dimensions available in informatica can be given as:

• Junk dimension
• Degenerative dimension
• Conformed Dimension

Q28). How Informatica 7.0 and Informatica 8.0 are different from each other?

In Informatica 8.0, power exchange concept is introduced that was not available in Informatica 7.0.

Q29). What do you understand by Joiner Caches in Informatica?

It specifies the directory that can be used to cache the master records and set indexes to these records. This directory can be mapped with a mounted drive. These are two types of joiner caches in Informatica. These are Data Cache and Index Cache.

Q30). How to differentiate Filter and Lookup transformation in Informatica?

Filter transformation is an active transformation, and Lookup transformation is passive transformation. Filter transformation is used to filter rows based on specified conditions, and lookup transformation is used to look up data in a relational table or a flat file.

Q31). Among Connected Lookup and Unconnected Lookup, which one is better and why?

If there is a well-defined source, then you should use connected lookup, if the source is not defined well then you can use unconnected lookup in that case. When you look up for a single value among thousands, then you should use unconnected lookup here.  If multiple columns are returned, then opt for connected lookup in that case.

Q32). What are the possible number of tasks can be processed at the port level using any specific port?

It is expression transformation or unconnected lookup.

Q33). Is it possible to read or access the rejected data from the bad file?

With the load order utility, you may find the rejected data from the bad file and make the corrections as required.

Q34). How are these three terms different - Normalization, Normalizer, and Normalizer Transformation?

1. Normalizer: It is a transformation that is majorly used for COBOL resources. It can convert rows into columns and columns into rows.
2. Normalization: it is the process used to prevent redundancy or inconsistency.
3. Normalizer Transformation: To obtain multiple columns from a single row, Normalizer transformation is used.

Q35). What are the conditions to specify a Joiner Transformation?

A joiner transformation compares each row of the master source against the detail source. There are a few unique rows and iterations in the master that speed up the join process. Joiner Transformation caches the master table data, so you should define only a selected number of rows as the master.

Q36). What is the process to access the first 100 rows from flat file to the Target?

For this purpose, create one procedure and declare a sequence inside the procedure. Now call the procedure in the Informatica using stored procedure transformation to access top 100 rows from the flat file to the target.

Q37). What is the process for importing Oracle Sequence in Informatica?

To import the Oracle sequence in Informatica, first, create one procedure and declare a sequence within the procedure. Now call the procedure in Informatica with the help of stored procedure transformation.

Q38). Do you know how to load time dimensions?

Here you can use SCD type 1/2/3 to load dimensions based on requirements. We can also use procedures to populate time dimensions.

Read: What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values)

Q39). Do Java transformation is available in Informatica?

Yes, it is available in Informatica 8.0 version.

Q40). What do you understand by the term hash table in Informatica?

In hash partitioning, the Informatica Server uses a hash function to group multiple rows of data among partitions. The data rows are grouped together based on a partition key. For example, you want to group multiple items together in a table having the same ID number without knowing it in advance.

Informatica Job Interview Questions and Answers

Q41). What is the process for handling Decimal Places while importing a flat file into Informatica?

By default, flat files support only numbers, not decimal places. To support the decimal places in Informatica, you should define a data type as decimal for that number port of the source.

Source -> Number Port -> Decimal Data Type

First of all, import fields as a string then use expressions to convert it. You should avoid truncation if there are decimal places in the source itself.

Q42). What is incremental aggregation in Informatica? Explain the concept with the help of an example.

In the case of Incremental aggregation, Informatica passes the source data through mapping and utilizes the historical data to perform new aggregation calculations incrementally to improve the performance. When the source changes incrementally, it can capture changes and able to process those changes. In this way, the target can be updated incrementally instead of modifying each record one by one that is highly time-consuming.

Q43). Do you know about the Target load order in Informatica?

A Target Load Order is the collection of source qualifiers, transformations, and targets that are connected together through mapping. Target load order can be specified based on source qualifiers in a mapping. If multiple source qualifiers are connected to different targets, then you can set an order in which Informatica server loads data into targets.

Q44). What is the process for recovering sessions into concurrent batches?

If multiple sessions in a concurrent batch fail, there is a possibility of truncating all targets and run them again. At the same time, if a particular session in a concurrent batch fails, then all other sessions can be executed successfully. However, that particular session can be recovered later as a standalone session.

Here is the process of how to recover a failure session in Informatica:

• For this, copy the failed session with the help of Operation-Copy session.
• Now drag the copied session outside the batch to a standalone session.
• Follow the given steps to recover a session, and
• Delete the standalone copy in the end.

Q45). How to differentiate Stop and Abort terms in Informatica? 

• The particular session you want to stop is a part of the batch, so stop that batch. If the batch is a part of nested batches, then stop the outermost batch.
• Abort and Stop works almost similar except the difference that abort has 60 seconds timeout.

Q46). How to differentiate Maplet and Reusable Transformation in Informatica?

Maplet:

• It has more than one transformation.
• Multiple transformations can be reused when needed.

Reusable Transformation:

• It contains only a single transformation.
• Only one transformation can be reused.

Q47). How to complete sessions that are not recoverable?

Under specific conditions, if a session does not complete, you should truncate the target tables and run sessions from the beginning. When running sessions for recovery, it may result in inconsistent data sometimes. If there is no recovery option for the session in the mid then:

• The table should not be truncated immediately.
• Check on the session carefully that gas caused the failure.

Q48). What is a Source Qualifier?

When you are adding a relation or flat file definition to the mapping, it is first connected to a Source Qualifier. A source qualifier represents the rows that Informatica Server reads and executes a session.

Q49). What do you understand by partition points in Informatica?

Partition points in Informatica mark the thread boundaries in a source pipeline and divides pipelines into stages further.

Q50). How can you improve the performance of the session using Aggregator Transformation?

The best idea is using sorted inputs for Aggregator Transformation. If data cannot be sorted due to any issue, then configure the cache and set indexes to it.

This blog, “Top 50 Informatica Interview Questions and Answers” gives you a depth idea of queries that you have to encounter during an Informatica Interview. This is a brief idea, but there are a lot more that can be asked by the interviewer. To understand it deeply, you are recommended joining the SQL certification course at JanBask Training and make yourself future-ready in IT marketplace now.