SnowPro Associate: Platform Question 145
Single answer● Use database objectsA data engineering team maintains a SALES_DB database with a curated schema named PROD. Analysts in a separate sandbox database need to query the curated SALES table, but the engineering team must ensure the analysts cannot modify the original table and that storage is not duplicated. Which Snowflake object should be created to meet this requirement?
- A
Create a materialized view of SALES_DB.PROD.SALES in the sandbox database
- B
Create a clone of SALES_DB.PROD.SALES in the sandbox database
- C
Create a secure view in the sandbox database that selects from SALES_DB.PROD.SALES
- D
Create a temporary table in the sandbox database using CREATE TABLE AS SELECT from SALES_DB.PROD.SALES
Show answer and explanation
Correct answer: C
Explanation
The best answer is to create a secure view in the sandbox database that selects from SALES_DB.PROD.SALES. In Snowflake, views are database objects that store a query definition rather than copying underlying table data, making them appropriate for presenting curated data to other users or teams without duplicating storage. A secure view is commonly used when sharing governed access because it restricts certain optimizations that could expose underlying details. By granting analysts SELECT on the view instead of direct privileges on the base table, the engineering team can allow query access while protecting the original object from modification. By contrast, a clone creates a separate table object, a materialized view stores maintained results, and CTAS creates a new physical copy of the data. These distinctions are consistent with Snowflake documentation on views, secure views, zero-copy cloning, and materialized views.
- A. Incorrect.
Incorrect. A materialized view stores precomputed query results and consumes storage for the maintained data. While it can provide query access, it does not best satisfy the requirement to avoid duplicating storage for the underlying table data in this scenario. It is also intended primarily for performance optimization, not simply for sharing read-only access across databases.
- B. Incorrect.
Incorrect. A clone is created using zero-copy cloning, so it does not initially duplicate storage. However, the cloned table is an independent object that can be modified by users with the necessary privileges, which does not satisfy the requirement that analysts must not be able to modify the original curated table through the shared object pattern. Cloning is better suited for creating isolated copies for testing or development.
- C. Correct.
Correct. A view can be created in one database and reference objects in another database, allowing analysts to query the curated table without duplicating the table's storage. A secure view is an appropriate choice when exposing curated data because it presents a controlled logical interface and supports read-only access to the underlying table when only SELECT privileges are granted on the view. This aligns with the requirement to prevent modification of the original table while avoiding storage duplication.
- D. Incorrect.
Incorrect. CREATE TABLE AS SELECT creates a new physical table and copies data into it, which duplicates storage. A temporary table is also session-scoped and not appropriate for a durable shared access pattern for analysts. This option conflicts with both the storage and usability requirements.