Study guide
6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
SnowPro Advanced: Architect ARA-C01
Track your progress as you read
Finished sections get a check mark.
1 Introduction: The ARA-C01 Exam
About the SnowPro Advanced: Architect (ARA-C01) Exam
2 Chapter 1: Account Strategy & Organization Architecture
1.1 Multi-Account Strategy & Snowflake Organizations 1.2 Snowflake Parameter Hierarchy & Configuration 1.3 Database and Schema Design Architecture
3 Chapter 2: Identity, Access Control & Storage Integrations
2.1 Role-Based Access Control Architecture & Hierarchy 2.2 Secondary Roles, Object Ownership & Delegated Administration 2.3 Cloud Storage Integrations & IAM Trust Policies 2.4 Identity Federation, SSO, SCIM & Authentication Policies
4 Chapter 3: Governance, Data Privacy, Network Security & Compliance
3.1 Column-Level Masking & Row Access Policies 3.2 Object Tagging, Tag-Based Masking & Audit Governance 3.3 Encryption, Key Management & Tri-Secret Secure 3.4 Aggregation Policies, Projection Policies & External Tokenization 3.5 Network Policies, Network Rules, External Access & Private Connectivity 3.6 Snowflake Editions & Compliance Architecture (PCI DSS, PII & PHI)
5 Chapter 4: Data Modeling in Snowflake
4.1 Dimensional Modeling: Star vs. Snowflake Schemas 4.2 Data Vault 2.0 Architecture in Snowflake 4.3 Relational Constraints & Query Optimizer Integration
6 Chapter 5: Data Sharing & Collaboration Solutions
5.1 Direct Data Sharing Architecture & Security 5.2 Managed Reader Accounts for External Consumers 5.3 Snowflake Marketplace, Data Exchange & Clean Rooms
7 Chapter 6: Multi-Cloud, Multi-Region & Business Continuity
6.1 Database & Account Replication Mechanics 6.2 Disaster Recovery Architecture & Client Redirect 6.3 Cross-Cloud Auto-Fulfillment & Listing Replication 6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
8 Chapter 7: Environments, Lakehouse, DevOps & AI/ML Applications
7.1 Multi-Tier Environment Strategy & Zero-Copy Cloning 7.2 Data Lakehouse Architecture & Apache Iceberg 7.3 DataOps, CI/CD & Snowflake Git Integration 7.4 AI/ML Pipelines & Applications: Cortex AI, ML Functions, Snowpark Container Services, Streamlit & Native Apps
9 Chapter 8: Data Ingestion Architecture
8.1 Bulk Ingestion Architecture: COPY INTO Optimization 8.2 Snowpipe Continuous Automated Ingestion 8.3 Snowpipe Streaming & Real-Time Ingestion 8.4 Data Unloading Architecture & Cloud Export Patterns 8.5 Schema Detection, Schema Evolution & Source-Change Architecture
10 Chapter 9: Continuous Pipelines & Stream Transformation
9.1 Change Data Capture (CDC) with Snowflake Streams 9.2 Task Orchestration, DAGs & Serverless Scheduling 9.3 Declarative Data Pipelines with Dynamic Tables 9.4 Views, Materialized Views & Transformation Table Design
11 Chapter 10: Semi-Structured Data & Ecosystem Tooling
10.1 Semi-Structured Data: VARIANT, OBJECT & ARRAY Processing 10.2 Snowflake Ecosystem Connectors, Drivers & SQL API 10.3 Snowpark, User-Defined Functions & Stored Procedures
12 Chapter 11: Virtual Warehouse Architecture & Concurrency
11.1 Virtual Warehouse Architecture, Sizing & Credit Economics 11.2 Multi-Cluster Warehouses & Concurrency Scaling Policies 11.3 Warehouse Types & Query Acceleration Service
13 Chapter 12: Query Profiling & Performance Diagnostics
12.1 Query Profile Deep-Dive & Bottleneck Analysis 12.2 Memory Spilling, Warehouse Queuing & Timeout Governance 12.3 Three-Tier Caching Architecture & Result Reuse
14 Chapter 13: Storage Optimization, Cost Governance & Observability
13.1 Micro-Partitioning & Automatic Clustering 13.2 Search Optimization Service (SOS) & Point Lookup Acceleration 13.3 Resource Monitors, Financial Governance & Usage Observability 13.4 Warehouse Monitoring, Alerts, Event Tables & Performance Explorer
6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
Key Takeaways Explain This Topic AI
DATA_RETENTION_TIME_IN_DAYS governs Time Travel retention: Standard Edition is limited to 0 or 1 day, while Enterprise and Business Critical Editions support 0 to 90 days for permanent tables.
Transient and Temporary tables support a maximum of 1 day (or 0) of Time Travel across all editions and have zero days of Fail-safe, making them ideal for high-churn staging tables.
Historical queries support AT (inclusive of the specified point) and BEFORE (strictly prior to the point) using TIMESTAMP, OFFSET (negative seconds), or STATEMENT (query ID).
Undrop operations (UNDROP TABLE, UNDROP SCHEMA, UNDROP DATABASE) restore dropped objects within Time Travel; namespace collisions must be resolved by renaming the active object first.
Fail-safe provides 7 non-configurable days of disaster recovery after Time Travel expires, accessible solely via Snowflake Support for catastrophic data loss; total storage is audited via ACCOUNT_USAGE.TABLE_STORAGE_METRICS.
Last updated: September 2026
6.4 Data Protection: Time Travel, Fail-safe & Storage Costs
Data protection and lifecycle governance are essential responsibilities for enterprise data architects. Accidental data deletions, errant batch transformation updates, schema corruption, and catastrophic storage failures require systematic recovery mechanisms. Snowflake provides a continuous, multi-tiered data protection lifecycle composed of Time Travel , Undrop operations , and Fail-safe .
While these primitives guarantee high availability and operational recoverability, they have direct financial ramifications: modifying or deleting records does not immediately release physical cloud storage. Architects must balance recovery windows against storage billing overhead.
Time Travel Architecture & Retention Mechanics
Snowflake's underlying storage engine stores data in immutable, columnar micro-partitions. When a table undergoes DML operations ( UPDATE , DELETE , MERGE , or TRUNCATE ), Snowflake does not overwrite existing files in place; instead, it marks old micro-partitions as historical and generates new micro-partitions for updated records. Time Travel enables querying, cloning, and restoring these historical micro-partitions up to a defined retention window. Time Travel Horizon
- ◄───────────────────────────────────────────────────────►
- Past Present
- ───────────────────────────────────┬─────────────────────────────────────────────►
- Historical Micro-Partitions │ Current Active Micro-Partitions
- Retained for Time Travel │ Queryable by standard SELECT statements
- (0 to 90 Days) │
- Queryable via AT | BEFORE │
- Restorable via UNDROP / CLONE │
- The DATA_RETENTION_TIME_IN_DAYS Parameter
Time Travel duration is controlled by the object parameter DATA_RETENTION_TIME_IN_DAYS . It participates in Snowflake's parameter hierarchy and can be set at the Account, Database, Schema, or Table level: -- Set account-wide default retention (Enterprise Edition)
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 14;
- - Override retention at database level
- ALTER DATABASE analytics_dw SET DATA_RETENTION_TIME_IN_DAYS = 30;
- - Configure maximum 90-day retention on mission-critical table
- ALTER TABLE analytics_dw.public.customer_ledger SET DATA_RETENTION_TIME_IN_DAYS = 90;
- - Disable Time Travel entirely for ephemeral scratch table
- CREATE TABLE scratch_temp (id INT, payload STRING) DATA_RETENTION_TIME_IN_DAYS = 0;
- Retention Limits by Snowflake Edition & Table Type
A critical set of rules tested on the SnowPro Advanced: Architect exam governs retention limits across editions and table types:
Table Type Standard Edition Enterprise Edition Business Critical Edition Fail-safe Duration
Permanent Table 0 or 1 Day (Default: 1) 0 to 90 Days (Default: 1) 0 to 90 Days (Default: 1) 7 Days (Non-configurable)
- Transient Table 0 or 1 Day (Default: 1) 0 or 1 Day (Default: 1) 0 or 1 Day (Default: 1) 0 Days (No Fail-safe)
- Temporary Table 0 or 1 Day (Default: 1) 0 or 1 Day (Default: 1) 0 or 1 Day (Default: 1) 0 Days (No Fail-safe)
MIN_DATA_RETENTION_TIME_IN_DAYS is an account-level parameter (set by ACCOUNTADMIN ) that enforces a floor for permanent tables: the effective retention is the higher of DATA_RETENTION_TIME_IN_DAYS and MIN_DATA_RETENTION_TIME_IN_DAYS . It does not apply to transient or temporary tables, external tables, materialized views, or streams.
Exam Trap: On Snowflake Standard Edition, attempting to set DATA_RETENTION_TIME_IN_DAYS to any value greater than 1 (e.g., ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 7; ) results in a compilation error. Upgrading to Enterprise Edition is mandatory for retention windows between 2 and 90 days.
Historical Querying Syntax: AT vs. BEFORE
Snowflake provides two temporal clauses to inspect historical data:
AT : Evaluates the table state at and including the exact specified point in time or statement.
BEFORE : Evaluates the table state strictly prior to the specified point in time or statement (excluding changes from that statement). -- Method 1: Point-in-time timestamp (inclusive)
- SELECT *
- FROM orders AT (TIMESTAMP => '2026-09-23 09:30:00 -07:00'::TIMESTAMP_TZ);
- - Method 2: Relative time offset in seconds (e.g., exactly 2 hours ago)
- - Note: OFFSET requires negative integer seconds relative to CURRENT_TIMESTAMP()
- SELECT *
- FROM orders AT (OFFSET => -60 * 120);
- - Method 3: Query ID / Statement (strictly before the specified query executed)
- SELECT *
- FROM orders BEFORE (STATEMENT => '01af501e-0000-0234-0000-000100020003');
- Restoring Data via Zero-Copy Clone and Time Travel
If a table suffers data corruption from an errant batch update, an architect can restore the exact historical state without restoring backups using CLONE with Time Travel: -- Create a restored replacement table from before the corrupted query ID
- CREATE OR REPLACE TABLE orders_restored
- CLONE orders BEFORE (STATEMENT => '01af501e-0000-0234-0000-000100020003');
- - Swap the restored table into production instantaneously via metadata swap
- ALTER TABLE orders SWAP WITH orders_restored;
- - Clean up the temporary corrupted table
- DROP TABLE orders_restored;
Undrop Operations & Collision Resolution
Snowflake allows recovering dropped databases, schemas, and tables with a single command, provided the object was dropped within its configured Time Travel retention window.
- Undrop Commands & Preserved Metadata UNDROP TABLE sales_dw.public.orders;
- UNDROP SCHEMA sales_dw.public;
- UNDROP DATABASE sales_dw;
When an object is undropped:
The physical micro-partitions remain completely untouched; no data copying occurs.
The object returns with its data, structure, comments, and constraints as they were when it was dropped.
Name Collision Protocol (CRITICAL EXAM CONCEPT)
A frequent real-world scenario and exam trap occurs when an object is dropped, and subsequent automated scripts or users create a new object with the exact same name in that schema before the administrator can undrop the original. -- Step 1: Accidental drop of critical table
DROP TABLE public.customers;
- - Step 2: Automated deployment script runs and recreates an empty table
- CREATE TABLE public.customers (id INT, name STRING);
- - Step 3: Administrator attempts to undrop original table
- UNDROP TABLE public.customers;
-- Result: SQL compilation error: Object 'CUSTOMERS' already exists.
Collision Resolution Workflow
To recover the dropped historical table, the administrator must resolve the namespace collision by renaming the active object first: -- Step 1: Rename the active object to free up the namespace
ALTER TABLE public.customers RENAME TO public.customers_new_empty;
- - Step 2: Undrop the original dropped table
- UNDROP TABLE public.customers;
- - Step 3: Verify data and optionally reconcile records
- SELECT COUNT(*) FROM public.customers;
- Dropping and Recreating vs. Undrop
If a user executes CREATE OR REPLACE TABLE t (...) , Snowflake implicitly drops the existing table and creates a new one. The old table is preserved in Time Travel and can be undropped, but only after renaming or dropping the replacement table.
Fail-safe Mechanics & Disaster Recovery
Once a table's configured Time Travel retention period expires, historical micro-partitions transition automatically into Fail-safe . Snowflake Data Protection Timeline
- Active Ingestion Time Travel Window Fail-safe Window Released
- (Day 0) (1 to 90 Days) (7 Days Fixed) (Purged)
- ───────┬─────────────────────────────┬───────────────────────────┬──────────────►
- │ • Fully self-service │ • Non-configurable │ • Permanently
- │ • Query via AT | BEFORE │ • Snowflake Support ONLY │ deleted from
- │ • Recover via UNDROP │ • Disaster recovery only │ cloud object
- │ • Set per-object (0-90) │ • Zero Fail-safe for │ storage
- │ │ Transient/Temporary │
- Fail-safe Architectural Rules
Fixed 7-Day Window : Fail-safe duration is strictly 7 days and cannot be configured, extended, or reduced by any customer or administrator.
No User Access : Customers, including ACCOUNTADMIN users, cannot query historical data in Fail-safe using AT or BEFORE , cannot execute UNDROP , and cannot clone from Fail-safe.
Support-Only Disaster Recovery : Fail-safe is solely accessible via Snowflake Support as a last-resort disaster recovery mechanism. It is intended for catastrophic events such as severe cloud storage corruption or operational emergencies. Recovery from Fail-safe involves engineering evaluation and may take several hours or days to fulfill.
Table Type Exclusions : Transient and Temporary tables have ZERO days of Fail-safe . As soon as Time Travel expires (maximum 1 day), transient and temporary micro-partitions are immediately marked for release and purged from storage.
Storage Billing Lifecycle & Table Storage Metrics
Every gigabyte of active, Time Travel, and Fail-safe data is stored in underlying cloud provider object storage and billed at standard storage rates. For high-churn tables, failing to govern the lifecycle can lead to catastrophic storage cost compounding.
Micro-Partition Churn & Storage Compounding
Consider an Enterprise Edition table storing 1 TB of base data where a nightly batch process rewrites 20% of the table (200 GB of micro-partitions churned daily): Total Billable Storage = Active Storage + Time Travel Storage + Fail-safe Storage \text{Total Billable Storage} = \text{Active Storage} + \text{Time Travel Storage} + \text{Fail-safe Storage} Total Billable Storage = Active Storage + Time Travel Storage + Fail-safe Storage
If DATA_RETENTION_TIME_IN_DAYS = 90 :
- Active storage = 1 TB
- Time Travel storage (90 days × \times × 200 GB/day) = 18 TB
- Fail-safe storage (7 days × \times × 200 GB/day) = 1.4 TB
- Total billable storage = 20.4 TB (over 20x the base active footprint!)
If redesigned as a Transient Table ( DATA_RETENTION_TIME_IN_DAYS = 1 , 0 Fail-safe):
- Active storage = 1 TB
- Time Travel storage (1 day × \times × 200 GB/day) = 0.2 TB
- Fail-safe storage = 0 TB
- Total billable storage = 1.2 TB (a 94% storage cost reduction!)
- Monitoring Storage via ACCOUNT_USAGE.TABLE_STORAGE_METRICS
Architects query SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS to audit storage consumption across lifecycle stages: -- Audit top 20 tables by billable storage footprint (Active + Time Travel + Fail-safe)
- SELECT
- table_catalog AS database_name,
- table_schema AS schema_name,
- table_name,
- ROUND(active_bytes / POWER(1024, 3), 2) AS active_gb,
- ROUND(time_travel_bytes / POWER(1024, 3), 2) AS time_travel_gb,
- ROUND(failsafe_bytes / POWER(1024, 3), 2) AS failsafe_gb,
- ROUND(retained_for_clone_bytes / POWER(1024, 3), 2) AS clone_retained_gb,
- ROUND((active_bytes + time_travel_bytes + failsafe_bytes) / POWER(1024, 3), 2) AS total_billable_gb,
- table_dropped
- FROM snowflake.account_usage.table_storage_metrics
- WHERE (active_bytes + time_travel_bytes + failsafe_bytes) > 0
- ORDER BY total_billable_gb DESC
- LIMIT 20;
- Architectural Guidelines for Storage Cost Governance
Use Transient Databases and Schemas for ELT Staging : Never ingest raw, staging, or transient data into permanent tables. Set staging environments to TRANSIENT to completely eliminate Fail-safe billing.
Right-Size Retention for Volatile Tables : On permanent dimension and fact tables with frequent updates, evaluate whether 90 days of Time Travel is truly required by regulatory compliance. Reducing retention from 90 days to 7 or 14 days drastically curtails historical storage churn.
Monitor Retained Clone Storage : Zero-copy cloning initially shares existing micro-partitions without extra storage costs ( RETAINED_FOR_CLONE_BYTES = 0 ). However, when source or cloned tables undergo modifications or when the source table is dropped, Snowflake retains historical micro-partitions as long as any clone references them, incurring continuous storage charges.