What Is Fail-safe? Snowflake Continuous Data Protection Complete Guide

Snowflake provides a comprehensive data protection framework called Continuous Data Protection (CDP), which defends your data with a layered model of Time Travel → Fail-safe → encryption. This article walks through the full CDP picture, how Fail-safe works, the clear distinctions between Time Travel and Fail-safe, and how protection periods differ by table type — all with comparison tables.

The Big Picture of Continuous Data Protection (CDP)

CDP is Snowflake's data protection framework, made up of the following three layers.

CDPのタイムライン:

 ← データ変更時点 ──────────────────────────────→ 時間経過

 [即時]          [Time Travel期間]      [Fail-safe期間]
   │                │                      │
   │   ユーザー自身で復旧可能    Snowflakeサポート
   │   - UNDROP                  のみ復旧可能
   │   - SELECT ... AT/BEFORE    （ベストエフォート）
   │   - CREATE ... CLONE ... AT/BEFORE
   │                │                      │
   │    1〜90日（設定次第）        7日間（固定）
   ├──────────────────┤──────────────────────┤
                                             │
                                     保護期間終了 → データ完全削除

Time Travel vs. Fail-safe Comparison

Protection Periods by Table Type

Time Travel and Fail-safe periods differ by table type. Choosing the right table type is essential for balancing cost optimization with data protection.

Characteristics of Each Table Type

-- Permanentテーブル（デフォルト）
CREATE TABLE production_data (
  id NUMBER,
  data VARIANT
);
-- Time Travel: 最大90日（Enterprise+）、Fail-safe: 7日

-- Transientテーブル
CREATE TRANSIENT TABLE staging_data (
  id NUMBER,
  raw_json VARIANT
);
-- Time Travel: 最大1日、Fail-safe: なし

-- Temporaryテーブル
CREATE TEMPORARY TABLE session_temp (
  key STRING,
  value STRING
);
-- Time Travel: 最大1日、Fail-safe: なし
-- セッション終了時に自動削除

-- Time Travel保持期間の設定
ALTER TABLE production_data
  SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- テーブルの保持期間を確認
SHOW TABLES LIKE 'production_data';
-- → retention_time カラムで確認

Impact on Storage Cost

Both Time Travel and Fail-safe retain the pre-change data (older micro-partitions), so they incur additional storage charges.

ストレージ消費の内訳:

[アクティブストレージ]     ← 現在のテーブルデータ
         +
[Time Travelストレージ]    ← 変更前のマイクロパーティション（設定期間分）
         +
[Fail-safeストレージ]      ← Time Travel期間後のデータ（7日分）
         =
[合計ストレージ費用]

※ 頻繁にUPDATE/DELETEされるテーブルほどTime Travel/Fail-safeのストレージが大きくなる
※ INSERT ONLYのテーブルでは追加ストレージはほとんど発生しない

-- ストレージ使用状況の確認
SELECT
  TABLE_NAME,
  ACTIVE_BYTES / (1024*1024*1024) AS active_gb,
  TIME_TRAVEL_BYTES / (1024*1024*1024) AS time_travel_gb,
  FAILSAFE_BYTES / (1024*1024*1024) AS failsafe_gb,
  (ACTIVE_BYTES + TIME_TRAVEL_BYTES + FAILSAFE_BYTES) / (1024*1024*1024) AS total_gb
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_CATALOG = 'MY_DB'
ORDER BY total_gb DESC
LIMIT 20;

Time Travel Operations

-- 過去の時点のデータを参照（AT句）
SELECT * FROM sales
  AT(TIMESTAMP => '2026-03-20 10:00:00'::TIMESTAMP);

-- 指定時間前のデータを参照（OFFSET）
SELECT * FROM sales
  AT(OFFSET => -3600);  -- 1時間前

-- 特定のステートメント実行前のデータを参照（BEFORE句）
SELECT * FROM sales
  BEFORE(STATEMENT => '01b2c3d4-0001-a234-0000-00000000beef');

-- ドロップしたテーブルの復元
UNDROP TABLE deleted_sales;

-- 過去時点のクローンを作成
CREATE TABLE sales_backup CLONE sales
  AT(TIMESTAMP => '2026-03-20 10:00:00'::TIMESTAMP);

How UNDROP Works

UNDROP only works within the Time Travel window. If an object with the same name already exists, UNDROP fails.

• UNDROP TABLE: restores a dropped table
• UNDROP SCHEMA: restores a dropped schema (and all of its tables)
• UNDROP DATABASE: restores a dropped database
• If an object with the same name exists, you must RENAME it first

Data Encryption

As the third pillar of CDP, Snowflake automatically encrypts all data using AES-256.

Key Points for the Exam

• Fail-safe is fixed at 7 days and cannot be changed; recovery is best-effort via Snowflake Support
• Time Travel allows self-service recovery by users (UNDROP / AT / BEFORE / CLONE)
• Transient and Temporary tables do not have Fail-safe applied (0 days)
• On Enterprise Edition or higher, Permanent tables can be configured with up to 90 days of Time Travel
• On Standard Edition, Time Travel maxes out at 1 day
• If a table with the same name already exists, UNDROP fails
• The Fail-safe period begins after the Time Travel period ends

Sample Question

Frequently Asked Questions