Accidentally deleted Bigquery data — data recovery

BigQuery matins a 7-day history and allows you to query a point-in-time snapshot of your data by using either table decorators or SYSTEM_TIME AS OF in the FROM clause. You can easily revert changes without having to request recovery from backups

To revert a change you can either use standard SQL or bq command-line tools.

For example,

bq cp project:dataset.table@-36000 project:dataset.table

This command will replace the table with data from the same table 1 hour ago.

SELECT * FROM `project:dataset.table` 
FOR SYSTEM TIME AS OF 
'2020-08-28 23:00:00-07:00'

This command will retrieve the data at a point in time for the given table. You can then replace the incorrect table with the point in time data. This works even after the dataset is deleted.

Similarly, using standard SQL dialogue, you can query the data 1 hour ago

SELECT * FROM `project.dataset.table` 
FOR SYSTEM_TIME AS OF 
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

or create the new table using data 1 hour ago

CREATE OR REPLACE TABLE `project.dataset.table` 
AS 
SELECT * FROM `project.dataset.table` 
FOR SYSTEM_TIME AS OF 
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)