Time Retention

Snowflake · Tables & Data Handling

Theory

Time Travel lets you query, clone or restore data as it existed at an earlier point within the retention period. You use AT or BEFORE with a TIMESTAMP, an OFFSET in seconds, or a STATEMENT (query ID), for example SELECT * FROM orders AT(OFFSET => -300) for five minutes ago, or BEFORE(STATEMENT => '<query_id>') for just before a bad DELETE. UNDROP TABLE, SCHEMA or DATABASE brings back a dropped object.

Retention is 1 day by default and can be raised to 90 days for permanent objects on Enterprise and above. When Time Travel ends, permanent tables move into a 7-day Fail-safe period; that data cannot be queried and can only be recovered by Snowflake Support.

Why we use it

Mistakes happen: a DELETE without WHERE, a wrong UPDATE, a dropped table. Time Travel lets you fix them yourself in minutes, without restoring backups, and lets you compare data with how it looked before.

When to use it

Use Time Travel for self-service recovery and point-in-time queries within the retention window. Treat Fail-safe only as disaster recovery through Support, not a backup plan. For long-term history, keep your own snapshots or clones.

What Time Travel Lets You Do

Query, clone, or restore a table exactly as it looked at a past moment — by timestamp or by an offset in seconds — invaluable for recovering from an accidental UPDATE or DELETE.

Retention Windows

Defaults to 1 day and can be extended up to 90 days on Enterprise edition; UNDROP instantly restores an accidentally dropped table within that window.

Fail-safe as a Last Resort

Once Time Travel expires on a Permanent table, Fail-safe gives a further 7-day, Snowflake-support-only recovery window — not self-service, but a genuine last line of defense.

The Data Lifecycle

┌─────────────┐ │ Active Data │ ← you query this ├─────────────┤ │ Time Travel │ ← recoverable (1-90 days) │ (Queryable) │ SELECT * FROM t AT(OFFSET => -3600) ├─────────────┤ │ Fail-Safe │ ← Snowflake Support only (7 days) │(Not visible)│ ├─────────────┤ │ Purged │ ← gone forever └─────────────┘

How it works

  1. Bad change or dropped table discovered
  2. Is it still within the retention period? Yes: Use AT/BEFORE to query the old data. No: Only Fail-safe via Snowflake Support may help.
  3. Was the whole table dropped? Yes: Run UNDROP TABLE. No: Insert or clone the old rows back.
  4. Verify the restored data
  5. Review retention settings for next time

Example

-- Simulate an accidental delete, then recover:
DELETE FROM orders WHERE region = 'NORTH';

-- Query data as it looked 5 minutes ago
SELECT * FROM orders AT(OFFSET => -300);

-- Restore just the deleted rows
INSERT INTO orders
SELECT * FROM orders AT(OFFSET => -300) WHERE region = 'NORTH';

-- If the whole table was dropped:
DROP TABLE orders;
UNDROP TABLE orders;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic