ADA-C01 Question 238
Select 2Implement and manage views, secure views, and materialized viewsA Snowflake administrator supports a data-sharing use case for external partners. The partners must query a subset of columns from the SALES table without being able to infer data from the underlying base table definition or query optimizations. At the same time, an internal analytics team repeatedly runs aggregate queries on the same SALES data and wants faster performance with minimal application changes. Which TWO actions should the administrator take to meet both requirements?
- A
Create a SECURE VIEW for the partner-facing dataset and grant access to that view instead of the base table.
- B
Create a MATERIALIZED VIEW on the frequently aggregated SALES data used by the internal analytics team.
- C
Create a standard VIEW for partners because view text is hidden from consumers by default in all sharing scenarios.
- D
Create a MATERIALIZED VIEW for partners because materialized views prevent users from seeing the underlying table structure.
- E
Grant partners SELECT on the base SALES table and use masking policies alone to prevent inference of underlying data.
Show answer and explanation
Correct answers: A, B
Explanation
This scenario combines two distinct Snowflake administrative design choices: using secure views for protected external data access and materialized views for internal query acceleration. Secure views are the recommended mechanism when exposing data to consumers, especially through sharing, while minimizing exposure of underlying implementation details and limiting some optimizer behaviors that could leak information. Materialized views are intended to improve performance for repeated query patterns, especially aggregations over large base tables, and Snowflake automatically maintains them as the base table changes. Best practice is to use the right object for the right purpose: secure views for protected data presentation and materialized views for performance optimization. Relevant Snowflake documentation includes the sections on secure views, materialized views, and data sharing considerations.
- A. Correct.
Correct. A secure view is designed for scenarios where the administrator wants to expose data while limiting exposure of underlying implementation details. Secure views are commonly used for data sharing because they provide stronger privacy protections than standard views, including preventing certain query optimizations that could otherwise expose underlying data characteristics. Granting access to the secure view rather than the base table aligns with the requirement to restrict partners to a subset of columns and reduce the risk of inference from the underlying objects.
- B. Correct.
Correct. Materialized views are appropriate when the same query patterns, such as repeated aggregations on a base table, need better performance with minimal changes to consuming applications. Snowflake stores precomputed results for the materialized view and maintains them automatically as the base table changes, which can improve performance for compatible queries. This directly addresses the internal analytics team's repeated aggregate workload.
- C. Incorrect.
Incorrect. Standard views do not provide the same privacy guarantees as secure views. In Snowflake, secure views exist specifically to protect sensitive logic and reduce exposure through optimizer behavior and metadata visibility. Assuming a standard view is sufficient for secure data sharing is a common misconception.
- D. Incorrect.
Incorrect. A materialized view is primarily a performance optimization object, not a security boundary for data sharing. While it stores precomputed results, it does not replace the need for a secure view when the goal is to hide underlying table characteristics or query behavior from external consumers.
- E. Incorrect.
Incorrect. Masking policies can protect sensitive column values, but granting direct access to the base table does not satisfy the stated requirement to avoid exposing underlying table definition or inference opportunities. Partners should not be given direct SELECT access to the base table when the intent is controlled exposure through an abstraction layer such as a secure view.