Informatica Powercenter (also simply called Informatica) is a popular ETL (Extract, Tranform, Load) or data integration tool which is a widely used tool developed by the American software development company, Informatica. It is very powerful and delivers the right data to help the needs of the users.

Data professionals have always played an important role in any business related to any industry. Professionals who are experts in Informatica are also in great demand. If you are appearing for Informatica interview, you can take the help of the following commonly-asked Informatica interview questions (with answers):

Q1. Differentiate between a database, a data warehouse and a data mart?

Ans. Database includes a set of sensibly affiliated data which is normally small in size as compared to data warehouse, whereas in a data warehouse there are assortments of all sorts of data from where data is taken out only according to the customer’s needs. Datamart is also a set of data which is designed to cater the needs of different domains.

Q2. What is a Sorter Transformation?

Ans. Sorter transformation helps us sort collections of data by port or ports.

Q3. What is an Expression Transformation?

Ans. An expression transformation is a common Powercenter mapping transformation.

Q4. What is Joiner Transformation?

Ans. The joiner transformation is an active and connected transformation used to join two heterogeneous sources.

Q5. What is a Decode in Informatica?

Ans. A decode in Informatica is a function used within an Expression Transformation.

Q6. What is a Router Transformation?

Ans. The Router Transformation allows users to split a single pipeline of data into multiple.

Q7. What is a Rank Transformation?

Ans. The Rank Transformation lets users to sort and rank the top or bottom set of records based on a specific port.

Q8. What is a Filter Transformation?

Ans. Filter transformation is used to filter out rows in a mapping. It is active and connected.

Q9. What is a Sequence Generator Transformation?

Ans. It is used to generate primary key values, or a range of sequence numbers for calculations or processing. It is passive and connected.

Q10. What is a Master Outer Join?

Ans. A master outer join is a specific join type setting within a joiner transformation.

Also Read>> Online Learning Vs Traditional Learning!

Q11. What are some examples of Informatica ETL programs?

Ans. Some of the programs are mappings, workflow and task.

Q12. Explain dimensional tables?

Ans. Dimension tables contain attributes that describe fact records in the table.

Q13. What is star schema?

Ans. It is the simplest form of data warehouse schema that consists of one or more dimensional and facts tables.

Q14. Describe snow flake schema?

Ans. It is a fact table which is connected to a number of dimensional tables.

Also Read>> What do Online Courses Have to Offer?

Q15. What is a mapplet?

Ans. Mapplet is essential to create reusable mappings in Informatica.

Q16. What is a natural primary key?

Ans. A natural primary key uniquely identifies each record within a table and relates records to additional data stored in other tables.

Q17. What is a surrogate key?

Ans. It is used in substitution for the natural primary key

Q18. What is the difference between a repository server and a powerhouse?

Ans. Repository server controls the complete repository which includes tables, charts, and various procedures etc.

A powerhouse server governs the implementation of various processes among the factors of server’s database repository.

Also Read>> Lifelong Learning: A Chance to Follow Your Heart and Carve Your Own Path!

Q19. How many repositories can be created in informatica?

Ans. As many as required.

Q20. Describe Data Concatenation?

Ans. Data concatenation is the bringing of different pieces of record together.

Q21. How can one identify whether mapping is correct or not without connecting session?

Ans. With the help of debugging option.

Q22. Name the designer tools for creating transformations?

Ans. Mapping designer, transformation developer and mapplet designer.

Q23. Differentiate between sessions and batches?

Ans. Session is a set of commands for the server to move data to the target.

Batch is a set of tasks that can include one or more number of tasks.

Also Read>> Take That Big Career Leap with e-Learning!

Q24. What is the difference between static cache and dynamic cache?

Ans. In static cache the data will remain the same for the entire session, whereas in dynamic cache, whenever a row is inserted the cache will also be updated.

Q25. What is the command used to run a batch?

Ans. The pmcmd command.

Q 26. What are the differences between ROUTER and FILTER?

Ans.

Router Filter
Captures data rows that don’t meet the conditions to a default output group Tests data for one condition and drops the data rows that don’t meet the condition
Single input and multi output group transformation Single input and single output group transformation
User can specify multiple filter conditions User can only one filter condition
It does not block input rows and failed records Chances that records can get blocked
Acts like IIF() function in Informatica or CASE Works as SQL WHERE clause

Q 27.  What is Enterprise Data Warehousing?

Ans. Enterprise data warehousing is a process of creating a centralized repository of operational data so that it can be used as per the reporting and analytics requirements. It has a single point of access and the data is provided to the server via single source store.

 

 Q 28.  What are the different names of the Data Warehouse System?

Ans. The Data Warehouse System has the following names –

  • Analytic Application
  • Business Intelligence Solution
  • Data Warehouse
  • Decision Support System (DSS)
  • Executive Information System
  • Management Information System

Q 29.  What is a Domain?

Ans. A Domain comprises of nodes and services and serves as the fundamental administrative unit in the Informatica tool. It categorizes all related relationships and nodes into folders and sub-folders depending upon the administration requirement.

Q 30.  Why should we partition a Session?

Ans. Partition not only helps in optimizing a Session but also helps in loading colossal volume of data and improve the server’s operation and efficiency.

Q 31.  What is Complex Mapping?

Ans. Complex Mapping is a mapping with huge requirements based on many dependencies. It doesn’t necessarily need to have hundreds of 100 transformations, it can be a complex map even with 5 odd transformations. In case the requirement has many business restrictions and constraints, it is complex mapping.

Q 32.  What are the features of Complex Mapping?

Ans. The features of Complex Mapping are –

  • Complicated and huge requirements
  • Complex business logic
  • Multiple transformations

Q 33.  What is a Lookup Transformation?

Ans. Lookup Transformations are passive transformations with admission rights to RDBMS based data set. It is used to look up a source, source qualifier, or target to get the relevant data.

Q 34.  What is different Lookup Cache(s)?

Ans. Lookups can be cached or un-cached and can be divided as –

  • Static cache
  • Dynamic cache
  • Persistent cache
  • Shared cache
  • Recache

Q 35.  Name different available editions of INFORMATICA PowerCenter.

Ans. Different editions of INFORMATICA PowerCenter are –

  • Standard Edition
  • Advanced Edition
  • Premium Edition

 

Q 36. What are Mapplets?

Ans. Mapplets are a reusable object that can be created in the Mapplet Designer and has a set of transformations that allow reuse of transformation logic in multiple mappings.

Q 37. What is the use of Source Qualifier?

Ans. Source Qualifiers represent rows using the PowerCenter server that the integrations service reads during a session. The source qualifier transformation converts the source data types to the Informatica native data types, hence eliminating the need to alter the data types of the ports in the source qualifier transformation.

Q 38. Define Workflow.

Ans. Workflow is a set of multiple tasks that enable a server to communicate and get the tasks implemented. These tasks are connected with start task link and trigger the required sequence to start a process.

Q 39. How many tools are there in the Workflow Manager? 

Ans. There are three types of tools in the Workflow Manager –

  1. Task Developer – To create tasks that need to be run in the workflow
  2. Workflow Designer – To create a workflow by connecting tasks with links
  • Worklet Designer – To create a worklet

Q40. What is a Target Load Order?

Ans. Also known as Target Load Plan, a Target Load Order specifies the order of target loading by integration service. It is dependent on the source qualifiers in a mapping.

Q41. What is a Command Task?

Ans. A Command Task is used to run the shell/UNIX commands in Windows during the workflow. It allows a user to specify UNIX commands in the command task to remove rejected files, create files, copy files, rename files and archive files, among others.

Q42. What is Standalone Command Task?

Ans. Standalone Command Task allows the shell commands to run anywhere during the workflow.

Q43. What is the PowerCenter Repository?

Ans. A PowerCenter Repository is a relational database like Oracle and SQL server. It consists of the following Metadata –

  • Mapping
  • ODBC Connection
  • Session and session logs
  • Source Definition
  • Target Definition
  • Workflow

Q44. What is Snowflake Schema? What is its advantage?

Ans. Snowflake Schema is a logical arrangement where dimension tables are normalized in a multidimensional database. It is designed in a manner that it looks like a snowflake, thus the name. It contributes to improving the Select Query performance.

Q45. What are the Different Components of PowerCenter?

Ans. A PowerCenter has eight crucial components –

  1. PowerCenter Service
  2. PowerCenter Clients
  • PowerCenter Repository
  1. PowerCenter Domain
  2. Repository Service
  3. Integration Service
  • PowerCenter Administration Console
  • Web Service Hub

 

Q46. What does PowerCenter Client application comprise of?

Ans. PowerCenter Client application is comprised of the following tools:

  • Designer
  • Mapping Architect for Visio
  • Repository Manager
  • Workflow Manager
  • Workflow Monitor

Q47. How will you define Tracing Level?

Ans. Tracing Level refers to the amount of information that the server writes in the session log. Tracing Level is created and configured either at –

  • The transformation level
  • The session-level
  • Else at both the levels

Different types of Tracing Level are –

  • None
  • Terse
  • Verbose Initialization
  • Verbose Data

Q48. What is a Surrogate Key?

Ans. A Surrogate Key is any column or set of columns attached to every record in a Dimension table in a Data Warehouse. It is used as a substitute or replacement for the primary key when the update process becomes difficult for a future requirement.

Q49. What is a Session?

Ans. A session in Informatica is a set of instructions to be followed when data is being transferred from source to target using Session Command. A Session Command can be a pre-session command or a post-session command.

Q50. What is a User-Defined Event?

Ans. A User-Defined Event is a flow of tasks in a workflow. It allows users to create and name an event.

These are some of the popular questions asked during an Informatica interviews. Always be prepared to answer all types of questions — technical skills, interpersonal, leadership or methodology. If you have recently started your career in a data profile, you can always get certified to understand the industry-related terminology, skills and methodologies.

Summary