The difference between snowflake stream and snowpipe

Question

By my understanding so far, snowpipe is something continuously ingesting data from an external stage (eg.s3) to an existing table. Triggered by newly arrived files.

Meanwhile, snowflake stream especially the "Directory table stream"

CREATE STREAM <name> ON STAGE <stage_name>

it can achieve exactly the same (with help of Task).

Can anyone please explain to me what's the difference between these two types of strategy? Any scenario that we want to use one rather than the other?

Answer 1

A stream is a bookmark, that is managed by snowflake, while a snowpipe is an ingestion process. The former could be read by ether code you manual call/your infrastructure calls/a task calls/snowpipe call. Where-as the snowpipe is pushes.

Which is to say, the stream allows something to make progress as it is ready. And what and when and what size instance etc etc is under your control, because at the end of the day, in some way you will trigger it.

We built all our infrastructure prior to snowpipe and streams, thus did all the bookmarking of which files had been loaded so we can/could process hours/day/months of files depending 'how far behind' with the SQL all running on AWS instances we controlled, and the bookmarks in DB's we already also had running. So in this instance the stream would mean our bookmarking could be dropped. However once you have something it's rather nice to be able to reset highwater marks at will to reload data (our stuff handled deduping). Were as if we started again today we would have just used streams.

Yes we could have used snowpipe, but there are processes we want to coordinate/control that needs something to run them, thus we most likely would have ran our SQL still from there. But again, we have it working. And now there is Stored Procedures, there is more free goodies that might have been worth it.