Snowflake : Is time travel a replacement for backups ?

Introduction

Snowflake offers a great feature, called time travel. I blogged about some events that happen at my current project with primary and foreign constraints that disappeared when we used time travel. Primary key and foreign key constraints are not enforced in Snowflake. Except one thing: you can't create a  foreign key if the referenced table doesn't exists. Okay, it's annoying when the PK en FK are gone, and if you have them stored separately you could reapply the constraints again, but it can a complex task in case of time traveling. The next thing I was wandering about is that could time travel be a replacement for backups as we are used to in the on - premise world? Let's do some experiments.

After reading a blog about timetravelling and backups I was curious about changing structure and time travel. 

Experiment : add and remove a column

I've created some scripts to test the timetravelling. First I created a database and a table with data.

ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;
USE ROLE SYSADMIN;
USE WAREHOUSE Compute_WH;

-- Create the database
CREATE OR REPLACE DATABASE BackupTimeTravel;

-- Select the right database
USE DATABASE BackupTimeTravel;

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  PRIMARY KEY (id));

--Insert some data into the table
INSERT INTO Test(id, name, street) VALUES(1, 'Hennie', 'Spicyvalley');

-- select some data from the table
select * from Test;

And this script results in the following:

Next step is to insert some extra data in the table.

INSERT INTO Test(id, name, street) VALUES(2, 'Peter', 'bakerstreet');

select * from Test at(offset => -60);

Results in (after 60 seconds waiting) : 

Now let's add a column to the table and insert data in the table:

ALTER TABLE Test  ADD COLUMN town varchar;

--Insert some data into the table
INSERT INTO Test(id, name, street, town) VALUES(1, 'Hennie', 'Spicyvalley', 'Amsterdam');

select * from Test

Results in  : 

Let's execute some timetravelling.

select * from Test at(offset => -160);

Results in :

So this seems all perfect. Now drop the column 'street' in the table.

ALTER TABLE Test  DROP COLUMN street

select * from Test at(offset => -260);

Results in :

The column 'street' is gone with time travelling through the table. 

Now, let's experiment with the database and see whether we can time travel and clone the database to get the columns back. 

create database BackupTimeTravel_clone clone BackupTimeTravel
  at(offset => -500);
  
-- Select the right database
USE DATABASE BackupTimeTravel_clone;

select * from Test

Results in :

And the column is back in the table in the cloned database of the original database. 

Now check if we have the PK available.

SELECT GET_DDL('Table', 'Test');

Resuting in :

create or replace TABLE TEST (
	ID NUMBER(38,0) NOT NULL,
	NAME VARCHAR(16777216),
	STREET VARCHAR(16777216),
	primary key (ID)
);

And now we still have the PK.

Experiment : Recreate a table

Another item that is mentioned in the blog is that when a object is recreated the time travel is gone. Let's do some experiments with this mind. First setup the database and the table.

ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;
USE ROLE SYSADMIN;
USE WAREHOUSE Compute_WH;

-- Create the database
CREATE OR REPLACE DATABASE BackupTimeTravel2;

-- Select the right database
USE DATABASE BackupTimeTravel2;

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  PRIMARY KEY (id));

--Insert some data into the table
INSERT INTO Test(id, name, street) VALUES(1, 'Hennie', 'Spicyvalley');

-- select some data from the table
select * from Test;

INSERT INTO Test(id, name, street) VALUES(2, 'Peter', 'bakerstreet');

select * from Test

Results in :

Now wait for 60 seconds or so (or use the query_id).

select * from Test at(offset => -60);

DROP TABLE Test

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  town varchar,
  PRIMARY KEY (id));
  
--Insert some data into the table
INSERT INTO Test(id, name, street, town) VALUES(1, 'Hennie', 'Spicyvalley', 'Amsterdam');

select * from Test 

Results in :

Timetravelling before the recreation of the table is not possible. The statement (offset => -60) doesn't work, only when the age of recreated table becomes older than 60 seconds.

select * from Test at(offset => -60);

Resulting in : 

The next thing I would like to try is whether I could create a clone of the database at the moment of the initial creation of the table (the first one).

create OR REPLACE database BackupTimeTravel2_clone clone BackupTimeTravel2
  at(offset => -220);
  
-- Select the right database
USE DATABASE BackupTimeTravel2_clone;

select * from Test

Results in 

I have the old table back! So it seems that it's not possible to timetravel back on table level when the table is recreated, but it is still possible when you time travel back on database level.

We only lost the PK (I blogged  earlier about this) .

SELECT GET_DDL('Table', 'Test')

Results in :

create or replace TABLE TEST (
	ID NUMBER(38,0) NOT NULL,
	NAME VARCHAR(16777216),
	STREET VARCHAR(16777216)
);

Final thoughts

At first, I thought that with the help of time travelling through the table, the column could not be retrieved, but with time travelling on database level I was able get the table back in original state. So the blog is true in case of timetravelling on table level but not on database level.

When a table is recreated it's not possible to timetravel back to previous version of the table on table level but it's possible to get the table back when timetravelling is used on database level (but no PK/FK).

There is one thing I would like to test is the following : "If the precision of a column is decreased below the maximum precision of any column data retained in Time Travel, you will not be able to restore the table without first increasing the precision.". This is for a future blogpost. 

The complete script 

//=============================================================================
// Experiment 6 : change the table and see if we can time travel
//=============================================================================
ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;
USE ROLE SYSADMIN;
USE WAREHOUSE Compute_WH;

-- Create the database
CREATE OR REPLACE DATABASE BackupTimeTravel;

-- Select the right database
USE DATABASE BackupTimeTravel;

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  PRIMARY KEY (id));

--Insert some data into the table
INSERT INTO Test(id, name, street) VALUES(1, 'Hennie', 'Spicyvalley');

-- select some data from the table
select * from Test;

INSERT INTO Test(id, name, street) VALUES(2, 'Peter', 'bakerstreet');

select * from Test at(offset => -60);

ALTER TABLE Test  ADD COLUMN town varchar;

--Insert some data into the table
INSERT INTO Test(id, name, street, town) VALUES(1, 'Hennie', 'Spicyvalley', 'Amsterdam');

select * from Test 

select * from Test at(offset => -160);


ALTER TABLE Test  DROP COLUMN street



select * from Test at(offset => -500);

create database BackupTimeTravel_clone clone BackupTimeTravel
  at(offset => -500);
  
-- Select the right database
USE DATABASE BackupTimeTravel_clone;

select * from Test

ALTER WAREHOUSE IF EXISTS COMPUTE_WH SUSPEND;
//=============================================================================
// Experiment 7 : recreate the table and see if we can time travel
//=============================================================================
ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;
USE ROLE SYSADMIN;
USE WAREHOUSE Compute_WH;

-- Create the database
CREATE OR REPLACE DATABASE BackupTimeTravel2;

-- Select the right database
USE DATABASE BackupTimeTravel2;

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  PRIMARY KEY (id));

--Insert some data into the table
INSERT INTO Test(id, name, street) VALUES(1, 'Hennie', 'Spicyvalley');

-- select some data from the table
select * from Test;

INSERT INTO Test(id, name, street) VALUES(2, 'Peter', 'bakerstreet');

select * from Test

select * from Test at(offset => -60);

DROP TABLE Test

-- Create the table
CREATE OR REPLACE TABLE Test (
  id integer,
  name varchar,
  street varchar,
  town varchar,
  PRIMARY KEY (id));
  
--Insert some data into the table
INSERT INTO Test(id, name, street, town) VALUES(1, 'Hennie', 'Spicyvalley', 'Amsterdam');

select * from Test 

select * from Test at(offset => -120);



create OR REPLACE database BackupTimeTravel2_clone clone BackupTimeTravel2
  at(offset => -220);
  
-- Select the right database
USE DATABASE BackupTimeTravel2_clone;

select * from Test

ALTER WAREHOUSE IF EXISTS COMPUTE_WH SUSPEND;