SnowPro Associate: Platform Question 146
Single answer● Use database objectsA data engineering team maintains a SALES database with a schema named CURATED. They want analysts to query customer purchase data without exposing sensitive columns such as EMAIL and CREDIT_CARD_NUMBER from the base table CURATED.CUSTOMERS. The analysts should also be protected from future changes to the underlying table structure as much as possible. Which Snowflake database object is the best choice to meet this requirement?
- A
Create a secure view that selects only the non-sensitive columns from CURATED.CUSTOMERS
- B
Create a materialized view on CURATED.CUSTOMERS and grant analysts access to it instead of the base table
- C
Create a temporary table with only the allowed columns and tell analysts to query it
- D
Create a stage on top of CURATED.CUSTOMERS so analysts can access only approved columns
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a secure view exposing only approved columns. In Snowflake, views are commonly used to simplify access to underlying tables, hide complexity, and restrict access to sensitive data by omitting columns or rows from the view definition. Secure views are specifically designed for cases where the view definition and underlying details should be better protected, which aligns well with controlled access scenarios. Materialized views are mainly for query performance optimization on repeated workloads and are not the preferred object for this requirement. Temporary tables are not persistent enough for shared analyst access, and stages are unrelated to relational projection of table columns. Snowflake documentation on views and secure views supports using views to provide controlled, abstracted access to base tables.
- A. Correct.
Correct. A view is the standard Snowflake database object for presenting a subset of table data and columns to users. Using a secure view is an appropriate choice when access should be controlled through the view definition rather than the base table, and it helps avoid exposing sensitive columns. It also provides a stable interface for analysts because they query the view instead of the underlying table directly. This is a common best practice for data sharing and controlled access scenarios.
- B. Incorrect.
Incorrect. A materialized view stores precomputed query results and is primarily used for performance optimization, not as the best primary mechanism for column-level data exposure control. Materialized views also have restrictions and are not intended simply as a masking layer for sensitive columns. While a materialized view can include a subset of columns, the requirement here is controlled access and abstraction from the base table, which is more directly addressed by a view, especially a secure view.
- C. Incorrect.
Incorrect. A temporary table is session-scoped and not appropriate as a durable interface for analysts. It would disappear at the end of the session in which it was created, making it unsuitable for ongoing analytical access. It also creates unnecessary data duplication and operational overhead.
- D. Incorrect.
Incorrect. A stage is used for loading and unloading data files, not for providing relational access to selected columns from a table. Stages do not act as a database object for column filtering in SQL queries against tables.