Creating a backup of BigQuery project

Question

I wonder what is the best strategy to backup data in BigQuery?

So far we are using BigQuery to store many different datasets coming from different sources. We do store the raw data (csv files) aside but re-creating the whole thing from them will be difficult and time-consuming task.

Is there a way to make a snapshot of all datasets/tables in the project?

Answer 1

So the question here mostly would make sense if you name the context.

BigQuery is a managed service, hence your data is replicated and you trust Google Cloud that it will be available all the time. In case of failure BigQuery engineers will handle the situation, you cannot start a rollback or healing or whatever as it's managed.

Suppose that all data is wiped out somehow (major earthquake + bombing hits several datacenters etc..) your data is lost if you don't have a source to build again. This is also true to other data that you have in your custody not just a BQ project. Maybe your CEO credentials are used by a hacker to wipe out first all your backups, then your current instances, hence all company's data is lost, and you cannot rebuild any data back to normal state, as you don't have backups anymore.

In order to be prepared for the ultimate disaster where BQ service gets shutdown, or Google is closing it's service or whatever worse situation, you need to have backup to rebuild your data. It's enough if you keep your raw files and be able to replay them. We usually store this as in a few years we may consider to relocate all other data to a different provider eg: Skynet Data Center etc. :) and we can use them again to replay our db state.

You can export the BigQuery tables and data to Google Cloud Storage, and you can move from there wherever you want cold storage etc... Also you can import files as large as 5TB when you want to restore.