# Snowflake Stale Stage

> Covers Snowflake stages with no COPY load in over 30 days; no findings are raised until load history is matched to each stage.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-stale-stage

---

## Staged files are billed like any other storage

Snowflake's [storage cost model](https://docs.snowflake.com/en/user-guide/cost-understanding-data-storage) includes files
staged for bulk loading and unloading, billed on their size alongside tables, Time Travel and
Fail-safe. By default, COPY leaves a file in the stage after loading it: the `PURGE` copy option
defaults to FALSE. A pipeline that has been switched off, repointed or replaced therefore leaves
its last batch of files behind, and nobody notices because stages do not appear in table-level
storage reports. Only internal stages carry Snowflake storage cost: files in an external stage sit
in your own cloud bucket and are billed there. ZopNight applies this check to both kinds.

## Finding stages with no recent loads

Load activity per stage is in [COPY_HISTORY](https://docs.snowflake.com/en/sql-reference/account-usage/copy_history), which
covers both COPY statements and Snowpipe for the last 365 days:

```sql
SELECT stage_location,
       MAX(last_load_time)                               AS last_load,
       DATEDIFF('day', MAX(last_load_time), CURRENT_TIMESTAMP()) AS days_idle
FROM SNOWFLAKE.ACCOUNT_USAGE.COPY_HISTORY
GROUP BY stage_location
HAVING MAX(last_load_time) < DATEADD('day', -30, CURRENT_TIMESTAMP())
ORDER BY days_idle DESC;
```

Cross-check against [ACCOUNT_USAGE.STAGES](https://docs.snowflake.com/en/sql-reference/account-usage/stages), where
`STAGE_TYPE` tells internal from external stages and `DELETED` is set for dropped ones. Then look
inside a candidate with `LIST @<stage>;` to see what is still there. COPY_HISTORY can lag by up to
2 hours in most cases.

## More than 30 days since the last COPY

ZopNight tracks, for each stage, how many days have passed since a COPY last loaded from it, and
takes the highest value seen in the trailing 30-day window. The finding fires when that figure is
above 30 days. There is no size condition, so a stage holding a handful of files qualifies as
readily as one holding terabytes.

Today this check raises no findings. ZopNight reads load history keyed by each stage's storage
location, which does not match the stage it discovered, so the days-since-last-COPY figure never
reaches the rule and it stays silent until that link is fixed.

## Stages it cannot judge

If ZopNight has no days-since-last-COPY series for the stage, it raises nothing: a stage is never
called stale on missing evidence. Stages used only as a target for unloading data are also
not measured by load activity, which is why they are a common false positive.

## No priced saving on this finding

The finding shows no dollar figure because ZopNight does not size the files inside the stage.
The storage recovered is whatever `LIST` reports, priced at your account's per-TB storage rate.

## Cleaning up a stale stage

1. `LIST @<stage>;` and confirm with the owning team that nothing will load these files again.
2. Remove them: `REMOVE @<stage> PATTERN = '.*\.csv\.gz';` or `REMOVE @<stage>;` for everything.
3. For stages that stay in use, add `PURGE = TRUE` to the COPY statement so files are removed after
   a successful load. Snowpipe does not accept `PURGE`, so for a pipe's stage schedule a `REMOVE` of
   files that have already loaded.

**Warning**
On an external stage, REMOVE deletes the files from your own cloud storage location, not
just from Snowflake's view of it. Check that no other system reads that path first.