bq_per_table_storage_billing_recommendation.sql

bq_per_table_storage_billing_recommendation.sql

DECLARE active_logical_price_per_gb NUMERIC DEFAULT 0.02;
DECLARE long_term_logical_price_per_gb NUMERIC DEFAULT 0.01;
DECLARE active_physical_price_per_gb NUMERIC DEFAULT 0.04;
DECLARE long_term_physical_price_per_gb NUMERIC DEFAULT 0.02;
WITH storage AS
(
  SELECT DISTINCT
    tb.table_schema AS dataset,
    tb.table_name,
    total_rows,
    total_partitions,
    
    -- Uncompressed bytes logical
    total_logical_bytes AS total_logical_bytes,
    ROUND(total_logical_bytes/POW(1024, 3),2) AS total_logical_gib,
    ROUND(total_logical_bytes/POW(1024, 4),2) AS total_logical_tib,

    active_logical_bytes AS active_logical_bytes,
    -- ROUND(SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 3),2) AS active_logical_gib,
    -- ROUND(SUM(IF(deleted=false, active_logical_bytes, 0)) / power(1024, 4),2) AS active_logical_tib,
    ROUND(active_logical_bytes/POW(1024, 3),2) AS active_logical_gib,
    ROUND(active_logical_bytes/POW(1024, 4),2) AS active_logical_tib,


    long_term_logical_bytes AS long_term_logical_bytes,
    -- ROUND(SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 3),2) AS long_term_logical_gib,
    -- ROUND(SUM(IF(deleted=false, long_term_logical_bytes, 0)) / power(1024, 4),2) AS long_term_logical_tib,
    ROUND(long_term_logical_bytes/POW(1024, 3),2) AS long_term_logical_gib,
    ROUND(long_term_logical_bytes/POW(1024, 4),2) AS long_term_logical_tib,

    -- Compressed bytes physical
    total_physical_bytes AS total_physical_bytes,
    ROUND(total_physical_bytes/POW(1024, 3),2) AS total_physical_gib,
    ROUND(total_physical_bytes/POW(1024, 4),2) AS total_physical_tib,

    -- Note that active physical bytes includes time travel so need to remove that
    active_physical_bytes-time_travel_physical_bytes AS active_physical_bytes,
    ROUND((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3),2) AS active_physical_gib,
    ROUND((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 4),2) AS active_physical_tib,

    long_term_physical_bytes AS long_term_physical_bytes,
    ROUND(long_term_physical_bytes/POW(1024, 3),2) AS long_term_physical_gib,
    ROUND(long_term_physical_bytes/POW(1024, 4),2) AS long_term_physical_tib,

    time_travel_physical_bytes AS time_travel_physical_bytes,
    ROUND(time_travel_physical_bytes/POW(1024, 3),2) AS time_travel_physical_gib,
    ROUND(time_travel_physical_bytes/POW(1024, 4),2) AS time_travel_physical_tib,

    fail_safe_physical_bytes AS fail_safe_physical_bytes,
    ROUND(fail_safe_physical_bytes/POW(1024, 3),2) AS fail_safe_physical_gib,
    ROUND(fail_safe_physical_bytes/POW(1024, 4),2) AS fail_safe_physical_tib,
    
    -- Compression ratios
    ROUND(SAFE_DIVIDE(total_logical_bytes, total_physical_bytes),2) AS total_compression_ratio,
    ROUND(SAFE_DIVIDE(active_logical_bytes, active_physical_bytes),2) AS active_compression_ratio,
    ROUND(SAFE_DIVIDE(long_term_logical_bytes, long_term_physical_bytes),2) AS long_term_compression_ratio,

    -- logical Pricing
    ROUND(((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb) +
      ((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb),2) AS total_logical_price,

    ROUND(((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb),2) AS active_logical_price,

    ROUND(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb),2) AS long_term_logical_price,

    -- physical Pricing
    ROUND((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb) +
      ((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb),2) AS total_physical_price,

    ROUND((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb),2) AS active_physical_price,

    ROUND((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb,2) AS long_term_physical_price,

    ROUND((time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb,2) AS time_travel_physical_price,

    ROUND((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb,2) AS fail_safe_physical_price,

    -- Price differences, note that >0 means physical storage is cheaper before adding in time travel and failsafe
    ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))-((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb)),2) AS active_price_difference,

   ROUND((((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb))-((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb),2) AS long_term_price_difference,

    -- Time travel and fail safe storage reductions
    ROUND((( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2) AS additional_costs_for_physical_storage,

    -- Totals for each model
    ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)),2) AS logical_storage_price,

    ROUND((((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+
      (( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2) AS physical_storage_price,

    -- Difference in values (logical - active)
    ROUND(((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)))
    -
    (
      (((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+
      (( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb))
    ),2) AS difference_price,

    -- Recommendation
    IF((ROUND((((active_logical_bytes/POW(1024, 3))*active_logical_price_per_gb))+(((long_term_logical_bytes/POW(1024, 3))*long_term_logical_price_per_gb)),2)) < (ROUND((((((active_physical_bytes-time_travel_physical_bytes)/POW(1024, 3))*active_physical_price_per_gb))+((long_term_physical_bytes/POW(1024, 3))*long_term_physical_price_per_gb))+(( (time_travel_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)+((fail_safe_physical_bytes/POW(1024, 3))*active_physical_price_per_gb)),2)),'Logical storage', 'Physical storage') AS recommendation

  FROM
    `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE AS tb
  JOIN `region-us`.INFORMATION_SCHEMA.TABLES AS t
    ON t.table_catalog = tb.project_id
      AND t.table_name = tb.table_name
  WHERE
    tb.deleted = false
)
select * from storage order by dataset,table_name,difference_price;