# snowflake_table_cold_90d

> A Snowflake table with zero reads across 90 days of ACCESS_HISTORY and QUERY_HISTORY, sitting on more than 100 GB of active storage, is paying for data nobody queries. Archiving to an external stage and dropping the table reclaims that storage entirely.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-table-cold-90d
Updated: 2026-08-19

---

## Two conditions, both hard

`snowflake.table.access_count` summed over 90 days must equal **exactly zero**, and
`snowflake.table.active_bytes` must exceed 100 GB.

The zero is a hard equality rather than a threshold, and that is deliberate: the metric defaults
to 0 when it is missing. A table whose access data has not been ingested looks identical to a
table nobody touches, so the rule treats "no evidence of reads" and "zero reads" the same way,
which makes the 90-day window and the size floor the real safeguards.

## Why 100 GB

Below that, the storage saving does not justify the operational cost of archiving and the risk of
needing the table back. Snowflake storage is inexpensive per terabyte; the finding only becomes
material at scale.

## Archive rather than delete

The remediation is to unload the table to an external stage (S3, GCS or Azure Blob) and then
drop it. The data stays available at object-storage rates, which are a fraction of Snowflake
active storage, and can be reloaded if it turns out somebody did need it.

That path matters because a dropped Snowflake table is recoverable only within Time Travel, and
after that only from Fail-safe via support. Unloading first turns an irreversible action into a
reversible one.

## Joining TABLE_STORAGE_METRICS to ACCESS_HISTORY

```sql
SELECT t.table_catalog, t.table_schema, t.table_name,
       t.active_bytes/POW(1024,3) AS active_gb,
       MAX(a.query_start_time)    AS last_access
FROM snowflake.account_usage.table_storage_metrics t
LEFT JOIN snowflake.account_usage.access_history a
  ON a.objects_accessed[0]:objectName::string =
     t.table_catalog||'.'||t.table_schema||'.'||t.table_name
WHERE t.active_bytes > 100*POW(1024,3)
GROUP BY 1,2,3,4
HAVING last_access IS NULL OR last_access < DATEADD(day,-90,CURRENT_TIMESTAMP());
```

ACCESS_HISTORY has a latency of up to a few hours and its own retention limit, so cross-check
anything the query returns before dropping it.