How To Restore Deleted Rows In Snowflake

To Nha Notes | April 2, 2023, 12:06 p.m.

Removing data from a table

1. Delete specific rows from the table:

DELETE FROM customer WHERE C_CUSTKEY = 60001;
SELECT * FROM customer WHERE C_CUSTKEY = 60001; // This should not produce results

2. Recover rows from immediately before the previous transaction occurred:

// Advanced - Get the data before the last transaction (checkpoint)
// Requirements - Go to Home > Activity
// Use the Query ID as <uuid> below
INSERT INTO customer
SELECT * FROM customer before(statement => '<query_id>')
WHERE C_CUSTKEY = 60001;

3. Recover rows from a specific date / time:

Follow the format: YYYY-MM-DD H:MM:SS. See Snowflake Documentation for more available formats.

Note: This datetime must fall within the range set by DATA_RETENTION_TIME_IN_DAYS.

// Advanced - Get data from a checkpoint based on timestamp
INSERT INTO customer
SELECT * FROM customer at(timestamp => '<YYYY-MM-DD H:MM:SS>'::timestamp)
WHERE C_CUSTKEY = 60001;

References

https://www.zuar.com/blog/how-to-delete-table-from-snowflake/#data-deletion-recovery

https://datamajor.net/how-to-restore-deleted-rows-in-snowflake/

https://docs.snowflake.com/en/user-guide/data-time-travel