Undo a mistake: time travel and snapshots

A transaction undoes a change before it is committed. After the commit, a wrong DELETE or UPDATE looks final, but BigQuery still holds the old state of a table for a few days.

How it works

FOR SYSTEM_TIME AS OF reads a table as it stood at one moment. A snapshot is a copy of a table at one moment that you keep on purpose.

Common mistake

Time travel ends when its window ends. Take a snapshot before the change, because one taken after holds the table as the change left it.

Worked example

DECLARE before TIMESTAMP;
CREATE OR REPLACE TABLE learn.shop.example_prices AS
SELECT 1 AS item_id, 'mug' AS item, 8 AS price
UNION ALL SELECT 2, 'plate', 12;
SET before = CURRENT_TIMESTAMP();
UPDATE learn.shop.example_prices SET price = price * 10 WHERE TRUE;
SELECT item, price
FROM learn.shop.example_prices FOR SYSTEM_TIME AS OF before
ORDER BY item_id;

The script saves the time after it makes the table, then multiplies every price by 10. The read after FOR SYSTEM_TIME AS OF before shows the prices as they were: 8 and 12.

A plain read of example_prices would show 80 and 120. The table now holds the new prices, and the old ones are in its past.

All BigQuery lessons