Skip to content
Finitizer
Priced

BigQuery physical storage billing: when it costs less

BigQuery datasets bill storage either by uncompressed (logical) bytes or by compressed (physical) bytes at a higher rate.

Data that compresses well is usually cheaper on physical billing.

Run this check on your own account

Run read-only queries in your own console and paste the results. The report is worked out in your browser, with no access to your account.

What the check looks at

Active and long-term bytes, logical and physical, time travel and fail-safe bytes and the current billing model of each dataset, from INFORMATION_SCHEMA.TABLE_STORAGE and SCHEMATA_OPTIONS.

Why it costs money

  • Logical billing charges uncompressed bytes and includes time travel and fail-safe in the rate.
  • Physical billing charges compressed bytes at a higher rate (twice the logical rate in the US multi-region), plus time travel and fail-safe at the active rate.
  • BigQuery data often compresses several times over, so physical bytes can be far smaller than logical ones.

How the tool measures it

Priced. The report puts a monthly saving range on this check, from your own query results at your price.

For each dataset on logical billing, the report prices both models at your location's list rates. The high end is today's snapshot; the low end doubles time travel and fail-safe bytes, because one snapshot can miss a busy week.

When the report raises it. Query 3 is provided and moving logically billed datasets would save at least $5 a month.

In the sample report

Check 1 dataset for physical storage billing

  • This dataset holds 240 TiB logical and 28 TiB physical, and costs about $3,072 a month now.

$2,257 to $2,331 a month, modeled.

From the tool's own on-demand sample: made-up results for one project over 30 days, at US multi-region list prices. The SQL above is for the US multi-region at project scope; the tool writes it for any location.

What to do

  1. 1Start with the largest dataset in the list.
  2. 2ALTER SCHEMA dataset SET OPTIONS(storage_billing_model = 'PHYSICAL').

How to tell it worked. Compare that dataset's storage SKUs in the billing export over a full month after the change.

Trade-offs. A change takes up to 24 hours to apply and cannot be changed back for 14 days. Legacy flat-rate commitments in the region block a move to physical. Query speed does not change.

The read-only query behind it

The same SQL the tool gives you. Each reads usage views and changes nothing.

Query 3: storage by dataset
-- Finitizer BigQuery Cost Check, query 3 of 3: storage by dataset.
-- Returns dataset names (the advice is per dataset) and byte totals. A snapshot
-- of now, so it has no date window. Follows Google's storage forecast: deleted
-- tables are left out of logical bytes and kept in physical bytes, because their
-- retained bytes still bill. Clones and snapshots report as complete tables, so
-- they are excluded and counted. Run it unchanged.
WITH options AS (
  SELECT
    schema_name,
    MAX(IF(option_name = 'storage_billing_model', option_value, NULL)) AS billing_model,
    MAX(IF(option_name = 'max_time_travel_hours', option_value, NULL)) AS time_travel_hours
  FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  WHERE option_name IN ('storage_billing_model', 'max_time_travel_hours')
  GROUP BY schema_name
),
storage AS (
  SELECT
    *,
    IF(STARTS_WITH(table_schema, '_'), '(hidden temporary datasets)', table_schema) AS dataset,
    IFNULL(deleted, FALSE)                                     AS is_deleted,
    IFNULL(table_type, 'BASE TABLE') IN ('BASE TABLE', 'MATERIALIZED VIEW') AS counted,
    IFNULL(total_physical_bytes, 0) + IFNULL(fail_safe_physical_bytes, 0) AS retained_bytes
  FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
)
SELECT
  s.dataset,
  COUNTIF(counted AND NOT is_deleted)                          AS tables,
  COUNTIF(NOT counted)                                         AS clones_and_snapshots,
  COUNTIF(counted AND is_deleted AND retained_bytes > 0)       AS deleted_tables,
  IFNULL(SUM(IF(counted AND NOT is_deleted, s.active_logical_bytes, 0)), 0)
                                                               AS active_logical_bytes,
  IFNULL(SUM(IF(counted AND NOT is_deleted, s.long_term_logical_bytes, 0)), 0)
                                                               AS long_term_logical_bytes,
  IFNULL(SUM(IF(counted, s.active_physical_bytes, 0)), 0)    AS active_physical_bytes,
  IFNULL(SUM(IF(counted, s.long_term_physical_bytes, 0)), 0) AS long_term_physical_bytes,
  IFNULL(SUM(IF(counted, s.time_travel_physical_bytes, 0)), 0)
                                                               AS time_travel_physical_bytes,
  IFNULL(SUM(IF(counted, s.fail_safe_physical_bytes, 0)), 0) AS fail_safe_physical_bytes,
  ANY_VALUE(o.billing_model)                                   AS billing_model,
  ANY_VALUE(o.time_travel_hours)                               AS time_travel_hours,
  COUNT(*) OVER ()                                             AS result_rows,
  CURRENT_TIMESTAMP()                                          AS snapshot_at,
  'us'                                                AS location,
  'project'                                             AS scope,
  '2026-11-07'                                         AS query_version
FROM storage s
LEFT JOIN options o ON o.schema_name = s.table_schema
GROUP BY s.dataset
ORDER BY SUM(IF(counted, s.total_physical_bytes, 0)) DESC;

Other checks in the report

All checks

What BigQuery charges in each location: BigQuery pricing in 2026.

Run this check every day, not once.

Finitizer reads your BigQuery job history and storage directly, raises these findings as they appear, and tracks each fix on the bill.

See how Finitizer works