Guarding BigQuery Costs with require_partition_filter on Ingestion‑Time Partitioned Tables

The problem: accidental full‑table scans

When analysts run ad‑hoc queries against a large BigQuery table, forgetting to include a date filter can cause the engine to scan the entire table. With on‑demand pricing, each terabyte processed costs money, so a single missed predicate can turn a cheap exploratory query into an unexpectedly large bill.

How require_partition_filter helps

BigQuery lets you mark a partitioned table with the table option require_partition_filter = true. When this flag is set, any query that does not contain a qualifying predicate on the partitioning column (or the pseudo‑column _PARTITIONTIME) is rejected with an error:

Error: Query cannot be executed without a partition filter.

This forces every query to include at least one filter that enables partition elimination, guaranteeing that only the relevant partitions are read.

Worked example: enabling the flag and measuring savings

1. Create an ingestion‑time partitioned table in your project (replace placeholders with your own values):

   bq mk --time_partitioning_type=DAY \
     --require_partition_filter=true \
     $PROJECT_ID:$DATASET.events

2. Load a modest amount of sample data (e.g., 10 GB spread over 365 days). This step is only to illustrate the concept; you can skip it if you already have a table.

   bq load --source_format=NEWLINE_DELIMITED_JSON \
     $PROJECT_ID:$DATASET.events ./events.json \
     ts:TIMESTAMP,user_id:STRING,action:STRING

3. Run a dry‑run query that omits the date filter. The command should fail:

   bq query --use_legacy_sql=false --dry_run \
     'SELECT COUNT(*) FROM `$PROJECT_ID.$DATASET.events` WHERE user_id = "alice"'

   Expected output (error):

   Error: Query cannot be executed without a partition filter.

4. Now run a dry‑run query that includes a proper partition predicate (using the _PARTITIONTIME pseudo‑column):

   bq query --use_legacy_sql=false --dry_run \
     'SELECT COUNT(*) FROM `$PROJECT_ID.$DATASET.events` \
      WHERE _PARTITIONTIME BETWEEN TIMESTAMP("2026-09-01") \
                        AND TIMESTAMP("2026-09-07") \
        AND user_id = "alice"'

   The dry‑run will report the estimated bytes processed, which should be roughly proportional to the seven days selected rather than the full year.

5. Optionally, execute the query and compare the actual bytes processed in the job details to the dry‑run estimate.

Trade‑offs and limitations

• Legitimate full‑table scans (e.g., nightly backfills or aggregate jobs) must be rewritten to either:

  • Add a dummy partition filter that covers all partitions (e.g., WHERE _PARTITIONTIME IS NOT NULL), or
  • Temporarily disable the flag on a copy of the table, run the job, then re‑enable it.

• The flag only rejects queries lacking a partition predicate; it does not guarantee low cost. A filter that spans all partitions (e.g., WHERE DATE(_PARTITIONTIME) < "2030-01-01") will still scan the whole table. Pair the flag with MAX_BYTES_BILLED or regular dry‑run checks to catch overly broad filters.

Practical verification steps

1. After creating or altering a table, confirm the flag is set:

bq show --format=prettyjson $PROJECT_ID:$DATASET.events | grep require_partition_filter

You should see "require_partition_filter": true.

1. Run the two dry‑run queries shown above and verify that the first fails while the second returns a non‑zero byte estimate.
2. For a production table, periodically review query logs for the error "Query cannot be executed without a partition filter" to ensure analysts are adapting their queries.

Actionable closing

Enabling require_partition_filter on ingestion‑time partitioned tables is a low‑effort guardrail that turns accidental full‑table scans into immediate, actionable errors. Combine it with routine dry‑run checks and, when necessary, a temporary table copy for jobs that truly need to scan all partitions. This approach keeps query costs predictable while preserving the performance benefits of partition elimination.