Unoptimized Billing Model for BigQuery Dataset Storage

Yet most datasets remain on logical storage by default, even when physical storage would reduce costs.

This inefficiency is common for cold or infrequently updated datasets that are no longer optimized or regularly reviewed. Because storage behavior and data characteristics evolve, failing to periodically reassess the billing model may result in persistent waste.

BigQuery supports two billing models for table storage:

Logical Storage (default): Billed based on the uncompressed size of user data. This model includes time travel and fail-safe storage at no extra cost.

Physical Storage: Billed based on the actual compressed bytes on disk. Time travel and fail-safe storage are charged separately at the same rate as active storage.

While physical storage can significantly reduce costs for highly compressible datasets, its per-GiB rates are higher than the logical rates (the gap varies by region and differs between active and long-term storage), so it only pays off when the logical-to-physical compression ratio exceeds that price ratio after adding time travel and fail-safe bytes. For frequently updated or less compressible data, the higher rate and the separate retention charges may lead to higher total costs.

• Evaluate datasets with high logical-to-physical compression ratios.
• The billing model is applied at the dataset level. Make sure that the compression ratio is aligned to all tables within the dataset.
• Run periodic queries against `INFORMATION_SCHEMA.TABLE_STORAGE` to compare logical and physical storage sizes across multiple dimensions: active, long-term, time travel and fail-safe.
• Prioritize datasets that are infrequently updated (therefore with low time travel and fail-safe storage volumes) or primarily used for historical lookback
• Use the storage pricing to simulate cost in the physical model \- multiply by the new rates, and add the time travel and fail-safe storage bytes.
• Flag datasets with simulated physical storage price lower than the current logical storage price
• Balance the frequency of detection queries with their cost, and avoid excessive scanning of large datasets

• Switch eligible datasets to physical storage billing when compression advantages are material
• There is no performance impact between the two billing models.
• Changing the billing model takes 24 hours before it's reflected in the GCP billing SKUs.
• There is a 14-day waiting period when a change is made to the billing model.
• Periodically reassess dataset compression ratios to determine if billing model changes are warranted
• Use partitioning and clustering to improve compressibility where possible
• Apply billing model changes to cold or infrequently modified data first, as their structure is more stable
• Document the dataset billing model decisions to ensure transparency and reproducibility
• Monitor billing changes for 2-3 months after switching to validate expected savings
• Consider dataset lifecycle - BigQuery has no storage classes, but tables and partitions left unmodified for 90 consecutive days are billed at the long-term rate automatically; for data that is no longer queried, set partition expiration or export it to Cloud Storage (for example, the Coldline or Archive class) before deleting it from BigQuery

• Physical vs logical storagecloud.google.com
• Querying INFORMATION\_SCHEMA for table metadatacloud.google.com
• TABLE\_STORAGE viewcloud.google.com
• BigQuery Pricingcloud.google.com
• BigQuery storage forecast queriescloud.google.com