Snowflake : Time Travel… How cool is this?

Rajiv Gupta

Published in

Dev Genius

6 min readMay 5, 2021

Have you ever screwed your database in the past ?

Have you updated wrong data in any table and want your old data back ?

Have you dropped the table or schema or database by mistake ?

Ever faced need of point-in-time recovery from the past?

Does this sounds similar?

All have been through this phase at some point…!

In my early stage of career I had also screwed my database environment while testing something. It's been a very generic case with any database developer.

Just imagine how you had convinced the DBA team in the past to get your mistake fixed.

How much relevant this topic is with respect to SnowPro Core exam?

This topic is very important, and you may expect a couple of question from this topic in SnowPro exam.

What is Snowflake Time Travel?

Time travel is a very powerful feature in Snowflake which is powered by the metadata & storage layer of Snowflake. It serves as a powerful tool for performing the following tasks:

Query data in the past that has since been updated or deleted.
Create clones of entire tables, schemas, and databases at or before specific points in the past.
Restore tables, schemas, and databases that have been dropped.

Is there any retention period for Time Travel data cycle ?

If the data cycle passes below period, the data is moved into Snowflake Fail-safe(7 days non-configurable period for disaster recovery support by Snowflake) and these actions can no longer be performed. A retention period of 0 days for an object effectively disables Time Travel for the object.

0–1 days for Standard Edition.
0–90 days for Enterprise Edition or above.

More about DATA_RETENTION_TIME_IN_DAYS parameter

This can be set at Account or Database or Schema or Table level using DATA_RETENTION_TIME_IN_DAYS parameter. Value at lowest(Table) level get the preference over top(Account). If nothing is specified at lower level than the value specified at top level is inherited as default for all underline objects. Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set. For example:

If you change the retention period at the account level, all databases, schemas, and tables that do not have an explicit retention period automatically inherit the new retention period.
If you change the retention period at the schema level, all tables in the schema that do not have an explicit retention period inherit the new retention period.

How to Check DATA_RETENTION_TIME_IN_DAYS at different level ?

— Show parameters for account
SHOW PARAMETERS FOR ACCOUNT;
— Show parameters for a specific database
SHOW PARAMETERS FOR DATABASE TIMETRAVEL;
— Show parameters for a specific table
SHOW PARAMETERS FOR TABLE EMPLOYEE;

How to modify DATA_RETENTION_TIME_IN_DAYS at different level ?

To modify this parameter you must be having ACCOUNTADMIN role or equivalent privilege.

— Set account level parameter (must be an ACCOUNTADMIN)
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 30;
SHOW PARAMETERS FOR ACCOUNT;
— Set database level parameter
ALTER DATABASE TIMETRAVEL SET DATA_RETENTION_TIME_IN_DAYS = 20;
SHOW PARAMETERS FOR DATABASE TIMETRAVEL;
— Set table level parameter
ALTER TABLE EMPLOYEE SET DATA_RETENTION_TIME_IN_DAYS = 10;
SHOW PARAMETERS FOR TABLE EMPLOYEE;

How we can use Time Travel in a different phase of data lifecycle?

We can utilize time travel SQL extension in 3 ways along with AT | BEFORE clause in Select & Create… Clone statement.

TIMESTAMP: Based on certain timestamp in past
OFFSET : Time difference in past from current time in second.
STATEMENT: Based on SQL query ID.

Note for AT | BEFORE (Source: Snowflake):

The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter.
The BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter.
If the TIMESTAMP, OFFSET, or STATEMENT specified in the AT | BEFORE clause falls outside the data retention period for the table, the query fails and returns an error.

How cool it would be if we can UNDO the wrong step in our daily life well that is quite not possible, but we can UNDROP the database/schema/table in snowflake. Isn't’d that’s cool ?

Snowflake metadata & storage layer powered us to UNDROP, CLONE & QUERY from point-in-time from past that too without going to DBA provided the data lifecycle has not moved to Fail-safe phase.

How about a quick Demo on Time Travel ?

Is Time Travel feature free ?

No, nothing is free in Snowflake :)

Time Travel is by default enabled for all edition without any extra cost. All it charges for is storage. Longer the data retention period, more the data storage cost. Regardless of whatever cost it may take it is worth of the same. Snowflake minimizes the amount of storage required for historical data by maintaining only the information required to restore the individual table rows that were updated or deleted. As a result, storage usage is calculated as a percentage of the table that changed. Full copies of tables are only maintained when tables are dropped or truncated.

Things to Remember:

After dropping an object, creating an object with the same name does not restore the object. Instead, it creates a new version of the object. The original, dropped version is still available and can be restored.
Restoring a dropped object restores the object in place (i.e. it does not create a new object).
If an object with the same name already exists, UNDROP fails. You must rename the existing object, which then enables you to restore the previous version of the object.
When a table, schema, or database is dropped, it is not immediately overwritten or removed from the system. Instead, it is retained for the data retention period for the object, during which time the object can be restored.
After the retention period for an object has passed and the object has been purged, it is no longer displayed in the SHOW <object_type> HISTORY output.

Hope this blog help you to get insight on Snowflake Time Travel feature. If you are interested in learning more details about time travel you can refer to Snowflake documentation. Feel free to ask a question in the comment section if you have any doubts regarding this. Give a clap if you like the blog. Stay connected to see many more such cool stuff. Thanks for your support.

You Can Find me:

Follow me on Medium: https://rajivgupta780184.medium.com/

Follow me on Twitter: https://twitter.com/RAJIVGUPTA780

Connect with me in LinkedIn: https://www.linkedin.com/in/rajiv-gupta-618b0228/

Subscribe to my YouTube Channel: https://www.youtube.com/channel/UC8Fwkdf2d6-hnNvcrzovktg