dbt Incremental Subquery Filter in BigQuery: Full Scan Every Run

If your dbt incremental model uses a subquery inside the WHERE clause to derive the partition filter — for example, WHERE event_date > (SELECT MAX(event_date) FROM {{ this }}) — BigQuery cannot prune partitions and will scan the full table on every run, even though your model looks correct. The fix is to materialise that subquery into a Jinja variable before the SQL executes, so BigQuery receives a literal value at compile time.

This is one of the most expensive silent failures in a dbt-on-BigQuery project. The model runs, the data loads correctly, the tests pass — and the bill quietly triples. If your BigQuery costs are climbing and you have incremental models that touch large partitioned tables, this is the first place to look.

Why BigQuery Cannot Prune Partitions on Subquery Filters

Partition pruning is BigQuery's mechanism for skipping partitions that are irrelevant to a query. <mark>When a query uses a qualifying filter on the partitioning column, BigQuery scans only the matching partitions and skips the rest — those skipped partitions are not counted in bytes processed, which means they do not appear on your bill.</mark>

The catch — and this is the one that trips engineers — is that <mark>partition pruning only works when partitions are filtered using literal values. Selecting partitions using a subquery does not improve performance.</mark> This is documented behaviour, but it is easy to miss, especially when the pattern looks perfectly reasonable in SQL.

Here is what the problematic pattern looks like in a dbt incremental model:

-- This looks correct. It is not.
SELECT
  event_id,
  event_date,
  user_id,
  revenue
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
WHERE event_date > (SELECT MAX(event_date) FROM {{ this }})
{% endif %}

When dbt compiles this during an incremental run, BigQuery receives the subquery (SELECT MAX(event_date) FROM your_table) as a dynamic expression. BigQuery must resolve that subquery at runtime, which means it cannot determine which partitions to skip before the scan begins. The result is a full table scan on the source table — on every incremental run, every time.

We have seen this pattern in production at multiple growth-stage companies. One series A payments business had a core transaction events model processing 800GB per run, daily. It had been doing so for eight months. The team assumed the model was efficient because it was incremental. It was not — and at 2026 on-demand pricing of $6.25 per TiB, that single model was contributing over $1,500 per month to a bill nobody could explain.

---

📺 Watch: SQL Window Functions | Clearly Explained | PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK

---

How to Detect It: Runnable Diagnostic Queries

The fastest way to find this in your own project is to query INFORMATION_SCHEMA.JOBS and look for incremental model runs that are scanning unexpectedly large volumes — specifically, models that scan close to the full table size every run rather than a small recent slice.

Step 1 — Find your most expensive dbt incremental model jobs in the last 30 days:

SELECT
  job_id,
  query,
  total_bytes_processed,
  ROUND(total_bytes_processed / POW(1024, 4), 4) AS tb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 2) AS estimated_cost_usd,
  creation_time,
  destination_table.table_id AS destination_table
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND total_bytes_processed > 10 * POW(1024, 3) -- models scanning > 10GB
  AND (
    LOWER(query) LIKE '%is_incremental%'
    OR LOWER(query) LIKE '%dbt_internal%'
  )
ORDER BY total_bytes_processed DESC
LIMIT 50;

Replace region-us with your BigQuery region (e.g. region-eu). Look for models where tb_processed is large and consistent across runs — an incremental model processing a fixed large volume daily is a strong signal that partition pruning is not working.

Step 2 — Spot the subquery filter pattern in a specific job:

Take a job_id from the results above and inspect the compiled SQL:

SELECT
  query,
  total_bytes_processed,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE job_id = 'YOUR_JOB_ID_HERE'
LIMIT 1;

In the returned query field, search for (SELECT MAX( or (SELECT MIN( within a WHERE clause. If you find it on the partition column, you have confirmed the problem.

Step 3 — Compare run-over-run bytes to detect a flat scan profile:

A healthy incremental model scans a fraction of the table on each run (recent partitions only). A broken one scans approximately the same volume every time:

SELECT
  DATE(creation_time) AS run_date,
  destination_table.table_id AS model,
  ROUND(AVG(total_bytes_processed) / POW(1024, 3), 2) AS avg_gb_processed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND LOWER(query) LIKE '%dbt_internal%'
GROUP BY 1, 2
ORDER BY 2, 1;

If a model shows a flat or slowly growing avg_gb_processed day over day rather than a small stable slice, it is a candidate for investigation.

---

Running those queries for one model is manageable. Checking every incremental model across a whole project is not. fintel-scan is a free, open-source MIT CLI that runs this check and fourteen others locally against your dbt project — no warehouse connection required: uvx fintel-scan.

---

The Fix: Materialise the Subquery Into a Jinja Variable

The solution is to resolve the MAX value before the SQL body executes, using dbt's run_query macro (or the dbt_utils.get_single_value helper) to execute the subquery at compile time and bind the result as a Jinja variable. BigQuery then receives a literal date string — which it can use for partition pruning.

Here is the corrected model:

{% set max_event_date_query %}
  SELECT MAX(event_date) FROM {{ this }}
{% endset %}

{% if is_incremental() %}
  {% set max_event_date = run_query(max_event_date_query).columns[0].values()[0] %}
{% endif %}

SELECT
  event_id,
  event_date,
  user_id,
  revenue
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
WHERE event_date > '{{ max_event_date }}'
{% endif %}

When dbt compiles this, BigQuery receives a literal value such as WHERE event_date > '2026-09-18' — a static string that the query planner can evaluate before the scan begins. Partition pruning is restored. Bytes processed drops from full table to recent-days-only.

If you use dbt_utils, the pattern is cleaner:

{% if is_incremental() %}
  {% set max_event_date = dbt_utils.get_single_value(
      'SELECT MAX(event_date) FROM ' ~ this
  ) %}
{% endif %}

SELECT
  event_id,
  event_date,
  user_id,
  revenue
FROM {{ ref('stg_events') }}
{% if is_incremental() %}
WHERE event_date > '{{ max_event_date }}'
{% endif %}

One important caveat with datetime partition columns: there is a known interaction where dbt's insert_overwrite strategy uses datetime_trunc() in the generated MERGE predicate, which can also force a full scan on datetime-partitioned tables even when the WHERE clause filter looks correct. If your partition column is a DATETIME rather than a DATE or TIMESTAMP, switching data_type to timestamp in your partition_by config forces dbt to use timestamp_trunc() instead — which BigQuery handles correctly for partition pruning.

{{ config(
  materialized='incremental',
  unique_key='event_id',
  partition_by={
    "field": "event_ts",
    "data_type": "timestamp",
    "granularity": "day"
  },
  incremental_strategy='insert_overwrite'
) }}

This is the kind of detail that does not appear in the dbt documentation prominently — you find it by watching a model scan 2TB on an incremental run and then tracing the compiled SQL backwards.

How to Stop It Recurring

Finding and fixing this once is useful. Making sure it never silently reappears is the actually valuable work.

Add a bytes-processed assertion to your dbt test suite. dbt does not natively support testing bytes processed, but you can use post-hooks to write job metadata to a monitoring table and alert when any incremental model exceeds a bytes threshold. Set a ceiling at two or three times the expected incremental slice size for each model.

Enable require_partition_filter on large tables. This dbt BigQuery config setting forces any query against the table to include a partition filter — BigQuery will return an error rather than silently scan the whole table. It is a blunt instrument, but for event-stream tables over 100GB it is the right one.

{{ config(
  materialized='incremental',
  partition_by={"field": "event_date", "data_type": "date"},
  require_partition_filter=true
) }}

Build bytes-processed monitoring into your CI pipeline. Every dbt PR that touches an incremental model should trigger a dry run in BigQuery (--dry-run flag on the compiled SQL) and report estimated bytes. If a model that previously scanned 5GB now dry-runs at 800GB, the PR should not merge without explanation.

A pattern we see repeatedly in our work with early-stage data teams: the incremental model pattern is adopted specifically to reduce cost and pipeline runtime, but without the monitoring layer to confirm it is actually working, the efficiency gains evaporate silently. The model label says "incremental" but the billing behaviour says "full scan." The gap between intention and execution is what costs real money.

For context on how the related problem of dbt intermediate model materialisation compounds this — if your broken incremental model feeds downstream intermediates materialised as tables, each of those rebuilds on the inflated upstream cost. The scan waste multiplies through your DAG.

If you want to see how partitioned incremental costs look before and after the fix, the BigQuery INFORMATION_SCHEMA cost attribution post walks through the job history queries in more detail.

Frequently Asked Questions

Q: Why does my dbt incremental model still scan the full table even though I have a WHERE clause?

A: BigQuery can only prune partitions when the filter value is a literal — a static string, date, or number known at compile time. If your WHERE clause contains a subquery such as (SELECT MAX(event_date) FROM {{ this }}), BigQuery must resolve that dynamically at runtime and cannot skip any partitions before the scan begins. The fix is to materialise the subquery result into a Jinja variable before the SQL body, so dbt compiles a literal value into the final query.

Q: How do I know if my dbt incremental model is doing a full table scan in BigQuery?

A: Query INFORMATION_SCHEMA.JOBS and compare total_bytes_processed across multiple incremental runs for the same model. If the bytes processed is consistently large and roughly constant run-over-run — rather than a small growing slice — partition pruning is not working. You can also inspect the compiled SQL for a specific job: look for (SELECT MAX( inside a WHERE clause on the partition column.

Q: Does the is_incremental() macro in dbt guarantee partition pruning in BigQuery?

A: No. The is_incremental() macro controls which SQL branch dbt compiles, but it does not guarantee that BigQuery will prune partitions. Pruning depends entirely on the form of the filter in the compiled SQL. A subquery filter inside is_incremental() will still cause a full table scan. Only literal values in the WHERE clause on the partition column enable pruning.

Q: What is the performance impact of switching from a subquery filter to a Jinja variable in a dbt incremental model?

A: The impact can be dramatic on large tables. For a table partitioned by day where the incremental slice is 1–2 days of data, switching from a subquery to a literal filter reduces bytes scanned from the full table size to roughly 1–2 days of data. On a 1TB table with daily runs, that is the difference between scanning 1TB per run and scanning 5–10GB per run — a reduction of 99% in bytes processed and cost.

Q: Can I use require_partition_filter in dbt to enforce this?

A: Yes. Setting require_partition_filter=true in your dbt BigQuery model config will cause BigQuery to reject any query against that table that does not include a valid partition filter. This is a useful safety net for large event tables, but note it will also break any ad-hoc queries against the table that omit a partition filter, so it is best reserved for tables where that constraint is acceptable.

If your team is watching BigQuery costs climb without a clear explanation, and you have incremental models that have been running for months on large partitioned tables, this is almost certainly part of the story. At Fintel Analytics, we have audited dozens of dbt projects where incremental models were billing like full table scans — and in every case, the fix paid for the audit many times over. If that pattern sounds familiar, it is worth getting an expert eye on your project before the next billing cycle closes.