How to Query Partitioned Tables Cheaply in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
Filter on the partition column so BigQuery scans only the partitions you need. require_partition_filter blocks queries that forget the filter, avoiding accidental full scans.
SELECT event_type, COUNT(*) AS events
FROM analytics.events
WHERE event_date BETWEEN '2026-07-01' AND '2026-07-26'
GROUP BY event_type;Require a partition filter
Setting require_partition_filter on the table makes BigQuery reject any query that omits a filter on the partition column, the cheapest safeguard against a runaway full-table scan on a large fact table.
ALTER TABLE analytics.events
SET OPTIONS (require_partition_filter = true);This table's partition column is event_date, a DATE, separate from any full-precision event_time TIMESTAMP the row might also store. Filtering on a derived expression like DATE(event_time) instead of the event_date partition column itself can prevent BigQuery from pruning partitions. Filter on event_date directly for guaranteed pruning.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free