CODEWITHSUNDEEP
streamsnowflake-cloud-data-platformretain
Dinakar Ullas
Dinakar Ullas

Reputation: 325

How to consume a stream but still retain the data without offsetting

we have a need to retain the data in the stream after the first transaction consumes it. How do we do this? The second transaction will consume again and offset the stream. Need a solution for the 2nd DML to see the stream data after the first DML is done. both the DML's will need the same data in the stream.

Upvotes: 3

Views: 811

Answers (2)

Michael Golos
Michael Golos

Reputation: 2049

It is recommended that users create a separate stream for each change record recipient for the table.

Note that a stream itself does not contain any table data.

Reference: Multiple Consumers of Streams

Upvotes: 2

Dean Flinter
Dean Flinter

Reputation: 664

Sergiu's comment would be the much preferred option but another approach may be to query the stream first using a simple SELECT statement and then follow it up with a DML statement based on the last query result

SELECT * FROM my_stream;


INSERT INTO my_table select * from table(result_scan(last_query_id()));

Upvotes: 2

Related Questions