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.
2 Answers
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;
Can you do something like this?
insert into THE_TARGET_TABLE select * from THE_STREAM where 1=0
That will consume the stream without changing anything on the target table.
Related