BigQuery MERGE Partition Pruning: Why CDC Merges Need a Partition Filter

A common BigQuery CDC setup: an activity table partitioned by day on created_at, with a UUID primary key. Every few minutes, the pipeline merges a few hundred changed rows into it. Without a partition filter, those merges can scan the full target table. We measured it: on a three-year, 51.5 GB version of that table, a 500-row merge read 5.67 GB without a partition filter and 94 MB with one.

The partitioning is there, but a MERGE that joins only on uuid gives BigQuery no partition filter to prune with. At a five-minute flush interval, that's 288 merges a day, and every one that scans the full table gets more expensive as the table grows.

Artie fixes this by adding the partition column to the merge condition. BigQuery can then prune unrelated historical partitions and read only the partitions the changed rows belong to.

The partitioning trap in BigQuery CDC

Here's the activity table:

CREATE TABLE analytics.activity (
  uuid STRING NOT NULL,
  created_at TIMESTAMP NOT NULL,
  status STRING,
  payload JSON
)
PARTITION BY DATE(created_at);

The application uses a random UUID in uuid as its primary key. That is normal: a UUID uniquely identifies a row, but it has no useful relationship to the date in which BigQuery stored that row.

A CDC pipeline still needs to apply inserts, updates, and deletes. In simplified form, an ordinary upsert merge might join on the primary key:

MERGE analytics.activity AS target
USING staging.activity_changes AS source
ON target.uuid = source.uuid
WHEN MATCHED THEN
  UPDATE SET status = source.status, payload = source.payload
WHEN NOT MATCHED THEN
  INSERT (uuid, created_at, status, payload)
  VALUES (source.uuid, source.created_at, source.status, source.payload);

This is correct for matching rows by uuid. But the join does not tell BigQuery which created_at partitions in the target table matter. A partition filter is a condition on the partition column that tells BigQuery which target partitions can contain the incoming rows. This merge has none, so BigQuery has nothing to prune with. Depending on the MERGE join and clauses, it may scan the full target table.

Partitioning improves BigQuery performance and cost only when the operation supplies a filter that the optimizer can use for partition pruning. Google documents that filters in a MERGE condition can be pushed down to the scan stage; the actual result depends on the join and the predicates used. A WHEN NOT MATCHED BY SOURCE clause without an applicable filter can require a scan of the entire target table.

Fivetran falls back to full-table scans for this schema

Fivetran's BigQuery partitioned-table documentation says that when the partition key is not based on the source primary key, it performs full-table scans to avoid writing duplicates across partitions. It states that it automatically optimizes writes when the partition key is based on the source primary key.

For many operational databases, that condition is restrictive:

• The source primary key is often a UUID or another identifier chosen for uniqueness, not time locality.
• The useful BigQuery partition column is often created_at, event_date, or a business date.
• The table may be large and frequently updated even when each merge applies only a few hundred changed rows.

The result: a table that's well-partitioned for analytics queries, with replication merges that still scan every historical partition.

How partition-aware merges work in Artie

Artie merges destination rows using the source table's primary key or keys. For a BigQuery table that is partitioned by a column such as created_at, you can also configure that partition column, and Artie adds a partition filter to the merge whenever the changed rows carry partition values.

For a daily time-partitioned table, Artie derives the distinct partition values among the changed rows and adds a target-side condition equivalent to:

ON target.uuid = source.uuid
AND DATE_TRUNC(target.created_at, DAY) IN ('2026-10-01', '2026-10-02')

The exact statement depends on the configured table and the rows being merged. If any of the changed rows has a null or missing partition value, Artie skips the distinct-values filter for that merge rather than risk excluding a row it can't place. Otherwise, the merge condition names the target partitions that contain the incoming changes. BigQuery can use that condition to avoid scanning unrelated historical partitions. Whether it does is the optimizer's call, not a guarantee, so confirm it in the job's bytes processed; the benchmark below is one table where it was. The merge Artie generates has no WHEN NOT MATCHED BY SOURCE clause, since deletes arrive as rows carrying a delete marker, so that clause's full-table scan doesn't come into play.

You turn it on per table in Artie's table settings. The steps are under Get started below.

Pick the partition granularity that matches your table

Artie supports hourly, daily, monthly, and yearly time partitioning for BigQuery. For each table, you choose a date or timestamp partition column and a granularity. Hourly is only offered for TIMESTAMP and DATETIME columns; a DATE column can be partitioned daily, monthly, or yearly, which is BigQuery's own rule.

For a new table, Artie creates the matching BigQuery partitioning expression, such as DATE(created_at) for daily or TIMESTAMP_TRUNC(created_at, MONTH) for monthly. For an existing partitioned table, the granularity you select must match the table's current BigQuery definition.

Artie then uses that same granularity in the MERGE predicate. A monthly table gets a month-level filter and an hourly table gets an hour-level filter, so BigQuery can target the partitions each merge actually touches instead of scanning history at the wrong resolution.

Grain shows up directly in bytes processed. In our benchmark (details in the economics section below), the same 500-change merge read 4 MB on a 150-day table with hourly partitions and 94 MB on the same table with daily partitions. On a three-year table, monthly partitions read 940 MB and yearly partitions read 8.31 GB, because a yearly predicate still reads all of the current year.

How to choose:

• Hourly: high-volume tables with a short retention window. Highest precision, but partitions accumulate quickly, and that collides with two BigQuery limits. A table can hold at most 10,000 partitions, and a single query or load job can modify at most 4,000. A backfill merge can touch the whole table, so the practical ceiling is the 4,000 figure: about 167 days of hourly partitions, five and a half months, versus about 11 years of daily ones. Use hourly only when the table's retention clearly fits inside that window.
• Daily: the default, and the right fit for most CDC tables.
• Monthly: long-lived tables or low daily volume, where daily partitions would be too numerous or too small to matter.
• Yearly: very long history with low monthly volume.

Changing granularity later means recreating the table, so pick with backfills and retention in mind, not just steady-state merge cost.

Granularity doesn't change the stability requirement covered in the next section. Artie uses the incoming value to identify the target partition, so if the value changes after insert, a row can move across partitions and leave a duplicate behind. Hourly is the easiest option to overuse. Unless a table clearly needs hour-level pruning, stay on daily.

Choose a partition column that doesn't change

A partition filter is an optimization only if it preserves the rows that can legitimately match. The partition column must be chosen with the table's mutation behavior in mind.

A created_at partition is often a good fit when the partition value is stable for a row: an update to an existing row keeps the same created_at, so Artie can identify the relevant target partition from the changed row.

Before enabling partition-aware merges, verify these questions for each table:

• Is the partition value stable? If an update can move a row to a different partition, test that the update doesn't leave a duplicate in the old partition.
• Do updates and deletes include the partition field? Artie needs the configured partition value in the change data to target the correct destination partition. Deletes are the one to check. For a Postgres source, the table needs REPLICA IDENTITY FULL. With the default replica identity, a delete carries only the primary key, so that merge falls back to running without the predicate.
• Does the destination use the same partition expression? The granularity you configure in Artie must match the table's BigQuery definition. A daily DATE(created_at) partition requires a date-level filter, and a monthly partition requires a month-level one.
• Can the table contain historical corrections? Historical updates are safe when the changed row carries its actual partition value. Do not replace a row-specific partition filter with a generic "recent records only" window unless that window is guaranteed to cover every possible update and delete.

The filter gives BigQuery the exact partition it needs to find each row, without scanning partitions that can't contain it.

Why this changes the BigQuery economics of CDC

On on-demand pricing, BigQuery bills merges by bytes processed, so a merge that reads two daily partitions instead of three years of them is generally billed for far fewer bytes. On capacity pricing, pruning reduces the slot work each merge needs. That can improve throughput and leave more capacity for other workloads, though the direct billing impact depends on your reservation and autoscaling setup.

We measured it. We ran the exact MERGE statement Artie generates against a real BigQuery project, on the activity table from above, with and without the partition predicate.

The setup. Three years of history: 1,095 daily partitions, 109.5M rows, 51.5 GB, about 47 MB per daily partition. Each merge applied 500 changes from just after midnight UTC (300 inserts, 150 updates, 50 deletes), which touch two daily partitions. Every run merged into a fresh clone of the table, and the numbers below are medians of 3-5 runs taken from BigQuery's INFORMATION_SCHEMA job statistics. Dollar figures use on-demand pricing at $6.25 per TiB. GB in this post are decimal, 10^9 bytes, straight from the byte counts in INFORMATION_SCHEMA, and BigQuery bills per binary TiB: 5.67 GB × 288 merges a day is about 1.5 TiB, or $9.28.

The result. The predicate cut bytes processed per merge by 60x.

Bytes processed were stable across runs. Slot time swung a lot from run to run, so treat the 25x there as directional. If you're on capacity pricing, slot time is the number to watch.

The daily-cost run wasn't only the easy case. Every sixth merge updated rows that were 400 and 900 days old, and one set of changes had a NULL created_at, which fell back to a full merge. Both arms finished with identical rows (109,506,001) and identical fingerprints.

Without the predicate, cost grows with history. The changes never changed, but the bill did. The same 500-row merge read 237 MB when the table held 30 days of data, 1.00 GB at 180 days, 1.95 GB at one year, and 5.67 GB at three years. With the predicate it read 94 MB at every size. Add that up for one table merged every five minutes on demand, and three years of merges come to roughly $5,200 without the predicate and about $175 with it.

Clustering by the primary key doesn't fix it. We also tried clustering the three-year table by uuid with no predicate. That brought bytes processed down to 3.82 GB, but BigQuery still read the whole table and used more slot time (221s vs 98s).

Where the predicate stops paying off. This is the honest trade. A merge with the predicate reads every column of each partition it touches, about 47 MB per daily partition here. A merge without it reads only the join columns, but from every partition. On this table the break-even is around 120 partitions in a single merge: changes deliberately spread across a full year read 17.1 GB with the predicate vs 5.67 GB without. A typical CDC merge touches one to three partitions, so the predicate is the right default. If a workload regularly updates rows scattered across hundreds of partitions in one merge, check bytes processed before and after rather than assuming.

End to end through Artie. Pushing changes from Postgres through Artie into BigQuery, the predicate took median flush time on this table from 8.2s to 6.9s over 13 flushes per arm. The bigger win is cost, not latency, because a two-partition merge is already fast.

Two things to take from this. With the predicate, merge cost tracks the partitions a merge touches, not the table's history. And the gap widens with history and row width: without the predicate every merge reads the join columns of every row, so a table of the same shape at 1 TB, 20x this one, would read on the order of 20x more per merge. Other shapes scale differently, so measure yours. The numbers above are one table and one shape of change, so check bytes processed in INFORMATION_SCHEMA.JOBS for your own tables before and after.

Want to check the numbers yourself? The benchmark is public at artie-labs/benchmarks: the table generator, the exact MERGE statements, and a run script that records bytes processed from INFORMATION_SCHEMA, so you can rerun it against your own BigQuery project.

Get started

If you replicate mutable database tables into BigQuery, start with the tables whose merge jobs process the most bytes or take the longest to complete.

For each one:

1. Identify a stable partition column, and the granularity (hourly, daily, monthly, or yearly) that matches the BigQuery table's partitioning. For a new table, daily is the default.
2. Confirm that changed rows contain the partition value needed to locate the target row.
3. In Artie's table settings, turn on Use a merge predicate, select the partition column, and set its matching granularity.
4. Compare BigQuery job statistics before and after deployment, including bytes processed, partitions scanned, duration, and replication freshness.

Read the BigQuery partitioning guide for the configuration steps, or talk with the Artie team about partitioning a production CDC workload.

Frequently asked questions

Does partitioning automatically make a BigQuery MERGE cheap?

No. A partitioned table gives BigQuery a way to avoid reading irrelevant data, but the MERGE must contain filters or join conditions the optimizer can use to identify the relevant partitions. The join type and the full set of WHEN clauses affect whether pruning is possible. Inspect the query plan and job statistics rather than assuming partitioning was used.

Is this only useful when the primary key is a UUID?

No. UUID primary keys make the issue easy to see because they do not encode date locality. The same pattern applies whenever the primary key used for row identity differs from the column used to partition the BigQuery table.

Can we use a "last 30 days" condition instead?

Only if the source's data model guarantees that all rows that can be updated or deleted fall within that window. A row-specific partition filter is safer because it targets the partition indicated by the change record. A fixed recency window can miss a legitimate historical correction and create an incorrect replica.

Is this a BigQuery-only idea?

No. Aligning merge work with physical layout is a general warehouse optimization. It matters most in BigQuery because partition pruning is explicit in MERGE behavior and bytes processed are directly relevant to on-demand query cost. Artie also supports additional merge predicates for destination layouts such as Snowflake clustering, but the BigQuery setting described here is specifically for its supported partitioned-table merge path.