dbt MERGE Strategy in BigQuery: Why It's Scanning Your Whole Table

dbt incremental models using the merge strategy in BigQuery can silently scan your entire target table on every run — not just the new data. If your incremental model is growing more expensive over time despite processing the same volume of new rows, this is almost certainly why. Here is how to detect it, fix it, and stop it recurring.

The merge strategy is dbt's default incremental strategy on BigQuery. It looks efficient on paper: only new or changed rows are processed. But BigQuery's MERGE statement has a specific cost model that catches almost every team off guard. When BigQuery executes a MERGE, it must scan the entire target table to evaluate which rows match the join condition — regardless of how small the incoming dataset is. If your unique_key is not the partition column, and there is no partition filter on the MERGE condition, you are paying for a full table scan on every single run.

In our work with early-stage fintech and payments companies, this is one of the most common and most expensive silent cost bleeders we encounter. A team builds an incremental model, watches it run cheaply for the first few weeks, and then sees their BigQuery bill double quarter-on-quarter as the target table grows — without any change to the pipeline logic.

Why Does dbt MERGE Scan the Whole Target Table in BigQuery?

BigQuery charges for bytes read, not rows written. When dbt executes a MERGE statement, the generated SQL joins the incoming staged data against the existing target table to find matching rows. That join — the lookup side — requires BigQuery to read the target table in full.

Here is what dbt generates under the hood for a standard merge incremental strategy:

MERGE `project.dataset.my_incremental_model` AS DBT_INTERNAL_DEST
USING (
  SELECT * FROM staging_data
) AS DBT_INTERNAL_SOURCE
ON DBT_INTERNAL_SOURCE.id = DBT_INTERNAL_DEST.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

Notice what is missing: there is no partition filter on DBT_INTERNAL_DEST. BigQuery has no way to skip any partitions on the target table when evaluating the ON clause. The issue is that the MERGE condition pulls in far more data than will be passed to the output — and BigQuery still bills you for it, since it had to scan and read that data.

This is not a dbt bug. It is an unavoidable consequence of how BigQuery prices MERGE operations — and it compounds aggressively as your table grows.

How to Detect the Problem in Your BigQuery Project

The fastest way to confirm this is happening is to query your job history directly from INFORMATION_SCHEMA. Run this against your BigQuery project:

SELECT
  job_id,
  statement_type,
  REGEXP_EXTRACT(query, r'MERGE `([^`]+)`') AS target_table,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 3), 2) AS gb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 4) AS estimated_cost_usd,
  creation_time
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  statement_type = 'MERGE'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
ORDER BY
  total_bytes_processed DESC
LIMIT 20;

Replace region-eu with your actual BigQuery region (e.g. region-us). This surfaces your twenty most expensive MERGE statements from the last 30 days, alongside a rough on-demand cost estimate using the standard $6.25 per TB rate.

If the gb_processed column shows numbers close to your total target table size — rather than just the size of your new staged data — you have confirmed the problem. A target table that is 200 GB but receives only 1 GB of new data each day should not be processing 200 GB per run. If it is, you are overpaying by a factor of roughly 200x on that MERGE.

To cross-reference which dbt models are generating these MERGEs, join against the query text:

SELECT
  job_id,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 3), 2) AS gb_processed,
  creation_time
FROM
  `region-eu`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  statement_type = 'MERGE'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND total_bytes_processed > 10 * POW(1024, 3)  -- flag anything over 10 GB
ORDER BY
  total_bytes_processed DESC;

Look for model names embedded in the target table path — dataset.stg_, dataset.fct_, dataset.int_ — to map back to your dbt project.

Running those queries table by table works fine for a quick investigation. Checking every incremental model across a whole project is a different task — fintel-scan is a free open-source CLI (MIT licensed) that automates this check and fourteen others locally, without needing a warehouse connection: uvx fintel-scan.

The Fix: Partition Filters on the MERGE Condition

The correct fix is to ensure the MERGE condition includes a partition filter so BigQuery can prune the target table before evaluating row matches. dbt exposes this through the partition_by and incremental_predicates configuration.

Here is the before state — a standard incremental model configuration that causes full scans:

{{ config(
  materialized = 'incremental',
  unique_key = 'transaction_id',
  incremental_strategy = 'merge'
) }}

SELECT
  transaction_id,
  user_id,
  amount,
  created_at
FROM {{ ref('stg_transactions') }}
{% if is_incremental() %}
  WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
{% endif %}

And here is the fixed version, using partition_by and incremental_predicates to constrain the MERGE target:

{{ config(
  materialized = 'incremental',
  unique_key = 'transaction_id',
  incremental_strategy = 'merge',
  partition_by = {
    'field': 'created_at',
    'data_type': 'timestamp',
    'granularity': 'day'
  },
  incremental_predicates = [
    'DBT_INTERNAL_DEST.created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)'
  ]
) }}

SELECT
  transaction_id,
  user_id,
  amount,
  created_at
FROM {{ ref('stg_transactions') }}
{% if is_incremental() %}
  WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
{% endif %}

The incremental_predicates clause is the critical addition. It tells dbt to append an AND condition to the MERGE's ON clause that references DBT_INTERNAL_DEST — the target table. Because the target table is partitioned on created_at, BigQuery can now apply partition pruning and skip all partitions outside the specified range.

Google's own benchmarks demonstrate up to 7x faster scanning and 80% cost reduction with correct partition filters — and on MERGE operations specifically, the gains compound because you are eliminating redundant target table reads on every run.

A few hard rules to follow when implementing this:

• The incremental_predicates field must reference DBT_INTERNAL_DEST — not your source alias
• The predicate column must match your partition column exactly, or partition pruning will not fire
• The lookback window in incremental_predicates should match (or slightly exceed) the window in your is_incremental() filter — using a tighter window risks silently missing late-arriving records
• Never use a non-deterministic function like CURRENT_TIMESTAMP() inside a predicate that you intend to cache — BigQuery will re-evaluate it on every run

For a deeper look at how late-arriving data interacts with these windows, see Late-Arriving Data in dbt BigQuery: Fix Silent Metric Corruption.

When MERGE Is the Wrong Strategy Entirely

Sometimes the right answer is not to fix the MERGE configuration — it is to switch strategies. If your use case meets one of these conditions, consider an alternative:

Use insert_overwrite instead when:

• Your data is append-only and true deduplication on a unique_key is not required
• You are processing event or log data where the natural unit is a partition (a day's events), not an individual row
• Your table receives a predictable, bounded slice of data per run (e.g. yesterday's partition)

insert_overwrite replaces the relevant partitions wholesale. It does not need to read the existing target data at all — it simply overwrites the specified partition. This makes it structurally immune to the full-scan problem that plagues MERGE.

{{ config(
  materialized = 'incremental',
  incremental_strategy = 'insert_overwrite',
  partition_by = {
    'field': 'event_date',
    'data_type': 'date',
    'granularity': 'day'
  }
) }}

SELECT
  event_id,
  user_id,
  event_type,
  DATE(event_timestamp) AS event_date
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY)
{% endif %}

For fact tables downstream of event streams — page views, transactions, API calls — insert_overwrite will typically be both cheaper and faster than merge, and it sidesteps the target-scan problem entirely.

For tables where deduplication genuinely matters (e.g. a customer dimension, a subscription state table), MERGE with incremental_predicates is the right tool. The fix is configuration, not strategy replacement.

For cost attribution context across your whole project, see BigQuery INFORMATION_SCHEMA Cost Attribution: Stop the Guessing for a broader diagnostic framework.

How to Prevent This From Recurring

Detecting this once is not enough — new models get added, lookback windows drift, and incremental_predicates gets dropped during refactors. Build the following into your standard workflow:

1. Make partition_by mandatory for any incremental model above a size threshold. Enforce this with a dbt meta tag check or a pre-commit hook that fails if an incremental model over 1 GB lacks a partition_by config.

2. Add a dbt test that confirms partition pruning is active. You cannot test this from dbt tests directly, but you can run the INFORMATION_SCHEMA query above as part of a post-deployment CI check and alert if any incremental MERGE processes more bytes than the expected lookback window should read.

3. Set a bytes-processed budget per dbt run. BigQuery supports custom quotas on bytes processed per project per day. For development and staging environments, setting a ceiling (e.g. 100 GB/day) will surface runaway MERGE models before they reach production scale.

4. Review incremental models after table growth milestones. A model that was cheap at 10 GB may be the top cost driver at 500 GB. Build a monthly INFORMATION_SCHEMA review into your data team's operating rhythm — the query at the top of this post takes under two minutes to run.

A pattern we see repeatedly in our work with growth-stage companies: an analytics engineer writes a perfectly reasonable incremental model early in the company's life, it runs well for months, and then silently becomes the single most expensive query in the project as the business scales. By the time someone notices the bill, the model has accumulated years of data in the target table and the fix requires a full-refresh rebuild — which, at 500 GB, carries its own cost.

Catch it now, while the table is small. The diagnostic query takes five minutes. The config fix takes ten.

Frequently Asked Questions

Q: Does the dbt MERGE strategy always scan the full target table in BigQuery?

A: Yes, by default — unless you configure incremental_predicates that reference a partition column on the target table (DBT_INTERNAL_DEST). Without a partition filter on the MERGE condition, BigQuery must read the entire target table to evaluate row matches, regardless of how little new data is being processed.

Q: What is the incremental_predicates config in dbt and when should I use it?

A: incremental_predicates is a dbt BigQuery config that appends additional conditions to the ON clause of a generated MERGE statement. You should use it whenever your target table is partitioned and you want to restrict the MERGE to a specific partition window, preventing full table scans. It is essential for any partitioned incremental model using the merge strategy on a table larger than a few GB.

Q: When should I use insert_overwrite instead of merge in dbt BigQuery?

A: Use insert_overwrite for append-only data where you do not need row-level deduplication — event streams, API logs, page views, and similar. It replaces entire partitions rather than performing a row-level merge, so it never needs to read the existing target data. This makes it structurally cheaper and faster for high-volume time-series data.

Q: How do I find which dbt incremental models are causing expensive MERGE scans in BigQuery?

A: Query INFORMATION_SCHEMA.JOBS_BY_PROJECT filtering on statement_type = 'MERGE' and order by total_bytes_processed. This shows you which MERGE statements are reading the most data. Cross-reference the query field to identify the target table and map it back to your dbt model.

Q: Will adding incremental_predicates fix an existing large table, or do I need a full refresh?

A: Adding incremental_predicates fixes future runs immediately — no full refresh is required. However, if your table is not yet partitioned, you will need to run dbt run --full-refresh to rebuild it with the new partition_by config before partition pruning can take effect. Plan for this carefully on large tables, as the full-refresh itself will be expensive.

At Fintel Analytics, we have diagnosed and fixed this exact problem for payments companies, fintech lenders, and SaaS businesses whose BigQuery bills were climbing quarter-on-quarter with no obvious cause — the culprit was almost always an incremental MERGE model that had quietly outgrown its original design. If your data team is watching costs rise and cannot pinpoint why, that is a problem we know how to solve, and solving it typically pays back the engagement cost within weeks.