Data Engineering Data Modeling and Schema Design 2 — Questions and Answers
Question 1: Which normal form requires that every non-key attribute is fully functionally dependent on the entire primary key, not just part of it?
- First Normal Form (1NF)
- Second Normal Form (2NF) (Correct answer)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
Correct answer: Second Normal Form (2NF)
2NF eliminates partial dependencies, requiring every non-key attribute to depend on the whole composite primary key.
Question 2: A fact table with very few numeric measures that primarily stores the occurrence of an event is called a:
- Transaction fact table
- Accumulating snapshot fact table
- Factless fact table (Correct answer)
- Periodic snapshot fact table
Correct answer: Factless fact table
A factless fact table records events or coverage relationships without any numeric measures, only keys to dimension tables.
Question 3: What is the purpose of a 'junk dimension' in dimensional modeling?
- To store low-quality or erroneous data
- To combine multiple low-cardinality flags and indicators into a single dimension (Correct answer)
- To archive obsolete dimension records
- To store frequently changing attributes separately
Correct answer: To combine multiple low-cardinality flags and indicators into a single dimension
A junk dimension consolidates miscellaneous low-cardinality flags and indicator fields into one dimension to reduce fact table width.
Question 4: In the Kimball methodology, what is the 'Bus Matrix' used for?
- Scheduling ETL job dependencies
- Mapping which business processes share which conformed dimensions (Correct answer)
- Documenting data lineage from source to target
- Defining the physical partitioning strategy
Correct answer: Mapping which business processes share which conformed dimensions
The Enterprise Bus Matrix maps each business process (fact table) against the dimensions it shares, ensuring conformed dimensions across the data warehouse.
Question 5: Which schema type is most appropriate when querying across multiple star schemas that share common dimensions?
- Snowflake schema
- Star schema
- Galaxy schema (fact constellation) (Correct answer)
- Flat schema
Correct answer: Galaxy schema (fact constellation)
A galaxy schema (fact constellation) consists of multiple fact tables sharing conformed dimension tables, enabling cross-subject-area queries.
Question 6: What is 'grain' in the context of a fact table?
- The smallest unit of data that can be stored
- The level of detail represented by each row in the fact table (Correct answer)
- The number of foreign keys in the fact table
- The precision of numeric measures
Correct answer: The level of detail represented by each row in the fact table
Grain defines exactly what one row in a fact table represents, and declaring the grain is the most critical step in dimensional design.
Data Engineering Data Modeling and Schema Design 2
Which normal form requires that every non-key attribute is fully functionally dependent on the entire primary key, not just part of it?