Problem statement: It is not uncommon to accidentally execute statements that update or delete incorrect data in database tables. To address this, Snowflake offers a feature known as Fail-safe, which allows for the recovery of lost or altered data. This functionality provides a way to restore data that may have been mistakenly updated or deleted. Below, we outline several examples and methods for recovering data using Snowflake’s Fail-safe feature.
Snowflake Time Travel is a powerful feature that allows users to access historical data — including data that has been modified or deleted — at any point within a specified retention period. This functionality is essential for a variety of tasks, including:
- Restoring deleted data objects: Recover tables, schemas, and databases that may have been accidentally or intentionally deleted.
- Backing up and duplicating data: Capture and preserve data from key moments in the past for reference or archiving purposes.
- Analyzing data changes: Examine how data has been used or manipulated over specific time periods.
With Snowflake Time Travel, users can perform the following actions within the defined retention window:
Below is the table created in snowflake
Below is the statement used to update ‘GENDER’ column to F accidentally for all the records.
Below is the statement which is used to recover data before 1 minute.
Select * from employees at(offset =>-60*1 );
The other way that we can use to recover the data is using query id. As below mentioned go to the monitoring section and get the query id’s and execute the below select statement.
Select * from stg.employees before (statement => ’01b84ac0-0712-2262-0074-35030a0b41ce’);
Below are the 2 statements used to recover the dropped table.
Drop table table_name;
Undrop table table_name;
