ADA-C01 Question 239
Select 2Implement and manage views, secure views, and materialized viewsA data platform team maintains a SALES table in Snowflake that contains sensitive customer attributes and high query volume from downstream analytics teams. The team must meet these requirements:
- External partners can query only non-sensitive columns, and the view definition and underlying table details must not be exposed.
- Internal analysts frequently run repetitive aggregations on total sales by region and day, and performance must improve without requiring analysts to change their query patterns significantly.
- The solution should minimize unnecessary recomputation where possible.
Which TWO actions should the Snowflake administrator take to meet these requirements?
- A
Create a SECURE VIEW for external partners that exposes only approved columns from SALES.
- B
Create a MATERIALIZED VIEW on SALES that precomputes sales totals by region and day for internal reporting queries.
- C
Replace the external partner view with a standard view, because Snowflake automatically hides view definitions from all consumers.
- D
Create a MATERIALIZED VIEW that includes all customer-sensitive columns, and grant access only to the materialized view to prevent exposure of the base table.
- E
Create a standard view for internal analysts, because standard views store precomputed query results and automatically refresh when the base table changes.
Show answer and explanation
Correct answers: A, B
Explanation
The correct solution combines a secure view for controlled external access and a materialized view for repeated internal aggregations. Secure views are used when stronger privacy and metadata-protection guarantees are needed, especially in data sharing or external-consumer scenarios where the underlying query logic or table structure should not be exposed. Materialized views persist precomputed query results and are maintained by Snowflake, making them well suited for frequently queried aggregations such as sales totals by region and day. They help reduce recomputation because Snowflake incrementally maintains changes instead of recalculating the full result set each time.
Best practice is to use standard views for logical abstraction, secure views when data exposure and definition visibility must be tightly controlled, and materialized views only for targeted performance optimization on repetitive, expensive query patterns. This aligns with Snowflake documentation on views, secure views, and materialized views, including their different security and performance characteristics.
- A. Correct.
Correct. A secure view is designed for scenarios where the administrator needs stronger data-sharing and metadata-protection controls. It can expose only the approved non-sensitive columns while preventing users from seeing underlying details that could otherwise be exposed through query optimization behavior or metadata visibility. This is the appropriate choice for external consumers who must not see the view definition or infer information about the underlying table.
- B. Correct.
Correct. A materialized view is appropriate when the same expensive aggregation is queried repeatedly, such as total sales by region and day. Snowflake stores the precomputed results and incrementally maintains them as the base table changes, which reduces repeated computation and can improve performance for matching or compatible query patterns without requiring users to manually rewrite every query to a different table structure.
- C. Incorrect.
Incorrect. Standard views do not provide the same protections as secure views. Snowflake does not treat standard views as fully hidden from consumers in the way required for secure data-sharing scenarios. This option reflects the misconception that any view is sufficient for external exposure of restricted data. When metadata protection and prevention of exposing underlying logic are requirements, a secure view should be used.
- D. Incorrect.
Incorrect. A materialized view is a performance feature, not a substitute for secure data masking or metadata protection. Including sensitive columns in the materialized view does not inherently make them safer, and granting access to that object could still expose sensitive data. In addition, materialized views are intended for selective, performance-oriented precomputation rather than broad replication of sensitive datasets.
- E. Incorrect.
Incorrect. Standard views do not store precomputed results. They store query definitions and are evaluated at query time against the underlying objects. The statement that standard views automatically store and refresh results describes materialized views, not standard views. This is a common confusion between logical views and physically maintained materialized views.