Snowpipe receives S3 message but does not forward it

Question

We configured S3 object create notifications to Snowpipe. It receives the message but does not forward them, so that data is not loaded. When we run the COPY INTO statement manually, the data loads.

Sample output from SYSTEM$PIPE_STATUS:

{
    "executionState": "RUNNING",
    "lastReceivedMessageTimestamp": "2020-03-18T14:14:48.69Z",
    "notificationChannelName": "arn:aws:sqs:us-east-1:[clipped]:[clipped]",
    "numOutstandingMessagesOnChannel": 0,
    "pendingFileCount": 0
}

Per https://docs.snowflake.net/manuals/user-guide/data-load-snowpipe-ts.html#step-1-check-the-pipe-status:

If event messages are getting received from the message queue but are not forwarded to the pipe, then there is likely a mismatch between the Blob storage path where the new data files are created and the combined path specified in the Snowflake stage and pipe definitions

But the path in the stage is the bucket name followed by /, so that any file created in the bucket should trigger a load. And the file names are definitely new and unique (as created by Kinesis Firehose). The stage definition:

CREATE STAGE if not exists my_stage URL = 's3://my-stage/'
    CREDENTIALS = (AWS_KEY_ID = 'XXX' AWS_SECRET_KEY = 'XXX');

The pipe definition:

create or replace pipe my_schema.my_data_pipe
    auto_ingest=true
    aws_sns_topic='arn:aws:sns:us-east-1:[my-account]:my-s3-create-event'
    as
  copy into my_schema.my_table
      from @my_schema.my_stage
      file_format=(type='PARQUET' compression='SNAPPY')
      match_by_column_name=case_insensitive;

Note also that when we refresh the pipe the data loads: alter pipe my_schema.my_pipe refresh;

What else might cause Snowpipe to not forward the S3 create message and trigger the load?

Answer 1

Your COPY in pipe create sql is bad, change to:

copy into my_db. my_schema.my_table
  from @my_db. my_schema.my_stage

Answer 2

Setting up snowpipe with SNS is a bit convoluted. Please ensure to follow these steps: https://docs.snowflake.net/manuals/user-guide/data-load-snowpipe-auto-s3.html#option-2-configuring-amazon-sns-to-automate-snowpipe-using-sqs-notifications

That still doesnt work, please contact Snowflake support.

PS. Note that ALTER PIPE REFRESH does not use the S3 notifications to load data. It simply lists all files from the stage and executes COPY for all such files.

Answer 3

you could try running the following (if you haven't done so already) to see if there are any errors listed on the Snowflake side:

SYSTEM$PIPE_STATUS( '<pipe_name>' )

Some other ideas here as well might be worth looking into: https://docs.snowflake.com/en/user-guide/data-load-snowpipe-ts.html#automatically-loading-data-using-cloud-storage-event-notifications

But if you can share any errors then others can provide feedback/ideas from there.

Answer 4

Is it possible that you accidentally mismatched the pipe with the wrong SNS topic?

In that case, the pipe would receive events, but possibly from the wrong bucket or path/prefix (hence not forwarded).