Pipe

• a wrapper over a COPY INTO statement that runs serverless. Unsupported options for copy into:
  • FILES, ON_ERROR = ABORT_STATEMENT, PURGE, MATCH_BY_COLUMN_NAME, VALIDATION_MODE, FORCE
• AUTO_INGEST setting that automatically ingests data from S3 when a message is received from configured messaging service
  • can load data from named and table stage, but not user stage
• de-duplicates only via filename, cannot reload a file without dropping and recreating the pipe
• when auto_ingest is enabled, all pipes will load if the paths overlap for multiple pipes
• Automation enablement when no other events exist
  1. use show pipes to get SQS ARN
  2. On AWS S3 console, configure event type as ObjectCreate (All) and select SQS -> Add SQS ARN -> SQS queue ARN
• Automation enablement (or Event Bridge) when other events exist
  1. Create SNS topic to handle events
  2. resubscribe, if any, existing services (other SQS or lambdas) to the new SNS topic
  3. use SYSTEM$GET_AWS_SNS_IAM_POLICY function to get IAM Policy that grants Snowflake SQS queue permission to SNS topic
  4. Edit SNS topic -> Access policy Optional,
     • add sns:Subscribe to let Snowflake subscribe to SNS
     • add sns:Publish to let S3 publish events

Error Notifications

• Works only when ON_ERROR=SKIP_FILE
• must supply value for ERROR_INTEGRATION
• steps
  1. Create SNS topic
  2. Create a policy that allows sns:publish on the SNS topic ARN
  3. Create AWS role (another AWS account, require external ID), selecting the policy
  4. create a notification integration with direction = outbound and SNS topic ARN and IAM role ARN
  5. edit role, modify trust relationship to allow sts:AssumeRole using SF_AWS_IAM_USER_ARN and SF_AWS_EXTERNAL_ID from notification integration