ARA-C01 Question 125
Single answerStar schemaA retail company is redesigning its analytics platform in Snowflake for executive sales reporting. The current model stores order headers, order lines, product attributes, customer attributes, and store attributes in a single wide table. Analysts frequently filter by product category, customer segment, store region, and calendar month, then aggregate revenue and units sold. The table is reloaded daily, and product and customer attributes can change over time. Query performance is inconsistent, storage costs are increasing, and business users want clearer metric definitions. As the Snowflake architect, which design should be recommended to best support this workload?
- A
Create a star schema with a central fact table for order line measures and surrounding dimension tables for product, customer, store, and date, using surrogate keys and handling attribute history in dimensions where required.
- B
Keep the single wide table, but create additional clustering keys on every frequently filtered attribute so analysts can continue querying one denormalized structure.
- C
Replace the wide table with a fully normalized third normal form model for all transactional entities, so Snowflake can optimize joins automatically during aggregation queries.
- D
Create separate fact tables for product, customer, store, and date so each business attribute can be queried independently and joined only when needed.
Show answer and explanation
Correct answer: A
Explanation
The best recommendation is to redesign the model as a star schema. For analytic workloads in Snowflake, a dimensional model is typically preferred when users repeatedly slice and aggregate measures across common business dimensions. In this scenario, revenue and units sold belong in a fact table at a clearly defined grain, such as order line. Product, customer, store, and date belong in dimension tables that support filtering, grouping, and descriptive analysis. This also improves business consistency because measures and attributes are modeled explicitly rather than embedded in one large denormalized structure. For changing attributes, dimensional techniques such as preserving history in dimensions are appropriate when the business requires historical accuracy. Snowflake documentation and best practices emphasize choosing data models based on workload, and dimensional models remain a strong design choice for reporting and BI use cases. Physical tuning features such as clustering can help specific large-table access patterns, but they should complement rather than replace a sound logical model.
- A. Correct.
Correct. A star schema is the appropriate dimensional model for this reporting pattern. The central fact table should hold additive measures such as revenue and units sold at the chosen grain, such as order line. Dimension tables for product, customer, store, and date provide descriptive attributes for filtering and grouping. This approach improves semantic clarity, supports conformed dimensions, and is well aligned with BI workloads that aggregate measures by business dimensions. Using surrogate keys in dimensions is standard dimensional modeling practice, and handling changing product and customer attributes in dimensions supports historical reporting needs.
- B. Incorrect.
Incorrect. A single wide table may reduce joins, but it mixes transactional facts and descriptive attributes, often causing duplication, larger storage footprint, and weaker governance for metric definitions. Adding clustering keys on every filtered attribute is also not a sound design recommendation. Snowflake supports clustering, but excessive clustering dimensions can increase maintenance cost and may not produce consistent benefit. Clustering is a physical optimization, not a substitute for a proper dimensional model.
- C. Incorrect.
Incorrect. A fully normalized 3NF model is often suitable for operational integration and data maintenance, but it is generally less optimal than a dimensional model for high-volume analytical reporting. Executive reporting that repeatedly aggregates sales by common descriptive dimensions is a classic use case for a star schema. Snowflake can process joins efficiently, but that does not mean 3NF is the best logical design for BI consumption.
- D. Incorrect.
Incorrect. Product, customer, store, and date are dimensions, not facts. Creating fact tables for descriptive business entities is a modeling error because facts should capture measurable business events at a defined grain. Splitting dimensions into separate fact-like structures would complicate joins, obscure business meaning, and make aggregate reporting less reliable.