COF-C03 Question 110
Single answerViews typesA data engineering team has a large SALES table that is updated continuously throughout the day. Business analysts want a simplified object they can query without exposing all underlying columns. The analysts also expect query results to reflect the most current committed data in SALES each time they run a query. To reduce repeated query logic, the team also wants Snowflake to store precomputed results for the object whenever possible to improve performance. Which type of view should the team create?
- A
A standard view
- B
A materialized view
- C
A secure view
- D
A temporary view
Show answer and explanation
Correct answer: B
Explanation
The best answer is a materialized view. In Snowflake, standard views are logical abstractions over query definitions and do not physically store results. Materialized views, by contrast, persist query results and are maintained by Snowflake as the base table changes, which can improve performance for common repeated queries. Secure views focus on limiting exposure of underlying implementation details and supporting secure data sharing use cases, not on precomputation. Temporary views are limited to a session and are not suitable for persistent analyst access. According to Snowflake documentation and best practices, materialized views are appropriate when query patterns are repetitive and the cost of maintaining the view is justified by query performance benefits.
- A. Incorrect.
A standard view can simplify access to the SALES table and expose only selected columns, and it returns current data at query time. However, it does not store precomputed results. Each query against a standard view is resolved against the underlying base table at runtime, so it does not meet the requirement to have Snowflake maintain stored results to improve performance.
- B. Correct.
A materialized view is correct because Snowflake stores the results of the view definition and maintains them as the base table changes. This can improve query performance for repeated access patterns. Materialized views are built on a single base table and can provide current data with Snowflake handling maintenance in the background. This best matches the requirement for simplified access, up-to-date committed data, and persisted precomputed results.
- C. Incorrect.
A secure view is designed to protect the view definition and provide additional data-sharing/privacy protections, not to store precomputed results. While it could hide underlying columns and still show current data, it does not satisfy the performance requirement based on stored precomputed results.
- D. Incorrect.
A temporary view is session-scoped and is typically used for short-lived work. It does not store precomputed results like a materialized view, and it would not be appropriate for a business analyst object intended for ongoing shared use.