# Missing Partitioning and Clustering on Large BigQuery Tables Canonical: https://www.pointfive.co/efficiency-hub/inefficiencies/missing-partitioning-and-clustering-on-large-bigquery-tables BigQuery charges for queries by the data they process (on-demand pricing) or by the slot capacity they consume (capacity pricing). By: PointFive Updated: 2026-09-28 [Cloud Efficiency Hub](https://www.pointfive.co/efficiency-hub) The short version BigQuery charges for queries by the data they process (on-demand pricing) or by the slot capacity they consume (capacity pricing). PointFive Research Cloud cost research at PointFive GCP service [GCP BigQuery](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-bigquery) Category [Databases](https://www.pointfive.co/efficiency-hub/service-category/databases) Reference CER-0484 Type Inefficient Configuration ## 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. - [Manage partition and cluster recommendations docs.cloud.google.com](https://docs.cloud.google.com/bigquery/docs/manage-partition-cluster-recommendations) - [Introduction to partitioned tables docs.cloud.google.com](https://docs.cloud.google.com/bigquery/docs/partitioned-tables) - [Introduction to clustered tables docs.cloud.google.com](https://docs.cloud.google.com/bigquery/docs/clustered-tables) - [Manage partitioned tables docs.cloud.google.com](https://docs.cloud.google.com/bigquery/docs/managing-partitioned-tables) - [BigQuery pricing cloud.google.com](https://cloud.google.com/bigquery/pricing) - [Optimize resource usage docs.cloud.google.com](https://docs.cloud.google.com/architecture/framework/cost-optimization/optimize-resource-usage) ## Related inefficiencies [Browse the library](https://www.pointfive.co/efficiency-hub) - GCP BigQuery CER-0226 ### [Unoptimized Billing Model for BigQuery Dataset Storage](https://www.pointfive.co/efficiency-hub/inefficiencies/unoptimized-billing-model-for-bigquery-dataset-storage) Highly compressible datasets, such as those with repeated string fields, nested structures, or uniform rows, can benefit significantly from physical storage billing. Yet most datasets remain on logical storage by default, even when... Databases - GCP BigQuery CER-0295 ### [Overselecting Data and Misusing LIMIT for Cost Control in BigQuery](https://www.pointfive.co/efficiency-hub/inefficiencies/overselecting-data-and-misusing-limit-for-cost-control-in-bigquery) Analysts use SELECT \* (reading more columns than needed) and/or rely on LIMIT as a cost-control mechanism. In BigQuery, projecting excess columns increases the amount of data read and can materially raise query cost, particularly on wide... Databases - GCP BigQuery CER-0069 ### [Inefficient Use of Reservations in BigQuery](https://www.pointfive.co/efficiency-hub/inefficiencies/inefficient-use-of-reservations-in-bigquery) Teams often adopt capacity-based pricing (BigQuery editions reservations with baseline slots and optional commitments) to stabilize costs or optimize for heavy, recurring workloads. However, if query volumes drop - due to seasonal cycles,... Databases --- Source: the public page above. Product screenshots and illustrative interfaces are examples, not live customer data.