Snowflake/S3 Pipeline: ETL architecture Questions
see: https://docs.snowflake.com/en/sql-reference/sql/copy-into-location.html

Depends... you can use SNOWFLAKE TASKS to copy data from table to external location (s3/azure..). You can also use any other system which can run commands in snowflake

Same as 1. I think you need use (js)UDF to make your dynamic SQL (filename part) and run it if you use snowflake only solution. Ie. Js UDF, which executes your SQL


see: https://stackoverflow.com/questions/62403809/how-to-write-dynamic-queries-in-snowflake-user-defined-functions

You could use snowpipe and listen events, or run command from ML or just have task that tries to stage new files from ML output s3 folder, which to my knowledge does not cost compute to try and fail to find new files

I don't know about that, maybe report after you have imported it into snowflake

reporting from task history could be one way, not my expertise


So in practise 1 ,2 ,3 can be done using snowflake task only or tasks and snowpipe using files into s3 as trigger. Data can be also enriched using external function calls in snowflake query which uses udf to make some call to some api.
Assuming that your team is small and you do not yet have company way of doing things, i would recommend to start using tasks + snowpipe to handle simple data staging. I personally use Matillion because i have one, but if you do not have one, it's cheaper just to start learn snowflake way and do things there. Learning curve is not that bad, it has it own problems, but those are quite easy to handle. Most of them were communication to ML data producers to keep output schema stable
Only thing which changes if you have company way of doing thing is that those programs orchestrate copy into s3 command and copy from s3 commands in snowflake and run ML in sagemaker ( i do not know about that one to say anything useful)