Sample questions
Data Modeling WarehousingDifficulty 1
In a dimensional data warehouse model, what is a fact table primarily used for?
- aStoring descriptive attributes about business entities like customer or product
- bStoring measures of a business process, with foreign keys to its dimensions✓
- cStoring only unique key values with no other columns
- dStoring the transaction log of the load job itself
Explanation:A fact table holds the measures/metrics of a business process (e.g., sales amount, quantity) together with foreign keys pointing to the dimension tables that give those measures context.
Data Modeling WarehousingDifficulty 1
What does a dimension table typically contain?
- aContext attributes: who, what, where, and when of an event✓
- bOnly numeric measures that get aggregated in reports
- cThe raw source system's transaction records, untransformed
- dIndex definitions used by the fact table
Explanation:Dimension tables carry the descriptive context (customer name, product category, store city, date attributes) that lets fact table measures be sliced and filtered.
Data Modeling WarehousingDifficulty 1
Which statement best describes a star schema?
- aA fact table joined to dimension tables split into further sub-tables
- bA set of fact tables with no dimension tables at all
- cA central fact table directly joined to flat dimension tables✓
- dA schema where every table only references itself
Explanation:In a star schema, dimensions are kept flat (denormalized) and connect directly to the fact table, which visually resembles a star with the fact table at the center.
Data Modeling WarehousingDifficulty 1
How does a snowflake schema differ from a star schema?
- aIt removes all foreign keys between fact and dimension tables
- bIt has no fact table, only dimension tables
- cIt stores facts and dimensions in the same table
- dIts dimension tables are normalized into related sub-tables✓
Explanation:A snowflake schema normalizes dimensions further, splitting them into multiple linked sub-tables, unlike the flat, denormalized dimensions of a star schema.
Data Modeling WarehousingDifficulty 1
In the context of a data warehouse, what is denormalization?
- aRemoving all keys between tables so they can no longer be joined
- bCombining data otherwise split across normalized tables✓
- cConverting numeric columns into text columns
- dEncrypting sensitive columns before loading
Explanation:Denormalization intentionally trades some redundancy for simpler, flatter table structures, which is common in dimension design to reduce joins.
Data Modeling WarehousingDifficulty 1
What is a surrogate key in a dimension table?
- aA system-generated ID, independent of any source-system key✓
- bThe exact identifier value used by the source operational system
- cA key that changes whenever a record's business meaning changes
- dA composite key made only of business attributes
Explanation:A surrogate key is generated by the warehouse itself and carries no business meaning, which keeps it stable regardless of what happens in the source system.