6

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.

Matt Florian
  • 71
  • 1
  • 3
  • 1
    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. – Mike Walton Jun 11 '20 at 13:16
  • 1
    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. – Matt Florian Jun 11 '20 at 15:12

2 Answers2

9

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;
Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • 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 – Matt Florian Jun 11 '20 at 14:30
  • 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; – Matt Florian Jun 11 '20 at 15:08
1

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.

Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29