Review BigQuery long-term storage billing

Why this is worth a look

BigQuery sets its logical or physical storage billing model at the dataset level. A partition moves to long-term storage after 90 days, and its last write time is used for eligibility. INFORMATION_SCHEMA.PARTITIONS exposes storage_tier, last_modified_time, total_logical_bytes, and total_billable_bytes for a dataset. Read the rows by table and partition to compare tiers and byte measures. Under physical billing, time travel and fail-safe storage are charged separately at active storage rates. Under logical billing, they are included in the base rate.

Read-only GoogleSQL query that lists ACTIVE and LONG_TERM rows, byte measures, and last-write timestamps by table and partition. It returns current metadata, not a historical time series.

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

1. 01

   Set the dataset scope

   Replace PROJECT_ID and DATASET_ID with the project and dataset you want to review. PROJECT_ID is optional in the documented qualifier, but DATASET_ID is required.

2. 02

   Run it in the right location

   Submit the SELECT-only query as a BigQuery job in the dataset's location. The query needs bigquery.tables.get and bigquery.tables.list permissions for the INFORMATION_SCHEMA.PARTITIONS view.

3. 03

   Read the tier and byte fields

   Use storage_tier to separate ACTIVE from LONG_TERM rows. Compare total_logical_bytes with total_billable_bytes when reviewing a dataset that uses physical storage billing. These byte fields are reported in bytes.

4. 04

   Review the aging signal

   Use last_modified_time as the latest write timestamp for each partition. A partition moves automatically from active to long-term storage after 90 days, while record deletions might not appear in this timestamp.

Primary sources