Understanding Snowflake’s Time Travel Feature

  • Share Post:
  • LinkedIn Icon
Snowflake Time Travel enables us to perform the following tasks:

• Accessing data that has been changed or deleted at any point within a retention period.

• Restoring tables, schemas, and databases that have been dropped.

• Create Clones of tables, schemas and databases at or before specific points in the past.

These tasks can be performed until the retention period, after which the data is moved into Snowflake Fail-safe.

Time Travel SQL Extensions:

In Snowflake Time Travel to access or clone historical data we use the Time Travel SQL Extensions AT or BEFORE clause which can be specified in SELECT statements and CREATE … CLONE commands. To pinpoint the exact historical data which you wish to access we use the below parameters with the SQL Extensions:

TIMESTAMP

OFFSET (time difference in seconds from the present time)
STATEMENT (identifier for statement, e.g. query ID)
Restoring Objects:

A dropped table or schema or database that has not yet been purged from the system (i.e. not yet moved into Snowflake Fail-safe) can be restored using the UNDROP command. Calling UNDROP restores the object to its most recent state before the object was dropped.

Enabling and Disabling Time Travel:

Time travel is automatically enabled in Snowflake with 1-day data retention period by default for all editions. However we can enable for longer a data retention period of up to 90 days for enterprise and higher editions. Time travel can be disabled for individual databases, schemas, and tables by specifying data retention time in days with a value of 0. To change the retention period for an object, use the appropriate ALTER command.

Storage Costs for Time Travel:

The storage costs are calculated for each 24-hour period (i.e. 1 day) from the time the data changed. Also, 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.

DataTerrain, with years of experience and reliable experts, is ready to assist. We have served more than 250 customers in the US and over 70 customers worldwide. We are flexible in working hours and do not need any long-term binding contracts.