Helps you estimate the physical storage pricing change for your BigQuery project(s)

bigquery_physical_storage_pricing_estimator.sql

DECLARE active_logical_gib_price FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical_gib_price FLOAT64 DEFAULT 0.01;
DECLARE active_physical_gib_price FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical_gib_price FLOAT64 DEFAULT 0.02;

WITH
 storage_sizes AS (
   SELECT
     table_schema AS dataset_name,
      -- Logical
      SUM(active_logical_bytes) / power(1024, 3) AS active_logical_gib,
      SUM(long_term_logical_bytes) / power(1024, 3) AS long_term_logical_gib,
      -- Physical
      SUM(active_physical_bytes - time_travel_physical_bytes) / power(1024, 3) AS active_no_time_travel_physical_gib,
      SUM(time_travel_physical_bytes) / power(1024, 3) AS time_travel_physical_gib,
      SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gib,
    FROM
      `region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
    WHERE total_logical_bytes > 0
    AND total_physical_bytes > 0
    GROUP BY 1
 )
SELECT
dataset_name,
-- Logical
ROUND(active_logical_gib, 2) AS active_logical_gib,
ROUND(long_term_logical_gib, 2) AS long_term_logical_gib,
-- Physical
ROUND(active_no_time_travel_physical_gib, 2) AS active_no_time_travel_physical_gib,
ROUND(time_travel_physical_gib, 2) AS time_travel_physical_gib,
ROUND(long_term_physical_gib, 2) AS long_term_physical_gib,
-- Compression ratio
ROUND(SAFE_DIVIDE(active_logical_gib, active_no_time_travel_physical_gib), 2) AS active_compression_ratio,
ROUND(SAFE_DIVIDE(long_term_logical_gib, long_term_physical_gib), 2) AS long_term_compression_ratio,
-- Costs logical
ROUND(active_logical_gib * active_logical_gib_price, 2) AS active_logical_cost,
ROUND(long_term_logical_gib * long_term_logical_gib_price, 2) AS long_term_logical_cost,
-- Costs physical
ROUND((active_no_time_travel_physical_gib + time_travel_physical_gib) * active_physical_gib_price, 2) AS active_physical_cost,
ROUND(long_term_physical_gib * long_term_physical_gib_price, 2) AS long_term_physical_cost,
-- Costs total
ROUND(((active_logical_gib * active_logical_gib_price) + (long_term_logical_gib * long_term_logical_gib_price)) -
(((active_no_time_travel_physical_gib + time_travel_physical_gib) * active_physical_gib_price) + (long_term_physical_gib * long_term_physical_gib_price)), 2) AS total_cost_difference
FROM
storage_sizes
ORDER BY
(active_logical_cost + active_physical_cost) DESC;