SnowPro Associate: Platform Question 340
Single answer● Time TravelA data engineer accidentally runs an UPDATE statement on the SALES table at 2:05 PM, overwriting several thousand rows with incorrect values. The table has a 1-day Time Travel retention period, and no Fail-safe recovery is needed. The team wants to compare the current data to the table state from 2:00 PM and then restore only the affected rows. Which Snowflake feature should the engineer use first to access the earlier version of the table?
- A
Query the SALES table using Time Travel with an AT or BEFORE clause referencing the timestamp from 2:00 PM
- B
Recover the SALES table from Fail-safe because Time Travel only supports dropped objects, not changed data
- C
Use UNDROP TABLE SALES to restore the table to its 2:00 PM state
- D
Create a database clone without specifying a point in time, because clones automatically use the earliest available table version
Show answer and explanation
Correct answer: A
Explanation
This scenario tests practical use of Snowflake Time Travel for data recovery after accidental DML changes. Time Travel supports accessing historical versions of data for supported objects, including tables, within the configured retention period. When a table still exists but its data was changed incorrectly, the correct approach is to query the table as of a prior time using AT or BEFORE. Common syntax includes using a TIMESTAMP, an OFFSET, or a STATEMENT ID. UNDROP applies only to dropped objects, not to rows changed by UPDATE or DELETE. Fail-safe is a separate recovery mechanism available after Time Travel expires and is not the first-line option for routine restoration. As a best practice, engineers often query the historical version first, validate the pre-change data, and then use SQL such as MERGE, INSERT, or UPDATE to restore only the affected rows rather than replacing the entire table.
- A. Correct.
Correct. Snowflake Time Travel allows querying historical data for a table at a specific point in time using AT or BEFORE with a TIMESTAMP, OFFSET, or STATEMENT parameter. This is the appropriate first step when the table still exists and the goal is to inspect or extract rows as they existed before an accidental UPDATE. After identifying the correct historical rows, the engineer can use them to restore just the affected records.
- B. Incorrect.
Incorrect. Fail-safe is not intended for user-driven self-service recovery or comparison of recent table versions. It is used only after the Time Travel retention period has expired and generally requires Snowflake Support. Also, Time Travel supports historical access to changed data, not just dropped objects.
- C. Incorrect.
Incorrect. UNDROP TABLE is used to recover a dropped table, schema, or database that is still within the Time Travel retention period. In this scenario, the SALES table was updated incorrectly but was not dropped, so UNDROP would not address the problem.
- D. Incorrect.
Incorrect. Cloning can be performed at a point in time, but a clone created without specifying a historical timestamp reflects the current state of the source object, including the incorrect updates. It does not automatically choose an earlier version.