Snowflake tables over 100 GiB that nobody read for 90 days

Storage keeps billing long after the last query

Snowflake charges storage as a flat rate per terabyte, calculated from the average bytes stored each day, and the rate depends on whether the account is On Demand or Capacity and on its region. Nothing about that charge depends on reads. A 100 GiB table that a retired dashboard used to feed costs the same every month as one queried a thousand times a day, and a permanent table also carries Time Travel and Fail-safe bytes on top of its active data.

Cold tables tend to pile up after migrations, one-off backfills and projects whose owner moved on. They rarely show up in a cost review because no single one looks large next to warehouse compute.

Pairing storage size with access history

The size half comes from TABLE_STORAGE_METRICS, the read half from ACCESS_HISTORY, which needs Enterprise Edition or higher and keeps 365 days of history:

WITH reads AS (
  SELECT DISTINCT f.value:"objectName"::string AS fqn
  FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY ah,
       LATERAL FLATTEN(input => ah.base_objects_accessed) f
  WHERE ah.query_start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
)
SELECT m.table_catalog || '.' || m.table_schema || '.' || m.table_name AS fqn,
       ROUND(m.active_bytes / POWER(1024, 3), 1) AS active_gib
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS m
LEFT JOIN reads r
  ON r.fqn = m.table_catalog || '.' || m.table_schema || '.' || m.table_name
WHERE m.deleted = FALSE
  AND m.active_bytes > 107374182400
  AND r.fqn IS NULL
ORDER BY m.active_bytes DESC;

TABLE_STORAGE_METRICS can lag by up to 90 minutes and ACCESS_HISTORY by up to 3 hours. Reading them takes the USAGE_VIEWER and GOVERNANCE_VIEWER SNOWFLAKE database roles.

Zero reads and more than 100 GiB, measured over 90 days

ZopNight reads two series for each table over a 90-day window: how many times the table was read, and how many active bytes it holds. The summed read count must be exactly zero, and the average active bytes must exceed 100 GiB (107,374,182,400 bytes). One read anywhere in the 90 days clears the table, and a large table that is read is never flagged here however rarely it is touched.

Tables it does not flag

If either series is missing or has no datapoints in the window, the rule stays silent rather than guessing. Small cold tables are out of scope on purpose: under 100 GiB the storage rarely justifies the effort of an archive review. Tables whose problem is history rather than size are covered by Snowflake Table Over-Long Time Travel Retention and Snowflake Permanent Table With Large Fail-safe.

Why no saving figure is shown

This finding carries no dollar amount. The storage bill is real, but the right action ranges from dropping the table (removing all of its storage) to moving rows to a cheaper archive tier (removing only part of it), so ZopNight does not guess a number. The medium severity reflects that a table this size, unread for a quarter, is usually both a cost leak and a data-ownership gap.

Archiving or retiring a cold table

1. Find the owner: the TABLE_OWNER column of ACCOUNT_USAGE.TABLES names the owning role. Check downstream views and tasks that reference the table before changing anything.
2. If the data must be kept, either unload it with COPY INTO location to a stage, or attach a storage lifecycle policy, which archives matching rows to COOL or COLD storage or expires them.
3. If nobody needs it, drop it: DROP TABLE <name>;