BigQuery Physical Storage Billing: Achieving 8x Compression on Telemetry Tables — How Does It Work in Production?

As Doddi Priyambodo, Google Cloud SEA Solutions Consultant and Principal Enterprise Architect, I frequently work with enterprise data teams managing petabyte-scale telemetry, observability, and IoT workloads.

One of the most impactful, yet misunderstood, FinOps levers in Google Cloud is BigQuery Physical Storage Billing. Telemetry data—characterized by repetitive JSON payloads, timestamps, and low-cardinality strings—compresses exceptionally well. However, by default, BigQuery bills on logical (uncompressed) bytes.

In this blueprint, we will architect a deterministic FinOps pipeline using the INFORMATION_SCHEMA.TABLE_STORAGE view to identify high-compression telemetry tables, migrate them to physical billing, and use Gemini 2.5 Pro to continuously monitor time-travel overhead.

What Google Cloud Shipped & The Enterprise Problem It Solves

The Enterprise Problem: Enterprises typically ingest terabytes of append-only telemetry data daily. Under BigQuery's default Logical Storage Billing, you are charged for the raw, uncompressed bytes of data ($0.02/GB for active, $0.01/GB for long-term). Because telemetry data often achieves an 8x to 12x compression ratio under the hood (via Capacitor, BigQuery's proprietary columnar format), paying for logical bytes leaves massive amounts of money on the table.

What Google Cloud Shipped: Google Cloud introduced Physical Storage Billing at the dataset level, allowing you to pay for the actual compressed bytes stored on disk ($0.04/GB active, $0.02/GB long-term). To provide visibility into this, Google shipped the INFORMATION_SCHEMA.TABLE_STORAGE view.

However, physical billing includes the storage used for Time Travel (up to 7 days of historical data for point-in-time recovery) and Fail-safe (an additional 7 days retained by Google for disaster recovery). If a table undergoes heavy UPDATE/DELETE operations (DML), the physical storage balloons because the old compressed blocks are retained in Time Travel. The architectural challenge is isolating append-only telemetry datasets, tuning their Time Travel windows, and safely switching them to physical billing without unexpected cost spikes.

Reference Architecture on Google Cloud

To operationalize this at scale, we separate storage billing models by dataset topology. Append-only telemetry is routed to physical-billed datasets, while DML-heavy dimensional data remains on logical billing. We deploy a Cloud Run job that queries TABLE_STORAGE and leverages Vertex AI to detect compression anomalies.

flowchart LR
    subgraph "Ingestion & Storage"
        A[Pub/Sub Telemetry Stream] -->|Dataflow / BQ Sub| B[(BigQuery Dataset:\nTelemetry_Physical)]
        C[CDC / OLTP Updates] -->|Datastream| D[(BigQuery Dataset:\nDimensions_Logical)]
    end

    subgraph "FinOps Automation (Cloud Run)"
        E[Cloud Scheduler] -->|Trigger| F[Cloud Run Job]
        B -.->|Query| G[`INFORMATION_SCHEMA.TABLE_STORAGE`]
        D -.->|Query| G
        G -->|Extract Metrics| F
    end

    subgraph "Vertex AI & Alerting"
        F -->|Prompt: Compression Ratios & Time Travel| H[Vertex AI: gemini-2.5-pro]
        H -->|JSON FinOps Recommendations| I[Pub/Sub: Alerts]
        I --> J[Slack / PagerDuty]
    end

    classDef gcp fill:#e8f0fe,stroke:#4285f4,stroke-width:2px,color:#1a73e8;
    class A,B,C,D,E,F,G,H,I,J gcp;

Step-by-Step Implementation

1. Querying TABLE_STORAGE for Compression Ratios

First, we must identify datasets where the compression ratio justifies the higher per-GB physical price. The break-even point is exactly 2x compression (since physical prices are double logical prices).

-- Execute in BigQuery to find FinOps migration candidates
SELECT 
  project_id,
  table_schema AS dataset_name,
  table_name,
  total_logical_bytes / pow(1024, 3) AS logical_gb,
  total_physical_bytes / pow(1024, 3) AS physical_gb,
  time_travel_physical_bytes / pow(1024, 3) AS time_travel_gb,
  fail_safe_physical_bytes / pow(1024, 3) AS fail_safe_gb,
  -- Calculate Compression Ratio (Logical / Active Physical)
  SAFE_DIVIDE(total_logical_bytes, active_physical_bytes) AS compression_ratio
FROM 
  `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE 
  total_logical_bytes > 0
ORDER BY 
  compression_ratio DESC;

2. Tuning Time Travel & Switching to Physical Billing

For append-only telemetry, 7 days of Time Travel is often unnecessary. We reduce it to 2 days (the minimum) to eliminate physical storage overhead, then alter the dataset billing model.

# 1. Update the dataset's default time travel to 48 hours (2 days)
bq update --dataset_default_table_expiration 0 \
  --max_time_travel_hours 48 \
  my_project:telemetry_dataset

# 2. Alter the dataset to use PHYSICAL storage billing
ALTER SCHEMA `my_project.telemetry_dataset`
SET OPTIONS(
  storage_billing_model = 'PHYSICAL'
);

3. Automating FinOps with Vertex AI (gemini-2.5-pro)

We deploy a Python script to Cloud Run that continuously monitors TABLE_STORAGE. If a physical-billed dataset suddenly experiences heavy DML (causing Time Travel bytes to spike), gemini-2.5-pro detects the anomaly and alerts the FinOps team.

import json
from google.cloud import bigquery
import vertexai
from vertexai.generative_models import GenerativeModel

# Initialize 2026 standard GCP clients
project_id = "my-enterprise-project"
vertexai.init(project=project_id, location="us-central1")
bq_client = bigquery.Client(project=project_id)

# Query the storage metadata
query = """
    SELECT table_schema, table_name, storage_billing_model,
           total_logical_bytes,