Review BigQuery long-term storage billing
On this page
This check is due for a source refresh. Confirm the current documentation before you rely on provider-specific details.
Storage forecasts can overstate long-term storage when they rely on data age alone. Check each partition's billed tier and last write time, then confirm the dataset's billing model before changing retention or cost assumptions. Skipping this can misclassify billed storage and omit physical-retention charges.
What you need first
- Tool
- Use GoogleSQL, BigQuery's SQL query language, to read the INFORMATION_SCHEMA.PARTITIONS metadata view. Start with the project ID, dataset ID and location, then submit the SELECT as a BigQuery query job.
- Access
- The view requires bigquery.tables.get and bigquery.tables.list for the dataset. The documented roles/bigquery.dataViewer role includes these permissions. If you cannot submit a query job, ask an authorized BigQuery colleague to run the SELECT for you.
- If you do not use that tool
- Give an authorized BigQuery colleague the project and dataset IDs and location. Ask them to run this read-only query, return its rows, and confirm whether the dataset uses logical or physical storage billing.
Why this is worth a look
Skipping this review can misclassify storage because age alone does not establish long-term eligibility. BigQuery uses a partition's most recent write time for the automatic transition after 90 days. Storage billing measures logical, uncompressed bytes or physical, compressed bytes, with the model set per dataset. Under physical billing, time travel and fail-safe retention are charged separately at active physical storage rates. Under logical billing, those costs are included in the base rate.
Start with this query
SQLRun this read-only GoogleSQL query as a BigQuery job in the dataset's location after replacing both IDs. It returns current partition metadata for one dataset, not a historical time window. Both size fields are in bytes.
SELECT
table_catalog,
table_schema,
table_name,
partition_id,
storage_tier,
last_modified_time,
total_rows,
total_logical_bytes,
total_billable_bytes
FROM `PROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.PARTITIONS`
ORDER BY
table_name,
partition_id,
storage_tier;How to confirm it
- 01
Confirm scope and billing model
Record the project, dataset, location and logical or physical storage billing model with the dataset owner. Do not assume the logical default is still current, because the dataset setting can change.
- 02
Run the read-only query
Replace PROJECT_ID and DATASET_ID, then submit the query in the dataset's location. If the dataset has more than 1,000 tables, repeat the query for each required table with a predicate such as WHERE table_name = 'mytable', or for explicitly defined, non-overlapping table-name groups, so every table is covered once.
- 03
Separate tiers and byte measures
Group the results by storage_tier. ACTIVE is billed as active storage and LONG_TERM as long-term storage. Keep total_logical_bytes and total_billable_bytes distinct because they do not match under physical billing.
- 04
Review writes before forecasting
Check last_modified_time on ACTIVE rows before assuming long-term eligibility. Use storage_tier for the reported billed tier, not an age inferred from partition_id. Do not treat these rows as a complete physical-storage cost forecast.
Before making changes
Treat last_modified_time as an eligibility signal, not a complete change audit, because record deletions might not be reflected. Assumptions: the dataset exists, the runner can submit jobs and has the listed view permissions, the job uses the dataset's location, and each query covers at most 1,000 tables. PARTITIONS is a Preview view. Keep both __UNPARTITIONED__ rows when ACTIVE and LONG_TERM coexist, because they represent different tiers rather than duplicates.