BigQuery Disaster Recovery: Cross-Region Dataset Replication, Failover Reservations, and Drills

Time travel and table snapshots protect you from a bad MERGE. They do not protect you from losing a region. If your warehouse sits in us-east4 and that region has a bad day, time travel is unreachable along with everything else. Regional resilience in BigQuery is a different set of features, and most teams only discover which ones they are missing during an incident review.

This tutorial covers the three layers that matter: cross-region dataset replication, managed disaster recovery (failover reservations), and the operational plumbing — pipelines, routing, and drills — that decides whether any of it actually works.

First: write down your RPO and RTO

Every design choice below follows from two numbers.

• RPO (recovery point objective) — how much recently written data you can afford to lose. BigQuery's asynchronous replication gives you minutes, not zero.
• RTO (recovery time objective) — how long you can be down before failover completes and queries resume.

Then classify your datasets. In practice most estates look like this:

Tier	Example	Target	Mechanism
1 — revenue-critical	Billing marts, regulatory reporting	RPO minutes, RTO < 1 hour	Managed DR with failover reservation
2 — business-important	Analyst marts, executive dashboards	RPO hours, RTO < 1 day	Cross-region dataset replication, read-only
3 — reproducible	Staging, raw landing, dev	RPO = last load	Re-run the pipeline from source

Paying tier-1 money for tier-3 data is the single most common mistake here. Raw landing zones that you can re-ingest from Cloud Storage or a CDC stream do not need a replica.

Layer 1: cross-region dataset replication

Dataset replication creates an asynchronous, read-only secondary copy of a dataset in another region. Writes continue to go to the primary; the replica lags by minutes.

-- Add a replica of an existing dataset in a second region
ALTER SCHEMA `acme-prod.sales_mart`
ADD REPLICA `us-west1`
OPTIONS (location = 'us-west1');

Watch the replica catch up before you rely on it:

SELECT
  catalog_name,
  schema_name,
  replication_time,                -- how current the replica is
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), replication_time, MINUTE) AS lag_minutes,
  creation_complete
FROM `region-us-west1`.INFORMATION_SCHEMA.SCHEMATA_REPLICAS
WHERE schema_name = 'sales_mart';

Things to know before you roll this out widely:

• The initial seed copies the whole dataset across regions and is billed as cross-region replication data transfer; steady-state cost is the delta plus storage for the second copy. Budget for roughly two copies of storage plus egress on the change rate.
• The replica is read-only. Jobs that try to write to it fail; this is a feature, not something to work around.
• You can promote a replica to primary, which makes the old primary a replica. That is the manual failover path.
• Not everything replicates. Check current coverage for external and BigLake tables, materialized views, search and vector indexes, and routines before you assume a replica is complete. Re-creating indexes and routines in the secondary region is usually part of the runbook.

Promotion, when you need it:

-- Make the secondary the primary (manual failover)
ALTER SCHEMA `acme-prod.sales_mart`
SET OPTIONS (primary_replica = 'us-west1');

-- Later, once the original region is healthy, fail back the same way
ALTER SCHEMA `acme-prod.sales_mart`
SET OPTIONS (primary_replica = 'us-east4');

Because replication is asynchronous, promotion can lose anything written after the last replication_time. That is your actual RPO — measure it in normal operation, not during the incident.

Layer 2: managed disaster recovery and failover reservations

Replication protects data. It does not protect compute: if you fail over your datasets but have no slots in the secondary region, your queries queue behind an on-demand cold start. BigQuery's managed disaster recovery, available on Enterprise Plus, pairs replicated data with a failover reservation so both the storage and the capacity move together.

-- A failover reservation in the primary region, with a secondary
CREATE RESERVATION `admin-project.region-us-east4.prod-dr`
OPTIONS (
  edition = 'ENTERPRISE_PLUS',
  slot_capacity = 500,
  secondary_location = 'us-west1'
);

-- Trigger a failover: the secondary becomes primary
ALTER RESERVATION `admin-project.region-us-east4.prod-dr`
SET OPTIONS (is_primary = FALSE);

Key properties:

• Compute capacity is reserved in both regions; you are billed for the standby capacity. This is the premium you pay for a low RTO.
• Failover is a control-plane operation measured in minutes, not a data copy, because the data is already replicated.
• Managed DR covers the datasets associated with the reservation; anything outside it still needs its own plan.

If your RTO is "a few hours" and you are comfortable creating a reservation in the secondary region by hand during an incident, plain dataset replication plus a documented Terraform apply is dramatically cheaper. Enterprise Plus managed DR buys you minutes, and only minutes.

Layer 3: the parts nobody tests

Failover fails in the plumbing far more often than in BigQuery.

Pipelines must follow the data. Scheduled queries, Dataform releases, Datastream destinations, and the Storage Write API all target a specific region. Parameterise the region:

# Resolve the active region at run time rather than hard-coding it
from google.cloud import bigquery

ACTIVE_REGION = get_runtime_config("bq_active_region")  # e.g. from Secret Manager
client = bigquery.Client(location=ACTIVE_REGION)

Queries must not pin a region by accident. Fully-qualified region-us-east4.INFORMATION_SCHEMA references, hard-coded dataset locations in dbt profiles, and BI tool connections all need a single switch. Keep that switch in one place — a Terraform variable or a config table — and have every consumer read it.

Keep a region-neutral export. Daily exports of tier-1 tables to a dual-region Cloud Storage bucket, in Parquet or as Iceberg tables, give you a floor that does not depend on any BigQuery feature working as documented:

EXPORT DATA OPTIONS (
  uri = 'gs://acme-dr-dual-region/sales_mart/orders/*.parquet',
  format = 'PARQUET',
  overwrite = TRUE
) AS
SELECT * FROM `acme-prod.sales_mart.orders`
WHERE order_date >= CURRENT_DATE() - 400;

IAM, CMEK, and quotas must exist on both sides. Customer-managed keys are regional: a replica in us-west1 needs a key ring in us-west1, and service accounts need bindings there. Slot quotas in the secondary region are separate quotas. All three are discovered the hard way during a real failover.

Run the drill

A DR design you have never exercised is a hypothesis. Schedule a quarterly game day:

1. Announce a window and freeze production writes to one tier-2 dataset.
2. Record replication_time and compute observed RPO.
3. Promote the replica; start a stopwatch.
4. Flip the config switch and re-point pipelines and the BI tool.
5. Run a fixed smoke-test query set and compare row counts and checksums against the pre-failover baseline.
6. Stop the stopwatch — that is your real RTO — then fail back and write up what broke.

Most first drills surface the same three things: a routine that did not replicate, a scheduled query with a hard-coded region, and a service account missing a binding in the secondary project. Each one is cheap to fix in a drill and expensive to find at 3 a.m.

Where to start this week

Classify your datasets into the three tiers, add a replica for one tier-2 dataset, measure the lag for a fortnight, and run a single drill. That is enough to turn "we think we're covered" into a number you can put in front of an auditor.

If you want help sizing managed DR against plain replication, or building the failover runbook and Terraform to go with it, our data architecture consulting and maintenance and support teams do this regularly — get in touch.