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?
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.
Stream: A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change so that actions can be taken using the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a “stream”) makes a “change table” available of what changed, at the row level, between two transactional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion. Details: https://docs.snowflake.com/en/user-guide/streams.html
Example of stream working: https://docs.snowflake.com/en/user-guide/data-pipelines-examples.html#transforming-loaded-json-data-on-a-schedule
Snowpipe: Snowpipe enables loading data from files as soon as they’re available in a stage. This means you can load data from files in micro-batches, making it available to users within minutes, rather than manually executing COPY statements on a schedule to load larger batches. Details: https://docs.snowflake.com/en/user-guide/data-load-snowpipe-intro.html