Snowpipe Infastructure & s3 subfolders

Question

I am trying to set up scalable snowpipe infrastructure. I have one AWS lambda function pulling data and putting the raw json files into their corresponding folders below.

Ideally I'd like to set up snowpipe to read in the data from each folder into it's own Snowflake table.

Ex)

• The leads json file living in the leads folder is now piped into a leads_json table within snowflake.
• The opportunities json file living in the opportunities folder is now piped into a opportunitie_json table within snowflake.

How do I go about setting up the pipelines and stages to reduce the number of pipelines and stages needed?

Will I need one pipeline and stage per sub folder in the bucket?

I'm going to make use out of the AUTO_INGEST=true feature using SQS notifications.

Answer 1

You will need 1 PIPE for each TABLE that you are loading via Snowpipe. You could have a single STAGE pointing to the top folder of your S3 bucket, if you wish, or you could create 1 per table at a lower level folder. I hope that is answering your question.