Panic hits when you mistakenly delete data. Problems can come from a mistake that disrupts a process, or worse, the whole database was deleted. Thoughts of how recent was the last backup and how much time will be lost might have you wishing for a rewind button. Straightening out your database isn't a disaster to recover from with Snowflake's Time Travel. A few SQL commands allow you to go back in time and reclaim the past, saving you from the time and stress of a more extensive restore.
We'll get started in the Snowflake web console, configure data retention, and use Time Travel to retrieve historic data. Before querying for your previous database states, let's review the prerequisites for this guide.
First things first, let's get your Snowflake account and user permissions primed to use Time Travel features.
Snowflake lets you try out their services for free with a trial account. A Standard account allows for one day of Time Travel data retention, and an Enterprise account allows for 90 days of data retention. An Enterprise account is necessary to practice some commands in this tutorial.
Log in to the web interface on your browser. The URL contains your account name and potentially the region.
Snowflake's web interface has a lot to offer, but for now, switch the account role from the default
ACCOUNTADMIN. You'll need this increase in permissions later.
Now that you have the account and user permissions needed, let's create the required database objects to test drive Time Travel.
Within the Snowflake web console, navigate to Worksheets and use a fresh worksheet to run the following commands.
create or replace database timeTravel_db;
Use the above command to make a database called ‘timeTravel_db'. The Results output will show a status message of
Database TIMETRAVEL_DB successfully created.
create or replace table timeTravel_table(ID int);
This command creates a table named ‘timeTravel_table' on the timeTravel_db database. The Results output should show a status message of
Table TIMETRAVEL_TABLE successfully created.
With the Snowflake account and database ready, let's get down to business by configuring Time Travel.
Be ready for anything by setting up data retention beforehand. The default setting is one day of data retention. However, if your one day mark passes and you need the previous database state back, you can't retroactively extend the data retention period. This section teaches you how to be prepared by preconfiguring Time Travel retention.
alter table timeTravel_table set data_retention_time_in_days=55;
The command above changes the table's data retention period to 55 days. If you opted for a Standard account, your data retention period is limited to the default of one day. An Enterprise account allows for 90 days of preservation in Time Travel.
Now you know how easy it is to alter your data retention, let's bend the rules of time by querying an old database state with Time Travel.
With your data retention period specified, let's turn back the clock with the
select * from timeTravel_table at(timestamp => 'Fri, 23 Oct 2020 16:20:00 -0700'::timestamp);
timestamp to summon the database state at a specific date and time.
select * from timeTravel_table at(offset => -60*5);
offset to call the database state at a time difference of the current time. Calculate the offset in seconds with math expressions. The example above states,
-60*5, which translates to five minutes ago.
select * from timeTravel_table before(statement => '<statement_id>');
If you're looking to restore a database state just before a transaction occurred, grab the transaction's statement id. Use the command above with your statement id to get the database state right before the transaction statement was executed.
By practicing these queries, you'll be confident in how to find a previous database state. After locating the desired database state, you'll need to get a copy by cloning in the next step.
With the past at your fingertips, make a copy of the old database state you need with the
create table restoredTimeTravel_table clone timeTravel_table at(offset => -60*5);
The command above creates a new table named
restoredTimeTravel_table that is an exact copy of the table
timeTravel_table from five minutes prior.
Cloning will allow you to maintain the current database while getting a copy of a past database state. After practicing the steps in this guide, remove the practice database objects in the next section.
You've created a Snowflake account, made database objects, configured data retention, query old table states, and generate a copy of the old table state. Pat yourself on the back! Complete the steps to this tutorial by deleting the objects created.
drop table if exists timeTravel_table;
By dropping the table before the database, the retention period previously specified on the object is honored. If a parent object(e.g., database) is removed without the child object(e.g., table) being dropped prior, the child's data retention period is null.
drop database if exists timeTravel_db;
With the database now removed, you've completed learning how to call, copy, and erase the past.