# Snowflake Large Cold Table

> Flags Snowflake tables above 100 GiB of active bytes with zero reads over 90 days, as an unpriced archive-or-drop advisory.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-large-cold-table

---

## Storage keeps billing long after the last query

Snowflake charges storage as a [flat rate per terabyte](https://docs.snowflake.com/en/user-guide/cost-understanding-data-storage),
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](https://docs.snowflake.com/en/sql-reference/account-usage/table_storage_metrics), the read half from
[ACCESS_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/access_history), which needs Enterprise Edition or
higher and keeps 365 days of history:

```sql
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](https://zop.dev/integrations/snowflake/recommendations/snowflake-table-over-long-time-travel-retention)
and [Snowflake Permanent Table With Large Fail-safe](https://zop.dev/integrations/snowflake/recommendations/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](https://docs.snowflake.com/en/sql-reference/sql/copy-into-location) to a stage, or attach a
   [storage lifecycle policy](https://docs.snowflake.com/en/user-guide/storage-management/storage-lifecycle-policies), which
   archives matching rows to COOL or COLD storage or expires them.
3. If nobody needs it, drop it: `DROP TABLE <name>;`

**Warning**
A dropped permanent table is not free straight away. Its bytes stay in Time Travel for
the retention period and then enter a 7-day Fail-safe period, and both are billed as storage.
Unload or archive before dropping if there is any doubt.