Stay Fresh: Four Ways to Track Update Times for BigQuery Tables and Views

Ever experienced a delayed dashboard? Been frustrated by late data for that critical report? That's the sting of stale data. As a data or analytics engineer, you know how crucial it is to have timely, up-to-date data at your fingertips.

In this post, we'll explore several ways to determine the "freshness" of your tables and views in Google BigQuery. We'll dive into both relevant SQL queries and metadata via Information Schema to give you multiple tools to keep your data transformations running smoothly.

Determining Last Update Time Using the MAX Function

The most straightforward approach to determine the last update time in BigQuery leverages the MAX() function on a timestamp column within your table. This method can be especially useful when your table rows include a timestamp column that gets updated whenever a new record is inserted or an existing one is modified.

Here's an example of how you can use the MAX() function:

SELECT 
 MAX(timestamp_column) AS last_modified
FROM 
  project_id.dataset.table

In this SQL command, replace project_id, dataset, and table with your respective Google Cloud Project ID, BigQuery dataset name, and table name. Also, replace timestamp_column with the name of the timestamp column in your table that records when each row was last updated.

This command returns the most recent timestamp in the timestamp_column column, which corresponds to the last time any row in the table was updated. This approach gives a precise picture of data freshness at the row level, which can be more informative than just the last time the table schema was updated.

However, for this method to work, your tables need to have a timestamp column that gets updated with each data modification. If such a column doesn't exist, you might want to consider adding one to your data ingestion pipelines or ETL processes to track row-level updates better.

Note that this method works on both tables and views, provided the underlying data of the views have a timestamp column that tracks updates.

Last Modified Time via Metadata

One straightforward approach to find out when a table was last updated in BigQuery is by checking the last_modified_time from the table's metadata.

You can run the following command:

SELECT 
  table_id, 
  TIMESTAMP_MILLIS(last_modified_time) AS last_modified
FROM 
  project_id.dataset.__TABLES__

In the above SQL command, replace project_id with your Google Cloud Project ID and dataset with your BigQuery dataset name. This script returns a list of tables in the specified dataset and their corresponding last modification timestamps.

_Note that this method only works for tables and not for views, as views in BigQuery do not have a last_modified_time property. _

Tracking Updates via INFORMATION_SCHEMA

Google BigQuery also provides an Information Schema, a series of system-generated views that provide metadata about your datasets, tables, and views.

To retrieve the last update timestamp for both tables and views, you can use the last_change_time column from the INFORMATION_SCHEMA.TABLES view. Here's an example:

SELECT 
  table_name, 
  TIMESTAMP(last_change_time) AS last_changed
FROM 
  project_id.dataset.INFORMATION_SCHEMA.TABLES

Like before, replace project_id and dataset with your respective project and dataset names.

However, there's an important caveat to note here. The last_change_time column represents the last time the table schema was updated, not necessarily the data. So, if you only added or removed rows but didn't modify the schema, last_change_time wouldn't reflect those changes.

Employing Partitioning and Clustering

For a more granular understanding of data freshness, BigQuery's native partitioning and clustering features can be utilized. If your tables are partitioned, you can identify the most recent partition, which often corresponds to the latest data.

SELECT 
  MAX(_PARTITIONTIME) AS last_modified
FROM 
  project_id.dataset.table

Remember to replace project_id, dataset, and table with your respective details.

This method is applicable only for partitioned tables, and it won't work for views or non-partitioned tables.

Final thoughts

Google BigQuery provides multiple methods to track the freshness of your data, each with its specific use cases and limitations. It's essential to understand these nuances and select the most appropriate method based on your needs.

In data-intensive environments where timeliness is of the essence, having these tools at your disposal ensures you can maintain the integrity and reliability of your data.

Want to track the freshness of BigQuery tables and views within minutes, then be alerted on anomalies with machine learning that accounts for trends and seasonalities? Get started with Metaplane for free or book a demo to learn more.