BigQuery Cost and Performance Optimization

Partitioned tables

Partition by date or integer range. Queries filtering on the partition column only scan relevant partitions — BigQuery charges by bytes scanned.

CREATE TABLE orders
PARTITION BY DATE(created_at)
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM orders_raw;

require_partition_filter = TRUE forces every query to include a partition filter, preventing accidental full scans.

Clustered tables

Clustering further sorts data within partitions by up to 4 columns. Queries filtering on clustered columns skip blocks automatically.

Avoid SELECT *

BigQuery is columnar — SELECT * reads every column. Specify only needed columns. A query on 3 of 100 columns costs 3% of the SELECT * equivalent.

Slot usage

On-demand pricing charges per byte scanned. For predictable, high-usage workloads, flat-rate slots (reservations) are cheaper above a certain query volume. Analyse with INFORMATION_SCHEMA.JOBS to estimate break-even.