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