time travel bigquery

BigQuery Time Travel: How to access Historical Data? | Easy Steps

By: Ishwarya M | Updated: May 24, 2024

Related Articles

BigQuery is one of the most popular and highly efficient analytics platforms that allow you to store, process, and analyze Big Data. In addition, BigQuery can process over 100 trillion rows and run more than 10,000 queries at the same time across the organization. It not only has advanced processing capabilities but also is highly scalable and fault-tolerant, which enables users to seamlessly implement advanced Analytics. One such advanced fault-tolerant feature is BigQuery Time Travel , which allows you to travel back in time for retrieving the previously deleted or modified data.

Table of Contents

In this article, you will learn about BigQuery, BigQuery Time Travel, and how to time travel to retrieve the deleted BigQuery data.

Prerequisites

Fundamental knowledge of data analysis using SQL.

What is BigQuery?

Developed by Google in 2010, BigQuery is a Cloud-based data warehousing platform that allows you to store, manage, process, and analyze Big Data. BigQuery’s fully managed Serverless Architecture will enable you to implement high-end Analytics operations like Geospatial Analysis and Business Intelligence. It not only allows you to implement Big Data Analytics but also empowers you to build end-to-end Machine Learning Models. 

Since BigQuery has a Serverless Infrastructure, you can focus on analyzing Big Data and building Machine Learning models instead of concentrating on resource and infrastructure management. Furthermore, developers and data professionals can use client libraries of popular programming languages like Python, Java, and JavaScript , as well as BigQuery’s REST API and RPC API for transforming, managing, and analyzing BigQuery data.

Key Features of Google BigQuery

The serverless Data Warehouse has various built-in capabilities that help with data analysis and provide deep insights. Take a look at some of BigQuery’s most important features.

• Fully Managed by Google: The Data Warehouse infrastructure is managed by Google. It keeps track of, updates, monitors, and distributes all of your data and information. Google will be notified if your task fails.
• Easy Implementation: BigQuery is simple to implement because it doesn’t require any additional software, cluster deployment, virtual machines, or tools. BigQuery is a serverless Data Warehouse that is very cost-effective. To evaluate and solve queries, all you have to do is upload or directly stream your data and run SQL.
• Speed: BigQuery can process a large number of rows in a matter of seconds. It can also perform terabyte-scale inquiries in seconds and petabyte-scale queries in minutes.

What is BigQuery Time Travel? 

Users can use BigQuery to process and analyze large amounts of data in order to implement end-to-end Cloud Analytics. However, there is a possibility that data can be deleted accidentally or due to some man-made errors while working on BigQuery. As a result of such unpredictable errors , users will permanently lose their data if they haven’t backed up or kept a copy of the deleted data. 

To eliminate this complication, BigQuery allows users to retrieve and restore the deleted data within seven days of deletion. In other words, BigQuery allows users to travel back in time to retrieve or fetch the historical data that was deleted before seven days. With a simple SQL query, users can effectively retrieve the deleted data by providing the table name and time for returning results from a given table according to the specific Timestamp .

How to access Historical data using BigQuery Time Travel? 

• With the BigQuery Time Travel feature, you can easily query and fetch historical data that has been stored, updated, or deleted before. The BigQuery’s SQL clause named FOR SYSTEM TIME AS OF helps you to time travel for a maximum of seven days in the past to access the modified or deleted data. 
• However, there are some limitations of the FOR SYSTEM TIME AS OF clause. 
• The deleted table that you want to retrieve must have previously been saved or stored in BigQuery; it cannot be an external table.
• To use the FOR SYSTEM TIME AS OF   clause for retrieving historical table data, there is no limit on table size, and the deleted BigQuery table can have an unlimited number of rows and columns. In other words, the FOR SYSTEM TIME AS OF fetches the deleted data from BigQuery irrespective of the table size.
• The source table of the FROM clause that includes FOR SYSTEM TIME AS OF in the BigQuery Time Travel query syntax should not be an ARRAY scan, including a flattened array or even the output of the UNNEST operator. The syntax should not include a common table expression defined by a WITH clause.
• The timestamp_expression in a BigQuery Time Travel query syntax must always be a constant expression and should not be in the form of subqueries and UDFs (User Defined Functions). The timestamp_expression should not be in the form of correlated references, which refers to columns that appear at a higher level of the query statement, like in the SELECT clause.
• Finally, the timestamp_expression in SQL syntax should not fall within a specific time range that includes the start and end times for retrieving data between the given range. The time period specified in the timestamp expression should never be longer than seven days.  
• The FOR SYSTEM TIME AS OF clause allows you to switch to the previous versions of the specific table definition and rows with respect to the given timestamp or time expression. On providing the particular timestamp in the syntax of the SQL query, you can easily travel back to the given time for fetching the historical data. The below-given command is the sample SQL query to fetch and return historical data in BigQuery.
• On executing the query given above, you can fetch or return deleted data of the table named from exactly one hour before. You can only return a historical version of the table before one hour since you gave the FOR SYSTEM_TIME AS OF parameter as INTERVAL 1 HOUR . In the above SQL query, we just passed the fetching interval to return data from the particular table based on the given interval. However, you can also set a specific timestamp to point back in time for fetching historical data precisely according to the given timestamp.
• For returning historical values in a table at an absolute or specific point in time, you can follow the sample SQL query given below. 
• By following the above command, you are able to fetch the aggregate values from the table named table_a with respect to the given timestamp in the point_in_time_timestmap parameter. You should always make sure that the parameter is given in the form of yyyy-mm-dd hh:mm:ss . For example, the point_in_time_timestmap should resemble 2022-03-17 10:30:00 . 
• If the timestamp you provide is greater than seven days earlier or before the table was created, the SQL query will fail to execute and return an error message given below. 
• If you use the CREATE OR REPLACE TABLE statement for replacing the active and existing table, you could still use the FOR SYSTEM TIME AS OF clause to query the previous version of the table based on the recent interval command. 
• When the existing table is accidentally deleted, the query fails and returns an error message, as given below.
• However, you can restore an active table by retrieving data from a specific point in time based on the timestamp, as explained in the above steps.
• Instead of just retrieving deleted data, you can use the BigQuery Time Travel feature to directly copy the modified or deleted historical data into a new table. This direct copying of deleted data into a new table works even if the table was deleted or expired, as long as you retrieve and restore historical data within seven days of deletion.
• For copying deleted historical data into the new table, you should use decorators in the SQL query. 
• There are three types of decorators: “tableid@TIME,” “tableid@-TIME_OFFSET,“ and “tableid@0.“ 
• The tableid@TIME decorator retrieves historical data based on a specific time in milliseconds, while the tableid@-TIME OFFSET decorator fetches data from the deleted table based on a specific time interval. For fetching the oldest available historical data (within a time period of seven days), you can use the tableid@0 decorator. 
• Execute the code given below to fetch the historical data from a deleted table and copy it into the new table. 
• In the above code, but is the load command in BigQuery, which allows you to transfer or load data from one table to another table. With the SQL query, you are fetching data from a table named “ table1 “ and copying it into the table called “ table1_restored .“ Consequently, the query retrieves the one-hour old data from the table1 and stores it in the “ table1_restored ” table. The query treated the time as one hour because you provided the time in milliseconds, i.e.,3600000, which equals one hour. Note : (3600000=1 hour).

By following the above-given steps, you can easily travel back in time to retrieve the deleted or modified data in BigQuery.

In this article, you learned about BigQuery and how to time travel to retrieve the deleted BigQuery data. The BigQuery Time Travel feature is one of the unique and advanced features of BigQuery that allows users to recover the lost data by traveling back in time and bringing them back. However, BigQuery also has more advanced features like teleportation, miniaturization, and multiverse for implementing cloud analytics, which you can explore later. In case you want to export data from a source of your choice into your desired Database/destination like BigQuery then Hevo Data is the right choice for you! 

Hevo Data , a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 100+ sources ( including 40+ free sources ) allows you to not only export data from your desired data sources & load it to the destination of your choice like BigQuery , but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about BigQuery Time Travel ! Let us know in the comments section below!

Ishwarya has experience working with B2B SaaS companies in the data industry and her passion for data science drives her to product informative content to aid individuals in comprehending the intricacies of data integration and analysis.

No-code Data Pipeline For BigQuery

• BigQuery Functions

Hevo - No Code Data Pipeline

Continue Reading

Arsalan Mohammed

How to Set Up Amazon Redshift ODBC Driver Connection

Manisha Jena

Setting up Redshift Data Lake Export: Made Easy 101

Chirag Agarwal

Snowflake Security & Sharing Best Practices

I want to read this e-book.

Photo by Zulfa Nazer on Unsplash

Using BigQuery Time Travel

In this practical BigQuery exercise, we’re going to look at BigQuery Time Travel and see how it can help us when working with data. It’s not as powerful as Marty McFly’s DeLorean in Back to the Future (nobody knows what your future data will look like), but a useful tool in our toolset nevertheless.

First of all, what is BigQuery Time Travel? It allows for retrieving the state of a particular table at a given point within a time window, which is set by default to 7 days.

Let’s have a look at an example. At 11:00 AM we’ll create the following table.

Then, several minutes later, we make some changes to that table, say, insert a row.

We can now confirm that we have the extra row. But what if we’d like to query the table as of earlier?

Using the approach above we can query the table at any particular point in the time travel window, set by default to 7 days, but configurable to be between 2 and 7 days.

Changing the time travel window

The time travel window is set at a dataset level, so affects all the tables in that dataset. The default time travel windows (7 days) can be overridden either at dataset creation time or on an existing dataset.

Practical considerations

The FOR SYSTEM_TIME AS OF clause comes after the table you’d like to apply Time Travel to, so a join query would look as follows.

Also, if you’d like to copy the table at a particular point in time, that can be done using the bq utility (part of the gcloud CLI ).

Note that the timestamp there is the UNIX epoch in milliseconds, which can be obtained as follows.

In this short practical exercise, we’ve looked at BigQuery Time Travel, a very handy tool to aid us in querying previous states of a particular table. I found it very helpful when debugging data pipelines.

Thanks for reading and stay tuned for more practical BigQuery tips.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com .

Did you find this article valuable?

Support Constantin Lungu by becoming a sponsor. Any amount is appreciated!

Time Travel with BigQuery

How to use BigQuery superpowers to rewind time.

Who does not like time travel? We all saw it and were fascinated by it in many sci-fi movies, unfortunately science did not crack real-life time travel yet.

However, we can “data time-travel”. Thanks to the amazing BigQuery SQL feature “FOR SYSTEM_TIME AS OF”, we can time travel (up to 7 days in the past) to a specific timestamp (for detailed information about the syntax refer to the official documentation ).

This feature is extremely easy to use. In fact, we can simply modify any query by adding the clause above mentioned to the FROM clause. The following example returns all rows in table_a from exactly 3 hours ago.

Or we can specify a precise point-in-time.

How is this useful?

Ok, it is nice to talk about time travel, but how is this useful in real-life? In the current age of high data integration, data is inserted, deleted, and modified at a high rate. In highly integrated and streaming systems, it is often challenging to plan and perform backups that can be used to recover from processing errors. In BigQuery this can be done by just selecting rows from a specific point-in-time. This removes the need to materialize snapshots (for the past 7 days) and give us incredible granularity (timestamp precision). The most obvious use-case is data recovery . We can easily recover a table state by creating a new one from a specific point-in-time.

A more interesting and less obvious use-case could be to ensure consistent analytics results over time from a table that is modified with a high frequency or that receives streaming inserts. In this case, we could configure the SQL query to always return results from a specific timestamp during a specific time frame. We could build a caching system to do that or we could use “FOR SYSTEM_TIME AS OF” with a specific point-in-time to “force” queries to always return results from a past table state and just change the value when we want the results from a different time.

Or for a more dynamic solution

Follow or contact me on Twitter for more stories about data, infrastructure, and automation.

Principal Data Platform Engineer

My interests include data engineering at scale, geospatial analytics, machine learning, windsurf, and cooking.

• Slots to the (Wallet) Rescue
• BigQuery Authorized Views with Terraform
• Real-Time data delivery at scale with BigQuery
• Serverless S3 Access Logs Analytics using BigQuery
• BigQuery Geography Clustering

Boost your log analysis with BigQuery vector search and LLMs

Cloud Solutions Architect, Google

Omid Fatemieh

Engineering Lead, Google Cloud

Try Gemini 1.5 models

Google's most advanced multimodal models in Vertex AI

We recently launched BigQuery vector search , which enables semantic similarity search in BigQuery. This powerful capability can be used to analyze logs and asset metadata stored in BigQuery tables. For example, given a suspected log entry, your Site Reliability Engineering (SRE) or incident response (IR) team can search for semantically similar logs to validate whether that’s a true anomaly. These searches can be done proactively to auto-triage such anomalies, saving your users time on otherwise lengthy investigations. Furthermore, these relevant search results can provide more complete context for your large language model (LLM) to identify relationships, patterns and nuances that can otherwise be harder to capture with traditional schema-based querying alone. This can accelerate many user workflows — from threat detection and investigation, to network forensics, business insights and application troubleshooting.

In this blog, we provide an overview of how to use BigQuery’s new vector search capabilities against logs and asset metadata. First, we’ll present key components of this solution. Then we’ll go over a few real-world customer use cases on how to leverage BigQuery vector search over your logs and assets. We’ll also include pointers on how to cost-effectively deploy this at scale.

Vector search vs. text search

While traditional text search relies on matching exact words, or tokens, vector search identifies semantically similar records, by finding closely matching vector embeddings. A vector embedding is a high-dimensional numerical vector that encapsulates the semantic meaning of a given data record (text, image, audio or video file). In the case of logs or assets, the data record is either a log entry (e.g., audit, network, application) or a resource metadata (e.g., asset or policy config change).

BigQuery users can configure and manage search indexes and vector indexes for fast and efficient retrieval. While full-text search indexes yield deterministic results (full recall), vector search with a vector index returns approximate results. This is due to using the Approximate Nearest Neighbor (ANN) search technique, which offers better performance and lower cost in exchange for lower recall .

While text search helps with needle-in-haystack type searches, vector search helps with similarity and outlier-type searches. Both of these search techniques are commonly used for log analytics to answer questions such as the following: 

For the rest of this blog, we’ll focus on a few illustrative examples on how you can use vector search on logs data, including retrieval-augmented generation (RAG).

From semi-structured back to unstructured data

In addition to the classic challenges of big data (notably the 4 Vs of data — volume, variety, velocity and variability), analyzing logs involves making sense of their complex semi-structured format. Logs are often composed of deeply nested fields and non-uniform payloads like JSON. A typical organization can have billions of log entries with hundreds or thousands of different log payload formats.

To handle the massive volume of logs and to make them embedding-friendly, this solution employs two key steps as depicted in the above architecture diagram:

Aggregation reduces data volume by combining multiple log entries into one. So instead of analyzing individual log entries, user actions can be grouped into a single entry per hour/day. For instance, a particular action done 20 times by a user on a given day (and therefore logged 20 times), can be coalesced into one record, achieving 20:1 reduction in space.

NL Conversion : Additionally, converting verbose semi-structured JSON log payloads into simple natural language log summaries further reduces data volume. This conversion makes it easier to generate embeddings from the textual data, and to subsequently query with vector search. For instance, a 2KB audit log entry can be condensed into a 200-character sentence that semantically explains what this audit log entry means, achieving 10:1 additional reduction in space.

As an example, here are SQL samples (in Dataform SQLX) to process Cloud Audit Logs:

SQL query to aggregate and convert Cloud Audit Logs into log summaries

SQL query to generate embeddings for those log summaries . This query also sets up the vector index over the embeddings column.

The natural language log summaries, alongside their embeddings, allow a subsequent LLM application to better understand and capture nuances that are otherwise harder to represent using structured or semi-structured raw data alone. We will demonstrate this in the next section. The raw logs can still be retained and referenced in the source log bucket or BigQuery dataset.

It's important to note that the exact parameters for aggregation and conversion are ultimately dependent on your specific log type and use case. For example, the aggregation interval (10 min vs. 1 hr vs. 1 day) depends on your search lag-time requirements and allowable time-precision loss. Similarly, the conversion step requires carefully selecting the most relevant log fields to extract — this is typically done by a subject matter expert who’s familiar with that particular log type. Careful planning and configuration of both aggregation and conversion steps will help you strike the right balance between reducing log volume and maintaining the ability to identify meaningful patterns and insights.

Real-world applications

Now, let’s demonstrate how to use BigQuery SQL function VECTOR_SEARCH in SQL queries that you can try on your own. The examples below assume the logs are already preprocessed (aggregated, converted, and embedded) into a table of historical logs called actions_summary_embeddings , as implemented in this sample Dataform repo .

Example 1 uses vector search to validate potential anomalies against the historical logs. The test dataset (potential anomalies) is stored in table actions_summary_embeddings_suspected .

Example 2 uses vector search to find rule violations throughout our historical logs. Notice how the rule is expressed in natural language, and embedded dynamically.

Examples 3 and 4 are more complex uses of vector search: example 3 uses subsequent logic to triage anomalies based on nearest neighbor results like number and distance of matches, while example 4 uses nearest neighbor results to augment the LLM in-context prompt (RAG) and have the LLM such as Gemini reason and triage anomalies for us.

Note : Solutions built based on embeddings, vector search, and LLM inference are inherently probabilistic and approximate. Consider evaluating the recall rate of your vector searches and other metrics based on your higher-level task’s objectives and risk tolerance.

1) Validate anomalies using vector search (outlier detection)

Example results:

2) Search policy violations using vector search (similarity search)

3) Triage anomalies using vector search with rule-based logic

4) Triage anomalies using vector search with LLM reasoning (RAG)

Example output (exported to Google Sheets) showing the prompt enriched with vector search results and the generated LLM response:

Generated LLM response copied below for clarity:

**Low risk.** - The principal ([email protected]) has performed the same operation (google.logging.v2.ConfigServiceV2.DeleteSink) on the same logging sink (<redacted>) in the same project (<redacted>) multiple times in the past using Terraform. - The IP address (<redacted>) used in the suspected administrative action is not significantly different from the IP addresses (<redacted> and <redacted>) used in previous administrative actions. - The frequency of the suspected administrative action (1 time) is consistent with the frequency of previous administrative actions (1 time, 3 times, 1 time, and 1 time).

A note about scale, cost and performance

Vector search, like full-text search, employs a specialized index to enhance performance. By using a vector index, vector search queries can efficiently search embeddings, leading to faster response times and reduced data scanning or slot usage. However, this increased efficiency comes with the trade-off of potentially returning less precise results.

For example, consider a project with 50GB worth of audit logs over the last 12 months. Assuming 2KB average payload of an audit log entry, this translates to about 25M log records. By employing the aforementioned methods of reducing log volume, we can achieve a two-to-three orders of magnitude reduction in volume:

25M log entries @ 2KB/each → 1.25M (aggregated) logs @ 200 bytes/each

50GB raw logs → ~240MB log embeddings

That’s roughly the size of your vector index. This means fast (1-2 seconds) and cost-effective semantic search over the 12-months lookback.

Now, let’s assume the 50GB of logs are ingested daily . For a one-year retention, this translates to a total of 17TB of raw log data, but only 81.72GB of log embeddings. This is far less data volume to store and search in BigQuery using a fully-managed vector index.

Try BigQuery vector search today

In summary, BigQuery has evolved into a comprehensive platform offering not just robust data analytics, but also integrated full-text and vector search. This is particularly useful in the context of log analytics including detecting and investigating anomalies. Semantic search, powered by vector search, broadens the types of questions you can ask of your data and accelerates the time to get to insights. Additionally, by leveraging vector search for information retrieval within a RAG architecture, you can develop sophisticated LLM-powered applications that keep pace with the increasing volume of logs and potential security threats.

To experiment with BigQuery vector search over your audit logs, try this Log Anomaly Detection & Investigation notebook, which provides a step-by-step walkthrough of log preparation, querying, and visualization. To deploy the solution described in this blog, you can use this BigQuery Dataform repo which automates the end-to-end data pipeline. All the steps including log aggregation, conversion and embeddings generation are codified in SQL queries and periodically executed using Dataform scheduled workflow. Using Dataform is the fastest way to customize, deploy and evaluate this solution for your use case and for your logs in log analytics or BigQuery.

• Data Analytics
• AI & Machine Learning

Related articles

Square Enix’s journey of building an AI-driven Customer Data Platform

By Tatsuo Yoshida • 7-minute read

Google is a Leader in the 2024 Gartner® Magic Quadrant for Analytics and Business Intelligence Platforms

By Kate Wright • 5-minute read

Simplify historical data tracking in BigQuery with Datastream's append-only CDC

By Etai Margolin • 3-minute read

Google is a Leader in the 2024 Gartner® Magic Quadrant™ for Data Science and Machine Learning Platforms

By Burak Gokturk • 6-minute read