A Beginner’s Guide to BigQuery Time Travel: What, Why, and How

Data changes are inevitable, whether due to accidental deletions, unintended modifications, or the need to track historical trends. Having a way to retrieve previous versions of your data can make all the difference in maintaining accuracy, compliance, and continuity. 

BigQuery’s Time Travel feature provides an easy way to access past table states, helping users restore lost data, monitor changes, and audit records without relying on backups. This guide explores what Time Travel is, why it matters, and how you can use it to efficiently manage and analyze your data. 

What Is BigQuery Time Travel?

The risk of accidental deletions or unexpected errors can result in permanent data loss if backups are unavailable. To address this, BigQuery provides the Time Travel feature, allowing users to retrieve and restore deleted or overwritten data within seven days.  

Using a simple SQL query, past table versions can be accessed by specifying the table name and a specific timestamp, making data recovery quick and efficient without relying on external backups.

How Does Time Travel in BigQuery Help?

Here are the key benefits of using the BigQuery Time Travel feature.

Recover Accidentally Deleted or Overwritten Data

Mistakenly deleting or overwriting data can lead to significant issues, especially when dealing with critical business information. BigQuery Time Travel helps prevent data loss by allowing users to recover past table versions within seven days. This feature ensures that valuable data remains accessible, reducing the risk of permanent loss and eliminating the need for complex backup and restore processes.

Analyze Data Changes Over Time

BigQuery Time Travel allows users to analyze historical trends by retrieving past table snapshots. By comparing data at different points in time, users can identify patterns, track changes, and detect anomalies. This feature helps businesses monitor performance metrics, financial trends, or customer behavior. 

With a simple query, users can access historical records, making it easier to analyze long-term trends and gain valuable insights without requiring separate backups or manual data tracking.

Ensure Data Integrity and Compliance

BigQuery Time Travel helps ensure data integrity by allowing users to investigate past records and pinpoint errors. If an issue arises, users can compare different snapshots of a table to identify when and how the problem occurred.

This feature is useful for debugging inconsistencies, resolving data discrepancies, and ensuring compliance with audit requirements. Businesses can verify past transactions and maintain accurate records without relying on external backups.

Practical Use Cases of Time Travel in BigQuery 

BigQuery Time Travel offers various practical applications, from recovering lost data to analyzing historical trends. Here are some practical use cases:

Query Historical Data with FOR SYSTEM_TIME AS OF

In BigQuery, the FOR SYSTEM_TIME AS OF clause allows you to retrieve historical versions of a table within the 7-day Time Travel window. This is useful for data recovery, analysis, and verification in cases of accidental updates or deletions.

Please note that for this, the user needs “bigquery.admin” role for the table.

Syntax:

1SELECT *  
2FROM `project_name.dataset_name.table_name`  
3FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL X TIMEUNIT);

‍

Here:

• project_name.dataset_name.table_name: Specifies the table from which to retrieve historical data.
• FOR SYSTEM_TIME AS OF: Queries the table as it existed at a past timestamp.
• TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL X TIMEUNIT): Retrieves the table state from X time units ago (e.g., 1 HOUR, 2 DAYS).

If you need to restore a table to a previous version using the FOR SYSTEM_TIME AS OF clause, use the CREATE OR REPLACE TABLE statement.

Syntax:

1CREATE OR REPLACE TABLE `project_id.dataset_id.table_name` AS  
2SELECT *  
3FROM `project_id.dataset_id.table_name`  
4FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL interval_amount TIME_UNIT);

‍

Example Use Case: Investigating Unexpected Data Changes

Suppose you (or a colleague) accidentally marked all orders as Cancelled with Failed payments, which affects analytics and reports.

1SELECT *  
2FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`  
3FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

‍

This helps identify missing or incorrect data caused by recent updates.

‍

Restoring a Previous Table Version:

You can also restore the table to its state from an hour ago using the following syntax.

1CREATE OR REPLACE TABLE `shopmart.sales_data.customer_orders` AS  
2SELECT *  
3FROM `shopmart.sales_data.customer_orders`  
4FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

‍

This ensures that the table is restored without inconsistencies.

Recover and Restore Deleted Tables Using Snapshots

When a table is accidentally deleted or modified, BigQuery Time Travel and table snapshots allow users to restore previous versions. This helps recover lost data, audit changes, and maintain data integrity. Below are common scenarios where this feature can be useful.

To retrieve or restore an earlier version of a table, use the following 

Syntax:

1SELECT *  
2FROM `project_name.dataset_name.table_name`  
3FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL X TIMEUNIT);

‍

Here:

• SELECT *: Retrieves all columns from the specified table.
• FROM project_name.dataset_name.table_name: Specifies the project, dataset, and table name from which to retrieve historical data.
• FOR SYSTEM_TIME AS OF: This clause allows querying the table as it existed at a specific point in time within the Time Travel window (up to 7 days).
• TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL X TIMEUNIT): CURRENT_TIMESTAMP() captures the current time, while INTERVAL X TIMEUNIT specifies how far back to query (e.g., 2 MINUTE, 1 HOUR, 1 DAY). TIMESTAMP_SUB subtracts this interval to retrieve the table’s state at that past moment.

‍

Example:

Suppose a retail company maintains a customer_orders table where daily transactions are recorded. An analyst notices that some records were deleted by mistake. They can use Time Travel and snapshots to recover the data.

Below are four common scenarios where this can be useful:

1. Query Data from 2 Minutes Ago

If an unintended deletion occurred recently, the analyst can check the table’s version from two minutes ago:

1SELECT *  
2FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`  
3FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 MINUTE);

‍

Here:

• SELECT *: Retrieves all columns from the customer_orders table.
• FROM owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart: Specifies the project (retail_project), dataset (sales_data), and table (customer_orders) from which historical data is being queried.
• FOR SYSTEM_TIME AS OF: Queries the table as it existed at a specific point in time within the 7-day Time Travel window.
• TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 MINUTE): Retrieves the table’s state exactly two minutes ago by subtracting 2 minutes from the current timestamp.

This helps verify if any data was lost.

2. Query Data from a Specific Time (Thursday, 8 AM)

Let’s say your company recently completed digitizing all historical order data from February 2024. While reviewing dashboards, the analytics team notices a discrepancy in the total sales figures. To investigate, you want to view the table exactly as it existed at 8 am on Thursday, right after the data import.

1SELECT *  
2FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`  
3FOR SYSTEM_TIME AS OF TIMESTAMP '2025-04-17 08:00:00 UTC';

‍

Here:

• SELECT *: Retrieves all columns from the customer_orders table.
• FROM owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart: Specifies the project (retail_project), dataset (sales_data), and table (customer_orders) from which historical data is being queried.
• FOR SYSTEM_TIME AS OF: Queries the table as it existed at a specific timestamp within the 7-day Time Travel window.
• TIMESTAMP '2025-04-17 08:00:00 UTC': Directly specifies a fixed point in time to retrieve the table’s state from that exact moment.

This allows users to compare past and current data for reporting or debugging.

3. Restore a Table from 10 Minutes Ago

If the team wants to restore the table to its state from 10 minutes ago, they can run:

1CREATE OR REPLACE TABLE `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart` AS  
2SELECT *  
3FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`  
4FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE);

‍

Here:

• CREATE OR REPLACE TABLE owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart: Creates a new table or replaces the existing customer_orders table with historical data.
• AS SELECT *: Copies all columns from the retrieved historical version of the table.
• FROM retail_project.sales_data.customer_orders: Specifies the project (retail_project), dataset (sales_data), and table (customer_orders) from which the historical data is being retrieved.
• FOR SYSTEM_TIME AS OF: Queries the table as it existed at a specific point in time within the 7-day Time Travel window.
• TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE): Retrieves the table’s state exactly 10 minutes ago by subtracting 10 minutes from the current timestamp.

This restores the table to how it looked 10 minutes ago, fixing unwanted changes.

If the table is fully deleted, then the following syntax can be used:

1CREATE TABLE `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart_Backup`  
2CLONE `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`;

‍

Here:

• CREATE TABLE owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart_Backup: Creates a new table named customer_orders in the specified project and dataset.
• CLONE owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart: Copies the entire structure and data from the snapshot table (customer_orders_snapshot) into the new table.

This restores the table to its state at the time the snapshot was taken, not a specific point like 10 minutes ago. Snapshots capture a single version, not multiple past states.

4. Make a Copy of the Table as It Was on Thursday, 8 AM

To create a separate backup of the table as it existed on Thursday, 8 AM, the following syntax can be used.

1CREATE TABLE `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart_Thursday8AM` AS  
2SELECT *
3FROM `owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart`  
4FOR SYSTEM_TIME AS OF TIMESTAMP '2025-04-17 08:00:00 UTC';

‍

Here:

• CREATE TABLE owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart_Thursday8AM: Creates a new table named customer_orders_backup in the specified project and dataset.
• AS SELECT *: Copies all columns from the retrieved historical version of the table.
• FROM owox-d-ikrasovytskyi-001.OWOX_Demo.Shopmart: Specifies the project (retail_project), dataset (sales_data), and table (customer_orders) from which historical data is being queried.
• FOR SYSTEM_TIME AS OF TIMESTAMP '2025-04-17 08:00:00 UTC: Retrieves the table’s state as it was on 2025-04-17, at 08:00 AM UTC.

This ensures a backup is available without altering the current table.

Using these queries, businesses can recover lost data, restore previous versions, and prevent permanent data loss efficiently.

Limitations of BigQuery Time Travel 

BigQuery Time Travel has restrictions, including a seven-day retention limit, required admin permissions for recovery, and incompatibility with external tables and certain storage types. Here, we’ll cover the key limitations:

Requires BigQuery Admin Permissions for Restoration

Restoring deleted tables in BigQuery requires specific Identity and Access Management (IAM) permissions. Only users with the BigQuery Admin role or equivalent permissions can retrieve past table versions. The required permissions include bigquery.tables.get, bigquery.tables.getData, bigquery.tables.restoreSnapshot, and bigquery.tables.create. 

Without these, even table owners cannot restore deleted data. Ensuring the right access control is essential for managing data recovery efficiently within the seven-day Time Travel window.

Time Travel Retention Limit

BigQuery Time Travel retains historical table versions for up to seven days, and this period cannot be extended. Once the retention window expires, the data is permanently deleted unless a backup or snapshot exists. 

However, Fail-safe storage provides an additional seven-day retention period, offering a second chance to recover data. Learn more about how Fail-safe storage works in the next section.

Limited to Standard BigQuery Tables

Time Travel is only available for standard BigQuery tables, meaning it does not support external tables or linked datasets in Analytics Hub. This limitation prevents users from recovering historical data for tables connected to external storage sources like Google Cloud Storage or third-party data platforms. 

Since these tables are not stored directly in BigQuery, they do not benefit from Time Travel’s automatic versioning. If long-term data retention is needed for such tables, consider using table snapshots, manual backups, or alternative data recovery strategies. Proper planning ensures critical data remains accessible when required.

BigQuery Time Travel and Fail-safe Storage

Managing historical data in BigQuery comes with two key features: Time Travel and Fail-safe Storage. Time Travel allows access to previous table versions for up to seven days, while Fail-safe storage retains deleted data for an additional seven days for emergency recovery. 

However, costs vary depending on whether the data is stored in logical storage or physical storage. Logical storage incurs no extra charges for these features, but physical storage applies active storage rates, potentially leading to higher costs than expected.

Key Cost Differences:

1. Time Travel Storage (Up to 7 Days)

• Enabled by default in BigQuery.
• Retains deleted or modified data within the set time travel window.
• Logical Storage: Automatically included in storage costs.
• Physical Storage: Billed separately as additional bytes.
• Cost: $0.02 per GiB per month (active storage rate).

‍

2. Fail-Safe Storage (Additional 7 Days) 

• Not enabled by default; requires a Google Support ticket to recover data.
• Always billed at active Physical Storage rates, even if using Logical Storage.
• Cost: $0.02 per GiB per month (same as active physical storage).

‍

3. Logical vs. Physical Storage Cost Impact

• Logical Storage: $0.01 per GiB per month for long-term storage (90+ days without modification). Includes Time Travel and Fail-Safe storage costs automatically.
• Physical Storage: $0.02 per GiB per month for active storage (tables modified in the last 90 days). $0.02 per GiB per month for Fail-Safe storage, charged separately. Does not include Time Travel costs automatically, but bills for extra bytes stored.

‍

4. Impact of Deleting Large Datasets

• If a large dataset (e.g., 10+ TiB) is deleted, it shifts from long-term storage ($0.01/GiB) to active storage ($0.02/GiB) for Time Travel and Fail-Safe.
• This sudden shift doubles the cost for at least 14 days, leading to unexpectedly high storage charges.

‍

An Overall Comparison of Time Travel vs. Fail-safe Storage

‍

Since Fail-safe Storage cannot be adjusted or disabled, businesses should carefully manage storage settings to control costs while ensuring data availability.

Option #1: Tweak Your BigQuery Time Travel Settings

The default Time Travel retention period in BigQuery is seven days, but it can be reduced to two days to optimize storage costs. If you have a strong backup system, such as automated archiving to Google Cloud Storage (GCS) or other external sources, a shorter retention period may be sufficient. 

Reducing the Time Travel window helps minimize costs while ensuring essential data remains accessible through alternative backup methods.

You can reduce the time travel window in the console in the following manner:

• In the Explorer panel, expand your project and select the desired dataset.
• Click the More Options menu (⋮) and select Open.
• In the Details panel, click Edit details.
• Expand Advanced options, then choose the desired Time travel window.
• Click Save to apply the changes.

‍

‍

You can also use SQL to modify the time travel window, use the ALTER SCHEMA SET OPTIONS statement with the max_time_travel_hours option.

Syntax:

1ALTER SCHEMA `owox-d-ikrasovytskyi-001.OWOX_Demo`
2SET OPTIONS(
3  max_time_travel_hours = 168);

‍

‍

Here:

• DATASET_NAME: Name of the dataset you want to update.
• HOURS: Time travel window duration in hours (must be a multiple of 24, between 48 (2 days) and 168 (7 days)).

For example, if you set  max_time_travel_hours to 168, the time travel window becomes 7 days (168 hours).

Option #2: Convert Your Table to BigQuery Logical Storage Before Deleting It

Switching your dataset’s storage model to logical storage before deleting a table can help avoid Time Travel and Fail-safe Storage costs, as these charges only apply to physical storage. However, this change can only be made once every 14 days and may take up to 24 hours to take effect. 

Before switching, compare the costs of 14 days of logical storage versus nine days of physical storage to determine the most cost-effective option.

Option #3: Don’t Switch to BigQuery Physical Storage to Begin With

For datasets with frequent data additions and deletions, staying on logical storage can be more cost-effective than switching to physical storage. Logical storage does not incur Time Travel or Fail-safe Storage costs, whereas physical storage does. 

If large volumes of data are regularly modified or deleted, these additional charges can significantly increase costs. Before switching to physical storage, analyze your data usage patterns to determine if the cost savings from compression outweigh the expenses of historical data retention.

Fixing Common Errors in BigQuery Time Travel 

While using BigQuery Time Travel, users may encounter errors related to data accessibility and invalid snapshot times. Here, we’ll cover common errors and how to resolve them efficiently.

Data No Longer Accessible

⚠️ Error:

In BigQuery, the "Data no longer accessible" error occurs when attempting to retrieve a table version older than seven days or if the table has been permanently deleted or expired. Since Time Travel has a strict retention limit, data beyond this window is no longer accessible.

✅ Solution:

Ensure your query references a timestamp within the seven-day Time Travel window. If older data is needed, use table snapshots or backups for long-term retention.

Invalid Snapshot Time

⚠️ Error:

In BigQuery, the "Invalid snapshot time" error occurs when querying a table using FOR SYSTEM_TIME AS OF, but the table is either permanently deleted beyond the seven-day Time Travel window or is an unsupported table type (view, materialized view, or external table).

✅ Solution:

Ensure you are querying a standard table, not a view or external table. If the table was deleted more than seven days ago, use a backup or table snapshot. Also, verify that the timestamp format in your query is correct.

Best Practices to Optimize BigQuery Time Travel 

Optimizing BigQuery Time Travel helps manage storage costs and ensure data availability. Implementing best practices like monitoring expiration policies, using audit logs, and creating table snapshots can improve data recovery and efficiency.

Set Reminders for Critical Table Expiration Policies

Accidental data loss can occur if tables expire without notice. To prevent this, regularly monitor table expiration settings using Google Cloud Monitoring or set up manual reminders. 

Reviewing expiration dates ensures critical data is backed up before deletion. Implementing an automated notification system can help avoid unexpected data loss and maintain data availability.

Combine Time Travel with BigQuery Audit Logs for Monitoring

BigQuery Audit Logs provide detailed records of data modifications and deletions, helping track changes over time. By integrating Time Travel with Audit Logs, users can monitor data alterations, troubleshoot unexpected updates, and maintain a reliable historical record. 

This combination enhances data governance, ensuring transparency and accountability in data operations while simplifying recovery when needed.

Use Table Snapshots for Long-Term Data Recovery

With Time Travel limited to seven days, table snapshots provide a more reliable option for long-term data recovery. Snapshots capture a point-in-time backup without altering the live table, ensuring critical data remains accessible. 

By implementing snapshots, businesses can protect historical data, simplify restoration, and maintain compliance with data retention policies beyond Time Travel’s constraints.

Optimize Storage Costs by Adjusting the Time Travel Window

Reducing the Time Travel retention period from seven days to a minimum of two days helps lower storage costs, especially under the physical storage billing model. 

For datasets that don’t need extended recovery, a shorter retention window minimizes unnecessary storage usage. Adjusting this setting ensures cost efficiency while maintaining access to recent historical data when needed.

Enhance Data Management with BigQuery Functions

BigQuery offers a wide range of functions that enable users to efficiently manage, transform, and analyze large datasets. These functions simplify data manipulation, optimize queries, and enhance performance, making it easier to work with complex datasets at scale.

• Date Functions: Work with date values using functions like DATE_ADD, DATE_DIFF, and DATE_TRUNC to calculate differences, shift dates, and extract specific parts of a date.
• Timestamp Functions: Use TIMESTAMP_DIFF, FORMAT_TIMESTAMP, and EXTRACT functions to perform calculations and retrieve or format date-time values.
• Datetime Functions: Handle precise date and time operations using DATETIME_SUB, DATETIME_TRUNC, and FORMAT_DATETIME for conversions, formatting, and extracting time details.
• DDL Functions: Use CREATE, ALTER, and DROP functions to efficiently modify tables, views, and schemas.
• Array Functions: Work with structured lists using ARRAY_LENGTH, ARRAY_AGG, and UNNEST to manipulate and analyze array-type data.
• DML Functions: DML commands in BigQuery allow you to modify and manage your data. These include commands like INSERT, UPDATE, and DELETE, which enable you to add, change, or remove records in your tables.

Maximize BigQuery Insights with OWOX BigQuery Data Marts

The OWOX BigQuery Data Marts improves reporting by making data analysis more efficient and user-friendly. It allows users to create detailed reports without complex SQL queries, simplifying data-driven decision-making for businesses.

With this extension, data professionals can optimize queries, automate reporting workflows, and extract valuable insights from BigQuery data. It serves as both a learning tool and a practical solution for implementing effective data strategies in professional settings.