SnowPro Associate: Platform Question 112
Single answer● ViewsA data engineering team maintains a SALES table that contains sensitive columns such as CUSTOMER_EMAIL and CREDIT_CARD_TOKEN. Analysts in another role need query access to only ORDER_ID, ORDER_DATE, REGION, and TOTAL_AMOUNT, and the company wants to avoid storing duplicate data. The team also expects new rows inserted into SALES to be visible immediately to analysts. Which solution should the Snowflake administrator implement?
- A
Create a standard view that selects only ORDER_ID, ORDER_DATE, REGION, and TOTAL_AMOUNT from the SALES table, and grant analysts access to the view.
- B
Create a materialized view that selects only ORDER_ID, ORDER_DATE, REGION, and TOTAL_AMOUNT from the SALES table, and grant analysts access to the materialized view.
- C
Create a clone of the SALES table, remove the sensitive columns from the clone, and grant analysts access to the clone.
- D
Unload the non-sensitive columns into an internal stage on a schedule and let analysts query the staged files directly.
Show answer and explanation
Correct answer: A
Explanation
The correct answer is to create a standard view over the SALES table. In Snowflake, views are virtual tables defined by a query and do not store the underlying result set like a table. They are commonly used to simplify access and restrict users to selected columns or rows while keeping the source data centralized. Because the view reads from the underlying table at query time, newly inserted rows in SALES are visible immediately through the view. This makes a standard view the most practical solution when the goal is data abstraction, security, and avoiding duplicate storage. Materialized views in Snowflake are intended mainly to improve performance for repeated query patterns and do consume storage. Cloning and staged-file approaches are less appropriate for governed, near-real-time analytical access. This aligns with Snowflake documentation and best practices for using standard views to provide controlled access to subsets of table data.
- A. Correct.
Correct. A standard view is the best fit because it presents only the required columns without duplicating table storage and reflects current data from the underlying SALES table whenever queried. This is a common pattern for limiting column exposure to downstream users. Granting access to the view allows controlled access to a subset of the base table's data.
- B. Incorrect.
Incorrect. Although a materialized view can expose a subset of columns, it is designed primarily for query performance optimization and does require additional storage for the materialized results. The requirement specifically emphasizes avoiding duplicate stored data and immediate visibility of base-table changes for a simple column-restriction use case. A standard view is the more appropriate and simpler choice here.
- C. Incorrect.
Incorrect. A clone is a separate table-like object that can initially share micro-partitions with the source through zero-copy cloning, but it is still an independent object and not the best mechanism for ongoing column-level restriction. Removing columns from the clone creates a divergent copy that must be managed separately, and it does not automatically remain aligned with source schema or governance intent in the same way a view does.
- D. Incorrect.
Incorrect. Staging exported files introduces unnecessary operational complexity and does not provide the same governed, real-time relational access pattern as a view. Analysts also do not query staged files in the same way they query database objects unless using external table-style patterns or file functions, which is not the simplest or most secure solution for this requirement.