ADE Data Modeling & Schema Design 1 — Questions and Answers
Question 1: In a star schema, what is the primary role of a fact table?
- Store descriptive attributes about business entities
- Store measurable, quantitative data about business events (Correct answer)
- Define relationships between dimension tables
- Maintain historical records of slowly changing data
Correct answer: Store measurable, quantitative data about business events
A fact table stores quantitative metrics (measures) about business events, such as sales amounts or order counts, and references dimension tables via foreign keys.
Question 2: Which normal form eliminates transitive dependencies in a relational database?
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF) (Correct answer)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Third Normal Form (3NF)
Third Normal Form (3NF) eliminates transitive dependencies by ensuring every non-key column depends only on the primary key, not on other non-key columns.
Question 3: What distinguishes a snowflake schema from a star schema?
- Snowflake schemas have no fact tables
- Snowflake schemas normalize dimension tables into sub-dimensions (Correct answer)
- Snowflake schemas store data in columnar format
- Snowflake schemas use only surrogate keys
Correct answer: Snowflake schemas normalize dimension tables into sub-dimensions
In a snowflake schema, dimension tables are further normalized into related sub-dimension tables, reducing redundancy at the cost of more complex joins.
Question 4: A surrogate key in data modeling is best described as:
- A natural business key derived from source systems
- A composite key made from multiple natural keys
- A system-generated unique identifier with no business meaning (Correct answer)
- A foreign key that references a dimension table
Correct answer: A system-generated unique identifier with no business meaning
A surrogate key is a system-generated (often sequential integer or UUID) unique identifier that has no inherent business meaning, used to uniquely identify rows in a dimension table.
Question 5: Slowly Changing Dimension (SCD) Type 2 handles attribute changes by:
- Overwriting the old value with the new value
- Creating a new row with the updated value and tracking effective dates (Correct answer)
- Storing old and new values in separate columns on the same row
- Ignoring the change and keeping the original value
Correct answer: Creating a new row with the updated value and tracking effective dates
SCD Type 2 inserts a new dimension row for each change, adding effective start/end dates or a current flag to maintain full historical tracking of attribute changes.
Question 6: What is denormalization and when is it typically applied?
- The process of splitting tables to reduce redundancy, applied during data ingestion
- The process of combining normalized tables to reduce joins, applied in OLAP/analytical systems (Correct answer)
- The process of removing duplicate rows, applied during ETL cleaning
- The process of adding indexes to tables, applied to speed up queries
Correct answer: The process of combining normalized tables to reduce joins, applied in OLAP/analytical systems
Denormalization intentionally introduces redundancy by merging tables, reducing the number of joins needed and improving read performance in analytical (OLAP) workloads.
Question 7: In dimensional modeling, a 'conformed dimension' is one that:
- Has only one row per dimension member
- Is shared and has consistent meaning across multiple fact tables or data marts (Correct answer)
- Contains only current records with no historical versions
- Uses natural keys instead of surrogate keys
Correct answer: Is shared and has consistent meaning across multiple fact tables or data marts
A conformed dimension uses the same keys, column names, and definitions across multiple fact tables or subject areas, enabling consistent drill-across analysis.
ADE Data Modeling & Schema Design 1
In a star schema, what is the primary role of a fact table?