Stay organized with collections Save and categorize content based on your preferences.

Access historical data using time travel

BigQuery lets you use time travel to access data stored in BigQuery that has been changed or deleted. You can access the data from any point within the time travel window, which covers the past seven days. Time travel lets you query data that was updated or deleted, restore a table that was deleted, or restore a table that expired.

Limitations

  • Time travel only provides access to historical data for the duration of the time travel window. To preserve table data for longer than the time travel window, use table snapshots.

  • If a table has, or has previously had, row-level access policies, then time travel can only be used by table administrators. For more information, see Time travel and row-level access.

Query data at a point in time

You can query a table's historical data from any point in time within the time travel window by using a FOR SYSTEM_TIME AS OF clause. This clause takes a constant timestamp expression and references the version of the table that was current at that timestamp. The table must be stored in BigQuery; it cannot be an external table. There is no limit on table size when using SYSTEM_TIME AS OF.

For example, the following query returns a historical version of the table from one hour ago:

SELECT *
FROM `mydataset.mytable`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

If the timestamp specifies a time from prior to the time travel window or from before the table was created, then the query fails and returns an error like the following:

Invalid snapshot time 1601168925462 for table
myproject:mydataset.table1@1601168925462. Cannot read before 1601573410026.

If you replace an existing table by using the CREATE OR REPLACE TABLE statement, then you can still use FOR SYSTEM_TIME AS OF to query the previous version of the table.

If the table was deleted, then the query fails and returns an error like the following:

Not found: Table myproject:mydataset.table was not found in location US

However, you can restore the table by copying from a point in time to a new table, as described in this document.

Restore a table from a point in time

You can restore a table from historical data by copying the historical data into a table. Copying a data works even if the table was deleted or has expired, as long as you restore the table within the duration of the time travel window.

To copy historical data from a table, add a decorator to the table name using the following syntax:

  • tableid@TIME where TIME is the number of milliseconds since the Unix epoch.
  • tableid@-TIME_OFFSET where TIME_OFFSET is the relative offset from the current time, in milliseconds.
  • tableid@0: Specifies the oldest available historical data.

For example, the following bq command-line tool command copies a table named table1 from one hour ago into a table named table1_restored. The time, -3600000, is specified in milliseconds using a relative offset.

bq cp mydataset.table1@-3600000 mydataset.table1_restored

The following bq command-line tool command copies a table named table1 into a table named table1_restored. The time, 1624046611000, is specified as milliseconds since the Unix epoch.

bq cp mydataset.table1@1624046611000 mydataset.table1_restored

For more information, see Restoring deleted tables.

Configure the time travel window

You can set the duration of the time travel window, from a minimum of two days to a maximum of seven days. Seven days is the default. You set the time travel window at the dataset level, which then applies to all of the tables within the dataset.

You can configure the time travel window to be longer in cases where it is more important to have the option of recovering the data, and to be shorter where that isn't a concern. Using a shorter time travel window allows you to save on storage costs. It can also allow you to conform to data retention policies that require hard deletion of data in a specific time frame.

How the time travel window affects table recovery

A deleted table is permanently associated with the time travel window duration that was in effect at the time of table deletion.

For example, if you have a time travel window duration of two days and then increase the duration to seven days, tables deleted before that change are still only recoverable for two days.

Similarly, if you have a time travel window duration of five days and you reduce that duration to three days, any tables that were deleted before the change are still recoverable for five days.

If you reduce the time travel window duration, delete a table, and then realize that you need a longer period of recoverability for that data, you can create a snapshot of the table from a point in time prior to the table deletion. To be successful, you must do this while the deleted table is still recoverable. For more information, see Create a table snapshot using time travel.

Specify a time travel window

You can use the Google Cloud console, the bq command-line tool, or the BigQuery API to specify the time travel window for a dataset.

For instructions on how to specify the time travel window for a new dataset, see Create datasets.

For instructions on how to update the time travel window for an existing dataset, see Update time travel windows.

Time travel and row-level access

If a table has, or has had, row-level access policies, then only a table administrator can access historical data for the table.

The following Identity and Access Management (IAM) permission is required:

Permission Resource
bigquery.rowAccessPolicies.overrideTimeTravelRestrictions The table whose historical data is being accessed

The following BigQuery role provides the required permission:

Role Resource
roles/bigquery.admin The table whose historical data is being accessed

The bigquery.rowAccessPolicies.overrideTimeTravelRestrictions permission can't be added to a custom role.

What's next