Snowflake Data Retention & Recovery: Time Travel, Fail-safe & Retention Design

Snowflake data retention and recovery rely on two mechanisms: Time Travel and Fail-safe. Time Travel lets users access past data directly, while Fail-safe is the final line of defense Snowflake holds internally after Time Travel expires.

This article walks through the timeline of both mechanisms, retention by edition, differences across table types, and the impact on storage cost. It is a frequently tested topic on the SnowPro Core exam under Domain 6 (Data Protection & Data Sharing / 10-15% of the exam).

Data Retention Timeline

Starting from the moment data is changed (UPDATE/DELETE/DROP), retention follows the timeline below.

データ変更発生
  │
  ├── Time Travel期間（0〜90日）──────┐
  │   ユーザーがAT/BEFORE句で        │
  │   過去データに直接アクセス可能     │
  │   UNDROP TABLE/SCHEMA/DATABASE   │
  │   で復元可能                     │
  │                                  │
  ├── Fail-safe期間（7日間）─────────┐
  │   Snowflakeサポートによる        │
  │   データ復旧のみ可能             │
  │   ユーザーは直接アクセス不可      │
  │                                  │
  └── データ完全消去                  │
      復旧不可能

On Enterprise Edition (90-day Time Travel), data is protected for up to 97 days (90 + 7) after a change. On Standard Edition (Time Travel capped at 1 day), the maximum window is 8 days (1 + 7).

Time Travel in Detail

Access Syntax

Recovery via UNDROP

Within the Time Travel window, tables, schemas, and databases dropped with DROP TABLE can be restored with UNDROP.

-- テーブルの復元
UNDROP TABLE sales;

-- スキーマの復元
UNDROP SCHEMA analytics;

-- データベースの復元
UNDROP DATABASE production;

If an object with the same name already exists, UNDROP fails. You need to rename the existing object first with ALTER TABLE ... RENAME.

Retention by Edition

On Standard Edition, DATA_RETENTION_TIME_IN_DAYS is capped at 1 day. You need to upgrade to Enterprise or above to use the 90-day Time Travel window.

Data Retention by Table Type

Snowflake has three table types (Permanent / Transient / Temporary), each with different retention behavior. The differences between these three are a frequent exam topic.

How to Choose a Table Type

• Permanent: business-critical data. Use for master tables and fact tables that must be recoverable after mistakes. Protected by both Time Travel and Fail-safe.
• Transient: data that can be re-created. Use for ETL staging tables, intermediate aggregations, and other data you can regenerate from the source. Storage cost is lower because Fail-safe is not applied.
• Temporary: session-scoped data. The table is automatically dropped when the session ends. Not visible to other users. Ideal for analytical scratch tables.

Impact on Storage Cost

Time Travel and Fail-safe retention directly affect storage cost. Retained data is stored as pre-change snapshots (delta data), so tables that change frequently accumulate more storage cost.

Setting 90-day Time Travel on a table with frequent UPDATEs and DELETEs can drive storage cost up sharply. Tune DATA_RETENTION_TIME_IN_DAYS based on each table's importance and update frequency.

Retention Design Guidelines

Hierarchical Configuration Strategy

DATA_RETENTION_TIME_IN_DAYS can be set at the account, database, schema, and table levels, with lower-level settings overriding higher-level ones.

-- アカウントレベル（デフォルト30日）
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 30;

-- ステージングスキーマ（0日 = Time Travel無効）
ALTER SCHEMA staging SET DATA_RETENTION_TIME_IN_DAYS = 0;

-- 重要テーブル（90日 = 最大保持）
ALTER TABLE master_customers SET DATA_RETENTION_TIME_IN_DAYS = 90;

Recommended Configuration Patterns

Operational Notes on Fail-safe

• Fail-safe recovery requires opening a ticket with Snowflake Support and takes hours to days. It is not an instant recovery path.
• Fail-safe applies only to Permanent tables — not to Transient or Temporary tables.
• Storage during the Fail-safe period is billed to you. To cut cost, consider using Transient tables.
• The Fail-safe period (7 days) is fixed and cannot be changed by users.
• Fail-safe is a last-resort insurance policy — normal operations should resolve recoveries through Time Travel and UNDROP.

Check Your Understanding

Frequently Asked Questions