How to Restore Deleted BigQuery Tables

Despite the complexity of data infrastructure, automation of tasks and expertise of a team, we can’t data engineer one thing: The fact that humans can and always will make mistakes.

As a data engineer one of the most significant (and terrifying) mistakes you can make is overwriting or deleting data from your organization’s data warehouse.

Luckily, if your organization uses Google Cloud to host its infrastructure, there are ways to address this costly and embarrassing mistake.

Although I’ll mention three methods to restore BigQuery data, my preference is to do so through the Python API.

To that end, at the conclusion of this story I’ll provide a short code snippet, derived from Google’s documentation, that automates the snapshot retrieval and data restoration process.

Build Your Pipeline To A Data Engineering Career

You’ve reached the limit of the public preview. The full version of this post includes the implementation details: The code, the edge cases, and the "why" behind the architecture.

When you join PipelineToDE, you get:

• The DA → DE Pathway Course: A structured roadmap to bridge the gap between analysis and engineering.
• Weekly Senior Deep Dives: Fresh, tactical insights on Python, Cloud (GCP/AWS), and modern orchestration delivered every week.
• Production-Ready Blueprints: Access to 80+ protected stories and code repos from my time in the trenches as a Senior DE
• The DE Job Board (Coming Soon): Exclusive access to a curated board of high-agency Data Engineering roles.

Note: The processes describe below only apply to restoring deleted tables. BigQuery does not provide accessible methods for restoring deleted datasets.

The four ways Google BigQuery enables data engineers to restore mistaken deletions are by using:

• The BigQuery UI to hit ‘Restore’ within the console
• The BigQuery command line, using the ‘bq cp’ command
• Python and the BigQuery API
• Accessing the SYSTEM TIME

There is also a way to generate backups of tables using standard SQL, but I haven’t found that method to be as effective as those listed below.

Restoring Data with the BigQuery UI

Perhaps the simplest method of the three is by restoring a snapshot within the BigQuery user interface. The limitation of this method is that you must have already stored a snapshot. For context, a snapshot is a saved version of your data that you can use refer to retroactively. Think of a Google BigQuery snapshot like a checkpoint in a video game. You can return to and ‘play on’ from this point. However, this method also requires you to capture a snapshot or, to continue with our video game comparison, to actively save your progress.

Restoring Data with the BigQuery Command Line

If you’re unable to find a ‘restore’ option within the BigQuery UI, the next best option is to resort to the command line.

Begin a cloud shell terminal session. What follows is a bit of code that will allow you to access an earlier version of the table you deleted and copy that output to a new table.

It’s important that you provide a new name for the new table or you may overwrite the snapshot, making it difficult to retrieve the data you want.

bq cp 'your_project:your_dataset.deleted_dataset'@-360000 your_project:dataset.new_backup

BQ cp simply tells the CLI to copy a snapshot of the deleted dataset. The ‘@-360000’ specifies the time in the past you’d like to access. Keep in mind that this requires a millisecond input. The easiest way to get this value is to find the unix time and convert it to milliseconds. I’ll demonstrate how to do that in the next method with the Python BigQuery API.

Restoring Data with the BigQuery API (Python)

Using the BigQuery API via Python is the most efficient and clearest documented method I’ve found when it comes to restoring table data in BigQuery.

The advantage of creating a Python script to facilitate a backup is that you can explicitly define a time frame and convert that variable to unix automatically, avoiding the need to think about converting the hours since you accessed the table into seconds.

The Table

For this demonstration, I created a test table within my existing ‘xmas’ dataset. If you’re unfamiliar, the xmas dataset was created using data scraped from the web and data parsed from a PDF.

The temp table I created is very simple, with just three rows.

Tree_test is the table we’ll be deleting and restoring.

Note that, in the script below, we need to specify both the table we want to restore and the table that will contain the recovered snapshot, defined as ‘table_id’ and ‘recovered_table_id’ respectively.

import time 
import logging
from google.cloud import bigquery
import osdef restore_bq_data():
    os.environ["GOOGLE_APPLICATION_CREDENTIALS"]="/Users/zachquinn/Downloads/ornate-reef-332816-a7425b762ba2.json"
    
client = bigquery.Client()

table_id = 'ornate-reef-332816.xmas.tree_test'
recovered_table_id = 'ornate-reef-332816.xmas.tree_recovery'

The next part of the script retrieves the current time so that we don’t have to manually calculate milliseconds.snapshot_epoch = int(time.time() * 1000).

If you’re looking into the more distant past, this snippet can convert a date to unix time and unix to milliseconds.

unix = datetime.datetime(2022, 1, 12, 0, 0).strftime('%s')
unix_int = int(unix)
unix_ms = unix_int * 1000
    
past_snap = unix_ms

The next step, deletion, can be accomplished with a BigQuery-provided function, ‘delete_table.’ Again, this is for demonstration purposes. You wouldn’t want to include this line in a restoration script.

client.delete_table(table_id)

The final step is to copy the snapshot to a new table in BigQuery. In this case, we’ll call the new table ‘tree_recovery.’

snapshot_table_id = "{}@{}".format(table_id, snapshot_epoch)
    
job = client.copy_table(
        snapshot_table_id,
        recovered_table_id,
        location="US"
    )
    
 success = "The table restoration was successful."
 if job.result():
     print(
      "Restored data from deleted table {} to {}".format(table_id, recovered_table_id)
        )
      logging.info(f'The job result was: {success}')
    
 failure = "The table restoration was unsuccessful."
 if not job.result():
     print(failure)
     logging.info(f'The job result was: {failure}')

And now we’ll check the output:

It worked! Tree_recovery provides us with all of the fields and rows present in tree_test and contains the exact same schema.

UPDATE: System Timestamp

When this was originally written, only three methods (mentioned in the introduction) were intended to facilitate the recovery of BigQuery data.

However, now Google Cloud allows users to access and even query a snapshot of the table in BigQuery Studio.

The syntax.

FOR SYSTEM TIMESTAMP AS OF {DATE_RANGE}

Now with our example.

-- Recover data from previous day.

SELECT * FROM `xmas.tree_recovery`
FOR SYSTEM TIMESTAMP AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

In addition to recovery this has additional benefits.

• Powering DDL statements to derivative table from an existing snapshot
• Confirming QA; i.e. are rows from today's snapshot inflated compared to yesterday's?

Unlike the static methods that would allow us to recover/create a table, FOR SYSTEM TIMESTAMP can allow us to directly compare using queries.

WITH today AS (
SELECT date, COUNT(1) AS non_null_rows
FROM `xmas.tree_recovery`
GROUP BY 1 ORDER BY 1 DESC
),
yest AS (
SELECT date, COUNT(1) AS non_null_rows
FROM `xmas.tree_recovery`
FOR SYSTEM TIMESTAMP AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

SELECT today.date AS date, prod.non_null_rows AS prod_rows, yest.non_null_rows AS test_rows 
INNER JOIN yest 
ON today.date = yest.date 

We can take it the two step operation one step further. Accidentally deleted yesterday's data?

INSERT INTO `xmas.tree_recovery`

(
SELECT * FROM `xmas.tree_recovery`
FOR SYSTEM TIMESTAMP AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
)

To view this code as one script, feel free to visit my GitHub.

Keep Ingesting

You just finished a deep dive into BigQuery table recovery, which is one piece of the larger engineering puzzle. If you're ready to take a break from reading and start following a structured roadmap, head over to the DA → DE Pathway Course.

Your Module Suggestions

• Module 1: The Python ETL Blueprint — Start here if you’re still mastering production-grade scripts
• Full Course Index — Browse all modules and architectural patterns included in your membership

Remember: As a member, you have full access to the source code for every project in the course. No extra fees, just execution.