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.
SELECT ... FROM table FOR SYSTEM_TIME AS OF timestampreads the table as it was at that moment. Deleted rows are back and changed values are the old ones. The timestamp is a constant expression, and a script variable is one.- Save the time before the risky statement:
DECLARE before TIMESTAMP DEFAULT CURRENT_TIMESTAMP();at the top of a script. A time taken after the statement reads the table with the mistake in it. - BigQuery refuses a time in the future, a time before the table existed, and a time older than the time travel window. The window is 7 days unless the dataset sets a shorter one, from 2 to 6 days.
- A statement reads a table at one moment only. A read at two different moments, or with and without
FOR SYSTEM_TIME AS OF, is refused. The table that anINSERT,UPDATE,DELETEorMERGEwrites counts as read at the moment the statement starts, so a read of that same table in the past has to land in a separate table first. - A temporary table lives in a hidden dataset that belongs to the script that made it. A stored table lives in a dataset of its own.
CREATE SNAPSHOT TABLE name CLONE sourcekeeps a read-only copy of the table as it is now. AddFOR SYSTEM_TIME AS OF timeafter the source to snapshot the table as it was. You read a snapshot like any table, andINSERT,UPDATEandDELETEon it are refused.CREATE OR REPLACE TABLE t AS SELECT ... AS OFbuilds a new table from a query, so a table with partitioning, clustering or a description loses it, and BigQuery refuses the replace of a partitioned table.CREATE OR REPLACE TABLE t CLONE t FOR SYSTEM_TIME AS OF timekeeps them.- A snapshot costs nothing when you take it. BigQuery shares the storage with the table and bills only the bytes that later differ from it. A snapshot stays until you drop it or its expiry passes, so it outlasts the time travel window.
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.