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.