TABLE_STORAGE_BY_FOLDER view

The INFORMATION_SCHEMA.TABLE_STORAGE_BY_FOLDER view contains one row for each table or materialized view in the parent folder of the current project, including its subfolders.

This table doesn't maintain real-time data and might be delayed by a few seconds to a few minutes. Storage changes that result from partition or table expiration alone, or from modifications to the dataset time travel window, might take up to a day to appear in the INFORMATION_SCHEMA.TABLE_STORAGE view. If a dataset containing more than 1,000 tables is deleted, this view won't reflect the change until the time travel window for the deleted dataset has passed.

The table storage views give you a convenient way to observe your current storage consumption, and in addition provide details on whether your storage uses logical uncompressed bytes, physical compressed bytes, or time travel bytes. This information can help you with tasks like planning for future growth and understanding the update patterns for tables.

Data included in the *_BYTES columns

The *_BYTES columns in the table storage views include information about your use of storage bytes. This information is determined by looking at your storage usage for materialized views and the following types of tables:

• Permanent tables created through any of the methods described in Create and use tables.
• Temporary tables created in sessions. These tables are placed into datasets with generated names like "_c018003e063d09570001ef33ae401fad6ab92a6a".
• Temporary tables created in multi-statement queries ("scripts"). These tables are placed into datasets with generated names like "_script72280c173c88442c3a7200183a50eeeaa4073719".

Data stored in the query results cache is not billed to you and so is not included in the *_BYTES column values.

Clones and snapshots show *_BYTES column values as if they were complete tables, rather than showing the delta from the storage used by the base table, so they are an over-estimation. Your bill does account correctly for this delta in storage usage. For more information on the delta bytes stored and billed by clones and snapshots, see the TABLE_STORAGE_USAGE_TIMELINE view.

Forecast storage billing

In order to forecast the monthly storage billing for a dataset, you can use either the logical or physical *_BYTES columns in this view, depending on the dataset storage billing model used by the dataset. Please note that this is only a rough forecast, and the precise billing amounts are calculated based on the usage by BigQuery storage billing infrastructure and visible in Cloud Billing.

For datasets that use a logical billing model, you can forecast your monthly storage costs as follows:

((ACTIVE_LOGICAL_BYTES value / POW(1024, 3)) * active logical bytes pricing) + ((LONG_TERM_LOGICAL_BYTES value / POW(1024, 3)) * long-term logical bytes pricing)

The ACTIVE_LOGICAL_BYTES value for a table reflects the active bytes currently used by that table.

For datasets that use a physical billing model, you can forecast your storage costs as follows:

((ACTIVE_PHYSICAL_BYTES + FAIL_SAFE_PHYSICAL_BYTES value / POW(1024, 3)) * active physical bytes pricing) + ((LONG_TERM_PHYSICAL_BYTES value / POW(1024, 3)) * long-term physical bytes pricing)

The ACTIVE_PHYSICAL_BYTES value for a table reflects the active bytes currently used by that table plus the bytes used for time travel for that table.

To see the active bytes of the table alone, subtract the TIME_TRAVEL_PHYSICAL_BYTES value from the ACTIVE_PHYSICAL_BYTES value.

For more information, see Storage pricing.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_STORAGE_BY_FOLDER view, you need the following Identity and Access Management (IAM) permissions for the parent folder of the project:

• bigquery.tables.get
• bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

• roles/bigquery.admin
• roles/bigquery.dataViewer
• roles/bigquery.dataEditor
• roles/bigquery.metadataViewer

For more information about BigQuery permissions, see BigQuery IAM roles and permissions.

Schema

The INFORMATION_SCHEMA.TABLE_STORAGE_BY_FOLDER view has the following schema:

Scope and syntax

Queries against this view must include a region qualifier. The following table explains the region scope for this view:

• Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: any dataset region name. For example, `region-us`.

To retrieve storage information for tables in the specified project's parent folder, run the following query:

SELECT * FROM `myProject`.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_FOLDER;

Example

The following query shows which projects in a folder use the most storage:

SELECT
  project_id,
  SUM(total_logical_bytes) AS total_logical_bytes
FROM
  `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_FOLDER
GROUP BY
  project_id
ORDER BY
  total_logical_bytes DESC;

The result is similar to the following:

+---------------------+---------------------+
|     project_id      | total_logical_bytes |
+---------------------+---------------------+
| projecta            |     971329178274633 |
+---------------------+---------------------+
| projectb            |     834638211024843 |
+---------------------+---------------------+
| projectc            |     562910385625126 |
+---------------------+---------------------+