When a buggy pipeline wipes your production data, you have minutes to act — not hours. Learn how Snowflake's Time Travel and Fail-Safe features let you query, restore, and clone data from any point in history, and how to build pipelines that make recovery fast and reliable.
It's a Tuesday morning, and your phone buzzes with a Slack message from your analytics lead: "Something's wrong with the revenue numbers — they're all showing zero for last quarter." You check the pipeline logs. An Airflow DAG ran overnight, and a faulty dbt model executed a DELETE statement against the wrong table. Forty-eight hours of carefully cleaned transaction data: gone. Your stomach drops.
This scenario isn't hypothetical — it happens to production data teams regularly. Bugs slip through code review. A transformation logic error turns UPDATE into a silent catastrophe. A misconfigured pipeline truncates a table that feeds three downstream dashboards. Without a recovery mechanism, you're either restoring from a backup (if one exists), or manually reconstructing data from source systems (if that's even possible). Either way, you're losing hours — maybe days.
Snowflake was designed with exactly this problem in mind. Its Time Travel and Fail-Safe features give you a built-in safety net that lets you query, restore, or clone data as it existed at any point in recent history — without managing snapshots yourself, without spinning up secondary storage systems, and without writing a single line of backup infrastructure code. By the end of this lesson, you'll understand how both features work under the hood, how to use them confidently during a production incident, and how to design your pipelines so recovery is fast and reliable.
What you'll learn:
AT and BEFORE syntaxYou should be comfortable writing basic SQL and have a working Snowflake account. Familiarity with the general shape of a modern data pipeline will help — if you haven't already explored how Snowflake fits into a broader architecture, check out The Modern Data Stack Explained: Tools and Architecture for context. You don't need to be a Snowflake expert.
Before we dive into commands, let's build the right mental model. Time Travel is not a traditional backup. You're not scheduling nightly snapshots to an S3 bucket and praying you can restore them. Snowflake's storage architecture handles this automatically, and understanding why requires a brief detour into how Snowflake stores data.
Snowflake uses micro-partitions — small, immutable chunks of compressed columnar data — as its fundamental storage unit. When you run an UPDATE or DELETE, Snowflake doesn't overwrite those micro-partitions in place. Instead, it marks the old partitions as no longer "active" and writes new ones. Crucially, the old partitions aren't immediately deleted. They're retained for your Time Travel window.
This means Time Travel is essentially free in terms of compute — Snowflake is already creating this historical record as a side effect of how it manages writes. What you're paying for is the storage of those retained micro-partitions, which we'll discuss when we cover cost considerations.
Key insight
Time Travel doesn't run a background job or require configuration to activate — it's on by default for all tables, schemas, and databases. The default retention period is 1 day, but you can extend it up to 90 days on Enterprise Edition and above.
Time Travel covers three broad scenarios:
The most immediate use of Time Travel is simply looking at what your data looked like before something went wrong. You do this with the AT or BEFORE clause appended to any SELECT statement.
Let's say you have a orders table and you want to see what it looked like yesterday at 9 AM UTC, before an overnight pipeline ran:
SELECT *
FROM orders
AT (TIMESTAMP => '2024-11-12 09:00:00'::TIMESTAMP_TZ);
You can also reference a specific query ID — useful when you know exactly which query caused the damage:
-- First, find the query that caused the problem
-- You can find query IDs in Query History in the Snowflake UI or via QUERY_HISTORY()
SELECT *
FROM orders
BEFORE (STATEMENT => '01b3f7a2-0000-3a5e-0000-000194fab2e6');
The BEFORE clause returns the state of the table before that specific statement executed. This is incredibly precise — you're not estimating a time window, you're referencing the exact transaction boundary.
You can also use a relative offset in seconds from the current time:
-- See what orders looked like 3600 seconds (1 hour) ago
SELECT *
FROM orders
AT (OFFSET => -3600);
Tip
When responding to a production incident, your first move should almost always be a Time Travel SELECT — not an immediate restore. Understand what changed before you restore, so you're not overwriting data that was partially correct.
One of the most powerful diagnostic patterns is comparing the historical version to the current version. Suppose your pipeline updated order_status incorrectly for a batch of orders:
-- Find rows that exist now but didn't exist 2 hours ago
SELECT current.*
FROM orders AS current
LEFT JOIN (
SELECT order_id
FROM orders AT (OFFSET => -7200)
) AS historical ON current.order_id = historical.order_id
WHERE historical.order_id IS NULL;
-- Find rows where a specific column changed
SELECT
current.order_id,
historical.order_status AS status_before,
current.order_status AS status_after
FROM orders AS current
INNER JOIN (
SELECT order_id, order_status
FROM orders AT (OFFSET => -7200)
) AS historical ON current.order_id = historical.order_id
WHERE current.order_status <> historical.order_status;
This gives you a precise diff — a list of exactly which rows changed and how. That's your evidence for what needs to be fixed, and it often informs whether you need a full restore or a targeted patch.
Once you know what happened, it's time to fix it. Snowflake gives you two main paths: UNDROP for recovering dropped objects, and cloning for restoring modified data.
If someone ran DROP TABLE orders — either by accident or because a pipeline cleanup step was misconfigured — you can recover it instantly:
UNDROP TABLE orders;
It's genuinely that simple. The table comes back with all its data, its column definitions, its grants, its clustering configuration — everything. The same works for schemas and databases:
UNDROP SCHEMA analytics;
UNDROP DATABASE production_warehouse;
Warning
If you've already created a new object with the same name after the drop, UNDROP won't overwrite it. You'll need to rename or drop the new object first, then run UNDROP. Snowflake is careful not to silently destroy existing objects.
There's a subtlety here: UNDROP restores the most recently dropped version of that object. If you dropped a table, recreated it, then dropped it again, UNDROP restores the second drop (the more recent one). The first version is also recoverable — but you'd need to use the clone-from-history approach described below.
For cases where a table still exists but its data was corrupted or modified incorrectly, cloning is your best tool. A zero-copy clone in Snowflake creates a new table that shares the underlying micro-partitions of the original — no data is physically copied until you start writing to the clone. This means creating a clone is nearly instant, even for tables with billions of rows.
Combine that with Time Travel, and you can create a snapshot of any table as it appeared at any past point:
-- Clone the orders table as it appeared before a specific statement ran
CREATE TABLE orders_recovery_20241112
CLONE orders
BEFORE (STATEMENT => '01b3f7a2-0000-3a5e-0000-000194fab2e6');
Now you have a recovery table you can inspect, validate, and use as the basis for a fix. You can restore fully:
-- Option 1: Rename and swap
ALTER TABLE orders RENAME TO orders_corrupted_20241112;
ALTER TABLE orders_recovery_20241112 RENAME TO orders;
-- Option 2: Surgical patch — copy back only the rows that were wrongly affected
INSERT OVERWRITE INTO orders
SELECT * FROM orders_recovery_20241112
WHERE order_id IN (SELECT order_id FROM affected_orders_list);
The second option is usually safer. A full swap restores all the data to its historical state, which may undo legitimate changes that happened in parallel with the corrupted writes. Always think carefully about the scope of your restore.
Tip
Zero-copy clones are not just for recovery — they're incredibly useful for testing pipeline changes against production data without the risk or cost of duplicating your dataset. This pattern is worth building into your standard development workflow.
The default retention period is 1 day. For transient tables (used for temporary calculations) or staging tables that are recreated every pipeline run, 1 day is fine — or you might set it to 0 to save on storage costs. For core business tables, you'll want longer.
You can set retention at the database, schema, or table level:
-- Set a 7-day retention period for your production schema
ALTER SCHEMA production SET DATA_RETENTION_TIME_IN_DAYS = 7;
-- Override to 30 days for a specific high-value table
ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 30;
-- Set to 0 for staging tables that don't need recovery
ALTER TABLE stg_raw_events SET DATA_RETENTION_TIME_IN_DAYS = 0;
Warning
Longer retention periods mean more storage costs, because Snowflake retains the historical micro-partitions for the full window. For tables with frequent writes (high-churn tables), this can become significant. You don't need 90-day retention everywhere — be strategic. Use longer windows for your gold-layer business tables and shorter windows for staging and intermediate models.
For teams using dbt to structure their transformations, a common pattern is to configure retention directly in the dbt model properties:
# in your dbt schema.yml
models:
- name: orders
config:
snowflake_warehouse: compute_wh
transient: false
post-hook: "ALTER TABLE {{ this }} SET DATA_RETENTION_TIME_IN_DAYS = 14"
This ensures your production models always have appropriate retention without requiring manual database configuration.
Time Travel is what you use yourself, in the console, during an incident. Fail-Safe is different — it's Snowflake's own internal protection layer that kicks in after your Time Travel window expires.
Here's how it works: after your Time Travel window closes, Snowflake retains the historical micro-partitions for an additional 7 days in a Fail-Safe state. During this window, Snowflake's support engineering team can potentially recover data on your behalf — but you cannot access it yourself through SQL. There's no AT (TIMESTAMP => ...) syntax for Fail-Safe data. You open a support ticket, and Snowflake works with you to retrieve it.
Think of the two layers like this:
Note
Fail-Safe is available for permanent tables only. Transient and temporary tables have no Fail-Safe protection at all. If you use transient tables for staging (a common pattern to reduce storage costs), be aware that they have neither extended Time Travel nor Fail-Safe. This is a reasonable trade-off for truly ephemeral data, but know what you're giving up.
The Fail-Safe window is fixed — you can't extend it or configure it. It's 7 days, period. And Fail-Safe data contributes to your storage costs, just like Time Travel data does, but without you having to opt in. It's always there, always accumulating for your permanent tables.
Understanding Time Travel and Fail-Safe is one thing. Designing your pipelines so that recovery is fast, clean, and low-stress is another. Here are the patterns that matter most in production.
When a pipeline runs, use QUERY_TAG to annotate your sessions with metadata that helps you identify which DAG run, which task, and which execution caused a problem:
-- Set this at the start of each pipeline session
ALTER SESSION SET QUERY_TAG = '{"pipeline": "orders_daily", "run_id": "2024-11-12T02:00:00Z", "dag_id": "orders_transform"}';
Now, when something goes wrong, you can query QUERY_HISTORY() to find the exact statement ID for the problematic run:
SELECT
query_id,
query_text,
start_time,
query_tag
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())
WHERE query_tag LIKE '%orders_daily%'
ORDER BY start_time DESC;
With that query_id, your Time Travel restoration becomes surgical and exact.
For complex, multi-step transformations — especially when running incremental dbt models that process late-arriving data — consider creating a clone of your target table before the transformation runs. Since clones are zero-copy, this is essentially free in terms of time and data storage (until the clone diverges from the original):
-- Run this as a pre-hook before a risky transformation
CREATE OR REPLACE TABLE orders_pre_transform_backup
CLONE orders;
-- Run your transformation
-- If it fails or produces bad data, simply swap back
ALTER TABLE orders RENAME TO orders_bad_transform;
ALTER TABLE orders_pre_transform_backup RENAME TO orders;
This pattern is especially valuable when your transformation involves bulk updates or deletes, where Time Travel's granularity might not be precise enough to isolate exactly which rows were affected.
A table that is overwritten every hour needs very different retention than a table that is updated once a week. A general heuristic: your Time Travel window should be at least 2x the interval between pipeline runs for critical tables. If your revenue roll-up runs daily, set a 2-day minimum. If a pipeline runs weekly, a 14-day window gives you a buffer.
This connects directly to monitoring your dbt pipelines for failures — if you have good alerting and you catch failures within hours, a 1-day window may be sufficient. If failures can go unnoticed for days (a sign your alerting needs work), you need a longer window.
Let's simulate an incident and recover from it. You'll need a Snowflake account (the free trial tier works fine — see Setting Up Your First Cloud Data Warehouse if you need to get set up).
Step 1: Create a sample table
CREATE DATABASE IF NOT EXISTS learning_db;
USE DATABASE learning_db;
CREATE SCHEMA IF NOT EXISTS sales;
USE SCHEMA sales;
CREATE OR REPLACE TABLE customer_orders (
order_id INTEGER,
customer_id INTEGER,
order_total DECIMAL(10, 2),
order_status VARCHAR(20),
created_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
INSERT INTO customer_orders VALUES
(1001, 42, 349.99, 'completed', CURRENT_TIMESTAMP()),
(1002, 17, 125.00, 'completed', CURRENT_TIMESTAMP()),
(1003, 88, 899.50, 'pending', CURRENT_TIMESTAMP()),
(1004, 55, 45.00, 'completed', CURRENT_TIMESTAMP());
Step 2: Simulate a bad pipeline run
Wait at least 30 seconds so there's a meaningful time gap, then run:
-- Oops — a buggy pipeline just wiped your order totals
UPDATE customer_orders SET order_total = 0;
Step 3: Verify the damage
SELECT * FROM customer_orders;
-- All order_total values are now 0
Step 4: Find the statement that caused the damage
SELECT query_id, query_text, start_time
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION())
WHERE query_text ILIKE '%UPDATE customer_orders%'
ORDER BY start_time DESC
LIMIT 5;
Copy the query_id from the result.
Step 5: Recover using Time Travel
-- Replace the query_id below with the one you copied
CREATE TABLE customer_orders_recovery
CLONE customer_orders
BEFORE (STATEMENT => '<your-query-id-here>');
-- Verify the recovery table has the original data
SELECT * FROM customer_orders_recovery;
-- Restore the original data
UPDATE customer_orders co
SET order_total = cor.order_total
FROM customer_orders_recovery cor
WHERE co.order_id = cor.order_id;
-- Verify
SELECT * FROM customer_orders;
Step 6: Clean up
DROP TABLE customer_orders_recovery;
Congratulations — you just ran a production-grade data recovery operation.
"Time Travel query returns an error saying the data is no longer available"
Your query timestamp or offset is outside the retention window. Either the retention period has expired, or you're querying a transient table (which has a maximum 1-day retention and no Fail-Safe). Check the table's DATA_RETENTION_TIME_IN_DAYS setting with SHOW TABLES.
"UNDROP isn't restoring the version I expected"
UNDROP always restores the most recently dropped version. If a table was dropped multiple times, you can only self-serve recover the last drop via UNDROP. For earlier versions, you'll need to use CLONE ... BEFORE (STATEMENT => ...) with the query ID of the specific drop operation.
"My Time Travel storage costs are much higher than expected"
Tables with frequent bulk updates accumulate large amounts of historical micro-partitions. Check your STORAGE_USAGE view in SNOWFLAKE.ACCOUNT_USAGE to identify which tables are driving costs. Consider reducing retention on high-churn staging tables, or marking them as TRANSIENT. This topic connects to broader cost management in cloud data platforms — retention is one lever among many.
"I can't create a clone with a past timestamp because the schema changed"
If a table's schema was altered (columns added or dropped) within your Time Travel window, cloning from before the schema change may fail. In this case, use the SELECT ... AT (TIMESTAMP => ...) approach to extract the historical data into a new table manually with CREATE TABLE AS SELECT.
"The Fail-Safe window has passed and Snowflake can't recover my data" Unfortunately, once both Time Travel and Fail-Safe windows have expired, the data is gone permanently. This is why setting appropriate retention windows upfront is so important — recovery is only possible if you've given yourself enough window to notice and respond to a problem.
Key insight
The most common reason teams can't recover data isn't that Time Travel failed — it's that they didn't notice the problem until after the retention window expired. Pair Time Travel with strong alerting and data quality checks in your production pipeline so you catch problems while recovery is still possible.
Let's consolidate what you've built. Snowflake's Time Travel feature gives you self-service access to your data's full history within a configurable retention window — up to 90 days on Enterprise Edition. Using the AT and BEFORE clauses, you can query historical states, compare versions, and create zero-copy clones of your tables at any past point in time. UNDROP gives you instant recovery for accidentally dropped objects. Fail-Safe extends protection for 7 additional days after Time Travel expires, but it's managed by Snowflake Support rather than accessible via SQL.
The key operational principles to take away:
QUERY_TAG to annotate your pipeline sessions so you can pinpoint the exact statement that caused damageWhere to go next: Now that you understand how to recover from data problems, consider how to prevent them in the first place. Implementing dbt tests and data quality checks in your production pipeline will help you catch bad data before it propagates. If you're running your transformations through Airflow, orchestrating dbt runs with Airflow covers how to build retry logic and failure handling directly into your DAGs. And if you're thinking about access controls — because the best recovery is the incident that never happens — configuring role-based access control in Snowflake will show you how to limit who can execute destructive operations in the first place.