Data Engineering Data Modeling and Schema Design 1 — Questions and Answers
Question 1: Which schema design uses a central fact table surrounded by multiple dimension tables with no normalization of dimensions?
- Snowflake schema
- Star schema (Correct answer)
- Galaxy schema
- Flat schema
Correct answer: Star schema
A star schema has a central fact table directly joined to denormalized dimension tables, resembling a star shape.
Question 2: In a snowflake schema, dimension tables are normalized into multiple related tables primarily to:
- Improve query speed
- Reduce storage redundancy (Correct answer)
- Increase fact table rows
- Simplify ETL processes
Correct answer: Reduce storage redundancy
Snowflake schemas normalize dimensions to reduce data redundancy, trading query simplicity for storage efficiency.
Question 3: What is a surrogate key in dimensional modeling?
- A natural business key from the source system
- A composite key made of multiple columns
- A system-generated integer used as the primary key for a dimension (Correct answer)
- A foreign key referencing the fact table
Correct answer: A system-generated integer used as the primary key for a dimension
A surrogate key is a system-generated (usually integer) identifier assigned to dimension records, independent of source system keys.
Question 4: Which modeling approach is best suited for ad-hoc analytics and is optimized for read performance in data warehouses?
- Third Normal Form (3NF)
- Dimensional modeling (Correct answer)
- Entity-Relationship modeling
- Graph modeling
Correct answer: Dimensional modeling
Dimensional modeling (Kimball approach) uses denormalized star/snowflake schemas optimized for analytical read queries.
Question 5: In data vault modeling, which component stores the unique business keys and acts as the hub of integration?
- Link
- Satellite
- Hub (Correct answer)
- Bridge
Correct answer: Hub
Hubs in data vault modeling store unique business keys and serve as the integration point between different source systems.
Question 6: What does a 'degenerate dimension' refer to in dimensional modeling?
- A dimension with very low cardinality
- A dimension key stored in the fact table with no corresponding dimension table (Correct answer)
- A deprecated dimension no longer in use
- A dimension derived from another dimension
Correct answer: A dimension key stored in the fact table with no corresponding dimension table
A degenerate dimension is a dimension attribute (like an order number) stored directly in the fact table without a separate dimension table.
Data Engineering Data Modeling and Schema Design 1
Which schema design uses a central fact table surrounded by multiple dimension tables with no normalization of dimensions?