Data Engineering Data Warehouse Modeling 2 — Questions and Answers
Question 1: In a star schema, what is the central table that contains measurable business metrics called?
- Dimension table
- Fact table (Correct answer)
- Bridge table
- Lookup table
Correct answer: Fact table
The fact table sits at the center of a star schema and holds quantitative measures plus foreign keys to dimensions.
Question 2: A snowflake schema differs from a star schema primarily because its dimension tables are:
- Denormalized into one flat table
- Normalized into multiple related tables (Correct answer)
- Removed entirely
- Stored as JSON blobs
Correct answer: Normalized into multiple related tables
Snowflake schemas normalize dimensions into multiple related tables, reducing redundancy at the cost of more joins.
Question 3: Which type of fact table stores a row only when an event occurs, such as a single sale?
- Snapshot fact table
- Accumulating snapshot
- Transaction fact table (Correct answer)
- Factless fact table
Correct answer: Transaction fact table
A transaction fact table records one row per discrete business event as it happens.
Question 4: What is the grain of a fact table?
- The number of dimensions attached
- The level of detail represented by one row (Correct answer)
- The total row count
- The storage format used
Correct answer: The level of detail represented by one row
Grain defines exactly what a single fact row represents, such as one line item per order.
Question 5: A factless fact table is most commonly used to:
- Store large text measures
- Record events or coverage with no numeric measures (Correct answer)
- Replace dimension tables
- Compress fact data
Correct answer: Record events or coverage with no numeric measures
Factless fact tables capture the occurrence of events or relationships without storing numeric measures.
Question 6: Which key type uniquely identifies a row in a dimension table within the warehouse, independent of the source system?
- Natural key
- Surrogate key (Correct answer)
- Composite key
- Foreign key
Correct answer: Surrogate key
A surrogate key is a warehouse-generated integer that uniquely identifies dimension rows regardless of source identifiers.
Question 7: Conformed dimensions are valuable in a data warehouse because they:
- Eliminate all fact tables
- Are shared consistently across multiple fact tables or data marts (Correct answer)
- Store only numeric data
- Require no surrogate keys
Correct answer: Are shared consistently across multiple fact tables or data marts
Conformed dimensions have consistent meaning and content across fact tables, enabling cross-process analysis.
Data Engineering Data Warehouse Modeling 2
In a star schema, what is the central table that contains measurable business metrics called?