# Snowflake Permanent Table With Large Fail-safe

> Flags permanent Snowflake tables averaging over 1 GiB of Fail-safe bytes in 14 days, where a transient rebuild would remove that storage.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-permanent-table-with-large-fail-safe

---

## Seven days of history you pay for but cannot query

Every permanent table gets [Fail-safe](https://docs.snowflake.com/en/user-guide/data-failsafe): a non-configurable 7-day
period that starts when the Time Travel retention period ends. Only Snowflake can recover data from
it, on a best-effort basis, and it is not a way for you to read old versions. The bytes held there
are still billed to the table as storage.

For a curated source-of-truth table that trade is usually worth it. For a staging or intermediate
table that a pipeline truncates and reloads, each reload pushes the replaced micro-partitions
through Time Travel and then into Fail-safe, so the table can carry several copies of data it could
rebuild from source in minutes. [Transient tables](https://docs.snowflake.com/en/user-guide/tables-temp-transient) have no
Fail-safe period and therefore no Fail-safe cost.

## Listing permanent tables by Fail-safe bytes

```sql
SELECT table_catalog, table_schema, table_name,
       ROUND(active_bytes    / POWER(1024, 3), 2) AS active_gib,
       ROUND(time_travel_bytes / POWER(1024, 3), 2) AS time_travel_gib,
       ROUND(failsafe_bytes  / POWER(1024, 3), 2) AS failsafe_gib
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE is_transient = 'NO'
  AND deleted = FALSE
  AND failsafe_bytes > 1073741824
ORDER BY failsafe_bytes DESC;
```

In [TABLE_STORAGE_METRICS](https://docs.snowflake.com/en/sql-reference/account-usage/table_storage_metrics) `IS_TRANSIENT`
reads 'YES' for transient and temporary tables, which have no Fail-safe. A Fail-safe figure much
larger than active bytes is the signature of a high-churn table.

## The two conditions

ZopNight skips any table it knows to be transient. For the rest, it averages the table's Fail-safe
bytes over the last 14 days and fires when that average is above 1 GiB (1,073,741,824 bytes). There
is no condition on active size, edition or how often the table is queried.

## When the check stays quiet

A table with no Fail-safe series, or no datapoints in the 14 days, is not flagged. A table whose
Fail-safe footprint sits at or under 1 GiB is left alone because a rebuild would not be worth the
risk. The rule cannot tell whether your data is regenerable; that judgement stays with you.

## No computed saving, and what you would recover

The finding is advisory and shows no dollar figure. Converting to transient removes the Fail-safe
bytes for new changes, but Snowflake bills storage per TB at a rate that depends on edition,
region and contract, and the old table's history still has to age out. The value is roughly the
table's Fail-safe bytes multiplied by your storage rate.

## Rebuilding the table as transient

A permanent table cannot be switched to transient in place. Build a copy and swap names:

1. Create the transient copy:
   `CREATE TRANSIENT TABLE stg.events_t AS SELECT * FROM stg.events;`
2. Check grants, clustering keys and dependent objects on the new table before going further.
3. Swap the two in one transaction: `ALTER TABLE stg.events SWAP WITH stg.events_t;`
4. Drop the old permanent copy once the pipeline has run cleanly against the new one.

**Warning**
A transient table can hold at most 1 day of Time Travel and has no Fail-safe, so Snowflake
cannot recover it after that window. Use this only for data you can rebuild from source. The
dropped permanent copy still spends 7 days in Fail-safe before its storage stops.