Scenario: You want to rollback changes due to a problematic query (e.g., accidental data modification or corruption). Snowflake provides two powerful tools:
✅ B. CLONE ... BEFORE (STATEMENT => 'query_id')
This uses Time Travel + Zero-Copy Cloning.
You can clone a table as it existed before a specific query.
It creates a full copy of the table's state at that moment without duplicating storage.
Example:
CREATE TABLE prd_table_bkp CLONE prd_table
BEFORE (STATEMENT => '01a2b3c4-0000-0000-0000-123456789abc');
✅ D. ALTER TABLE ... SWAP WITH ...
Once you've cloned the backup, you can swap it with the live table.
This is a fast, atomic operation - ideal for rollback.
Example:
ALTER TABLE prd_table SWAP WITH prd_table_bkp;
❌ Why the Other Options Are Incorrect:
A . SELECT SYSTEM$CANCEL_QUERY(...)
Cancels a currently running query - doesn't help if the query already executed and caused damage.
C . CREATE TABLE ... AS SELECT * FROM RESULT_SCAN(...)
Reconstructs results, not the original table.
Only captures output rows, not full table state.
Not ideal for rollback.
E . Contact Snowflake Support to retrieve Fail-safe data
Fail-safe is for disaster recovery only, and only accessible by Snowflake support.
It's not intended for routine rollback or recovery and has a 7-day fixed retention (non-configurable).
SnowPro Administrator Reference:
Zero-Copy Cloning with Time Travel
ALTER TABLE SWAP
System Functions - SYSTEM$CANCEL_QUERY
Fail-safe Overview