DAA-C01 Question 197
Select 2Regular, secure, and materialized viewsA retail analytics team stores transaction data in a large SALES table that is continuously queried by internal analysts and a small set of external partners. Internal analysts need fast dashboard performance for a query that repeatedly aggregates daily sales by REGION and ORDER_DATE. External partners should only see rows for their assigned REGION, and the company does not want the underlying table definition or business logic exposed through metadata. The data changes throughout the day, and the team wants to minimize query latency for the repeated aggregate query without requiring users to rewrite their SQL. Which TWO actions best meet these requirements?
- A
Create a materialized view on SALES that precomputes the daily sales aggregation used by internal dashboards.
- B
Create a secure view for external partners that filters rows by REGION and hides underlying object details from metadata exposure.
- C
Create a regular view for external partners because regular views prevent users from seeing view definitions and underlying objects.
- D
Replace the internal dashboard query with a secure view, because secure views provide the same precomputed performance benefits as materialized views.
- E
Create a materialized view for external partners instead of a secure view, because materialized views are designed to enforce row-level visibility restrictions.
Show answer and explanation
Correct answers: A, B
Explanation
This scenario requires matching each view type to its best use case. Materialized views are designed to improve performance for repeated, expensive query patterns by storing precomputed results and automatically maintaining them as base table data changes. They are appropriate for the internal dashboard workload that repeatedly aggregates a large SALES table by REGION and ORDER_DATE. Secure views are designed for controlled data access, especially when you want to hide underlying implementation details and reduce metadata exposure. They are appropriate for external partners who should see only their authorized REGION data and should not be able to inspect business logic or underlying object definitions.
Regular views are logical abstractions only; they do not provide the enhanced confidentiality guarantees of secure views. Secure views also do not provide the precomputed storage and optimizer behavior associated with materialized views. Snowflake documentation and best practices distinguish these features clearly: use materialized views for performance optimization of stable, repeated query patterns, and use secure views when data sharing or sensitive access patterns require stronger privacy protections around the view definition and underlying objects.
- A. Correct.
Correct. Materialized views in Snowflake physically store the results of a query and are useful when the same expensive query pattern is executed repeatedly against a changing base table. In this scenario, the dashboard repeatedly aggregates SALES by REGION and ORDER_DATE, which is a strong candidate for a materialized view if the query pattern is stable and the performance gain justifies maintenance cost. Users can continue querying in ways that benefit from the precomputed data when the optimizer can rewrite queries to use the materialized view.
- B. Correct.
Correct. Secure views are appropriate when you need to share filtered data while protecting the view definition and limiting metadata exposure. In Snowflake, secure views are specifically intended for cases where the provider wants tighter control over what consumers can infer about underlying objects and logic. A secure view can implement the REGION-based filtering logic for external partners while providing better confidentiality than a regular view.
- C. Incorrect.
Incorrect. This reflects a common misconception. Regular views do not provide the metadata protection guarantees of secure views. In Snowflake, secure views are the object type intended to prevent exposure of underlying view definitions and certain internal details in data sharing and similar sensitive scenarios. A regular view may implement filtering logic, but it does not satisfy the requirement to avoid exposing the underlying table definition or logic through metadata.
- D. Incorrect.
Incorrect. Secure views address security and metadata exposure concerns, not query acceleration through persisted precomputation. Unlike materialized views, secure views do not store precomputed result sets for performance optimization. If the goal is to reduce latency for repeated aggregate queries, a materialized view is the relevant feature, not a secure view.
- E. Incorrect.
Incorrect. Materialized views are for performance optimization, not for enforcing secure row-level visibility for consumers. While a materialized view can contain filtered or aggregated data, it is not the recommended mechanism here for hiding business logic or protecting metadata exposure to external partners. The requirement for secure sharing and concealed definitions points to a secure view.