SnowPro Associate: Platform Question 148
Single answer● Use database objectsA data engineering team loads daily sales files into a STAGE and then inserts the transformed data into the ANALYTICS.SALES_FACT table. Recently, a developer accidentally truncated SALES_FACT while testing. The team wants to reduce the risk of accidental data loss and also simplify access for BI users, who should query only a curated subset of columns without needing direct access to the base table. Which solution best meets both requirements?
- A
Create a temporary table as a backup of SALES_FACT before each load, and grant BI users SELECT on the temporary table.
- B
Create a view on SALES_FACT with only the approved columns for BI users, and rely on Time Travel to recover SALES_FACT if it is accidentally truncated.
- C
Create a materialized view for BI users and disable TRUNCATE on SALES_FACT for developers.
- D
Create an external table for BI users and use Fail-safe to restore SALES_FACT immediately after an accidental truncate.
Show answer and explanation
Correct answer: B
Explanation
This question tests practical use of database objects in Snowflake. A view is a common object used to simplify and secure access by exposing only selected columns or rows from base tables, allowing BI users to query curated data without direct access to the underlying table. For accidental deletion or truncation of table data, Snowflake Time Travel is the primary recovery mechanism, enabling access to historical data versions within the retention period configured for the table, schema, or database. Temporary tables are session-scoped and not suitable for durable backups or shared BI access. External tables are for data stored outside Snowflake. Materialized views can improve performance but are not the best fit here because the requirement is controlled access to approved columns and protection against accidental data loss. Relevant Snowflake documentation includes topics on Views, Time Travel, Table Types, and Access Control/Privileges.
- A. Incorrect.
Incorrect. A temporary table exists only for the session that created it, so it is not a reliable backup mechanism for protecting production data. It also is not appropriate for ongoing BI access across users and sessions. This option reflects a common misconception that temporary tables are suitable for durable protection or shared reporting access.
- B. Correct.
Correct. A standard view is an appropriate database object for exposing only the curated subset of columns to BI users while keeping access to the base table restricted. If SALES_FACT is accidentally truncated, Snowflake Time Travel can be used to recover historical data within the configured retention period. This combination directly addresses both the access-control and recovery requirements using standard Snowflake database objects and platform capabilities.
- C. Incorrect.
Incorrect. Materialized views are designed to improve query performance for specific query patterns, not primarily to control column-level exposure for BI users. More importantly, Snowflake does not provide a separate object-level feature to 'disable TRUNCATE' specifically for some users while allowing other DML based solely on a special truncate switch. Control is handled through privileges and roles, not by turning off TRUNCATE as described here.
- D. Incorrect.
Incorrect. External tables are used to query data stored in external cloud storage, not to present a curated subset of columns from an internal Snowflake table for BI users. Fail-safe is a Snowflake recovery feature intended for use after Time Travel has expired and is not designed for immediate self-service restoration by users after an accidental truncate. This option mixes unrelated features.