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 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

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.

    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.

  • 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;