Has anyone created a procedure to manually purge a stream in snowflake?

Question

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.

Dustpan

Why do you not want to do a CREATE OR REPLACE STREAM statement? You could create a stored procedure that runs a GET_DDL() statement for the stream and then use that DDL to run the CREATE OR REPLACE STREAM statement. – Guiana

While that could work, the reason we do not do it is that we manage DDL deployments in production using very strict roles and user groups. I do not want a developer to create a production object through code that can interrupt our deployment pipelines. But you are absolutely correct that a create or replace has the same affect that I was looking for. Thanks. – Dustpan

Answer 1

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;

Guiana

This is by far the most simple solution for this. I was working towards using a temp table as you suggested and this confirmed it for me. I have the code below for testing that this works if anyone is interested – Dustpan

create table BI_DATA_MART.MY_TABLE (COL1 STRING); CREATE OR REPLACE STREAM MY_TABLE_STREAM ON TABLE BI_DATA_MART.MY_TABLE; INSERT INTO BI_DATA_MART.MY_TABLE VALUES('A'); INSERT INTO BI_DATA_MART.MY_TABLE VALUES('B'); INSERT INTO BI_DATA_MART.MY_TABLE VALUES('C'); INSERT INTO BI_DATA_MART.MY_TABLE VALUES('D'); INSERT INTO BI_DATA_MART.MY_TABLE VALUES('E'); SELECT * FROM MY_TABLE_STREAM; create OR REPLACE temporary table BI_DATA_MART.MY_TABLE_TMP AS SELECT * FROM MY_TABLE_STREAM; SELECT * FROM BI_DATA_MART.MY_TABLE_TMP; SELECT * FROM MY_TABLE_STREAM; – Dustpan

Answer 2

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.

Overdevelop