Snowflake tables keeping more than 7 days of Time Travel and over 1 GiB of history

Every retained day is a day of storage

Time Travel keeps the previous versions of changed or deleted data for the table’s retention period so it can be queried, cloned or undropped. The standard period is 1 day. On Enterprise Edition and above, a permanent table can be set anywhere from 0 to 90 days, and Snowflake states plainly that extended retention needs extra storage that shows up on the monthly bill. When the period ends, the data moves into Fail-safe for a further 7 days.

How much a long window costs depends on churn, not table size. An append-only fact table barely grows its Time Travel footprint. A table that is rewritten or merged daily keeps a copy of every replaced micro-partition for the whole window.

Listing tables by retention and Time Travel bytes

SELECT t.table_catalog || '.' || t.table_schema || '.' || t.table_name AS fqn,
       t.retention_time,
       ROUND(m.time_travel_bytes / POWER(1024, 3), 2) AS time_travel_gib,
       ROUND(m.active_bytes / POWER(1024, 3), 2)      AS active_gib
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLES t
JOIN SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS m
  ON m.id = t.table_id
WHERE t.deleted IS NULL
  AND t.retention_time > 7
  AND m.time_travel_bytes > 1073741824
ORDER BY m.time_travel_bytes DESC;

RETENTION_TIME in ACCOUNT_USAGE.TABLES is the number of days kept for Time Travel; TIME_TRAVEL_BYTES in TABLE_STORAGE_METRICS is what that history occupies. The two views need the OBJECT_VIEWER and USAGE_VIEWER roles.

Retention above 7 days with more than 1 GiB retained

ZopNight needs the table’s retention setting and fires only when it is above 7 days. It then averages the table’s Time Travel bytes over the last 14 days and requires more than 1 GiB (1,073,741,824 bytes). Both must hold at once, so a 90-day window on a quiet table is left alone because there is little to reclaim.

Tables outside the check

If the retention setting was not captured or is not a number, or the Time Travel series is missing or empty, the rule does not fire. Retention of 7 days or less is treated as reasonable no matter how much history it holds.

Unpriced, with the storage as the prize

This is a governance advisory without a dollar figure. When retention is shortened, history already older than the new window moves into Fail-safe, and on a permanent table it is billed there for 7 more days, so the saving arrives over about a week rather than on the day of the change. What you recover over time is most of the Time Travel bytes shown by the query, at your per-TB storage rate.

Shortening the retention window

1. Agree the recovery window with whoever owns the data. For a pipeline table that can be rebuilt, 1 day is common.
2. Apply it with ALTER TABLE, as below.
3. Check whether an account administrator has set a minimum retention for the account. When one is set, the effective retention is the larger of the two values, so a table-level change can have no effect.

ALTER TABLE stg.orders_merge SET DATA_RETENTION_TIME_IN_DAYS = 1;
SHOW PARAMETERS LIKE 'MIN_DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;

For rebuildable tables that also carry heavy Fail-safe bytes, see Snowflake Permanent Table With Large Fail-safe.