bq_table_storage.sql

bq_table_storage.sql

-- requires: --role=roles/bigquery.metadataViewer
-- make sure to change to correct region in line 15 & 32 !

WITH base_ AS (
    SELECT
    CONCAT(project_id, ":", table_schema) AS dataset_name
    , SUM(ACTIVE_LOGICAL_BYTES)/POW(1024, 3) active_logical_gib
    , SUM(LONG_TERM_LOGICAL_BYTES)/POW(1024, 3) longterm_logical_gib
    , SUM(ACTIVE_PHYSICAL_BYTES) /POW(1024, 3) active_physical_gib
    , SUM(LONG_TERM_PHYSICAL_BYTES) /POW(1024, 3) longterm_physical_gib
    , SUM(TIME_TRAVEL_PHYSICAL_BYTEs)/POW(1024, 3) time_travel_physical_gib
    , (1- (SUM(total_physical_bytes) /  SUM(total_logical_bytes)))*100  compression_ratio
    , SUM(ACTIVE_LOGICAL_BYTES)/POW(1024, 3) * 0.02 + SUM(LONG_TERM_LOGICAL_BYTES)/POW(1024, 3) * 0.01 logical_storage_pricing
    --, SUM(ACTIVE_PHYSICAL_BYTES)/POW(1024, 3) * 0.04 + SUM(LONG_TERM_PHYSICAL_BYTES)/POW(1024, 3) * 0.02 physical_storage_pricing # US
    , SUM(ACTIVE_PHYSICAL_BYTES)/POW(1024, 3) * 0.044 + SUM(LONG_TERM_PHYSICAL_BYTES)/POW(1024, 3) * 0.022 physical_storage_pricing # EU
    FROM
    `region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
    WHERE total_rows > 0
    GROUP BY 1
 )

SELECT
  dataset_name
  , ROUND(logical_storage_pricing,2) logical_storage_pricing
  , ROUND(physical_storage_pricing,2) physical_storage_pricing
  , ROUND(compression_ratio,2) compression_ratio
  , CASE WHEN logical_storage_pricing <= 5 THEN NULL
    ELSE ROUND((1-(SAFE_DIVIDE(physical_storage_pricing,logical_storage_pricing)))*100,2)
    END cost_reduction_by_percent
FROM base_     
ORDER BY 2 DESC

consider changing names to GiB instead of bytes

, SUM(ACTIVE_LOGICAL_BYTES)/POW(1024, 3) active_logical_bytes
, SUM(LONG_TERM_LOGICAL_BYTES)/POW(1024, 3) longterm_logical_bytes

add 'where' total_rows > ' to avoid division by zero

@nadavwn Thanks for the ideas! ✅

Suggest to add after line 15 the following to calculate using the FAIL_SAFE:
, SUM(ACTIVE_PHYSICAL_BYTES)/POW(1024, 3) * 0.044 + SUM(LONG_TERM_PHYSICAL_BYTES)/POW(1024, 3) * 0.022 + SUM(FAIL_SAFE_PHYSICAL_BYTES)/POW(1024, 3) * 0.044

The query is missing the following condition to exclude EXTERNAL_TABLES:
AND table_type = 'BASE TABLE'