Snowflake tables over 100 GB with no reads in 90 days

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

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.