BigQuery time travel: FOR SYSTEM_TIME AS OF, the window and examples
BigQuery time travel lets you query a table as it was at any moment in the last seven days, with FOR SYSTEM_TIME AS OF. This guide shows the syntax on a live table, how to set the window from two to seven days, how to compare two versions, how to save a past state, what time travel costs under each storage billing model, and the four errors it produces.
BigQuery time travel lets you query a table as it was at an earlier moment, up to seven days back by default. Add FOR SYSTEM_TIME AS OF and a timestamp after the table name:
SELECT COUNT(*) AS row_count, MAX(data_date) AS latest_day
FROM `myproject.searchconsole.searchdata_site_impression`
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-10-14 00:00:00+00'
WHERE data_date >= '2026-10-01';On a daily-loaded Search Console export in our project, on 16 October 2026:
| Version | row_count | latest_day |
|---|---|---|
| as of 14 October, 00:00 UTC | 204,468 | 2026-10-11 |
| current | 245,548 | 2026-10-13 |
The two loads that arrived in between are visible as the difference. Nothing had to be set up: time travel is on for every table.
Note: Originally published in April 2025. Rewritten in October 2026, and it now also covers what the separate page of time travel examples did. Every result and error below was produced in BigQuery on 16 October 2026; statements that change something were validated by dry runs, which change nothing.
The syntax
| Form | Reads the table as it was |
|---|---|
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-10-14 00:00:00+00' |
at that moment |
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY) |
two days ago |
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR) |
an hour ago |
The clause goes right after the table name, before WHERE. Google lists where it is not allowed: on a CTE defined in WITH, on UNNEST, and in the source of a table function. The timestamp must be a constant expression, so no subqueries or UDFs. Write it with a time zone, +00 for UTC: Google says the default time zone here is America/Los_Angeles, unlike timestamp literals elsewhere, which default to UTC.
How far back: the time travel window
How far back: the time travel window
The window is set at the dataset or project level, from 2 to 7 days, and is 7 days by default. After it closes there are 7 more days of fail-safe: you cannot copy a table out of it until Google Cloud Customer Care grants temporary recovery access on the dataset; then you run the copy yourself. Both figures are from Google’s time travel and fail-safe page, read in October 2026.
To change the window, set it in hours on the dataset:
ALTER SCHEMA `myproject.searchconsole`
SET OPTIONS (max_time_travel_hours = 48);The value must be a multiple of 24: 50 hours failed with “Invalid max_time_travel_hours: “50” has to be multiple of 24.” To see a dataset’s setting, query INFORMATION_SCHEMA.SCHEMATA_OPTIONS; if no max_time_travel_hours row appears, the default of seven days applies, as it did for our export dataset.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Compare two versions
A query cannot read the same table at two moments:
Table … is referenced with and without ‘FOR SYSTEM_TIME AS OF’ expression. If a ‘FOR SYSTEM_TIME AS OF’ expression is used, all references of a table should use the same TIMESTAMP value.
So save the past version first, then compare:
CREATE TABLE mydataset.site_impression_oct14 AS
SELECT *
FROM `myproject.searchconsole.searchdata_site_impression`
FOR SYSTEM_TIME AS OF TIMESTAMP '2026-10-14 00:00:00+00'
WHERE data_date >= '2026-10-01';Then join or EXCEPT DISTINCT the saved table against the current one to see which rows changed. Comparing aggregates needs no copy: run the same query twice, once with the clause and once without. We did that for 1 to 11 October on our export:
| Read | Days | Rows | Clicks | Impressions |
|---|---|---|---|---|
| as of 14 October | 11 | 204,468 | 1,256 | 574,454 |
| current, 16 October | 11 | 204,468 | 1,256 | 574,454 |
Every day matched to the row, so the Search Console export did not rewrite days it had already loaded; the extra rows in the opening example are only the two new days. That is worth knowing before blaming the source when a report moves: if old days did not change, the query or the report did. The dry run estimated 20.7 MB to read for this copy, because SELECT * reads every column; name the columns you need to compare and it reads less.
This is also the way to answer “what did the dashboard show on Monday”: save Monday’s state of the table, and rerun the dashboard’s query on it.
Undo a bad change
When an UPDATE, DELETE or a double load damaged a table that still exists, read the good state with time travel and write it back. When the table itself was dropped, FOR SYSTEM_TIME AS OF cannot read it: you restore it by copying with a time decorator, bq cp mydataset.t@<millis> mydataset.t_restored, or UNDROP SCHEMA for a whole dataset. Both procedures, with the commands and their limits, are in how to restore a deleted table.
The errors you will hit
| Error | Cause |
|---|---|
| “Invalid time travel timestamp … Cannot read before …” | the moment is older than the window; the message gives the earliest moment available |
| “… evaluates to a TIMESTAMP value in the future” | the timestamp is after now, often a time zone mistake |
| “… referenced with and without ‘FOR SYSTEM_TIME AS OF’ …” | the same table read at two moments in one query |
| “Invalid max_time_travel_hours … has to be multiple of 24” | the window must be whole days |
What time travel costs
Time travel adds no charge of its own: a query with FOR SYSTEM_TIME AS OF is billed like any other, by the bytes of the version it reads. Our 14 October read processed 1.64 MB; the same query on the current table, with two more days in it, 1.96 MB. The storage is what differs, and it depends on the dataset’s storage billing model:
- Logical (the default): time travel and fail-safe bytes are included; you do not pay for them separately.
- Physical: they are charged at the active physical storage rate. A table rewritten in full every day keeps up to seven days of old versions in time travel and seven more in fail-safe, all billed, so a shorter window can save money here.
Source: Google’s time travel page, as above. The rates are in BigQuery pricing.
What time travel cannot do
- Go further back than the window. For anything older, take table snapshots on a schedule; the snapshot backup routine shows how.
- Read external tables (Apache Iceberg external tables are the exception), tables in external datasets, temporary tables and cached results.
- Be used by everyone on tables with row-level access policies. If a table has, or ever had, such a policy, only table administrators can use time travel on it.
- Bring back table metadata such as descriptions; it keeps data.
- Work across a dropped table with a query; restore the table first.
A dashboard you can explain after the fact
A dashboard you can explain after the fact
The most useful thing time travel gives a reporting team is an answer to “the number was different yesterday, why?”: read the source table as of yesterday, rerun the query, and compare. That works when the query behind the number is one known query, not a copy in someone’s dashboard. In OWOX Data Marts it is a Data Mart, run as written in your own BigQuery project with its runs listed in Run History, so you know which SQL produced Monday’s number. OWOX Data Marts does not transform the data.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Is time travel enabled by default in BigQuery?
Yes. Every table can be queried as it was at any moment in the dataset's time travel window, seven days by default, with FOR SYSTEM_TIME AS OF. Nothing has to be set up.
Can I turn time travel off in BigQuery?
No. You can shorten the window to two days per dataset with ALTER SCHEMA ... SET OPTIONS (max_time_travel_hours = 48), in multiples of 24 hours, but not below two days.
What happens to time travel data after the window?
It moves to fail-safe for seven more days. Fail-safe data cannot be queried or restored by you; only Google Cloud support can recover it, at table level. After that it is gone.
Can I keep time travel longer than seven days?
No; seven days is the maximum window. For longer history, take table snapshots on a schedule or copy the table, and set an expiration so old copies delete themselves.
Does time travel work on every table?
It works on standard BigQuery tables. It does not cover external tables, temporary tables or cached query results, and it keeps data, not table metadata such as descriptions.
How do I restore a table with time travel?
If the table still exists, read its earlier state with FOR SYSTEM_TIME AS OF and write it to a new table, then swap. If it was dropped, copy it from its earlier state with bq cp dataset.table@<milliseconds> dataset.table_restored, or UNDROP SCHEMA for a whole dataset.
How do I compare a table with its earlier version?
Save the earlier version first with CREATE TABLE ... AS SELECT ... FOR SYSTEM_TIME AS OF, then compare the two tables. One query cannot read the same table at two moments; BigQuery returns an error that all references must use the same timestamp.
