BQBrains | Time Travel, Snapshots, and Table Clones: A BigQuery Recovery Playbook

Someone runs DELETE FROM orders WHERE 1=1 against production instead of the dev copy. Someone else drops a dataset that a Dataform pipeline was about to rebuild. Nothing in BigQuery pops up an "are you sure?" dialog, and there is no Recycle Bin.

What BigQuery does have is four distinct recovery layers, each with different retention, different cost, and different ways of failing you at the worst possible moment. Most teams only know about one of them. This tutorial walks all four, and finishes with the drill you should run this quarter.

The four layers at a glance

Layer	Covers	Retention	You pay for	Recovers a dropped table?
Time travel	Bad DML, bad DDL, accidental drops	2–7 days (configurable)	Storage of changed data	Yes, if you know the table name
Fail-safe	Data past the time-travel window	7 extra days	Storage (billed)	Only via Cloud Customer Care
Table snapshots	Point-in-time copies you chose to take	Until you set expiry / delete	Only bytes that diverge	Yes
Clones + cross-region copies	Environment rebuilds, regional loss	As configured	Only bytes that diverge / full copy	Yes

Time travel and fail-safe are automatic. Snapshots and clones are yours to schedule. Automatic-only is not a backup strategy.

Layer 1: time travel

Every standard BigQuery table keeps a change history, seven days by default. You query it with FOR SYSTEM_TIME AS OF:

-- What did the table look like 90 minutes ago?
SELECT *
FROM `acme-prod.sales.orders`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 MINUTE);

To actually restore, write the historical snapshot back over the damaged table:

-- Non-destructive first: stage the good version alongside
CREATE OR REPLACE TABLE `acme-prod.sales.orders_restore_20260115` AS
SELECT *
FROM `acme-prod.sales.orders`
  FOR SYSTEM_TIME AS OF TIMESTAMP '2026-01-15 08:45:00 UTC';

-- Eyeball the row counts, then swap
SELECT
  (SELECT COUNT(*) FROM `acme-prod.sales.orders`)                    AS current_rows,
  (SELECT COUNT(*) FROM `acme-prod.sales.orders_restore_20260115`)   AS restored_rows;

If the restore looks right, CREATE OR REPLACE TABLE ... AS SELECT * FROM the_restore_table, or use the copy job with --replace from the CLI.

Finding the exact moment things went wrong

Don't guess the timestamp. INFORMATION_SCHEMA.JOBS knows:

SELECT
  creation_time,
  user_email,
  statement_type,
  destination_table.table_id,
  query
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)
  AND statement_type IN ('DELETE','UPDATE','MERGE','TRUNCATE_TABLE',
                         'CREATE_TABLE_AS_SELECT','DROP_TABLE')
  AND destination_table.dataset_id = 'sales'
ORDER BY creation_time DESC;

Take the creation_time of the offending job and time-travel to one second before it.

Recovering a dropped table

If the table itself is gone, the FOR SYSTEM_TIME AS OF clause still resolves — but only if no new table has been created with the same name (that resets the history). Use a copy job with a snapshot decorator:

# @<epoch-millis> selects the table as of that instant
bq cp \
  'acme-prod:sales.orders@1768467900000' \
  acme-prod:sales.orders_recovered

The golden rule after an accidental drop: do not recreate the table under the same name until you have recovered it.

Tuning the window

ALTER SCHEMA `acme-prod.sales`
SET OPTIONS (max_time_travel_hours = 168);  -- 7 days, the maximum

Valid values are 48 to 168 hours in 24-hour multiples. Shortening the window to 48 hours trims time-travel storage on high-churn tables — a real saving on tables rewritten hourly — but it also shortens your recovery runway. Shorten it on staging datasets, not on the ones your finance team depends on.

Layer 2: fail-safe

After time travel expires, BigQuery keeps deleted data for a further seven days in fail-safe storage. You cannot query it, you cannot restore it yourself, and you are billed for it. Recovery requires opening a case with Google Cloud Customer Care, and it is best-effort with no restore-time SLA.

Treat fail-safe as a fire extinguisher behind glass: reassuring, but never part of a plan. If your recovery point objective is measured in days, you need layer three.

Layer 3: table snapshots

A table snapshot is a read-only, point-in-time record of a table. It is cheap because it stores only the bytes that later diverge from the base table.

CREATE SNAPSHOT TABLE `acme-prod.backups.orders_20260115`
CLONE `acme-prod.sales.orders`
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 35 DAY),
  description = 'Nightly snapshot of sales.orders'
);

Snapshots can be taken up to the edge of the time-travel window, so a job that failed overnight can still be snapshotted at 9 a.m. from the state it had at midnight:

CREATE SNAPSHOT TABLE `acme-prod.backups.orders_premerge`
CLONE `acme-prod.sales.orders`
  FOR SYSTEM_TIME AS OF TIMESTAMP '2026-01-15 00:00:00 UTC';

Restoring is a plain copy:

CREATE OR REPLACE TABLE `acme-prod.sales.orders`
CLONE `acme-prod.backups.orders_20260115`;

Automating nightly snapshots

Wrap it in a stored procedure and hang it off a scheduled query:

CREATE OR REPLACE PROCEDURE `acme-prod.ops.snapshot_critical_tables`()
BEGIN
  DECLARE tables ARRAY<STRING> DEFAULT ['orders','customers','invoice_lines'];
  DECLARE i INT64 DEFAULT 0;
  DECLARE suffix STRING DEFAULT FORMAT_DATE('%Y%m%d', CURRENT_DATE());

  WHILE i < ARRAY_LENGTH(tables) DO
    EXECUTE IMMEDIATE FORMAT("""
      CREATE SNAPSHOT TABLE `acme-prod.backups.%s_%s`
      CLONE `acme-prod.sales.%s`
      OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 35 DAY))
    """, tables[OFFSET(i)], suffix, tables[OFFSET(i)]);
    SET i = i + 1;
  END WHILE;
END;

Schedule it daily. Set the expiration so old snapshots retire themselves — an unmanaged snapshot dataset is a storage bill nobody reads.

Layer 4: zero-copy clones for dev environments

A table clone is a writable copy that initially shares storage with its source. You are billed only for the delta you write. This turns "give me a realistic dev environment" from an overnight copy job into a two-second statement:

CREATE TABLE `acme-dev.sales_sandbox.orders`
CLONE `acme-prod.sales.orders`;

Useful patterns:

Pre-migration safety net. Clone every table in a dataset before a schema migration, run the migration, and diff. Rollback is a clone in the other direction.
Ephemeral CI datasets. Clone the conformed layer into ci_<build_id>, run the transformation suite and assertions against it, drop the dataset when the build finishes.
Reproducing a bug. Clone FOR SYSTEM_TIME AS OF the moment the incident started, and debug against exactly the data the pipeline saw.

Caveats worth knowing before you standardise on clones: the clone and its source must be in the same region; the source must be a standard table (not a view or external table); and dropping the source does not break the clone, but the shared bytes then become billable to the clone.

Dataset- and project-level recovery

Undeleting a dataset: bq mk --transfer_config is not it — use UNDROP SCHEMA \acme-prod.sales`;`, valid within the time-travel window, provided no dataset of the same name has been recreated.
Cross-region copies: for genuine disaster recovery, schedule a dataset copy into a second region with the BigQuery Data Transfer Service. Time travel and snapshots live in the same region as the data; a regional outage takes them with it.
Managed disaster recovery: Enterprise Plus offers cross-region replication with managed failover for reservations and datasets. If your recovery time objective is measured in minutes, price that against the cost of an outage.

What this costs

Time-travel and fail-safe bytes are billed at the same rate as the storage they shadow, and they show up as TIME_TRAVEL_PHYSICAL_BYTES and FAIL_SAFE_PHYSICAL_BYTES when a dataset uses physical storage billing:

SELECT
  table_schema,
  table_name,
  ROUND(SUM(active_physical_bytes)      / POW(1024,3), 2) AS active_gib,
  ROUND(SUM(time_travel_physical_bytes) / POW(1024,3), 2) AS time_travel_gib,
  ROUND(SUM(fail_safe_physical_bytes)   / POW(1024,3), 2) AS fail_safe_gib
FROM `acme-prod.sales.INFORMATION_SCHEMA.TABLE_STORAGE`
GROUP BY table_schema, table_name
ORDER BY time_travel_gib DESC
LIMIT 20;

If a handful of tables dominate time_travel_gib, they are almost always tables being fully rewritten on every run. Two fixes, in order of preference: switch the pipeline to incremental MERGE on a partitioned table so only touched partitions change, or drop max_time_travel_hours to 48 on that dataset alone.

The drill

A backup you have never restored is a hypothesis. Book 45 minutes this quarter and run this end to end:

Pick a real production table and clone it to a scratch dataset. Work only on the clone.
Break it: DELETE a third of the rows. Note the wall-clock time.
Find the damaging job in INFORMATION_SCHEMA.JOBS without being told the timestamp.
Restore via time travel to one second before the delete. Verify row counts and a checksum on a numeric column.
Repeat from last night's snapshot instead of time travel, and record how long each path took.
Write down the two numbers: how long it took to detect, and how long it took to restore. Those are your real RTO figures — not the ones in the policy document.

Most teams discover something uncomfortable in step 3: nobody had the IAM permission to read INFORMATION_SCHEMA.JOBS across the project, or the snapshot dataset expired last month, or the critical table was never in the snapshot list. Far better to find out during a drill.

A minimum viable backup policy

Time travel at 7 days on every dataset containing anything a human would panic about losing.
Nightly snapshots of the conformed and serving layers, 35-day expiry, in a separate dataset with restricted write access.
Weekly cross-region dataset copy for anything with a regulatory retention requirement.
Clones, never manual copies, for dev and CI environments.
A restore drill on the calendar, once a quarter, owned by a named person.

If you'd like a second pair of eyes on your BigQuery recovery posture — or you are in the middle of an incident right now — get in touch. We do this work with clients across regulated and high-growth environments, and the first conversation is a short one.