Lab 16 · Domain 5 · You are Priya

Undo the bad edit — Time Travel

Domain 5 starts here. You will query the past with AT / BEFORE, UNDROP a table you just dropped, set retention, and learn why Fail-safe is not something you run yourself.

Business problem Kai ran an UPDATE that wiped good notes on a scratch table. Morgan asks: “Can we get yesterday’s version back without restoring from a nightly dump?” Sam adds: “And if the retention window already closed?”

Solution you will build Scratch table in ALPINE_RETAIL.LAB, Time Travel query with AT(OFFSET => …), clone-at-timestamp, DROP then UNDROP, and a clear mental model: Time Travel = you; Fail-safe = Snowflake Support only (7 days on permanent tables).

Before

Panic mode

Bad UPDATE already committed
“Restore from backup” talk
No idea what Fail-safe is

After

User-recoverable history

SELECT … AT / BEFORE
UNDROP TABLE inside retention
Fail-safe ticket only after TT ends

Episode 16 · Domain 5

Kai broke it. Dev refuses the backup story.

KaiAnalyst — oops

I UPDATEd every note to “oops.” Do we restore last night’s dump? That takes forever.

DevCoach

Not yet. Snowflake kept older micro-partitions for a retention window. Query them with Time Travel — or UNDROP if you deleted the whole table.

Picture: storage keeps old versions for a while. You point the query at “then.”

See it — Time Travel vs Fail-safe

You recover

Time Travel

Query or undrop objects as they were, while inside DATA_RETENTION_TIME_IN_DAYS.

SELECT * FROM T AT(OFFSET => -3600)
UNDROP TABLE T;

Support only

Fail-safe

After Time Travel expires: ~7 more days on permanent tables. You cannot SELECT into Fail-safe.

Open a Support case
Not a SQL command

Edition trap

How long can TT be?

Standard: default 1 day. Enterprise (and above): up to 90 days.

Exam: “90-day TT on Standard?” → No

Table type trap

Transient / temporary

Shorter or zero Fail-safe story: no Fail-safe on transient/temp. Don’t plan Support recovery for them.

permanent → TT + Fail-safe
transient → TT only (short)

AT vs BEFORE — same idea, different pointer

1 · Past AT(…) inclusive of that moment

2 · Exclusive BEFORE(…) state just before stmt/time

3 · Clone CLONE … AT new table from old history

You can point with TIMESTAMP, OFFSET (seconds), or STATEMENT (query id). Exam loves: BEFORE is exclusive of that statement.

Before Step 1

Morgan: “Will this cost storage forever?”

MorganManager — cost

If we keep 90 days of history on every table, who pays?

RileyPipeline — pragmatist

You do — Time Travel history is billable storage. Raise retention only where recovery matters. Default 1 day is fine for scratch tables.

1 Scratch table + a bad edit

What this does: Creates LAB.SCRATCH_TT, inserts a good row, then overwrites it so we have history to query.

SQL · scratch + bad UPDATE

USE ROLE ROLE_CORE_LAB_ADMIN;
USE WAREHOUSE WH_CORE_XS;
USE DATABASE ALPINE_RETAIL;
USE SCHEMA LAB;

CREATE OR REPLACE TABLE SCRATCH_TT (
  ID NUMBER, NOTE STRING
);
INSERT INTO SCRATCH_TT VALUES (1, 'v1-original');
UPDATE SCRATCH_TT SET NOTE = 'v2-oops-bad-edit' WHERE ID = 1;

✓ Current row shows v2-oops-bad-edit

Before Step 2

Kai: “Is AT a backup restore?”

KaiAnalyst

When I SELECT AT OFFSET, am I rolling the live table back for everyone?

DevCoach

No — that SELECT is a read of past micro-partitions. The live table stays on v2 until you deliberately rewrite it or clone/undrop.

2 Query the past with AT

What this does: Reads the table as of ~60 seconds ago (bump the offset if your session was slow).

SQL · Time Travel SELECT

SELECT * FROM SCRATCH_TT AT(OFFSET => -60);

-- Exam syntax awareness (use a real query_id when you have one):
-- SELECT * FROM SCRATCH_TT BEFORE(STATEMENT => '<query_id>');

✓ You should see v1-original (or empty if offset > age of insert — raise offset)

3 Retention + clone at timestamp

What this does: Shows the retention parameter, sets 1 day on the scratch table, and clones history into a new table.

SQL · retention + clone AT

SHOW PARAMETERS LIKE 'DATA_RETENTION_TIME_IN_DAYS'
  IN TABLE SCRATCH_TT;

ALTER TABLE SCRATCH_TT SET DATA_RETENTION_TIME_IN_DAYS = 1;

CREATE OR REPLACE TABLE SCRATCH_TT_CLONE_AT
  CLONE SCRATCH_TT AT(OFFSET => -60);

SELECT * FROM SCRATCH_TT_CLONE_AT;

✓ Clone table exists; live SCRATCH_TT still on the bad edit

Before Step 4

Sam: “What if they DROP the whole table?”

SamCompliance

DROP feels permanent. Is UNDROP only for tables?

PriyaYou

UNDROP works for TABLE, SCHEMA, and DATABASE — while still inside Time Travel retention. After that window: Fail-safe / Support, not UNDROP.

4 DROP then UNDROP

What this does: Deletes the table object, then brings it back from Time Travel history.

SQL · DROP / UNDROP

DROP TABLE SCRATCH_TT;

UNDROP TABLE SCRATCH_TT;

SELECT * FROM SCRATCH_TT;

✓ Table is back; row still whatever it was at drop time

Exam presses further — say these out loud

Cover each answer before you open it. Domain 5 loves these traps.

Q1. Time Travel vs Fail-safe — who can recover, and with what?

Say: Time Travel is user-driven SQL (AT/BEFORE, UNDROP, clone-at-time) inside retention. Fail-safe is after TT expires: ~7 days on permanent tables, recoverable only via Snowflake Support — not a SELECT.

Q2. Standard vs Enterprise Time Travel length?

Say: Standard defaults to 1 day. Enterprise (and higher) can configure up to 90 days via DATA_RETENTION_TIME_IN_DAYS. Distractor: “Standard supports 90-day TT.”

Q3. Do transient tables have Fail-safe?

Say: No. Transient and temporary tables do not have Fail-safe. They may have short Time Travel; do not plan Support recovery the same way as permanent tables.

Q4. What can you UNDROP?

Say: DATABASE, SCHEMA, and TABLE (and some related objects in the same spirit) within the Time Travel window. After retention ends, UNDROP fails — Fail-safe path is Support, not UNDROP.

Q5. Does raising retention to 90 days cost anything?

Say: Yes — retained historical micro-partitions are billable storage. Use long retention where recovery justifies cost; keep scratch/lab tables at 0–1 day.

Priya’s wrap-up (say this out loud)
“Kai’s bad UPDATE didn’t need a nightly restore. I queried SCRATCH_TT with Time Travel AT OFFSET, cloned history into SCRATCH_TT_CLONE_AT, then proved DROP + UNDROP. Retention is DATA_RETENTION_TIME_IN_DAYS — Standard 1 day, Enterprise up to 90. Fail-safe is seven extra days on permanent tables and Support-only — never something I SELECT. Outcome: Domain 5 recovery story locked for the exam.”

Next Lab 17 — zero-copy clones and the replication story (when storage charges start).