Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
**Question 1. Which characteristic best describes a Data Warehouse?**
A) Real‑time transaction processing
B) Volatile, frequently changing data
C) Integrated, subject‑oriented, time‑variant, non‑volatile collection of data
D) Designed only for operational reporting
Answer: C
Explanation: A Data Warehouse stores integrated, subject‑oriented data that is time‑variant and
non‑volatile, optimized for analytical queries rather than real‑time transactions.
**Question 2. In the context of OLTP vs. OLAP, which statement is true?**
A) OLTP systems typically use star schemas.
B) OLAP queries are generally short and involve single‑row transactions.
C) OLTP focuses on read‑only operations, while OLAP supports heavy writes.
D) OLAP systems are optimized for complex, multi‑dimensional queries.
Answer: D
Explanation: OLAP (Online Analytical Processing) is designed for complex, multi‑dimensional
analytical queries, whereas OLTP (Online Transaction Processing) handles high‑volume, short,
write‑heavy transactions.
**Question 3. What is the primary purpose of an Operational Data Store (ODS)?**
A) To store historical data for long‑term analysis
B) To provide a consolidated, near‑real‑time view of operational data for reporting
C) To replace the Data Warehouse entirely
D) To store only master data
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
Answer: B
Explanation: An ODS integrates data from multiple operational systems and provides a current,
consolidated view for operational reporting, but it does not store long‑term historical data like a
Data Warehouse.
**Question 4. Which type of fact table stores values that can be summed across all
dimensions?**
A) Additive
B) Semi‑additive
C) Non‑additive
D) Degenerate
Answer: A
Explanation: Additive facts can be summed across any dimension, such as sales amount.
Semi‑additive facts can be summed across some dimensions only (e.g., inventory balance over
time). Non‑additive facts cannot be summed.
**Question 5. In a star schema, how are dimension tables typically related to the fact table?**
A) Many‑to‑many via bridge tables
B) One‑to‑many from dimension to fact
C) One‑to‑one across all tables
D) No relationships, they are independent
Answer: B
Explanation: In a star schema, each dimension table has a one‑to‑many relationship with the
fact table (one dimension row can relate to many fact rows).
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
**Question 6. Which schema design reduces data redundancy by normalizing dimension
tables?**
A) Star schema
B) Snowflake schema
C) Fact constellation
D) Galaxy schema
Answer: B
Explanation: A snowflake schema normalizes dimension tables into multiple related tables,
reducing redundancy at the cost of more complex joins.
**Question 7. Which ETL extraction technique captures only changed rows since the last
load?**
A) Full extraction
B) Incremental extraction using Change Data Capture (CDC)
C) Bulk extraction without filters
D) Random sampling
Answer: B
Explanation: Incremental extraction with CDC identifies and extracts only rows that have
changed (inserts, updates, deletes) since the previous load, improving efficiency.
**Question 8. In transformation logic, which operation is performed by an Expression
transformation?**
A) Row filtering based on a condition
B) Joining two data streams
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
C) Performing calculations, string manipulation, or data type conversion on a row
D) Aggregating rows into summary totals
Answer: C
Explanation: An Expression transformation allows you to compute derived columns, convert
data types, and manipulate strings on a per‑row basis.
**Question 9. What is the difference between bulk loading and normal loading in
Informatica?**
A) Bulk loading uses the target’s native bulk API; normal loading inserts rows one at a time.
B) Bulk loading always commits after each row; normal loading never commits.
C) Bulk loading is only for flat files; normal loading is for databases.
D) There is no functional difference; they are synonyms.
Answer: A
Explanation: Bulk loading leverages the target database’s bulk‑load utilities (e.g., Oracle
SQL*Loader) for high‑throughput inserts, while normal loading uses standard insert statements
per row.
**Question 10. Why are surrogate keys used in dimensional models?**
A) To replace primary keys from source systems with stable, system‑generated identifiers
B) To encrypt sensitive data in the warehouse
C) To enforce referential integrity across all tables automatically
D) To improve query performance by using natural keys
Answer: A
Certificate Practice Exam
**Question 1. Which characteristic best describes a Data Warehouse?**
A) Real‑time transaction processing
B) Volatile, frequently changing data
C) Integrated, subject‑oriented, time‑variant, non‑volatile collection of data
D) Designed only for operational reporting
Answer: C
Explanation: A Data Warehouse stores integrated, subject‑oriented data that is time‑variant and
non‑volatile, optimized for analytical queries rather than real‑time transactions.
**Question 2. In the context of OLTP vs. OLAP, which statement is true?**
A) OLTP systems typically use star schemas.
B) OLAP queries are generally short and involve single‑row transactions.
C) OLTP focuses on read‑only operations, while OLAP supports heavy writes.
D) OLAP systems are optimized for complex, multi‑dimensional queries.
Answer: D
Explanation: OLAP (Online Analytical Processing) is designed for complex, multi‑dimensional
analytical queries, whereas OLTP (Online Transaction Processing) handles high‑volume, short,
write‑heavy transactions.
**Question 3. What is the primary purpose of an Operational Data Store (ODS)?**
A) To store historical data for long‑term analysis
B) To provide a consolidated, near‑real‑time view of operational data for reporting
C) To replace the Data Warehouse entirely
D) To store only master data
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
Answer: B
Explanation: An ODS integrates data from multiple operational systems and provides a current,
consolidated view for operational reporting, but it does not store long‑term historical data like a
Data Warehouse.
**Question 4. Which type of fact table stores values that can be summed across all
dimensions?**
A) Additive
B) Semi‑additive
C) Non‑additive
D) Degenerate
Answer: A
Explanation: Additive facts can be summed across any dimension, such as sales amount.
Semi‑additive facts can be summed across some dimensions only (e.g., inventory balance over
time). Non‑additive facts cannot be summed.
**Question 5. In a star schema, how are dimension tables typically related to the fact table?**
A) Many‑to‑many via bridge tables
B) One‑to‑many from dimension to fact
C) One‑to‑one across all tables
D) No relationships, they are independent
Answer: B
Explanation: In a star schema, each dimension table has a one‑to‑many relationship with the
fact table (one dimension row can relate to many fact rows).
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
**Question 6. Which schema design reduces data redundancy by normalizing dimension
tables?**
A) Star schema
B) Snowflake schema
C) Fact constellation
D) Galaxy schema
Answer: B
Explanation: A snowflake schema normalizes dimension tables into multiple related tables,
reducing redundancy at the cost of more complex joins.
**Question 7. Which ETL extraction technique captures only changed rows since the last
load?**
A) Full extraction
B) Incremental extraction using Change Data Capture (CDC)
C) Bulk extraction without filters
D) Random sampling
Answer: B
Explanation: Incremental extraction with CDC identifies and extracts only rows that have
changed (inserts, updates, deletes) since the previous load, improving efficiency.
**Question 8. In transformation logic, which operation is performed by an Expression
transformation?**
A) Row filtering based on a condition
B) Joining two data streams
, Informatica Tutorial Beginner to Expert Level
Certificate Practice Exam
C) Performing calculations, string manipulation, or data type conversion on a row
D) Aggregating rows into summary totals
Answer: C
Explanation: An Expression transformation allows you to compute derived columns, convert
data types, and manipulate strings on a per‑row basis.
**Question 9. What is the difference between bulk loading and normal loading in
Informatica?**
A) Bulk loading uses the target’s native bulk API; normal loading inserts rows one at a time.
B) Bulk loading always commits after each row; normal loading never commits.
C) Bulk loading is only for flat files; normal loading is for databases.
D) There is no functional difference; they are synonyms.
Answer: A
Explanation: Bulk loading leverages the target database’s bulk‑load utilities (e.g., Oracle
SQL*Loader) for high‑throughput inserts, while normal loading uses standard insert statements
per row.
**Question 10. Why are surrogate keys used in dimensional models?**
A) To replace primary keys from source systems with stable, system‑generated identifiers
B) To encrypt sensitive data in the warehouse
C) To enforce referential integrity across all tables automatically
D) To improve query performance by using natural keys
Answer: A
