How much of my BigQuery storage is on long-term pricing?
The active/long-term split by dataset, which decides half your storage bill.
Free to run — INFORMATION_SCHEMA scans no billable bytesChecked 4 Aug 2026
JOBS and TABLE_STORAGE are region-scoped.
SELECT
table_schema,
COUNT(*) AS tables,
ROUND(SUM(active_logical_bytes) / POW(1024, 4), 3) AS active_tib,
ROUND(SUM(long_term_logical_bytes) / POW(1024, 4), 3) AS long_term_tib,
ROUND(SAFE_DIVIDE(
SUM(long_term_logical_bytes),
SUM(total_logical_bytes)), 3) AS long_term_fraction
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE NOT deleted
GROUP BY table_schema
ORDER BY active_tib DESC;What it returns
- long_term_fraction
- Share of the dataset already on the ~50% discount.
How to read it
- Long-term pricing applies automatically after 90 days without modification — there is nothing to buy and nothing to configure.
- Any write to a table resets it to active pricing for the whole table, not just the changed rows. A daily full-refresh pipeline guarantees you never get the discount; partitioned incremental loads let old partitions age into it.
- A dataset stuck at a low long-term fraction with slow-changing data is usually a pipeline writing WRITE_TRUNCATE where it could write to a partition.
Run this once, or have it run every day.
Finitizer evaluates this class of question continuously against your live BigQuery estate, tracks how each number moves between runs, and turns findings into assigned tasks. Read-only and keyless.