# snowflake_table_retention_excessive

> DATA_RETENTION_TIME_IN_DAYS above 7, combined with a Time Travel footprint averaging more than 1 GiB over 14 days, marks a table where retention has become pure storage rent. ZopNight reads the footprint from TABLE_STORAGE_METRICS; lowering retention on staging and ELT-intermediate tables cuts that cost dollar-for-dollar.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-table-retention-excessive
Updated: 2026-08-19

---

## Time Travel is storage you rent per retained day

Every UPDATE, DELETE, and overwrite in Snowflake keeps the old micro-partitions around
for the table's retention window, and those bytes bill at the normal storage rate. On a
source-of-truth table that is cheap insurance. On a staging table that a pipeline
truncates and reloads nightly, a 30-day retention window means storing roughly 30 full
copies of data you would never restore. Churn is what inflates Time Travel, and ELT
intermediates churn the most.

## Both gates, in numbers

The rule fires only when two things are true at once: the table's `retention_time`
attribute exceeds 7 days, and its `snowflake.table.time_travel_bytes` metric averages
above 1,073,741,824 bytes (1 GiB) over a 14-day window. A long retention on a table
with trivial Time Travel is left alone; there is nothing worth reclaiming. A table whose
retention attribute the discoverer never captured parses as non-numeric and never
matches, so absent data cannot produce a finding.

## Find the heavy tables yourself

```sql
SELECT t.table_catalog || '.' || t.table_schema || '.' || t.table_name AS table_uid,
       t.retention_time,
       m.time_travel_bytes / POWER(1024, 3) AS time_travel_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;
```

## Tables the rule leaves alone, and one honest cap

Permanent source-of-truth tables are the intended beneficiaries of long retention; the
lever targets regenerable staging and intermediate tables where recovery means re-running
a pipeline, not restoring history. One scale caveat: the metrics pull caps per-table
storage rows at 200,000, keeping the largest tables by active bytes and logging loudly
when truncation happens. On an account with millions of tables, small tables past the
cap are not evaluated. TABLE_STORAGE_METRICS is also a lagging ACCOUNT_USAGE view, so a
retention change shows up in the metric hours later, not immediately.

## Dialing it down

```sql
ALTER TABLE my_db.staging.events_raw SET DATA_RETENTION_TIME_IN_DAYS = 1;
```

Storage falls as the old partitions age out of the shortened window. Agree the number
with whoever owns recovery expectations first. Shortening retention is immediate and
irreversible for history already outside the new window. For truly regenerable
intermediates, the stronger companion move is making the table TRANSIENT, which also
drops the 7-day Fail-safe copy that follows every permanent table's Time Travel window;
that change requires a rebuild, so start with the retention parameter and schedule the
rebuild with the pipeline's owners.