An Intro to Snowflake Time Travel

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Snowflake Time Travel is one of the platform features people hear about early and usually understand only halfway. The simple version is true: it lets you query and restore historical data. The important version is more nuanced: retention periods vary by edition and object settings, storage costs still matter, and Time Travel is most useful when you treat it as an operational safety feature rather than a backup strategy.

This guide covers how Snowflake Time Travel works, when to use it, and the production gotchas that surprise most teams.

In this post, we’ll cover:

• What Time Travel actually does
• Retention limits and edition differences
• Querying historical data
• Restoring dropped or changed objects
• Cost and operational gotchas

What Snowflake Time Travel does

Time Travel lets you access historical versions of Snowflake data for a limited retention period. That includes:

• Querying data as it existed at an earlier time
• Restoring dropped tables, schemas, and databases
• Cloning objects from a previous point in time

The official docs describe Time Travel as access to data that has been changed or deleted at any point within a defined period. That makes it useful for mistakes like accidental deletes, bad updates, and “we deployed the wrong transformation” moments.

What it is not:

• It is not a replacement for long-term backups
• It is not infinite history
• It is not free just because data is no longer “active”

Time Travel is best thought of as short-horizon recovery and debugging.

Retention limits matter more than most teams expect

By default, Snowflake enables Time Travel automatically with a 1-day retention period. According to the docs, retention periods above 1 day require Enterprise Edition or higher, and can be configured up to 90 days for databases, schemas, and tables.

That means a lot of teams think they have “historical recovery” when they really only have 24 hours.

You control retention with the DATA_RETENTION_TIME_IN_DAYS parameter at the account, database, schema, or table level. The practical implication is:

• Your account may default to 1 day
• Specific objects may inherit something shorter or longer
• Restores fail once the retained history is gone

If Time Travel is part of your incident response story, do not assume the defaults are enough. Check them.

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN ACCOUNT;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN DATABASE analytics;
SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS' IN SCHEMA analytics.marts;

Querying historical data

The most common use of Time Travel is asking “what did this table look like before we changed it?”

Snowflake supports AT and BEFORE clauses for querying historical data. A common pattern looks like this:

SELECT *
FROM orders AT (TIMESTAMP => '2026-04-15 12:00:00'::timestamp_tz)
WHERE customer_id = 42;

Or using an offset relative to now:

SELECT *
FROM orders AT (OFFSET => -60 * 10)
WHERE customer_id = 42;

That kind of query is extremely useful in debugging:

• Compare a dashboard table before and after a dbt run
• Inspect a row before an accidental update
• Validate whether a data quality issue is new or older than the current deployment

For teams that regularly investigate warehouse regressions, this is one of the best “why did this change?” tools Snowflake gives you.

Restoring dropped objects

The second major use case is object recovery. If someone drops a table or schema inside the retention window, you can restore it.

For dropped objects, the pattern usually looks like this:

UNDROP TABLE analytics.marts.daily_revenue;

That sounds simple, but it is operationally important. Many warehouse incidents are not dramatic failures. They are bad DDL, mis-targeted cleanup jobs, or a human deleting the wrong thing in the wrong environment. Time Travel gives you a recovery path without rebuilding from raw data from scratch.

That said, recovery depends entirely on retention still being available. If the object is outside the window, UNDROP will not save you.

Cloning is one of the most underrated Time Travel workflows

Snowflake’s CREATE ... CLONE supports Time Travel as well. That means you can create a clone from a prior point in time instead of only restoring the live object in place.

CREATE TABLE orders_debug CLONE orders
  AT (TIMESTAMP => '2026-04-15 12:00:00'::timestamp_tz);

This is often the safer workflow when you want to inspect or compare old data without changing production objects. A clone lets you:

• Diff historical and current data side by side
• Validate a backfill before restoring anything
• Hand an analyst or engineer a safe sandbox for investigation

In practice, teams often should clone first and restore second.

Time Travel does not mean zero cost

Snowflake documentation is explicit that extended retention requires additional storage, and those charges show up in your monthly bill. This matters for large fact tables and rapidly changing objects.

The common failure mode is this:

1. A team raises retention because it sounds safer.
2. Large mutable tables keep more history around.
3. Storage costs rise.
4. No one revisits the setting.

The right question is not “should we maximize retention everywhere?” It is “which objects justify a longer recovery window?”

Typical candidates for longer retention:

• Curated marts that power executive reporting
• Tables touched by risky batch jobs
• High-value dimensional tables that are hard to reconstruct cleanly

Typical candidates for shorter retention:

• Rebuildable transient staging layers
• Short-lived intermediate tables
• Objects where external lineage or raw storage already provides the real recovery path

Time Travel vs Fail-safe

Snowflake also documents Time Travel and Fail-safe together, which is where a lot of confusion comes from.

The short version:

• Time Travel is for customer-controlled short-term historical access and recovery
• Fail-safe is Snowflake’s last-resort disaster recovery mechanism, not an interactive restore feature you use like Time Travel

If your team talks about “we can always get it back from Fail-safe,” that is usually a sign the recovery model is not well understood.

For day-to-day operations, the feature you actively use is Time Travel.

Common production mistakes

Assuming every object has the same retention

It does not. Retention can be inherited, overridden, or effectively constrained by edition and object type. Check the actual parameters, not the team folklore.

Treating Time Travel like a backup plan for everything

Time Travel is great for recent mistakes. It is not a full disaster recovery architecture, and it does not replace external backup, replication, or object lifecycle planning.

Forgetting cloning exists

Many teams jump straight to UNDROP or object replacement when a safer first move is to create a clone and inspect it.

Ignoring cost until after enabling longer retention

If you extend retention across large mutable objects, expect storage consequences.

When to use Time Travel

Time Travel is especially good for:

• Recovering from accidental deletes and updates
• Debugging bad transformations
• Investigating warehouse regressions
• Creating historical comparison clones

It is weaker as the sole answer for:

• Long-term archival
• Cross-account disaster recovery
• Governance around rebuildable vs non-rebuildable datasets

Frequently asked questions

What is Snowflake Time Travel? Time Travel is a built-in feature that lets you query, clone, or restore data as it existed at a point in time in the recent past — by timestamp, by offset (e.g., 5 minutes ago), or by a statement id. It is enabled by default and works on tables, schemas, and databases.

How long does Snowflake Time Travel keep data? The default is 1 day on every edition. On Enterprise Edition and higher you can raise DATA_RETENTION_TIME_IN_DAYS up to 90 days per object. Once the retention window passes, the historical state moves into Fail-safe, which is recovery-only via Snowflake Support and lasts 7 more days on permanent tables.

Is Time Travel free? Time Travel costs storage for the historical versions Snowflake has to keep alive. On low-churn tables it is effectively free; on high-update tables, raising retention from 1 day to 30 or 90 days can multiply storage cost noticeably. The compute to query a Time Travel point is the same as any other query.

What is the difference between Time Travel and Fail-safe? Time Travel is user-controlled and addressable from SQL (AT, BEFORE, CLONE ... AT). Fail-safe is a non-configurable 7-day disaster-recovery window that only Snowflake Support can restore from, and it only applies to permanent tables. Transient and temporary tables have no Fail-safe.

Can you Time Travel after a DROP TABLE? Yes — UNDROP TABLE <name> restores a dropped object within its retention window. The grants come back too. After the window expires the object is permanently gone.

Does Time Travel work on external tables or transient tables? External tables have no Time Travel (the data lives in object storage, not in Snowflake). Transient tables support Time Travel up to 1 day, never more, and have no Fail-safe period after that. Permanent tables are the only objects with full Time Travel + Fail-safe.

How do I restore a table to a previous state without losing the current data? Clone the table at the target point in time (CREATE TABLE backup_old CLONE my_table AT (OFFSET => -3600);), then swap (ALTER TABLE my_table SWAP WITH backup_old). The swap is atomic; the clone is metadata-only and effectively free until divergence. This is safer than INSERT OVERWRITE because both copies remain queryable until you drop the old one.

Wrapping up

Snowflake Time Travel is one of the most useful warehouse safety features because it solves real operational problems with very little setup. The mistake is assuming the headline feature description is enough.

The details matter:

• default retention is often only 1 day
• longer retention costs money
• cloning is frequently safer than restoring in place
• Time Travel is a recovery tool, not a whole backup strategy

If your team uses Snowflake heavily, it is worth reviewing retention settings now instead of waiting for the first time you need them. Recovery is also where access control matters most: the role doing the restore needs OWNERSHIP or ALTER on the target, which is the kind of decision the RBAC and secure views guide covers, and the post-recovery table inherits any dynamic table downstream you had defined against it. And if you’re evaluating the day-to-day interface for working with Snowflake while debugging issues like these, start with our guide to the best Snowflake GUI tools.