DAA-C01 Question 198
Single answerRegular, secure, and materialized viewsA retail company stores customer orders in a large Snowflake table that is continuously updated throughout the day. Two different teams need access to this data. The Finance team shares a filtered view of the data with an external audit partner and must prevent the partner from seeing underlying table definitions or business logic in the view definition. At the same time, internal analysts run the same aggregation query every few minutes to summarize order totals by region, and query latency has become a problem. Which combination of view types best meets these requirements?
- A
Create a regular view for the external audit partner and a regular view for the internal aggregation query.
- B
Create a secure view for the external audit partner and a materialized view for the internal aggregation query.
- C
Create a materialized view for the external audit partner and a secure view for the internal aggregation query.
- D
Create a secure materialized view for the external audit partner and a regular view for the internal aggregation query.
Show answer and explanation
Correct answer: B
Explanation
This scenario requires choosing the right object for two different goals: security during sharing and performance for repeated analytics. In Snowflake, a regular view is a stored query definition and does not materialize results. It is useful for abstraction but not for accelerating repeated workloads. A secure view is intended for cases where data is shared and the underlying details of the view should be protected; secure views are commonly used with Secure Data Sharing because they provide stronger privacy guarantees around the exposed object. A materialized view stores precomputed query results and is maintained by Snowflake, making it well suited for repeated queries over large tables, especially common aggregations or selective projections where low latency matters. Best practice is to use secure views for controlled external sharing and materialized views only when the performance benefit justifies the maintenance cost and the query pattern fits Snowflake's materialized view limitations.
- A. Incorrect.
Incorrect. A regular view can expose metadata and does not provide the additional protections expected when sharing data externally where the underlying logic should be hidden. It also does not improve performance for repeated aggregation workloads because regular views store only the query definition and are computed at query time.
- B. Correct.
Correct. A secure view is appropriate for sharing filtered data with an external party when you want to limit exposure of underlying implementation details and use Snowflake's secure object semantics for data sharing. A materialized view is appropriate for a frequently executed aggregation on a large, changing base table because Snowflake physically stores and maintains the precomputed results, which can reduce latency for repeated queries.
- C. Incorrect.
Incorrect. Materialized views are designed for performance optimization, not for hiding view definitions from consumers in a secure sharing scenario. A secure view, while useful for protecting shared data access, does not provide the precomputed storage benefits needed to accelerate repetitive aggregation queries.
- D. Incorrect.
Incorrect. Snowflake does not support a 'secure materialized view' object type. This option combines valid concepts into an invalid feature. Also, using a regular view for the internal aggregation query would not address the stated performance problem.