Informatica MCQ

Q 1) Full form of ETL is  ………………….

  1. Enable, Transaction, Load
  2. Extract, Transform, Load
  3. Exact, Transform, Limited
  4. Enable, Transform, Load

Sol: B) Extract, Transform, Load

Explanation: ETL is a tool for data extraction, transformation, and loading into the target database/ warehouse.

Q 2) In which year was Informatica established?

  1. 2000
  2. 1993
  3. 1995
  4. 1991

Solution: B) 1993

Explanation: In Febuary1993, the Informatica tool stirred the IT industry, and Gaurav Dhillon and Diaz Nesamoney founded a tool that can do multiple functions in handling data over different data formats.

Q 3) Informatica is a/an ………………..

  1. Model
  2. ETL tool
  3. Database controller
  4. Error detector

Solution: an ETL tool

Explanation: Informatica is a tool that does data extraction, transform and loading into the target database. This is used to handle a large amount of data in business.

Q 4) Choose the components provided by the Informatica ETL tool?

  1. Repository manager
  2. Workflow monitor
  3. Informatica designer
  4. All of above

Solution: All of above

Explanation: Informatica provides various components:

  1. Repository manager
  2. Informatica workflow manager
  3. Informatica administrator control
  4. Informatica power center designer

Q 5) The process of upgrading the data quality before loading it to the final data warehouse.

  1. Data cleansing
  2. Data scrubbing
  3. Data transformation
  4. Both a and b

Solution:  Both a and b

Explanation: Data cleansing is the process of finding and cleaning the data from unwanted errors, duplicity in corrected and corrupted data.

Data scrubbing is a process of cleaning the data before it is saved to the data warehouse.

Q 6) Does Informatica is a company?

  1. Yes
  2. No

Solution: a) Yes

Explanation: Informatica is a software company that focuses on data integration, masking, extraction, transformation, data loading, master data management, data virtualization, data quality, data replication, etc. Informatica company has launched various versions of Informatica for handling the data.

Q 7) Find the active transformation among these?

  1. Filter
  2. Expression
  3. Normalizer
  4. Router

Solution: C) Normalizer

Explanation: In Informatica, transformation is of two types:

  1. Active – Inactive transformation number of rows change form source to target.
  2. Passive – In this transformation number of rows remain the same from source to target.
Transformation Active/ Passive
Normalizer Active
Rank Active
Router Active
Source qualifier Active
Update strategy Active
Sorter Active
Advance external procedure Active
External procedure Active
Filter Active
Joiner Active
Aggregator Active
Expression Passive
Sequence generator Passive
Lookup Passive
XML source qualifier Passive
Stored procedure Passive

Q 8) External procedure is ______________ transformation.

  1. Unconnected
  2. Connected
  3. Both
  4. None

Solution: C) Both

Explanation: In Informatica, transformation can be of two kinds:

  1. Connected – In this case, transformations are connected or with the target directly.
  2. Unconnected – In this case, transformations are not connected physically. Instead, they called inside another transformation.
Transformation Connected/ unconnected
Normalizer Connected
Rank Connected
Router Connected
Source qualifier Connected
Update strategy Connected
Sorter Connected
Advance external procedure Connected
External procedure Connected/ Uncontrolled
Filter Connected
Joiner Connected
Aggregator Connected
Expression Connected
Sequence generator Connected
Lookup Connected/ Uncontrolled
XML source qualifier Connected
Stored procedure Connected/ Uncontrolled

Q 9) Find which of the following client component used for creating the repository?

  1. Repository manager
  2. Workflow manager
  3. Repository server administrator Console
  4. Designer

Solution: C) Repository server administrator Console

Explanation: The repository server administration console is one of the client components used for creating a repository because it contains all the data needed in the database.

Q 10) ______________ transformation sorts the data according to a specified condition in ascending or descending order.

  1. Lookup
  2. Joiner
  3. Sorter
  4. Source qualifier

Solution: C) Sorter

Explanation:  Sorter transformation is an active transformation that sorts the data in ascending or descending order, and it is connected to the data flow. It is also configured for the case-sensitive sorting as it specifies that output rows must be distinct.

Q 11) Which is not a reusable transformation among these?

  1. Source qualifier
  2. Aggregator
  3. External procedure
  4. Filter

Solution: A) Source qualifier

Explanation: Source qualifier is not a reusable transformation as any changes made in this transformation cannot be reflected in mapping transformations.

Q 12) Choose the type of lookup cache?

 Dynamic Cache, Volatile Cache, Non-volatile cache, Static Cache, Re Cache form Database, Persistence cache

  1. Non-volatile cache, Static cache, Dynamic cache, Persistence cache
  2. Re cache from database, Volatile cache, Static cache, Dynamic cache
  3. Volatile cache, Non- volatile cache, Dynamic cache, Persistence cache
  4. Dynamic Cache, Re cache from database, Static cache, Persistence cache

Solution: D) Dynamic cache, Re cache from DB, Static cache, Persistence cache


The lookup cache file can be saved in the following caches:

Dynamic Cache: A lookup transformation is created to use a dynamic cache to cache the target table and insert new tables in the cache. The Informatica server dynamically inserts data to the target tables.

Persistent cache: This cache is used to save lookup cache filed and reuse them next time. A lookup transformation is processed by the Informatica server configured to use the cache.

Static cache: Informatica server creates a static cache by default, and it can be considered only by looking up tables. Static cache catches the lookup values and tables for each row that comes in transformation. Informatica server doesn't update the cache when the lookup condition is true.

Re cache from the database: Lookup transformation is configured to rebuild the cache when the persistent cache is not synchronized with the lookup table.

Q 13) Which data type is used in comparing filter and aggregate transformation?

  1. Integer
  2. Char
  3. Double
  4. String

Solution: A) Integer

Explanation: Integer data type is used in comparing filter and aggregate transformation as the only integer data type is entered in the tables.

Q 14) In workflow manager, which of the following are not control tasks?

  1. Check the performance of the session
  2. Email notification
  3. Alert notification
  4. All the above

Solution: D) All the above

Explanation: There are various tasks supported in the control task of workflow manager:-

  • Abort parent
  • Stop parent
  • Fail parent
  • Fail me
  • Stop top-level workflow
  • Abort top-level workflow
  • Fail top-level workflow

Q 15) Find the type of Joins in Informatica?

  1. Left outer
  2. Master outer
  3. Full outer
  4. Outer join

Solution: B and C (master and full outer)

Explanation: Types of joins supported in Informatica

  • Master outer – In Informatica, master outer join returns all the records form detail rows and only matching rows form the master table.
  • Normal outer – It is a default join in Informatica that returns only matching rows from detail and master tables.
  • Detail outer – This type of join simply shows all master rows from the master table and matching rows from the detail table.
  • Full outer – This joins shows all the matching and non-matching rows from both detail and master table.

Q 16) Among these, which can be managed on the security page of the administrator tool?

  1. Groups
  2. Users
  3. Privileges and roles
  4. All of them

Solution: D) All of them

Explanation: All the users, user groups, privileges, and roles are managed on the security page of the administrator tool in Informatica as a security task creates the users and groups in the repository. The repository services stored all the permission, role, and privilege assignments with the list of groups and users in the repository.

Q 17) Function/s of security manager in Informatica

  1. Store user and group in the domain configuration database.
  2. The list of users and groups is copied to the power center repository.
  3. Synchronize the list of users and groups in the repository.
  4. All of them

Solution: D) All of them

Explanation: The security manager plays an important role in the administrator tool. It stores users and groups in the domain configuration database and synchronizes the list of users and groups in the repository with the domain configuration database. The service manager copies the list of users and groups to the power center repository.

Q 18) Types of task user can do in the designer.

  1. Create mapping b)Create applet c) Import/ Export registry d) Import/ Export repository objects.
  1. a, c, d
  2. a, b, d
  3. b, c, d
  4. a, b, c

Solution: 2) a, b, d.

Explanation: Designer is a graphical user interface in Informatica that manage task like mapping, mapplet, transformation, and import/ export of objects.

Q 19) What’s the result of running a maximum number of sessions and the full database log file?

  1. Session fails
  2. Session log out
  3. Error occurs
  4. Session expires

Solution: A) Session fails.

Explanation: In Informatica, when a maximum number of sessions run and the database log file is full, that session fails. It doesn’t work out, and the user has to start a new session with space in the database for files.

Q 20) _____________ are the entries kept by enterprise as record.

  1.  Domains
  2. Dimensions
  3. Applications
  4. None of them

Solution: B) Dimensions

Explanation: Dimensions are the entries kept by the enterprise as a record. Dimensions tables contain the information related to a subject like subject keys, values, and attributes.

Q 21)  Name the table that consists of the record of measurement of a subject?

  1. Fact table
  2. Dimension table
  3. Both
  4. None of them

Solution: A) Fact table

Explanation: The fact table and dimension table both are different as a fact table consists of the measurements of a subject, and a dimension table consists of information related to dimensions of the subject, such as attributes, keys, and values about the subject.

Q 22) It returns the matching or common rows between two result sets.

  1. Intersect operator
  2. Minus operator
  3. Addition operator
  4. Division operator

Solution: A) Intersect operator.

Explanation: When the intersect operator is applied in tables, only the common or matching rows between the two result sets.

Q 23) Overflow data are stored in _____________ files.

  1. Metadata
  2. Cache
  3. RAM
  4. Database

Solution: B) Cache

Explanation: Overflow data is stored in cache files as the cache is a partial memory that stores the relatable files when files don't find space in the database.

Q 24) Informatica distributes the data evenly in equal proportion called as____.

  1. Hash portioning
  2. Round-Robin portioning
  3. Both a & b
  4. None

Solution: B) Round- Robin portioning

Explanation: Informatica uses round-robin portioning where the number of rows to process is distributed in evenly equal proportion.

Informatica server uses hash portioning to ensure the process groups of rows with the same portioning keys in the same partition.

Q 25)  A column or set of columns is also declared as a primary key instead of a natural or real key.

  1. Primary key
  2. Surrogate key
  3. Candidate key
  4. None of them

Solution: B) Surrogate key

Explanation: A surrogate key can be declared a primary key instead of a natural or real key. The primary key is the column's constraint that ensures that the column doesn't have any NULL value, and every value must be unique.

Q 26) Match the following:

a) Session 1) Create or configure a set of transformations.
b) Mapping 2) A set of information that makes data move from source to target.
c) Workflow 3) It represents data flow from source to target.
d) Mapplet 4) These are the set of instructions that are used to execute the task Informatica by the Informatica server.
  1. A-2, B-4, C-1, D-3
  2. A-1, B-2, C-3, D-4
  3. A-2, B-3, C-4, D-1
  4. A-4, B-3, C-2, D-1

Solution: C) A-2, B-3, C-4, D-1

Explanation: A session is the set of information that makes data move from source to target.

Mapping is the method of representing how data flows from source to target.

Workflow is the set of instructions used to execute the task in Informatica by the Informatica server.

Mapplet – Creates or configure the set of transformations to execute the task.

Q 27) Choose the type of bug that doesn’t allow to enter of valid values in ETL testing.

  1. Race condition bug
  2. Calculation bug
  3. Input/ Output bug
  4. Load condition bug
  5. All of them
  6. None of them

Solution: E) All of them

Explanation: During the testing of ETL tools, various bugs can occur that create an error in entering the valid values for testing. Types of bugs are:

  • Race condition bug hangs and crashes the system.
  • Cosmetic bug relates to the application's graphical user interface that includes font size, font style, navigation, spelling mistake, etc.
  • Input/ output bug controls the input and output values. It does not accept the valid values (only accept invalid values).
  • A calculation Bug is a mathematical bug that does mistakes in calculating.
  • Hardware bug reflects that the application is not supported by hardware.
  • Other bugs are:- Help source bug, version control bug, Load condition bug, boundary analysis related bug, Equivalence class partitioning related bug.

Q 28)  Name the area that holds the data in the ETL process where data cleansing and merging are performed before loading it to the data warehouse?

  1. Staging area
  2.  Hard disk
  3. Data Mart
  4. System memory

Solution: A) Staging area

Explanation:  In the ETL process, data is initially held by the staging area, where data cleansing and merging are performed before loading it to the data warehouse.

Q 29) How many stages are there in ETL testing?

  1. 3
  2. 5
  3. 7
  4. 2

Solution: B) 5

Explanation:  There are five stages in ETL testing in Informatica.

  • Identifying the requirements and data sources
  • Data acquisition
  • Implementation of dimensional modeling and business logic on the acquired data.
  • All the data is populated, and new data is formed,
  • Build reports from the newly formed data.

Q 30) ETL testing type includes testing data type checks, length checks, and index/ constraint checks.

  1. Data accuracy testing
  2. Metadata testing
  3. Data completeness testing
  4. All of above

Solution: B) Metadata testing

Explanation: Metadata testing is the type of  ETL testing that includes testing of data length check, Index/ constraint check, and data type check.

Data accuracy testing is carried out to check whether the data is accurately loaded and transformed as expected.

Data completeness testing ensures that all the data is loaded to the target from the source completely. Tests are run to compare and validate counts, aggregates, and actual data between the source and target for columns with or without specific or simple transformations.