Retention Costs & Storage Impact Analysis
Data backup and recovery features in Snowflake, such as Time Travel and Fail-Safe, are incredibly powerful. However, they come with a direct impact on your storage costs.
Because Snowflake charges you for the physical storage used by your data, extending Time Travel retention or maintaining high-churn tables as Permanent tables can drastically increase your monthly bill.
Cost Mechanics: Time Travel and Fail-Safe
- Time Travel Storage: Snowflake retains historical versions of modified or deleted data for a configurable period (0 to 90 days). If a table has a high "churn rate" (frequent
UPDATEorDELETEoperations), the historical data retained for Time Travel can consume more storage than the active table itself. - Fail-Safe Storage: After the Time Travel period ends, data moves into Fail-Safe for an unconfigurable 7 days (for Permanent tables). This is a non-queryable backup managed by Snowflake support. You pay for this storage just like active data.
Hands-On Demonstration: Right-Sizing Retention
In this lab, we will analyze the difference between Permanent and Transient tables, configure retention periods, and insert sample data to visualize a high-churn scenario.
1. Database & Schema Setup
Copy and paste the following code into your Snowflake worksheet.
USE ROLE SYSADMIN;
-- Create our training database and schema
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
USE DATABASE BEEMS_TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS STORAGE_COST_LAB;
USE SCHEMA STORAGE_COST_LAB;
2. Permanent vs. Transient Tables
Let's create two tables: one Permanent (with 90 days of Time Travel) and one Transient (with 0 days of Time Travel and no Fail-Safe).
-- 1. Create a Permanent Table with maximum retention (Enterprise Edition required for 90 days)
CREATE OR REPLACE TABLE PERMANENT_CUSTOMER_TRANSACTIONS (
TXN_ID INT,
CUSTOMER_NAME STRING,
STATE STRING,
AMOUNT NUMBER(10,2)
) DATA_RETENTION_TIME_IN_DAYS = 90;
-- 2. Create a Transient Table (Great for temporary or easily reproducible ETL data)
CREATE OR REPLACE TRANSIENT TABLE TRANSIENT_ETL_CACHE (
CACHE_ID INT,
CUSTOMER_NAME STRING,
STATE STRING,
AMOUNT NUMBER(10,2)
) DATA_RETENTION_TIME_IN_DAYS = 0; -- 0 days of Time Travel, 0 days of Fail-Safe
3. Inserting Sample Data (Indian Demographics)
Let's insert some mock data containing names from various Indian states.
-- Insert data into the permanent table
INSERT INTO PERMANENT_CUSTOMER_TRANSACTIONS VALUES
(101, 'Aarav Desai', 'Gujarat', 1500.50),
(102, 'Ananya Sharma', 'Delhi', 890.00),
(103, 'Vikram Singh', 'Punjab', 12000.00),
(104, 'Meera Menon', 'Kerala', 450.75),
(105, 'Karthik Natarajan', 'Tamil Nadu', 3200.00),
(106, 'Rajesh Patil', 'Maharashtra', 8500.00),
(107, 'Priya Banerjee', 'West Bengal', 1100.25);
-- Insert data into the transient table
INSERT INTO TRANSIENT_ETL_CACHE
SELECT * FROM PERMANENT_CUSTOMER_TRANSACTIONS;
4. Simulating "High Churn"
When you update data, Snowflake writes a new micro-partition and retains the old one for Time Travel.
-- Update all records in the permanent table
-- This doubles the storage used by this table behind the scenes! The old values are kept for 90 days.
UPDATE PERMANENT_CUSTOMER_TRANSACTIONS
SET AMOUNT = AMOUNT * 1.05;
-- Update all records in the transient table
-- The old values are discarded immediately because DATA_RETENTION_TIME_IN_DAYS = 0.
UPDATE TRANSIENT_ETL_CACHE
SET AMOUNT = AMOUNT * 1.05;
Analyzing Storage Costs
To find out exactly how much storage your tables are consuming (including active data, Time Travel, and Fail-Safe), you can query the Account Usage views.
(Note: If you need to know your current username to ensure you have the ACCOUNTADMIN role, you can run SELECT CURRENT_USER(); to check).
USE ROLE ACCOUNTADMIN;
-- View storage metrics across your account
SELECT
TABLE_CATALOG AS DATABASE_NAME,
TABLE_SCHEMA AS SCHEMA_NAME,
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_BILLABLE_GB
FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS
WHERE TABLE_CATALOG = 'BEEMS_TRAINING_DB'
ORDER BY TOTAL_BILLABLE_GB DESC;
Cost Optimization Strategies
- Use Transient Tables for ETL/Staging: If a table is dropped and recreated daily, or can be easily rebuilt from raw files, make it
TRANSIENT. This avoids Fail-Safe storage costs entirely. - Right-Size Time Travel: Don't default every table to 90 days of Time Travel. Use 90 days for critical financial tables, but 1 to 7 days for standard dimensional tables.
- Avoid Pointless Updates: High-frequency updates on tables with long Time Travel retention will skyrocket your storage costs.
Summary
- Storage = Active + Time Travel + Fail-Safe.
- Permanent Tables incur both Time Travel and Fail-Safe costs.
- Transient Tables incur limited Time Travel costs (max 1 day) and zero Fail-Safe costs.
- Monitor
TABLE_STORAGE_METRICSregularly to identify tables where retention costs exceed business value.