ARA-C01 Question 121
Single answerData modelsA retail company is redesigning its analytics platform in Snowflake. Sales transactions arrive continuously and are queried by finance, merchandising, and supply-chain teams. Analysts frequently join the sales fact data to customer, product, store, and promotion attributes, and they also run time-based aggregations such as daily sales by region and category. The current design stores all business attributes in one very wide denormalized table, but ETL costs are growing because small attribute changes cause large table rewrites. The architect wants a model that improves maintainability while still supporting high-performance analytical queries in Snowflake. Which approach is the MOST appropriate?
- A
Implement a star schema with a central sales fact table and surrounding dimension tables, and use surrogate keys where appropriate for dimensional management
- B
Fully normalize all sales and reference data into a third normal form (3NF) model to minimize storage and rely on analysts to rebuild reporting joins in every query
- C
Replace relational modeling with a single VARIANT column that stores each transaction and all related customer, product, and store attributes as nested JSON documents
- D
Create separate physical copies of the sales data for each department so each team can optimize its own schema and avoid shared joins
Show answer and explanation
Correct answer: A
Explanation
The best answer is to implement a star schema. For analytic workloads, Snowflake commonly supports dimensional modeling patterns such as fact and dimension tables because they align well with business-oriented querying, simplify reporting, and isolate descriptive changes from large transactional datasets. In this scenario, the key issue is that a single wide denormalized table is expensive to maintain when small business attributes change. Moving descriptive data into dimensions improves maintainability while still supporting efficient joins and aggregations. Snowflake documentation and best-practice guidance emphasize choosing data models based on workload patterns; for reporting and BI use cases, dimensional models are often more practical than highly normalized schemas. While Snowflake can handle semi-structured data and complex joins, those capabilities do not make JSON-centric or fully normalized models the best fit for every analytical workload. The architect should optimize for governed, reusable analytics with clear business entities, which is exactly what a star schema provides.
- A. Correct.
Correct. A star schema is a common and effective analytical data model for Snowflake workloads that involve large fact tables joined to descriptive dimensions. It balances maintainability and query usability by separating frequently changing descriptive attributes from transactional measures. This reduces the need to rewrite a very wide table when a small dimension attribute changes. Using surrogate keys is also a standard dimensional modeling practice, especially when managing slowly changing dimensions and preserving historical context.
- B. Incorrect.
Incorrect. A 3NF model can be appropriate for operational integration layers, but it is usually less suitable as the primary presentation model for broad analytics because it increases join complexity and makes self-service querying harder. In this scenario, many users frequently perform analytical joins and aggregations, so forcing them to reconstruct a highly normalized model in every query reduces usability and often does not align with dimensional modeling best practices for reporting.
- C. Incorrect.
Incorrect. VARIANT is useful for semi-structured data, schema flexibility, and ingesting JSON, but using a single nested document as the primary analytical model for core enterprise reporting is usually not the best choice here. The scenario describes consistent dimensions and repeated joins across well-understood entities such as customer, product, store, and promotion. Flattening these into a dimensional model provides clearer semantics and better maintainability for governed analytics than embedding everything into one JSON structure.
- D. Incorrect.
Incorrect. Creating separate physical copies for each department increases data duplication, governance risk, and maintenance overhead. It can also introduce consistency problems when definitions diverge across teams. Snowflake supports secure shared access patterns, virtual warehouses for workload isolation, and reusable data models without requiring multiple duplicated departmental copies of the same sales data.