40+ [REAL-TIME] Informatica Analyst Interview Questions and Answers
Last updated on 26th Sep 2022, Blog, Interview Question
1. What is difference between the Informatica vs Talend?
|Provides only a commercial data integration.
|Available an open-source and commercial editions.
|Founded way back in a 1993.
|Founded in a year 2006.
|Charges are applicable per customer.
|Open source is for to be free.
|RDBMS repository stores a metadata generated.
|Implemented on the any java supported platforms.
|Integrating code is not much effective.
|Code customization is an effective.
|No prior knowledge is be required.
|knowledge on java is to be preferred.
|Automated deployment is not up to a mark.
|Deployment made simple.
|Transformations are be re-usable.
|Components are be re-usable.
2. What is meaning of Enterprise Data Warehousing?
Enterprise Data Warehousing is a data of the organization being created or developed at single point of access. The data is globally accessed and viewed through the single source since the server is linked to a single source. It also included the periodic analysis of a source.
3. What is meaning of Lookup transformation?
To get a relevant data or information, a Lookup transformation is used to find source qualifier, a target, or other sources. Many types of files can be searched in a Lookup transformation like for example flat files, relational tables, synonym, or views, etc. A Lookup transformation can be cited as a active or passive. The following are various types of ports with which the lookup transformation is created:
- Input port
- Output port
- Lookup ports
- Return port
4. What are points of difference between the connected lookup and unconnected lookup?
- Connected lookup is a one that takes up the input directly from other transformations and also participates in a data flow. On the other hand, an unconnected lookup is just an opposite. Instead of taking a input from the other transformations, it simply receives a values from the result or function of a LKP expression.
- Connected Lookup cache can be both a dynamic and static but unconnected Lookup cache can’t be dynamic in the nature. The First one can return to a multiple output ports but a latter one returns to only one output port. User-defined values which ads generally a default values are supported in a connected lookup but are not supported in a unconnected lookup.
5. How many input parameters can be present in the unconnected lookup?
The number of parameters that can included in an unconnected lookup is a numerous. However, no matter how many parameters are put, the return value would be there only one. For example, parameters like a column 1, column 2, column 3, and column 4 can be put in unconnected lookup but there is a only one return value.
6. How many lookup caches are be available?
- Static Cache
- Dynamic Cache
- Persistent Cache
- Shared Cache
7. What is difference between a data warehouse, a data mart, and a database?
Data warehouse consists of various kinds of data. A database also consists of data but however, an information or data of the database is smaller in size than a data warehouse. Datamart also includes a various sorts of data that are needed for various domains. Examples – Different dates for different sections of the organization such as sales, marketing, financing, etc.
8. What is domain?
The major organizational point sometimes undertakes all interlinked and interconnected nodes and relationships and this is known as the domain. These links are covered mainly by a one single point of an organization.
9. What is Cite the differences between the powerhouse and a repository server?
The powerhouse server is a main governing server that helps in an integration process of various processes among the different factors of the server’s database repository. On other hand, the repository server ensures a repository integrity, uniformity, and consistency.
10. In Informatica, how many numbers are repositories are possible to be made?
The total figure of a repositories created in Informatica mainly depends on a total amounts of ports of the Informatica.
11. What are the benefits of partitioned session?
A session is a partitioned in order to increase and improved the efficiency and the operation of a server. It includes a solo implementation sequences in session.
12. Define a parallel processing?
Parallel processing helps in further improvement of a performance under hardware power. The parallel processing is actually done by using a partitioning sessions. This partitioning option of a Power Center in Informatica increases a performance of Power Center by parallel data processing. This allows a large data set to be divided into the smaller subset and this is also processed in order to get good and better performance of a session.
13. What are different types of methods for implementation of a parallel processing in Informatica?
- Database Partitioning
- Round-Robin Partitioning
- Hash Auto-keys partitioning
- Hash User-Keys Partitioning
- Key Range Partitioning
- Pass-through Partitioning
14. What are best mapping development practices?
- Source Qualifier
15. What are different mapping design tips for an Informatica?
16. What is the meaning of a word ‘session’? Give an explanation of how to combine the execution with the assistance of a batches?
Converting a data from a source to a target is generally implemented by teaching service and this is known as the session. Usually, the session manager executes a session. In order to combine a session’s executions, batches are used in a two ways – serially or parallelly.
17. How many numbers of sessions are grouped in a one batch?
Any number of sessions can be grouped in a one batch but however, for simpler migration process, it is better if a number is lesser in one batch.
18. What is difference between a mapping parameters and mapping variables?
The mapping variable refers to a changing values of sessions’ execution. On other hand, when the value doesn’t change during a session then it is called a mapping parameters. The mapping procedure explains a procedure of the mapping parameters and usage of this parameter. Values are best allocated before beginning of session to these mapping parameters.
19. What are features of complex mapping?
- Difficult requirements
- Numerous transformations
- Complex logic regarding business
20. Which option helps in finding whether a mapping is correct or not?
The debugging option helps to judging whether the mapping is correct or not without a really connecting to the session.
21. What is mean by OLAP?
OLAP or also known as a On-Line Analytical Processing is a method with the assistance of which a multi-dimensional analysis occurs.
22. Mention different types of OLAP?
23. What is the meaning of a surrogate key?
The surrogate key is just a replacement in the place of the prime key. The latter is a natural in nature. This is a various type of identity for each consisting of a different data.
24. What is session task?
When a Power Centre Server transfers data from a source to the target, it is often guided by a set of instructions and this is known as a session task.
25. What is meaning of a command task?
Command task only allows a flow of more than one shell command or sometimes flow of a one shell command in Windows while a work is running.
26. What is meaning of a standalone command task?
The type of a command task that allows a shell commands to run anywhere during a workflow is known as the standalone task.
27. Define a workflow?
The workflow includes a set of instructions that allows a server to communicate for an implementation of tasks.
28. How many tools are there in a workflow manager?
- Task Designer
- Task Developer
- Workflow Designer
- Worklet Designer
29. Define a target load order?
Target load order is a dependent on a source qualifiers in a mapping. Generally,a multiple source qualifiers are linked to the target load order.
30. Define a Power Centre repository of Informatica?
- Source Definition
- Session and session logs
- Target Definition
- ODBC Connection
Two repositories are as below :
- Global Repositories
- Local Repositories
31. Name a scenario in which the Informatica server rejects files?
When a server faces rejection of update strategy transformation, it regrets files. The database consisting of an information and data also gets disrupted. This is the rare case scenario.
32. How to use a Normalizer Transformation in Informatica?
- This is a type of an Active T/R which reads a data from COBOL files and VSAM sources (virtual storage access method).
- Normalizer T/R act like the source Qualifier T/R while reading a data from COBOL files.
- Use Normalizer T/R that converting an each input record into a multiple output records. This is known as a Data pivoting.
33. What are Limitations of a Pushdown Optimization?
- Rank T/R cannot be pushed.
- Transaction control T/R.
- Sorted aggregation.
- Design the mapping with filter, rank, and expression T/R.
- Create the session –> Double click a session select properties tab.
- Select a mapping tab –> set reader, writer a connection with target load type normal.
- Click apply –> click ok –> save a session.
- Create & start a workflow.
34. What is difference between the Copy and Shortcut?
|Copy an object to the another folder
|Dynamic-link to the object in folder
|Changes to an original object don’t reflect
|Dynamically reflects a changes to an original object
|Duplicate’s a space
|Preserves a space
|Created from the unshared folders
|Created from the shared folders
35. How to use a PMCMD Utility Command?
1. It is command based client program that communicates with an integration service to perform some of a tasks which can also be performed using a workflow manager client.
2. Using a PMCMD and can perform the following tasks:
- Starting a workflow.
- Scheduling a workflow.
3. The PMCMD can be operated in a two various modes:
- Interactive Mode.
- Command-line Mode.
36. How do scheduling a Workflow?
A schedule is an automation of running a workflow at a given date and time.There are 2 types of a schedulers:
- Reusable scheduler
- Non Reusable scheduler
37. What is a Dynamic Lookup Cache?
- The cache updates or changes dynamically when a lookup at target table.
- The dynamic lookup T/R allows for a synchronization of target lookup table image in a memory with its physical table in database.
- The dynamic lookup T/R or dynamic lookup cache is operated in a only connected mode (connected lookup ).
- Dynamic lookup a cache support only an equality conditions (=conditions).
|New Lookup Row
|The integration service does not update or insert a row in cache.
|The integration service inserts a row into cache.
|The integration service updates a row in cache.
38. How to use a PowerCenter Command-Line in Informatica?
The transformation language provides a two comment specifiers to let insert comments in an expression:
- Two Dashes ( – – )
- Two Slashes ( / / )
The Power center integration service ignores all text on the line preceded by these two comment specifiers.
39. What is difference between a variable port and the Mapping variable?
|Local to a T/R
|Local to a Mapping
|Values are be non-persistent
|Values are be persistent
|Can’t be used with a SQL override
|Can be used with a SQL override
- Mapping variables is used for an incremental extraction.
- In a mapping variables no need to change a data. It automatically be changed.
- In a mapping parameter, and have to change a date and time.
40. Which is a T/R that builts only a single cache memory?
Rank can build a two types of cache memory. But sorter always built only a one cache memory. The cache is also called be a Buffer.
41. What is XML Source Qualifier Transformation in an Informatica?
- Reads a data from XML files.
- XML source definition associates with the XML source Qualifier.
- XML files are be case-sensitive markup language.
- Files are saved with the extension. XML.
- XML files are the hierarchical (or) parent-child relationship file formats.
- Files are normalized or denormalized.
42. What is a Load Order?
Design mapping applications that first load a data into dimension tables. And then load a data into fact table:
Load Rule: If all dimension table loadings are be success then load the data into a fact table.
Load Frequency: Database gets a refreshed on daily loads, weekly loads, and monthly loads.
43. What is a Snowflake Schema?
Snowflake Schema is the large denormalized dimension table is split into a multiple normalized dimensions.
|Select a Query performance increases.
|Maintenance cost increases due to the more no. of tables.
44. What is Standalone Email task?
- It can be used anywhere in a workflow, explained will Link conditions to notify a success or failure of prior tasks.
- Visible in a Flow Diagram.
- Email Variables can be explained with the stand-alone email tasks.
45. What is a Mapping Debugger?
A debugger is the tool. By using this can identify a records are loaded or not and correct data is loaded or not from a one T/R to another T/R.Session succeeded but records are not be loaded. In this situation, have to use a Debugger tool.
46. What is functionality of a F10 in Informatica?
F10 –> a Next Instance.
47. What is T/R has No cast?
- F5 –> Start a Debugger.
- A debugger is used to test a records are loader or not, correct data is a loader or not.
- A debugger is used only to test a Valid Mapping but not an invalid Mapping.
48. What are the Worklet and what types of a workouts?
A worklet is explained as a group of a related tasks.
There are 2 types of a worklet:
- Reusable worklet
- Non-Reusable worklet
Worklet expands and executes a tasks inside a workflow.
A workflow that contains a worklet is known as a Parent Workflow.
49. What is a Relative Mode?
Relative Time: The timer task can start a timer from the start timer of an timer task, the start time of a workflow or worklet, or from the start time of parent workflow.
- A timer task is majorly used for a scheduling workflow.
- Workflow 11 AM –> Timer (11:05 AM) –> an Absolute Mode.
- Anytime a workflow start after a 5 mins Timer –> (5 mins) will start a Relative Mode.
50. What is Difference between the Filter and Router T/R?
|A Single condition.
|The Multiple conditions.
|A Single Target.
|The Multiple Targets.
|Rejected a rows cannot be captured.
|A Default group captures rejected a rows.
51. What is Repository Manager?
It is the GVI based administrative client that allows to performing a following administrative tasks:
- Create, edit and also delete folders.
- Assign a users to access folders with the reading, write and execute permissions.
- Backup and Restore a repository objects.
52. What is a Rank Transformation in Informatica?
This a type of an active T/R which allows to find out either a top performance or bottom performers:
- Input Port (I)
- Output Port (O)
- Rank Port (R)
- Variable Port (V)
53. What is meant by a Informatica PowerCenter Architecture?
- A Power Center Clients.
- A Power Center Repository.
- A Power Center Domain.
- A Power Center Repository Service (PCRS).
- A Power Center Integration Service (PCIS).
- An Informatica administrator.
54. What is a Workflow Monitor?
1. It is the GUI based client application that allows users to monitor an ETL objects running an ETL Server.
2. Collect a runtime statistics such as:
- No. of records be extracted.
- No. of records be loaded.
- No. of records are rejected.
- Fetch a session log.
3. Complete the information can be accessed from a workflow monitor.
4. For an every session, one log file is to be created.
55. If Informatica has its own scheduler why using the third-party scheduler?
The client uses a different applications and integrates a different applications & scheduling those applications it is more easy by using a third party schedulers.
56. What is a Workflow Manager?
- A session is the task that executes mapping.
- A session is created for every Mapping.
- A session is created to provide a runtime properties.
- A session is a set of an instructions that tells ETL server to move a data from a source to destination.
Workflow: Workflow is the set of instructions that tells how to run a session tasks and when to run a session tasks.
57. What is an Informatica PowerCenter?
A data integration tool that combines a data from the multiple OLTP source systems, transforms a data into a homogeneous format and delivers a data throughout an enterprise at any speed.There are more products in an Informatica corporation:
- An Informatica Analyzer.
- A Life cycle management.
- A Master data.
Having more products in a Informatica.Informatica power center is one of products of an Informatica.Using a Informatica power center will do the Extraction, transformation, and loading.
58. What is Dimensional Model?
Dimensional modeling consists of a following types of schemas designed for the Datawarehouse:
- Star Schema.
- Snowflake Schema.
- Gallery Schema.
A schema is the data model that consists of a one or more tables.
59. How does a Rank transformation handle string values?
Rank transformation can return a strings at the top or the bottom of the session sort order. When an Integration Service runs in a Unicode mode, it sorts a character data in a session using the selected a sort order associated with a Code Page of IS which may be French, German, etc. When an Integration Service runs in an ASCII mode, it ignores this setting and uses binary sort order to sort a character data.
60. Is sorter an active or a passive transformation?
The sorter is active transformation because when it configures a output rows, it discards a duplicates from a key and consequently changes the number of rows.
61. Mention types of transformations available in an Informatica?
- A Source Qualifier Transformation
- A Rank Transformation
- A Lookup and Reusable transformation
- A Router Transformation
- An Aggregator Transformation
- A Joiner transformation
- A Sequence Generator Transformation
- A Transaction Control Transformation
- A Performance Tuning for Transformation
- An Expression Transformation
- A Normalizer Transformation
- An External Transformation
62. What is difference between the active and passive transformation?
Based on change in a number of rows, an active transformations are those which are change the number of input and data rows passed to them. While a passive transformations remain a same for any number of input and output rows are passed to them.
63. What are output files created by a Informatica server at runtime?
Informatica Server log: Informatica home directory creates the log for all error messages and status.
Session log file: For every session, a session log file stores a data into the log file about ongoing initialization process, SQL commands, errors, and more.
Session detail file: It contains a load statistics for every target in mapping, including a data about the name of table, no of rows written or rejected.
Performance detail file: It includes a data about the session performance.
Reject file: Rows of data not written to the targets.
Control file: Information about a target flat-file and loading instructions to an external loader.
Post-session email: Automatically delivers a session run data to a designated recipients.
Indicator file: It contains number to indicate whether a row was marked for insert, delete or reject, and update.
Output file: Informatica server creates the target file based on details entered in a session property sheet.
Cache file: It automatically builds, when an Informatica server creates memory cache.
64. What is difference between the static cache and dynamic cache?
|A static cache is the default cache, and cannot be insert or update the caches
|In a dynamic cache, and can insert or update data into a lookup and pass the data to target
|Handles the multiple matches
|Doesn’t handles the multiple matches
|Suitable for a relational and flat file lookup types
|Suitable for a relational lookup types
|Relational operators are like = &= can be used
|Only = operator is to be used
|Used for the both connected and unconnected lookup a transformation
|Used for the only connected to lookup transformation
65. Can tell what types of groups does a router transformation contains?
Router transformation contains a following types of the transformations:
- Input group
- Output group
Further, output group contains a two types:
- User-defined groups
- Default group
66. How do differentiate stop and abort options in the workflow monitor?
|The stop option is used for an executing a session task and allows another task to run.
|The abort option turns off a task completely that is to be running.
|While using this option, an integration service stop reading a data from source of the file.
|Abort waits for a services to be completed, and then only an actions take place.
|Processes data to a targets or sources.
|Time out period of a 60 seconds.
|Stops a sharing resources from processes.
|Stops a process and session gets terminated.
67. Is it possible to store a previous session logs in Informatica?
- Yes, it is a possible. If any session is running in a timestamp mode, then automatically session logout will not be overwrite the current session log.
- Go to the Session Properties –> Config Object –> Log Options.
- Select a properties as follows.
- Save a session log by –> SessionRuns.
- Save a session log for these runs –> Change a number that want to save a number of log files (Default is 0).
- If want to save all of log files created by an every run, and then select the option Save a session log for these runs –> Session TimeStamp.
68. What know about a Data-Driven sessions?
- In Informatica, Data-Driven is a property that decides a way the data needs to perform when mapping includes Update strategy transformation.
- By mentioning a DD_INSERT or DD_DELETE or DD_UPDATE in an update strategy transformation, and can execute a data-driven sessions.
69. What is Mapplet in Informatica?
A reusable data object created in a Mapplet designer is called a Mapplet. It includes the collection of transformations that allows to reuse transformation logic in various mappings.
70. What is difference between the Mapping and Mapplet?
|A set of a source, target, and transformation.
|Collection of the transformations.
|They are not reusable and developed with the different transformation.
|They are more reusable.
71. List a transformations used for a SQL override?
- Source Qualifier
72. State a differences between the SQL override and Lookup override?
|Limits a no of rows that enter a mapping pipeline.
|Limits no of lookup rows for avoiding table scan and saves a lookup time.
|Manually enters a query .
|By default, an “Order By” clause .
|Supports any kind of join by writing a query.
|Supports a only Non-Equi joins.
73. What is shared cache?
A shared cache is the static lookup cache shared by a various lookup transformations in a mapping. Using shared cache reduces amount of time needed to build a cache.
74. Explain a code page compatibility?
Compatibility between the code pages used for getting an accurate data movement when an Informatica Server runs in a Unicode data movement mode. There won’t be of any data losses if code pages are be identical. One code page can be superset or subset of another.
75. Define an Expression transformation?
In Informatica, expression transformation is the passive transformation that allows to performing non-aggregate calculations on a source data. It means that can perform calculations on single row. Using this transformation, and can test a data before passing it to a target table or another transformation through the conditional statements.
76. What is an Aggregator transformation?
Aggregator Transformation in an Informatica is an active transformation that allows to perform calculations like a sum, average, etc. Can perform the aggregate operations over the group of rows, and it stores all estimates and records in the temporary placeholder called aggregator a cache memory.
77. What know about the filter transformation?
Filter transformation in an Informatica is an active transformation that changes a number of rows passed through it. It allows a rows to pass through it based on a specified filter conditions and drops rows that don’t meet requirement. The data can be filtered based on a one or more terms.
78. Why is the union transformation active?
In Informatica, union transformation is the active transformation because it combines a two or more data streams into one. The total no of rows passing through the Union is equal to no of rows passing out of it, and sequence of rows from an input stream preserved are same in the output stream, but cannot preserve a position of the rows.
79. What is use of an incremental aggregation in Informatica?
Incremental aggregation usually gets created when the session gets created through an execution of an application. This aggregation allows to capture changes in a source data for aggregating calculations in the session. If a source changes incrementally, and can capture those changes and configure a session to process them. It will allow to update a target incrementally, rather than deleting previous load data and recalculating similar data each time to run the session.
80. What doesa reusable transformation mean?
Reusable transformations are practiced a numerous times in the mapping. It is stored as a metadata and is various from other mappings that use transformation. If any changes are to be performed to reusable transformation, then all mappings where the transformation is used get nullified.
81. How does update a strategy work in Informatica?
The update strategy is an active and connected transformation that allows to insert, delete, or update records in a target table. Also, it restricts files from not reaching a target table.
82. Differentiate the Informatica and Datastage?
Both the Informatica and Datastage are powerful ETL tools. Still, a significant difference between both is an Informatica forces to organize in a step-by-step process. In a contrast, Datastage provides a flexibility in dragging and dropping objects based on a logic flow.
|A Dynamic partitioning.
|A Static partitioning.
|Supports a flat-file lookups.
|Supports a hash files, lookup file sets, etc.
|It has service-oriented architecture.
|It has client-server architecture.
|Step by step a data integration solution.
|A Project-based integration solutions.
83. Explain the transaction control transformation?
Transaction Control in an Informatica is an active and connected transformation that allows are committing or rollbacking transactions during mapping execution. A transaction is the collection of rows bound by commit or a rollback rows. Based on variation in no input rows, a transaction is explained . Commit or rollback operations ensure a data availability.The built-in variables are available in this transformation are:
84. State differences between the Informatica PowerCenter and Informatica PowerMart?
|Informatica a PowerCenter processes high volumes of the data.
|Informatica PowerMart processes the small volumes of data.
|Supports a data retrieval from ERPSources like the PeopleSoft, SAP, etc.
|Doesn’t support a data retrieval.
|Converts a local repositories to global.
|Doesn’t convert a local repositories to global.
|Supports the both global and local repositories.
|Supports only a local repositories.
85. What does update a strategy mean, and what are different option of it?
Row by row processing is a done by informatica. Each row is inserted in a target table because it is marked as a default. Update strategy is used whenever a row has to be updated or inserted based on a some sequence. Moreover condition must be specified in an update strategy for the processed row to be marked as a updated or inserted.
86. What is a scenario which compels an informatica server to reject files?
This happens when it faces the DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in a rows was condensed.
87. What are prerequisite tasks to achieve a session partition?
In order to perform a session partition one need to configure a session to partition source data and then installing an Informatica server machine in a multifold CPU’s.
88. Which files are created during a session rums by informatics server?
During a session runs, files created are namely Errors log, Bad file, Workflow low and session log.
89. What is meant by a pre and post session shell command?
Command task can be called as a pre or post session shell command for the session task. One can run it as a pre session command r post session success command or a post session failure command.
90. What is a predefined event?
It is the file-watch event. It waits for a particular file to arrive at specific location.
91. How can define a user defied event?
User defined event can be explained as a flow of tasks in a workflow. Events can be created and then raised as a need arises.
92. Tell any other tools for a scheduling purpose other than a workflow manager pmcmd?
The tool for a scheduling purpose other than a workflow manager can be a third party tool like ‘CONTROL M’.
93. What are the different types of a OLAP? Give an example?
ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP.
94. What is mean by a worklet?
When a workflow tasks are grouped in a set, it is called as a worklet. Workflow tasks are includes timer, decision, command, event wait, mail, session, link, assignment, control etc.
95. What is use of a target designer?
Target Definition is created with help of a target designer.
96. Where can find a throughput option in informatica?
Throughput option can be found in an informatica in workflow monitor. In a workflow monitor, right click on a session, then click on get run properties and under source/target statistics and can find a throughput option.
97. What is a target load order?
Target load order is a specified on the basis of a source qualifiers in a mapping. If there are multifold a source qualifiers linked to various targets then one can entitle order in which an informatica server loads data into a targets.
98. What is understand by a phrase Staging Area?
An organizing zone is where transitory a tables associated with a work territory are put away or reality tables to give contributions to information preparation.
99. Explain a tracing level?
Informatica tracing level means amount of data that an Informatica server writes to a session log file. It is an important component that is helpful in locating a bugs and error analysis for an every transformation.
100. Explain dimension?
Dimension tables are tables in the star schema of a data warehouse that contains a keys, values, and attributes of dimensions. A dimension table generally contains a description or textual information about facts contained within the fact table.
Are you looking training with Right Jobs?Contact Us
- Hadoop Interview Questions and Answers
- Apache Spark Tutorial
- Hadoop Mapreduce tutorial
- Apache Storm Tutorial
- Apache Spark & Scala Tutorial
- What is Dimension Reduction? | Know the techniques
- Difference between Data Lake vs Data Warehouse: A Complete Guide For Beginners with Best Practices
- What is Dimension Reduction? | Know the techniques
- What does the Yield keyword do and How to use Yield in python ? [ OverView ]
- Agile Sprint Planning | Everything You Need to Know