Snowflake : Is time travel a replacement for backups (Part II) ?

Introduction

In a couple of blogpposts I've discovered the time travel- and clone capabilities of Snowflake. There are several ways to execute timetravel: time travel on a table, time travel on a schema and time travel on a database. There is also the possibillity of cloning and there is, what I call, "the swipe a version of the table away in order to retrieve an earlier version" (rename and undrop). 

This investigation is due to an event that I've experienced at my current project. In short, we recreated schemas with tables with constraints, but using time travel and cloning resulted that the constraints were gone.

I've already tried to time travel on table level and on database level, but not on schema level. And, this blogpost is about the latter one. What happens when a tablein a schema is recreated and you want to go back to the previous version of the table (with time travel on schema level).

The experiment

First setup the experiments with some scripts. It is a script with statements that create a database in the schema TST.  A couple of insert statements follows.

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

-- Create the database
CREATE OR REPLACE DATABASE BackupTimeTravel10;

-- Select the right database
USE DATABASE BackupTimeTravel10;

CREATE SCHEMA TST;

USE SCHEMA TST;

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

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

The next step is to drop the TST schema and recreate the schema again. The next step is a table creation but with another structure and data.

-- DROP the schema
DROP SCHEMA TST;

CREATE OR REPLACE SCHEMA TST;

USE SCHEMA TST;

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

select * from TST.Test; 

SELECT GET_DDL('Table', 'TST.Test')

The next step is trying to time travel throught the schema, but I can tell you it will not succeed.

create OR REPLACE SCHEMA TST1 clone TST
  at(offset => -120);

USE SCHEMA TST1;

select * from Test;

Results in :

And let's check again if we can retrieve the table if we clone the database. I've done this 

create OR REPLACE database BackupTimeTravel10_clone clone BackupTimeTravel10
  at(offset => -220);
USE DATABASE BackupTimeTravel10_clone;
select * from TST.Test;

Results in :

And we have our previous version of the table back.

SELECT GET_DDL('Table', 'TST.Test')

Results in :

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

The constraint is not present.

Final Thoughts

I've conducted several experiments with time travel, cloning and undrop :

• Replace a table and try to use time travel on table level and clone to go back before the replacement of the table.
• Replace a schema and try to use time travel and clone to go back before the replacement of the schema (this post).
• Replace a table and try to use time travel on database level and clone the database to go back before the replacement of the table.
• Use the undrop/drop trick to go back to a previous table.

My observations leads to the following conclusions: In the cases when a table is recreated in a database in Snowflake you can't use time travel on table- and schemalevel. It's not possible to retrieve the previous version. Time travel (and cloning) on database level is possible, but the constraints will not be there.

Rename and undrop a table is also possible to retrieve a previous version of the table (and with constraints).

Hennie