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.

For more information, see Understanding & Using Time Travel.

See also:
FROM

Syntax

SELECT ...
FROM ...
  {
   AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]
TIMESTAMP => timestamp
Specifies an exact date and time to use for Time Travel. Note that 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 ID of a statement to use as the reference point for Time Travel. The specified statement can be a query or any other supported type of statement for which you have the statement ID.

Usage Notes

  • Data in Snowflake is identified by timestamps that may 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 or OFFSET is milliseconds.
  • If requested data is beyond the Time Travel retention period (default is 1 day), the statement fails.
  • When accessing historical table data, the current table schema is used (columns, default values, etc.).
  • Historical data has the same access control requirements as current data. Any changes are applied retroactively.

Troubleshooting

Error message: 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')               -- wrong
... AT(TIMESTAMP => '2018-07-27 12:00:00'::TIMESTAMP)    -- right

select * from tab1 where c1 = 'This is a string', c2 = 10 and "Column 2" = 20;

Examples

Select historical data from a table using a specific timestamp:

SELECT * FROM my_table AT(TIMESTAMP => 'Mon, 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;