Snowflake Stream created using SHOW_INITIAL_ROWS parameter raising error on selection

user_12518487 0
Viewed 376

I've been using SHOW_INITIAL_ROWS parameter in Snowflake when creating a new Stream on a table and it's always worked fine.

For some reason, the stream created in one of the tables is raising an error when some select statement is run against it.

Time travel data is not available for table [...]. The requested time is either beyond the allowed time travel period or before the object creation time

All the tables have time travel enabled and the statement I use to create the streams are something like:

create or replace stream <database>.<schema>.<stream>
on table <database>.<schema>.<table> SHOW_INITIAL_ROWS = TRUE;

Does anyone know if SHOW_INITIAL_ROWS is somehow related to time travel? If yes, what could be causing this error?

snowflake-sql snowflake-cloud-data-platform
1 Answers

It should be related to the recent behaviour change on Time Travel:

https://community.snowflake.com/s/article/Time-Travel-Queries-Beyond-Data-Retention-Period-Will-Fail-Pending

Previous:

If the historical version and the current version of a table were identical, queries beyond the table’s Time Travel data retention period could have completed successfully.

Currently:

If a query is beyond the Time Travel data retention period for a table, the query fails. This change ensures the semantics for Time Travel are consistent and produces predictable query results regardless of when a table was last modified.

user_125509650
Related