- Categories:
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:
Syntax¶
SELECT ...
FROM ...
{
AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
BEFORE( STATEMENT => <id> )
}
[ ... ]
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
whereN
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 |
---|---|---|---|---|
|
1 |
INSERT INTO my_table(id) VALUE(1) |
Start |
Insert starts execution by performing required checks. |
|
1 |
INSERT INTO my_table(id) VALUE(1) |
End |
Insert updated |
|
1 |
DELETE FROM my_table |
Start |
Delete identifies the list of records to delete (id=1). |
|
2 |
INSERT INTO my_table(id) VALUE(2) |
Start |
Insert starts execution by performing required checks. |
|
2 |
INSERT INTO my_table(id) VALUE(2) |
End |
Insert updated |
|
2 |
SELECT * FROM my_table |
End |
Thread |
|
1 |
DELETE FROM my_table |
End |
Delete updates |
|
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 |
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);
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;
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
|
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);
SELECT * FROM my_table AT(TIMESTAMP => TO_TIMESTAMP(1432669154242, 3));
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';
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');
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;