Understanding & Using Time Travel

Snowflake Time Travel enables accessing historical data (i.e. data that has been changed or deleted) at any point within a defined period. It provides powerful tools for restoring data that may have been accidentally or intentionally deleted, duplicating data from key points in the past, and analyzing data usage/manipulation over a specified period.

In this Topic:

What is Time Travel?

Time Travel in Continuous Data Protection lifecycle

Using Time Travel, you can perform the following actions within a defined period of time:

  • 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.

Time Travel SQL Extensions

To support Time Travel actions, the following SQL extensions are provided:

  • AT | BEFORE clause which can be specified in SELECT statements and CREATE...CLONE commands (immediately after the table name). The clause uses one of the following parameters to pinpoint the exact historical data you wish to access:

    • TIMESTAMP
    • OFFSET (time difference in seconds from the present time)
    • STATEMENT (identifier for statement, e.g. query ID)
  • UNDROP command for tables, schemas, and databases.

    Time Travel SQL extensions

Data Retention Period

A key component of Snowflake Time Travel is the data retention period.

When data in the system is updated, including deletion of data, the system preserves the state of the data before performing the update. The data retention period specifies the number of days for which this historical data is preserved and, therefore, Time Travel operations (SELECT, CREATE...CLONE, UNDROP) can be performed on the data.

The standard retention period is 1 day (24 hours) and is automatically enabled for all Snowflake accounts:

  • For Snowflake Standard Edition, the retention period can be changed to 0 at the account and object levels (for databases, schemas, and tables).
  • For Snowflake Enterprise Edition, the retention period can be changed as follows:
    • For temporary or transient objects, 0 or 1 day.
    • For permanent objects, any value from 0 up to 90 days.

Note

A retention period of 0 days for an object effectively disables Time Travel for the object.

When the retention period ends for an object, the historical data is purged from the system:

  • Purged data is not available for querying.
  • Purged objects can no longer be cloned.
  • Purged objects can no longer be restored.

To specify the data retention period for Time Travel:

  • The DATA_RETENTION_TIME_IN_DAYS object parameter can be used by your account administrators to set the default retention period for your account.
  • The same parameter can be used to override the default when creating databases, schemas, and individual tables.
  • The data retention period for a database, schema, or table can be altered at any time; however, the new period only applies to data updates that occur after the new period has gone into effect.

Enabling and Disabling Time Travel

No tasks are required to enable Time Travel. It is automatically enabled with the standard, 1-day retention period.

However, you may wish to upgrade to Snowflake Enterprise Edition to enable configuring longer data retention periods of up to 90 days for databases, schemas, and tables. Note that extended data retention requires additional storage which will be reflected in your monthly storage charges. For more information about storage charges, see Storage Costs for Time Travel and Fail-safe.

Time Travel cannot be disabled for an account; however, it can be disabled for individual databases, schemas, and tables by specifying DATA_RETENTION_TIME_IN_DAYS with a value of 0 for the object.

Specifying the Data Retention Period for an Object

By default, the maximum retention period is 1 day (i.e. one 24 hour period). With Snowflake Enterprise Edition, the default for your account can be set to any value up to 90 days:

  • When creating a table, schema, or database, the account default can be overridden using the DATA_RETENTION_TIME_IN_DAYS parameter in the command.
  • If a retention period is specified for a database or schema, the period is inherited by default for all objects created in the database/schema.

The retention period for an object can also be changed using the appropriate ALTER command.

For example:

CREATE TABLE mytable data_retention_time_in_days=90;

ALTER TABLE mytable SET data_retention_time_in_days=30;

Note

Changing the retention period for a table only applies to data updates that occur after the new retention period goes into effect. Historical data that existed prior to the change is bound by the original retention period.

The following diagram illustrates the effect of changing the retention period for a table from 1 day to 3 days:

Altering data retention for a table

Querying Historical Data

When any DML operations are performed on a table, Snowflake retains previous versions of the table data for a defined period of time. This enables querying earlier versions of the data using the AT | BEFORE clause.

This clause supports querying data either exactly at or immediately preceding a specified point in the table’s history within the retention period. The specified point can be time-based (e.g. a timestamp or time offset from the present) or it can be the ID for a completed statement (e.g. SELECT or INSERT).

For example:

  • The following query selects historical data from a table as of the date and time represented by the specified timestamp:

    SELECT * FROM my_table AT(TIMESTAMP => 'Mon, 01 May 2015 16:20:00 -0700'::timestamp);
    
  • The following query selects historical data from a table as of 5 minutes ago:

    SELECT * FROM my_table AT(OFFSET => -60*5);
    
  • The following query selects historical data from a table up to, but not including any changes made by the specified statement:

    SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');
    

Note

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.

Cloning Historical Objects

In addition to queries, the AT | BEFORE clause can be used with the CLONE keyword in the CREATE command for a table, schema, or database to create a logical duplicate of the object at a specified point in the object’s history.

For example:

  • The following CREATE TABLE command creates a clone of a table as of the date and time represented by the specified timestamp:

    CREATE TABLE restored_table CLONE my_table
      AT(TIMESTAMP => 'Mon, 09 May 2015 01:01:00 +0300'::timestamp);
    
  • The following CREATE SCHEMA command creates a clone of a schema and all its objects as they existed 1 hour before the current time:

    CREATE SCHEMA restored_schema CLONE my_schema AT(OFFSET => -3600);
    
  • The following CREATE DATABASE command creates a clone of a database and all its objects as they existed prior to the completion of the specified statement:

    CREATE DATABASE restored_db CLONE my_db
      BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');
    

Dropping and Restoring Objects

Dropping Objects

To drop a table, schema, or database, use the following commands:

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. Once dropped objects are removed from the system (i.e. purged), they cannot be restored.

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).

Listing Dropped Objects

Dropped tables, schemas, and databases can be listed using the following commands with the HISTORY keyword specified:

For example:

SHOW TABLES HISTORY LIKE 'load%' IN mytestdb.myschema;

SHOW SCHEMAS HISTORY IN mytestdb;

SHOW DATABASES HISTORY;

The output includes all dropped objects and an additional DROPPED_ON column, which displays the date and time when the object was dropped. If an object has been dropped more than once, each version of the object is included as a separate row in the output.

Note

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.

Restoring Objects

A dropped object that has not been purged from the system (i.e. the object is displayed in the SHOW object_type HISTORY output) can be restored using the following commands:

Calling UNDROP restores the object to its most recent state before the DROP command was issued.

For example:

UNDROP TABLE mytable;

UNDROP SCHEMA myschema;

UNDROP DATABASE mydatabase;

Note

If an object with the same name already exists, UNDROP fails.

Access Control Requirements and Name Resolution

Similar to dropping an object, a user must have OWNERSHIP privileges for an object to restore it. In addition, the user must have CREATE privileges on the object type for the database or schema where the dropped object will be restored.

Restoring tables and schemas is only supported in the current schema or current database, even if a fully-qualified object name is specified.

Example: Dropping and Restoring a Table Multiple Times

In the following example, the mytestdb.public schema contains two tables: loaddata1 and proddata1. The loaddata1 table is dropped and recreated twice, creating three versions of the table:

  • Current version
  • Second (i.e. most recent) dropped version
  • First dropped version

The example then illustrates how to restore the two dropped versions of the table:

  1. First, the current table with the same name is renamed to loaddata3. This enables restoring the most recent version of the dropped table, based on the timestamp.
  2. Then, the most recent dropped version of the table is restored.
  3. The restored table is renamed to loaddata2 to enable restoring the first version of the dropped table.
  4. Lastly, the first version of the dropped table is restored.
SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

DROP TABLE loaddata1;

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

CREATE TABLE loaddata1 (c1 number);
INSERT INTO loaddata1 VALUES (1111), (2222), (3333), (4444);

DROP TABLE loaddata1;

CREATE TABLE loaddata1 (c1 varchar);

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | Fri, 13 May 2016 19:05:51 -0700 |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

ALTER TABLE loaddata1 RENAME TO loaddata3;

UNDROP TABLE loaddata1;

SHOW TABLES HISTORY;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA3 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | Fri, 13 May 2016 19:04:46 -0700 |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+

ALTER TABLE loaddata1 RENAME TO loaddata2;

UNDROP TABLE loaddata1;

+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+
| created_on                      | name      | database_name | schema_name | kind  | comment | cluster_by | rows | bytes | owner  | retention_time | dropped_on                      |
|---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------|
| Tue, 17 Mar 2016 17:41:55 -0700 | LOADDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 48   | 16248 | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:05:32 -0700 | LOADDATA2 | MYTESTDB      | PUBLIC      | TABLE |         |            | 4    | 4096  | PUBLIC | 1              | [NULL]                          |
| Fri, 13 May 2016 19:06:01 -0700 | LOADDATA3 | MYTESTDB      | PUBLIC      | TABLE |         |            | 0    | 0     | PUBLIC | 1              | [NULL]                          |
| Tue, 17 Mar 2016 17:51:30 -0700 | PRODDATA1 | MYTESTDB      | PUBLIC      | TABLE |         |            | 12   | 4096  | PUBLIC | 1              | [NULL]                          |
+---------------------------------+-----------+---------------+-------------+-------+---------+------------+------+-------+--------+----------------+---------------------------------+