BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator
-- Google BigQuery FinOps: Dataset-Level LOGICAL vs PHYSICAL Storage Cost Calculator
-- Replace 'region-us' with your BigQuery region (e.g. 'region-eu', 'region-us-central1')
-- Evaluates active + long-term + 7-day time-travel + 7-day fail-safe compressed bytes
DECLARE logical_active_gib_usd FLOAT64 DEFAULT 0.020;
DECLARE logical_longterm_gib_usd FLOAT64 DEFAULT 0.010;
DECLARE physical_active_gib_usd FLOAT64 DEFAULT 0.040;
DECLARE physical_longterm_gib_usd FLOAT64 DEFAULT 0.020;
WITH dataset_footprints AS (
SELECT
table_schema AS dataset_name,
COUNT(table_name) AS table_count,
SUM(active_logical_bytes) / POW(1024, 3) AS active_logical_gib,
SUM(long_term_logical_bytes) / POW(1024, 3) AS longterm_logical_gib,
-- Physical billing includes time-travel and fail-safe bytes at active physical rate
SUM(active_physical_bytes + time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 3) AS active_physical_total_gib,
SUM(long_term_physical_bytes) / POW(1024, 3) AS longterm_physical_gib,
SAFE_DIVIDE(
SUM(total_logical_bytes),
NULLIF(SUM(total_physical_bytes + fail_safe_physical_bytes), 0)
) AS effective_compression_ratio
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT
WHERE total_logical_bytes > 0
GROUP BY table_schema
),
cost_comparison AS (
SELECT
dataset_name,
table_count,
ROUND(active_logical_gib + longterm_logical_gib, 2) AS logical_total_gib,
ROUND(active_physical_total_gib + longterm_physical_gib, 2) AS physical_billed_gib,
ROUND(effective_compression_ratio, 2) AS compression_x,
ROUND(
active_logical_gib * logical_active_gib_usd + longterm_logical_gib * logical_longterm_gib_usd,
2
) AS est_logical_monthly_usd,
ROUND(
active_physical_total_gib * physical_active_gib_usd + longterm_physical_gib * physical_longterm_gib_usd,
2
) AS est_physical_monthly_usd
FROM dataset_footprints
)
SELECT
dataset_name,
table_count,
logical_total_gib,
physical_billed_gib,
compression_x,
est_logical_monthly_usd,
est_physical_monthly_usd,
ROUND(est_logical_monthly_usd - est_physical_monthly_usd, 2) AS monthly_savings_usd,
CASE
WHEN est_logical_monthly_usd - est_physical_monthly_usd > 10 THEN
FORMAT("ALTER SCHEMA `%s` SET OPTIONS (storage_billing_model = 'PHYSICAL');", dataset_name)
ELSE '-- Keep LOGICAL (compression < ~2x after time-travel overhead)'
END AS recommended_ddl
FROM cost_comparison
ORDER BY monthly_savings_usd DESC;