How to Use Snowflake Time Travel to Recover Deleted Data
Snowflake Time Travel lets you query a table as it existed at an earlier point — up to 1 day on Standard edition and up to 90 days on Enterprise — using AT or BEFORE clauses, and restore dropped objects with UNDROP. No backup infrastructure required.
The recovery workflow depends entirely on what was lost. A bad DELETE is a query problem. A DROP TABLE is an object problem. A TRUNCATE is somewhere in between and catches people out.
What are the retention limits?
| Edition | Maximum Time Travel |
|---|---|
| Standard | 1 day |
| Enterprise | 90 days |
| Business Critical | 90 days |
Even on Enterprise, retention is set per object with DATA_RETENTION_TIME_IN_DAYS, and the default is 1 day. If nobody changed it, you have one day, not 90. Check with:
SHOW PARAMETERS IN TABLE my_db.my_schema.my_table;
A one-day window is not a backup strategy. For any table that matters, set retention explicitly and remember that extended retention adds storage cost — Snowflake charges for the historical data it keeps. The same credit logic applies to compute, which is why warehouse sizing and retention are usually tuned together.
How do you undo a bad DELETE or UPDATE?
Query the table as it was before the change, then insert the missing rows back.
- Find when the damage happened:
SELECT * FROM my_table AT(OFFSET => -300);— five minutes ago. - Or pin to a timestamp:
SELECT * FROM my_table AT(TIMESTAMP => '2026-10-27 09:15:00'::timestamp); - Compare against current state to find what is missing.
- Recover with
INSERT INTO my_table SELECT * FROM my_table BEFORE(STATEMENT => query_id_string) WHERE id NOT IN (SELECT id FROM my_table);
The BEFORE(STATEMENT => the query id) form is the precise one — it uses the offending statement’s own ID as the boundary, so you do not have to guess a timestamp. Get the query ID from Query History.
Caveat: recovered rows need the right updated_at and any audit columns. A raw re-insert brings back the old values, which can confuse downstream incremental models that filter on timestamps, or the drift alerts monitoring them.
How do you restore a dropped table?
UNDROP TABLE my_table; brings it back with grants, comments, and constraints intact. It restores to the schema it was dropped from.
Sequence matters if the name was reused:
- Rename the new table that took the old name.
- Run
UNDROP TABLE original_name;. - Compare the two and decide which to keep.
You can also UNDROP SCHEMA and UNDROP DATABASE, same rules.
What Time Travel cannot do
This is the part worth internalising before an incident:
- Cannot recover a TRUNCATE’s data via UNDROP.
TRUNCATEkeeps the table but removes rows, so you need the query-based recovery. And in Snowflake,TRUNCATEdoes not create a new table version the way some databases do — plan for the INSERT…SELECT path. - Cannot recover past the retention window. Once a table’s historical data ages out, it is gone.
- Does not protect against access. Time Travel restores state, not permissions. If someone was granted read access and exfiltrated data, UNDROP does nothing.
- Does not survive a dropped database with retention set to 0. Check for
DATA_RETENTION_TIME_IN_DAYS = 0on any object; that disables Time Travel entirely. - Cannot recover external table contents. Time Travel applies to Snowflake-managed storage, not the files in your S3 bucket.
How do you set this up before you need it
Three actions, five minutes:
ALTER TABLE important_table SET DATA_RETENTION_TIME_IN_DAYS = 90;on any table where loss would matter (Enterprise required).- Set account-level default retention so new tables inherit it:
ALTER ACCOUNT SET DATA_RETENTION_TIME_IN_DAYS = 7; - Set a failsafe policy expectation. FailSafe covers 7 additional days after Time Travel expires, but only for recovery by Snowflake Support — it is not self-service and is not a substitute for Time Travel.
Also worth knowing: cloning uses Time Travel too. CREATE TABLE copy CLONE original AT(OFFSET => -3600); gives you a free snapshot of an hour ago without copying data, which is the cheapest way to snapshot before a risky migration.
When should you reach for Time Travel first
If you have been leaning on cheaper rewind elsewhere — Neon branching, Databricks snapshots, or DuckDB / MotherDuck, where there is usually no server-side history at all — Snowflake’s window is the one you have to set yourself. Any query-level accident in the last day, and any dropped object in the last day. Beyond that, weigh it against your actual backup process — and if you do not have one, that is the conversation to have today, not during an incident. If you would rather ask the data in plain English than write the AT clause from memory, MindsDB is the gentler entry point.
Key Takeaways
- Snowflake Time Travel gives 1 day on Standard and up to 90 days on Enterprise, but defaults to 1 day per object unless changed.
- Use
BEFORE(STATEMENT => the query id)to recover from a specific bad statement precisely. UNDROP TABLErestores dropped tables with grants intact; rename any name-collision table first.TRUNCATErequires the INSERT…SELECT recovery path, not UNDROP.- Zero-day retention disables Time Travel entirely — check the parameter before assuming you are covered.
