Incremental Pipelines in BigQuery: APPENDS, CHANGES, and MERGE Patterns That Stop Rescanning History

Most BigQuery pipelines start life as a full rebuild: CREATE OR REPLACE TABLE analytics.orders AS SELECT ... FROM raw.orders. It is simple, it is correct, and at 50 GB it costs nothing worth discussing. At 5 TB, run hourly, it becomes the single largest line on your bill — and the reason your ELT window keeps sliding into the business day.

This tutorial covers the four mechanisms BigQuery gives you for processing only what changed: the APPENDS and CHANGES table-valued functions, watermark-driven incremental MERGE, and Dataform incremental tables. It also covers the traps — late-arriving data, duplicate deliveries, and the streaming buffer — that quietly corrupt naive incremental logic.

1. Decide what "changed" means

Before writing SQL, classify the source table:

Source shape	What changed means	Best mechanism
Append-only event log	New rows since a watermark	APPENDS TVF or partition filter
Mutable dimension (CDC target, MERGEd)	Inserts, updates, and deletes	CHANGES TVF or CDC column comparison
Batch-loaded daily partitions	Whole partitions	Partition-scoped MERGE / INSERT OVERWRITE pattern
Externally managed (Iceberg, BigLake)	New snapshots	Snapshot metadata, then partition filter

Getting this wrong is the root cause of most "our incremental model drifts from the full refresh" incidents.

2. APPENDS: read only the rows that arrived

For append-only tables, the APPENDS change-history TVF returns rows inserted within a time range, without scanning the whole table:

SELECT
  order_id,
  customer_id,
  order_total,
  _CHANGE_TIMESTAMP AS appended_at
FROM APPENDS(
  TABLE `proj.raw.orders`,
  -- start: exclusive lower bound on insert time
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 MINUTE),
  -- end: NULL means "up to now"
  NULL
);

Notes that matter in production:

• The start timestamp must be inside the table's time-travel window (7 days by default, configurable from 2 to 7 days). Miss the window and the query errors rather than silently returning nothing — which is the behaviour you want.
• APPENDS sees rows from the Storage Write API, streaming inserts, INSERT, and load jobs. It does not give you updates or deletes.
• _CHANGE_TIMESTAMP is when BigQuery committed the row, not when the event happened. Use it for pipeline bookkeeping and your own event_time for business logic.

3. CHANGES: inserts, updates, and deletes

For mutable tables, enable change history first:

ALTER TABLE `proj.raw.customers`
SET OPTIONS (enable_change_history = TRUE);

Then:

SELECT
  customer_id,
  email,
  segment,
  _CHANGE_TYPE,       -- 'INSERT', 'UPDATE_AFTER', 'DELETE', ...
  _CHANGE_TIMESTAMP
FROM CHANGES(
  TABLE `proj.raw.customers`,
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR),
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE)
)
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id ORDER BY _CHANGE_TIMESTAMP DESC
) = 1;

Two details people trip over. First, CHANGES requires an end timestamp strictly in the past — roughly ten minutes of margin is the documented safety band — because very recent changes may not be fully materialised. Second, change history is only recorded from the moment you enable the option, so backfill with a one-off full pass before you cut over.

The QUALIFY clause collapses multiple changes to the same key down to the latest one, which is exactly what a MERGE target wants.

4. The watermark MERGE pattern

TVFs are convenient, but a watermark table is the pattern that survives replays, backfills, and outages. Keep pipeline state in BigQuery itself:

CREATE TABLE IF NOT EXISTS `proj.meta.watermarks` (
  model_name   STRING NOT NULL,
  watermark_ts TIMESTAMP NOT NULL,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

Then run the load inside a scripted transaction so the watermark can never advance past data you failed to write:

BEGIN
  DECLARE low_water  TIMESTAMP;
  DECLARE high_water TIMESTAMP DEFAULT TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE);

  SET low_water = (
    SELECT COALESCE(MAX(watermark_ts), TIMESTAMP '2024-01-01')
    FROM `proj.meta.watermarks`
    WHERE model_name = 'fct_orders'
  );

  BEGIN TRANSACTION;

  MERGE `proj.analytics.fct_orders` AS t
  USING (
    SELECT * FROM (
      SELECT
        order_id,
        customer_id,
        order_total,
        order_status,
        ingested_at,
        ROW_NUMBER() OVER (
          PARTITION BY order_id ORDER BY ingested_at DESC
        ) AS rn
      FROM `proj.raw.orders`
      -- partition pruning: the whole point of the exercise
      WHERE ingested_at >  low_water
        AND ingested_at <= high_water
    )
    WHERE rn = 1
  ) AS s
  ON  t.order_id = s.order_id
  -- prune the TARGET too, or BigQuery scans every partition of it
  AND t.ingested_at >= TIMESTAMP_SUB(low_water, INTERVAL 3 DAY)
  WHEN MATCHED AND s.ingested_at > t.ingested_at THEN UPDATE SET
    customer_id  = s.customer_id,
    order_total  = s.order_total,
    order_status = s.order_status,
    ingested_at  = s.ingested_at
  WHEN NOT MATCHED THEN INSERT ROW;

  MERGE `proj.meta.watermarks` AS w
  USING (SELECT 'fct_orders' AS model_name, high_water AS watermark_ts) AS n
  ON w.model_name = n.model_name
  WHEN MATCHED THEN UPDATE SET watermark_ts = n.watermark_ts, updated_at = CURRENT_TIMESTAMP()
  WHEN NOT MATCHED THEN INSERT (model_name, watermark_ts) VALUES (n.model_name, n.watermark_ts);

  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  ROLLBACK TRANSACTION;
  RAISE USING MESSAGE = FORMAT('fct_orders incremental failed: %s', @@error.message);
END;

Four things this gets right that hand-rolled versions usually get wrong:

1. Both sides are pruned. A MERGE predicate on the source only halves the work; without a filter on the target's partitioning column, BigQuery reads the entire target table. This is the single most common reason an "incremental" model still scans terabytes.
2. Deduplication happens before the merge. MERGE raises an error if the source contains two rows matching the same target row, so at-least-once delivery from Pub/Sub or the Storage Write API will break you eventually. ROW_NUMBER() (or QUALIFY) fixes it.
3. The high watermark lags by ten minutes. Rows still in flight would otherwise fall between two runs and never be picked up.
4. A late-arrival allowance. The three-day lookback on the target absorbs events that arrive after their partition has already been processed. Size it from evidence — measure the real distribution of ingested_at - event_time before choosing a number.

5. Let Dataform do the bookkeeping

If you already run Dataform (or dbt), express the same pattern declaratively instead:

config {
  type: "incremental",
  uniqueKey: ["order_id"],
  bigquery: {
    partitionBy: "DATE(ingested_at)",
    clusterBy: ["customer_id"],
    updatePartitionFilter: "ingested_at >= timestamp_sub(current_timestamp(), interval 3 day)"
  }
}

SELECT order_id, customer_id, order_total, order_status, ingested_at
FROM ${ref("raw_orders")}

${when(incremental(), `WHERE ingested_at > (SELECT MAX(ingested_at) FROM ${self()})`)}

updatePartitionFilter is the part teams omit, and it is what supplies the target-side pruning from point 1 above. With uniqueKey set, Dataform generates the MERGE for you; without it, you get an append and duplicates.

6. Prove the saving, then prove correctness

Measure before and after from INFORMATION_SCHEMA:

SELECT
  DATE(creation_time) AS day,
  COUNT(*) AS runs,
  ROUND(SUM(total_bytes_processed)/POW(1024,4), 2) AS tib_processed,
  ROUND(SUM(total_slot_ms)/1000/3600, 1) AS slot_hours
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND statement_type = 'MERGE'
  AND query LIKE '%fct_orders%'
GROUP BY day
ORDER BY day;

Then add a scheduled reconciliation that compares the incremental table against a full rebuild over a bounded window — row counts and a checksum of the measures, per day, for the last 30 days. Any incremental pipeline without a reconciliation query is a pipeline whose drift you will discover from a finance dashboard instead of a test.

A few remaining caveats: rows in the streaming buffer cannot be modified by DML, so a MERGE that targets very fresh streamed data may fail or miss rows — keep the ten-minute lag. MERGE on tables with require_partition_filter = TRUE needs the filter in the ON clause, not only in the source subquery. And if your target is an Iceberg or BigLake managed table, check the DML support matrix for your table type before assuming MERGE behaves identically.

---

Rebuilding everything, every hour, is a decision — usually an unexamined one. If your ELT bill is dominated by a handful of full-refresh models, the incremental patterns above typically cut bytes processed by one to two orders of magnitude while leaving results identical.

Need help? BQBrains builds and repairs BigQuery ELT pipelines — incremental model design, MERGE and CDC correctness reviews, Dataform rollout, and cost reduction. Get in touch with your current run times and bill, and we will tell you where the savings are.