Why dbt MERGE jobs scan your whole BigQuery table

dbt's incremental merge strategy looks like the grown-up way to load a fact table. The model SQL filters the source to the last day or two, unique_key is set, and the PR description says incremental. But the compiled job that actually runs in BigQuery is still a MERGE, and a MERGE has two reads that matter: the source you filtered, and the target you have to match against. If that match never becomes a partition predicate on the target READ, BigQuery rewrites every partition of events (4 TB) to apply 40 GB of new rows.

MERGE `proj.analytics.events` T
USING (
  SELECT * FROM `proj.staging.events`
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
) S
ON T.event_id = S.event_id
WHEN MATCHED THEN UPDATE SET T = S
WHEN NOT MATCHED THEN INSERT ROW;
-- Source is 40 GB. Target READ has no event_date predicate.
-- job_stages: records_read on T ≈ the whole table.

Finance sees a cost spike and engineering points at the dbt YAML; both are right from where they sit. The incremental filter lived on the source SELECT, but the target was a heap as far as the execution plan was concerned. The warehouse does not read your intent. It reads the ON clause, the WHEN MATCHED predicates, and whatever storage-level filter those expressions can push to the destination table.

This is the same meter as a missing partition filter on a SELECT, just spelled as DML. On-demand BigQuery bills tebibytes scanned; capacity BigQuery burns slot-ms while the rewrite shuffles. Either way, a nightly 4 TB MERGE is often the largest single job in the project, hiding in the transform layer because nobody reviews DML with the suspicion they reserve for dashboard SQL. The usual path there is a fact table that started as a full refresh, got switched to incremental to save time, picked up a date filter on the source CTE, and kept unique_key as event_id without ever adding the partition column to the merge predicate. For a few months the table is small enough that this goes unnoticed. Then it is 4 TB, and a job that used to take eight minutes takes an hour and a four-figure monthly line on the invoice.

{{ config(
  materialized = 'incremental',
  unique_key = 'event_id',
  incremental_strategy = 'merge',
  partition_by = {'field': 'event_date', 'data_type': 'date'}
) }}

SELECT * FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
{% endif %}
-- is_incremental() filters the source.
-- unique_key is not the partition column.
-- Compiled MERGE still matches T on event_id only.

Incremental is a contract with the warehouse, not a comment in a model. Until the target READ in job_stages shows a filter on the partition column, you are paying for a full table scan that happens to be spelled MERGE. The rest of this post covers how to see that in the plan, how SlashPeak attributes it, and how to stop rewriting 4 TB for 40 GB of new rows.

SlashPeak does not grep the MERGE text for a date column. MERGE, UPDATE, and DELETE jobs are owned by the MERGE_REWRITE rule, which inspects only the READ of the target table. A filter on the source CTE cannot hide a full rewrite of T: grep INFORMATION_SCHEMA.JOBS.query for event_date and you will often get false comfort, since the column sits on the source and the billed READ of the destination never saw it.

MERGE `proj.analytics.events` T
USING (
  SELECT * FROM `proj.staging.events`
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
) S
ON T.event_date = S.event_date
 AND T.event_id = S.event_id
WHEN MATCHED THEN UPDATE SET
  T.event_name = S.event_name,
  T.props = S.props
WHEN NOT MATCHED THEN INSERT ROW;
-- AND T.event_date = … can push to the target READ.
-- Combine with require_partition_filter = TRUE on events.

Jobs without plan data are skipped (a false positive on a 4 TB MERGE wastes an afternoon and trains the team to ignore findings), and detection also requires recurrence: a one-off MERGE that rewrote a table during a backfill is ugly, not a nightly habit. MERGE_REWRITE groups by the normalized query hash and requires a majority of runs in the window to look like a wholesale rewrite, either bytes processed near the size of the target table or a target READ with no partition filter. Matching jobs are then aggregated per query pattern, and the representative job, the one with the largest bytes billed, is the MERGE to open first.

The plan is the only honest layer, because BigQuery can constant-fold, push, or fail to push predicates in ways SQL text cannot show. A comparison that looks like T.event_date = S.event_date in the ON clause may still not reach storage if it is wrapped in a function, buried in an OR with an unscoped unique-key match, or applied only in WHEN MATCHED after the target has already been read. job_stages reports what storage actually filtered, which is the billed truth.

-- Looks scoped. Storage still reads every partition of T.
ON T.event_id = S.event_id
  OR T.event_date = S.event_date

-- Function on the partition column is the same miss as a SELECT.
ON DATE(T.event_ts) = S.event_date
 AND T.event_id = S.event_id

-- WHEN MATCHED filter is too late: T was already read.
WHEN MATCHED AND T.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  THEN UPDATE SET T = S

This is why a console screenshot of one job is not a cost program. You need the plan, the destination table, the table size from metadata, and enough history to know the MERGE is a schedule, not an accident. SlashPeak already joins those pieces from INFORMATION_SCHEMA; applying the predicate and watching the next scan is the verification, not the YAML.

A nightly DELETE FROM events WHERE event_date < DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) is a prune: storage can skip every partition still inside the retention window. DELETE FROM events WHERE user_id IN (SELECT user_id FROM gdpr_requests) on an unclustered 4 TB table is a full rewrite with a post-read filter instead. Same statement type, very different invoices, because the difference is whether the mutation key is also a storage key.

DELETE FROM `proj.analytics.events`
WHERE user_id IN (
  SELECT user_id FROM `proj.ops.gdpr_requests`
  WHERE requested_on = CURRENT_DATE()
);
-- No event_date predicate. Target READ is the heap.
-- 200 keys. 4 TB billed. Same meter as the nightly MERGE.

The same is true of UPDATE … SET status = 'closed' WHERE order_id IN (SELECT …): if order_id is not the partition or cluster key, you pay to read the heap, rewrite the matching rows, and often shuffle the rest of the partition. Clustering on the mutation key, or staging the keys into a partition-aligned table first, is the cheaper shape. GDPR and support-ticket mutations quietly become the second-largest DML line after the nightly MERGE. TRUNCATE is the exception, since it is a metadata operation rather than a scan, so MERGE_REWRITE does not own it. The dangerous cousins are UPDATE and DELETE that look surgical in SQL and wholesale in job_stages; if you only hunt MERGE because that is what dbt emits, you will miss the weekly GDPR DELETE scanning the same 4 TB table with a list of 200 user ids.

Staging keys is not ceremony. CREATE TEMP TABLE keys AS SELECT order_id, order_date FROM the request list, then DELETE WHERE order_date BETWEEN a bounded window AND order_id IN (SELECT … FROM keys). The date bound is what lets storage skip partitions; the temp table is what keeps the IN list from turning into a broadcast of a giant subquery that still cannot prune. If you do not have a date for the rows you must mutate, you do not have a cheap mutation, and you should say so in the runbook.

CREATE TEMP TABLE keys AS
SELECT order_id, order_date
FROM `proj.ops.gdpr_keys`;

DELETE FROM `proj.analytics.orders` T
WHERE T.order_date BETWEEN '2024-01-01' AND CURRENT_DATE()
  AND T.order_id IN (SELECT order_id FROM keys);
-- Partition predicate on T.order_date can skip old days.
-- Cluster on order_id if this DELETE is a weekly habit.

Treat UPDATE and DELETE with the same plan review you give MERGE: destination table, target READ filter, bytes versus table size, recurrence. SlashPeak already uses one rule for all three statement types so the backlog is a DML problem, not a MERGE-only superstition.

Find the MERGE, UPDATE, and DELETE jobs with the largest bytes billed. Confirm the target is partitioned and that the plan's target READ has a filter. If it does not, add the partition column to the ON or WHERE, set require_partition_filter on the destination, and re-scan. Do not start by raising the reservation; that pays more to rewrite the same heap.

For dbt models, incremental_predicates is the patch that matches how the compiler emits MERGE. unique_key alone will not prune a date-partitioned target. Put the lookback on DBT_INTERNAL_DEST.partition_column so the compiled ON or scan of the destination is bounded, then check the compiled SQL in target/compiled rather than just the model file, because that is what BigQuery actually runs.

If the table is not partitioned, you cannot predicate your way out of a rewrite. Partition it on the column the MERGE already uses as a lookback, cluster on the unique_key if mutations and SELECTs both filter it, and only then re-introduce incremental; a 4 TB unpartitioned target with a beautiful incremental_predicates list is still a 4 TB READ. Once the known producers compile with the new predicate, turn on require_partition_filter. Expect a short burst of failures from forgotten ad-hoc MERGEs: a failed job at 2am that names the missing column is cheaper than a silent full rewrite.

Re-scan the next day. The finding should drop or disappear. If it does not, you changed YAML that was not the billed READ, perhaps a different model, a different unique_key, or a WHEN MATCHED clause that still cannot push. Open the representative job in the finding, not the model you remember writing.

Napkin math is enough to get a VP's attention and wrong enough to need the scan. 4 TB scanned at the US multi-region on-demand list price of 6.25 dollars per TiB is about 23 dollars per run; nightly for a month is roughly 690 dollars for one model. If three incremental models do the same thing (events, orders, sessions), that is a couple of thousand dollars a month to rewrite heaps you already stored, before slot contention makes the 6am dbt run miss the dashboard SLA. The 40 GB of new rows is the number people remember, because it is the business change. The 4 TB is the number the invoice remembers: a ratio of roughly one hundred times more bytes than the increment, produced by a source filter that works next to a target filter that does not.

SELECT
  job_id,
  total_bytes_billed / POW(1024, 4) AS tib_billed,
  (total_bytes_billed / POW(1024, 4)) * 6.25 AS usd_on_demand,
  destination_table.dataset_id,
  destination_table.table_id,
  statement_type
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND statement_type IN ('MERGE', 'UPDATE', 'DELETE')
  AND total_bytes_billed > 0
ORDER BY total_bytes_billed DESC
LIMIT 20;
-- 4e12 bytes ≈ 3.6 TiB ≈ ~23 USD per run at US on-demand list.

Capacity pricing does not make this free. A MERGE that reads 4 TB and shuffles to apply updates still occupies slots for the duration, and if that job runs in the same reservation as Looker, the rewrite is also why Monday's forecast explore queued. Buying more slots pays twice: once for the rewrite, again for the reservation you sized around it.

Do not annualize a single backfill and call it a budget. A one-time 4 TB MERGE to rebuild after a schema change is a project cost; a 4 TB MERGE every night is a product defect. SlashPeak requires multiple occurrences and a majority-rewrite signal so the finding reflects the habit, not the backfill. Rank it against SELECT waste using the scan-window dollars, and use the 23-dollar-per-night figure only as the story you tell after you have confirmed recurrence.

-- After ON T.event_date = S.event_date AND T.event_id = S.event_id
-- job_stages READ of T should show a filter on event_date.
-- Bytes billed should land near:
--   (lookback days / 365) * table size
-- 3-day lookback on a 4 TB year of events ≈ 33 GB, not 4 TB.

If you need a stop-the-bleeding number this week, take bytes billed on the MERGE from JOBS, multiply by list price, multiply by runs per month. Compare that to bytes billed if only the lookback partitions were read, often a few percent of the table. The gap is the ticket, and the plan is how you prove the gap is real.

dbt's merge strategy compiles a MERGE that matches on unique_key, which is correct for row identity and wrong for partition pruning when unique_key is a surrogate. incremental_predicates exists because of this gap: it injects extra predicates on the destination alias (DBT_INTERNAL_DEST in current dbt) so the compiled SQL can push a date bound to the target READ.

{{ config(
  materialized = 'incremental',
  unique_key = 'event_id',
  incremental_strategy = 'merge',
  partition_by = {'field': 'event_date', 'data_type': 'date'},
  incremental_predicates = [
    "DBT_INTERNAL_DEST.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)"
  ],
  require_partition_filter = true
) }}

SELECT event_id, event_date, event_name, account_id, props
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
  WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)
{% endif %}

The predicate has to be something storage can use. DBT_INTERNAL_DEST.event_date >= date_sub(current_date(), interval 3 day) is the usual shape; wrapping the column in date() or comparing a different timestamp column is the same miss you already know from SELECT. If the lookback is 3 days, late-arriving facts outside that window will not update, which is a product choice, same as the 4 TB rewrite is a product choice. Pick the lookback in the open with the analytics owner, not as an unbounded MERGE because late data might exist.

insert_overwrite is a different compiled shape: it replaces whole partitions instead of matching rows. For a date-partitioned events table where the incremental batch is complete days, overwriting those partitions can be cheaper and simpler than MERGE. It is the wrong strategy if you need to update a handful of rows across many days, so switch when the grain of the load matches the grain of the partition, not just to dodge a missing predicate.

{{ config(
  materialized = 'incremental',
  incremental_strategy = 'insert_overwrite',
  partition_by = {'field': 'event_date', 'data_type': 'date'},
  partitions = ['date_sub(current_date(), interval 1 day)']
) }}

SELECT event_id, event_date, event_name, account_id
FROM {{ ref('stg_events') }}
WHERE event_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);
-- Replaces yesterday's partition. Does not MERGE-match the other 4 TB.

Read the compiled SQL: target/compiled/.../events.sql is the artifact that has to contain the destination predicate. If it does not, the YAML did not do what you think. Run the model in a dev project against a subset and inspect job_stages before you ship to prod, since a green dbt run that still shows records_read on T equal to the table is not a win. incremental_predicates is not a substitute for unique_key either; you need both, unique_key so MERGE knows which row to update, a partition predicate so it does not read the other 360 days to find it. SlashPeak's MERGE rule does not trust your dbt config, even with a dbt manifest connected. It reads the job that landed in BigQuery: compile, run, inspect the target READ, then re-scan.

event_id is a great unique_key and a terrible partition column: cardinality is roughly one per row, so clustering on it can help point lookups but partitioning on it cannot. A MERGE that only matches on event_id asks BigQuery to find those ids somewhere in 4 TB, and without a date bound, somewhere is everywhere.

Teams reach for unique_key because dbt docs lead with it, and because the grain of the fact table really is one event. The warehouse still needs a pruning key, and on events that key is almost always event_date. Put it in the ON clause next to the id, or in incremental_predicates, or both. Equality on the partition column plus equality on the id is the shape that can skip days and then skip blocks if you also cluster on account or id.

ON T.event_date = S.event_date
 AND T.account_id = S.account_id
 AND T.event_id = S.event_id
-- event_date: partition prune
-- account_id: cluster skip inside the day
-- event_id: row identity for WHEN MATCHED

Composite unique keys do not change the story: (account_id, event_id) is still not a date. If the table is partitioned by event_date, the MERGE must mention event_date in a way storage understands. If you partition by ingestion date and match on event_id, late events in old ingestion partitions will be missed unless your lookback covers them, another reason to say the lookback out loud. Clustering on unique_key helps the SELECT that filters one id; it does not turn an unscoped MERGE into a cheap one. Block-level min/max on a high-cardinality id can skip some blocks, but a MERGE that must find 40 GB of ids spread across a day still reads a lot of that day, and without a partition filter it still considers every day. Layout and predicates are both required; neither substitutes for the other.

When you review a dbt incremental model, read three fields before the SQL: partition_by, unique_key, incremental_predicates. If the second is set and the third is empty, assume a rewrite until the plan says otherwise. That assumption is how you stop learning this lesson from the invoice.

If both rules could fire on the same MERGE, you would get two tickets for one job: add a WHERE clause, and add an incremental predicate. The first recommendation is wrong for DML, so FULL_TABLE_SCAN skips MERGE, UPDATE, and DELETE, and MERGE_REWRITE owns those statement types while SELECT full scans stay on the other rule. That split is also why a dashboard that scans events without a partition filter and a dbt MERGE that rewrites events show up as two findings: they are two producers, and fixing the explore does not fix the MERGE. Attribution on each finding, dbt versus Looker versus console, is what keeps those tickets from bouncing between teams.

Savings math is also split. Partition-filter SELECT waste uses a high skip ratio because a correct date predicate usually skips most of a year-shaped table. MERGE rewrite savings are a conservative share of billed bytes on repeat runs, because a correctly scoped MERGE still reads the lookback partitions and still writes. Do not add the two dollar figures and claim you will save the table twice.

Unpartitioned targets are still MERGE_REWRITE's problem when the destination READ has no storage-level filter at all. The recommendation in that case is not only a predicate; it is layout, partition the table, then predicate. LARGE_UNPARTITIONED_TABLE may also flag the heap as an advisory with no dollar figure. Use that advisory as the layout ticket and MERGE_REWRITE as the DML ticket. They are related, not duplicates.

SlashPeak never reads table contents to make this call. Destination from JOBS, size and DDL from metadata, filters from minimized job_stages. If the plan is missing, the MERGE is skipped rather than guessed, the same conservative stance as the SELECT partition rule, applied to the statement types that rewrite.

A merged PR that says added incremental_predicates is not proof. Proof is the next production run whose target READ shows the partition filter, whose bytes billed drop from 4 TB toward the lookback, and whose MERGE_REWRITE finding disappears or shrinks on the following SlashPeak scan.

Compare total_bytes_billed on the job_id before and after, not slot-ms alone. Slot-ms moves with reservation congestion; bytes billed is the on-demand meter and the honest proxy on capacity too. If bytes did not move, the predicate did not push. Open job_stages and look at the READ of the destination table. If the filter substep is missing, go back to compiled SQL.

SELECT
  DATE(creation_time) AS day,
  COUNT(*) AS runs,
  AVG(total_bytes_billed) / POW(1024, 4) AS avg_tib
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE destination_table.table_id = 'events'
  AND statement_type = 'MERGE'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
GROUP BY 1
ORDER BY 1;
-- After the predicate ships, avg_tib should fall off a cliff.
-- If it does not, the billed READ is not the model you changed.

Watch for the second MERGE. Some projects have a history table and a current table, or a dbt model and a scheduled query that copies it, and you can fix one producer while the other still rewrites. Workload source on the finding (dbt comment headers versus scheduled_query job ids) tells you which runtime to open; fix both or the invoice will not notice.

require_partition_filter will fail jobs that still omit the column. Keep it on: the failures are cheaper than silent rewrites. If Connected Sheets or a notebook starts failing, that is a producer you had not listed, so point it at a mart or teach it the partition column rather than turning the guardrail off to make the MERGE green. The verification loop is the product: scan, fix the representative MERGE, re-scan, confirm the finding is gone, move to the next DML job in the ranked list. SlashPeak already ranks by dollars in the window; you supply the predicate.