Informatica Data Quality Interview Questions
1. What is the Update Strategy transformation?
Answer: Update strategy transformation is an active and connected transformation. It is used to update data in the target table, either to maintain a history of data or recent changes. You can specify how to treat source rows in table, insert, update, delete or data-driven.
2. What is the different lookup cache(s)?
Answer: Informatica Lookups can be cached or uncached (No cache). And Cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built and it remains the same during the session run. On the other hand, A dynamic cache is refreshed during the session run by inserting or updating the records in the cache based on the incoming source data. By default, Informatica cache is static cache.
Lookup cache can also be divided as persistent or nonpersistent based on whether Informatica retains the cache even after the completion of session run or deletes it
3. Define What Is Aggregate Cache In Aggregator Transformation?
Answer: The aggregator is nothing but a function which stores all the data in the aggregator cache until and unless it deals with all the aggregate calculations.
So when you are executing a session in which you are using an aggregator transformation, the Informatica server will automatically start creating indexes and data caches in the memory to accommodate and process the transformation.
It is a known fact that Informatica server needs more space, it stores the overflow values in all the cache files.
4. What Are The Different Tools In Workflow Manager?
The different tools available in the workflow manager are:
- Task designer
- Task developer
- Workflow designer
5. What is the source qualifier transformation?
Answer: When you add a relational or a flat file source definition to a mapping, you need to connect it to a source qualifier transformation. The source qualifier transformation represents the records that the Informatica Server reads when it runs a session.
6. What is the difference between maplet and reusable transformation?
Answer: Maplet consists of a set of transformations that are reusable.
A reusable transformation is a single transformation that can be reusable.
7. What is the difference between Static and Dynamic Lookup Cache?
Answer: We can configure a Lookup transformation to cache the underlying lookup table. In case of static or read-only lookup cache, the Integration Service caches the lookup table at the beginning of the session and does not update the lookup cache while it processes the Lookup transformation. (E learning portal)
In case of dynamic lookup cache, the Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is synchronized with the target.
In case you are wondering why do we need to make lookup cache dynamic, read this article on dynamic lookup
Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”. Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as a primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.
8. What Does The Update Strategy Actually Mean And What Are The Different Options Available For It?
Answer: Within Informatica, the data is processed based on row by row.
Within the target table, every row is inserted and it is marked as a default one.
The use of update strategy is done only when there is a need to update a single row or insert a row based on a sequence defined.
Within the update strategy, we need to mention the condition so that the specified row in the update strategy can be processed and the row can be actually marked as per the condition, i.e. updated or inserted.
9. What is Filter transformation?
Answer: Filter transformation is an Active and Connected transformation. This can be used to filter rows in a mapping that do not meet the condition.
10. Can You Briefly Define What Is A Reusable Transformation?
Answer: The reusable transformation concept is widely used in mappings.
Reusable transformation is different from that of other mappings where they use transformations as it stores as metadata.
Whenever there is a change in the reusable transformation, the transformation will be nullified in the mappings.
11. What are slowly changing dimensions?
Answer: Dimensions that change over time are called Slowly Changing Dimensions(SCD).
Slowly Changing Dimension-Type 1: Which has only current records.
Slowly Changing Dimension-Type2: Which has current records + historical records.
Slowly Changing Dimension-Type3: Which has current records + one previous records.
12. What Is A Predefined Event?
Answer: As the name itself suggests that the event is predefined. It is nothing but a file watch event. Within this process, it will wait for a certain file to arrive at a specific location.
13. Explain What Is The Difference Between Static Cache And Dynamic Cache?
It decreases the performance and productivity when compared to the static cache
The static cache is a process where it just inserts the data all the time. It doesn’t matter how many times the data is coming through, all it cares about is just inserting the data.
14. Define What Is A Surrogate Key?
Answer: A surrogate key is nothing but a replacement of the primary key within the database.
It is considered to be a unique identification factor for each row within a table.
It is very helpful because the primary key can change and thus makes it a difficult process to update the data, but not with the surrogate key.
A surrogate key is always in the form of a digit or an integer.
15. What Is The Difference Between The Powercenter Repository Service And The Model Repository Service?
Answer: The PowerCenter application services and PowerCenter application clients use the PowerCenter Repository Service. The PowerCenter repository has folder-based security.
The other application services, such as the Data Integration Service, Analyst Service, Developer tool, and Analyst tool, use the Model Repository Service. The Model Repository Service has project-based security.
You can migrate some Model repository objects to the PowerCenter repository.
16. What is Rank transformation?
Answer: Rank transformation is an Active and Connected transformation. It is used to select the top or bottom rank of data.
17. What is the update strategy and what are the options for update strategy?
Answer: Informatica processes the source data row-by-row. By default, every row is marked to be inserted in the target table. If the row has to be updated/inserted based on some logic Update Strategy transformation is used. The condition can be specified in Update Strategy to mark the processed row for an update or insert. company
Following options are available for update strategy :
DD_INSERT If this is used the Update Strategy flags the row for insertion. The equivalent numeric value of DD_INSERT is 0.
DD_UPDATE If this is used the Update Strategy flags the row for the update. The equivalent numeric value of DD_UPDATE is 1.
DD_DELETE If this is used the Update Strategy flags the row for deletion. The equivalent numeric value of DD_DELETE is 2.
DD_REJECT If this is used the Update Strategy flags the row for rejection. The equivalent numeric value of DD_REJECT is 3.
18. Define What Is A Mapplet?
Answer: A mapplet is nothing but a recyclable object which uses a mapplet designer.
Mapplet permits to reuse the transformation logic in different mappings.
A mapplet consists of a set of transformations.
19. How many types of dimensions are available in Informatica?
There are three types of dimensions available are :
20. Define mapping and sessions?
It is a set of source and target definitions linked by transformation objects that define the rules for transformation.
It is a set of instructions that describe how and when to move data from source to targets.
21. What Are The Types Of Loadings That Are Available In Informatica?
Answer: In Informatica, they are two types of loading:
Normal loading is a process where the records are loaded one by one and it writes a log for the same. When compared to other types of loading normal loading the loading process takes time to the target source.
Bulk loading is a process where a set of records are loaded into the target database at once. When compared to the normal loading process, the bulk loading process takes very less time to load the data.
22. Can You Name At Least One Alternative Tool For Scheduling Processes Other Than Workflow Manager?
Control M is an alternative tool for scheduling processes other than workflow manager pmcmd.
23. Explain Where We Can Find The Throughput Option In Informatica?
Answer: The throughput option is found in the Informatica workflow monitor. Within the workflow monitor, right-click on the session, then click on the run properties. Under source/target statistics we can find the throughput option.
24. What is the Sorter transformation?
Sorter transformation is a Connected and an Active transformation. It allows sorting data either in ascending or descending order according to a specified field
25. What is the difference between Router and Filter?
Answer: Router Filter
Router transformation divides the incoming records into multiple groups based on some condition. Such groups can be mutually inclusive (Different groups may contain the same record) Filter transformation restricts or blocks the incoming recordset based on one given condition.
Filter transformation restricts or blocks the incoming recordset based on one given condition. Filter transformation does not have a default group. If one record does not match filter condition, the record is blocked
The router acts like CASE. WHEN statement in SQL (Or Switch().. Case statement in C) Router acts like CASE. WHEN statement in SQL (Or Switch().. Case statement in C)
26. At The Time Of Informatica Powercenter Installation, Can You Please Let Us Know What All Components Are Installed?
Answer: The following components are installed while installing Informatica power center:
Administration console for PowerCenter
27. What are Session and Batches?
A session is a set of commands that describes the server to move data to the target.
A Batch is a set of tasks that may include one or more number of tasks (sessions, event wait, email, command, etc).
28. What is Data Transformation Manager(DTM)?
Answer: After the load manager performs validations for the session, it creates the DTM process. The DTM process is the second process associated with the session run.
29. What can we do to improve the performance of Informatica Aggregator Transformation?
Answer: Aggregator performance improves dramatically if records are sorted before passing to the aggregator and “sorted input” option under aggregator properties is checked. The recordset should be sorted on those columns that are used in Group By operation.
30. Explain What Is Meant By A Transformation? What Are The Different Types Of Transformations Available In Informatica?
Answer: The term transformation itself depicts the nature of the activity. It is a repository object where it generates, modifies and passes the data.
The following are different types of transformations that are available in Informatica:
- Aggregator transformation
- Expression transformation
- Filter transformation
- Joiner transformation
- Lookup transformation
- Normalizer transformation
- Rank transformation
- Router transformation
31. What is the Lookup transformation?
Answer: Lookup transformation is Passive and it can be both Connected and UnConnected as well. It is used to lookup data in a relational table, view, or synonym. Lookup definition can be imported either from source or from target tables.
32. What is the difference between STOP and ABORT options in Workflow Monitor?
Answer: When we issue the STOP command on the executing session task, the Integration Service stops reading data from the source. It continues processing, writing and committing the data to targets. If the Integration Service cannot finish processing and committing data, we can issue the abort command.
In contrast, the ABORT command has a timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.
33. What Are The Prerequisites Tasks That Are Needed To Achieve The Session Partition?
Answer: If you have to do the session partition then you need to start configuring the session to partition to source data and then you have to install Informatica server machine in different CPU. I.e. multifold CPU’s.
34. What is Router transformation?
Answer: Router transformation is an Active and Connected transformation. It is similar to filter transformation. The only difference is, filter transformation drops the data that do not meet the condition whereas the router has an option to capture the data that do not meet the condition. It is useful to test multiple conditions.
35. How can we update a record in the target table without using Update strategy?
Answer: A target table can be updated without using ‘Update Strategy’. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and check the “Update” check-box.
36. What Is The Main Use Of Stored Procedure Transformation?
Answer: The main use of stored procedure transformation is because it is a vital tool for maintaining and populating databases within the environment.
37. What Is The Difference Between Active Transformations And A Passive Transformation In Informatica? Give Example Transformations For Each?
Answer: Active transformation:
It is a process it changes the number of rows that have gone through the mapping. This process is called as Active transformation
Some of the Active transformations are:
- Sorter transformations
- Filter transformations
- Joiner transformations
- Rank transformations
- Router transformations
It is a process where it doesn’t change the number of rows that have gone through the mapping. This process is called Passive transformation.
Some of the Passive transformations are:
- Expression transformation
- Sequence Generator transformation
- Lookup transformation
- External procedure transformation
- Output transformation
- Input transformation
38. What are the types of lookup caches?
Answer: Static cache Dynamic cache Persistent cache Shared cache Recache.
39. What is a Data warehouse?
Answer: According to Bill Inmon, known as the father of Data warehousing. “A Data warehouse is a subject-oriented, integrated, time-variant, nonvolatile collection of data in support of management’s decision-making process”.
40. How can you define a transformation?
Answer: A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that perform specific functions.
41. Explain What Is Target Load Order?
Answer: Target load order is nothing but a list of all activities where one can define the priority. Based on this priority the data will be loaded into the Informatica server.
If you have a list of source qualifiers connected to multiple targets then you can define the order or dictate an order to the Informatica server so that the data can be loaded into the targets.
42. What is the difference between Active and Passive transformation?
Answer: Active Transformation: An active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not meet the condition in transformation.
Passive Transformation: A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.