BenchMark Quizzes by MDFT Pro

Select Join Columns for Star Schema

You work for MDFT Pro, a well-known training agency that offers certification courses. Claire, a Data Warehouse Architect at MDFT Pro, is continuing the star schema implementation for the CourseAnalytics warehouse. After denormalizing the ProductCategory, ProductSubcategory, and Product tables into a single product dimension table, she needs to establish the relationship between this dimension and the Sales fact table. The dimension table will contain attributes like product name, category name, subcategory name, price, and description. The Sales fact table will contain transaction-level data with columns for quantity sold, revenue, and discount amounts. Claire needs to choose the appropriate column type to use as the join key between these tables. The solution should provide optimal query performance, follow dimensional modeling best practices, and support potential future requirements like tracking historical changes to product attributes.

Which columns should Claire use to join the Sales fact table to the product dimension table?

Choose the correct answer from the options below.

Explanations for each answer:

The product name and date is incorrect. Using product name and date as join columns is inefficient and error-prone. Product names can change over time, may contain inconsistent formatting or spelling variations, and string-based joins perform poorly compared to integer joins. This approach also doesn't follow dimensional modeling best practices.
The product category name is incorrect. Using product category name as a join column is problematic because it doesn't uniquely identify individual products, provides insufficient granularity for joining to the Sales fact table, and suffers from the same performance issues as other text-based keys. A proper star schema requires unique product-level identifiers.
A unique system generated identifier is correct. A unique system-generated identifier (surrogate key) is the correct choice for joining the Sales fact table to the product dimension. Surrogate keys provide optimal join performance using integer values, enable proper handling of slowly changing dimensions, remain stable even when business attributes change, and follow dimensional modeling best practices for star schema design.

Learn more about dimension table design:

Dimension Table Design

Practice Exam

Select Join Columns for Star Schema

Discuss this question on social media: