CertCram

Snowflake Time Travel vs Fail-safe: retention, recovery and cost

· 3 min read

Time Travel and Fail-safe are both part of Snowflake's Continuous Data Protection, and exam questions love to blur them. The short version: Time Travel is yours; Fail-safe is Snowflake's.

The one-table summary

Time Travel Fail-safe
Who can access it You, with SQL Only Snowflake Support
Default period 1 day 7 days (permanent tables only)
Configurable Yes — DATA_RETENTION_TIME_IN_DAYS No
Starts When data is changed or dropped When the Time Travel period ends
Purpose Self-service recovery, point-in-time queries, cloning Last-resort disaster recovery
Costs storage Yes Yes

How long Time Travel lasts

Retention is controlled by DATA_RETENTION_TIME_IN_DAYS, which can be set at the account, database, schema or table level — the most specific setting wins.

  • Standard edition: 0 or 1 day.
  • Enterprise edition and higher: up to 90 days for permanent databases, schemas and tables.
  • Transient and temporary tables: 0 or 1 day on any edition.

Setting retention to 0 disables Time Travel for that object. On Enterprise, account administrators can enforce a floor with MIN_DATA_RETENTION_TIME_IN_DAYS.

What you can do with Time Travel

Query the past with AT or BEFORE, using a timestamp, an offset in seconds, or a statement ID:

-- the table as it looked 30 minutes ago
SELECT * FROM orders AT (OFFSET => -60 * 30);

-- the table just before a specific (bad) statement ran
SELECT * FROM orders BEFORE (STATEMENT => '01b2c3d4-0000-1234-0000-000000000000');

-- restore by cloning a point in time
CREATE TABLE orders_restored CLONE orders
  AT (TIMESTAMP => '2026-09-28 14:00:00'::TIMESTAMP_LTZ);

Bring back dropped objects with UNDROP — it works for tables, schemas and databases as long as they're still inside their retention period:

UNDROP TABLE orders;

A common exam trap: UNDROP only helps with dropped objects. If someone ran TRUNCATE or a bad DELETE, the table still exists — recover with an AT/BEFORE query or a clone instead.

What Fail-safe is (and isn't)

When Time Travel ends for a permanent table, its historical data moves into a 7-day Fail-safe period. You can't query it, clone it or undrop from it. Recovery means contacting Snowflake Support; it's best-effort and can take hours to days. Treat Fail-safe as insurance against catastrophic failure, not as part of your recovery plan.

Transient and temporary tables have no Fail-safe — that's exactly why they exist.

Changing retention on existing data

  • Increasing retention extends how long data that is currently in Time Travel is kept.
  • Decreasing retention: data still inside the new, shorter window stays in Time Travel; anything older moves to Fail-safe.

Data that had already moved to Fail-safe before an increase stays in Fail-safe — raising retention doesn't bring it back. Scenario questions test exactly this.

The cost side

Both Time Travel and Fail-safe consume storage, and you pay for it. High-churn tables — staging tables truncated and reloaded every day, for example — can accumulate far more historical bytes than active bytes.

Check where storage is going:

SELECT table_name, active_bytes, time_travel_bytes, failsafe_bytes
FROM information_schema.table_storage_metrics
WHERE table_schema = 'STAGING'
ORDER BY failsafe_bytes DESC;

Practical rules:

  1. Use transient tables for staging and intermediate ETL tables you can rebuild — no Fail-safe cost.
  2. Keep long retention (30–90 days) on tables where a human mistake would be expensive to undo.
  3. Use 0 retention only where you're sure nobody will need to look back.

Quick self-check

  1. A permanent table with 1-day retention was dropped 4 days ago. Can you UNDROP it? No — it's in Fail-safe; only Snowflake Support can help.
  2. Which editions allow 90-day Time Travel? Enterprise and above.
  3. Why make a staging table transient? No Fail-safe storage, and at most 1 day of Time Travel.

Want more questions like these? Try 15 free SnowPro Core practice questions — every one comes with an explanation.

More study guides