Skip to content

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
  • use show pipes to get SQS ARN
  • 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
  • Create SNS topic to handle events
  • resubscribe, if any, existing services (other SQS or lambdas) to the new SNS topic
  • use SYSTEM$GET_AWS_SNS_IAM_POLICY function to get IAM Policy that grants Snowflake SQS queue permission to SNS topic
  • 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
  • Create SNS topic
  • Create a policy that allows sns:publish on the SNS topic ARN
  • Create AWS role (another AWS account, require external ID), selecting the policy
  • create a notification integration with direction = outbound and SNS topic ARN and IAM role ARN
  • edit role, modify trust relationship to allow sts:AssumeRole using SF_AWS_IAM_USER_ARN and SF_AWS_EXTERNAL_ID from notification integration