# Snowflake Table Over-Long Time Travel Retention

> Flags Snowflake tables with Time Travel retention above 7 days and more than 1 GiB of Time Travel bytes over 14 days.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-table-over-long-time-travel-retention

---

## Every retained day is a day of storage

[Time Travel](https://docs.snowflake.com/en/user-guide/data-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

```sql
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](https://docs.snowflake.com/en/sql-reference/account-usage/tables) is the number of
days kept for Time Travel; `TIME_TRAVEL_BYTES` in
[TABLE_STORAGE_METRICS](https://docs.snowflake.com/en/sql-reference/account-usage/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.

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

**Warning**
Once history falls outside the shorter window, it can no longer be queried, cloned or
undropped through Time Travel. Make the change only after the owner has agreed.

For rebuildable tables that also carry heavy Fail-safe bytes, see
[Snowflake Permanent Table With Large Fail-safe](https://zop.dev/integrations/snowflake/recommendations/snowflake-permanent-table-with-large-fail-safe).