BigQuery require_partition_filter: Find Tables Missing the Guard

Partitioned BigQuery tables that are missing require_partition_filter = TRUE are a silent bill multiplier: they look correct in your schema, they run without error, and they scan every partition on every query that forgets a filter — including your scheduled dbt models. Here is how to find every table in your project that is missing this guard and cross-reference it against your job history to quantify exactly what it is costing you.

This is one of the most underdiagnosed cost leaks we encounter when auditing BigQuery projects for growth-stage companies. The partition column is there, the table looks optimised, but nothing enforces filter usage — so the optimisation only works when the person writing the query remembers to include it. At scale, that assumption fails constantly.

Why require_partition_filter Exists — and Why Most Teams Skip It

BigQuery's on-demand pricing charges per byte scanned. At $6.25 per TiB (2026 pricing), a single unguarded full-table scan of a large fact table can cost more than a week of careful, filtered queries. The require_partition_filter flag was built specifically to prevent this: when set to TRUE, BigQuery rejects any query against that table that does not include a qualifying predicate on the partition column, returning an error rather than silently billing you.

The problem is that this flag defaults to FALSE. When a table is created — either manually, via a dbt config() block, or by a pipeline tool — the guard is off unless someone explicitly enables it. In practice, most teams partition their tables correctly and then stop there, assuming partitioning alone is enough to control costs.

It is not. Partitioning reduces scan cost only when the query includes a filter on the partition column. Without require_partition_filter, nothing stops a dbt model, a scheduled query, or an analyst's ad-hoc SELECT from scanning the entire table. The partition exists, but pruning never fires.

A pattern we see repeatedly in early-stage companies: a fact table is partitioned by event_date, a staging model joins to it without a date filter "just to get all records", and that model runs hourly. By the time anyone notices the bill, hundreds of full-table scans have quietly accumulated.

Step 1 — Find Every Partitioned Table Missing the Guard

Run this against INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_OPTIONS in your project. It returns every partitioned table where require_partition_filter is not enforced:

SELECT
  t.table_catalog,
  t.table_schema,
  t.table_name,
  t.row_count,
  ROUND(t.size_bytes / POW(1024, 3), 2) AS size_gb,
  -- Check whether require_partition_filter is explicitly set to TRUE
  MAX(CASE
    WHEN opt.option_name = 'require_partition_filter'
      AND opt.option_value = 'TRUE'
    THEN 1 ELSE 0
  END) AS has_partition_filter_required
FROM
  `your-project`.`your-dataset`.INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN
  `your-project`.`your-dataset`.INFORMATION_SCHEMA.TABLE_OPTIONS AS opt
    ON t.table_catalog = opt.table_catalog
    AND t.table_schema = opt.table_schema
    AND t.table_name = opt.table_name
WHERE
  t.table_type = 'BASE TABLE'
  -- Only include partitioned tables
  AND EXISTS (
    SELECT 1
    FROM `your-project`.`your-dataset`.INFORMATION_SCHEMA.COLUMNS AS c
    WHERE c.table_name = t.table_name
      AND c.table_schema = t.table_schema
      AND c.is_partitioning_column = 'YES'
  )
GROUP BY
  t.table_catalog, t.table_schema, t.table_name,
  t.row_count, t.size_bytes
HAVING
  has_partition_filter_required = 0
ORDER BY
  size_gb DESC;

This gives you a ranked list of your largest partitioned tables that have no enforcement in place. Sort by size_gb — the biggest tables at the top are your highest-exposure risk. Any dbt model or scheduled query that touches these tables without a partition predicate will scan the full table on every run.

Replace your-project and your-dataset with your actual values. To scan across multiple datasets, move the query to region-us.INFORMATION_SCHEMA.TABLES (replacing region-us with your region) which gives you project-wide visibility without specifying individual datasets.

Step 2 — Cross-Reference Against Job History to Find the Offenders

Knowing which tables are unguarded is useful. Knowing which queries are actively scanning them without a partition filter is what tells you the actual cost. Run this against INFORMATION_SCHEMA.JOBS to find the highest-cost recent jobs hitting those tables:

SELECT
  j.job_id,
  j.user_email,
  j.creation_time,
  ROUND(j.total_bytes_billed / POW(1024, 4), 4) AS tib_billed,
  ROUND((j.total_bytes_billed / POW(1024, 4)) * 6.25, 4) AS estimated_cost_usd,
  j.query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS AS j,
  UNNEST(j.referenced_tables) AS ref
WHERE
  j.creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND j.job_type = 'QUERY'
  AND j.state = 'DONE'
  -- Filter to jobs referencing tables you identified in Step 1
  -- Replace with your table names
  AND ref.table_id IN ('your_table_a', 'your_table_b')
  -- High-scan jobs only
  AND j.total_bytes_billed > 10737418240  -- 10 GB threshold
ORDER BY
  j.total_bytes_billed DESC
LIMIT 50;

The query column in the results will show you the exact SQL. Look for dbt model names in the query text — they will typically contain your project's schema prefix. If you see the same query appearing repeatedly with high tib_billed, you have found a scheduled model that is full-scanning a partitioned table every single run.

For context on scale: at BigQuery's on-demand rate of $6.25 per TiB (2026), a 500 GB fact table full-scanned hourly by a dbt incremental model accumulates roughly $23 per day — or close to $700 per month — from that single model alone. The cost compounds fast when multiple models reference the same unguarded tables.

Running that two-query audit manually across one or two tables is manageable. Running it systematically across an entire dbt project is a different problem — fintel-scan is a free, open-source MIT-licensed CLI that automates this check and fourteen others locally, without needing a live warehouse connection: uvx fintel-scan.

Step 3 — Fix the Tables: Enabling the Guard

Once you have identified the unguarded tables, there are two paths to fix them.

For tables managed by dbt, add require_partition_filter to the model's config() block:

{{ config(
    materialized='table',
    partition_by={
        'field': 'event_date',
        'data_type': 'date',
        'granularity': 'day'
    },
    require_partition_filter=True
) }}

Once this is deployed and the model is rebuilt, BigQuery will enforce the filter at the table level. Any downstream query — from another dbt model, a BI tool, or an analyst — that hits this table without a partition predicate will fail with a clear error rather than silently billing you.

For tables not managed by dbt (raw source tables, external pipeline outputs), apply the flag via DDL:

ALTER TABLE `your-project.your-dataset.your_table`
SET OPTIONS (require_partition_filter = TRUE);

This takes effect immediately and applies to all subsequent queries. It does not require a table rebuild.

One important caveat: enabling this guard will cause any existing models or queries that reference the table without a filter to fail. That is the point — but run the Step 2 job history audit first so you know exactly which queries will break before you flip the switch in production. Fix the queries first, then enable the guard.

For dbt models that are unexpectedly scanning large partitioned tables, also check whether you have incremental_predicates configured correctly — a missing or misconfigured predicate on an incremental model's MERGE statement is a common companion issue. See our post on dbt Incremental Subquery Filter in BigQuery: Full Scan Every Run for how that failure mode presents and how to fix it.

Step 4 — Make It Stick: Governance That Prevents Regression

A one-time fix is not enough. New tables get created, new models get added, and without a systematic check the unguarded surface area grows back. Build prevention into three places.

dbt project defaults — set require_partition_filter at the model-group level in dbt_project.yml so all new fact and event tables inherit the guard automatically:

models:
  your_project:
    marts:
      facts:
        +partition_by:
          field: event_date
          data_type: date
          granularity: day
        +require_partition_filter: true

dbt schema tests — you can write a custom generic test that queries INFORMATION_SCHEMA.TABLE_OPTIONS and asserts that require_partition_filter is TRUE for any model tagged as a fact table. Run this in CI so a PR that removes the flag fails the test suite before it merges.

Scheduled audit query — set up the Step 1 query as a scheduled BigQuery query or a monitoring job that alerts to a Slack channel when new unguarded partitioned tables appear. This catches tables created outside of dbt — raw ingestion tables, manually created views promoted to tables, and so on.

A fintech client we worked with had seventeen partitioned fact tables, none of which had require_partition_filter enabled. After the audit and remediation, three scheduled dbt models that had been full-scanning a 400 GB transactions table daily were identified, refactored with proper date range predicates, and the guard was enabled. The BigQuery bill for that dataset dropped measurably within the first billing cycle — and more importantly, future regressions became impossible without an explicit test failure.

For teams running dbt query labels, you can also surface the label field from INFORMATION_SCHEMA.JOBS to tie each expensive scan back to its specific dbt model name and environment, making attribution unambiguous when debugging billing spikes.

Frequently Asked Questions

Q: Does enabling require_partition_filter on a BigQuery table break existing queries?

A: Yes — intentionally. Any query that references the table without a qualifying partition filter predicate will return an error. Run the job history audit first to identify all queries that will be affected, fix them to include a proper filter, then enable the guard. Do not enable it in production without completing that audit first.

Q: How do I set require_partition_filter in a dbt BigQuery model?

A: Add require_partition_filter=True to the config() block of your model, alongside your partition_by config. You can also set it as a default for entire model groups in dbt_project.yml under the relevant directory path, so all new models in that group inherit the setting automatically.

Q: Will require_partition_filter affect BigQuery BI Engine or Looker queries?

A: Yes — any query engine that queries the table, including Looker, Looker Studio, Holistics, or ad-hoc SQL from any tool, must include a filter on the partition column. For BI tools, this typically means ensuring your report filters pass through to the underlying SQL. Most well-configured BI semantic layers handle this automatically when date filters are applied.

Q: What is the difference between require_partition_filter and partition pruning?

A: Partition pruning is what happens when BigQuery skips irrelevant partitions because a filter is present. require_partition_filter is the enforcement mechanism that mandates a filter be present — it turns a silent scan into a loud error. Pruning can happen without the flag; the flag ensures pruning always happens by making filterless queries impossible.

Q: How do I find which dbt models are causing full table scans in BigQuery?

A: Use INFORMATION_SCHEMA.JOBS filtering on referenced_tables to find high-bytes-billed jobs hitting your target tables, then inspect the query column for your dbt schema prefix. If you have dbt query labels configured, you can filter directly on labels.dbt_model to identify the exact model responsible.

Missing require_partition_filter guards on large BigQuery fact tables is one of the quietest and most consistent sources of bill inflation we find when auditing data stacks at growth-stage companies — and it is entirely fixable in an afternoon once you know where to look. At Fintel Analytics, we have helped fintech, payments, and e-commerce teams run exactly this kind of warehouse audit, closing cost leaks and putting the governance in place to stop them coming back. If your BigQuery bill is climbing and you are not certain why, that is a conversation worth having.