Skip to content
Finitizer
To investigate

BigQuery time travel window: the cost on physical billing

On physical storage billing, changed and deleted data bills at the active rate for as long as the time-travel window keeps it.

The window is 7 days by default and can be as short as 2.

Run this check on your own account

Run read-only queries in your own console and paste the results. The report is worked out in your browser, with no access to your account.

What the check looks at

Time travel bytes and the max_time_travel_hours setting of each physically billed dataset.

Why it costs money

  • Time travel lets you query or restore a table as it was, for up to 7 days.
  • Under physical billing the bytes kept for it are charged separately; under logical billing they are not.
  • Fail-safe adds 7 more days after the window, and cannot be shortened.

How the tool measures it

To investigate. The report shows what this costs now and where to look. It claims no saving, because the results cannot prove one.

The report shows what time travel bytes bill a month, and the most a 48-hour window could cut if data changed evenly across the window. It is not added to the modeled savings.

When the report raises it. Query 3 shows physically billed datasets with a window over 48 hours whose time travel bills at least $10 a month.

In the sample report

Check the time-travel window on physically billed datasets

  • Time-travel bytes on this dataset bill about $37 a month.

$37 a month now. No saving is claimed.

From the tool's own on-demand sample: made-up results for one project over 30 days, at US multi-region list prices. The SQL above is for the US multi-region at project scope; the tool writes it for any location.

What to do

  1. 1Ask who relies on recovery past two days.
  2. 2Where nobody does, ALTER SCHEMA dataset SET OPTIONS(max_time_travel_hours = 48).

How to tell it worked. Read time_travel_physical_bytes in INFORMATION_SCHEMA.TABLE_STORAGE a week after the change.

Trade-offs. You can no longer query or restore versions older than the new window.

The read-only query behind it

The same SQL the tool gives you. Each reads usage views and changes nothing.

Query 3: storage by dataset
-- Finitizer BigQuery Cost Check, query 3 of 3: storage by dataset.
-- Returns dataset names (the advice is per dataset) and byte totals. A snapshot
-- of now, so it has no date window. Follows Google's storage forecast: deleted
-- tables are left out of logical bytes and kept in physical bytes, because their
-- retained bytes still bill. Clones and snapshots report as complete tables, so
-- they are excluded and counted. Run it unchanged.
WITH options AS (
  SELECT
    schema_name,
    MAX(IF(option_name = 'storage_billing_model', option_value, NULL)) AS billing_model,
    MAX(IF(option_name = 'max_time_travel_hours', option_value, NULL)) AS time_travel_hours
  FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  WHERE option_name IN ('storage_billing_model', 'max_time_travel_hours')
  GROUP BY schema_name
),
storage AS (
  SELECT
    *,
    IF(STARTS_WITH(table_schema, '_'), '(hidden temporary datasets)', table_schema) AS dataset,
    IFNULL(deleted, FALSE)                                     AS is_deleted,
    IFNULL(table_type, 'BASE TABLE') IN ('BASE TABLE', 'MATERIALIZED VIEW') AS counted,
    IFNULL(total_physical_bytes, 0) + IFNULL(fail_safe_physical_bytes, 0) AS retained_bytes
  FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
)
SELECT
  s.dataset,
  COUNTIF(counted AND NOT is_deleted)                          AS tables,
  COUNTIF(NOT counted)                                         AS clones_and_snapshots,
  COUNTIF(counted AND is_deleted AND retained_bytes > 0)       AS deleted_tables,
  IFNULL(SUM(IF(counted AND NOT is_deleted, s.active_logical_bytes, 0)), 0)
                                                               AS active_logical_bytes,
  IFNULL(SUM(IF(counted AND NOT is_deleted, s.long_term_logical_bytes, 0)), 0)
                                                               AS long_term_logical_bytes,
  IFNULL(SUM(IF(counted, s.active_physical_bytes, 0)), 0)    AS active_physical_bytes,
  IFNULL(SUM(IF(counted, s.long_term_physical_bytes, 0)), 0) AS long_term_physical_bytes,
  IFNULL(SUM(IF(counted, s.time_travel_physical_bytes, 0)), 0)
                                                               AS time_travel_physical_bytes,
  IFNULL(SUM(IF(counted, s.fail_safe_physical_bytes, 0)), 0) AS fail_safe_physical_bytes,
  ANY_VALUE(o.billing_model)                                   AS billing_model,
  ANY_VALUE(o.time_travel_hours)                               AS time_travel_hours,
  COUNT(*) OVER ()                                             AS result_rows,
  CURRENT_TIMESTAMP()                                          AS snapshot_at,
  'us'                                                AS location,
  'project'                                             AS scope,
  '2026-11-07'                                         AS query_version
FROM storage s
LEFT JOIN options o ON o.schema_name = s.table_schema
GROUP BY s.dataset
ORDER BY SUM(IF(counted, s.total_physical_bytes, 0)) DESC;

Other checks in the report

All checks

What BigQuery charges in each location: BigQuery pricing in 2026.

Run this check every day, not once.

Finitizer reads your BigQuery job history and storage directly, raises these findings as they appear, and tracks each fix on the bill.

See how Finitizer works