# GCP BigQuery optimization | Cloud Efficiency Hub Canonical: https://www.pointfive.co/efficiency-hub/cloud-services/gcp-bigquery 9 documented GCP BigQuery cost inefficiencies, each with how to detect it and how to fix it. [Cloud Efficiency Hub](https://www.pointfive.co/efficiency-hub) 9 documented GCP BigQuery cost inefficiencies, each with how to detect it and how to fix it. ## 9 inefficiencies [All services](https://www.pointfive.co/efficiency-hub#browse-by-service) - 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-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-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 - GCP BigQuery CER-0024 ### [Unnecessary Reset of Long-Term Storage Pricing in BigQuery](https://www.pointfive.co/efficiency-hub/inefficiencies/unnecessary-reset-of-long-term-storage-pricing-in-bigquery) BigQuery incentivizes efficient data retention by cutting storage costs in half for tables or partitions that go 90 days without modification. However, many teams unintentionally forfeit this discount by performing broad or unnecessary... Databases - GCP BigQuery CER-0484 ### [Missing Partitioning and Clustering on Large BigQuery Tables](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). When a large table is neither partitioned nor clustered, a query that filters on a date or key column still... Databases - GCP BigQuery CER-0485 ### [Missed Capacity-Based Pricing for Heavy On-Demand BigQuery Workloads](https://www.pointfive.co/efficiency-hub/inefficiencies/missed-capacity-based-pricing-for-heavy-on-demand-bigquery-workloads) BigQuery bills queries by default on the on-demand model, per TiB of data processed. The alternative is capacity pricing through BigQuery editions, where compute is billed per slot-hour with autoscaling and optional baseline and committed... Databases - GCP BigQuery CER-0486 ### [Missing Custom Query Quotas and Maximum Bytes Billed in BigQuery](https://www.pointfive.co/efficiency-hub/inefficiencies/missing-custom-query-quotas-and-maximum-bytes-billed-in-bigquery) On the on-demand pricing model, every BigQuery query is billed for the data it processes, and there is no per-query or per-user spending limit unless one is configured. The default project-level quota allows 200 TiB of data processed per... Databases - GCP BigQuery CER-0487 ### [Missing Table Expiration on Temporary BigQuery Tables](https://www.pointfive.co/efficiency-hub/inefficiencies/missing-table-expiration-on-temporary-bigquery-tables) BigQuery tables never expire unless an expiration is set. Google's documentation is explicit: if no default table expiration is set on the dataset and no expiration is set on the table when it is created, the table never expires and must... Databases - GCP BigQuery CER-0488 ### [Streaming Ingestion via REST Instead of Storage Write API gRPC in BigQuery](https://www.pointfive.co/efficiency-hub/inefficiencies/streaming-ingestion-via-rest-instead-of-storage-write-api-grpc-in-bigquery) BigQuery offers two streaming paths. The Storage Write API (REST), previously known as the legacy tabledata.insertAll method, bills streamed data per 200 MiB and counts every row as at least 1 KB. The Storage Write API over gRPC bills per... Databases ## More GCP services - [GCP Compute Engine 14](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-compute-engine) - [GCP GKE 9](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-gke) - [GCP Cloud SQL 8](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-cloud-sql) - [GCP GCS 6](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-gcs) - [GCP Cloud Logging 5](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-cloud-logging) - [GCP Vertex AI 5](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-vertex-ai) - [GCP Dataflow 4](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-dataflow) - [GCP Cloud Run 3](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-cloud-run) - [GCP Cloud Memorystore 2](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-cloud-memorystore) - [GCP Cloud NAT 2](https://www.pointfive.co/efficiency-hub/cloud-services/gcp-cloud-nat) --- Source: the public page above. Product screenshots and illustrative interfaces are examples, not live customer data.