BigQuery FinOps: PHYSICAL vs LOGICAL Storage Savings Calculator

Supanote Core Team @supanote · 2 min read

plain (sql) 1 day ago · 60 lines · 4 views

Artifact

Verified Blueprint Google BigQuery

Audit every BigQuery dataset via INFORMATION_SCHEMA.TABLE_STORAGE to calculate exact monthly savings from switching to PHYSICAL billing.

✓ Accounts for 7-day time_travel_physical_bytes and fail_safe_physical_bytes so savings are never overestimated
✓ Computes exact compression ratios and projected monthly USD delta per dataset using regional list rates
✓ Outputs ready-to-execute ALTER SCHEMA ... SET OPTIONS(storage_billing_model = PHYSICAL) DDL

Quick Fetch

-- 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;

1-- Google BigQuery FinOps: Dataset-Level LOGICAL vs PHYSICAL Storage Cost Calculator

2-- Replace 'region-us' with your BigQuery region (e.g. 'region-eu', 'region-us-central1')

3-- Evaluates active + long-term + 7-day time-travel + 7-day fail-safe compressed bytes

5DECLARE logical_active_gib_usd FLOAT64 DEFAULT 0.020;

6DECLARE logical_longterm_gib_usd FLOAT64 DEFAULT 0.010;

7DECLARE physical_active_gib_usd FLOAT64 DEFAULT 0.040;

8DECLARE physical_longterm_gib_usd FLOAT64 DEFAULT 0.020;

10WITH dataset_footprints AS (

11 SELECT

12 table_schema AS dataset_name,

13 COUNT(table_name) AS table_count,

14 SUM(active_logical_bytes) / POW(1024, 3) AS active_logical_gib,

15 SUM(long_term_logical_bytes) / POW(1024, 3) AS longterm_logical_gib,

16 -- Physical billing includes time-travel and fail-safe bytes at active physical rate

17 SUM(active_physical_bytes + time_travel_physical_bytes + fail_safe_physical_bytes) / POW(1024, 3) AS active_physical_total_gib,

18 SUM(long_term_physical_bytes) / POW(1024, 3) AS longterm_physical_gib,

19 SAFE_DIVIDE(

20 SUM(total_logical_bytes),

21 NULLIF(SUM(total_physical_bytes + fail_safe_physical_bytes), 0)

22 ) AS effective_compression_ratio

23 FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT

24 WHERE total_logical_bytes > 0

25 GROUP BY table_schema

26),

27cost_comparison AS (

28 SELECT

29 dataset_name,

30 table_count,

31 ROUND(active_logical_gib + longterm_logical_gib, 2) AS logical_total_gib,

32 ROUND(active_physical_total_gib + longterm_physical_gib, 2) AS physical_billed_gib,

33 ROUND(effective_compression_ratio, 2) AS compression_x,

34 ROUND(

35 active_logical_gib * logical_active_gib_usd + longterm_logical_gib * logical_longterm_gib_usd,

36 2

37 ) AS est_logical_monthly_usd,

38 ROUND(

39 active_physical_total_gib * physical_active_gib_usd + longterm_physical_gib * physical_longterm_gib_usd,

40 2

41 ) AS est_physical_monthly_usd

42 FROM dataset_footprints

43)

44SELECT

45 dataset_name,

46 table_count,

47 logical_total_gib,

48 physical_billed_gib,

49 compression_x,

50 est_logical_monthly_usd,

51 est_physical_monthly_usd,

52 ROUND(est_logical_monthly_usd - est_physical_monthly_usd, 2) AS monthly_savings_usd,

53 CASE

54 WHEN est_logical_monthly_usd - est_physical_monthly_usd > 10 THEN

55 FORMAT("ALTER SCHEMA `%s` SET OPTIONS (storage_billing_model = 'PHYSICAL');", dataset_name)

56 ELSE '-- Keep LOGICAL (compression < ~2x after time-travel overhead)'

57 END AS recommended_ddl

58FROM cost_comparison

59ORDER BY monthly_savings_usd DESC;

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related Google BigQuery & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

SQL Google BigQuery

BigQuery INFORMATION_SCHEMA.JOBS Cost, Slot & Spill Profiler

Group 30-day BigQuery INFORMATION_SCHEMA.JOBS by normalized query fingerprint to expose top TB billed, slot contention, and shuffle spills.

SQL Google BigQuery

BigQuery GoogleSQL Pipe Syntax (|>) & GROUP BY ALL Blueprint

Refactor nested BigQuery CTEs into clean top-to-bottom GoogleSQL Pipe Syntax (|>) with early partition pruning, EXTEND, and AGGREGATE.

SQL SQLite

High-Performance SQLite Production PRAGMAs

Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.

SQL SQLite

SQLite FTS5 Full-Text Search Schema & Sync Triggers

External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.

Interactive Developer Workbench: Online CSV & TSV Table Viewer

Turn raw comma, tab, or semicolon-delimited text into a fast, sortable spreadsheet table in one paste.

Open CSV & TSV Viewer →