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?
Q6). How are the Router and Filter different from each other?
Q7). What are the different types of repositories created using Informatica Repository Manager?
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.
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.
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?
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.
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.
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?
Q26). How to configure mapping in Informatica?
These are the common steps to follow for configuring mapping in Informatica.
To improve the performance, you should use these tips:
Q27). What are the possible number of dimensions available in Informatica?
The possible number of dimensions available in informatica can be given as:
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?
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.
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.
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:
Q45). How to differentiate Stop and Abort terms in Informatica?
Q46). How to differentiate Maplet and Reusable Transformation in Informatica?
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:
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.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Receive Latest Materials and Offers on SQL Server Course