Data retention with time travel and fail-safe

This document describestime travel andfail-safe data retention windows fordatasets. During the time travel and fail-safe periods, data that you havechanged or deleted in any table in the dataset continues to be stored in caseyou need to recover it.

Time travel and data retention

You can access changed or deleted data from any point within the time travelwindow, which covers the past seven days by default. Time travel lets youquerydata that was updated or deleted,restore atable ordataset that was deleted, restoreatable that expired,orrestore a table to a point in time.

You can set the duration of the time travel window, from a minimum of two daysto a maximum of seven days. A longer time travel window is useful in cases whereit is important to be able to recover updated or deleted data. A shorter timetravel window lets you save on storage costs when using thephysical storagebilling model.These savings don't apply when using the logical storage billing model. For moreinformation on how the storage billing model affects cost, seeBilling.

Configure the time travel window

You set the time travel window at the dataset or project level. These settingsthen apply to all tables associated with the dataset or project.

Set the project-level time travel window

To specify the project-level default time travel window, you can use datadefinition language (DDL) statements. To learn how to set the project-level timetravel window, seeManage configuration settings.

Set the dataset-level time travel window

To specify or modify the time travel window for a dataset, you can use theGoogle Cloud console, the bq command-line tool, or the BigQuery API.

• To specify the default time travel window for new datasets, seeCreatedatasets.
• To modify or update the time travel window for an existing dataset, seeUpdate time travel windows.

When modifying a time travel window, if the timestamp specifies a time outsidethe time travel window, or from before the table was created, then the queryfails and returns an error like the following:

TableID was created at time which isbefore its allowed time travel intervaltimestamp. Creationtime:timestamp

How time travel works

BigQuery uses a columnar storage format. This means that data isorganized and stored by column rather than by row. When you have a table withmultiple columns, the values for each column across all rows are stored togetherin storage blocks.

When you modify a cell in a BigQuery table, you are changing aspecific value within a particular row and a specific column. BecauseBigQuery stores columns together, modifying even a single cellwithin a column typically requires reading the entire storage block containingthat column's data for the affected rows, applying the change, and then writinga new version of that storage block.

The time travel feature works by tracking the versions of storage blocks thatmake up your table. When you update data, BigQuery doesn't justmodify the existing storage block in place. Instead, it creates a new version ofthe affected storage blocks with the updated data. The previous version isthen retained for time travel purposes.

BigQuery uses adaptive file sizes and storage blocks. The size ofstorage blocks is not fixed but can vary depending on factors like the size ofthe table and its data distribution. Changing even one cell in a storage blockchanges the data for that column, potentially affecting many rows. Therefore,the unit of data that is versioned and sent to time travel is often the entirestorage block that contains the modified data of that column, not just a singlecell.

For this reason, changing one cell can result in more data being sent to timetravel than just the size of the change.

How the time travel window affects table and dataset recovery

A deleted table or dataset uses the time travel window duration that was ineffect at the time of deletion.

For example, if you have a time travel window duration of two days and thenincrease the duration to seven days, tables deleted before that change are stillonly recoverable for two days. Similarly, if you have a time travel windowduration of five days and you reduce that duration to three days, any tablesthat were deleted before the change are still recoverable for five days.

Because time travel windows are set at the dataset level, you can't change thetime travel window of a deleted dataset until it is undeleted.

If you reduce the time travel window duration, delete a table, and thenrealize that you need a longer period of recoverability for that data, you cancreate 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, seeCreate a table snapshot using time travel.

Time travel and row-level access

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

The followingIdentity and Access Management (IAM)permission is required:

The following BigQuery role provides the required permission:

Thebigquery.rowAccessPolicies.overrideTimeTravelRestrictions permissioncan't be added to acustom role.

• Table deletion or replacement: When a table is deleted, or when its data is fully replaced (for example, by using theWRITE_TRUNCATE write disposition in a load job or by using theCREATE OR REPLACE TABLE statement), the previous contents of the table are retained.
• Partition deletion: If a specific partition is deleted from apartitioned table, the data belonging to that specific partition is retained. Other partitions in the table aren't affected.

You can't query or directly recover data in fail-safe storage. To recover datafrom fail-safe storage, contactCloud Customer Care.

If you set yourstorage billing modelto use physical bytes, you are billed separately for thebytes used for time travel and fail-safe storage. Time travel and fail-safestorage are charged at the active physical storage rate. You canconfigure the time travel window to balancestorage costs with your data retention needs.

If you set your storage billing model to use logical bytes, the total storagecosts for time travel and fail-safe storage are included in the base rate thatyou are charged.

The following table show a comparison of physical and logical storage costs:

If you use physical storage, you can see the bytes used by time travel andfail-safe by looking at theTIME_TRAVEL_PHYSICAL_BYTES andFAIL_SAFE_PHYSICAL_BYTES columns in theTABLE_STORAGE andTABLE_STORAGE_BY_ORGANIZATIONviews. For an example of how to use one of these views to estimate your costs,seeForecast storage billing.

Storage costs apply for time travel and fail-safedata, but you are only billed if data storage fees don't apply elsewhere inBigQuery. The following details apply:

• When a table is created, there is no time travel or fail-safe storage cost.
• If data is changed or deleted, then you are charged for the storage of thechanged or deleted data saved by time travel during the time travel windowand the fail-safe period. This is similar to the storage pricing for tablesnapshots and clones.

Data retention example

The following table shows how deleted or changed data moves betweenstorage retention windows. This example shows a situation where the total activestorage is 200 GiB and 50 GiB is deleted with a time travelwindow of seven days:

Deleting data from long-term physical storage works in the same way.

Limitations

Data retrieval with time travel is subject to the following limitations:

• Time travel only provides access to historical data for the duration of thetime travel window. To preserve table data for non-emergency purposesfor longer than the time travel window, usetable snapshots.
• If a table has, or has previously had, row-level access policies, then timetravel can only be used by table administrators. For more information, seeTime travel and row-level access.
• Time travel does not restore table metadata.
• Time travel is not supported in the following table types:
  • External tables. However, forApache Iceberg external tables, you can use theFOR SYSTEM_TIME AS OF clauseto access snapshots that are retained in your Icebergmetadata.
  • Temporary cached query result tables.
  • Temporary session tables.
  • Temporary multi-statement tables.
  • Tables listed under external datasets.

What's next

• Learn how toquery and recover time travel data.
• Learn more abouttable snapshots.