Dataset replication and disaster recovery in BigQuery

A BigQuery multi-region keeps your data in one region and gives no protection from a regional outage. Cross-region replication keeps a read-only copy in a second region, and managed disaster recovery (Enterprise Plus) adds failover. DML on replicated tables can multiply transfer costs.

Every BigQuery table is stored in two zones of its region, written synchronously, so a single zone failing loses nothing. A whole region going down is a different matter, and a common assumption is wrong here: a multi-region such as US or EU does not replicate your data across regions. Its data lives in one region inside that area. Cross-region dataset replication keeps a read-only copy in a second region. To keep writing and running queries through a regional outage, you need managed disaster recovery.

Cross-region dataset replication

Replication keeps a second copy of a whole dataset in another region or multi-region:

CREATE SCHEMA my_dataset OPTIONS (location = 'us-central1');

ALTER SCHEMA my_dataset
ADD REPLICA `my_replica`
OPTIONS (location = 'us-east4');

• The original is the primary replica and the only writable one. The new one is the secondary replica: read-only, updated asynchronously from the primary.
• The first copy takes time; queries against the primary keep working meanwhile.
• The secondary can be queried with slots in its own region, or on demand.
• INFORMATION_SCHEMA.SCHEMATA_REPLICAS shows each replica’s state and replication_time, how current it is.

To move the primary, for example for a planned migration, promote the secondary:

ALTER SCHEMA my_dataset SET OPTIONS (primary_replica = 'us-east4');

Promotion needs the primary region to be up. During a total outage of the primary region you can’t promote the secondary: it keeps serving reads, with data as of the last sync, and accepts no writes until the region is back. Google says plain replication is not meant as a disaster recovery plan for that case. Replication is also asynchronous, so writes made just before an outage may not have reached the secondary yet. Streaming writes and CDC upserts are replicated best-effort and can lag well behind batch writes.

What replication costs

• Storage twice. The secondary replica is billed as storage in its region. Tables in long-term storage stay long-term in the replica.
• Data transfer. The bytes copied to the secondary are billed per physical GiB, under BigQuery’s network egress replication SKUs. Replicating between a multi-region and the region it is colocated with (US and us-central1, EU and europe-west4) is free.
• DML rewrites whole files. BigQuery storage blocks are immutable, so an UPDATE, DELETE or MERGE rewrites every file it touches, and replication then copies the whole new file. Google’s example: updating 100 MB inside a 1 GB file replicates 1 GB. Frequent DML on large, unpartitioned tables can multiply the transfer bill. Keep DML-heavy tables in a separate dataset that isn’t replicated, or partition them so DML touches fewer files. See INSERT, UPDATE and DELETE.

Replication doesn’t use your slots.

Managed disaster recovery

Replication protects the data; you would still need slots in the other region and a way to redirect writes. Managed disaster recovery bundles both. It requires the Enterprise Plus edition: you create a failover reservation in the primary region, standby capacity in the paired region is included, and you attach replicated datasets to it.

In an outage you fail over the reservation, and its datasets and slots move to the secondary region together:

• A soft failover waits until every committed change has been replicated, so nothing is lost. Both regions must be available, so it is for planned switches and partial problems.
• A hard failover promotes the secondary immediately, even if the primary is down. Changes not yet replicated are lost, including recent access grants and authorized views. Jobs that committed after the replica’s replication_time must be rerun.

Once a dataset is attached to a failover reservation, only Enterprise Plus reservations can write to it, and you can no longer promote it with the plain replication command above. Any capacity model can still read it. Turbo replication, a faster replication mode, is available only with managed disaster recovery.

What else to plan for

• Things that must sit next to the data. Views, materialized views and plain external tables have to be in the same location as the data they read. A Cloud Storage bucket behind an external table must be in the replica’s region for queries there to work.
• Access controls. Plan to reapply dataset-level permissions after a hard failover, for example with Terraform.
• Within-region recovery from mistakes (a bad DELETE, a dropped table) is a different problem: use time travel and snapshots, which replication doesn’t replace, since a mistake on the primary is replicated too.

See Locations for how regions and multi-regions work.