Recovering Deleted Data in BigQuery: A Beginner’s Guide to Time Travel and Restoration

Data restoration in BigQuery allows users to recover deleted records within a certain time frame, depending on the time travel option set while creating the dataset.  

 

You can see above option while creating the dataset.

 

What is Data Restoration in BigQuery?

BigQuery provides a time travel feature that enables users to access past versions of their data within a specific retention period (up to 7 days). This feature is useful for recovering accidentally deleted records.

 

2. How Does BigQuery Time Travel Work?

·      When you delete records from a table, BigQuery retains a snapshot of the data for the time travel window.

·      You can retrieve previous versions of data using SYSTEM_TIME AS OF or table decorators.

·      However, if the entire table is deleted, restoration requires a special recovery command.

 

For example, I have employees table with following data.

 

Let me drop the columns FirstName and LastName from employees table.

 

ALTER TABLE myProject.myDataset.employees
DROP COLUMN FirstName;

ALTER TABLE myProject.myDataset.employees
DROP COLUMN LastName;

After dropping the columns, you can see current data looks like below

 

3. Methods to Restore Data

Using SYSTEM_TIME AS OF, we can query a past version of the table before the deletion: 

SELECT * FROM myProject.myDataset.employees FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE);

This retrieves the state of the table as it was 5 minutes ago.

 

4. Limitations of Data Restoration in BigQuery

·      Time-Dependent: Data restoration is only possible within the time travel window (up to 7 days).

·      No Support for External Tables: This feature does not work for external tables.

·      Not for Deleted Tables: If the entire table is deleted, you must restore it using the bq command.

 

5. Restoring a Deleted Table

If a table is deleted within the time travel window, use the following command to restore the table.

 

bq cp --restore myProject:myDataset.deletedTable myProject:myDataset.newTable

 

In summary, BigQuery’s data restoration feature is a powerful tool for recovering deleted records, but it has limitations. Understanding time travel, SYSTEM_TIME AS OF will help you manage accidental data deletions effectively.

 

 

Previous                                                    Next                                                    Home