BigQuery table snapshots and time travel: backup before migrations

Goal: never lose a table to a bad migration.

Two tools:

• Time travel: query any table as of up to 7 days ago (configurable window). Free for oops recovery: SELECT * FROM [T] FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR).
• Table snapshots: named, restorable point-in-time copies. Create before migrations:

bq mk --snapshot --snapshot_time [TIMESTAMP] [PROJECT]:[DATASET].[TABLE] [PROJECT]:[DATASET].[SNAPSHOT]

Workflow for a risky migration:

1. Snapshot the table.
2. Run the migration/backfill.
3. Validate row counts and spot-check data.
4. If bad: restore from the snapshot (copy job) or query the snapshot directly.
5. Delete the snapshot after the validation window closes, or snapshots accumulate cost.

Traps:

• Time travel window is 7 days by default and configurable down, not up, freely. Do not rely on it for month-old recovery; snapshot for that.
• Snapshot restores are COPY jobs: they bill and take time on huge tables. Plan the restore, do not assume instant.
• Location: snapshots live in the dataset's location; cross-location copies have their own rules and costs.
• Snapshots of partitioned tables capture everything; restoring a single partition from a snapshot needs a filtered copy query, not a whole-table restore.

Cost note: snapshots bill at table storage rates. They are cheap insurance, not free; clean them up.

Verify: after snapshotting, query the snapshot and confirm row count matches the source at snapshot time.

Verified 2026-09-26 on the BigQuery table snapshots docs: snapshot creation, time travel window, restore-as-copy semantics. Location and billing behavior are documented.