SnowPro Associate: Platform Question 106
Single answer● TablesA data engineering team loads a SALES table every hour using a TRUNCATE followed by INSERT process. Analysts have asked to be able to query the table exactly as it looked at the end of the previous hour when they troubleshoot reporting issues. The table is large, and the team wants to minimize storage and operational overhead while keeping the current object name for downstream dashboards. Which approach best meets these requirements?
- A
Create a zero-copy clone of the SALES table at the end of each hour and have analysts query the clone when needed.
- B
Use Time Travel to query the SALES table at a specific point in the past with AT or BEFORE.
- C
Convert the SALES table to a temporary table so each session can preserve its own prior version.
- D
Enable Search Optimization Service on the SALES table so previous hourly versions can be queried efficiently.
Show answer and explanation
Correct answer: B
Explanation
The best answer is to use Time Travel on the SALES table. Snowflake tables support historical data access for a defined retention period, allowing users to query data as it existed at a previous time using SQL clauses such as AT and BEFORE. This is especially useful when a table is refreshed with operations like TRUNCATE and INSERT and users need to investigate prior states without changing application object references. Compared with creating recurring clones, Time Travel typically involves less operational overhead because it does not require managing additional table objects. Zero-copy cloning is useful for creating independent copies for development, testing, or point-in-time snapshots, but for ad hoc troubleshooting of a prior table state, Time Travel is the more direct feature. Temporary tables and Search Optimization Service do not address the requirement. This aligns with Snowflake documentation and best practices for table recovery and historical query access.
- A. Incorrect.
Incorrect. Zero-copy cloning is storage efficient initially because it does not copy micro-partitions right away, but creating a clone every hour introduces additional objects to manage and does not best satisfy the requirement to minimize operational overhead. Analysts would also need to know which clone to query. This can work operationally, but it is not the simplest or most direct solution for accessing a prior version of the same table.
- B. Correct.
Correct. Snowflake Time Travel allows users to query historical data from a table using AT or BEFORE with a timestamp, offset, or statement. Since the team wants analysts to see the table as it looked at the end of the previous hour while keeping the current table name, querying the same SALES table through Time Travel is the most appropriate solution. This is designed for recovery and historical access to table data without creating additional copies.
- C. Incorrect.
Incorrect. Temporary tables exist only for the duration of the session that created them and are not intended for preserving historical versions of a permanent production table for other users. Converting the SALES table to temporary would break the shared, persistent behavior expected by dashboards and analysts.
- D. Incorrect.
Incorrect. Search Optimization Service can improve performance for certain selective queries, but it does not provide historical versions of table data. It is a performance feature, not a data versioning or recovery mechanism.