Categories:: Query Syntax

AT | BEFORE¶

The AT or BEFORE clause is used for Snowflake Time Travel. In a query, it is specified in the FROM clause immediately after the table name, and it determines the point in the past from which historical data is requested for the object:

The AT keyword specifies that the request is inclusive of any changes made by a statement or transaction with timestamp equal to the specified parameter.
The BEFORE keyword specifies that the request refers to a point immediately preceding the specified parameter. If the specified parameter is the query ID of a statement, this point is just before the statement is completed. For more information, see Using the BEFORE clause with a query ID.

For more information, see Understanding & Using Time Travel.

See also:: FROM

Syntax¶

SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]

Copy

TIMESTAMP => timestamp

Specifies an exact date and time to use for Time Travel. The value must be explicitly cast to a TIMESTAMP.

OFFSET => time_difference

Specifies the difference in seconds from the current time to use for Time Travel, in the form -N where N can be an integer or arithmetic expression (e.g. -120 is 120 seconds, -30*60 is 1800 seconds or 30 minutes).

STATEMENT => id

Specifies the query ID of a statement to use as the reference point for Time Travel. This parameter supports any statement of one of the following types:

DML (e.g. INSERT, UPDATE, DELETE)
TCL (BEGIN, COMMIT transaction)
SELECT

The query ID must reference a query that has been executed within the last 14 days. If the query ID references a query over 14 days old, the following error is returned:

Error: statement <query_id> not found

To work around this limitation, use the timestamp for the referenced query.

STREAM => 'name'

Specifies the identifier (i.e. name) for an existing stream on the queried table or view. The current offset in the stream is used as the AT point in time for returning change data for the source object.

This keyword is supported only when creating a stream (using CREATE STREAM) or querying change data (using the CHANGES clause). For examples, see these topics.

Using the BEFORE clause with a query ID¶

If the parameter for the BEFORE clause is a query ID, the point in the past used by Time Travel is just before the statement is completed rather than before the statement is started. If concurrent queries commit modifications to the data between the start and end of the statement, these changes are included in your results.

For example, the following statements are being executed on table my_table in parallel in two separate threads:

Time	Thread	Operation	Phase	Description
`t1`	1	INSERT INTO my_table(id) VALUE(1)	Start	Insert starts execution by performing required checks.
`t2`	1	INSERT INTO my_table(id) VALUE(1)	End	Insert updated `my_table`.
`t3`	1	DELETE FROM my_table	Start	Delete identifies the list of records to delete (id=1).
`t4`	2	INSERT INTO my_table(id) VALUE(2)	Start	Insert starts execution by performing required checks.
`t5`	2	INSERT INTO my_table(id) VALUE(2)	End	Insert updated `my_table`.
`t6`	2	SELECT * FROM my_table	End	Thread `2` selects rows from `my_table`. The results include all rows (id=1, id=2).
`t7`	1	DELETE FROM my_table	End	Delete updates `my_table` deleting all old records present before time `t3` when the delete statement started in thread `1` (id=1).
`t8`	1	SELECT * FROM my_table BEFORE(STATEMENT => LAST_QUERY_ID())	End	SELECT statement uses Time Travel to retrieve historical data from before the completion of the delete operation. The results include the row from the 2nd insert statement that happened concurrently in thread `2` (id=1, id=2).

As a workaround, you can use a TIMESTAMP parameter that specifies a point in time just before the start of the statement.

Usage notes¶

Data in Snowflake is identified by timestamps that can differ slightly from the exact value of system time.
The value for TIMESTAMP or OFFSET must be a constant expression.
The smallest time resolution for TIMESTAMP is milliseconds.
If requested data is beyond the Time Travel retention period (default is 1 day), the statement fails.

In addition, if the requested data is within the Time Travel retention period but no historical data is available (e.g. if the retention period was extended), the statement fails.
If the specified Time Travel time is at or before the point in time when the object was created, the statement fails.
When you access historical table data, the results include the columns, default values, etc. from the current definition of the table. The same applies to non-materialized views. For example, if you alter a table to add a column, querying for historical data before the point in time when the column was added returns results that include the new column.
Historical data has the same access control requirements as current data. Any changes are applied retroactively.

The AT and BEFORE clauses do not support selecting historical data from a CTE.

For example, the following query is not supported:

WITH mycte AS
  (SELECT mytable.* FROM mytable)
SELECT * FROM mycte AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ);

Copy

However, these clauses are supported in a query in a WITH clause. For example, the following query is supported:

WITH mycte AS
  (SELECT * FROM mytable AT(TIMESTAMP => '2024-03-13 13:56:09.553 +0100'::TIMESTAMP_TZ))
SELECT * FROM mycte;

Copy

Troubleshooting¶

Error	Time travel data is not available for table <tablename>
Cause	In some cases, this is caused by using a string where a timestamp is expected.
Solution	Cast the string to a timestamp. ... AT(TIMESTAMP => '2018-07-27 12:00:00') -- fails ... AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP) -- succeeds Copy

Examples¶

Select historical data from a table using a specific timestamp:

SELECT * FROM my_table AT(TIMESTAMP => 'Fri, 01 May 2015 16:20:00 -0700'::timestamp);

Copy

SELECT * FROM my_table AT(TIMESTAMP => TO_TIMESTAMP(1432669154242, 3));

Copy

Select historical data from a table as of 5 minutes ago:

SELECT * FROM my_table AT(OFFSET => -60*5) AS T WHERE T.flag = 'valid';

Copy

Select historical data from a table up to, but not including any changes made by the specified transaction:

SELECT * FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

Copy

Return the difference in table data resulting from the specified transaction:

SELECT oldt.* ,newt.*
  FROM my_table BEFORE(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS oldt
    FULL OUTER JOIN my_table AT(STATEMENT => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726') AS newt
    ON oldt.id = newt.id
  WHERE oldt.id IS NULL OR newt.id IS NULL;

Copy