ARA-C01 Question 338
Single answerViews and tablesA data platform team maintains a large SALES_FACT table that is queried by multiple BI tools. To simplify access, they created a view named SALES_REPORTING_VW that selects a subset of columns and applies several joins and aggregations. Analysts report that queries against the view are significantly slower than expected, even when they filter on SALE_DATE and REGION. The architects want to improve performance while preserving a simple object for analysts to query and minimizing changes to downstream tools.
Which solution is the MOST appropriate?
- A
Replace the standard view with a materialized view on the most frequently queried subset of SALES_FACT data, if the view definition meets materialized view limitations.
- B
Convert SALES_REPORTING_VW into a secure view so Snowflake can cache the result set across users more effectively.
- C
Create a transient table with the same definition as the view and point analysts to it, because transient tables automatically stay synchronized with base table changes.
- D
Cluster the view directly on SALE_DATE and REGION so partition pruning occurs before the underlying table is scanned.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a materialized view when the workload repeatedly queries the same derived subset and the definition is compatible with Snowflake's materialized view capabilities. Standard views are logical abstractions only; they do not store data and therefore do not inherently improve performance. Materialized views can reduce compute required for repeated queries by maintaining precomputed results over base table data.
The other options reflect common misconceptions: secure views are for governance and data protection rather than speed, transient tables are persisted but not self-maintaining, and clustering applies to tables rather than views. In practice, architects should evaluate whether the base table should be clustered on common filter columns such as SALE_DATE or REGION, and whether the view logic is simple enough for a materialized view. Relevant Snowflake guidance includes documentation on views, secure views, materialized views and their limitations, and clustering keys for tables.
- A. Correct.
Correct. A standard view stores only the query definition and does not persist precomputed results. If repeated workloads are hitting the same derived dataset, a materialized view can improve performance by storing and maintaining precomputed results for eligible queries. This is especially useful when users need a simple object to query and downstream SQL should change minimally. However, the architect must verify that the view logic is compatible with Snowflake materialized view restrictions, because not all joins, aggregations, or expressions are supported.
- B. Incorrect.
Incorrect. Secure views are primarily for data protection and limiting exposure of underlying logic or sensitive data. They are not a performance optimization feature and can reduce optimization opportunities in some scenarios. Result caching behavior is not improved simply by making a view secure.
- C. Incorrect.
Incorrect. A transient table can be used as a persisted derived table, but it does not automatically stay synchronized with changes in the base tables. Keeping it current would require ETL/ELT orchestration, tasks, dynamic tables, or other refresh logic. This adds operational overhead and does not satisfy the requirement as cleanly as a materialized view when the use case is eligible.
- D. Incorrect.
Incorrect. Snowflake supports clustering for tables, not for views. Micro-partition pruning occurs based on the physical storage characteristics of the underlying table data. You can cluster the base table or use a materialized view where appropriate, but you cannot define clustering keys directly on a standard view.