BigQuery storage cost: logical vs physical billing, long-term and time travel

BigQuery bills storage by the GiB-month in one of 2 ways per dataset: logical billing on the uncompressed size, or physical billing on the compressed size at twice the rate. Physical billing usually saves money when data compresses more than about 2 times, but it also charges for time travel and fail-safe copies, so tables that are rewritten often can end up costing more.

Neither choice changes how data is stored or how fast queries run: BigQuery always stores data compressed. Only the meter changes, and it is set per dataset.

The prices

US multi-region, per GiB a month:

Most single regions cost a little more, for example $0.023 active logical in Iowa. The first 10 GiB a month is free. A table or partition that has not been modified for 90 consecutive days moves to long-term storage and its price halves on its own. Each partition counts on its own, and only modifying or deleting its data resets the clock.

The catch: time travel and fail-safe

When you update, delete or overwrite data, BigQuery keeps the old bytes: for the time travel window (7 days by default, 2 to 7 per dataset), then for a further 7 days of fail-safe. Under logical billing that is included. Under physical billing those bytes are charged at the active physical rate.

So a table that is replaced in full every day keeps up to 14 old copies around: 7 in time travel and 7 in fail-safe.

Worked examples

A typical warehouse dataset: 50 TiB logical (51,200 GiB), compressing 6 times, 40% of it long-term, and 2% of the active data changed or deleted each day.

• Logical: 30,720 GiB × $0.02 + 20,480 GiB × $0.01 = $819 a month.
• Physical: 8,533 GiB compressed, so 5,120 × $0.04 + 3,413 × $0.02 = $273, plus 14 days × 2% of 5,120 GiB = 1,434 GiB retained × $0.04 = $57: $330 a month.

Physical saves about $489 a month, 60%.

A table rebuilt every night with CREATE OR REPLACE: 1 TiB logical, compressing 6 times.

• Logical: 1,024 × $0.02 = $20 a month.
• Physical: 171 GiB × $0.04 = $7 for the table, plus 14 days of full copies, 2,389 GiB × $0.04 = $96: $102 a month, 5 times more.
• With the time travel window cut to 2 days, physical is still $68.

Data that compresses poorly: the same 50 TiB dataset at 1.8 times compression costs $819 logical and $1,101 physical. Below about 2 times, physical cannot win.

Try itLogical or physical storage billing?

Logical size (TiB)

50 TiB

Compression ratio

6×

Long-term share

40%

Active data changed or deleted a day

2%

Time travel window (days)

7

Logical billing$819

Physical billing$330

Switch this dataset to physical

Find out which wins for your datasets

INFORMATION_SCHEMA.TABLE_STORAGE has both sizes for every table. This query prices each dataset both ways at US multi-region rates:

SELECT
  table_schema AS dataset,
  SUM(total_logical_bytes) / SUM(total_physical_bytes) AS compression,
  SUM(active_logical_bytes) / POW(1024, 3) * 0.02
    + SUM(long_term_logical_bytes) / POW(1024, 3) * 0.01 AS logical_usd,
  (SUM(active_physical_bytes) + SUM(fail_safe_physical_bytes)) / POW(1024, 3) * 0.04
    + SUM(long_term_physical_bytes) / POW(1024, 3) * 0.02 AS physical_usd
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE NOT deleted
GROUP BY dataset
ORDER BY logical_usd - physical_usd DESC;

active_physical_bytes already includes time travel bytes, so the physical figure counts them. These are snapshots of today's sizes; the bill is bytes multiplied by time, so treat the result as a forecast. Swap in your region's prices from the BigQuery cost calculator.

Switch, and keep it cheap

ALTER SCHEMA mydataset SET OPTIONS (storage_billing_model = 'PHYSICAL');

The change takes 24 hours, and you then have to wait 14 days before switching again. It is not available while your organisation has legacy flat-rate slot commitments in the same region; edition commitments are fine.

Then:

1. Shorten time travel on datasets with heavy rewrites: ALTER SCHEMA mydataset SET OPTIONS (max_time_travel_hours = 48); (2 days, the minimum).
2. Append or merge instead of replacing whole tables, so fewer bytes are retained.
3. Partition by date, so old partitions stay untouched and reach long-term pricing.
4. Set expirations on staging and temporary tables.

Storage is usually the smaller part of a BigQuery bill; queries are the larger. Our guide to on-demand vs editions covers that side, and Snowflake vs BigQuery compares the 2 warehouses.