Time travel and fail-safe in BigQuery

BigQuery keeps changed and deleted data for 2 to 7 days (7 by default) and lets you query it with FOR SYSTEM_TIME AS OF. How to restore deleted rows and dropped tables, what fail-safe is, and when time travel adds to the storage bill.

BigQuery keeps the old version of any table you change or delete from, for a window of 7 days by default. Inside that window you can query the table as it was with FOR SYSTEM_TIME AS OF, copy it into a new table, or restore a table you dropped. The window is set per dataset and can be 2 to 7 days. After it ends, deleted data sits in fail-safe for 7 more days, where only Google Cloud support can get it back.

Query a table as it was

Add FOR SYSTEM_TIME AS OF after the table name. It takes a constant timestamp expression:

SELECT *
FROM mydataset.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

This reads the table as it stood one hour ago, whatever happened to it since: deleted rows are back, updated values are the old ones. There is no limit on table size. The bytes it processes are the bytes of the columns you select, as in a normal query, so a read of the past is billed like a read of the present:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

That is the same 1.3 MB as SELECT word without the clause. The estimate comes from a dry run, so nothing was billed.

The clause is not a log. It gives you the table at one instant, not the changes between two instants.

What the window is

The window is a number of hours, from 48 to 168 in steps of 24. The dataset option is max_time_travel_hours, and anything else is refused:

ALTER SCHEMA sales_archive
SET OPTIONS (max_time_travel_hours = 24)

The error comes back before BigQuery looks for the dataset, so the name doesn’t matter. To change an existing dataset:

ALTER SCHEMA mydataset SET OPTIONS (max_time_travel_hours = 48);

A table deleted before you change the window keeps the window it had when it was deleted. Raising 2 days to 7 does not make older drops recoverable for 7 days, and lowering 7 to 3 does not shorten them.

Errors at the edges of the window

A timestamp in the future is rejected:

SELECT word
FROM `bigquery-public-data.samples.shakespeare`
  FOR SYSTEM_TIME AS OF TIMESTAMP '2999-01-01 00:00:00+00'

A timestamp before the window, or before the table existed, fails with a message that tells you the earliest readable time in milliseconds since the Unix epoch. Asking orders, a table created two hours earlier, for the state eight days ago returned:

Invalid time travel timestamp 1790629914027 for table
myproject:mydataset.orders@1790629914027.
Cannot read before 1791321090688

The number after “Cannot read before” is the later of the table’s creation time and the start of the window. For an old table with the default window it is 7 days before now. For a new table it is the creation time. So the error doubles as a way to read the real window of a table.

Restore deleted rows

Time travel reads the old rows. Putting them back takes a second step, and the obvious way fails. Say someone ran DELETE FROM orders WHERE order_id = 3. Inserting from the old version into the same table is refused:

INSERT INTO mydataset.orders
SELECT * FROM mydataset.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP_MILLIS(1791321214126)
WHERE order_id = 3;

Table 'orders' is referenced with multiple snapshot read timestamps.
For example, if 'FOR SYSTEM_TIME AS OF' expressions are used on the table,
they should use the same TIMESTAMP value. Using the table as DML destination
table references the table at query start time.

The destination of a DML statement is read at the time the statement starts, and the historical read is a second timestamp on the same table. Put the old rows in a temporary table first, in a script:

CREATE TEMP TABLE lost AS
SELECT * FROM mydataset.orders
  FOR SYSTEM_TIME AS OF TIMESTAMP_MILLIS(1791321214126)
WHERE order_id = 3;

INSERT INTO mydataset.orders SELECT * FROM lost;

This worked in our test: afterwards orders had rows 1, 2 and 3 again. Use a fixed timestamp from before the delete, and do it before the window ends. These statements change real tables, so they are shown here and not run.

Restore a dropped table

You can’t query a table that has been dropped, even with a timestamp from before the drop:

Not found: Table myproject:mydataset.doomed@1791321250357
was not found in location US

CREATE TABLE ... CLONE ... FOR SYSTEM_TIME AS OF fails with the same error for a dropped table. What works is a copy job with the time decorator, which takes milliseconds since the epoch, or a negative offset in milliseconds from now:

bq cp mydataset.doomed@1791321250357 mydataset.doomed_restored
bq cp mydataset.doomed@-3600000      mydataset.doomed_restored

@0 means the oldest data available. The restore is a copy of the data at that moment. Table metadata such as the partitioning setup does not come back, so recreate it yourself, and you can only restore the whole table, not one partition. The same copy works for a table that expired.

Schema changes are in the past too

Time travel returns the old schema along with the old data. In our test a column was dropped, and a read from before the drop still shows it:

ALTER TABLE mydataset.sc DROP COLUMN qty;

SELECT * FROM mydataset.sc
  FOR SYSTEM_TIME AS OF TIMESTAMP_MILLIS(1791321328513);
-- id, note, qty   with qty = 5

The same read still returned qty = 5 after a new qty column had been added back. See Schema changes.

Fail-safe

When the window closes, deleted or replaced data moves to fail-safe for 7 days. The data covered is what a table loses by deletion, by being replaced (CREATE OR REPLACE TABLE, or a load job with WRITE_TRUNCATE), or by a partition being deleted. You can’t query fail-safe data and you can’t change its length. To get it back you contact Cloud Customer Care, and it is recovered at table level, as of the moment the table was deleted.

So a table you dropped on day 0 with a 7-day window is yours to restore until day 7, then Google’s until day 14.

What it costs

A new table has no time travel cost. The cost starts when data changes: updated and deleted bytes stay for the window and then for the fail-safe period. How that is billed depends on the dataset’s storage billing model:

• Logical billing: time travel and fail-safe bytes are included in the base rate, and you don’t pay for them separately.
• Physical billing: both are billed at the active physical rate, so a table that is rewritten often can carry up to 14 days of old versions.

That is why shortening the window only saves money on physical billing. The comparison with real numbers is in Storage pricing. On physical billing, TIME_TRAVEL_PHYSICAL_BYTES and FAIL_SAFE_PHYSICAL_BYTES in INFORMATION_SCHEMA.TABLE_STORAGE show how much a table holds.

Changing one cell can send more than one cell to time travel. BigQuery versions storage blocks, and a block holds a column’s values for many rows, so the retained bytes are often larger than the change.

Where time travel doesn’t work

• External tables. Iceberg external tables are an exception: FOR SYSTEM_TIME AS OF reads snapshots kept in the Iceberg metadata.
• Temporary tables: cached query results, session tables and multi-statement (script) temporary tables.
• Tables with row-level access policies, now or in the past, unless you hold the bigquery.rowAccessPolicies.overrideTimeTravelRestrictions permission. The roles roles/bigquery.admin and roles/bigquery.studioAdmin have it.

To keep data longer than 7 days, use table snapshots. To delete data on a schedule, see Table expiration.

In Querylab.io

The Preview tab of a table has a TT button that shows the table as it was an hour to seven days ago, or at a time you enter, with no clause to write and no query billed. Presets outside the dataset’s time travel window are hidden (Time travel). The Compare tab has a Time Travel field on each side, so you can diff a table against itself as of yesterday (Data compare).

In the editor, FOR SYSTEM_TIME AS OF is parsed and gets a byte estimate, so you can check a restore query before running it. A table’s CLONE TABLE template includes the FOR SYSTEM_TIME AS OF line commented out (DDL operations).