Time travel

BigQuery keeps every table's history for up to seven days. The Preview tab has a picker that shows the table as it was an hour, a day or a week ago, and the same clause works in any query — which is how you get deleted rows back.

In the Preview tab

Open a table, switch to Preview, click TT. Pick a preset or Custom… for an exact date and time (the picker refuses future dates and anything beyond the table’s window). The grid reloads and a banner above it says Viewing historical data from the chosen timestamp; the row count in the footer changes with it. Choose Now to return to the current data.

Presets outside the table’s window are hidden. A dataset created with max_time_travel_hours = 48 shows only Now, 1 hour, 6 hours and 1 day; the window itself is on the dataset’s Details tab as Time travel window.

• Table, not a view or external table
• Timestamp within the dataset’s time-travel window (2–7 days, default 7)
• Table still exists — a dropped table is restored with bq cp, not previewed

Time-travel previews are as free as normal ones: the rows are read through the table-data API with a snapshot decorator (table@timestamp), not a query. Nothing is billed until you write the clause in SQL yourself.

In SQL

The clause is FOR SYSTEM_TIME AS OF after the table name. Absolute:

SELECT *
FROM `ace-analytics.warehouse.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-08-27 14:30:00 UTC'
WHERE order_id = 88213

Relative:

SELECT COUNT(*) AS rows_yesterday
FROM `ace-analytics.warehouse.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

Completions offer the whole FOR SYSTEM_TIME AS OF phrase after a table reference. Each table in a query gets its own clause; there is no snapshot of the whole dataset, so two tables joined at “1 hour ago” are each read at their own 1-hour-ago version.

Recovering rows

The usual reason to reach for it. Compare, then insert back:

INSERT INTO `ace-analytics.warehouse.orders`
SELECT *
FROM `ace-analytics.warehouse.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR)
WHERE order_id NOT IN (SELECT order_id FROM `ace-analytics.warehouse.orders`)

To bring back a whole state — after a bad TRUNCATE or UPDATE — clone it instead of copying. Creating a clone is free; you pay storage only for data that diverges from the source afterwards:

CREATE TABLE `ace-analytics.warehouse.orders_before_truncate`
CLONE `ace-analytics.warehouse.orders`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

The table’s SQL Templates include the CREATE TABLE … CLONE statement, with the FOR SYSTEM_TIME AS OF line commented out for you to uncomment; there is no recover-rows INSERT template. See DDL operations.

Elsewhere in the app

• Storage tab, Storage Breakdown — Time Travel (N days) is the physical storage the history occupies; it is billed under the physical billing model.
• Compare tab — each side has a Time Travel: field, so you can diff a table against itself as of yesterday.