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.

Query data at a point in time

You can query a snapshot of a table 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.

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 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 a snapshot to a new table, as described in this document.

Restore a table from a point in time

You can restore a table from a snapshot by copying the snapshot into a table. Copying a snapshot works even if the table was deleted or has expired, as long as you restore the table within seven days after deletion.

To copy a table snapshot, add a snapshot decorator to the table name. A snapshot decorator has the following syntax:

  • tableid@TIME where TIME is the time in milliseconds since the Unix epoch.
  • tableid@-TIME_OFFSET where TIME_OFFSET is the relative offset from the current time, in milliseconds.
  • tableid@0: Oldest available snapshot.

The following example uses the bq command-line tool to copy a table named table1 from one hour ago into a table named table1_restored, using a relative offset.

bq cp mydataset.table1@-3600000 mydataset.table1_restored

The next example specifies an absolute epoch value. This example only works within seven days of the date shown.

date "+%s" -d "02/20/2020 01:00:00"
1582160400
bq cp mydataset.table1@1582160400 mydataset.table1_restored

For more information, see Restoring deleted tables.