TopolearnPartitioning is the main tool for cutting both cost and latency on large BigQuery tables.
A partitioned table is split into segments by the value of one column, and queries that filter on that column read only the matching partitions. On-demand, that directly lowers bytes billed, and the dry-run estimate reflects it.
Partitioning options:
- Time-unit column:
PARTITION BY DATE(event_ts), orTIMESTAMP_TRUNC(event_ts, HOUR),DATE_TRUNC(d, MONTH). - Ingestion time:
PARTITION BY _PARTITIONDATE, based on when rows arrived. - Integer range:
PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000)).
Useful options:
require_partition_filter = TRUErejects queries that don't filter on the partition column, preventing accidental full scans.partition_expiration_days = 400drops old partitions automatically.
Pruning needs a filter BigQuery can evaluate against the partition column, e.g. WHERE DATE(event_ts) BETWEEN '2025-06-01' AND '2025-06-07' or a constant expression. Filters that hide the column behind complex expressions or depend on a subquery may not prune. Check the estimate.
A table can have at most 10,000 partitions, and each partition should hold a meaningful amount of data. Daily partitions on a small table only add overhead; use monthly, or rely on clustering alone.
Older datasets often use date-sharded tables (events_20250601, events_20250602...) queried with a wildcard table events_* and _TABLE_SUFFIX. GA4 exports still do. Partitioned tables are preferred for new designs: one schema, less metadata, better performance.
Example
CREATE TABLE web.events (
event_ts TIMESTAMP NOT NULL,
user_id STRING,
event_name STRING,
country STRING
)
PARTITION BY DATE(event_ts)
OPTIONS (
require_partition_filter = TRUE,
partition_expiration_days = 400
);
-- Reads one week of partitions only
SELECT event_name, COUNT(*) AS n
FROM web.events
WHERE DATE(event_ts) BETWEEN '2025-06-01' AND '2025-06-07'
GROUP BY event_name;
-- Rejected: no filter on the partition column
-- SELECT COUNT(*) FROM web.events; -- error: cannot query without a partition filter
-- Legacy date-sharded tables use a wildcard and _TABLE_SUFFIX
SELECT COUNT(*) FROM `proj.analytics_123.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250601' AND '20250607';Remember
- Partition by a date/timestamp, ingestion time or integer range
- Filters on the partition column prune data and lower cost
- require_partition_filter prevents accidental full scans
- Prefer partitioned tables over date-sharded tables
Watch out for
- Partitioning a small table by day, creating thousands of tiny partitions with no benefit.