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 timeline:

 <- point of change ───────────────────────────────→ time

 [immediate]     [Time Travel window]   [Fail-safe window]
   │                │                      │
   │   you can recover it       only Snowflake support
   │   - UNDROP                 can recover it
   │   - SELECT ... AT/BEFORE   (best effort)
   │   - CREATE ... CLONE ... AT/BEFORE
   │                │                      │
   │    1-90 days (configurable)  7 days (fixed)
   ├──────────────────┤──────────────────────┤
                                             │
                                     protection ends -> data permanently deleted

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 table (the default)
CREATE TABLE production_data (
  id NUMBER,
  data VARIANT
);
-- Time Travel: up to 90 days (Enterprise+), Fail-safe: 7 days

-- Transient table
CREATE TRANSIENT TABLE staging_data (
  id NUMBER,
  raw_json VARIANT
);
-- Time Travel: up to 1 day, Fail-safe: none

-- Temporary table
CREATE TEMPORARY TABLE session_temp (
  key STRING,
  value STRING
);
-- Time Travel: up to 1 day, Fail-safe: none
-- Dropped automatically when the session ends

-- Set the Time Travel retention period
ALTER TABLE production_data
  SET DATA_RETENTION_TIME_IN_DAYS = 90;

-- Check a table's retention period
SHOW TABLES LIKE 'production_data';
-- -> shown in the retention_time column

Impact on Storage Cost

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

Where storage cost goes:

[Active storage]           <- the current table data
         +
[Time Travel storage]      <- pre-change micro-partitions (for the configured window)
         +
[Fail-safe storage]        <- data after the Time Travel window (7 days)
         =
[Total storage cost]

* The more often a table is UPDATEd or DELETEd, the larger its Time Travel and Fail-safe storage
* INSERT-only tables incur almost no additional storage

-- Check storage usage
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

-- Read the data as of a past point in time (AT clause)
SELECT * FROM sales
  AT(TIMESTAMP => '2026-03-20 10:00:00'::TIMESTAMP);

-- Read the data as of a given time ago (OFFSET)
SELECT * FROM sales
  AT(OFFSET => -3600);  -- one hour ago

-- Read the data as it was before a given statement (BEFORE clause)
SELECT * FROM sales
  BEFORE(STATEMENT => '01b2c3d4-0001-a234-0000-00000000beef');

-- Restore a dropped table
UNDROP TABLE deleted_sales;

-- Clone as of a past point in time
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