Explanation
Why the waste happens and who it affects.
When a large table is neither partitioned nor clustered, a query that filters on a date or key column still has to read every row of the selected columns, because BigQuery has no layout information to skip the irrelevant data. Partitioning lets BigQuery prune whole partitions that do not match a filter on the partitioning column, and clustering lets it prune storage blocks when queries filter on the clustering columns; pruned data is not scanned and not billed.
Tables end up unpartitioned when they are created by a load job, a tool default or a quick CREATE TABLE AS SELECT, and then grow for months while dashboards, scheduled queries and ad hoc analysis filtered them by date. Every repeat of those queries pays for a full scan. Google's Well-Architected Framework cost pillar lists optimizing query performance and costs with partitioning and clustering, and BigQuery ships a dedicated partitioning and clustering recommender that estimates the savings from workload history.
Billing model
The pricing dimensions that drive this cost.
BigQuery bills the queries that scan these tables in one of two ways, and pruning reduces both.
- On-demand compute
- Billed per TiB of data processed by each query, with the first 1 TiB per month free and a 10 MB minimum per table referenced
- Capacity compute
- Billed per slot-hour through BigQuery editions; full scans consume more slot time and push reservations to autoscale
- Partition pruning
- Filters on the partitioning column let BigQuery skip non-matching partitions so they are not scanned or billed
- Block pruning
- On clustered tables, only scanned blocks count toward bytes processed; the pre-run estimate is an upper bound
How to detect
5 checks to find it in your estate.
- Review recommendations from the BigQuery partitioning and clustering recommender (google.bigquery.table.PartitionClusterRecommender) in the BigQuery Recommendations tab, with gcloud recommender recommendations list, or in INFORMATION_SCHEMA.RECOMMENDATIONS; it analyzes the past 30 days of workload and estimates monthly savings in slot hours and bytes processed
- Find unpartitioned and unclustered tables with INFORMATION_SCHEMA.COLUMNS, where no column has is_partitioning_column = 'YES' and clustering_ordinal_position is NULL for every column, and join with table storage size to focus on large tables
- In INFORMATION_SCHEMA.JOBS, expand referenced_tables and sum total_bytes_processed and total_slot_ms per table to find the tables that drive the most scanning
- For the top tables, inspect the WHERE clauses of recurring and scheduled queries to identify the date or key columns they consistently filter on
- Note the recommender's limits: it excludes legacy SQL queries and tables, can overestimate savings, and per Google's launch announcement only analyzes tables larger than 100 GB for partitioning and 10 GB for clustering, so check smaller heavily scanned tables yourself
How to fix
5 ways to remove the waste.
- Partition large tables on the DATE, TIMESTAMP or DATETIME column used in filters (or by ingestion time, or integer range for key-based access); because the partitioning scheme cannot be changed in place, create a new partitioned table from the existing data and swap it in
- Cluster on up to four frequently filtered, high-cardinality columns; clustering can be added to an existing table, and BigQuery reclusters automatically in the background at no cost
- Enable Require partition filter on large partitioned tables so queries without a predicate on the partitioning column fail instead of scanning every partition
- Update the recurring queries to filter directly on the partitioning column, since pruning only applies to qualifying filters on that column
- Stay within partitioning limits (10,000 partitions per table, one partitioning column); prefer clustering when partitions would be too many or too small
Documentation
Vendor references for pricing and configuration.