57 Tricky Informatica Interview Questions

1.Define Enterprise Data Warehousing?

When the organization data is developed at a single point of access it is known as enterprise data warehousing.

2.Differentiate between a database, and data warehouse?

The database has a group of useful information which is brief in size and time as compared to a data warehouse which also maintains historical data and there are set of every kind of data whether it is useful or not and data is extracted as the-the requirement of customer.

3.What do you understand by a term domain?

Domain is the term in which all interlinked relationship and nodes are undertaken by sole organizational point.

4.In Informatica Repository Manager, how many repositories can be created?

It mainly depends on upon the number of ports we required however as mentioned by Informatica there can be any number of repositories. 

5.Write the advantages of partitioning a session?

The main advantage of partitioning a session is to get better server’s process and competence. Another advantage is it implements the solo sequences within the session.

6.How can indexes be created after completing the load process?

With the help of command task at the session level, we can create indexes after the load procedure. Also, create indexes statements can be defined in Post SQL to create indexes after load completion.

7. Define sessions in Informatica ETL.

A session is a wrapper for the mapping which actually processes the data as per the flow layout of the mapping from source to a target.

8.In one workflow how many number of sessions can we have?

We can have any number of sessions but it is advisable to have lesser number of sessions in a workflow because it will become easier for migration.

9.Differentiate between mapping parameter and mapping variable?

At the time values alter during the session’s implementation it is known as mapping variable whereas the values that don’t alter within the session implementation is called as mapping parameters.

10.What are the features of complex mapping?

The features of complex mapping are:
Many numbers of transformations and complex business rules implemented.

11.How can we identify whether a mapping is correct or not without connecting session?

With the help of debugging option, we can identify whether a mapping is correct or not without connecting sessions. 

12.Can we use mapping parameter or variables developed in one mapping into any other reusable transformation?

Yes, we can use mapping parameter or variables into any other reusable transformation because it doesn’t have any mapplet.

13.What is the use of aggregator cache file?

These are the cache files created by Aggregator transformation to calculate aggregate functions like SUM, MAX, MIN values on the data. They can also be saved for later use to implement Incremental Load on the target. 

14.What is lookup transformation?

The transformation that is used to do a lookup on the set of values is Lookup transformation, the lookup values can be from RDBMS table or a flat file.

15.What do you understand by 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.

16.How can we access repository reports without SQL or other transformations?

We can access repository reports by using metadata reporter. No need of using SQL or other transformation as it is a web app.

17.Write the types of metadata those stores in repository?

The types of metadata which are stored in a repository are Target definition, Source definition, Mapplet, Mappings, Transformations, Session properties et al.

18.What is code page compatibility?

Transfer of data takes place from one code page to another keeping that both code pages have the same character sets to avoid any data failure. 

19.How can we validate all mappings in the repository simultaneously?

Multiple mappings can be validated in a one go using Query builder in the Repository Manager.

20.Define Aggregator transformation?

It is a transformation to calculate aggregate functions like SUM, MAX, MIN on the data set.

21.What is Expression transformation?

It is used for performing non-aggregated calculations and to implement row level functions like TO_DATE, TO_CHAR. We can test conditional statements before output results move to the target tables.

22.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.

23.Define Joiner transformation?

It combines two associated mixed sources of data located in different locations.

24.How can we use Union Transformation?

It is a different input group transformation that is used to combine data from different sources. It works the same way like UNION ALL in a database.

25.Define Incremental Aggregation?

The incremental aggregation is done whenever a session is developed for a mapping aggregate.

26.Differentiate between a connected lookup and unconnected lookup?

In connected lookup inputs are taken straight away from various transformations in the pipeline it is called connected lookup. While unconnected lookup doesn’t take inputs straight away from various transformations, but it can be used in any transformations and can be raised as a function using LKP expression.

27.Define mapplet?

A mapplet is a recyclable object that is using mapplet designer. It can have all the functionality of a mapping except target instance.

28.What is a reusable transformation?

This transformation can be used various times in the same mapping or different mappings.

29.Define update strategy.

Whenever the row has to be updated or inserted based on some sequence then update strategy is used. But this condition should be specified before for the processed row to be updated or inserted.

30.Explain the scenario which compels Informatica server to reject files?

When it faces DD_Reject in update strategy transformation then it sends records to reject files.

31.What is a surrogate key?

It is a substitute for the natural prime key. It is a unique identification for each row in table.

32.Write  the prerequisite tasks to achieve the session partition?

In order to perform session partition one need to configure the session to partition source data and then installing the Informatica server machine in multifold CPU’s.

33.In informatics server, Which files are created during the session runs?

Errors log, Bad file, Workflow log and session log namely files are created during the session runs.

34.Define a session task?

It is a mass of instruction that guides power center server about how and when to move data from sources to targets.

35.Define command task?

This task permits one or more than one shell commands in UNIX or DOS in windows to run during the workflow.

36.Explain standalone command task?

This task can be used anywhere in the workflow to run the shell commands.

37.What is pre and post session shell command?

Command task can be called as the pre or post session shell command for a session task. One can run it as pre-session command or post session success command or post session failure command.

38.What is a predefined event?

Predefined event is the file-watch event. It waits for a specific file to arrive at a specific location.

39.Define user defined event?

User defined event are a flow of tasks in the workflow. Events can be developed and then raised as the need comes.

40.Define workflow?

The group of directions that communicates server about how to implement tasks is known as work flow.

41.Write the different tools in workflow manager?

The different tools in workflow manager are:
Task Developer
Worklet Designer
Workflow Designer

42.Name other tools for scheduling purpose other than workflow manager pmcmd?

There are many third party tools for scheduling like ‘CONTROL-M’ is the third party tool for scheduling purpose other than workflow manager.

43.Define OLAP (On-Line Analytical Processing)?

It is a process by which multi-dimensional analysis occurs.

44.Name the different types of OLAP? Write an example?

Different types of OLAP are ROLAP, HOLAP, MOLAP.

45.Define worklet?

Worklet is said when the session tasks are collected in a group. It  includes timer, decision, command, event wait, etc.

46.Write the use of target designer?

With the help of target designer, we can create target definition.

47.From where can we find the throughput option in Informatica?

In Workflow Monitor, we can find throughput option.
Right click on the session, then click on Get Run Properties and under source/target statistics we can find this option.

48.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 in a mapping then we can define the order in which data is to be loaded in targets.

49. Define Informatica?

Informatica is a tool, supporting all the steps of Extraction, Transformation and Load process. Nowadays Informatica is also being used as an Integration tool.

Informatica is 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 Informatica server takes care of fetching data from the source, transforming it, & loading it to the target systems/databases.

50. 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 “sorted input” option under aggregator properties is checked. The record set should be sorted on those columns that are used in Group By operation.

It is often a good idea to sort the recordset in database level e.g. inside a source qualifier transformation unless there is a chance that already sorted records from source qualifier can again become unsorted before reaching aggregator.

51. What are the different lookup cache(s)?

Informatica Lookups can be cached or uncached (No cache). And Cached lookup can be either static or dynamic. A static cache is one which does not modify the cache once it is built and it remains same during the session run. On the other hand, caches can be refreshed during the session run by inserting or updating the records in the cache based on the incoming source data called Dynamic cache. By default, Informatica cache is 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.

52. 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 mappinglevel and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and check the “Update” checkbox.

Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”. Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as primary key in mapping 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.

53. 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:

Now Lookup can be configured as an active transformation – it can return multiple rows on successful match
Now you can write SQL override on uncached lookup also. Previously you could do it only on cached lookup
You can control the size of your 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 your session does not immediately fail if it encounters any database deadlock, it will now retry the operation again. You can configure any number of retry attempts.

54. What is Informatica ETL Tool?

Informatica ETL tool is a market leader in data integration and data quality services. Informatica is successful ETL and EAI tool with significant industry coverage.

ETL refers to extract, transform, load. Data integration tools are different from other software platforms and languages. They have no inbuilt feature to build user interface where an end user can see the transformed data. Informatica ETL tool “power center” has the capability to manage, integrate and migrate enterprise data.

55. What are the Advantage of Informatica?

Its GUI tool, Coding in any graphical tool is generally faster than hand code scripting.
Can communicate with all major data sources (mainframe/RDBMS/Flat Files/XML/VSM/SAP etc).
Can handle very large/huge data very effectively.
Users can apply Mappings, extract rules, cleansing rules, transformation rules, aggregation logic and loading rules are in separate objects in an ETL tool. Any change in any of the object will give a minimum impact on another object.
Reusability of the object (Transformation Rules)
Informatica has different “adapters” for extracting data from packaged ERP applications (such as SAP or PeopleSoft).
Availability of resource in the market.
Can be run on Window and Unix environment.

56. What is InformaticaPowerCenter?

InformaticaPowerCenter is one of the Enterprise Data Integration products developed by Informatica Corporation. InformaticaPowerCenter is an ETL tool used for extracting data from the source, transforming and loading data into the target.

The Extraction part involves understanding, analyzing and cleaning of the source data.
Transformation part involves cleaning of the data more precisely and modifying the data as per the business requirements.
The loading part involves assigning the dimensional keys and loading into the warehouse.

57. What is the need of an ETL tool?

The problem comes with traditional programming languages where you need to connect to multiple sources and you have to handle errors. For this, you have to write complex code. ETL tools provide a ready-made solution for this. You don’t need to worry about handling these things and can concentrate only on coding the requirement part.

Informatica Interview Questions

1.What is ETL Process?

ETL is the process of Extraction, Transforming, and Loading. 

2.How many steps are there in ETL process?

In ETL process data is extracted from the source such as the database servers and it is used to generate business roll.

3.What are the steps involved in ETL process?

The steps involved are defining the source; target, creating the mapping, creating the session, and creating the workflow. 

4.Can there be sub-steps of ETL steps?

Each of the steps involved in ETL has several sub-steps. The data transformation step has most sub-steps. 

5.What is initial load and what is full load?

In ETL, the initial load is the process for populating all data warehousing tables for very first time. Full load means when the data is loaded for the first time all set records are loaded at a stretch depending on its volume. It would erase all contents in the table and would reload fresh data. 

6.What is meant by incremental load?

Incremental load refers to applying the dynamic changes as and when required in a specific period and predefined schedules. 

7.What are three tier systems in ETL?

The data warehouse is considered to be the three tier system in ETL. 

8.What are the three tiers in ETL?

Middle layer in ETL provides the data that is usable in a secure way to end users. Other two layers are on the other side of the middle tier, the end user and back end data storage.

9.What are the names of the layers in ETL?

The first layer in ETL is the source layer and it is the layer where data lands. The second layer is integration layer where data is stored after transformation. The third layer is the dimension layer where actual presentation layer stands. 

10.What is meant by snapshots?

Snapshots are the copies of read-only data that is stored in the master table. 

11.What are the characteristics of Snapshots?

Snapshots are located on the remote node and refreshed periodically so that the changes in the master table can be recorded. They are also the replica of tables. 

12.What are views?

Views are built using the attributes of one or more tables. View with single tables can be updated but those with multiple tables cannot be updated. 

13.What is meant by materialized view log?

Materialized view log is the pre-computed table with aggregated or joined data from the fact tables as well as the dimension tables. 

14.What is a materialized view?

Materialized view is an aggregate table. 

15.What is the difference between power center and power mart?

Task accomplished by Power Center is processing large volumes of data. Power Mart processes low volumes of data. 

16.With which apps can Power Center be connected?

Power Center can be connected with ERP source like the SAP, Oracle Apps, and the People Soft etc. 

17.Which partition is used to improve the performances of ETL transactions?

To improve the performances of ETL transactions the session partition is used.

18.Does Power Mart provide connections to ERP sources?

No! Power Mart does not provide connection to any of the ERP sources. It also does not allow sessions partition.

19.What is meant by partitioning in ETL?

Partitioning in ETL refers to the subdivision of the transactions in order to improve their performances. 

20.What is the benefit of increasing number of partitions in ETL?

The increase in the number of partitions enables the informatics Server to create multiple connections to a host of sources. 

21.What are the types of partitions in Informatica?

Types of partitions in ETL are Round-Robin partition and Hash partition.

22.What is Round Robin partitioning?

In Round Robin partitioning the data is evenly distributed by the Informatica among all the partitions. It is used when the number of rows in the process in each of the partitions is nearly the same.

23.What is Hash partitioning?

In Hash partitioning, the Informatica server would apply a hash function in order to partition keys to group data among the partitions. It is used to ensure the processing of a group of rows with the same partitioning key in the same partition. 

24.What is mapping in Informatica?

Mapping refers to the flow of data from source to the destination. 

25.What is a session in Informatica?

A Session is a set of instructions that describes the data movement from the source to the destination. 

26.What is meant by Worklet in Informatica?

A Worklet is the set of tasks in Informatica which can be reused in other workflows to achieve the same functionality. It can be any set of tasks in the program. 

27.What is workflow in Informatica?

Workflow is a set of instruction that specifies the way of executing the tasks to the Informatica Integration Service.

28.What is referred by Mapplet in Informatica?

Mapplet in ETL is used for the purpose of creation as well as the configuration of a group of transformations. However, it cannot contain any Target within it.

29.What is meant by operational data store?

The operational data store is the repository that exists between the staging area and the data warehouse. Data stored in ODS has low granularity. 

30.How operational data store works?

Aggregated data is loaded into the EDW after it is populated in the operational data store or ODS. Basically, ODS is also semi DWH helping analysis of business data. Data persistence period in ODS is usually in the range of 30-45 days and not more. 

31.What the ODS in ETL generates?

ODS in ETL generates primary keys, takes care of the error

32.How can the mapping be fine tuned in Informatica?

Steps for fine tuning the mapping involves using condition for filter in source qualifying the data without use of filter; utilizing persistence as well as cache store in Lookup transformation; using the Aggregator transformation in sorted input group by different ports, using operators in expressions instead of functions, and increase the cache size and setting the optimized commit interval. 

33.What are differences between connected and unconnected look up in Informatica?

            Connected Lookup is used for mapping and can have more than one return port. It is connected to other                 transformations and is part of the main flow. Unconnected Lookup is used to call Lookup table based on               required conditions and have only single return port. It is not connected to any transformation but are                    reusable.

Finding Date of a particular Day of the Week in Informatica

The code that can be used to get a date of a particular day of the week in Informatica is listed below with the brief explanation of the same, it can be pasted in the expression transformation.

Let's suppose you need to load the date of coming Friday of this week for all the records. I have considered SYSDATE as the incoming date which can be replaced by your's any system date. The idea used here is simple, first, get the day of the week using TO_CHAR(SYSDATE, 'D') which returns the day of the week; second, based on the day of the week find out how many days we need to add to get the required day, for example, Friday in the code below which is the 6th day of the week starting from Sunday as 1st day; third and final step, use ADD_TO_DATE inbuilt Informatica function to add the required days in the provided date to get the date of the particular day. Another point to consider here, ADD_TO_DATE function accepts integer values only to add so explicitly or implicitly need to change the adding part to an integer, TO_INTEGER is used below for the same purpose. Hope it will be beneficial to you.

ADD_TO_DATE( SYSDATE, 'DD',
                            TO_INTEGER(
              DECODE(TO_CHAR(SYSDATE, 'D'),
                  '1','5',
                '2','4',
                '3','3',
                '4','2',
                '5','1',
                '6','0',
                '7','6'
              )
         )
        )