Informatica Interview Questions

Informatica: Introduction

Informatica is actually a power center. It is widely used as an information, extraction, transformation and loading tool. It is further used to build an enterprise data warehouse. The components, which are located within Informatica help in extracting data from its sources and further using it for business requirements. But in order to use the data for business requirements, you need to convert in firstly. And finally, the data is loaded onto a target data warehouse.

Informatica: Career Prospects

Informatica has got the widespread ability in terms of efficient data processing, data partitioning, and bulk extraction. This altogether contributes to the maximum and quality work at any large-scale organization. Almost maximum of business domains utilize Informatica tools and thus career prospects are endless in the field of Informatica. But in order to land up with a good job in Informatica, you need to successfully crack Informatica interview questions.

Cracking Informatica Interview Questions

Cracking Informatica interview questions is not exactly a rocket science. No doubt since the web world is rapidly evolving, there are more and more challenges about the Informatica field. And you need to understand that you will not be ever able to know it all. What you need to do is brush up your foundation. Try to know and understand your basics. Clear up the basic know-how of Informatica and this will help you to a get an upper hand at the Informatica interview. Also, be open and honest about what you know and what you don’t know. This will give a clear picture in the mind of interviewers. But you must display a learning attitude. Make sure that you always tell the interviewer that you are willing to learn new things and incorporate new skills which will further help you in your personal and professional growth.

Following are the list of some Informatica Interview Questions and their answers.

  • Q1.

    In warehouses how many schemas are there?

    Posted by Sharad Jaiswal
    There are four Schema’s They are:
    • Star Schema
    • Snow Flake Schema
    • Galaxy Schema
    • Fact Constellation Schema
  • Q2.

    Could you explain what is Enterprise data warehouse?

    Posted by Sharad Jaiswal
    In enterprise data warehousing all organization data is created at a single point of access .data can be provided a global view to the server through a single source storage.we can do periodic analysis on the same source.it takes time but it gives better results.
  • Q3.

    What is different between the data warehouse and data mart?

    Posted by Sharad Jaiswal
    A Data Mart Is a simple form of a data warehouse that is focused on a single subject or functional areas such as sales or finance or marketing.
    A Data warehouse is a collection of multiple functional areas.it is the central unit which is made by combining all the data marts
  • Q4.

    How to Delete duplicate row using Informatica?

    Posted by Sharad Jaiswal
    We can delete the duplicate rows in two ways which are coming from the source. They are
    1. Using Source Qualifier, under properties tab select Distinct
    2. Using Sorter, under properties tab select distinct

    First one is the better option when the source is relational but when a source is a file then you need to use the second option only.

  • Q5.

    How can we handle two sessions in Informatica?

    Posted by Sharad Jaiswal
    In Informatica Linking more than one session called batches, there are two types of batches
    • Sequential batches: sequential batches session runs one by one
    • Concurrent batch: concurrent batches session runs at the same time
  • Q6.

    Do you find any difficulty while working with flat files as source and target?

    Posted by Sharad Jaiswal
    Yes, there are difficulties I found while working with flat file some of them are:
    1. We can’t use SQL override in flat files instead of that we have to use transformations.
    2. testing the flat files is very uninteresting job
    3. We need to specify the correct path in the session and mention either that file is ‘direct’ or ‘indirect. Keep that file in the exact path which we have specified in the session and we have to keep the file in the exact path as mentioned in the session.
    4. If we miss the link to any column of the target, then all the data will be placed in wrong fields. That missed column won’t exist in the target data file.
  • Q7.

    What is the meaning of  Decode in Informatica?

    Posted by Sharad Jaiswal
    Decode is a special function which searches a port for the specified value. It is a better option to use DECODE function when the number of conditions is large because it is less costly compared to IIF function. and it can use in a select statement whereas we can not use IIF Function In select Statement.
  • Q8.

    What do think which one is the better joiner or look up?

    Posted by Sharad Jaiswal
    We can’t compare which one is a better joiner or look up because both have their own functionalities .its all depends on table size and source. If we are using flat file sorted joiner is more effective than look up because of joiner caches fewer rows .lookup caches who file in always. If we are using database lookup is effective when the database can return sorted data fast and the amount of data is small.
  • Q9.

    What is Domain in terms of Informatica?

    Posted by Sharad Jaiswal
    Domains are primary component Of Informatica power center, it is used for managing and administrating various services in Informatica power center.
  • Q10.

    What is a Mapplet/worklet in Informatica?

    Posted by Sharad Jaiswal
    Mapplet is used for creation and configuration of a group of transformation. A worklet is an object by combining the set of tasks to build a workflow logic.It can be reusable in multiple workflows, which can be configured to run concurrently.
  • Q11.

    Describe an Informatica PowerCenter?

    Posted by Sharad Jaiswal
    In terms os Informatica power center is a service-oriented architecture which provides the capability to share service & resource across multiple machines.
  • Q12.

    What is the cumulative sum and moving sum?

    Posted by Sharad Jaiswal
    Cumulative sum is partial sums of a given column that can calculate by using CUME(COLUMN NAME).
    moving sum returns the sum of a specifies set of rows. It ignores null values when calculating the moving sum so that if all values are NULL, Then the function Returns NULL
    MONINGSUM(ROWNAME,Rowsetvalue)
  • Q13.

    Explain sessions and how many types of sessions are there?

    Posted by Sharad Jaiswal
    A session is a set of instructions which explains the movement of data from source to target.
    There are two type of sessions in Informatica
    • Reusable Sessions
    • Non-Reusable  Sessions
  • Q14.

    Explain what transformation and how many  are there in Informatica?

    Posted by Sharad Jaiswal
    A transformation is a repository object which generates, updates or sends the source data according to the requirements of the target system. It is mainly classified into 4 types
    • Active
    • Passive
    • connected,
    • unconnected
  • Q15.

    What is a surrogate key?

    Posted by Sharad Jaiswal
    A surrogate key is a unique Identification key and acts as natural primary key .it is unique for each row of the table that why it is very useful to identify each unique row.
  • Q16.

    What is PMCMD COMMAND?

    Posted by Sharad Jaiswal
    PMCMD(power Mart Command) is a built-in command line program or utility that is used to interact with the Informatica server .this Command mainly used to perform the following tasks.
    • Scheduling a workflow
    • Start or stop workflow
    • Start workflow from a task
    • Stopping / Aborting specific task
    • Aborting workflow
  • Q17.

    Is STOP AND ABORT are same IN INFORMATICA?

    Posted by Sharad Jaiswal
    No both kill the process but the way is different, the process timeout period is the main difference in Stop and Abort.
    Stop will Stop reading from the source and release the memory blocks that are occupied by the session at a time but it will continue updating and committing changes in target whereas abort also stop reading but it has the timeout period of 60 sec. If the session fails to update or commit the changes within 60 sec then the session will be aborted by terminating the DTM process thread and it will not release the memory immediately.
    To ensure Data Consistency Stop Will try to Rollback data but in Abort kill the process immediately so that can’t be rolled back.
  • Q18.

    What are batches?

    Posted by Sharad Jaiswal
    A group of sessions executed either Serial Or-Parallel Execution By The Informatica Server is called a batch.
    There are two types of batches are theirs.
    batch run session one after another is called Sequential batch and batch which run a session at the same is concurrent.
  • Q19.

    Can you generate sequence numbers without using the sequence generator transformation?

    Posted by Sharad Jaiswal
    Yes, we can generate sequence numbers with expression transformation instead of the sequence generator transformation.
  • Q20.

    What are the static cache and dynamic cache in Informatica?

    Posted by Sharad Jaiswal
    In the static cache, the cache memory will not refresh even though the record is inserted or updated in the lookup table it will refresh when in next session run.
    In dynamic caches, cache memory will refresh as soon as the record is inserted or updated in the look-up table.
  • Q21.

    What is the need of ETL tools?

    Posted by Sharad Jaiswal
    ETL(Extract-Transform-Load) itself tell that is extract, transform and load the data to the source to destination for better decision making.if we don’t use ETL tools then we have to do all these things by manually by creating SQL codes that are not possible for an end user that can do only expert programmers.this process was very tedious and cumbersome in many cases because it involved many resources and complex coding.these difficulties are eliminated by ETL Tool because they are very comfortable to use and many other advantages in all stages like visual flow, structured system design, operational resilience, impact analysis, data profiling and cleansing and excellent performance.
  • Q22.

    What is the fact table?

    Posted by Sharad Jaiswal
    Fact table is the centralized table in a star schema that’s why it is also called central table. Fact table mainly contains the facts nothing measurement (values)that is related to the data in the dimension table. it typically has two types of columns one contains facts(values) and another contains foreign keys to dimension tables. a composite key that is made by all of its foreign keys is acts as a primary key of a fact table.
  • Q23.

    What is polling?

    Posted by Sharad Jaiswal
    Polling means displaying the updated data about the session while it is running in a window. The monitor window displays the status of each session when you poll the Informatica server.
  • Q24.

    How many dimensions are there in Informatica?

    Posted by Sharad Jaiswal
    Dimension table consists of attributes about the facts and it stores the textual descriptions of the business. dimensions are very important to measure the facts. and they are mainly 4 types.
    • Conformed Dimension
    • Degenerated Dimension
    • Role-playing dimension
    • Junk Dimension
  • Q25.

    How Can u generate reports in Informatica?

    Posted by Sharad Jaiswal
    Not directly we could not generate reports, but we can generate metadata report by using Informatica Metadata driven reporting Tool.
  • Q26.

    What is the Rank index port in Rank transformation?

    Posted by Sharad Jaiswal
    Rank transformation is an active and Connected transformation. it is used to select bottom or top rank of data this means to select the smallest or largest numeric value in a group or port. rank index port used by Informatica server to store the rank position for each record A Designer can create a rank index port automatically for each rank transformation.For Example, if you create a Rank Transformation that ranks the top 10 products for each quarter, then the rank index numbers products from 1-10.
  • Q27.

    What is the target load order?

    Posted by Sharad Jaiswal
    Target load plan Target load order (or) is used to specify the order in which the integration service loads the targets. We can specify a target load order based on the Source Qualifier transformations in a mapping. if we are working with multiple source qualifier transformations connected to multiple targets  then we can designate the order in which the integration service loads data into targets.
  • Q28.

    What is data transformation manager Process?

    Posted by Sharad Jaiswal
    The Integration Service process starts the data Transformation Manager (DTM)process when the workflow reaches a session.DTM process is also known as the PMDTM process. The main purpose of the DTM process is to create and manage threads that carry out the session tasks.
  • Q29.

    How to generate sequence numbers?

    Posted by Sharad Jaiswal
    We can generate sequence numbers by using sequence generator or expression transformation.
  • Q30.

    Which transformation is needed while using the Cobol sources as source definitions?

    Posted by Sharad Jaiswal
    Normalizer transformation because COBOL sources consist of denormalized data, normalizer transformation is essential to get normalized data.
  • Q31.

    What are active and passive transformations?

    Posted by Sharad Jaiswal
    The Active and passive transformations transform the records in two different ways. Inactive transformation changes the number of rows number of rows that pass through the mapping. In case of Passive transformation don’t change the number of rows.
  • Q32.

    In how many ways we can create ports?

    Posted by Sharad Jaiswal
    We can create ports in two ways  by Dragging the port from another transformation and another by clicking the add port button on the ports tab
  • Q33.

    What is Data Caches/ Index Caches?

    Posted by Sharad Jaiswal
    Index cache contains all of the port values which satisfies the given condition those are stored in index cache data cache stores port values which are not satisfies the given condition is stored in a data cache.
  • Q34.

    What do you mean by blocking transformation?

    Posted by Sharad Jaiswal
    When the integration service waits for a row from a different input group. Some multiple input group transformations require the integration service to block data at an input group.  A  blocking transformation is a multiple input group transformation that blocks incoming data. Custom transformation and Joiner transformation comes into this category.
  • Q35.

    What you know about transaction control transformation?

    Posted by Sharad Jaiswal
    Transaction Control is connected and an active transformation and it is used to control the rollback and commit of transactions .we can specify the transaction depending on varying number of input rows.It is used in two levels mapping and session levels.
  • Q36.

    How do you change a non-reusable transformation to reusable transformation?

    Posted by Sharad Jaiswal
    We can change a non reusable transformation to reusable transformation by selecting the reusable transformation in the navigation bar and drag it to the mapping and hold the control key just before releasing the transformation then release into mapping.