Change history in BigQuery

Ever needed to track what changed in a table and when? In data engineering, this is known as Change Data Capture (CDC)—a fundamental challenge when dealing with evolving datasets.

Now, the Change History features in BigQuery sound pretty interesting.

BigQuery SQL has had the APPENDS table-valued function (TVF) for some time now, which works well for append-only scenarios. But it didn’t capture updates or deletes.

A few months ago a CHANGES TVF was added, which provides visibility into UPDATE and DELETE operations.

Unlike APPENDS (which works right out of the box), you need to enable change history tracking manually either at table creation or with an ALTER TABLE ... SET OPTIONS() command.

To illustrate how it all works I've:
1️⃣ Created a table 2️⃣ Inserted a row 3️⃣ Updated a row

INSERT learning.customers

SELECT 9 AS id,
       'George' AS first_name,
       'Matthews' AS last_name,
       'Australia' AS country;

UPDATE learning.customers

SET country = 'UAE'

WHERE id = 4;

ALTER TABLE learning.customers SET OPTIONS (enable_change_history = TRUE);

SELECT * FROM APPENDS (
  TABLE `learning.customers`,
  TIMESTAMP '2025-03-06 14:06:45',
  CURRENT_TIMESTAMP())

SELECT * FROM CHANGES (
  TABLE `learning.customers`,
  TIMESTAMP '2025-03-06 14:06:45',
  TIMESTAMP '2025-03-06 14:11:00') -- needs to be >10 minutes before NOW
  ORDER BY _CHANGE_TIMESTAMP

As you will be able to see:
✅ APPENDS captures new rows only.
✅ CHANGES logs updates too (as a DELETE + INSERT).

Key things to note:

⚠️ Both features are still in preview, so not production-ready.
💰 Querying this data still incurs processing costs.
⏳ CHANGES only tracks modifications older than 10 minutes.
📦 Enabling Change History means extra storage costs for metadata.

Has anyone tried using these in real-life scenarios?