Work with change history

BigQuery change history lets you track the history of changes to a BigQuery table. You can use GoogleSQL functions to see particular types of changes made during a specified time range, so that you can process incremental changes made to a table. Understanding what changes have been made to a table can help you do things like incrementally maintain a table replica outside of BigQuery while avoiding costly copies.

Required permissions

To view the change history on a table, you need the bigquery.tables.getData permission on that table. The following predefined Identity and Access Management (IAM) roles include this permission:

• roles/bigquery.dataViewer
• roles/bigquery.dataEditor
• roles/bigquery.dataOwner
• roles/bigquery.admin

If a table has, or has had, row-level access policies, then only a table administrator can access historical data for the table. The bigquery.rowAccessPolicies.overrideTimeTravelRestrictions permission is required on the table and is included in the predefined roles/bigquery.admin IAM role.

If a table has column-level security, you can only view the change history on the columns that you have access to.

Change history functions

You can use the following functions to understand a table's change history:

• APPENDS: returns all rows appended to a table for given time range.

  The following operations add rows to the APPENDS change history:

  • CREATE TABLE DDL statement
  • INSERT DML statement
  • Data appended as part of a MERGE DML statement
  • Loading data into BigQuery
  • Streaming ingestion

• CHANGES: returns all rows that have changed in a table for a given time range. To use the CHANGES function on a table, you must set the table's enable_change_history option to TRUE.

  The following operations add rows to the CHANGES change history:

  • CREATE TABLE DDL statement
  • INSERT DML statement
  • Data appended or changed as part of a MERGE DML statement
  • UPDATE DML statement
  • DELETE DML statement
  • Loading data into BigQuery
  • Streaming ingestion
  • TRUNCATE TABLE DML statement
  • Jobs configured with a writeDisposition of WRITE_TRUNCATE
  • Individual table partition deletions

Pricing and costs

Calling change history functions incurs BigQuery compute costs. Both the APPENDS and CHANGES functions require processing all data written to the table within the specified time range. This processing applies to all writes, including both append and mutation operations. Setting a table's enable_change_history option to FALSE does not reduce the data processed by APPENDS.

When you set the enable_change_history option on a table to TRUE in order to use the CHANGES function, BigQuery stores table change metadata. This stored metadata incurs additional BigQuery storage costs and BigQuery compute costs. The amount billed depends on the number and type of changes made to the table, and is typically small. Tables that have many change operations, especially large deletions, are the most likely to incur noticeable costs.