Disaster Recovery and Backups on GCP

Disaster recovery on GCP sounds like a plan for earthquakes. In practice, the event you will actually face is quieter: someone runs an UPDATE without its WHERE clause, a pipeline overwrites the wrong partition, a script deletes a bucket it should not have. No data centre is harmed, and the data is gone all the same.

The two problems need different protection, and confusing them is the most common mistake in this subject.

Two different problems

Redundancy does not protect against mistakes. A replica copies a deletion as faithfully as it copies everything else. History does not protect against an outage if it is stored in the place that is down.

RPO and RTO

Two numbers define what you are aiming for (Google Cloud: disaster recovery planning guide):

• Recovery point objective, RPO. How much recent data you can afford to lose, measured as time. An RPO of one hour means the newest hour of changes may be gone.
• Recovery time objective, RTO. How long you can afford to be without the data.

Smaller numbers cost more and add complexity. A reporting table rebuilt nightly from raw files has a generous RPO, because it can be rebuilt. The raw files themselves do not.

BigQuery and mistakes

Time travel. BigQuery keeps earlier versions of every table for seven days by default. You can query a table as it was at any moment in that window, or restore it. The window is set per dataset, from two to seven days (Google Cloud: time travel and fail-safe).

-- The table as it was an hour ago
SELECT *
FROM `analytics.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

# Restore a table, even a deleted one, from an hour ago into a new table
bq cp analytics.orders@-3600000 analytics.orders_restored

Two limits matter. Only a whole table can be restored, not one partition, and the restored copy does not carry the original's partitioning (Google Cloud: access historical data).

Fail-safe. After time travel ends, deleted or replaced tables and deleted partitions are held for seven more days. You cannot query this data or restore it yourself. Recovery goes through Google's support, and after the period nothing can be recovered. Treat it as a last resort, not a plan.

Snapshots. A table snapshot is a read-only copy of a table at a moment, kept for as long as you choose. It costs little, because BigQuery stores only the bytes that differ from the base table (Google Cloud: table snapshots).

CREATE SNAPSHOT TABLE `backups.orders_2026_10_01`
CLONE `analytics.orders`
OPTIONS (expiration_timestamp = TIMESTAMP '2027-01-01 00:00:00 UTC');

That statement follows the documented form (Google Cloud: create table snapshots). Run on a schedule, it extends your reach beyond seven days. See scheduling queries in BigQuery.

BigQuery and outages

BigQuery writes every dataset to two zones in one region. The loss of a zone is not expected to lose data (Google Cloud: understand reliability).

A region is different, and here a common belief is wrong. Choosing the US or EU multi-region does not spread your data across regions. Google's documentation is explicit: a multi-region location does not provide cross-region replication, and the data sits in a single region within that geography. A hard regional failure could lose it, and BigQuery keeps no automatic copy elsewhere.

If that risk matters, there are two steps up:

• Cross-region dataset replication keeps a replica of a dataset in a second region, updated asynchronously (Google Cloud: cross-region dataset replication).
• Managed disaster recovery adds failover of compute and storage to that replica. It requires the Enterprise Plus edition. A soft failover waits for replication to finish. A hard failover does not wait, and data loss can occur (Google Cloud: managed disaster recovery).

Cloud Storage

Cloud Storage is designed for eleven nines of annual durability. Where the copies are depends on the bucket's location type. A regional bucket stores data in at least two zones. Dual-region and multi-region buckets store it in at least two separate geographic places, replicated asynchronously (Google Cloud: availability and durability). So a multi-region bucket is spread across regions, and a multi-region BigQuery dataset is not.

For mistakes, there is soft delete. It is on by default: a deleted or overwritten object can be restored for 7 days, and the period can be set from 7 to 90 days (Google Cloud: soft delete). Object Versioning keeps older versions of objects until you remove them. Google recommends soft delete for protection against accidental deletion and notes that versioning does not protect against deleting the bucket itself (Google Cloud: Object Versioning).

See Google Cloud Storage for location types and lifecycle rules.

Cloud SQL

Cloud SQL takes backups on demand and on a schedule. Automated backups also support point-in-time recovery, which restores the instance to a chosen moment instead of to the last backup. A backup can be used to create a new instance in another zone or region. If an instance is deleted, its backups are kept for four days (Google Cloud: Cloud SQL backups). See Cloud SQL vs Spanner vs Bigtable.

Rehearsing a restore

A backup that has never been restored is a hope. The script rehearses the whole sequence on a real database: the Invoice table of the Chinook sample database (Chinook database), in SQLite. It takes a backup, lets three new invoices arrive, runs a bad UPDATE, restores from the backup and compares checksums.

It is a local rehearsal. It is not BigQuery, Cloud Storage or Cloud SQL, and the three new invoices are invented.

import hashlib
import shutil
import sqlite3

shutil.copy("../../sqlds/Chinook_Sqlite.sqlite", "work.sqlite")   # work on a copy
db = sqlite3.connect("work.sqlite")
NEW_INVOICES = [(413, 1, "2024-01-15 09:10:00", 5.94),
                (414, 2, "2024-01-15 11:45:00", 8.91),
                (415, 3, "2024-01-15 14:20:00", 1.98)]


def state(label):
    rows, total = db.execute("SELECT COUNT(*), SUM(Total) FROM Invoice").fetchone()
    content = db.execute("SELECT InvoiceId, Total FROM Invoice ORDER BY InvoiceId").fetchall()
    checksum = hashlib.sha256(repr(content).encode()).hexdigest()[:10]
    print(f"{label:38} rows={rows}  total={total:>10.2f}  checksum={checksum}")
    return checksum


def add_invoices():
    db.executemany("INSERT INTO Invoice (InvoiceId, CustomerId, InvoiceDate, Total) "
                   "VALUES (?, ?, ?, ?)", NEW_INVOICES)
    db.commit()


at_backup = state("1. healthy table, backup taken")
backup = sqlite3.connect("backup.sqlite")
db.backup(backup)
backup.close()

add_invoices()
last_good = state("2. three invoices arrive afterwards")

db.execute("UPDATE Invoice SET Total = Total * 100")   # the WHERE clause was forgotten
db.commit()
state("3. after the bad UPDATE")

db.execute("ATTACH 'backup.sqlite' AS bak")
db.execute("DELETE FROM Invoice")
db.execute("INSERT INTO Invoice SELECT * FROM bak.Invoice")
db.commit()
restored = state("4. restored from the backup")
print(f"   matches the backup: {restored == at_backup}   matches the last good state: {restored == last_good}")

add_invoices()                                         # replayed from the source system's records
replayed = state("5. three invoices replayed")
print(f"   matches the last good state: {replayed == last_good}")

1. healthy table, backup taken         rows=412  total=   2328.60  checksum=aae3d90d7c
2. three invoices arrive afterwards    rows=415  total=   2345.43  checksum=44f4fcf195
3. after the bad UPDATE                rows=415  total= 234543.00  checksum=57c74a6df4
4. restored from the backup            rows=412  total=   2328.60  checksum=aae3d90d7c
   matches the backup: True   matches the last good state: False
5. three invoices replayed             rows=415  total=   2345.43  checksum=44f4fcf195
   matches the last good state: True

• The restore succeeded and still lost data. Step 4 matches the backup exactly, and has 412 rows. The table had 415 before the mistake. The three invoices written after the backup are gone.
• That gap is the RPO, made visible. Everything between the last backup and the incident is lost by a restore, however well the restore goes.
• Replaying from the source closed the gap. Step 5 reaches the last good state because the three invoices could be loaded again from the system that produced them.
• The checksum did the comparing. Row counts alone would not have caught step 3, where the count is right and every value is wrong.

This is the argument for keeping raw data. A pipeline that retains the files or events it was built from can replay them, and its real RPO becomes the age of the newest raw record, not the age of the last backup.

What to set up

1. Decide RPO and RTO per dataset, not once for everything. Raw data and reporting tables deserve different answers.
2. Leave time travel at seven days unless storage cost under physical billing gives a reason to shorten it.
3. Schedule snapshots for tables that cannot be rebuilt.
4. Keep raw files, with soft delete on and a retention period you chose.
5. Turn on automated backups and point-in-time recovery for operational databases.
6. Decide about a second region on purpose. If it is required, configure replication. If not, record that the risk was accepted.
7. Rehearse a restore twice a year, and time it. That measurement is your real RTO.
8. Restrict who can delete. See GCP IAM.

For how raw, staging and reporting tables make rebuilding possible, see building a data pipeline on GCP.

Common mistakes

• Assuming replication is a backup.
• Assuming a multi-region BigQuery dataset survives the loss of a region.
• Relying on fail-safe.
• Discovering on day eight that time travel has ended.
• Never restoring anything until the day it matters.
• Keeping backups in the project, and under the permissions, of the thing they protect.

Building pipelines that can be rerun and rebuilt is part of the Data Engineering course.

Sources

• Disaster recovery planning guide (Google Cloud Architecture Center) — Google Cloud
• Data retention with time travel and fail-safe (Google Cloud Documentation) — Google Cloud
• Access historical data (Google Cloud Documentation) — Google Cloud
• Introduction to table snapshots (Google Cloud Documentation) — Google Cloud
• Create table snapshots (Google Cloud Documentation) — Google Cloud
• Understand reliability (Google Cloud Documentation) — Google Cloud
• Cross-region dataset replication (Google Cloud Documentation) — Google Cloud
• Managed disaster recovery (Google Cloud Documentation) — Google Cloud
• Data availability and durability (Google Cloud Documentation) — Google Cloud
• Soft delete (Google Cloud Documentation) — Google Cloud
• Object Versioning (Google Cloud Documentation) — Google Cloud
• About Cloud SQL backups (Google Cloud Documentation) — Google Cloud
• Chinook Database (GitHub, lerocha) — Luis Rocha