TL;DR. BigQuery storage pricing has four rates per location: active and long-term, logical and physical. The common rule says physical billing wins above 2:1 compression, but that break-even is exact in only one of the 48 locations on Google's price list, the US multi-region; in 34 others, physical needs more than 2:1 on active bytes. Across all 48 the active break-even runs from 1.73 to 3.04, and a table rebuilt every day, like a dbt
tablemodel, needs about 15 times that ratio: 30:1 in the US. Decide per table, then group tables into datasets.
BigQuery storage pricing charges per GiB-month at four rates, and you pick, per dataset, whether BigQuery counts uncompressed (logical) or compressed (physical) bytes. In the US multi-region, Google's pricing page lists $0.02 and $0.01 per GiB-month for active and long-term logical storage, and $0.04 and $0.02 for physical, read November 20, 2026. Those round numbers are where the 2:1 rule comes from.
Everything priced below is a model built from Google's list prices, not a measurement on a live account. The queries follow Google's documentation of the same date.
BigQuery storage pricing: four rates, one decision per dataset
Physical billing pays when a table's logical bytes divided by its billed physical bytes exceed the physical-to-logical price ratio. That ratio is set by location. Google's pricing page lists storage rates for 48 locations, and only one, the US multi-region, has a ratio of exactly 2.00 for both active and long-term bytes. Active ratios run from 1.73 (Dammam) to 3.04 (Sao Paulo); long-term ratios from 1.23 (Dammam) to 2.30.
| Location | Active logical | Active physical | Active break-even | Long-term logical | Long-term physical | Long-term break-even | Rebuilt daily |
|---|---|---|---|---|---|---|---|
| US (multi-region) | $0.02 | $0.04 | 2.00 | $0.01 | $0.02 | 2.00 | 30.0 |
| EU (multi-region) | $0.02 | $0.044 | 2.20 | $0.01 | $0.022 | 2.20 | 33.0 |
| Iowa (us-central1) | $0.023 | $0.04 | 1.74 | $0.016 | $0.02 | 1.25 | 26.1 |
| South Carolina (us-east1) | $0.023 | $0.044 | 1.91 | $0.016 | $0.022 | 1.38 | 28.7 |
| Northern Virginia (us-east4) | $0.023 | $0.05 | 2.17 | $0.016 | $0.025 | 1.56 | 32.6 |
| Montreal (northamerica-northeast1) | $0.023 | $0.05 | 2.17 | $0.016 | $0.025 | 1.56 | 32.6 |
| Frankfurt (europe-west3) | $0.023 | $0.052 | 2.26 | $0.016 | $0.026 | 1.63 | 33.9 |
| Sao Paulo (southamerica-east1) | $0.023 | $0.07 | 3.04 | $0.016 | $0.035 | 2.19 | 45.7 |
Prices are per GiB-month. "Rebuilt daily" is the active break-even times 15, explained below. In Iowa, physical billing pays above 1.25:1 on long-term data; in Sao Paulo, active data needs more than 3.04:1. For scale, DoiT reports compression rates between 1:6 and 1:12 in its internal tests.
One trap sits in Google's own tooling. The TABLE_STORAGE forecast query declares $0.02, $0.01, $0.04 and $0.02 and says "The prices used in the pricing variables for this query are for the us-central1 region." On the pricing page those are the US multi-region rates. Run it unchanged in Iowa and it understates logical cost by 13% on active bytes and 37.5% on long-term bytes, so it understates what physical billing would save. Set the four variables from your own location's row first.
Long-term storage starts after 90 days without a write
Google bills "any table or table partition that has not been modified for 90 consecutive days" at the long-term rate, with "no difference in performance, durability, or availability." The clock runs per partition. It resets on any load, copy or query job that writes to the table, on DML, on CREATE OR REPLACE TABLE and on streaming; querying, exporting, copying the table elsewhere and creating a view do not reset it.
That makes table shape the first storage lever. A date-partitioned, append-only table moves its old partitions to long-term on its own, which our partitioning runbook prices in detail. A table rewritten every night never gets there, under either billing model.
Physical vs logical billing: a table rebuilt daily needs 15 times the compression
Under physical billing, Google charges time travel and fail-safe storage "at the active physical storage rate"; under logical billing both are "included in the base rate." Time travel keeps changed or deleted data for 2 to 7 days, 7 by default. Fail-safe keeps it "for an additional seven days after the time travel window," and "isn't configurable."
The cost of that depends on how often a table's bytes are replaced. Google's docs say that when a table's data "is fully replaced (for example, by using the WRITE_TRUNCATE write disposition in a load job or by using the CREATE OR REPLACE TABLE statement), the previous contents of the table are retained." A table replaced once a day therefore carries about one old copy per day of time travel plus seven of fail-safe, all at the active rate:
In this model, physical pays only if compression ratio > price ratio x (1 + r x (W + 7)), where r is the share of the table's bytes replaced per day and W is the time travel window in days.
For an append-only table, r is near 0 and the regional price ratio is the whole test. For a full daily rebuild, r is 1: with the default 7-day window the multiplier is 15, so the US break-even is 30:1; with a 2-day window it is 10, or 20:1. A table rebuilt every hour multiplies by 337.
Daily full rebuilds are common because dbt's table materialization rebuilds the whole table on every run. The dbt-bigquery adapter's create_table_as macro emits create or replace table for each run of a SQL table model. Every scheduled dbt run on a physical-billing dataset adds a full compressed copy of each such model to time travel.
Run this per-table query before you switch a dataset
The billing model is a dataset setting, but the break-even is a table property. This query prices each table both ways from INFORMATION_SCHEMA.TABLE_STORAGE and shows how many recovery bytes it carries per live byte. Set the prices to your location's row.
-- Monthly storage cost per table, logical vs physical, US multi-region prices
DECLARE active_logical FLOAT64 DEFAULT 0.02;
DECLARE long_term_logical FLOAT64 DEFAULT 0.01;
DECLARE active_physical FLOAT64 DEFAULT 0.04;
DECLARE long_term_physical FLOAT64 DEFAULT 0.02;
SELECT
table_schema AS dataset,
table_name,
deleted,
ROUND((active_logical_bytes * active_logical
+ long_term_logical_bytes * long_term_logical) / POW(1024, 3), 2) AS logical_cost,
ROUND(((active_physical_bytes + fail_safe_physical_bytes) * active_physical
+ long_term_physical_bytes * long_term_physical) / POW(1024, 3), 2) AS physical_cost,
ROUND(SAFE_DIVIDE(active_logical_bytes + long_term_logical_bytes,
active_physical_bytes - time_travel_physical_bytes + long_term_physical_bytes), 1) AS compression_ratio,
ROUND(SAFE_DIVIDE(time_travel_physical_bytes + fail_safe_physical_bytes,
active_physical_bytes - time_travel_physical_bytes + long_term_physical_bytes), 1) AS recovery_copies
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE table_type = 'BASE TABLE'
ORDER BY physical_cost - logical_cost DESC;
Read the top of the list first: those tables cost more under physical billing. Google's docs say active_physical_bytes already includes time travel bytes and fail_safe_physical_bytes is a separate column, which is why the query subtracts one and adds the other. Deleted tables still bill their recovery bytes under physical billing, so keep the deleted rows in view. The view is a snapshot, not an invoice: Google warns its values "are not expected to directly match the line items in your billing report."
Then group, then switch. Move high-churn tables to a logical-billing dataset, or shorten the window on their dataset with ALTER SCHEMA ... SET OPTIONS (max_time_travel_hours = 48), and switch the rest with SET OPTIONS (storage_billing_model = 'PHYSICAL'). Per Google's dataset guide, the change takes 24 hours, you must wait 14 days to change again, and long-term data is not reset to active by the switch.
A 20 TiB dataset priced both ways: split it before you switch
Consider a hypothetical analytics dataset in the US multi-region with 20 TiB of logical data. An append-only, date-partitioned events table holds 16 TiB, of which 12 TiB is in partitions older than 90 days. dbt table models rebuilt once a night hold the other 4 TiB. Every table compresses 8:1, inside DoiT's range. Appends are assumed to add no time travel bytes, since only changed or deleted data is retained.
| Billing | Events | Marts: live | Marts: time travel | Marts: fail-safe | Monthly total | vs all logical |
|---|---|---|---|---|---|---|
| All logical | $204.80 | $81.92 | included | included | $286.72 | |
| All physical, 7-day window | $51.20 | $20.48 | $143.36 | $143.36 | $358.40 | +25.0% |
| All physical, 2-day window | $51.20 | $20.48 | $40.96 | $143.36 | $256.00 | -10.7% |
| Split: events physical, marts logical | $51.20 | $81.92 | included | included | $133.12 | -53.6% |
The inputs: events cost 4,096 GiB x $0.02 + 12,288 GiB x $0.01 logical, or 512 GiB x $0.04 + 1,536 GiB x $0.02 physical. The marts are 512 GiB compressed; at the default window they carry 7 x 512 GiB in time travel and 7 x 512 GiB in fail-safe, all at $0.04.
Switching the whole dataset raises the bill by a quarter; splitting it first cuts it by more than half. The marts' 8:1 is far below their 30:1 break-even, while the events table clears its 2:1 four times over.
The same dataset in Iowa shows why a dataset-level forecast is not enough. Logical costs $385.02 there and physical $358.40, so the dataset forecast says switch, and it is right by 6.9%. The marts still cost $307.20 physical against $94.21 logical inside that winning dataset, and the split comes to $145.41.
The strongest objection is that the 15x multiplier is a worst case: many teams build marts incrementally, and Google's forecast query already counts time travel and fail-safe bytes. Both are true. An incremental model with a small r sits close to the plain price ratio, and the forecast is right about the dataset as it is today. What it cannot show is the table inside a winning dataset that loses money, or the cost of next month's new table model. The per-table query does.
Limits of this model: real compression varies by column and table, a partial MERGE rewrites an amount we did not measure, and the 10 GiB free tier is ignored. Run the query on your own tables before acting on any number here.
Tables nobody reads cost the most to rebuild
The cheapest storage line is a mart that is no longer rebuilt. Before choosing a billing model for a daily table model, check INFORMATION_SCHEMA.JOBS for queries that read it, as step 3 of our BigQuery cost optimization guide shows: stopping an unread rebuild removes its compute and its recovery copies together. Compute pricing, on-demand against editions, is in BigQuery pricing: on-demand vs editions, and the general case of cost nobody owns is in data FinOps.
Datapace is building one map of your data estate, with usage and cost attached, so tables like these show up with their storage cost and their readers side by side, and agents that propose the change as a pull request your team reviews and merges. To see what that looks like on your own BigQuery estate, book a call.




