google-cloud-platformgoogle-bigqueryinformation-schema

Bigquery view storage billing model of dataset

With BigQuery you can set the storage billing model of a dataset from LOGICAL to PHYSICAL to save costs.

We have done this for some datasets in the past but I would like to see for which ones. It seems that there is no way to access the active billing model for datasets. There is no overview of this anywhere.

I tried looking at the INFORMATION_SCHEMA for storage but there was no field indicating the billing model.

Does anyone have an idea where I can find this?

Solution

  • It is available but not documented. I use this query to get this information

      SELECT
    catalog_name,
    schema_name,
    OPTION_VALUE AS dataset_billing_model
  FROM
    <PROJECT_ID>.`region-<REGION>`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  WHERE
    OPTION_NAME="storage_billing_model"

    You must run one query per region to get all the dataset info.