Snowflake stages that have not served a COPY load in over 30 days

Staged files are billed like any other storage

Snowflake’s storage cost model 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, which covers both COPY statements and Snowpipe for the last 365 days:

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, 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.