DMC Database Normalization and Design Patterns 2 — Questions and Answers
Question 1: What is the Star Schema pattern used in data warehouse modeling?
- A schema where all tables connect in a circular chain
- A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance (Correct answer)
- A fully normalized OLTP schema shaped like a star in the ERD
- A schema using only one large table with all data
Correct answer: A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance
The Star Schema places a central fact table containing measures and foreign keys surrounded by denormalized dimension tables, enabling fast analytical queries with simple joins.
Question 2: How does a Snowflake Schema differ from a Star Schema?
- Snowflake uses more fact tables; Star uses more dimension tables
- Snowflake normalizes dimension tables into sub-dimensions, reducing redundancy but requiring more joins; Star denormalizes for simplicity (Correct answer)
- Snowflake is for OLTP; Star is for OLAP
- Snowflake adds surrogate keys; Star uses natural keys
Correct answer: Snowflake normalizes dimension tables into sub-dimensions, reducing redundancy but requiring more joins; Star denormalizes for simplicity
A Snowflake Schema normalizes dimension tables by splitting hierarchies into separate tables, reducing data redundancy at the cost of more complex queries requiring additional joins.
Question 3: What is a fact table in dimensional modeling?
- A table containing only lookup codes and their descriptions
- A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables (Correct answer)
- A table that stores audit logs of all data changes
- A table containing master data like customers and products
Correct answer: A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables
A fact table stores measurable, quantitative data about business events — such as sales amounts, quantities, or counts — along with foreign keys linking to the relevant dimensions.
Question 4: What is a slowly changing dimension (SCD) Type 1?
- A dimension that is never updated once created
- A method of handling attribute changes by simply overwriting the old value, preserving no history (Correct answer)
- A dimension updated on a slow nightly schedule
- A dimension that stores one record per change with full history
Correct answer: A method of handling attribute changes by simply overwriting the old value, preserving no history
SCD Type 1 handles changes by overwriting the old attribute value with the new one, which is simple but destroys historical data — appropriate when history is not needed.
Question 5: In a Data Vault model, what does a Hub contain?
- All historical records for a business entity
- The unique business keys of a core business entity along with metadata about its source and load date (Correct answer)
- Only the relationships between two entities
- Descriptive attributes that change over time
Correct answer: The unique business keys of a core business entity along with metadata about its source and load date
A Hub in Data Vault modeling stores the unique business keys for a core business concept, along with a surrogate key, load date, and record source — no descriptive attributes.
Question 6: In Data Vault modeling, what is the purpose of a Link?
- To store descriptive attributes about an entity
- To capture the relationships and associations between two or more Hubs (Correct answer)
- To record the historical values of Hub business keys
- To connect the data vault to the presentation layer
Correct answer: To capture the relationships and associations between two or more Hubs
A Link in Data Vault modeling captures the many-to-many or associative relationships between Hubs, storing only the foreign keys to the related Hubs plus metadata.
DMC Database Normalization and Design Patterns 2
What is the Star Schema pattern used in data warehouse modeling?