Skip to content
Finitizer
To investigate

BigQuery storage billing model: when the report cannot tell

At organization scope, BigQuery has no view of each dataset's storage billing model.

Where logical and physical billing differ a lot, the report asks you to confirm the model per project.

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

Storage bytes for every dataset in the organization, from INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION.

Why it costs money

  • The same dataset can cost noticeably more on one model than the other.
  • Without SCHEMATA_OPTIONS, which exists per project only, the report cannot see which model a dataset uses now.

How the tool measures it

To investigate. The report shows what this costs now and where to look. It claims no saving, because the results cannot prove one.

The report shows the gap between the two models for each dataset. It claims no saving, because the dataset may already be on the cheaper one.

When the report raises it. Query 3 is run at organization scope and some datasets differ by at least $1 a month between the models.

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. 1Run query 3 at project scope in each project that matters, then follow the physical or logical billing check.

How to tell it worked. Project-scope query 3 shows the model.

Trade-offs. A model change takes up to 24 hours and locks 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