How can I advance the offset of a Snowflake stream without a DML?

user_9151345 0
Viewed 904

Snowflake streams are really cool, but I can't seem to figure out how to apply them to my use case. I have an external process that I want to use to detect changes to rows in my tables and notify other consumers that this happened. There are several ways to do this, but the nice thing about streams is that they can keep track of last time they were asked, and that would provide a clean way to track the offset and prevent dupes or gaps. For example, an alternative that uses explicit time travel would need externally keep track of the last time it ran the query, including accounting for clock skew between Snowflake and the process.

However, the offset only seems to move up if you push the data in it to another table. Meaning, this changes the offset:

insert into other_table select * from my_stream

but this does not:

select * from my_stream

In my case, I don't need the data in another table. I could insert into a temp table or something to create the side effect of advancing the offset, but that seems wasteful and messy. Is there some alternative I'm missing? Some way to "bump" the stream?

snowflake-cloud-data-platform
1 Answers

You can use a where clause that selects no rows. It will still consume the stream.

insert into other_table select * from my_stream where false;

If you're never going to use the rows in the stream, just use it to detect when changes are there since the last consumption point, you may also consider replacing the stream.

create or replace stream my_stream on table my_table;

That will allow you to check for changes on the old stream and start with a new one when you do whatever needs to happen when the old one reports changed rows.

user_127563810
Related