SnowPro Associate: Platform Question 109
Single answer● ViewsA data engineering team has a table SALES_RAW in the PROD database that contains customer_id, order_id, order_total, and sensitive columns such as credit_card_number. Analysts in the ANALYTICS role need to query only non-sensitive columns, and the security team wants future changes to the base table to be handled without recreating access objects. Which solution best meets this requirement?
- A
Create a standard view that selects only the allowed columns from PROD.SALES_RAW, and grant analysts access to the view instead of the base table.
- B
Create a materialized view on PROD.SALES_RAW so analysts can query only stored results and cannot access hidden columns.
- C
Grant analysts SELECT on PROD.SALES_RAW and rely on BI tool settings to hide the sensitive columns.
- D
Create a temporary view for analysts that excludes the sensitive columns, because temporary views automatically adapt to base table changes for all users.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create a standard view that projects only the non-sensitive columns and grant the ANALYTICS role access to that view. In Snowflake, views are commonly used to simplify access patterns, abstract underlying table structures, and restrict exposure of sensitive columns by not including them in the view definition. This is a practical and widely used approach for data sharing within an organization. Materialized views are designed mainly to improve performance for certain queries and are not the primary mechanism for access control. Direct access to the base table combined with front-end hiding is not secure because Snowflake privileges govern actual data access. Temporary views are not suitable for persistent shared access. This aligns with Snowflake best practices around using views for logical data abstraction and controlled access.
- A. Correct.
Correct. A standard view can expose only the approved columns from the base table and serve as the access layer for analysts. Granting privileges on the view instead of the underlying table is a common Snowflake design pattern for limiting exposure to sensitive data. This approach supports schema abstraction and lets the team manage changes centrally in the view definition rather than granting direct table access.
- B. Incorrect.
Incorrect. A materialized view is primarily for performance optimization of repeated query patterns, not for security isolation by itself. While it can contain a subset of columns, it is not the standard answer for controlling analyst access to sensitive fields in this scenario. It also adds maintenance/storage considerations and has feature restrictions compared with standard views.
- C. Incorrect.
Incorrect. Granting SELECT on the base table exposes all columns in that table to the role with the privilege. Hiding columns only in a BI tool is not a security control in Snowflake because users or tools with direct SQL access could still query the sensitive data.
- D. Incorrect.
Incorrect. Temporary views are session-scoped objects and are not appropriate for shared, durable access by an analyst role. They do not provide a persistent access layer for multiple users, and they are not the recommended mechanism for handling ongoing schema governance.