You have a Fabric warehouse named DW1 that contains four staging tables named ProductCategory, ProductSubcategory, Product, and SalesOrder. ProductCategory, ProductSubcategory, and Product are used…

This question tests your ability to design a star schema in Microsoft Fabric warehouse with minimal development effort, specifically knowing when to denormalize staging tables into dimensions using views rather than full ETL pipelines.

Design and implement data modeling

Question

You have a Fabric warehouse named DW1 that contains four staging tables named ProductCategory, ProductSubcategory, Product, and SalesOrder. ProductCategory, ProductSubcategory, and Product are used often in analytical queries. You need to implement a star schema for DW1. The solution must minimize development effort. Which design approach should you use? To answer, select the appropriate options in the answer area.

Explanation

Approach. The correct approach is to create a single denormalized DimProduct view by joining ProductCategory, ProductSubcategory, and Product into one flattened dimension table (or view), and treat SalesOrder as the FactSalesOrder table. Since ProductCategory, ProductSubcategory, and Product are hierarchically related and frequently queried together, collapsing them into one star-schema dimension avoids a snowflake schema - which would require more joins and more development work. Using views (rather than physical tables with full ETL) on top of the existing staging tables minimizes development effort because no data movement or transformation pipelines are needed. SalesOrder naturally becomes the central fact table since it contains transactional/measurable data (sales events) that reference the product dimension.

Concept tested. Star schema design in Microsoft Fabric Warehouse - specifically the tradeoff between star schema (denormalized, single dimension per subject area) vs. snowflake schema (normalized, multiple related dimension tables), and how creating views over staging tables reduces development effort compared to building full ETL pipelines into new physical tables.

Reference. Microsoft Learn: Model data in Microsoft Fabric - Implement a star schema; DP-600 / DP-700 exam objectives covering data warehouse design patterns in Microsoft Fabric

Topics

#Dimensional Modeling#Star Schema#Data Warehouse Design#Fabric Warehouse

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice