Snowflake/S3 Pipeline: ETL architecture Questions I am trying to build a pipeline which is sending data from Snowflake to S3 and then from S3 back into Snowflake (after running it through a production ML model on Sagemaker). I am new to Data Engineering, so I would love to hear from the community what the recommended path is. The pipeline requirements are the following:


I am looking to schedule a monthly job. Do I specify such in AWS or on the Snowflake side?
The monthly pulls should get the last full month (since this should be a monthly pipeline).
All monthly data pulls should be stored in own S3 subfolder like this query_01012020,query_01022020,query_01032020 etc.
The data load from S3 (query_01012020,query_01022020,query_01032020) back to a specified Snowflake table should be triggered after the ML model has successfully scored the data in Sagemaker.
I want to monitor the performance of the ML model in production overtime to catch if the model is decreasing its accuracy (some calibration-like graph perhaps).
I want to get any error notifications in real-time when issues in the pipeline occur.


I hope you are able to guide me on what components the pipeline should include. Any relevant documentation/tutorials for this effort are truly appreciated.

Thank you very much.
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)