DAA-C01 Question 251
Single answerOperationalize data for consumptionA retail analytics team has built a curated SALES_DAILY table in Snowflake and wants downstream BI tools to query a stable object that should keep working even if the underlying storage design changes. The team also wants to limit consumers to a subset of columns, apply row-level filtering by region for different consumer roles, and minimize ongoing maintenance when the base table is replaced during monthly pipeline deployments. Which approach best operationalizes this data for consumption?
- A
Create a secure view on top of SALES_DAILY, expose only required columns, implement a row access policy for regional filtering, and have BI tools query the secure view instead of the base table.
- B
Grant BI roles direct SELECT on SALES_DAILY and use masking policies on all columns, because masking policies also handle row-level regional filtering and preserve object stability when tables are replaced.
- C
Create a materialized view for each region and point BI tools directly to those materialized views, because materialized views automatically preserve access behavior when the base table is dropped and recreated.
- D
Create a stream on SALES_DAILY and have BI tools query the stream, because streams provide a stable consumption layer and can enforce row-level filtering for different roles.
Show answer and explanation
Correct answer: A
Explanation
The best practice is to present curated data through a governed logical layer rather than granting direct access to physical tables. In Snowflake, views are commonly used to operationalize data for consumption because they provide abstraction and can limit exposed columns. When secure data sharing or stronger metadata protection is a concern, secure views are appropriate. For row-level filtering, Snowflake provides row access policies, while masking policies are for column-level protection. Streams are for CDC workflows, not BI consumption, and materialized views are performance optimization objects rather than the primary consumer-facing governance layer. This aligns with Snowflake documentation and best practices around views, secure views, row access policies, and masking policies.
- A. Correct.
Correct. A secure view is an appropriate consumption layer when consumers need a stable interface decoupled from underlying physical design changes. Views can expose only the required columns, reducing unnecessary data exposure. Row-level filtering should be implemented with a row access policy, which is specifically designed for row-level security. Using the view as the BI entry point is a common best practice for operationalizing curated data for consumption.
- B. Incorrect.
Incorrect. Direct access to the base table does not provide the abstraction layer needed for a stable consumer-facing interface. In addition, masking policies protect column values, not row-level access. Row-level filtering in Snowflake is handled by row access policies, not masking policies. This option reflects a common confusion between column-level and row-level governance features.
- C. Incorrect.
Incorrect. Materialized views can improve performance for certain repeated query patterns, but they are not the primary mechanism for governing consumer access or abstracting a stable semantic interface. Creating one materialized view per region adds maintenance complexity. Also, materialized views depend on the base table and are not the right answer for preserving a logical access layer when the goal is controlled consumption with row-level security.
- D. Incorrect.
Incorrect. Streams are used for change data capture by tracking table and view changes for downstream processing. They are not intended as a BI consumption layer and do not provide row-level security enforcement for end-user queries. This option confuses operational data pipelines with governed data consumption.