Snowflake
Time Travel & Fail-safe: Data Recovery Windows
Recover historical or accidentally-modified data using Snowflake's built-in versioning.
Someone runs an UPDATE without a WHERE clause. In most databases that is a restore from backup and several hours of downtime.
Time Travel is like a document's full undo/version history you can jump back into yourself; Fail-safe is the IT department's tape backup you can only request through a support ticket.
Key Concepts
1
In Snowflake it is a query.
2
SELECT * FROM orders AT(OFFSET => -300); -- as of 5 minutes ago
SELECT * FROM orders BEFORE(STATEMENT => '01a...'); -- just before that query
UNDROP TABLE orders; -- the table is back3
It works because nothing was ever overwritten. Micro-partitions are immutable. An update writes new
ones and marks the old ones superseded rather than deleting them.
4
So the previous version of the table is still sitting in storage. Time Travel is just permission to read
it, with a timestamp.
5
The retention window.
Standard edition 1 day (0 to 1)
Enterprise and above up to 90 days
transient tables 0 or 1 day
temporary tables 0 or 1 day6
It is set per table, schema or database, and defaults to 1 day. Ninety days is available and is not free —
see below.
7
Recovering, in practice.
CREATE TABLE orders_recovered CLONE orders AT(OFFSET => -600);8
Clone the old state rather than querying it, because the clone is instant and costs no extra storage.
Check it, then swap it in.
9
Fail-safe is not a feature you can use. After Time Travel expires, a further 7 days begin during which
Snowflake still holds the data — but it is not reachable by SQL, and recovery means opening a support case
that takes days.
10
day 0 ..... Time Travel (your window) -> self-service SQL
then ...... Fail-safe, 7 days, fixed -> Snowflake support only
then ...... genuinely gone11
Treat Fail-safe as Snowflake's insurance policy, not yours. It cannot be configured, shortened or disabled
on permanent tables.
12
Both cost storage, and this is the part that bites. You are billed for the retained versions. On a
table that changes heavily, the retained versions can exceed the live data several times over:
13
a 2 TB table, fully rewritten nightly, 90-day Time Travel
-> up to 90 daily versions retained14
Set retention to match how long you would actually take to notice a mistake — a day or two for most tables,
longer for the few that matter. For large staging or intermediate tables, make them transient: no
Fail-safe at all, and at most a day of Time Travel. That one choice often removes a surprising share of a
Snowflake storage bill.
15
What the interviewer is probing.1. "What is the difference between the two?" Probing: precision. Stalls: "They are the
same." Moves up: time travel is yours to query for up to 90 days on Enterprise; fail-safe is a
further seven days only Snowflake support can recover from.
16
2. "What can you do with time travel?" Probing: the capability. Stalls: "Undo a delete."
Moves up: query AT or BEFORE a timestamp or statement, clone from a past point, and UNDROP a
table, schema or database.
17
3. "What does retention cost?" Probing: the trade. Stalls: "Nothing." Moves up: storage
for changed data over the window, so a long window on a high-churn table multiplies storage.
18
4. "Is fail-safe a backup strategy?" Probing: whether you push back. Stalls: "Yes." *Moves
up:* no — it is Snowflake's last resort with no SLA on recovery time; deliberate retention and
cloning are your strategy.