Access historical data

BigQuery lets you use time travel to access data stored in BigQuery that has been changed or deleted.

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.

After you replace an existing table by using the CREATE OR REPLACE TABLE statement, you can 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 LOCATION

Where LOCATION is the dataset's location.

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.

What's next