BigQuery Storage Billing Strategies

Storage represents 10-15% of a typical BigQuery bill. After addressing compute costs — column selection, partition pruning, clustering — storage billing offers 30-50% savings through one-time configuration changes.

Physical vs Logical Billing

In July 2023, Google introduced physical bytes storage billing as an alternative to the default logical billing.

Logical billing (the default): You pay for uncompressed data size at $0.02/GB per month. Time travel storage (7 days) and fail-safe storage (7 days) are included free. What you see in the BigQuery console as “table size” is what you’re billed for.

Physical billing: You pay for compressed data at $0.04/GB per month. The per-GB rate is 2x higher, but because data compresses significantly, the total bill is usually much lower. The catch: time travel and fail-safe storage appear as separate line items, billed at the same $0.04/GB rate.

The math is simple. Physical billing wins when your compression ratio exceeds 2:1. Since BigQuery’s Capacitor format typically compresses data 6-17x, most organizations save significantly by switching. A table that’s 100 GB logical might be 10 GB physical — at $0.04/GB that’s $0.40/month versus $2.00/month on logical billing.

Before switching, query your actual compression ratios:

SELECT
  table_schema,
  table_name,
  total_logical_bytes / POW(1024, 3) AS logical_gb,
  total_physical_bytes / POW(1024, 3) AS physical_gb,
  ROUND(total_logical_bytes / total_physical_bytes, 2) AS compression_ratio,
  -- If ratio > 2, physical billing saves money
  CASE
    WHEN total_logical_bytes / total_physical_bytes > 2
    THEN 'Switch to physical'
    ELSE 'Keep logical'
  END AS recommendation
FROM `project.region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_logical_bytes > 0
ORDER BY total_logical_bytes DESC;

Run this across all datasets. If most tables show compression ratios above 2:1, switch to physical billing at the dataset level.

The Time Travel Trade-off

Under physical billing, time travel and fail-safe storage appear as separate charges. For tables with frequent updates — CDC tables receiving many MERGE operations, for instance — each update creates time travel overhead. The original data versions are retained for 7 days, and those bytes are billed at $0.04/GB.

For append-heavy tables (event logs, analytics data), this overhead is minimal. For tables with high update rates, the time travel cost can partially offset compression savings. The INFORMATION_SCHEMA query above captures total_physical_bytes, which includes time travel storage, so the comparison is apples-to-apples as long as you run it during normal operations.

Long-Term Storage: The Free 50% Discount

BigQuery automatically transitions data to long-term storage after 90 consecutive days without modification. The price drops from $0.02/GB to $0.01/GB on logical billing — a 50% discount requiring zero configuration, zero effort, zero code changes.

What resets the 90-day timer:

• Any DML operation (INSERT, UPDATE, DELETE, MERGE)
• Streaming inserts
• CREATE OR REPLACE TABLE

What does NOT reset the timer:

• Querying the table (read operations are free for this purpose)
• Creating views on the table
• Exporting data
• Copying the table

For partitioned tables, each partition evaluates independently. A table with 365 daily partitions will have roughly 275 partitions at long-term pricing and roughly 90 at active pricing, assuming only recent data receives writes. This is one of the strongest arguments for time-based partitioning: older partitions naturally age into cheaper storage.

The key insight: if your dbt incremental models use insert_overwrite with a 3-day lookback, only the 3 most recent partitions reset their timer on each run. The other 362 partitions continue aging toward long-term pricing. Full table refreshes (CREATE OR REPLACE) reset every partition — another reason to prefer incremental materializations.

Table Expiration: Preventing Storage Creep

Temporary tables, ETL artifacts, staging tables, and development experiments accumulate silently. Storage creep is common in teams without expiration policies. One example: $3,000 monthly for temporary tables that had not been queried in months.

Set dataset-level defaults for staging and development datasets:

ALTER SCHEMA `project.staging`
SET OPTIONS (
  default_table_expiration_days = 7
);

Every new table created in this dataset automatically expires after 7 days unless overridden. This is the single most effective policy for preventing storage creep in non-production environments.

Override at the table level when you need a specific retention window:

CREATE TABLE `project.staging.temp_analysis`
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
) AS
SELECT * FROM source_table WHERE condition;

In dbt, use hours_to_expiration for temporary or staging models that don’t need to persist:

{{ config(
    materialized='table',
    hours_to_expiration=168  -- 7 days
) }}

This is particularly useful for dbt development targets where every developer creates personal datasets that grow indefinitely without expiration policies.

The Storage Optimization Hierarchy

Storage optimization sits at priority 5 in the overall cost optimization hierarchy — after query patterns, incremental models, governance, and pricing model selection. Storage is 10-15% of the typical BigQuery bill, so a 50% storage reduction translates to 5-7.5% off total spend. The effort is minimal:

1. Run the compression ratio query across all datasets (10 minutes)
2. Switch to physical billing where compression ratio exceeds 2:1 (one ALTER SCHEMA per dataset)
3. Set expiration policies on staging and development datasets (one ALTER SCHEMA per dataset)
4. Let long-term storage work automatically — no action required beyond preferring incremental over full-refresh materializations

These are one-time changes with permanent savings.