Time Travel retention periods in Snowflake - Deep Dive

Overview - Time Travel retention periods

What is it?

Time Travel retention periods in Snowflake let you access past versions of your data for a set time. This means you can see or restore data as it was before changes or deletions. It works like a rewind button for your database tables and schemas. The retention period is how long Snowflake keeps these past versions available.

Why it matters

Without Time Travel retention, accidental data changes or deletions could cause permanent loss. This feature helps recover from mistakes quickly without complex backups. It also supports auditing and historical analysis by letting you query past data states. Without it, data recovery would be slow, costly, or impossible.

Where it fits

You should first understand basic Snowflake data storage and table concepts. After learning Time Travel retention, you can explore Fail-safe, which is a longer-term recovery option. Later, you might study data cloning and continuous data protection features that build on Time Travel.

Mental Model

Core Idea

Time Travel retention periods define how long Snowflake keeps past data versions so you can rewind and recover changes within that time.

Think of it like...

It's like a DVR recording your favorite TV show for a set number of days, letting you rewind and watch earlier scenes before the recording expires.

┌───────────────────────────────┐
│ Current Data State            │
├─────────────┬─────────────────┤
│ Past States │ Retention Period │
│  (Snapshots)│  (Time Travel)   │
│  t-1, t-2   │  1 to 90 days    │
└─────────────┴─────────────────┘

You can query or restore any snapshot within the retention period.

Build-Up - 6 Steps

1

Foundation

What is Time Travel in Snowflake

Concept: Introduces the basic idea of Time Travel as a feature to access historical data versions.

Time Travel in Snowflake lets you see or restore data as it was in the past. It keeps snapshots of your tables and schemas automatically. You can query or recover data from these snapshots within a set time called the retention period.

Result

You understand that Snowflake stores past data states for a limited time to help with recovery and auditing.

Understanding Time Travel as a rewind feature helps you see data changes as reversible within a time window.

2

Foundation

Retention Period Basics

Concept: Explains what retention period means and its default settings.

The retention period is how long Snowflake keeps past data versions available. By default, it's 1 day for standard accounts. You can increase it up to 90 days depending on your Snowflake edition. After this period, old data versions are removed and can't be recovered via Time Travel.

Result

You know the default and maximum time you can rewind data in Snowflake.

Knowing retention limits sets expectations on how far back you can recover data.

3

Intermediate

Configuring Retention Periods

🤔Before reading on: do you think retention periods can be set per table, schema, or only account-wide? Commit to your answer.

Concept: Shows how retention periods can be customized at different levels.

You can set Time Travel retention periods at the account, database, schema, or table level. Table-level settings override schema, which overrides database, which overrides account defaults. This lets you keep longer history for critical tables and shorter for others.

Result

You can control how long past data versions are kept for different parts of your data.

Understanding hierarchical retention settings helps optimize storage costs and recovery needs.

4

Intermediate

Impact on Storage and Costs

🤔Before reading on: does increasing retention period increase storage costs, decrease, or have no effect? Commit to your answer.

Concept: Explains how retention periods affect storage usage and billing.

Longer retention means Snowflake stores more historical data, increasing storage usage and costs. Shorter retention saves space and money but reduces recovery time. You pay for the extra storage used by Time Travel data separately from current data.

Result

You understand the tradeoff between recovery time and storage cost.

Knowing cost impact guides smart retention period choices balancing safety and budget.

5

Advanced

Fail-safe vs Time Travel Retention

🤔Before reading on: do you think Fail-safe is part of Time Travel or a separate recovery feature? Commit to your answer.

Concept: Distinguishes Time Travel retention from Fail-safe, a longer recovery option.

Time Travel retention is user-accessible and configurable, lasting up to 90 days. Fail-safe is a 7-day period after Time Travel ends, used by Snowflake support for emergency recovery only. You cannot query or restore data during Fail-safe yourself.

Result

You know the limits of Time Travel and when Fail-safe applies.

Understanding Fail-safe clarifies the full data recovery lifecycle and user control limits.

6

Expert

Retention Periods and Data Cloning

🤔Before reading on: do you think cloned tables share Time Travel data or have independent retention? Commit to your answer.

Concept: Explores how retention periods affect zero-copy cloning and data version sharing.

Cloned tables share the same underlying data and Time Travel history as the source at clone time. Retention periods apply to both. Changes to clone or source create new versions separately. Managing retention carefully avoids unexpected storage growth from clones.

Result

You understand how retention periods interact with cloning to affect storage and recovery.

Knowing this prevents surprises in storage costs and data recovery when using clones.

Under the Hood

Snowflake stores data in micro-partitions with metadata tracking changes over time. Time Travel retention keeps snapshots of these micro-partitions for the configured period. When you query past data, Snowflake reconstructs the state by combining current and historical micro-partitions. After retention expires, old micro-partitions are purged to free storage.

Why designed this way?

This design balances fast access to historical data with efficient storage. Using micro-partitions and metadata avoids full copies of data, saving space. Retention periods limit storage growth and cost. Alternatives like full backups are slower and more expensive.

┌───────────────┐
│ Data Storage  │
│ Micro-partitions│
├───────────────┤
│ Metadata with │
│ change history│
├───────────────┤
│ Time Travel   │
│ Retention    │
│ (Snapshots)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Query Past    │
│ Data State    │
│ (Reconstruction)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can you recover data older than the retention period using Time Travel? Commit to yes or no.

Common Belief: Time Travel lets you recover any past data regardless of age.

Reality: You can only recover data within the configured retention period. Older data is permanently removed from Time Travel.

Why it matters: Believing otherwise leads to relying on Time Travel for long-term backups, risking data loss.

Quick: Does setting a longer retention period automatically increase your storage costs? Commit to yes or no.

Common Belief: Retention period length does not affect storage costs.

Reality: Longer retention stores more historical data, increasing storage usage and costs.

Why it matters: Ignoring this causes unexpected billing spikes.

Quick: Is Fail-safe accessible to users like Time Travel? Commit to yes or no.

Common Belief: Fail-safe is just an extension of Time Travel users can access.

Reality: Fail-safe is a separate, Snowflake-managed recovery period not accessible to users.

Why it matters: Confusing these leads to false confidence in data recovery options.

Quick: Do cloned tables have independent Time Travel histories? Commit to yes or no.

Common Belief: Cloned tables have their own separate Time Travel data from the start.

Reality: Clones share the source table's Time Travel history at clone time and only diverge after changes.

Why it matters: Misunderstanding this can cause unexpected storage use and recovery confusion.

Expert Zone

1

Retention periods can be dynamically adjusted, but reducing them does not immediately purge old data; cleanup happens asynchronously.

2

Time Travel snapshots are consistent across databases and schemas, enabling cross-object recovery within the retention window.

3

Retention settings interact with data retention policies and legal compliance, requiring careful configuration in regulated environments.

When NOT to use

Time Travel retention is not suitable for long-term archival or compliance storage; use Snowflake's external stages or dedicated archival solutions instead. Also, avoid very long retention on large tables to prevent excessive storage costs.

Production Patterns

In production, teams set longer retention on critical tables like financial transactions and shorter on logs or temporary data. Automated scripts monitor retention settings and storage usage to optimize costs. Time Travel is combined with cloning for safe testing and rollback.

Connections

Version Control Systems (e.g., Git)

Similar pattern of storing historical versions and enabling rollback.

Understanding Time Travel like version control helps grasp how data snapshots enable recovery and auditing.

Backup and Restore Strategies

Builds on the idea of data recovery but offers a faster, integrated alternative for short-term recovery.

Knowing Time Travel complements backups clarifies when to use each for data protection.

Undo Functionality in Text Editors

Same concept of reverting to previous states within a limited window.

Recognizing this connection shows how Time Travel makes databases more forgiving like everyday software.

Common Pitfalls

#1 Setting retention period too long on large tables without monitoring storage.

Wrong approach:

ALTER TABLE sales SET DATA_RETENTION_TIME_IN_DAYS = 90;

Correct approach:

ALTER TABLE sales SET DATA_RETENTION_TIME_IN_DAYS = 7; -- balance recovery and cost

Root cause:

Misunderstanding that longer retention increases storage and costs significantly.

#2 Assuming Fail-safe can be used by users to recover data.

Wrong approach:

SELECT * FROM sales AT (TIMESTAMP => '2022-01-01') AFTER Fail-safe period;

Correct approach:

Use Time Travel within retention period; contact Snowflake support for Fail-safe recovery.

Root cause:

Confusing Fail-safe with Time Travel and user access capabilities.

#3 Changing retention period at account level only, expecting all tables to update immediately.

Wrong approach:

ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 14;

Correct approach:

ALTER TABLE important_table SET DATA_RETENTION_TIME_IN_DAYS = 14; -- override if needed

Root cause:

Not knowing retention settings cascade and can be overridden at lower levels.

Key Takeaways

Time Travel retention periods let you rewind and recover past data versions within a set time window.

Retention periods can be configured at multiple levels, affecting how long historical data is kept and impacting storage costs.

Fail-safe is a separate, Snowflake-managed recovery period after Time Travel ends, not accessible to users.

Cloned tables share Time Travel history with their source at clone time, affecting storage and recovery.

Balancing retention length is key to optimizing data recovery capabilities and controlling storage expenses.