Estimate what a query costs before you run it
This recipe tells you what already happened. The analyzer takes a query you have not run yet and estimates what it will scan, in your browser.
How much am I paying for BigQuery time travel and fail-safe?
The invisible storage overhead behind frequently-rewritten tables.
Reads metadata only, a fraction of a cent to run (10 MB minimum)Reviewed against Google’s docs, 23 Oct 2026
Permissions. Needs bigquery.tables.get and bigquery.tables.list for TABLE_STORAGE (BigQuery Metadata Viewer has them); bigquery.jobs.create to run any query (BigQuery Job User has it).
Pick the region your data is in: every INFORMATION_SCHEMA view here is qualified by region.
-- active_physical_bytes already includes the time-travel bytes, so the
-- live data is active minus time travel, plus long-term.
SELECT
table_schema,
table_name,
ROUND((active_physical_bytes - time_travel_physical_bytes
+ long_term_physical_bytes) / POW(1024, 3), 2) AS live_physical_gib,
ROUND(time_travel_physical_bytes / POW(1024, 3), 2) AS time_travel_gib,
ROUND(fail_safe_physical_bytes / POW(1024, 3), 2) AS fail_safe_gib,
ROUND(SAFE_DIVIDE(
time_travel_physical_bytes + fail_safe_physical_bytes,
NULLIF(active_physical_bytes - time_travel_physical_bytes
+ long_term_physical_bytes, 0)), 2) AS overhead_ratio
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE NOT deleted
AND time_travel_physical_bytes > 0
ORDER BY time_travel_physical_bytes DESC
LIMIT 100;What it returns
- live_physical_gib
- The table as it stands now, compressed: active and long-term bytes without the time-travel copies.
- time_travel_gib
- Previous versions retained for the time-travel window (default 7 days).
- fail_safe_gib
- A further 7 days Google keeps for recovery. Under physical storage billing you pay for it and cannot turn it off; under logical billing it is included in the rate.
- overhead_ratio
- Overhead ÷ live data. Above 1.0 means you store more history than data.
How to read it
- A high ratio is the signature of WRITE_TRUNCATE on a large table: every full overwrite keeps the old copy for the window.
- The time-travel window is configurable per dataset between 2 and 7 days. Dropping it to 2 on a heavily rewritten staging dataset is a real, immediate saving when the dataset uses physical storage billing; under logical billing time travel is not charged separately.
- Fail-safe is not configurable. If the overhead is still unacceptable after tuning time travel, the fix is to stop rewriting the table.
Run this once, or have it run every day.
Finitizer evaluates this class of question continuously against your live BigQuery estate, tracks how each number moves between runs, and turns findings into assigned tasks. Read-only and keyless.
See how Finitizer works