ARA-C01 Question 124
Single answerStar schemaA retail company is redesigning its analytics platform in Snowflake for sales reporting. Analysts run thousands of dashboard queries each day that aggregate sales by date, product, store, and promotion. The current model stores most descriptive attributes directly in a wide SALES table, causing repeated storage of product and store attributes, complex update logic when attributes change, and inconsistent business definitions across reports. The architect proposes a star schema with a central SALES_FACT table and separate DATE_DIM, PRODUCT_DIM, STORE_DIM, and PROMOTION_DIM tables. Which approach best addresses the company's requirements while aligning with Snowflake and dimensional modeling best practices?
- A
Create a SALES_FACT table with numeric measures and foreign keys to conformed dimension tables, and keep descriptive business attributes in the dimensions to simplify reporting and maintain consistent definitions.
- B
Store all dimension attributes as VARIANT columns inside SALES_FACT so Snowflake can automatically optimize joins away and eliminate the need for separate dimension tables.
- C
Fully normalize product, store, and promotion data into many third-normal-form tables because this reduces joins for BI tools and is the preferred schema for high-volume analytic workloads in Snowflake.
- D
Replace the dimensional model with one large denormalized reporting table because Snowflake charges by compute, so star schemas should be avoided for frequently queried dashboards.
Show answer and explanation
Correct answer: A
Explanation
The best answer is Option 1 because the scenario explicitly highlights classic reasons to adopt a star schema: reducing repeated descriptive data, centralizing business definitions, simplifying reporting, and improving maintainability. In dimensional modeling, the fact table captures measurable business events at a defined grain, while dimensions provide descriptive context for slicing, filtering, and grouping. Conformed dimensions are especially important when multiple facts or reporting domains need consistent definitions for entities such as product or store. In Snowflake, star schemas remain a recommended pattern for analytics workloads because they are intuitive for BI tools and business users, even though Snowflake can also support normalized and semi-structured models. Snowflake documentation and dimensional modeling best practices emphasize designing for query patterns, simplicity, and governance rather than assuming that denormalization or semi-structured storage is inherently superior for all analytic use cases.
- A. Correct.
Correct. A star schema is designed for analytical workloads: the fact table stores measurable events such as sales amount, quantity, and discount, while dimension tables store descriptive attributes used for filtering, grouping, and reporting. Using conformed dimensions improves consistency across reports and subject areas, and reduces redundant storage of descriptive data. This also simplifies maintenance when attributes change, compared with embedding repeated attributes in the fact table.
- B. Incorrect.
Incorrect. VARIANT is useful for semi-structured data, but it is not a substitute for proper dimensional modeling in a star schema. Snowflake does not automatically eliminate the need for dimensional tables simply because data is stored in VARIANT. Putting core dimensional attributes into semi-structured columns inside the fact table would make governance, consistency, and BI usability worse, not better.
- C. Incorrect.
Incorrect. Third normal form can be appropriate for operational systems or certain integration layers, but it is generally not the preferred design for end-user analytics and dashboarding. A highly normalized model usually increases join complexity for BI tools and users. The misconception is that normalization automatically improves analytical usability; for dimensional analytics, star schemas are typically easier to query and understand.
- D. Incorrect.
Incorrect. While a single wide table may seem simple, it reintroduces the problems described in the scenario: repeated descriptive data, inconsistent business logic, and more difficult maintenance when attributes change. Snowflake's compute-based pricing does not imply that star schemas should be avoided. In many real analytic use cases, a star schema is a strong design choice because it balances usability, maintainability, and performance.