Snowflake Stream became stale even though data is coming through

user_9972301 0
Viewed 1071

I've set up a few streams and tasks that consume these streams in Snowflake and at times I need to recreate them because they became stale. I've read through the documentation and there is nothing in my account that causes the issue. The tasks are running every 5 minutes and there is data flowing through every minute. The task scripts take the data in the streams and use a MERGE statement. At times the stream just becomes stale and then the task starts failing.

How do I prevent this?

snowflake-cloud-data-platform
1 Answers

I found that we had some direct castings like ::int in the script. After a while developers started sending strings in the JSON data. The stream contained several scripts encapsulated in begin..commit. The task did not show the scripts failed but in reality, the DML statements were rolled back so the stream did not clear. Eventually, they became stale as the data was not cleared within the default timeframe. We changed any x::int to try_to_number(x::string) and this worked.

user_99723010
Related