Skip to content
Finitizer
Priced

BigQuery logical storage billing: when to move back

Physical storage billing charges time travel and fail-safe at the active rate.

Datasets that compress poorly or are rewritten often can cost more on physical billing than on logical.

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

The same storage figures as the physical billing check, for datasets already on physical billing.

Why it costs money

  • Each rewrite of a table keeps the old bytes for the time-travel window and 7 days of fail-safe.
  • On physical billing those bytes bill at the active rate; on logical billing they are part of the rate.

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 physical billing, the report prices both models. The high end is today's snapshot; the low end halves time travel and fail-safe bytes, in case the snapshot caught a busy week.

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

In the sample report

The tool's sample does not raise this check, because its made-up account does not meet the condition above.

What to do

  1. 1Check that the dataset's churn is normal, not a one-off backfill.
  2. 2ALTER SCHEMA dataset SET OPTIONS(storage_billing_model = 'LOGICAL').

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

Trade-offs. A change takes up to 24 hours and cannot be changed back for 14 days.

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