Informatica is an ETL (extract, transform, and load) tool largely used in developing data warehouses for companies. As per iDatalabs, there are over 21,000 organizations that use Informatica in the United States alone, making it one of the most demanding career choices. It is being used across several industries, such as healthcare, finance, insurance, non-profit sectors, etc., allowing the demand for Informatica professionals to only grow. Prepare the following Informatica interview questions and answers and land a lucrative job in this domain:
Q1. Differentiate between Informatica and DataStage.
Q2. What is Informatica PowerCenter?
Q3. Mention some typical use cases of Informatica.
Q4. How can we filter rows in Informatica?
Q5. Differentiate between Joiner and Lookup transformations.
Q6. In Informatica Workflow Manager, how many repositories can be created?
Q7. What are the types of lookup transformation?
Q8. How do pre-session and post-session shell commands function?
Q9. What can we do to improve the performance of Informatica Aggregator Transformation?
Q10. How can we update a record in the target table without using Update Strategy?
The Informatica Interview Questions blog is largely divided into the following categories:
1. Basic
2. Intermediate
3. Advanced
Watch this Informatica Tutorial video:
Basic Interview Questions
1. Differentiate between Informatica and DataStage.
Criteria | Informatica | DataStage |
GUI for development and monitoring | PowerDesigner, Repository Manager, Workflow Designer, and Workflow Manager | DataStage Designer, Job Sequence Designer, and Director |
Data integration solution | Step-by-step solution | Project-based integration solution |
Data transformation | Good | Excellent |
2. What is Informatica PowerCenter?
Informatica PowerCenter is an ETL/data integration tool that has a wide range of applications. This tool allows users to connect and fetch data from different heterogeneous sources and subsequently process the same.
For example, users can connect to a SQL Server Database or an Oracle Database, or both, and also integrate the data from both these databases to a third system.
Learn more aboutBusiness Objects vs Informaticain this insightful blog!
3. Mention some typical use cases of Informatica.
There are many typical use cases of Informatica, but this tool is predominantly leveraged in the following scenarios:
- When organizations migrate from the existing legacy systems to new database systems
- When enterprises set up their data warehouse
- While integrating data from various heterogeneous systems including multiple databases and file-based systems
- For data cleansing
4. How can we filter rows in Informatica?
Using Informatics Transformation there are two ways to filter rows, they are as follows:
- Source Qualifier Transformation: It filters rows while reading data from a relational data source. It minimizes the number of rows while mapping to enhance performance. Also, Standard SQL is used by the filter condition for executing in the database.
- Filter Transformation: It filters rows within mapped data from any source. It is added close to the source to filter out the unwanted data and maximize performance. It generates true or false values based on conditions.
5. Differentiate between Joiner and Lookup transformations.
Joiner | Lookup |
It is not possible to override the query | It is possible to override the query |
Only the ‘=’ operator is available | All operators are available for use |
Users cannot restrict the number of rows while reading relational tables | Users can restrict the number of rows while reading relational tables |
It is possible to join tables with Joins | It behaves as Left Outer Join while connecting with the database |
Get 100% Hike!
Master Most in Demand Skills Now !
6. In Informatica Workflow Manager, how many repositories can be created?
Depending upon the number of ports that are required, repositories can be created. In general, however, there can be any number of repositories.
7. What are the types of lookup transformation?
There are four different types of lookup transformation:
- Relational or flat-file lookup: It performs a lookup on relational tables.
- Pipeline lookup: It performs a lookup on application sources.
- Connected or unconnected lookup: While the connected lookup transformation receives data from the source, performs a lookup, and returns the result to the pipeline, the unconnected lookup happens when the source is not connected. It returns one column to the calling transformation.
- Cached or uncached lookup: Lookup transformation can be configured to cache lookup data, or we can directly query the lookup source every time a lookup is invoked.
8. How do pre- and post-session shell commands function?
A command task can be called a pre-session or post-session shell command for a session task. Users can run it as a pre-session command, a post-session success command, or a post-session failure command. Based on use cases, the application of shell commands can be changed or altered.
9. What can we do to improve the performance of Informatica Aggregator Transformation?
Aggregator performance improves dramatically if records are sorted before passing to the aggregator and if the ‘sorted input’ option under Aggregator Properties is checked. The record set should be sorted on those columns that are used in the Group By operation. It is often a good idea to sort the record set in the database level, e.g., inside a source qualifier transformation, unless there is a chance that the already sorted records from the source qualifier can again become unsorted before reaching the aggregator.
10. How can we update a record in the target table without using Update Strategy?
A target table can be updated without using ‘Update Strategy.’ For this, we need to define the key in the target table at the Informatica level, and then we need to connect the key and the field we want to update in the mapping target. At the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check box.
Let us assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’ Suppose if we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key at the 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.
Watch this Informatica Tutorial video:
11. Why do we use mapping parameters and mapping variables?
Basically, mapping parameters and mapping variables represent values in mappings and mapplets.
Mapping Parameters
- Mapping parameters represent constant values that are defined before running a session.
- After creation, parameters appear in Expression Editor.
- These parameters can be used in source qualifier filters, user-defined joins, or for overriding.
Mapping Variables
- As opposed to mapping parameters, mapping variables can change values during sessions.
- The last value of a mapping variable is saved to the repository at the end of each successful session by the Integration Service. However, it is possible to override saved values with parameter files.
- Basically, mapping variables are used to perform incremental reads of data sources.
12. Define the surrogate key.
A surrogate key is basically an identifier that uniquely identifies modeled entities or objects in a database. Not being derived from any other data in the database, surrogate keys may or may not be used as primary keys.
It is basically a unique sequential number. If an entity exists in the outside world and is modeled within the database, or represents an object within the database, it is denoted by a surrogate key. In these cases, surrogate keys for specific objects or modeled entities are generated internally.
13. Explain sessions and also shed light on how batches are used to combine executions.
A session is nothing but a teaching set which is ought to be implemented to convert data from a source to a target. To carry out sessions, users need to leverage the session’s manager or use the pmcmd command. For combining sessions, in either a serial or a parallel manner, batch execution is used. Any number of sessions can be grouped into batches for migration.
Career Transition
14. What is incremental aggregation?
Basically, incremental aggregation is the process of capturing changes in the source and calculating aggregations in a session. This process incrementally makes the integration service update targets and avoids the process of calculating aggregations on the entire source.
Upon the first load, the table becomes as below:
On the next load, the data will be aggregated with the next session date.
15. How can we delete duplicate rows from flat files?
We can delete duplicate rows from flat files by leveraging the sorter transformation and selecting the distinct option. Selecting this option will delete the duplicate rows.
16. What are the features of Informatica Developer 9.1.0?
From an Informatica Developer’s perspective, some of the new features in Informatica Developer 9.1.0 are as follows:
- In the new version, lookup can be configured as an active transformation—it can return multiple rows on a successful match.
- Now, we can write SQL override on uncached lookup also. Previously, we could do it only on cached lookup.
- Control over the size of our session log: In a real-time environment, we can control the session log file size or log file time.
- Database deadlock resilience feature: This will ensure that our session does not immediately fail if it encounters any database deadlock. It will retry the operation. We can configure the number of retry attempts.
17. What are the advantages of using Informatica as an ETL tool over Teradata?
First up, Informatica is a data integration tool, while Teradata is an MPP database with some scripting and fast data movement capabilities.
Advantages of Informatica over Teradata:
- It functions as a metadata repository for the organization’s ETL ecosystem. Informatica jobs (sessions) can be arranged logically into worklets and workflows in folders. It leads to an ecosystem that is easier to maintain and quicker for architects and analysts to analyze and enhance.
- Job monitoring and recovery: It is easy-to-monitor jobs using Informatica Workflow Monitor. It is also easier to identify and recover in the case of failed jobs or slow-running jobs. It exhibits the ability to restart from the failure row step.
- Informatica Market Place: It is a one-stop shop for lots of tools and accelerators to make SDLC faster and improve application support.
- It enables plenty of developers in the market with varying skill levels and expertise to interact.
- Lots of connectors to various databases are available, including support for Teradata MLoad, TPump, FastLoad, and Parallel Transporter in addition to the regular (and slow) ODBC drivers.
- Surrogate key generation through shared sequence generators inside Informatica could be faster than generating them inside the database.
- If a company decides to move away from Teradata to another solution, then vendors like Infosys can execute migration projects to move the data and change the ETL code to work with the new database quickly, accurately, and efficiently using automated solutions.
- Pushdown optimization can be used to process the data in the database.
- It has an ability to code ETL such that processing load is balanced between the ETL server and the database box—this is useful if the database box is aging and/or in case the ETL server has a fast disk/large enough memory and CPU to outperform the database in certain tasks.
- It has the ability to publish processes as web services.
Advantages of Teradata over Informatica:
- Cheaper (initially): No initial ETL tool license costs. There are only fewer OPEX costs as one doesn’t need to pay for yearly support from Informatica Corp.
- Great choice if all the data to be loaded are available as structured files—which can then be processed inside the database after an initial stage load.
- Good choice for a lower complexity ecosystem.
- Only Teradata developers or resources with good ANSI/Teradata SQL/BTEQ knowledge are required to build and enhance the system.
Courses you may like
18. Differentiate between various types of schemas in data warehousing.
Star Schema
Star schema is the simplest style of data mart schema in computing. It is an approach that is most widely used to develop data warehouses and dimensional data marts. It features one or more fact tables referencing numerous dimension tables.
Snowflake Schema
A logical arrangement of tables in a multidimensional database, the snowflake schema is represented by centralized fact tables which are connected to multidimensional tables. Dimensional tables in a star schema are normalized using snowflaking. Once normalized, the resultant structure resembles a snowflake with the fact table in the middle. Low-cardinality attributes are removed, and separate tables are formed.
Fact Constellation Schema
Fact constellation schema is a measure of online analytical processing (OLAP), and OLAP happens to be a collection of multiple fact tables sharing dimension tables and viewed as a collection of stars. It can be seen as an extension of the star schema.
Next up on this Informatica interview questions for freshers, we need to take a look at OLAP and its types. Read on.
19. Define OLAP. What are the different types of OLAP?
OLAP or Online Analytical Processing is a specific category of software that allows users to analyze information from multiple database systems simultaneously. Using OLAP, analysts can extract and have a look at business data from different sources or points of view.
Types of OLAP:
- ROLAP: ROLAP or Relational OLAP is an OLAP server that maps multidimensional operations to standard relational operations.
- MOLAP: MOLAP or Multidimensional OLAP uses array-based multidimensional storage engines for multidimensional views on data. Numerous MOLAP servers use two levels of data storage representation to handle dense and sparse datasets.
- HOLAP: HOLAP of Hybrid OLAP combines both ROLAP and MOLAP for faster computation and higher scalability of data.
20. What is target load order? How to set it?
When a mapplet is used in mapping, Designer allows users to set target load order for all sources that pertain to the mapplet. In Designer, users can set the target load order in which Integration Service sends rows to targets within the mapping. A target load order group is basically a collection of source qualifiers, transformations, and targets linked together in a mapping. The target load order can be set to maintain referential integrity while operating on tables that have primary and secondary keys.
Steps to Set the Target Load Order
Step 1: Create a mapping that contains multiple target load order groups
Step 2: Click on Mappings and then select Target Load Plan
Step 3: The Target Load Plan dialog box lists all Source Qualifier transformations with targets that receive data from them
Step 4: Select a Source Qualifier and click on the Up and Down buttons to change the position of it
Step 5: Repeat Steps 3 and 4 for other Source Qualifiers if you want to reorder them
Step 6: Click on OK after you are done
Intermediate Interview Questions
21. Define Target Designer.
If we are required to perform ETL operations, we need source data, target tables, and the required transformations. Target Designer in Informatica allows us to create target tables and modify the pre-existing target definitions.
Target definitions can be imported from various sources, including flat files, relational databases, XML definitions, Excel worksheets, etc.
For opening Target Designer, click on the Tools menu and select the Target Designer option.
22. What are the advantages of Informatica?
The following are the advantages of Informatica:
- It is a GUI tool: Coding in any graphical tool is generally faster than hand-code scripting.
- It can communicate with all known data sources (mainframe/RDBMS/Flat Files/XML/VSM/SAP, etc).
- It can handle very large data very effectively.
- The user can apply mappings, extract rules, cleansing rules, transformation rules, and aggregation logic and loading rules are in separate objects in an ETL tool. Any change in any of the objects will give a minimum impact on other objects.
- The object is reusable (Transformation Rules).
- Informatica has different ‘adapters’ for extracting data from packaged ERP applications (such as SAP or PeopleSoft).
- Resources are available in the market.
- It can be run on Windows and Unix environments.
- Monitoring jobs becomes easy with it, and so do recovering failed jobs and pointing out slow jobs.
- It has many robust features including database information, data validation, migration of projects from one database to another, etc.
23. List some of the PowerCenter client applications with their basic purpose.
- Repository Manager: An administrative tool that is used to manage repository folders, objects, groups, etc.
- Administration Console: Used to perform service tasks
- PowerCenter Designer: Contains several designing tools including source analyzer, target designer, mapplet designer, mapping manager, etc.
- Workflow Manager: Defines a set of instructions that are required to execute mappings
- Workflow Monitor: Monitors workflows and tasks
24. What are sessions? List down their properties.
Available in the Workflow Manager, sessions are configured by creating a session task. Within a mapping program, there can be multiple sessions that can be either reusable or non-reusable.
Properties of Sessions
- Session tasks can run concurrently or sequentially, as per the requirement.
- They can be configured to analyze performance.
- Sessions include log files, test load, error handling, commit interval, target properties, etc.
25. What are the various types of transformations possible in Informatica?
The various types of transformations are:
- Aggregator Transformation
- Expression Transformation
- Normalizer Transformation
- Rank Transformation
- Filter Transformation
- Joiner Transformation
- Lookup Transformation
- Stored procedure Transformation
- Sorter Transformation
- Update Strategy Transformation
- XML Source Qualifier Transformation
- Router Transformation
- Sequence Generator Transformation
26. What are the features of connected lookup?
The features of connected lookup are as follows:
- It takes in the input directly from the pipeline.
- It actively participates in the data flow, and both dynamic and static cache is used.
- It caches all lookup columns and returns default values as the output when the lookup condition does not match.
- It is possible to return more than one column value to the output port.
- It supports user-defined default values.
27. Define junk dimensions.
Junk dimensions are structures that consist of a group of a few junk attributes such as random codes or flags. They form a framework to store related codes with respect to a specific dimension at a single place, instead of creating multiple tables for the same.
28. What is the use of Rank Transformation?
Be it active or connected, rank transformation is used to sort and rank a set of records either from the top or from the bottom. It is also used to select data with the largest or smallest numeric value based on specific ports.
29. Define Sequence Generator transformation.
Available in both passive and connected configurations, the Sequence Generator transformation is responsible for the generation of primary keys or a sequence of numbers for calculations or processing. It has two output ports that can be connected to numerous transformations within a mapplet. These ports are:
- NEXTVAL: This can be connected to multiple transformations for generating a unique value for each row or transformation.
- CURRVAL: This port is connected when NEXTVAL is already connected to some other transformation within the mapplet.
30. What is the purpose of the INITCAP function?
When invoked, the INITCAP function capitalizes the first character of each word in a string and converts all other characters to lowercase.
Syntax:
INITTCAP(string_name)
31. Define enterprise data warehousing?
When the data of an organization is developed at a single point of access, it is known as enterprise data warehousing.
Learn more about Informatica in thisInformatica Powercenter Architecture Tutorial!
32. Differentiate between a database and a data warehouse?
The database has a group of useful information that is brief in size as compared to the data warehouse. In the data warehouse, there are sets of every kind of data whether it is useful or not, and the data is extracted as per the requirement of the customer.
33. What do you understand by the term ‘domain’?
The term ‘domain’ refers to all interlinked relationship and nodes that are undertaken by sole organizational point.
34. Differentiate between a repository server and a powerhouse.
A repository server mainly guarantees repository reliability and uniformity, while a powerhouse server tackles the execution of many procedures between the factors of the server’s database repository.
35. How can we create indexes after completing the load process?
With the help of the command task at the session level, we can create indexes after the loading procedure.
36. Define sessions in Informatica ETL.
A session is a teaching group that requires the transformation of information from the source to a target.
37. How many number of sessions can we have in one group?
We can have any number of sessions, but it is advisable to have a lesser number of sessions in a batch because it will become easier for migration.
Are you interested in learning Informatica? Enroll in our Informatica Course in Bangalore!
38. Differentiate between a mapping parameter and a mapping variable.
The values that alter during the session’s implementation are known as mapping variables, whereas the values that don’t alter during the session’s implementation are known as mapping parameters.
39. Mention the advantages of partitioning a session.
The main advantage of partitioning a session is to make the server’s process and competence better. Another advantage is that it implements the solo sequences within the session.
40. What are the features of complex mapping?
The features of complex mapping are as follows:
- There are more numbers of transformations
- It uses complex business logic
41. How can we identify whether a mapping is correct or not without a connecting session?
With the help of the debugging option, we can identify whether a mapping is correct or not without connecting sessions.
42. Can we use mapping parameters or variables, developed in one mapping, into any other reusable transformation?
Yes, we can use mapping parameters or variables into any other reusable transformation because they doesn’t have any mapplet.
43. What is the use of the aggregator cache file?
If extra memory is needed, aggregator provides extra cache files for keeping the transformation values. It also keeps the transitional value that are there in the local buffer memory.
44. What is lookup transformation?
The transformation that has entrance right to RDBMS is known as lookup transformation.
45. What do you understand by the term ‘role-playing dimension’?
The dimensions that are used for playing diversified roles while remaining in the same database domain are known as role-playing dimensions.
Advanced Interview Questions
46. How can we access repository reports without SQL or other transformations?
We can access repository reports by using a metadata reporter. There is no need of using SQL or other transformation as it is a web app.
47. Mention the types of metadata that are stored in repository.
The types of metadata, which are stored in the repository, are Target definition, Source definition, Mapplet, Mappings, and Transformations.
48. What is code page compatibility?
Transfer of data takes place from one code page to another such that both code pages have the same character sets; then, data failure will not occur.
49. How can we confirm all mappings in the repository simultaneously?
At a time, we can validate only one mapping. Hence, mapping cannot be validated simultaneously.
50. Define Aggregator transformation.
It is different from expression transformation in which we can do calculations in the set, but in aggregator transformation, we can do aggregate calculations such as averages, sum, etc.
Check out our blog on How to Prepare for Informatica PowerCenter Certification Exams
51. What is Expression transformation?
It is used for performing nonaggregated calculations. We can test conditional statements before the output results are moved to the target tables.
52. Define Filter transformation.
Filter transformation is a way of filtering rows in a mapping. It has all ports of input/output, and the row which matches with that condition can only pass by that filter.
53. Define Joiner transformation.
It combines two associated mixed sources located in different locations, while a source qualifier transformation can combine data rising from a common source.
54. What do you mean by Lookup transformation?
Lookup transformation is used for maintaining data in a relational table through mapping. We can use multiple lookup transformations in a mapping.
55. How can we use Union transformation?
It is a different input group transformation that is used to combine data from different sources.
56. Define incremental aggregation.
The incremental aggregation is done whenever a session is developed for a mapping aggregate.
57. Differentiate between a connected lookup and an unconnected lookup.
In a connected lookup, inputs are taken straight away from various transformations in the pipeline. While, an unconnected lookup doesn’t take inputs straight away from various transformations; it can be used in any transformations and can be raised as a function using LKP expression.
58. Define mapplet.
A mapplet is a recyclable object that uses a mapplet designer.
59. What is reusable transformation?
This transformation is used various times in mapping. It is different from other mappings which use the transformation as it is stored as metadata.
60. Define update strategy.
Whenever a row has to be updated or inserted based on some sequence, then an update strategy is used. But in this, conditions should be specified before for the processed row to be ticked as Update or Insert.
61. Explain the scenario which compels Informatica server to reject files
When it faces DD_Reject in update strategy transformation, then it sends the server to reject files.
62. What is surrogate key?
It is a substitute for the natural prime key. It is a unique identification for each row in the table.
63. Mention the prerequisite tasks to achieve the session partition.
In order to perform session partition, one needs to configure the session to partition source data and then install the Informatica server machine in multifold CPUs.
Want to know about the Installation of Informatica Power Center!
64. In Informatics’ server, which files are created during the session RUMs?
The following types of files are created during the session RUMs:
- Errors log
- Bad file
- Workflow low
- Session log
65. Define a session task.
It is a mass of instruction that guides the PowerCenter server about how and when to move data from sources to targets.
66. Define the command task.
This task permits one or more shell commands in UNIX or DOS in Windows to run during the workflow.
67. Explain standalone command task.
This task can be used anywhere in the workflow to run the shell commands.
68. What is a predefined event?
A predefined event is a file-watch event. It waits for a specific file to arrive at a specific location.
69. What is a user-defined event?
User-defined events are a flow of tasks in the workflow. Events can be developed and then raised as per requirement.
70. Define workflow.
The group of directions that communicates with the server about how to implement tasks is known as a workflow.
71. Mention the different tools used in Workflow Manager?
The different tools used in Workflow Manager are:
- Task Developer
- Task Designer
- Workflow Designer
72. Name the other tools used for scheduling purpose other than Workflow Manager and pmcmd.
‘CONTROL M’ is a third-party tool used for scheduling purposes.
73. Define OLAP (Online Analytical Processing).
It is a process by which multi-dimensional analysis occurs.
74. Name the different types of OLAP.
Different types of OLAP are ROLAP, HOLAP, and DOLAP.
Check out How Upskilling in Informatica Helped me to Get Back into the Workforce: Subhrosmita’s Journey!
75. Define worklet.
Worklet is said when the workflow tasks are collected in a group. It includes a timer, decision, command, event wait, etc.
76. Mention the use of a Target Designer.
With the help of a Target Designer, we can create a target definition.
77. From where can we find the throughput option in Informatica?
In Workflow Monitor, we can find the throughput option. By right-clicking on the session, then pressing on get run properties, and, under source/target statistics, we can find this option.
78. Define target load order.
It is specified on the criteria of source qualifiers in a mapping. If there are many source qualifiers attached to various targets, then we can entitle an order in which Informatica loads data in targets.
79. Define Informatica.
Informatica is a tool, supporting all the steps of the Extraction, Transformation, and Load (ETL) process. Nowadays, Informatica is also being used as an integration tool. Informatica is an easy-to-use tool. It has got a simple visual interface like forms in visual basic. You just need to drag and drop different objects (known as transformations) and design process flow for data extraction, transformation, and load.
These process flow diagrams are known as mappings. Once a mapping is made, it can be scheduled to run as and when required. In the background, the Informatica server takes care of fetching data from the source, transforming it, and loading it to the target.
Check out our blog if you want to know about Informatica Business components!
80. What are the different lookup cache(s)?
Informatica Lookups can be cached or uncached (no cache). A cached lookup can be either static or dynamic. A static cache is one that does not modify the cache once it is built, and it remains the same during the session run. On the other hand, a 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 a static cache. A lookup cache can also be divided as persistent or non-persistent based on whether Informatica retains the cache even after the completion of the session run or deletes it.
81. How can we update a record in target table without using Update Strategy?
A target table can be updated without using ‘Update Strategy.’ For this, we need to define the key in the target table at the Informatica level, and then we need to connect the key and the field we want to update in the mapping target. At the session level, we should set the target property as ‘Update as Update’ and check the ‘Update’ check-box. Let’s assume, we have a target table ‘Customer’ with fields as ‘Customer ID,’ ‘Customer Name,’ and ‘Customer Address.’
If we want to update ‘Customer Address’ without an Update Strategy, then we have to define ‘Customer ID’ as the primary key at the Informatica level and 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.
82. What are the new features of Informatica 9.x Developer?
From an Informatica Developer’s perspective, some of the new features in Informatica 9.x are as follows:
- Lookup can be configured as an active transformation—it can return multiple rows on a successful match.
- You can write SQL override on uncached lookups also. Previously, you could do it only on cached lookups.
- You can control the size of the session log. In a real-time environment, you can control the session log file size or time.
- Database deadlock resilience feature—this will ensure that the session does not immediately fail if it encounters any database deadlock; it will now retry the operation again. You can configure the number of retry attempts.
83. What is Informatica ETL Tool?
Informatica ETL tool is the market leader in data integration and data quality services. Informatica is a successful ETL and EAI tool with significant industry coverage. ETL refers to extracting, transforming, and loading. Data integration tools are different from other software platforms and languages.
They have no inbuilt feature to build a user interface where end-user can see the transformed data. Informatica ETL tool “power center” has the capability to manage, integrate, and migrate enterprise data.
84.What is the need for an ETL tool?
The problem comes with traditional programming languages where we need to connect to multiple sources and then handle errors. For this, we have to write complex code. ETL tools provide a ready-made solution for this. We don’t need to worry about handling these things, and hence we can concentrate on coding the required part.
FAQs
What is difference between SQL override and lookup override in Informatica? ›
State the differences between SQL Override and Lookup Override? The role of SQL Override is to limit the number of incoming rows entering the mapping pipeline, whereas Lookup Override is used to limit the number of lookup rows to avoid the whole table scan by saving the lookup time and the cache it uses.
How to prepare for Informatica developer interview? ›- Differentiate between Informatica and DataStage. ...
- What is Informatica PowerCenter? ...
- Mention some typical use cases of Informatica. ...
- How can we filter rows in Informatica? ...
- Differentiate between Joiner and Lookup transformations. ...
- In Informatica Workflow Manager, how many repositories can be created?
Informatica conducts 4-6 rounds to select appropriate candidates for various set of roles. During the shortlisting process, the selection panel looks for talent and learning abilities in candidates.
How to load every 4th record in Informatica? ›- Bring your source into mapping designer area.
- Generate seq trans and then check cycle option on and set end value = 4.
- Add another expression combine your source data with seq nextval column.
Check for the following things in your mapping. --> Use update strategy transformation and don't forget to divert the records(DD_INSERT,DD_UPDATE) based on condition. Your only way to update without primary key is to write an Target override in Target.By doing this, you dont need primary key for updation.
What is the difference between static and dynamic lookup transformation in Informatica? ›Introduction – Static vs Dynamic Lookup
The lookup cache can be either static or dynamic. If the lookup cache is static (which is by default), the data in the lookup cache doesn't change as the mapping task runs. If you enable the lookup cache to be dynamic, the cache gets updated based on the changes in the task.
It has four components to develop, configure, process and monitor the ETL flows or mappings. The components of Informatica architecture are Powercenter Designer, Workflow Manager, Workflow Monitor, Repository Manager.
How to find 2nd highest salary in Informatica? ›- In the source DB Use order by for the salaray column (or) order by salary using Sorter Transformation.
- Create the following port in the expression TX. Salary (inputport) ...
- Then allow only the records which is having Rank = 2 using Filter transformation.
What is the highest salary in Informatica? The highest-paying job at Informatica is a Senior Manager with a salary of ₹56.9 Lakhs per year. The top 10% of employees earn more than ₹36.68 lakhs per year. The top 1% earn more than a whopping ₹92.87 lakhs per year.
What are the 3 rounds of interview? ›- Written/Aptitude test: The first round for most of the companies is written round. ...
- Technical interview Round: The next round is a technical interview. ...
- HR interview round: The HR interview round is the last round where candidates are judged on the basis of their communication skills, soft skills, confidence level, etc.
How many sessions can run under one workflow in Informatica? ›
One workflow can run two instances of the same worklet if the worklet has no persisted variables. A session in a worklet can run concurrently with a session in another worklet of the same instance name when the session does not contain persisted variables.
How to check the size of a file in Informatica? ›- Select the host whose file you want to monitor, and provide the criteria in the. Add Monitored Path. page.
- Provide a valid configuration name for the monitoring criteria.
- Enter the path of the file that you want to monitor.
- Select the. Path Type. as. File. ...
- Click. Save. .
Put one Aggregator and select the Group By for one column. > You can also use unix command i.e. sort and uniq in pre-session to avoid duplicate records in file. Using Rank T/R ,rank number is 1 records are uniq.
How to find duplicates in Informatica? ›Use a filter transformation, only to pass IS_DUP = 0. As from the previous expression transformation, we will have IS_DUP =0 attached to only records, which are unique. If IS_DUP > 0, that means, those are duplicate entries.
How to avoid duplicate records in Informatica? ›Select distinct record from the source
When there are duplicate rows in the source that are rejected, duplicate entries are entered into the bad file. To avoid duplicate records loaded in by the same session, filter out the duplicate rows at the source \by selecting only the distinct rows.
You can define keys which allow duplicate values. However, do not allow duplicates on primary keys as the value of a record's primary key must be unique.
Can primary key be replaced? ›Primary key attributes are just as updateable as any other attributes of a table. Stability is often a desirable property of a key but definitely not an absolute requirement.
Can primary key be changed? ›You can modify a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.
How to remove duplicates using dynamic lookup in Informatica? ›- Select the target table in the Lookup transformation so that the Lookup cache is updated with the data in the target table.
- Select dynamic cache in the Lookup transformation.
One disk cache for all partitions. One memory cache for all partitions. One disk cache for each partition. One memory cache for each partition.
Which lookup is better in Informatica? ›
Unconnected Lookup is always better.. It takes less time as compared to that of Connected Lookup. Because Unconnected Transformation is Isolated within the mapping,we can call this lookup with the help of a Expression transformation. Where as connected loopup is connected in a single pipeline from source to target.
What are the 5 components of Informatica? ›So, in nutshell, client component of Informatica comprises of 5 components viz. Informatica Repository Manager, Informatica PowerCenter Designer, Informatica Workflow Manager, Informatica Workflow Monitor and Informatica Administrator Console.
What are types of logs in Informatica? ›- Log Event Components.
- Domain Log Events.
- Analyst Service Log Events.
- Data Integration Service Log Events.
- Listener Service Log Events.
- Logger Service Log Events.
- Model Repository Service Log Events.
- Metadata Manager Service Log Events.
Following are the three major types of schemas: Star Schema. Snowflake Schema. Galaxy Schema.
What is Rownum in Informatica? ›function in Informatica. The Row_Number function is used to provide consecutive numbering of the rows in the result by the order selected in the OVER clause for each partition specified in the OVER clause. It will assign the value 1 for the first row and increase the number of the subsequent rows.
How to improve performance of rank transformation in Informatica? ›To improve the session performance, drop constraints and indexes before running the session (while loading facts and dimensions) and rebuild them after completion of the session. Parallel sessions: Running parallel sessions by using concurrent batches will also reduce the time of loading the data.
What is the salary of Informatica with 10 years experience? ›A mid-career Informatica Powercenter Developer with 4-9 years of experience earns an average salary of ₹7.6 Lakhs per year, while an experienced Informatica Powercenter Developer with 10-20 years of experience earns an average salary of ₹12.3 Lakhs per year.
What is the salary of Informatica full stack? ›The typical Informatica Software Engineer salary is ₹12,30,431 per year.
What is the salary of Informatica Big Data Engineer? ›Average Informatica Business Solutions Data Engineer salary in India is ₹ 13.5 Lakhs for experience between 3 years to 7 years. Data Engineer salary at Informatica Business Solutions India ranges between ₹ 12.0 Lakhs to ₹ 15.5 Lakhs.
Why should we hire you? ›“I should be hired for this role because of my relevant skills, experience, and passion for the industry. I've researched the company and can add value to its growth. My positive attitude, work ethics, and long-term goals align with the job requirements, making me a committed and valuable asset to the company.”
How many candidates are usually shortlisted for final interview? ›
On average, about two to four candidates make it to the final interview. For the final interview, employers usually invite individuals who performed well in previous interviews. This means every invited candidate typically has the same level of experience and qualification.
How do you pass the final interview? ›- Research the company and your interviewers.
- Practice your answers to common interview questions.
- Study the job description.
- Answer questions using the STAR method.
- Recruit a friend to practice answering questions.
- Be prepared with examples of your work.
- Open the workflow in the editor.
- Click. Edit. Validate. . Errors appear in the. Validation Log. view.
- If an error appears, fix the error and validate the workflow again.
The Integration Service uses the instructions configured in the session and mapping to move data from sources to targets. A workflow is a set of instructions that tells the Integration Service how to execute tasks, such as sessions, email notifications, and shell commands.
How to fail a workflow in Informatica? ›Fail Workflow
To fail the Workflow: For each task within the Workflow that should cause the Workflow to fail under the "General" tab for the task properties check the following two check boxes: Fail parent if this task fails. Fail parent if this task does not run.
To calculate the cache size when the Integration Service uses cache partitioning, calculate the total requirements for the transformation, and divide by the number of partitions. To configure the cache size in the session: In the Workflow Manager, open the session. Click the Mapping tab.
What is the file size limit in informatica? ›The maximum extent size is limited to 2GB - 1. An asterisk (*) may be used as a shorthand notation for the maximum extent size. For example, these definitions define two extents. The first is limited to 1MB and the second extent defaults to 2GB - 1.
How to merge two flat files in Informatica? ›Drag all the cols of file1 into one expression and create one out port and hardcode as DUMMY and create one more expression to the file2 and do the same hardcode in that expression. Now you can join those two file by using Join condition based on the two outports of the two expressions.
Which command removes duplicate records? ›One of the easiest ways to remove duplicate data in SQL is by using the DISTINCT keyword. You can use the DISTINCT keyword in a SELECT statement to retrieve only unique values from a particular column.
How do I quickly delete duplicates? ›- Select the range of cells that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.
- Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates. ...
- Click OK.
Why records get rejected in Informatica? ›
During the load process, records in the staging table might be rejected for the following reasons: future date or NULL date in the LAST_UPDATE_DATE column.
How to identify the incremental data in Informatica? ›- log in to Informatica PowerCenter designer with valid credentials. ...
- Go to tools> source analyzer then imports your source definition. ...
- Go to tools> target designer then imports your target definition.
- Go to tools> mapping designer, then create a mapping with meaningful naming convention (ex: m_MyFirstMapping).
Sorter transformation CAN be used here to remove these duplicate records by using 'DISTINCT' option.
Which transformation is used to remove duplicate values? ›Aggregator Transformation – To remove the duplicate records just Group By the port JOB_ID which will group all unique records together and pass it to target. Enable Sorted Input to improve the performance of Aggregator Transformation.
What is the difference between connected lookup and unconnected lookup? ›Connected Lookup takes input values directly from the upstream transformations in the pipeline. Unconnected Lookup receives input values from lookup expression in another transformation.
What is the purpose of SQL override in Informatica? ›By using SQL Override in Informatica, you can read only the required columns from the table instead of reading all columns from a table and can remove all unnecessary ports. You can also use customized logic in the SQL override. DataTerrain, with years of experience and reliable experts, is ready to assist.
What is lookup SQL override in Informatica? ›task runs a default lookup query when the first row of data enters the Lookup transformation. If the Lookup transformation performs a relational lookup, you can override the default query. task.
Can we use SQL override in unconnected lookup? ›In order to retrieve more than one output from Unconnected Lookup Transformation, use Lookup SQL Override with multiple fields concatenated which are expected as output.
What is the use of SQL override in source qualifier? ›Source Qualifier SQL Override
The Source Qualifier transformation provides the SQL Query option to override the default query. We can use a parameter or variable as the SQL query or include parameters and variables within the query.
Connected lookup receives input values directly from mapping pipeline, whereas UnConnected lookup receives values from the lookup expression from another transformation. A mapping in Informatica may contain Source, Transformations and Targets connected together are considered as a pipeline.
How do you handle multiple matches in lookup transformation? ›
- Use the value in the first matching record in the lookup cache.
- Use the value in the last matching record in the lookup cache.
- Return an error.
Same way un-connected lookup can be used in any scenario where you need use the lookup repeatedly either in multiple or same transformation. With the un-connected lookup, You get the performance benefit of not caching the same data multiple times.
How to remove null records in Informatica? ›To filter Null values in Informatica you can use the ISNULL function. ISNULL Function will test the value for port and check if the value is null or not.
What is difference between stop and abort in Informatica? ›When you stop a task in the workflow, the Integration Service stops processing the task and all other tasks in its path. The Integration Service continues running concurrent tasks. If the Integration Service cannot stop processing the task, you need to abort the task.
What is cache in Informatica? ›A cache is a memory area where informatica server holds the data to perform calculations during the session run. It creates cache files in the $PMcachedir as soon as the session finishes, server deletes the cache files from the directory.
How to disable lookup cache in Informatica? ›- add the lookup to the mapping task.
- configure the Connection for under Properties/Lookup Object.
- check/uncheck the Lookup Caching Enabled check box under Properties/Advanced.
In PowerCenter you can configure a Lookup transformation to return all matching rows when you create the transformation. In the Designer create the Lookup transformation (Transformation > Create). On the Select Lookup Table for Lookup Transformation dialog select Return All Values on Multiple Match.
How to filter records from flat file in Informatica? ›- Create a Logical System for PowerCenter.
- Create an RFC Destination.
- Create a tRFC Port for the RFC Destination.
- Create a Partner Profile for PowerCenter.
- Create Outbound and Inbound Parameters for the Partner Profile.
...
Linking Ports by Name using the Autolink Dialog Box
- Click Layout > Autolink.
- Select the transformations and targets.
- Select Name.
- Click OK.
The Source Qualifier transformation filters rows from relational sources. Filter transformation. The Filter transformation filters data within a mapping. The Filter transformation filters rows from any type of source.