Most production data loss isn’t caused by platform failures or hardware. It comes from a pipeline that silently overwrites a table with bad data, an engineer who runs a DELETE in the wrong environment, or a CREATE OR REPLACE that wipes out three months of history on a Friday afternoon. By the time the problem is noticed, the stakeholders are already asking questions, and the clock is running.
Snowflake Time Travel exists for exactly this scenario. It gives data teams a configurable window to query historical data, clone past object states, and restore dropped tables without involving Snowflake support or rerunning an upstream pipeline. Most teams know the feature exists. Far fewer have designed their retention policy around it, thought through what it costs at scale, or tested their recovery steps before something breaks.
In this article, we’ll cover how Snowflake Time Travel works mechanically, how to configure retention by table criticality, how to run a recovery the right way, and how to keep storage costs from compounding.
Key Takeaways Snowflake Time Travel gives data teams a self-service window to query past data, clone object states, and restore dropped tables. No support ticket or upstream reload needed.Standard Edition accounts get a 1-day retention window. Enterprise Edition and higher can extend that to 90 days for permanent objects. Fail-safe is not user-accessible. It activates after Time Travel expires and recovery requires a Snowflake support ticket, not a self-service command. CREATE OR REPLACE creates a new table version, which can break Time Travel recovery flows if teams do not account for it. Time Travel storage costs are driven by changed data volume multiplied by the retention period, not by total table size. Designing retention policy by table criticality rather than applying blanket 90-day retention across all objects keeps costs manageable .
Need Help Setting Up Snowflake Time Travel for Production? Kanerika’s certified team will assess your current setup and tell you exactly what needs fixing.
Book a Meeting
What Is Snowflake Time Travel Snowflake Time Travel is a built-in feature that lets users access historical data within a configurable retention window. When rows are updated, deleted, truncated, or an object is dropped, Snowflake preserves the prior state of the data for the duration of the retention period. During that window, engineers can query old data , clone an object as it existed at a past point, or restore a dropped table, schema, or database.
Three core actions are available within the retention window:
Query past data: Use SELECT with AT or BEFORE clauses to read rows as they existed at a specific timestamp, offset, or before a specific statement ran.Clone a past state: Use CREATE TABLE … CLONE with AT or BEFORE to create a point-in-time copy of a table, schema, or database.Restore dropped objects: Use UNDROP to recover a table, schema, or database dropped within the retention window.
Time Travel is often confused with adjacent Snowflake features . It is not a backup system, not Change Data Capture , not Streams, and not a long-term archival solution. Teams that expect it to replace a backup strategy will find gaps. It is a short-to-medium-term recovery and auditing tool for data that Snowflake manages directly.
One distinction worth being precise about: Snowflake Streams track ongoing row-level changes for CDC pipelines and feed downstream transforms in real time. Time Travel lets teams query or restore data at a past point after the fact. Streams capture what changed as it changes. Time Travel recovers what existed before a change happened.
How Snowflake Time Travel Works Snowflake stores data in immutable micro-partitions . When a DML operation changes data, Snowflake writes new partitions and flags the old ones as historical rather than overwriting them. Those historical partitions are what Time Travel queries against, and they stay accessible for the duration of the retention window. Once the window closes, data moves into Fail-safe and user-initiated operations against it stop. Teams running Microsoft Fabric Lakehouse time travel will find the concept similar, though the syntax and retention model differ.
One production note worth flagging. A long-running Time Travel query delays movement of that data into Fail-safe. Snowflake waits for active queries to finish before advancing data through the lifecycle, which can push storage costs up when long queries reference old partitions on tables with extended retention.
Setting the Right Retention Period in Snowflake Retention limits depend on Snowflake edition and table type. Standard Edition caps all objects at 1 day. Enterprise Edition and above can configure permanent objects with retention periods from 0 to 90 days .
Object Type Standard Edition Enterprise Edition+ Temporary table 0 or 1 day 0 or 1 day Transient table 0 or 1 day 0 or 1 day Permanent table 0 or 1 day 0 to 90 days Schema 0 or 1 day 0 to 90 days Database 0 or 1 day 0 to 90 days
Temporary and transient tables are capped at 1 day regardless of edition. This is intentional, as these object types are designed for short-lived or scratchpad workloads where long retention would generate unnecessary cost. Teams building continuous pipelines with auto-refreshing results should also review Snowflake Dynamic Tables , which handle incremental refresh differently and carry their own retention considerations.
Inheritance behavior is where teams often get caught off guard. If a parent database has a 30-day retention period and a child table has an explicitly set 7-day period, dropping the database overrides the child’s setting. The child’s explicitly set value is not honored once the parent is dropped. To preserve distinct retention settings for child objects, drop them explicitly before dropping the parent database or schema.
Snowflake Time Travel Commands and Query Examples The AT and BEFORE clauses are the primary SQL interface for Time Travel. Both work in SELECT statements and CREATE … CLONE commands, and accept three parameter types depending on how precisely the target moment can be identified.
Each parameter suits a different recovery scenario:
TIMESTAMP: Use when the team knows the exact moment before a problem occurred.OFFSET: Use for quick lookbacks where the window is known approximately, such as “two hours ago.”STATEMENT: The most precise option. Use when a specific bad query can be identified in query history. The statement ID is available in the Query History tab in the Snowflake UI or through the QUERY_HISTORY table function.
-- Query data as of a specific timestamp
SELECT * FROM orders AT(TIMESTAMP => '2026-06-14 14:00:00'::timestamp_tz);
-- Query data from 2 hours ago using offset
SELECT * FROM orders AT(OFFSET => -60*120);
-- Query data as it existed before a specific statement ran
SELECT * FROM orders BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');
Timezone Errors Are Common The TIMESTAMP value passed to AT or BEFORE must include timezone information. Without it, Snowflake interprets the timestamp in the session timezone, which varies by user and connection context. If your session is set to UTC but the data was written in US/Eastern, a timestamp without timezone information shifts the query window by several hours and returns the wrong historical state, or nothing at all. Always cast timestamps with ::timestamp_tz and confirm the session timezone before running recovery queries on production tables.
Always Validate Row Counts Before Writing Back Before inserting or swapping restored data back into any production table, compare row counts against the expected pre-change state. A mismatch at this stage usually means the timestamp or offset landed in the wrong window: either before a batch load completed or after a partial transaction committed. Spot-check a sample of rows for known values as a secondary check. Skipping this step and writing back an incorrect state compounds the original problem rather than fixing it.
Out-of-Window Queries Return an Error, Not an Empty Result If the timestamp, offset, or statement ID falls outside the active retention window, Snowflake does not return an empty result set. It returns an error. This catches teams off guard during incidents when they assume a failed query means no data exists. The actual cause is almost always that the retention window has closed. Check the table’s DATA_RETENTION_TIME_IN_DAYS setting and compare the target timestamp against DROPPED_ON or the current time before concluding data is unrecoverable.
Snowflake Architecture: Storage, Compute, and Platform Model Explained How micro-partitions, virtual warehouses, and Snowflake’s separation of storage and compute work together, and what that means for data teams designing for scale.
Read Blog
How to Restore Dropped Tables, Schemas, and Databases Recovery through Time Travel works best when teams follow a sequenced approach rather than running UNDROP immediately. The order matters because rushing to restore a dropped object without validating state or checking for name conflicts will generate further errors.
The correct recovery sequence:
Stop any active write jobs that might overwrite the state further Identify the dropped object using SHOW TABLES HISTORY, SHOW SCHEMAS HISTORY, or SHOW DATABASES HISTORY Confirm the retention window is still open by checking the DROPPED_ON timestamp against the current time Clone the object at a pre-drop timestamp to a staging name before restoring, giving a validation copy Validate row counts and spot-check data quality on the clone UNDROP the original object or swap the validated clone into the production path Review storage costs after the incident and adjust retention policy if needed
UNDROP restores to the state immediately before the DROP ran, not a specific point in time. For point-in-time restoration, use CREATE … CLONE with AT or BEFORE instead. One failure mode to know: if an object with the same name already exists, UNDROP fails. Rename the existing object first, then run UNDROP.
The DDL Mistake That Kills Time Travel Recovery CREATE OR REPLACE is one of the most common Time Travel failure modes in production environments. When a CREATE OR REPLACE TABLE command runs, Snowflake creates a new table object. The previous version of the table becomes a separate, dropped version. Time Travel history on the new table starts fresh from the moment of creation.
Command Time Travel Behavior INSERT / UPDATE / DELETE Historical rows preserved within retention window TRUNCATE Data removed but object intact; prior rows still accessible DROP TABLE Object dropped; accessible via UNDROP or cloning within retention CREATE OR REPLACE TABLE New table object created; old version treated as a separate dropped object SWAP Both objects retain their individual Time Travel histories
This creates a recovery problem. CREATE OR REPLACE is a common pattern in ELT pipelines that reload staging tables, and each replacement creates a new object with its own Time Travel clock starting at zero. Querying history across the replacement boundary requires identifying each dropped version via SHOW TABLES HISTORY and working through each separately.
Clone Before Running Destructive DDL Take an explicit pre-change clone before running CREATE OR REPLACE on any table that carries production history. The clone preserves the full historical state at that moment and costs nothing in storage until it diverges. If the replacement goes wrong, you swap back without touching Time Travel at all, the clone is already there. This pairs naturally with zero-copy cloning as part of a broader Snowflake data protection approach.
Use TRUNCATE + INSERT Instead of CREATE OR REPLACE TRUNCATE clears all rows but leaves the table object intact, which means the Time Travel clock keeps running from the original creation point. Prior rows stay accessible within the retention window. CREATE OR REPLACE creates a brand new object with a fresh Time Travel clock, everything before the replace is gone. For any table where historical recovery matters, TRUNCATE + INSERT is the safer default for routine refreshes. Teams managing Snowflake data warehouse environments at scale benefit most from this pattern on high-churn tables.
Log Query IDs from Every Load Operation Every pipeline run that writes to a production table should record its Snowflake query ID before exiting. STATEMENT-based Time Travel is the most precise recovery method available, but it requires knowing that query ID. Without it, engineers are left searching Query History manually under incident pressure, which is slow and error-prone. Logging query IDs as part of normal pipeline operations costs nothing and makes STATEMENT-based recovery straightforward when it matters. It is also a core part of any data governance framework for Snowflake environments handling regulated or sensitive data.
Time Travel vs Fail-Safe vs Zero-Copy Cloning These three features are often mentioned together but have different purposes with different access models.
Feature Controlled by Window User Access Purpose Time Travel User 0 to 90 days Full: query, clone, UNDROP Self-service recovery and auditing Fail-safe Snowflake 7 days post-Time Travel None, support ticket required Last-resort catastrophic recovery Zero-copy cloning User On demand Full Branching, testing, migration snapshots
Fail-safe activates automatically after Time Travel expires and lasts 7 days, but it is Snowflake-controlled. Users cannot query or restore from it directly. Recovery requires a support ticket and is best-effort, not guaranteed speed. Teams that plan around Fail-safe as a recovery fallback will be disappointed during an actual incident.
Zero-copy cloning paired with Time Travel is the cleanest rollback pattern available. A CREATE TABLE … CLONE with AT or BEFORE creates a logical duplicate of any object at a past point. Because Snowflake references existing micro-partitions rather than copying data, the clone is instant and shares storage with the source until it diverges, making it effective for pre-migration snapshots, rollback staging, and test environments without the cost of a full copy.
What Snowflake Time Travel Costs Time Travel storage is billed on changed data, not the full table. The cost is changed data volume × retention period × daily storage rate. High-churn tables (frequent updates, large deletes, or daily truncations) generate the most Time Travel storage even at short retention periods. A table refreshed daily with 30-day retention could retain up to 30 versions of its data in the Time Travel layer.
Snowflake bills Time Travel and Fail-safe storage at the same daily rate as active storage , billed per terabyte. That makes extended retention on high-churn tables the primary cost lever to watch.
The TABLE_STORAGE_METRICS view breaks this down per table:
SELECT
table_name,
active_bytes,
time_travel_bytes,
failsafe_bytes,
retained_for_clone_bytes
FROM information_schema.table_storage_metrics
ORDER BY time_travel_bytes DESC;Run this monthly. Tables with high time_travel_bytes are retention review candidates. The two most common culprits are high-churn staging tables with long retention and large dropped tables whose data hasn’t moved to Fail-safe yet. Understanding Time Travel costs sits alongside a broader picture of Snowflake’s storage billing model .
Designing a Retention Policy for Production Environments Applying the same retention period across every object in a Snowflake account is the most common retention policy mistake. Most teams default to whatever the account-level setting is without distinguishing between a production table where data loss would be severe and a transient staging table that gets dropped and recreated daily.
A table criticality matrix provides a cleaner framework.
Table Category Retention Recommendation Rationale Production critical 7 to 30 days High business impact from accidental loss; recovery window needed Regulated / compliance Per legal requirement Audit trail requirements may dictate minimum; check with legal teamRecoverable from source 1 day Source data can be reloaded; extended retention adds cost without value Transient / staging 0 or 1 day Short-lived by design; Time Travel rarely useful here Sandbox / dev 0 or 1 day Low stakes; cost savings outweigh recovery need
90-day retention makes sense only for tables where rebuild cost or data loss impact is severe and upstream recovery isn’t an option. For most platforms, 7 to 14 days covers production objects and 0 to 1 day handles everything else. Set DATA_RETENTION_TIME_IN_DAYS at the table level for the most control, and use MIN_DATA_RETENTION_TIME_IN_DAYS at account level to enforce a floor.
Governance and Access Controls for Time Travel Data Time Travel introduces a governance consideration most teams miss . Historical queries via AT or BEFORE still apply the table’s current masking and row access policies, not the policies that were in place at the historical timestamp. A column masked six months ago under a different policy now returns historical rows under today’s rules. Teams with regulated data, PII, or financial records should audit their masking configurations with Time Travel access patterns specifically in mind. For teams also using Snowflake Cortex for AI-driven data processing, the same masking rules apply to any historical data queried through Time Travel within Cortex workloads.
Ownership of retention settings is the second governance gap. Without clear policy, uncontrolled retention periods lead to:
Inconsistent windows across tables, with some overprotected and others with no recovery path at all Unexpected cost exposure from staging tables inheriting long retention from parent databases Gaps in the recovery plan that only surface during an actual incident
Assigning authority to change DATA_RETENTION_TIME_IN_DAYS to a defined owner, whether data platform or data governance teams, prevents all three.
For organizations building out a full governance layer, Time Travel retention, access controls, and recovery ownership sit naturally alongside data classification, lineage, and compliance audit requirements .
Snowflake Time Travel in ETL, Migration, and BI Workflows Time Travel is most often discussed as a recovery tool, but its practical value in day-to-day data engineering is broader. Three workflows where it regularly earns its keep:
ETL rollback: When a load job writes incorrect data, transforms merge rows incorrectly, or a late-arriving record corrupts a result set, engineers can query the pre-load state and use it as the correction baseline without re-running the full upstream pipeline.Migration validation: Migration teams can query the source table at a known timestamp before cutover and compare row counts and spot samples against the target, giving a clean validation framework without freezing source data or taking a manual snapshot.BI audit: If a dashboard value shifts unexpectedly between two dates, a SELECT with AT against the underlying table confirms whether the change came from the data or from a model update, without requiring access to the full pipeline history.
6 Best Practices for Snowflake Time Travel Most data teams know Snowflake Time Travel exists. Fewer have thought through what happens when they actually need it under pressure. The six practices below are the difference between a 30-minute fix and a four-hour incident. None of them require extra tooling. They require decisions made before something goes wrong.
1. Keep at least 1 day of retention on production objects Setting DATA_RETENTION_TIME_IN_DAYS to 0 on any table that gets dropped removes the ability to UNDROP it entirely. The storage cost of 1 day is low. The cost of losing a production table with no recovery path is not. Only set to 0 on objects that are safely and quickly rebuildable from an upstream source.
2. Monitor TIME_TRAVEL_BYTES and FAILSAFE_BYTES monthly Use the TABLE_STORAGE_METRICS view in INFORMATION_SCHEMA. Unexpected spikes are the earliest signal that retention settings need review before costs compound.
3. Test restore procedures at least quarterly Run UNDROP or a clone-and-swap in a staging environment before an incident happens. Teams that rehearse recovery know the exact commands, the name conflict behavior, and the validation steps. That preparation cuts incident response time when the real incident happens.
4. Set retention at the table level, not the account level Account-level defaults apply broadly and create two problems: critical tables may be under-protected, and low-value tables may carry unnecessarily long retention. Use ALTER TABLE … SET DATA_RETENTION_TIME_IN_DAYS per object to match retention to actual recovery requirements.
5. Log query IDs from every production load job STATEMENT-based Time Travel is the most precise recovery method, but it requires the query ID of the bad operation. If load jobs don’t log their query IDs, engineers are left hunting through Query History under pressure during an incident.
6. Set transient tables to 0-day retention by default Staging tables, intermediate transforms, and scratch tables accumulate Time Travel storage fast. Unless a staging table carries data that can’t be rebuilt from source, there is no reason to retain its history. Setting transient object retention to 0 keeps costs predictable without affecting production reliability.
Is Your Snowflake Environment Ready Before an Incident Hits? Kanerika’s certified Snowflake team assesses your setup and fixes the gaps.
Book a Meeting
How Kanerika Designs Snowflake Time Travel for Production Most teams that run into Time Travel problems in production share the same gaps: retention policies set at account level without distinguishing table criticality, no documented recovery sequence until an incident forces one, and storage costs that compound unnoticed until a monthly bill surfaces the problem. Kanerika resolves these gaps as part of every Snowflake engagement, with retention design, recovery runbooks, and governance configuration included from day one.
As a Snowflake Select Tier Partner, Kanerika has delivered Snowflake-based platforms across financial services, healthcare, logistics, and manufacturing. What that looks like in practice:
Retention policy design : table-criticality-based retention mapped to actual recovery requirements, not account-level defaultsRecovery planning : documented runbooks covering UNDROP, clone-and-swap, and STATEMENT-based recovery before an incident forces the team to figure it out under pressureGovernance via KANGovern : data classification, RBAC tied to role hierarchies, masking policy governance, and compliance audit readiness applied directly to the Snowflake environmentStorage cost monitoring : TABLE_STORAGE_METRICS review as part of ongoing platform health, not a one-time setupEngagements start with an assessment of what is already in place: retention settings, recovery documentation, storage cost concentration, and access control alignment. That assessment surfaces the gaps before an incident makes them urgent.
Case Study: How Kanerika Cut Manual Reconciliation Using Snowflake Kanerika’s Snowflake delivery work regularly involves data teams where the absence of a proper recovery framework made incidents harder than they needed to be. One such engagement involved a distributed operations company struggling with data consistency across locations. Time Travel and a well-structured Snowflake environment helped resolve the underlying problem.
Challenge
Solution Migrated the data platform to Snowflake, consolidating distributed data sources into a unified environment with consistent retention policy across all production tablesImplemented Time Travel with table-criticality-based retention: production tables set to 14 days, staging and transient tables kept at 1 day to control storage costs Established a documented recovery runbook so engineering teams could use BEFORE(STATEMENT) cloning to isolate bad pipeline runs without touching upstream source systems Established monthly storage monitoring to catch retention drift before it compounded into unexpected bills
Results 60% reduction in manual data reconciliation 40% faster data reporting cycles 3x quicker analytics delivery across distributed operations
Wrapping Up Snowflake Time Travel is one of the most operationally useful features in the platform. But most of its value is realized before an incident, not during one. Teams that define retention policy by table criticality, monitor storage costs with TABLE_STORAGE_METRICS, document their recovery sequence, and understand the limits of Fail-safe will recover faster and spend less than those who treat Time Travel as a safety net that will just work when needed. The feature is simple. The operating model around it determines whether recovery is a 30-minute fix or a four-hour scramble.
Ready to Build a Snowflake Platform That Holds Up in Production? From implementation to ongoing governance, Kanerika’s team has you covered.
Book a Meeting
FAQs Snowflake Time Travel lets users access historical data that has been changed or deleted within a configurable retention window. Snowflake stores prior row states as immutable micro-partitions when data is modified or objects are dropped. During the retention period, teams can query old data with AT or BEFORE clauses, clone objects at past timestamps, or restore dropped objects using UNDROP.
How far back can Snowflake Time Travel go? The maximum window is 90 days for permanent objects in Snowflake Enterprise Edition and higher. Standard Edition accounts are limited to 1 day. Temporary and transient tables are capped at 1 day regardless of edition. The default for all accounts is 1 day unless explicitly changed at the account, database, schema, or table level.
How do I check the Time Travel retention setting for a table? Use SHOW TABLES and check the retention_time column in the output. To filter for a specific table, run SHOW TABLES and query the result with a filter on the name column. To check tables where Time Travel has been disabled, filter for retention_time = 0 in the SHOW SCHEMAS or SHOW DATABASES output.
How do I recover a dropped table in Snowflake? Run SHOW TABLES HISTORY to confirm the table is still within its retention window. If it appears in the output and has not expired, run UNDROP TABLE [table_name]. If an object with the same name already exists, rename it first, then run UNDROP. UNDROP restores the table to the state it was in immediately before the DROP command ran.
Can Snowflake recover deleted rows if I don't know the exact deletion time? Yes. Use the STATEMENT parameter with a query ID from the DELETE operation. The query ID is available in the Snowflake Query History UI or through the QUERY_HISTORY table function. Running SELECT … BEFORE(STATEMENT => ‘[query_id]’) returns the data as it existed before that specific statement ran, without requiring a precise timestamp.
Does CREATE OR REPLACE affect Snowflake Time Travel? Yes. CREATE OR REPLACE creates a new table object, which starts a fresh Time Travel clock. The old table version becomes a separate dropped object with its own retention window. Teams that use CREATE OR REPLACE regularly in their pipelines may need to query SHOW TABLES HISTORY to locate the specific dropped version they want to recover, then use a clone rather than UNDROP.
What is the difference between Snowflake Time Travel and Fail-safe? Time Travel is user-controlled. It runs for the configured retention period and allows direct querying, cloning, and UNDROP operations without Snowflake support. Fail-safe activates automatically after Time Travel expires for permanent objects and lasts 7 days. It is Snowflake-controlled and not directly accessible by users. Recovery from Fail-safe requires contacting Snowflake support and is not guaranteed at a specific speed.
How much does Snowflake Time Travel cost? Time Travel storage is charged based on the volume of changed data multiplied by the retention period, not the full table size. Dropped or truncated tables may retain a larger portion of the original data volume. Fail-safe storage is billed at the same rate for the 7-day period after Time Travel expires. The TABLE_STORAGE_METRICS view in INFORMATION_SCHEMA shows a per-table breakdown of active bytes, Time Travel bytes, Fail-safe bytes, and retained clone bytes.
