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> | STREAM => '<name>' } ) |
   BEFORE( STATEMENT => <id> )
  }
[ ... ]
Copy
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

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
Copy

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.

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 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
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
Language: English