Accessing 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 last seven days. You can use time travel to query data that was updated or deleted, restore a table that was deleted, or restore a table that expired.

Limitation

Time travel only provides access to table data for the past seven days. To preserve table data for longer than seven days, use table snapshots.

Query data at a point in time

You can query a table's historical data from any point in time within the previous seven days 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);

Note: The FOR SYSTEM_TIME AS OF clause is supported in standard SQL. For legacy SQL, time decorators provide equivalent functionality.

If the timestamp specifies a time from more than seven days ago 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 seven days after deletion.

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.

Table security

To control access to tables in BigQuery, see Introduction to table access controls.