Work with change history

BigQuery change history lets you track the history of changes to a BigQuery table. The change history for a table is exposed as a SQL table-valued function (TVF) that shows you particular types of changes made during a specified time range. This feature lets you 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.

APPENDS TVF

The APPENDS TVF returns a table of all rows appended to a table for a given time range. The following operations add rows to the APPENDS change history:

Syntax

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)
  • table: the BigQuery table name. It can not be a view, subquery, external table, materialized view, or wildcard table. This argument must be preceded by the word TABLE.
  • start_timestamp: a TIMESTAMP indicating the earliest time at which a change is included in the output. If it is NULL, all changes since the table creation are returned. If the table is created after start_timestamp, the actual table creation time is used instead. If the time is earlier than allowed by time travel, an error is returned. For standard tables, this window is seven days, but you may configure the time travel window to be less than that.
  • end_timestamp: a TIMESTAMP indicating the latest time, exclusive, at which a change is included in the output. If it is NULL, all changes made until the start of the query are included.

Return value

The APPENDS TVF returns a table with the following columns:

  • All columns of the input table at the time the query is run. If a column is added after end_timestamp, it appears with NULL values populated in any rows that were inserted before the addition of the column.
  • _CHANGE_TYPE: a STRING indicating the type of change that produced the row. For APPENDS, the only supported value is INSERT.
  • _CHANGE_TIMESTAMP: a TIMESTAMP indicating the commit time of the transaction that made the change.

Details

Records of inserted rows persist even if that data is later deleted. Deletions are not reflected in the APPENDS TVF. If a table is copied, calling the APPENDS TVF on the copied table returns every row as inserted at the time of table creation. If a row is modified due to an UPDATE operation there is no effect.

Examples

This example shows the change history returned by APPENDS as various changes are made to a table called Produce. This example might not work if completed over a duration longer than your time travel window. First, create the table.

CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

Next, insert two rows into the table.

INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);

View the full change history of appends. Use NULL values to get the full history within the time travel window.

SELECT
  product,
  inventory,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

The output is similar to the following:

+---------+-----------+-------------+--------------------------------+
| product | inventory | change_type | change_time                    |
+---------+-----------+-------------+--------------------------------+
| apples  | 10        | INSERT      | 2022-04-15 20:06:00.488000 UTC |
| bananas | 20        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| carrots | 30        | INSERT      | 2022-04-15 20:06:08.490000 UTC |
+---------+-----------+-------------+--------------------------------+

Next, add a column, insert a new row of values, update the inventory, and delete the "bananas" row.

ALTER TABLE mydataset.Produce ADD COLUMN color STRING;
INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple");
UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE;
DELETE mydataset.Produce WHERE product = "bananas";

View the new table.

SELECT * FROM mydataset.Produce;
+---------+-----------+--------+
| product | inventory | color  |
+---------+-----------+--------+
| apples  | 15        | NULL   |
| carrots | 35        | NULL   |
| grapes  | 45        | purple |
+---------+-----------+--------+

View the full change history of appends.

SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

The output is similar to the following:

+---------+-----------+--------+-------------+--------------------------------+
| product | inventory | color  | change_type | change_time                    |
+---------+-----------+--------+-------------+--------------------------------+
| apples  | 10        | NULL   | INSERT      | 2022-04-15 20:06:00.488000 UTC |
| bananas | 20        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| carrots | 30        | NULL   | INSERT      | 2022-04-15 20:06:08.490000 UTC |
| grapes  | 40        | purple | INSERT      | 2022-04-15 20:07:45.751000 UTC |
+---------+-----------+--------+-------------+--------------------------------+

The inventory column displays the values that were set when the rows were originally inserted into to the table. It does not show the changes from the UPDATE statement. The row with information on bananas is still present since the APPENDS TVF only captures additions to tables, not deletions.

Limitations

Change history is subject to the following limitations:

  • You can only view information about appends, not updates or deletions.
  • The data is limited to the time travel window of the table.