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?
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.