Answer-first summary for fast verification
Answer: Implement a star schema with a central fact table for sales transactions linked to dimension tables for time, products, customers, and stores. This approach simplifies queries and enhances performance by denormalizing dimension tables.
The star schema (Option A) is the most appropriate choice for this scenario. It balances the need for efficient query performance with the simplicity of the data model, making it ideal for read-heavy analytical workloads typical in retail analytics. The central fact table allows for quick aggregation of sales data, while the denormalized dimension tables provide easy access to related information without complex joins. This design supports scalability and performance, meeting the company's current and future needs.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
As a Microsoft Fabric Analytics Engineer Associate, you are designing a data warehouse schema for a multinational retail company. The company operates in multiple regions, has a vast inventory of products, and serves millions of customers. The schema must support complex analytical queries across these dimensions efficiently, while also considering scalability and performance for future growth. Additionally, the solution must minimize data redundancy without compromising query performance. Given these requirements, which of the following schema designs would you recommend? (Choose one)
A
Implement a star schema with a central fact table for sales transactions linked to dimension tables for time, products, customers, and stores. This approach simplifies queries and enhances performance by denormalizing dimension tables.
B
Adopt a snowflake schema where dimension tables are normalized to reduce data redundancy. This method involves more complex joins but can save storage space.
C
Use a flat schema with all sales transaction data, including product, customer, and store information, stored in a single table. This approach eliminates the need for joins but may lead to data redundancy and slower query performance.
D
Design a graph-based schema to model the intricate relationships between products, customers, and stores. This approach is highly flexible but may not be the most efficient for standard analytical queries.