What you will be able to do
- Write AT and BEFORE queries using TIMESTAMP, OFFSET and STATEMENT, and work out why one fails
- Clone a table, schema or database as of a point in the past, including when some child tables have shorter retention
- List dropped objects and restore them with UNDROP, including name conflicts and objects dropped more than once
1.Querying historical data with AT | BEFORE
Every query on this page only works within an object's data retention period, the number of days Snowflake keeps changed or dropped data. Within that period, you read past data by putting an AT or BEFORE clause in the FROM clause, right after the table name. AT includes changes made by a statement or transaction at exactly the given point. BEFORE means the point just before it. You give the point with one of three parameters.
SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');Checkpoint 1 of 8· Match them up
Match each AT | BEFORE parameter to the point in time it sets
Tap a term, then the definition that fits it.
TIMESTAMP, OFFSET and STATEMENT are the three ways to choose a point in time in ordinary queries and clones. STREAM is only accepted when creating a stream or querying change data.
“TIMESTAMP OFFSET (time difference in seconds from the present time) STATEMENT (query ID for statement)”Source: docs.snowflake.com
The time zone rule causes real failures. In the documented example, a table was created at 15:25 Pacific time, and a query cast to TIMESTAMP is read as UTC. Snowflake then thinks the requested time comes before the table existed:
SELECT * FROM tt1 at(TIMESTAMP => '2024-06-05 15:29:00'::TIMESTAMP);
000707 (02000): Time travel data is not available for table TT1. The requested time is either beyond the allowed time travel period or before the object creation time.The same query cast to ::TIMESTAMP_LTZ returns the expected rows. Other reasons a Time Travel query can fail:
- The requested time is beyond the retention period. - The time is within the retention period, but no historical data exists for it, for example because retention was only recently extended. - The time is at or before the moment the object was created. - A STATEMENT query ID is more than 14 days old. In that case, use the query's timestamp instead.
Also keep in mind:
- Results use the *current* table definition, so a column added later appears in historical results too. - Historical data has the same access controls as current data. - AT and BEFORE cannot be applied to a CTE, though they can be used inside one. - BEFORE(STATEMENT) means just before the statement *completed*, so changes that concurrent sessions committed while it ran show up in the results.
Checkpoint 2 of 8· Check yourself
A table has 30-day retention. You want to see its data as it was before a DELETE that ran 20 days ago, and you have that DELETE's query ID. What happens with BEFORE(STATEMENT => '<that id>')?
STATEMENT can only look up query IDs from the last 14 days, whatever the retention period. A TIMESTAMP from the same window still works.
“The query ID must reference a query that has been executed within the last 14 days.”Source: docs.snowflake.com
Checkpoint 3 of 8· Exam question
A company on Standard Edition must keep 14 days of Time Travel history on its production ORDERS table for an audit. An administrator runs `ALTER TABLE orders SET DATA_RETENTION_TIME_IN_DAYS = 14;` and the statement fails. What is the MOST appropriate way to meet the requirement?
Correct answer: C — Upgrade the account to Enterprise Edition or higher, then rerun the same ALTER TABLE statement to set the table retention to 14 days.
- A. Incorrect. The minimum-retention parameter only raises the effective floor within what the edition allows, and it cannot exceed the Standard Edition one-day maximum.
- B. Incorrect. The account-level parameter is bounded by the same edition limit, so a value of 14 is rejected on Standard Edition just like the table-level setting.
- C. Correct. Standard Edition caps Time Travel at 1 day, and retention of 2 to 90 days for permanent tables requires Enterprise Edition or higher, so the edition must change first.
- D. Incorrect. Transient tables are limited to at most 1 day of Time Travel on every edition, so converting the table would reduce retention rather than extend it.
Sources1
2.Cloning objects as of a point in the past
The same AT | BEFORE clause works after the source name in CREATE TABLE, SCHEMA or DATABASE ... CLONE. It creates a copy of the object as it was at that point. If you leave out the point in time, the clone uses the object's current state. Cloning is a way to look at or recover past data without overwriting the live object.
CREATE SCHEMA restored_schema CLONE my_schema AT(OFFSET => -3600);Cloning a database or schema follows its children's retention, not the container's. The clone fails if the requested time is beyond the retention of any current child, such as a table with a shorter period. It also fails if the time is at or before the container was created. If some children have already been purged from Time Travel, add IGNORE TABLES WITH INSUFFICIENT DATA RETENTION to skip them.
Some objects are never included in a Time Travel clone: external tables and internal (Snowflake) stages. Hybrid tables can be cloned with a database but not with a schema. User tasks are left out when you clone a schema with a timestamp, but are included in an ordinary clone with no timestamp.
Checkpoint 4 of 8· Fill the gap
Which keyword lets this four-day-old database clone succeed when some tables keep less than four days of history?
CREATE DATABASE restored_db CLONE my_db AT(TIMESTAMP => DATEADD(days, -4, current_timestamp)::timestamp_tz) ? TABLES WITH INSUFFICIENT DATA RETENTION;IGNORE TABLES WITH INSUFFICIENT DATA RETENTION skips children that no longer have history for the requested time, so the clone does not fail.
Source: docs.snowflake.comCheckpoint 5 of 8· Check yourself
You clone schema etl AT(TIMESTAMP => ...) to inspect yesterday's state. Which object from the source schema will be missing from the clone?
Tasks are only cloned when no timestamp is given. External tables and internal stages are also left out of Time Travel clones.
“User tasks in a database or schema are not cloned when using CREATE SCHEMA … TIMESTAMP.”Source: docs.snowflake.com
Checkpoint 6 of 8· Exam question
A team on Enterprise Edition reloads a 4 TB staging table from source files every night and has never needed to recover it. To reduce storage cost they re-create it as a TRANSIENT table. Select TWO statements that correctly describe the effect of this change.(Select 2)
Correct answers: A, E — The table has no Fail-safe period, so changed and deleted data stops accruing storage once its Time Travel window ends.; The table can keep at most 1 day of Time Travel, even though permanent tables on this account can be set up to 90 days.
- A. Correct. Transient tables have no Fail-safe, which removes the extra seven days of storage that permanent tables incur after Time Travel ends.
- B. Incorrect. Purging at session end is a temporary-table behavior, whereas a transient table persists across sessions until it is explicitly dropped.
- C. Incorrect. Transient tables fully support Time Travel for up to 1 day, so AT and BEFORE queries work as long as they stay within that window.
- D. Incorrect. Transient tables have no Fail-safe at all, and they can still be recovered with UNDROP while the dropped table is inside its Time Travel window.
- E. Correct. Transient tables allow a retention of only 0 or 1 day regardless of edition, so the 90-day maximum for permanent tables does not apply.
Sources2
3.Restoring dropped objects with UNDROP
Dropping a table, schema or database does not remove it straight away. It is kept for its retention period, and you can restore it until then. Once it moves to Fail-safe, you can no longer restore it yourself. Note that creating a new object with the same name does not restore the old one. It creates a new version, and the dropped version can still be restored.
To find what you can restore, add HISTORY to SHOW TABLES, SHOW SCHEMAS, SHOW DATABASES or SHOW ACCOUNTS. The output adds a dropped_on column, and an object dropped several times appears once per version. Objects whose retention has expired are purged and no longer appear.
SHOW TABLES HISTORY LIKE 'load%' IN mytestdb.myschema;UNDROP supports tables, schemas, databases, notebooks, Iceberg tables, dynamic tables, external volumes, tags and accounts. It brings back the object's most recent state before the DROP, in place, without creating a new object. Rules to remember:
- Name conflicts: if an object with that name already exists, UNDROP fails. Rename the existing object first.
- Location: a table is restored to the database and schema it was dropped from, not to your current schema.
- Privileges: you need OWNERSHIP on the object, plus CREATE on that object type in the target database or schema.
- Several dropped versions: UNDROP TABLE restores the most recent one. To pick an older version, find its table_id in SNOWFLAKE.ACCOUNT_USAGE.TABLES and pass it with IDENTIFIER(). The table comes back with its original name.
- Hybrid tables cannot be undropped.
UNDROP TABLE IDENTIFIER(408578);Checkpoint 7 of 8· Check yourself
An engineer dropped table orders an hour ago, then ran CREATE TABLE orders to start again. Now they want the original data back. What has to happen?
A new object with the same name blocks UNDROP. Rename it first, and the dropped version, still within retention, can then be restored in place.
“If an object with the same name already exists, UNDROP fails.”Source: docs.snowflake.com
Checkpoint 8 of 8· Exam question
An analyst needs the ORDERS table as it looked 30 minutes ago, before a faulty update. The query `SELECT * FROM orders AT(OFFSET => -30);` still returns rows that include the faulty update. What explains this and how should the query be fixed?
Correct answer: B — OFFSET is measured in seconds, so the query reads the table 30 seconds ago; use `AT(OFFSET => -60*30)` to go back 30 minutes.
- A. Incorrect. OFFSET is not in minutes, and the session timezone has no effect on an offset, which is a relative number of seconds.
- B. Correct. The OFFSET parameter is expressed in seconds relative to the current time, so -30 is 30 seconds ago and -1800 is needed for 30 minutes ago.
- C. Incorrect. OFFSET works on any table within its retention period and is not limited to dropped tables, so the real issue is only the unit.
- D. Incorrect. BEFORE does accept OFFSET, but with -30 it still points at 30 seconds ago, so the unit mistake would remain and the update would still appear.
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An AT(TIMESTAMP => '...') literal with no cast is read in the session's time zone.Why is that wrong?
Without a cast it is treated as UTC (TIMESTAMP_NTZ). Cast to TIMESTAMP_LTZ to use the session's local time.
Covered in Querying historical data with AT | BEFORE
2.Creating a table again with the dropped table's name brings back its data.Why is that wrong?
It creates a new, empty version. The dropped version still exists and has to be restored with UNDROP after the new table is renamed.
Covered in Restoring dropped objects with UNDROP
3.UNDROP TABLE with a qualified name restores the table into your current schema.Why is that wrong?
A table always goes back to the database and schema it was dropped from.
Covered in Restoring dropped objects with UNDROP
Practise it for real
Use Time Travel on a test table: read past data, then drop and restore the table
1.Create a test table, insert a few rows, wait a few minutes, then insert one more row.
Why: Time Travel needs at least one change to have history to show.
You should see: The table has all the rows, and SHOW TABLES shows its retention_time.
2.Run SELECT * FROM <table> AT(OFFSET => -60*2) with an offset that falls before your last insert.
Why: OFFSET gives the point in time as seconds before now.
You should see: The result leaves out the row you inserted last.
3.DROP the table, then run SHOW TABLES HISTORY LIKE '<table>%'.
Why: HISTORY lists dropped objects that are still within retention.
You should see: The table appears with a dropped_on timestamp.
4.Run UNDROP TABLE <table>, then query it.
Why: UNDROP restores the most recent version in place, with all its data.
You should see: A message confirms the table was restored, and every row is back.
Stuck? Get a nudge
If the AT query fails with error 000707, check that your offset does not reach back before the table was created.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“no historical data is available (e.g. if the retention period was extended), the statement fails.”
↩︎ Querying historical data with AT | BEFORE“Historical data has the same access control requirements as current data.”
↩︎ Querying historical data with AT | BEFORE“the timestamp in the AT clause is treated as a timestamp with the UTC time zone”
↩︎ Exam trap 1“the timestamp in the AT clause is treated as a timestamp with the UTC time zone”
↩︎ Prediction“The query ID must reference a query that has been executed within the last 14 days.”
↩︎ Checkpoint - 2.
“If the specified Time Travel time is beyond the retention time of any current child (for example, a table) of the entity.”
↩︎ Cloning objects as of a point in the past“Restoring a dropped object restores the object in place (that is, it does not create a new object).”
↩︎ Restoring dropped objects with UNDROP“a user must have OWNERSHIP privileges for an object to restore it.”
↩︎ Restoring dropped objects with UNDROP“After dropping an object, creating an object with the same name does not restore the object.”
↩︎ Exam trap 2“TIMESTAMP OFFSET (time difference in seconds from the present time) STATEMENT (query ID for statement)”
↩︎ Checkpoint“User tasks in a database or schema are not cloned when using CREATE SCHEMA … TIMESTAMP.”
↩︎ Checkpoint“If an object with the same name already exists, UNDROP fails.”
↩︎ Checkpoint - 3.
“You cannot undrop a hybrid table.”
↩︎ Restoring dropped objects with UNDROP“Tables can only be restored to the database and schema that contained the table at the time of deletion.”
↩︎ Exam trap 3