SnowPro Associate: Platform Question 111
Single answer● ViewsA data engineering team maintains a table named SALES_RAW that contains all sales transactions, including a column named CREDIT_CARD_NUMBER. Analysts in another role need query access to sales data, but they must not be able to see the credit card numbers. The team wants a solution that minimizes data duplication and can be queried like a table. Which action should the team take?
- A
Create a standard view that selects only the required columns from SALES_RAW and grant analysts access to the view instead of the base table.
- B
Create a materialized view on SALES_RAW and grant analysts access to the materialized view, because materialized views automatically hide unselected columns from the base table.
- C
Clone SALES_RAW into a new table without the CREDIT_CARD_NUMBER column and grant analysts access to the cloned table, because clones do not consume storage.
- D
Create a temporary view for the analysts' session and grant the analyst role access to that temporary view.
Show answer and explanation
Correct answer: A
Explanation
The best solution is to create a standard view that projects only the non-sensitive columns from SALES_RAW and then grant analysts access to that view. In Snowflake, views are commonly used to present a restricted or simplified representation of underlying data without copying the full dataset. This makes them appropriate for scenarios where teams need to hide sensitive columns while still enabling SQL-based access. Materialized views are designed mainly for query performance optimization and do store data, so they are not the most appropriate choice here. Cloning is useful for creating independent table-like objects quickly, but it does not by itself solve the column-exposure problem and introduces separate object management. Temporary views are session-specific and therefore not suitable for durable access patterns. This aligns with Snowflake best practices for using views to control data presentation and limit direct access to base tables.
- A. Correct.
Correct. A standard view can expose only the allowed columns from the underlying table, which helps restrict access to sensitive data such as CREDIT_CARD_NUMBER. Views do not store duplicated table data, so this approach minimizes data duplication while allowing users to query the object like a table. Granting access to the view rather than the base table is a common best practice for column-level data exposure control.
- B. Incorrect.
Incorrect. A materialized view physically stores precomputed query results to improve performance for certain workloads, but it is not the best answer for this requirement. The primary need is to restrict visible columns and avoid unnecessary data duplication. While a materialized view can include a subset of columns, it introduces storage and maintenance considerations and is intended for performance optimization, not as the default mechanism for simple secure column projection.
- C. Incorrect.
Incorrect. Cloning creates a copy-on-write object, so it initially uses minimal additional storage, but it is still a separate table object and not the best solution when the requirement is to minimize duplication and manage access cleanly. Also, a clone of the table would include the sensitive column unless additional steps are taken to create a new derived table structure. A view is the more appropriate object for exposing only selected columns.
- D. Incorrect.
Incorrect. Temporary views are session-scoped and are not suitable for persistent shared access by an analyst role. Once the creating session ends, the temporary view is no longer available. This does not meet a realistic enterprise requirement for ongoing analyst access.