Work with change history

Preview

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Note: For support during the preview, contactbq-change-history-feedback@google.com.

BigQuery change history lets you track the history ofchanges to a BigQuery table. You can use GoogleSQLfunctionsto see particular types of changes made during a specified time range, sothat you can process incremental changes made to a table. Understanding whatchanges have been made to a table can help you do things like incrementallymaintain a table replica outside of BigQuery while avoidingcostly copies.

Required permissions

To view the change history on a table, you need thebigquery.tables.getDatapermission 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 onlya table administrator can access historical data for the table. Thebigquery.rowAccessPolicies.overrideTimeTravelRestrictions permission isrequired on the table and is included in the predefinedroles/bigquery.adminIAM role.

If a table has column-level security, you can only view thechange 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 theAPPENDS change history:

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

• CHANGES:returns all rows that have changed in a table for a given time range. To usetheCHANGES function on a table, you must set the table'senable_change_history optiontoTRUE.

  The following operations add rows to theCHANGES change history:

  • CREATE TABLE DDL statement
  • INSERT DML statement
  • Data appended or changed as part of aMERGE DML statement
  • UPDATE DML statement
  • DELETE DML statement
  • Loading data into BigQuery
  • Streaming ingestion
  • TRUNCATE TABLE DML statement
  • Jobs configured with awriteDisposition ofWRITE_TRUNCATE
  • Individualtable partition deletions

Pricing and costs

Calling change history functionsincursBigQuery compute costs.Both theAPPENDS andCHANGES functions require processing all data writtento the table within the specified time range. This processing applies to allwrites, including both append and mutation operations.Setting a table'senable_change_history option toFALSE does not reduce the data processed byAPPENDS.

When you set theenable_change_history optionon a table toTRUE in order to use theCHANGES function,BigQuery stores table change metadata. This stored metadataincurs additionalBigQuery storage costsandBigQuery 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, especiallylarge deletions, are the most likely to incur noticeable costs.