Physical vs logical storage: a dataset classification rule for SMEs

Choose BigQuery storage billing per dataset from measured storage behavior. Don’t turn it into a warehouse-wide belief system.

Logical billing charges for uncompressed logical bytes and includes time-travel and fail-safe storage in that model. Physical billing charges for compressed physical bytes and separately accounts for retained physical bytes created by changes, deletion, time travel, and fail-safe. The cheaper option therefore depends on compression and churn, not merely table size.

Classify datasets by how they retain and rewrite data

A useful first classification is:

• append-heavy raw history
• slowly changing curated data
• mutation- or replacement-heavy staging and serving data
• short-lived scratch or intermediate data

Append-heavy datasets often compress well and create little retained history from mutation, which can make physical billing attractive. A dataset repeatedly replaced with CREATE OR REPLACE, WRITE_TRUNCATE, partition deletion, or heavy DML can accumulate time-travel and fail-safe physical bytes. In that case the apparent compression advantage may be consumed by retained versions.

Treat these as hypotheses, not a static mapping. Query INFORMATION_SCHEMA.TABLE_STORAGE and model both bills from the dataset’s actual active logical bytes, active physical bytes, long-term bytes, time-travel bytes, and fail-safe bytes.

Retention policy is part of the decision

The configurable time-travel window changes how long replaced or deleted data remains available before the fixed fail-safe period. A shorter window can reduce physical-billing cost for high-churn datasets, but it also reduces self-service recovery history.

Set it from a recovery requirement. Raw ingestion with replayable source data may need less BigQuery time travel than a curated dataset whose accidental overwrite would be difficult to reconstruct. Staging tables may need almost none beyond operational recovery. A seven-day default everywhere is still a decision, merely one nobody bothered to make.

Under physical billing, churn and retention interact directly with cost. Under logical billing, time-travel and fail-safe bytes aren’t billed separately, so the same rewrite pattern has different economics.

Keep performance out of the argument

The billing model doesn’t make queries faster, improve partition pruning, or isolate compute. It changes how stored bytes are charged.

Handle query performance through model shape, partitioning, clustering, serving tables, and compute design. Handle storage billing through compression, retained history, and mutation behavior. Mixing the two produces confident explanations for unrelated effects.

Forecast before switching

Use at least several weeks of representative storage data when possible. Include:

• active and long-term logical bytes
• active and long-term physical bytes
• time-travel and fail-safe physical bytes
• table and partition replacement frequency
• expected growth and retention changes
• regional prices and the dataset-level change delay

Model the steady state, not only today’s snapshot. A recent backfill or migration may make one billing model look temporarily bad. A new overwrite-heavy pipeline may make historical data falsely reassuring.

Storage billing is configured at the dataset level and changes aren’t a rapid tuning loop. Group tables with similar behavior or separate datasets when their storage economics and retention requirements diverge materially. Don’t split datasets solely to chase trivial savings. Access, lifecycle, and ownership boundaries still matter.

Wait when storage isn’t the problem

For many SME warehouses, compute and engineering time dominate storage savings. Leave the default alone when datasets are small, behavior is changing, or the forecast difference is immaterial.

Revisit the choice when storage becomes a meaningful cost, dataset classes stabilize, or a high-churn workload changes retained-byte behavior.

Use a measured rule: forecast logical and physical cost for each stable dataset class, include recovery retention, choose the cheaper acceptable model, and review it when the workload changes.