Restoring BigQuery Data using time travel and snapshots
gcpBigQuery has a time travel feature that allows you to access data from any point in time within the past 7 days by default.
SELECT *
FROM `sambaiztest.test_dataset.test_table`
FOR SYSTEM_TIME AS OF "2024-11-22 22:41:00+09:00"
This allows you to restore data even if records have been overwritten or deleted.
You can also save snapshots and restore them by overwriting. You can hold backups over 7 days ago with low storage costs since only the differences are stored.
CREATE SNAPSHOT TABLE `sambaiztest.test_dataset.test_table`
CLONE `sambaiztest.test_dataset.test_table_snapshot`
SELECT *
FROM sambaiztest.test_dataset.INFORMATION_SCHEMA.TABLE_SNAPSHOTS
WHERE base_table_name = 'test_table'
CREATE OR REPLACE TABLE `sambaiztest.test_dataset.test_table`
CLONE `sambaiztest.test_dataset.test_table_snapshot`
Even if the table itself has been deleted, you can restore it by specifying time using the bq cp command.
$ bq cp test_dataset.test_table@-3600000 test_dataset.test_table_restored