BigQuery physical vs. logical storage: compare the full bill

An append-only archive and a staging dataset rebuilt every hour can have very different storage bills, even when their current tables occupy the same space. The staging dataset also retains versions of the data it replaced. Under physical billing, those versions matter to the price.

We’d compare storage billing per dataset rather than choose one model for the whole warehouse. For a small dataset, leave the default alone until the potential saving is worth investigating. For a material storage expense, measure compression and retained history through a representative processing cycle before switching.

What the billing model changes

BigQuery stores data in compressed form under either model. Logical billing measures the uncompressed size; physical billing measures compressed storage. Changing the setting doesn’t convert the tables or make queries faster. Google’s storage overview explains the distinction.

Use Google Cloud’s regional BigQuery storage rates. A compression ratio isn’t enough: the unit prices differ, and the retained bytes may be larger than the current table. Compare both forecasts using the same currency, period, and pricing assumptions.

Measure current data and retained versions

This read-only GoogleSQL query produces the four quantities needed for an ordinary base-table comparison. Run it in the dataset’s location, replace region-europe-west1 as needed, and supply @dataset_name as a string parameter. The region-qualified view uses the current project unless you explicitly qualify another project.

select
  table_schema as dataset_name,

  sum(if(deleted, 0, active_logical_bytes)) / pow(1024, 3)
    as active_logical_gib,

  sum(if(deleted, 0, long_term_logical_bytes)) / pow(1024, 3)
    as long_term_logical_gib,

  sum(active_physical_bytes + fail_safe_physical_bytes) / pow(1024, 3)
    as active_billable_physical_gib,

  sum(long_term_physical_bytes) / pow(1024, 3)
    as long_term_physical_gib,

from
  `region-europe-west1`.INFORMATION_SCHEMA.TABLE_STORAGE

where
  table_schema = @dataset_name
  and table_type = 'BASE TABLE'

group by
  table_schema;

Multiply each quantity by its corresponding monthly rate. The two logical amounts form one forecast; the two physical amounts form the other.

The important detail in Google Cloud’s TABLE_STORAGE reference is that active_physical_bytes already includes time travel. Add fail_safe_physical_bytes, not time travel a second time. Keep deleted tables in the physical calculation while excluding them from logical charges. Deletion doesn’t necessarily remove their retained physical storage immediately.

This is a snapshot estimate, not an invoice. Storage is billed over time, metadata can lag, and the query deliberately excludes other table types. Clones and snapshots need particular care: their metadata can report full sizes even though billing accounts for shared storage. Google Cloud’s byte-accounting notes explain that limitation.

Include the rebuild that hasn’t happened yet

Take measurements across normal daily loads and the largest routine replacement or backfill. An estimate taken just before a rebuild can miss the history that rebuild is about to create. An estimate taken immediately after a one-time migration can exaggerate the ongoing cost.

Append-heavy history is a reasonable candidate for physical billing when it compresses well and rarely changes. Repeated table replacement deserves a closer look. So do frequent partition rewrites and large updates. These are starting hypotheses, not rules that every raw dataset should be physical and every staging dataset logical.

Retained history is also not simply the number of changed rows multiplied by row size. BigQuery versions storage blocks; a small change can cause a larger block to be retained. Measure the resulting bytes rather than predict them from the number of records your pipeline updated. This behavior is covered in Google Cloud’s explanation of how time travel works.

Price the recovery requirement too

The time-travel window is configurable from two to seven days. It cannot be reduced to zero. A further seven-day fail-safe period isn’t configurable or a replacement for normal self-service recovery. Both periods affect physical billing; neither is charged separately under logical billing. See Google Cloud’s retention documentation.

Choose the window from the damage you need to recover from. Two days may be enough for a replayable staging dataset. A curated dataset whose overwrite might go unnoticed over a long weekend may need more. Shorten the time-travel window only if it still covers the mistakes you need to recover from.

Put tables with materially different recovery or rewrite behavior in separate datasets when that separation also makes operational sense. Don’t introduce several new datasets to save a trivial amount while making access and maintenance harder.

Switch only on a meaningful difference

Google Cloud’s switching rules specify a 24-hour delay before a billing-model change takes effect and a 14-day wait before another change. Switching doesn’t reset long-term storage eligibility.

Record the forecast, the observation period, and the expected monthly difference. Switch when the advantage survives normal churn and is large enough to justify the change. Recheck when the pipeline’s rewrite pattern changes.

When query compute dominates the bill, leave this setting out of the performance discussion. Investigate repeated scans and table shape instead.