Actiknow
Data Engineering

BigQuery Change History Functions: How to Build Auditable Incremental Pipelines

Learn how BigQuery APPENDS and CHANGES work, when to use each, and how to design incremental pipelines with safe checkpoints, reconciliation and auditability.

Data engineering architect presenting an auditable bigquery incremental pipeline architecture to enterprise technology leaders
Contents hide

Executive summary

A warehouse pipeline can report success every hour while quietly missing a correction made ten minutes after the original record arrived. A dashboard may show the right number today and the wrong number tomorrow because the transformation copied inserts but not updates or deletes. BigQuery’s change history functions offer a useful way to see what was appended or changed within a specified period, but they are not a substitute for an end-to-end data contract.

APPENDS returns rows added to a supported table during a time interval. CHANGES can expose inserts, updates and deletes on a regular BigQuery table when change history is enabled. Both use BigQuery commit timestamps rather than the business event’s timestamp. Their history is bounded by BigQuery time travel, and CHANGES has additional restrictions. The practical opportunity is to process smaller, well-defined windows instead of repeatedly scanning full tables, while keeping enough evidence to prove what downstream systems received.

The leadership question is not simply whether these functions are available. It is whether their retention, semantics, cost and failure modes match your reporting deadlines, recovery objectives and audit obligations.

What exactly do APPENDS and CHANGES return?

APPENDS: the additions, not the current state

APPENDS(TABLE table, start_timestamp, end_timestamp) returns rows appended by operations such as INSERT, supported MERGE inserts, load jobs and streaming ingestion. It includes _CHANGE_TYPE, which is INSERT, and _CHANGE_TIMESTAMP, the commit time. An inserted row can still appear in APPENDS after that row has been deleted. An UPDATE does not turn into a new APPENDS record. Therefore APPENDS is a change feed for additions, not a method for reconstructing the latest state of a mutable table.

That distinction matters for append-only event logs, landing tables and ingestion ledgers. If your source records are genuinely immutable and corrections arrive as new events, APPENDS may be sufficient. If a CRM order row can be edited in place, APPENDS alone cannot reliably maintain a replica of the current order table.

CHANGES: mutations, with important semantics

CHANGES(TABLE table, start_timestamp, end_timestamp) returns changes after the table’s enable_change_history option is set to TRUE. INSERT produces an INSERT record. DELETE produces a DELETE record. An UPDATE produces a DELETE record representing the old row and an UPDATE record representing the new row. The _CHANGE_IS_FOR_UPDATE flag distinguishes a DELETE emitted as part of an update from an actual deletion. Treating every DELETE as a business deletion would produce incorrect downstream results.

CHANGES also exposes _CHANGE_TIMESTAMP. This timestamp records when BigQuery committed a change, not when a customer placed an order or when a source system first created the record. A correction to a September invoice applied in November belongs to the November change window even though the invoice’s business date remains in September. A robust pipeline preserves both times.

Quick comparison for architects

Use APPENDS when your contract is ‘newly appended records’ and the table’s updates and deletes are irrelevant or prohibited. Use CHANGES when the contract is ‘the mutations needed to synchronize state’ and the source is a supported regular BigQuery table. Use an explicit source CDC system, durable event log or retained audit table when you need a longer history, cross-system provenance, guaranteed recovery beyond time travel, or change semantics unsupported by BigQuery change history.

Cloud data architects comparing bigquery appends and changes functions for incremental data processing

Both functions operate against the table’s current schema, which means schema changes can affect how older change rows are represented. Neither is a permanent, immutable compliance audit ledger. Google Cloud’s change-history documentation explains the supported operations, access requirements and pricing model.

Why leaders should care: three business failure modes

1. The dashboard looks fresh but is wrong

Imagine an order imported as pending, then updated to completed. An append-only replication job captures the pending row and never sees the change. The dashboard refreshes on schedule, yet completed revenue remains understated. A pipeline SLA based only on job success cannot detect this. The correct acceptance criterion is whether the downstream state agrees with the source for the required business keys and periods.

Bi reporting owner and finance analyst investigating an order revenue discrepancy caused by missed data changes

2. Corrections silently rewrite history

A finance adjustment to an old transaction can change a closed month’s total. CHANGES can help identify that the warehouse row changed, but it does not decide whether the business should restate a published KPI. That requires an explicit policy for provisional periods, closed periods, materiality thresholds, approval and communication. Technical capture and financial reporting governance are different responsibilities.

3. An outage exceeds the recoverable window

Suppose an hourly pipeline fails while its owner is away. If the last successful checkpoint falls outside the table’s time-travel window, the missing change history may no longer be queryable. Restarting from the old timestamp can fail rather than repair the gap. The design must then fall back to a snapshot, source replay or another durable log, followed by reconciliation. Recovery is an architecture decision, not a retry setting.

How to query BigQuery change history

Step 1: identify eligible tables and access

Start by classifying tables as append-only, mutable operational, transient staging or governed analytical marts. Check whether the source is a regular BigQuery table and whether it has unsupported features for CHANGES, including BigQuery CDC enabled on that table. Confirm the executing identity has bigquery.tables.getData. Tables with row-level policies can require table-administrator privileges to inspect history; column security still applies. Avoid granting broad administrative access just to make a pipeline run.

Step 2: enable CHANGES only where needed

ALTER TABLE `project.raw.orders` SET OPTIONS (enable_change_history = TRUE);

This is an illustrative statement, not an instruction to enable history indiscriminately across production. Enabling the option stores change metadata and can increase storage and compute costs. History from before enablement may be incomplete. Establish a change-control process, benchmark a representative high-churn table and verify downstream consumers before rolling out.

Step 3: query a bounded interval

SELECT order_id, status, amount, _CHANGE_TYPE AS change_type, _CHANGE_TIMESTAMP AS committed_at, _CHANGE_IS_FOR_UPDATE AS is_update_before_image FROM CHANGES(TABLE `project.raw.orders`, TIMESTAMP(‘2026-11-09 10:00:00+00’), TIMESTAMP(‘2026-11-09 11:00:00+00’)) ORDER BY committed_at, order_id;

The example reads a one-hour interval, with an inclusive start and exclusive end. The order-by clause makes review easier; it does not create a globally unique sequence number. Multiple changes may share a timestamp, so downstream processing must not assume _CHANGE_TIMESTAMP uniquely identifies an event. The CHANGES query window may be no longer than one day. Use smaller windows for operations and recovery.

Step 4: use APPENDS for an immutable landing table

SELECT event_id, event_type, event_time, _CHANGE_TIMESTAMP AS committed_at FROM APPENDS(TABLE `project.raw.event_log`, TIMESTAMP(‘2026-11-09 10:00:00+00’), TIMESTAMP(‘2026-11-09 11:00:00+00’));

This is appropriate only if the landing table’s business contract is append-only or if subsequent mutations are intentionally ignored. Do not confuse event_time, which the application supplies, with committed_at, which reflects the BigQuery write. A late-arriving event can have yesterday’s event_time and today’s committed_at. Incremental extraction based solely on event_time would miss that record; change-history extraction based on committed_at can capture the write.

A production architecture that is recoverable

Layer A: initial baseline and a controlled transition

A change feed does not automatically provide the complete current table. New downstream consumers normally need an initial snapshot or backfill plus incremental changes. Establish a documented snapshot boundary, record the source table identity and schema, and plan an overlap or reconciliation step so writes occurring around snapshot creation are not lost. Do not assume that running a snapshot query and then starting CHANGES creates an atomic handoff unless your implementation proves it.

For mutable tables, choose a stable business key and define how duplicates, null keys and key changes are handled. If the source has no reliable key, a simple MERGE cannot guarantee a correct replica. Fix the identity contract before promising synchronization.

Layer B: capture, persist and acknowledge

Use an orchestrator to select a bounded [start, end) window. Query the source history, then persist the resulting change batch into a durable staging or audit table with a batch identifier, source table, window boundaries, extraction timestamp, schema version, job ID and row count. Only after the batch has been durably written and validated should the extractor advance its checkpoint. A successful SELECT by itself is not a completed delivery.

Data platform engineer monitoring a durable bigquery change batch with extraction metadata and checkpoint controls

A useful control record includes pipeline_id, source_table, window_start, window_end, batch_id, extraction_job_id, extracted_rows, applied_rows, checksum_or_control_totals, status, started_at and completed_at. Store operational errors and recovery decisions separately. This ledger supports investigation, but it should be protected against unauthorized edits and retained according to the organization’s audit policy.

Layer C: deterministic application

For APPENDS, use event IDs or a documented composite key to deduplicate replays. For CHANGES, preserve the before-image and after-image relationship of updates, and apply changes according to a deterministic ordering strategy. A DELETE associated with an update should not be interpreted as a permanent removal of the business entity. If multiple mutations affect the same key within a window, validate whether the target needs every intermediate transition or only the final state.

One practical pattern is to keep the full extracted mutation batch for traceability, then derive a latest-state projection for downstream dashboards. This separates an audit-friendly record of what was observed from the operational table used by reports. If the downstream target cannot handle before-images, transform them explicitly rather than dropping them without a documented rule.

Layer D: publish only after reconciliation

Compare the staged batch with the applied result. At minimum, reconcile counts by change type, distinct business keys, duplicate rates, null keys and affected partitions. For financial data, also compare relevant monetary totals at a clearly defined business grain. Mark the window complete only after these checks pass. Consumers should be able to distinguish data that is current, delayed, provisional or under correction.

Checkpointing and replay: the most important implementation decision

A checkpoint should represent a successfully processed exclusive upper boundary, not merely the timestamp of the last row seen. An empty interval can be a valid completed interval. If a job crashes after writing target rows but before recording success, rerunning the same interval must not duplicate its effects. This is why idempotent MERGE logic, unique batch identities and transactional or carefully sequenced control updates matter.

Data reliability engineer reviewing checkpoint boundaries and replay procedures for a recoverable bigquery pipeline

Use fixed windows and persist their exact boundaries. Never construct the next start from MAX(_CHANGE_TIMESTAMP) alone: multiple rows can share a commit timestamp, and an equality boundary can skip changes. Replaying an overlapping interval is safer when writes are idempotent. Choose a small lag behind the wall clock if your ingestion pattern needs stabilization, but test the specific write method rather than assuming an arbitrary five-minute delay is universally safe.

When an extraction fails, retain the prior successful checkpoint, alert the owner and retry the same window. When the time-travel horizon is approaching, escalate before the source history expires. If the recoverable window is already lost, stop normal incremental advancement, rebuild from a trusted baseline or source log, and reconcile before declaring the feed healthy. Never silently jump the checkpoint forward.

Where change history stops being an audit trail

The phrase ‘auditable incremental pipeline’ can be misleading. CHANGES provides a useful record of table mutations within a short operational horizon. It does not automatically provide the identity of the human who authorized a business correction, the original upstream transaction, an immutable record retained for years, or evidence that a downstream consumer applied the change correctly. Those controls belong in source application logs, warehouse job metadata, access logs and a durable pipeline ledger.

Data governance and compliance specialists reviewing audit evidence, access controls and change history records

For regulated or financially material reporting, capture source identifiers, actor or service identity where legitimately available, approval references, effective business date, processing date, before/after values as required, and the version of the transformation that produced the output. Protect sensitive fields with least-privilege access and appropriate retention. Do not copy entire sensitive rows into an audit table merely because the function returns them.

Limits and operational risks to design around

Retention and window boundaries

Change-history results are limited to the table’s time-travel window. Standard tables commonly have a seven-day time-travel window, which can be configured shorter. CHANGES accepts a maximum interval of one day. Its end timestamp is exclusive, and a future end timestamp is invalid. These are operational limits: set extraction frequency, alerts and recovery targets accordingly. A monthly scheduled job cannot depend on a seven-day change-history feed to recover every intervening mutation.

Unsupported operations and object types

CHANGES works with regular BigQuery tables, not views, materialized views or external tables. It cannot be used with a table that has BigQuery change data capture enabled, and multi-statement transactions have restrictions. Clones and snapshots do not carry over the source table’s historical changes. Partition expiration deletions are not captured. APPENDS supports regular tables and certain Apache Iceberg managed tables but also has object and transaction limitations. Verify eligibility per table, not per project.

Streaming and timestamp consistency

Google documents timestamp consistency caveats for rows written through pending or buffered Storage Write API streams. Teams requiring repeatable reads should assess the recommended committed or default stream modes, or batch load jobs. A pipeline that assumes every streaming write has immediately stable change-history visibility can produce inconsistent replays. Test the exact ingestion mode under concurrent writes, updates and retries.

Schema evolution and cost

Both functions return the current table schema, so older rows may show NULL for columns added later. Version the target contract and test renames, dropped columns, type changes and nullable fields. Queries process data written during the requested window, including writes involving mutations. CHANGES also incurs change-metadata storage and compute overhead. Compare total cost against full refreshes, source CDC, managed replication and an explicit append-only audit table; the cheapest query is not necessarily the lowest-risk architecture.

An illustrative capacity and cost assessment

Consider a hypothetical order table with 12 million current rows and 80,000 mutations each day. A full hourly refresh scans the entire current table repeatedly, while an incremental approach examines writes in smaller windows. That does not prove a particular cost saving: scanned bytes, update size, write patterns, table layout, pricing model, storage overhead and downstream processing all affect the result. Run a controlled benchmark using actual billing and job statistics before making an ROI claim.

A sensible pilot records five measures: source history query bytes processed; additional storage attributable to change metadata; end-to-end latency from commit to usable report; reconciliation exceptions per million changed rows; and recovery time after an intentionally interrupted run. Evaluate these alongside operational complexity. A solution that saves compute but cannot recover from a weekend outage may be a poor choice for an executive reporting system.

A four-week rollout plan

Week 1: classify and define the contract

Inventory candidate tables, their write methods, schema evolution, mutation rates, source business keys, access controls and time-travel settings. Identify the consumers that need current state versus a history of events. Document freshness targets, maximum tolerated data loss, recovery time and audit retention. Select one noncritical but representative mutable table and one append-only table for comparison.

Week 2: prototype and test edge cases

Enable CHANGES only for the approved pilot table. Capture an initial baseline and query short windows. Test INSERT, UPDATE, DELETE, MERGE, repeated updates to the same key, late business events, schema changes and source writes during extraction. Confirm the _CHANGE_IS_FOR_UPDATE handling. Measure cost and latency against an existing full-refresh or source-CDC alternative.

Week 3: add controls and failure recovery

Implement durable batch storage, idempotent application, checkpoints, alerting and reconciliation. Intentionally interrupt the pipeline after extraction, after staging and after target application. Verify that replay does not duplicate records or advance the checkpoint prematurely. Simulate an expired retention window and document the baseline-rebuild procedure.

Week 4: operationalize with owners

Publish a runbook with on-call ownership, escalation thresholds, source-table changes requiring review and a tested rollback plan. Expose freshness and exception status to BI owners. Compare pilot results with acceptance criteria, then approve or reject expansion. Scale table by table rather than enabling change history across an entire warehouse by default.

Executive scorecard and go/no-go questions

Freshness: How long after a BigQuery commit is the change reflected in the consuming report? Completeness: What proportion of expected mutations were extracted and applied? Correctness: Do affected keys and business totals reconcile? Recoverability: Can the team replay a failed interval without duplication, and rebuild after history expires? Governance: Who can read change history and approve schema or policy changes? Economics: What are the measured query, metadata, storage and support costs?

Before production approval, require a documented answer to five questions. Can the chosen function capture every change type the business cares about? Is the longest plausible outage shorter than the recoverable history horizon, or is there a fallback? Are target writes replay-safe? Can a data owner explain a changed historical KPI? Is the change feed being used for synchronization, or is someone incorrectly treating it as a permanent audit record?

How this fits into an enterprise BI program

Change capture is one layer in a reporting system. The larger design still needs source integration, data modeling, semantic definitions, reconciliation and governed dashboard refreshes. Actiknow’s business intelligence services cover data integration, solution architecture, modeling, reporting and ongoing refresh mechanisms. Those disciplines are relevant when deciding whether a BigQuery-native change feed improves an existing analytics stack.

Where warehouse updates must flow into a custom application or another system, integration design matters as much as extraction. Actiknow’s custom solutions practice describes API integrations, database and data lake setup, and automation. The right architecture may combine a warehouse-native feed with an application event log or a source connector rather than forcing one mechanism to serve every downstream requirement.

Frequently asked questions

1. Are BigQuery APPENDS and CHANGES generally available?

Google Cloud documents both functions as available features. Check current product documentation and regional or object-specific limitations before deployment; availability does not remove eligibility or retention constraints.

2. Does APPENDS capture updates or deletes?

No. APPENDS captures appended rows. It does not reflect later UPDATE or DELETE operations against those rows. Use CHANGES or another mutation-aware approach when the target must mirror current state.

3. Do I have to enable change history for APPENDS?

No. APPENDS does not require enable_change_history. CHANGES does require that table option on a supported regular BigQuery table.

4. Can CHANGES replace Debezium or a database transaction log?

Not universally. CHANGES reflects mutations made to a BigQuery table. Source-side CDC can capture transactions before they reach the warehouse and may offer different ordering, provenance and retention guarantees. Compare where the authoritative change originates.

5. Can I use CHANGES for a permanent compliance audit?

Not by itself. The history is limited by time travel and does not contain every business approval or actor. Persist the necessary evidence in a governed, retained audit system.

6. Why does an update produce both DELETE and UPDATE?

The function represents the old row as a DELETE before-image and the new row as an UPDATE after-image. The _CHANGE_IS_FOR_UPDATE field helps distinguish an update’s before-image from a true deletion.

7. Can I run CHANGES for a whole month?

No. A single CHANGES call has a maximum one-day interval, and the underlying history is limited to the time-travel window. Process bounded windows and persist results if longer retention is required.

8. What happens when the pipeline is down longer than time travel?

Some required history may be unavailable. Recover from a source replay, durable mutation log or trusted snapshot and reconcile the rebuilt target. Do not simply advance the watermark past the gap.

9. Is _CHANGE_TIMESTAMP the same as the source event time?

No. It is a BigQuery commit timestamp. Keep source event time and business effective date separately when they matter to reporting.

10. Does an empty change window mean the pipeline failed?

Not necessarily. Zero changes can be legitimate. Record the completed interval and verify job success, permissions and source health rather than using row count alone as a success criterion.

11. Are these functions automatically cheaper than full refresh?

No. Cost depends on written data, mutation patterns, metadata storage, compute and downstream processing. Benchmark actual workloads and include operational support and recovery costs.

12. What should a CTO approve before production?

Approve the source eligibility assessment, data contract, key strategy, checkpoint and replay design, reconciliation rules, cost benchmark, retention fallback, access controls and named operational owner.

The practical takeaway

BigQuery APPENDS and CHANGES make it easier to ask what was written or changed within a recent window. The hard work is turning those results into a trustworthy, replayable and explainable data product. Use the functions when they fit the table and recovery contract; preserve the evidence you need beyond time travel; and measure correctness at the point where a business user consumes the result.

If your team is evaluating incremental warehouse pipelines or reconciling changing BI numbers, contact Actiknow to discuss the architecture, validation controls and operational requirements. A focused review of one representative pipeline is a sensible starting point.

Technical references

Google Cloud’s time series function reference documents APPENDS and CHANGES syntax, output columns and restrictions. Its change-history guide covers permissions, eligible operations and cost considerations. Review these pages against your current environment before adopting any example SQL.