How to achieve cost-effective BigQuery disaster recovery?

PDE By islever Editorial Last updated

Short answer

A Schedule a daily export of the table to a Cloud Storage dual or multi-region bucket.

Question

You are using BigQuery with a regional dataset that includes a table with the daily sales volumes. This table is updated multiple times per day. You need to protect your sales table in case of regional failures with a recovery point objective (RPO) of less than 24 hours, while keeping costs to a minimum. What should you do?
  1. Schedule a daily export of the table to a Cloud Storage dual or multi-region bucket. Correct Answer
  2. Schedule a daily copy of the dataset to a backup region.
  3. Schedule a daily BigQuery snapshot of the table.
  4. Modify ETL job to load the data into both the current and another backup region.

Key Takeaways

The exam tests the ability to select the most cost-effective disaster recovery solution that meets specific RPO constraints

To protect BigQuery data from regional failures with minimal costs and an RPO under 24 hours

Common mistake: Choosing Option D (Dual-write ETL) is a common mistake because it offers the best RPO but violates the

Expert Analysis

Why the Answer Is Correct

Option A is correct because exporting to a Cloud Storage dual or multi-region bucket provides the necessary redundancy against regional outages at a significantly lower storage cost than keeping data in BigQuery. A daily export satisfies the Recovery Point Objective (RPO) of less than 24 hours, ensuring that data loss is limited to a single day at most. Google's official documentation identifies this method as the most cost-efficient disaster recovery strategy for BigQuery.

Why the Other Options Are Wrong

Option C is incorrect because BigQuery snapshots are stored in the same region as the original table, offering no protection against regional failures. Option D is incorrect because, while it provides near real-time recovery, it is not cost-effective; maintaining active tables and ETL processes in two regions doubles the operational costs. Option B is less optimal than A because copying a dataset to another region incurs higher BigQuery storage costs compared to the cheaper storage classes available in Cloud Storage.

Community Comment Notes

Commenters highlighted that Option A is supported by official Google documentation as the most cost-efficient solution (Comment [1]). There was debate regarding the cost comparison between Options A and B, with Comment [6] confirming that saving data to GCS is cheaper than copying to another BQ dataset. Additionally, Comment [5] correctly identified that snapshots are regional and thus insufficient for disaster recovery.

Official Reference

https://cloud.google.com/blog/topics/developers-practitioners/backup-disaster-recovery-strategies-bigquery

This link opens the official vendor documentation in a new tab.

Exam Strategy

When faced with

Community Votes

A
67%
D
33%

67% of anonymous learners picked answer A. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 7 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 7

  1. HectorLeon2099 👍 4 Answered A
    Option A is the most cost efficient: https://cloud.google.com/blog/topics/developers-practitioners/backup-disaster-recovery-strategies-bigquery
  2. joelcaro 👍 2 Answered D
    Opción D: Modify ETL job to load the data into both the current and another backup region Evaluación: Ajustar el ETL para escribir en dos tablas (una en la región principal y otra en una región de respaldo) asegura que los datos estén disponibles en ambas ubicaciones casi en tiempo real. Esto garantiza un RPO de menos de 24 horas, ya que las actualizaciones intradía se reflejan en ambas regiones. Aunque podría aumentar los costos de almacenamiento por duplicar los datos, es la solución más efectiva y directa para proteger contra fallos regionales.
  3. Pime13 👍 1 Answered A
    This approach ensures that your data is stored in multiple regions, providing redundancy and protection against regional failures c is not possible it has some limitations compared to exporting the table to a Cloud Storage dual or multi-region bucket: Regional Limitation: BigQuery table snapshots are limited to the same region as the base table. This means that if the entire region experiences a failure, the snapshot may not be accessible. Storage Costs: While snapshots can be cost-effective, they still incur storage costs for the data that is different from the base table. Exporting to Cloud Storage can be more cost-effective, especially when using dual or multi-region buckets. RPO Considerations: Both options can meet an RPO of less than 24 hours, but exporting to Cloud Storage provides additional redundancy by storing data in multiple regions, enhancing data availability and durability
  4. MarcoPellegrino 👍 1 Answered D
    https://cloud.google.com/blog/topics/developers-practitioners/backup-disaster-recovery-strategies-bigquery Google presents both A and D Why A: - Cost: Lower. GCS storage is significantly cheaper than BigQuery storage. You pay for storage in GCS and minimal egress charges when exporting. - Complexity: Simpler. You schedule a daily export job. Restoring involves importing from GCS to BigQuery in another region. - Consistency: Easier to manage. The export process creates a consistent snapshot of the data at the time of export. You might have some latency (up to 24 hours in this scenario), but the data within the export is consistent. - RPO: Meets the requirement. A daily export ensures an RPO of less than 24 hours. - RTO: Depends on the restore process from GCS to BigQuery. You can pre-provision slots in the backup region to minimize restore time.
  5. mdell 👍 1 Answered B
    In most cases, it is cheaper to copy a BigQuery dataset to a new region directly rather than exporting it to a Cloud Storage bucket and then loading it into a new BigQuery dataset in the desired region, as you only pay for data transfer costs when copying within BigQuery, while exporting to a bucket incurs additional storage charges for the exported data in Cloud Storage, even if it's only temporary. Key points to consider: No extra storage cost for copying: When copying a BigQuery dataset to a new region, you only pay for the data transfer cost, not the storage of the data in a separate location. Storage cost for exporting: Exporting data to a Cloud Storage bucket means you are charged for the storage of that data in the bucket until you delete it, even if you are just temporarily storing it for transfer.
Show 2 more comments
  1. Parandhaman_Margan 👍 1 Answered C
    Meets the RPO requirement (< 24 hours) Cost-effective solution Quick recovery from regional failures
  2. FireAtMe 👍 1 Answered A
    Both A and B works. But it is cheaper to save data in GCS.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide