Time Travel: Hidden Storage Costs You're Probably Paying

Time Travel: Hidden Storage Costs You're Probably Paying

The default 1-day Time Travel setting may be costing you thousands in storage. Learn how to optimize retention periods based on your actual needs.

The Problem

Snowflake's default Time Travel retention is 1 day for all tables. Enterprise Edition supports up to 90 days. This means Snowflake stores every version of your data for the retention period, dramatically increasing storage costs.

The Cost Impact

For tables with frequent updates, Time Travel can double or triple storage costs. A 10TB table with daily updates and 90-day retention could actually consume 40-50TB of storage.

Optimizing Time Travel

-- Set at table level
ALTER TABLE <table_name> SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Set at schema level
ALTER SCHEMA <schema_name> SET DATA_RETENTION_TIME_IN_DAYS = 1;

-- Set at database level
ALTER DATABASE <db_name> SET DATA_RETENTION_TIME_IN_DAYS = 1;

Best Practices

• Staging/temp tables: 0-1 days
• Development tables: 1-3 days
• Production tables with infrequent changes: 7-30 days
• Compliance-required tables: As needed (7-90 days)
• High-churn tables: 0-1 days

Real-World Example

A SaaS company with 50TB of production data and 90-day retention across all tables was paying for 180TB of storage. By auditing Time Travel usage and setting appropriate retention periods (0-7 days for most tables), they reduced storage to 65TB, saving $23,000 monthly.

Monitoring Time Travel Storage

SELECT TABLE_NAME,
BYTES / (1024*1024*1024) AS SIZE_GB,
TIME_TRAVEL_BYTES / (1024*1024*1024) AS TIME_TRAVEL_GB,
FAILSAFE_BYTES / (1024*1024*1024) AS FAILSAFE_GB
FROM INFORMATION_SCHEMA.TABLE_STORAGE_METRICS
ORDER BY TIME_TRAVEL_BYTES DESC;

Key Takeaways

• Default Time Travel retention may be costing you 2-3x more storage
• Most tables don't need more than 1-7 days
• Set retention at the table level for granular control
• Monitor Time Travel storage usage regularly