DAA-C01 Question 165
Single answerSelect and implement an effective data modelA retail company is redesigning its Snowflake analytics model for sales reporting. Analysts frequently join a 4-billion-row SALES_FACT table to CUSTOMER, PRODUCT, STORE, and DATE dimensions. Most dashboard queries aggregate revenue and units sold by day, product category, store region, and customer segment for the last 24 months. A separate data science team occasionally needs transaction-level detail with additional semi-structured promotion attributes stored in VARIANT. The current model is a highly normalized 3NF schema with many bridge tables, and BI performance is poor because queries require numerous joins. Which data modeling approach should the company implement to best balance dashboard performance, usability, and support for detailed analysis?
- A
Replace the 3NF model with a star schema centered on SALES_FACT, using denormalized dimensions for CUSTOMER, PRODUCT, STORE, and DATE, and keep the transaction-level promotion attributes in the fact table or a closely related detail structure.
- B
Convert the model into a fully normalized Data Vault and require BI tools to query hubs, links, and satellites directly for dashboard reporting.
- C
Store all sales, customer, product, and store attributes in a single wide flattened table to eliminate joins for every workload, including detailed data science exploration.
- D
Use a snowflake schema that further normalizes the PRODUCT and STORE hierarchies into additional lookup tables so dashboard queries scan fewer rows.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a star schema. For repeated BI queries against a large sales fact table, dimensional modeling remains a best practice because it optimizes for understandable business entities, predictable joins, and efficient aggregation paths. In this scenario, the company's current 3NF design is causing poor dashboard performance because analysts must traverse many tables. A star schema reduces that complexity by placing measures at the center and surrounding them with conformed dimensions such as CUSTOMER, PRODUCT, STORE, and DATE. This aligns with common Snowflake analytics patterns and with general dimensional modeling guidance from Kimball-style best practices. Snowflake supports both structured and semi-structured data, so the data science requirement for transaction-level detail and VARIANT promotion attributes does not require keeping the entire reporting model in 3NF. If the organization also needs an integrated enterprise model, it can maintain that upstream and publish dimensional marts for consumption. The key principle is selecting a model that matches the access pattern: star schema for high-volume dashboard reporting, while preserving detailed grain for advanced analysis.
- A. Correct.
Correct. For analytic reporting in Snowflake, a star schema is typically the most effective model when the workload consists of repeated aggregations across a large fact table and a manageable set of business dimensions. Denormalized dimensions reduce join complexity and improve usability for BI tools and analysts. Keeping transaction-level grain in the fact table preserves detailed analysis, while semi-structured promotion attributes can remain available for less frequent exploratory use without forcing the primary reporting model into excessive normalization. This approach directly addresses the stated pain point: poor BI performance due to many joins in the existing 3NF model.
- B. Incorrect.
Incorrect. Data Vault can be a strong pattern for enterprise data integration, auditability, and historization, but it is generally not the best presentation model for BI dashboards to query directly. Hubs, links, and satellites often increase join complexity for end-user analytics. In practice, Data Vault is commonly followed by dimensional marts such as star schemas for reporting consumption. Choosing this option reflects the misconception that an integration model is automatically the best consumption model.
- C. Incorrect.
Incorrect. A single wide flattened table may reduce joins, but it usually creates major drawbacks: duplicated dimension data, more storage churn when descriptive attributes change, reduced semantic clarity, and difficulty supporting both aggregated BI and detailed exploratory use cleanly. It can also complicate governance and maintenance, especially with slowly changing descriptive data. This option represents the common but oversimplified idea that eliminating joins entirely is always the best analytical design.
- D. Incorrect.
Incorrect. Further snowflaking dimensions usually increases the number of joins required by dashboard queries. While normalization can reduce redundancy in dimension tables, that benefit is typically outweighed in BI consumption models by added complexity and worse usability. In Snowflake, compute is separated from storage, but query simplicity and dimensional design still matter for analytics performance and maintainability. This option is attractive if someone assumes normalization inherently improves query performance, which is not generally true for dashboard-oriented dimensional models.