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:CHANGES
: returns all rows that have changed in a table for a given time range. To use theCHANGES
function on a table, you must set the table'senable_change_history
option toTRUE
.The following operations add rows to the
CHANGES
change history:CREATE TABLE
DDL statementINSERT
DML statement- Data appended or changed as part of a
MERGE
DML statement UPDATE
DML statementDELETE
DML statement- Loading data into BigQuery
- Streaming ingestion
TRUNCATE TABLE
DML statement- Jobs configured with a
writeDisposition
ofWRITE_TRUNCATE
- Individual table partition deletions
Pricing and costs
Calling change history functions incurs BigQuery compute costs.
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 BigQuery storage 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.