I am looking for a snippet of code that will cause a stream to empty, essentially resetting it. Here is the use case. We use streams to track changes for type 2 dimension tables into a data mart. There are occasions that call for us to truncate and reload that dimension table to fix a defect or other data issues. When this happens, the stream needs to reset to only start capturing changes after the reload occurred. Snowflake does not have a function such as ALTER STREAM PURGE to manage this for us, so we need to do it ourselves. I do not want to issue a CREATE OR REPLACE STREAM statement each time we need to do this.
Similar to Greg's, you could run something like this, if inserting into an existing table makes you nervous.
CREATE OR REPLACE TEMP TABLE RESET_TBL AS
SELECT * FROM THE_STREAM;