BigQuery table snapshots and time travel: backup before migrations

TL;DR: Snapshot a table before schema migrations or backfills; time travel covers the last 7 days for oops recovery. Snapshots are cheap, restores are copies, and both need location awareness. Goal: never lose a table to a bad migration. Create before migrations: Workflow for a risky migration: 1.

The fix

1. 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).
2. Table snapshots: named, restorable point-in-time copies. Create before migrations: Workflow for a risky migration:
3. Snapshot the table.
4. Run the migration/backfill.
5. Validate row counts and spot-check data.
6. If bad: restore from the snapshot (copy job) or query the snapshot directly.
7. Delete the snapshot after the validation window closes, or snapshots accumulate cost. Traps:
8. 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.
9. Snapshot restores are COPY jobs: they bill and take time on huge tables. Plan the restore, do not assume instant.
10. Location: snapshots live in the dataset's location; cross-location copies have their own rules and costs.
11. 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.

When to use this

• This covers exactly what the title says: BigQuery table snapshots and time travel.
• You are setting this up for the first time, or auditing an existing setup.
• You want the key gotchas in one place before you start.

When not to use this

• You are doing a different workflow with BigQuery; these steps are specific to the title above.
• You need the full reference docs; this is the short path, not the manual.

Compatibility

• Not pinned to a specific version; follows current BigQuery behavior.

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.