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 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
- 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
- BigQuery on-demand vs Editions: which costs less for your queriesPriced
- BigQuery repeated scans: queries that run every dayTo investigate
- BigQuery large scans: queries reading 100 GiB or moreTo investigate
- BigQuery physical storage billing: when it costs lessPriced
- BigQuery logical storage billing: when to move backPriced
- BigQuery time travel window: the cost on physical billingTo investigate
- BigQuery idle reservation capacity: slots billed, not usedTo investigate
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