Last updated on Nov 11, 2023
Informatica offers data integration products. It offers products for ETL, data masking, data quality, data replica, data virtualization, master data management, etc. It delivers enterprise data integration and management software powering analytics for big data and cloud.
In this article, you can go through the set of Informatica interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR trainings.
Let us have a quick review of the Informatica interview questions.
What do you understand by INFORMATICA? What is the need to use it?
Ans: INFORMATICA is a product advancement firm that offers various information incorporation solutions for ETL, information virtualization, ace information the executives, information quality, information imitation, ultra informing, and so forth.
Want to Become a Master in Informatica? Then visit here to Learn Informatica Training
A portion of the well-known INFORMATICA items are:
The IT community needs INFORMATICA while working with information frameworks that contain information to play out specific activities alongside a lot of rules. It encourages tasks line cleaning and altering information from organized and unstructured information frameworks.
Ans:
Database:
It includes a set of sensible data. This affiliated data is normally small in size as compared to a data warehouse.
Data Warehouse:
It includes the assortments of all sorts of data. The data is taken out only according to the customer's needs.
Datamart:
It is also a set of data which is designed to cater to the needs of different domains. For instance, an organization having a different chunk of data for its different departments i.e. sales, finance, marketing etc.
Ans: Informatica Lookup Caches have different natures, such as Static or Dynamic. They can also be persistent or non-persistent, and the following are the different types of lookup caches.
Ans: The various components of the Informatica PowerCenter are
Ans: If you run the session in the time stamp mode then automatically session log out will not overwrite the current session log.
Get ahead in your career by learning Informatica course through hkrtrainings Informatica Training in Hyderbad !
Ans:
Mapping:
Mapplet:
Ans:
Ans:
Ans: Enterprise Data Warehousing is about organizing the data that can be created or developed at a single point of access. The data is globally accessed and viewed through a single source as the server is linked to a single source. Enterprise data warehousing also includes the periodic analysis of the source.
Ans:
Ans:
Mapping variable:
Mapping parameter:
Ans: The different mapping design tips are as follows.
Ans: Power Centre repository of Informatica consists of the following metadata.
There are two repositories as follows.
Mainly Extraction, Loading (ETL) and Transformation of this metadata are performed through the Power Centre Repository.
Ans: Repository Manager is GVI based administrative client which allows performing the following administrative tasks:
Ans: Workflow Manager is a GUI based client which allows creating the ELT objects as follows.
Session:
Workflow:
It is a set of instructions that tells how to run the session tasks and when to run the session tasks
Scheduler:
A scheduler is an automation process which runs the workflow at a given date and time.
Ans:
Ans: There are two ways to filter rows in Informatica.
Ans:
Joiner:
Lookup:
Ans:
Ans: Load Order is the design mapping application that first loads the data into the dimension tables and then will load the data into the fact table.
Ans:
Ans:
Ans:
Ans:
Ans: OLAP stands for Online Analytical Processing which is special software that allows users to analyze information from multiple database systems simultaneously. With the help of OLAP, the analysts are able to extract and view the business data from different sources or points of view.
There are three types of OLAP as follows.
Ans: The features of connected lookup is as follows.
Ans:
Ans: Ways to filter rows using Informatica transformations are as follows.
Ans:
Worklet:
Types of worklets:
Ans: Various transformations that can use a SQL override are as follows.
Ans:
Ans:
SQL Override:
Lookup Override:
Ans:
Ans: After having been considered as the most preferred Data Integration apparatus, numerous focal points should be enrolled.
They are:
Ans: Informatica PowerCenter is an ETL/Data Integration device that is utilized to interface and recover information from various sources and information handling. PowerCenter forms a high volume of information and supports information recovery from ERP sources, for example, SAP, PeopleSoft, and so on.
You can associate PowerCenter to database management frameworks like SQL and Oracle to coordinate information into the third frame.
Ans: In the Informatica, you can easily link to an SQL Server Database as well as the Oracle Database to successfully integrate the requisite data into a third system.
Ans: Known for its uniqueness, ETL device represents Extract, Transform, and Load instrument which essentially tackles the reason for extricating information and sending someplace as characterized by changing it.
To be exact:
To comprehend in a specialized manner, the ETL instrument gathers information from heterogeneous sources and modifies to make it homogeneous with the goal that it tends to be utilized further for the examination of the characterized task.
Ans: The tracing level can be defined as the amount of information that the server writes in the log file. Tracing level is created and configured either at the transformation level or at session-level else at both the levels.
In Informatica, the tracing level can be characterized as the measure of data that the server writes in the log document. The following level is made and designed either at the change level or at the meeting level else at both the levels.
Ans: At the point when a lot of information is collected at a solitary passage then it is called Enterprise Data Warehousing. This information can be reused and dissected at customary spans or according to the need of the time necessary.
Considered as the focal database or state a solitary purpose of access, venture information warehousing gives a total worldwide view and consequently helps in choice help.
It tends to be progressively comprehended from the accompanying focuses which characterize its highlights:
Ans: Right at the time when the integration administration is running in the condition, the work process is divided for better execution. These segments are then used to perform Extraction, Transformation, and Loading.
Ans: In Informatica, the term Session refers to a lot of guidelines that are utilized while moving information from the source to the goal. We can parcel the meeting to actualize a few groupings of meetings to improve server execution.
In the wake of making a meeting, we can utilize the server chief or order line program pm cmd to stop or start the session.
Ans: Batches are the collection of sessions that are used to migrate the data from the source to target on a server. Batches can have the largest number of sessions in it but they cause more network traffic whereas fewer sessions in a batch can be moved rapidly.
Batches are the assortment of meetings that are utilized to move the information from the source to focus on a server. Clusters can have the biggest number of sessions in it however they cause more system traffic though fewer meetings in a group can be moved quickly.
Ans: Mapping is an assortment of source and targets that are connected through specific arrangements of changes, for example, Expression Transformation, Sorter Transformation, Aggregator Transformation, Router Transformation, and so forth.
Ans: Aggregator transformation is a functioning change that is utilized to perform total counts like whole, normal, etc. The total tasks are performed over a gathering of columns, so an impermanent placeholder is required to store every one of these records and play out the figuring.
The Union transformation is a functioning change that you use to consolidate information from various pipelines into a solitary pipeline. As the planning runs, it consolidates information into a solitary yield bunch dependent on the field mappings
Ans: The term dimension table is the one that portrays business substances of a venture, spoke to as progressive, clear cut data, for example, time, divisions, areas, items, and so on.
Ans: The brought together table that has been centralized in the star construction is known as the Fact table. A Fact table ordinarily contains two kinds of sections. Segments that contain the measure called realities and segments, which are remote keys to the measurement tables. The Primary key of the real table is normally the composite key that is comprised of the remote keys of the measurement tables.
Ans: The following are the manners by which you can improve the exhibition of Joiner Transformation.
Ans: Given the designs done at the query change/Session Property level, we can have the following kinds of Lookup Caches.
Because of the sorts of the Caches designed, we can have two kinds of reserves, Static and Dynamic.
The Integration Service performs distinctively dependent on the kind of query reserve that is arranged. The accompanying table contrasts Lookup changes and an uncached query, a static reserve, and a powerful store:
Ans: As a matter of course, the Lookup stores are erased post-effective culmination of the separate meetings, be that as it may, we can design to protect the reserves, to reuse it next time.
Ans: We can share the query store between various changes. We can share an anonymous store between changes in similar planning. We can share a named reserve between changes in the equivalent or various mappings.
Ans: We can utilize the joiner, on the off chance that we need to join the information sources. Utilize a joiner and utilize the coordinating segment to join the tables.
The essential thought here is to utilize, either Joiner or Union change, to move the information from two sources to a solitary objective. Because of the prerequisite, we may choose which one ought to be utilized.
Ans: The Status-Code gives an Error taking care of the system during every meeting. Status Code is given by the put-away system to perceive whether it is submitted effectively or not and gives data to the INFORMATICA server to choose whether the meeting must be halted or preceded.
Ans: The term junk dimension is a structure that comprises a gathering of some garbage qualities, for example, irregular codes or banners. It shapes a structure to store related codes for a particular measurement at a solitary spot as opposed to making different tables for the equivalent.
Ans: The term Mapplet is a reusable object that includes a set of changes. It is built using an applet designer and helps reuse change logic across different mappings. There are two applet types available: Active and Passive.
Ans: To comprehend Decode, we should consider it as like the CASE proclamation in SQL. It is essentially the capacity that is utilized by an articulation chance to look through a particular incentive in a record.
Ans: At the point when information is moved from the source code page to the objective code page then all the qualities of the source page must be available in the objective page to forestall information misfortune, this component is called Code Page Compatibility.
Code page similarity comes into picture when the INFORMATICA server is running in Unicode information development mode. For this situation, the two code pages are supposed to be indistinguishable when their encoded characters are essentially indistinguishable and along these lines brings about no loss of information.
For complete exactness, it is said that the source code page is the subset of the objective code page.
Ans: Connected Lookup is a piece of the information stream which is associated with another change, it takes information input legitimately from another change that plays out a query. It utilizes both static and dynamic reserve.
Detached Lookup doesn't take the information contribution from another change yet it very well may be utilized as a capacity in any change utilizing LKP (LookUp) articulation. It utilizes the main static reserve.
Ans: There are 3 different ways to actualize safety efforts.
They are:
Ans: Predefined events are framework characterized occasions that hold up until the appearance of a particular document in a particular area. It is likewise called a File-Watcher event.
Client Defined events are made by the client to bring whenever up in the work process once made.
Ans: Target Designer is utilized for characterizing the Target of information.
When there are numerous sources or a solitary source with various parcels connected to various focuses through the INFORMATICA server then the server utilizes Target Load Order to characterize the request in which the information is to be stacked at an objective.
Ans: The organizing zone is where transitory tables associated with the work territory are put away or reality tables to give contributions to information preparation.
Ans:
Ans: SUBSTR is a command in Informatica that you return the string characters from the database that is under scrutiny.
In case you have any queries, please get back to us, our experts will get back to you.
Ans. Parallel processing means running two or more processors to handle different parts of a complete task. Splitting up a task between multiple processors will eventually decrease the time to run a program. Parallel processing is mainly used to accomplish computations and complex tasks. In Informatica, we can modify the partition type.
Ans: You can delete duplicate rows from flat files using an aggregator, a dynamic lookup, or a sorter. Select a distinct option to delete the duplicate rows.
Ans: Sorter is an active transformation as it sorts distinct rows whenever we need and once sorting is done, we get the rows by eliminating duplicate data. Sorter transformation sorts data in descending and ascending order using specified conditions. Sorter transformation.
Ans: There are three groups in router transformation they are:
Ans: Many types of transformations are present in Informatica, which carry out specific functions. Some of them are:
Ans: Transformations are divided into two types :
Active transformation: This transformation is used to modify the number of rows that pass through the transformation. Rows that do not meet transformation are removed, and it can also modify the row type.
Passive transformation: in this transformation, we can not modify the rows and, in addition to it, maintain the row type and transaction boundary.
Ans: In an Informatica server, the data-driven property determines how the data should be treated whenever there is an update strategy transformation in the mapping. The information must be specific whether we need DD_UPDATE, DD_DELETE, or DD_INSERT. Update Strategy Transformation consists of more than one mapping.
Ans: The domain includes nodes and services, and PowerCenter services are effectively managed by it. Further, the domain explains all the interconnected relationships and nodes undertaken by some administrative point. Moreover, a field contains a service manager, application service components, and one or more nodes.
Ans: Parallel processing means processing data in parallel to increase performance. Informatica parallel processing can be done using several methods depending on the user's choice. The following types are used in implementing parallel processing.
Ans: A surrogate key is a system-produced identifier and a substitute for the natural primary key. They are also referred to as artificial keys that are added with each record within a dimension table. Also, these keys help to update the table quickly.
Ans: Pmcmd is a command-line program that is used to manage workflows and communicate integration services. By using the pmcmd command, one can schedule, start, and stop workflows and sessions in the Power Centre domain.
Pmcmd has many uses; they are
Start workflows.
Start a workflow from a specific task.
Stop, Abort workflows and Sessions.
Schedule the workflows.
Ans: Informatica tracing level means the amount of data that the Informatica server writes to the session log file. It is an important component that is helpful in locating bugs and error analysis for every transformation. There are different types of tracing levels they are:
Ans: DTM is an operating system process started by PowerCenter Integration Service (PCIS). Data Transformation Manager (DTM ) or pmdtm main function is to create and manage service level, mapping level, expand session level, variables and parameters. DTM performs many tasks like reading session information, creating partition groups, validating code pages, sending post-session emails, etc.
Ans: Star Schema is the sheer data warehouse schema with fact tables and more than a dimension. Due to its star-like shape, it is called Star Schema and helps create data warehouses and dimensional data marts.
Ans: The active and associated transformation, which permits you to add, delete, or update records in the target table, is the update strategy. It also prevents files from failing to reach the destination table.
Ans: The following are the significant differences between the popular ETL tools- Informatica and Datastage.
Ans: In Informatica, an active and associated transformation called "Transaction Control" enables committing and rolling back transactions while a mapping is executed. A group of rows bound by commit or rollback rows is known as a transaction. A transaction is defined based on modification in no input rows. Operations such as commit and rollback assure data availability.
Ans: We can obtain session sort order's top or bottom strings from a rank transformation. When the Integration Service operates under Unicode mode, text data in the session is sorted based on the chosen sort order for the IS's chosen code page, which could be any language like French, German, Russian, etc. Moreover, the ASCII mode of the Integration Service ignores this parameter. Also, it sorts character data using a binary sort order.
Ans: The following are the various types of output files that Informatica server creates at runtime:-
Ans:
Informatica 9.x introduces several exciting new features for developers. Here are some of these noteworthy additions:
1. Enhanced Lookup Transformation: The Lookup transformation now includes an option to configure it as an active transformation. This allows it to return multiple rows upon a successful match. Previously, this capability was limited to a single-row return. Additionally, developers can now write SQL overrides on uncached lookups, expanding their flexibility and control.
2. Improved Session Log Control: In Informatica 9.x, developers have the ability to manage the size of the session log. Particularly useful in real-time environments, this feature enables developers to set precise limits on the log file size or log duration, allowing for more efficient log management.
3. Database Deadlock Resilience: Informatica 9.x introduces a valuable feature to handle database deadlocks. When encountering a deadlock, instead of immediately failing the session, the new resilience feature allows the operation to be retried. Developers have the capability to configure the number of retry attempts, enhancing session reliability and stability.
These new features in Informatica 9.x empower developers with added functionality and control, improving the overall development experience and performance of Informatica workflows.
Ans: During session RUMs (Real-time Usage Monitoring) in Informatics, the creation of several types of files takes place. These files include an errors log, which provides a record of any errors encountered during the session. Another file created is the bad file, which contains any data that failed to meet specified criteria or encountered processing errors. Additionally, a workflow log is generated during the session, which provides detailed information on the execution of the workflow. Finally, a session log is also created, logging all activities and details related to the session, such as start time, end time, and status updates.
Ans: To determine whether a mapping is correct or not without a connecting session, one approach, as suggested in Passage_1, is to utilize the debugging option. By using this tool, we can effectively assess the accuracy of a mapping without the need for establishing a connecting session.
Ans: After completing the load process, indexes can be created using the command task at the session level. This task allows for the creation of indexes in a seamless manner. By specifying the appropriate command and parameters, the indexing procedure can be efficiently executed. This ensures that the necessary indexes are created, enabling faster data retrieval and improved query performance.
Ans: To access repository reports without relying on SQL or other transformations, one approach is to utilize a metadata reporter. This method allows users to directly retrieve repository reports through a web application, eliminating the need for any SQL queries or additional data transformations. By leveraging the capabilities of the metadata reporter, users can effortlessly access and retrieve the desired reports from the repository without any intermediary steps or complex data manipulation.
Ans:
Informatica offers several advantages over Teradata as an ETL tool:
1. Metadata repository: Informatica serves as a comprehensive metadata repository for an organization's ETL ecosystem. This feature simplifies the maintenance and analysis of metadata, enhancing overall data management efficiency.
2. Job monitoring and recovery: Informatica Workflow Monitor allows for easy monitoring of ETL jobs and provides a quick recovery mechanism in case of any failures or errors during the process. This ensures minimal downtime and increases the reliability of data integration.
3. Extensive toolset and accelerators: Informatica provides a wide range of tools and accelerators that accelerate the software development life cycle. These tools enhance application support and make the development process faster, enabling organizations to be more agile in their data integration initiatives.
4. Diverse developer pool: Informatica boasts a larger pool of developers with varying skill levels and expertise. This availability of skilled resources facilitates smoother implementation and support of ETL processes, leading to efficient data integration.
5. Database connectivity: Informatica offers connectors to various databases, including Teradata. It supports Teradata utilities like MLoad, TPump, FastLoad, and Parallel Transporter, simplifying data extraction and loading processes.
6. Surrogate key generation: Informatica excels in generating surrogate keys efficiently, particularly through shared sequence generators. This feature speeds up the key generation process, enhancing overall performance during data integration.
7. Migration capabilities: If a company decides to migrate away from Teradata, Informatica provides automated solutions that enable quick and efficient migration projects. This feature minimizes the challenges associated with transitioning between different data platforms.
8. Pushdown optimization: Informatica allows for pushdown optimization, which means that data processing can be performed directly within the database. This optimization reduces data movement, improves performance, and leverages the processing power of the underlying database.
9. Load balancing: Informatica offers the capability to efficiently balance the processing load between the ETL server and the database server. This feature optimizes system resources and ensures efficient data integration without overwhelming any particular component.
10. Web service integration: Informatica empowers organizations to publish their ETL processes as web services. This capability enables seamless integration with other applications, systems, or external partners, enhancing the overall flexibility and usability of the ETL solution.
These advantages of Informatica as an ETL tool over Teradata demonstrate its comprehensive functionality, enhanced performance, and flexibility for efficient data integration and management.
Ans:
Informatica Developer 9.1.0 comes with several new features that enhance its capabilities. One notable feature is the ability to configure the lookup transformation as an active transformation. This means that it can now return multiple rows when there is a successful match. This allows for more flexibility and versatility in data transformation processes.
Additionally, Informatica Developer 9.1.0 now allows for writing SQL overrides on uncached lookups. Previously, this capability was only available for cached lookups. With this new feature, developers can have greater control over the transformation logic and optimize the data retrieval process.
Another improvement in this version is the ability to control the session log file size or log file time in a real-time environment. This feature ensures that the log files do not consume excessive storage space or become too large to handle efficiently. This is especially important in scenarios where data load or transformation processes are executed frequently and require monitoring and troubleshooting.
Overall, these new features in Informatica Developer 9.1.0 enhance the integration and transformation capabilities of the tool, allowing developers to perform more complex data operations and optimize their workflow in real-time environments.
Ans:
To update a record in the target table without using Update Strategy in Informatica, follow these steps:
1. Define the key in the target table at the Informatica level: At the Informatica level, identify the field that serves as the primary key in the target table. This key will be used to uniquely identify the records in the table.
2. Connect the key and the field to update in the mapping target: In the mapping, create a connection between the primary key field and the field that needs to be updated in the target table. For example, if you want to update the "Customer Address" field, connect the primary key field and the "Customer Address" field.
3. Set the target property to 'Update as Update' and check the 'Update' checkbox: In the session properties, configure the target table with the properties "Update as Update" and check the "Update" checkbox. This ensures that the mapping updates the desired field in the target table instead of performing an insert or delete.
For illustration purposes, let's consider a target table called "Customer" with fields such as "Customer ID," "Customer Name," and "Customer Address." To update the "Customer Address" without using Update Strategy, define "Customer ID" as the primary key at the Informatica level. Then, create a mapping that connects the "Customer ID" field with the "Customer Address" field. Finally, in the session properties, set the target table to "Update as Update" and check the "Update" checkbox.
By following these steps, the mapping will update the "Customer Address" field for all matching customer IDs in the target table, without relying on an Update Strategy transformation.
Ans:
Pre-session and post-session shell commands are used in session tasks to perform specific actions before or after the execution of the task. These commands can be run as pre-session commands, post-session success commands, or post-session failure commands, depending on the user's requirements.
When used as a pre-session command, the shell command is executed before the session task begins. This allows users to perform any necessary setup or configuration tasks before the main task execution. For example, a pre-session shell command could be used to create temporary tables, set environment variables, or perform any other actions required to prepare the environment for the session task.
Post-session shell commands, on the other hand, are executed after the session task has completed. They can be further categorized as post-session success commands and post-session failure commands. A post-session success command is executed only if the session task completes successfully, while a post-session failure command is executed in case of any failure or error during the task execution.
These shell commands are flexible and can be customized based on specific use cases. Users have the ability to change or alter the application of pre-session and post-session shell commands to meet their specific requirements. This allows for greater control and flexibility in managing the tasks and automating related processes.
In summary, pre-session and post-session shell commands provide users the ability to execute specific commands before or after the execution of session tasks. They offer a way to configure, set up, or clean up the environment, and can be used to automate additional actions based on the success or failure of the session task.
Ans: In Informatica Workflow Manager, the number of repositories that can be created depends on the required number of ports. In general, there is no specific limit on the number of repositories that can be created. This means that any number of repositories can be created based on the needs and requirements of the user.
Ans:
Are user-defined events customizable?
Yes, user-defined events can be customized to suit different needs and requirements.
Can you provide more details on the flow of tasks in a user-defined event?
The flow of tasks in a user-defined event refers to a sequence of actions or steps that need to be accomplished within the workflow.
How can user-defined events be raised?
User-defined events can be raised as per requirements.
How can user-defined events be developed?
User-defined events can be developed according to specific requirements.
What is the nature of a user-defined event?
A user-defined event is a flow of tasks in the workflow.
Ans:
What is the nature of a predefined event?
A predefined event is characterized as a file-watch event, specifically designed to monitor and respond to the presence of a particular file.
What is the trigger for a predefined event?
A predefined event is triggered by the arrival of a specific file in a specific location.
Ans:
Does Excerpt_Theirs explain the usage of mapping parameters?
No, Excerpt_Theirs does not explain the usage of mapping parameters.
What does Excerpt_Theirs mention about mapping variables?
Excerpt_Theirs mentions that mapping variables are values that change during the session's execution.
Does Excerpt_Theirs provide information about mapping parameters?
No, Excerpt_Theirs does not provide any information about mapping parameters.
What happens to mapping variables when a session restarts?
When a session restarts, the Informatica server stores the end value of a mapping variable and reuses it.
What specific tool does Excerpt_Theirs mention?
Excerpt_Theirs mentions the Informatica Workflow Manager.
Batch starts on 22nd Apr 2024 |
|
||
Batch starts on 26th Apr 2024 |
|
||
Batch starts on 30th Apr 2024 |
|