An insurer keeps a 40 TB BigQuery dataset. It must be able to restore the data as it stood no more than an hour before an accidental delete, keep those recovery points for 90 days, and minimize storage cost. Which approach is most cost-effective?
Show answer and explanation
Answer: B
BigQuery table snapshots record a point-in-time copy of a table and store only the data that differs from its base table, which keeps storage costs low for slowly changing datasets. Scheduled queries can run CREATE SNAPSHOT TABLE statements every hour, so recovery points are never more than an hour apart, and expiration settings can keep them for the required 90 days. Time travel is built in but covers only a short window, which is why it does not satisfy the retention need. A continuously refreshed cross-region copy stores the full dataset twice. Cloud SQL is not built for analytics at this scale.
Why the other options are wrong
- A. A continuously refreshed copy in another region stores the full 40 TB again and pays for cross-region replication. That is far more expensive than snapshots, which store only changed data.
- C. Hourly exports write full data to Cloud Storage, incurring storage costs and a restore process that is slow and manual. Recovering a table this way is more error prone than a direct restore from a snapshot.
- D. Cloud SQL is a transactional database service sized for operational workloads, not a 40 TB analytics store. Mirroring BigQuery tables into it would be complex and expensive.