Data retention with time travel and fail-safe

This document describes time travel and fail-safe data retention for datasets. During the time travel and fail-safe periods, data that you have changed or deleted in any table in the dataset continues to be stored in case you need to recover it.

Time travel

You can access data from any point within the time travel window, which covers the past seven days by default. Time travel lets you query data that was updated or deleted, restore a table that was deleted, or restore a table that expired.

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 important to have a longer time to recover updated or deleted data, and to be shorter where it isn't required. Using a shorter time travel window lets you save on storage costs when using the physical storage billing model. These savings don't apply when using the logical storage billing model.

For more information on how the storage billing model affects cost, see Billing.

How the time travel window affects table recovery

A deleted table always uses 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. 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.

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

Table ID was created at time which is
before its allowed time travel interval timestamp. Creation
time: timestamp

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.

  • Run the following command to get the equivalent Unix epoch time:

    date -d '2023-08-04 16:00:34.456789Z' +%s000

  • Replace the UNIX time 1691164834000 received from the above command in the bq command-line tool. Run the following command to restore a copy of the deleted table deletedTableID in another table restoredTable, within the same dataset myDatasetID:

    bq cp myProjectID:myDatasetID.deletedTableID@1691164834000 myProjectID:myDatasetID.restoredTable

Fail-safe

BigQuery provides a fail-safe period. During the fail-safe period, deleted data is automatically retained for an additional seven days after the time travel window, so that the data is available for emergency recovery. Data is recoverable at the table level. Data is recovered for a table from the point in time represented by the timestamp of when that table was deleted. The fail-safe period is not configurable.

You can't query or directly recover data in fail-safe storage. To recover data from fail-safe storage, contact Cloud Customer Care.

Billing

If you set your storage billing model to use physical bytes, the total storage costs you are billed for include the bytes used for time travel and fail-safe storage. If you set your storage billing model to use logical bytes, the total storage costs you are billed for do not include the bytes used for time travel or fail-safe storage. You can configure the time travel window to balance storage costs with your data retention needs.

If you use physical storage, you can see the bytes used by time travel and fail-safe by looking at the TIME_TRAVEL_PHYSICAL_BYTES and FAIL_SAFE_PHYSICAL_BYTES columns in the TABLE_STORAGE and TABLE_STORAGE_BY_ORGANIZATION views.

Limitations

  • Time travel only provides access to historical data for the duration of the time travel window. To preserve table data for non-emergency purposes 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.
  • Time travel does not restore table metadata.

What's next