Time traveling with snowflake

01/01/2023

Snowflake time-travel allows us to view the history of objects up to 90 days. This is a powerful feature that has helped me when debugging and can also be used for backups.

By default the retention period is 1 day, to view the current retention:

show parameters like '%DATA_RETENTION_TIME_IN_DAYS%' in account;

To view historic data we use the at keyword, e.g

select count(*) from users at (timestamp => '2022-01-01 12:30:00.000'::timestamp_tz);

For example to revert the users table to a previous state

alter table users rename to users_backup;
create or replace table users clone users_backup at (timestamp => '2022-12-12 12:00:00.000'::timestamp_tz);

There’s some extra work needed if the table or schema you want to view has been dropped. Especially if the table or schema has been dropped multiple times.

drop table users;
create or replace table users clone users_backup at (timestamp => '2022-12-13 12:00:00.000'::timestamp_tz);

drop table users;
create or replace table users clone users_backup at (timestamp => '2022-12-14 12:00:00.000'::timestamp_tz);

drop table users;

To view when the table was dropped, we can use the history command.

show tables history like '%uses%' in my_schema;

To view the users table with data from 2022-12-13, we will need to undrop the users table multiple times.

undrop table users;
alter table users rename to users_2022-12-14;
undrop table users;
select count(*) FROM users at (timestamp => '2022-12-13 12:00:00.000'::timestamp_tz);

This same process can be used for schemas as well.