snowflake pipe status numOutstandingMessagesOnChannel

Question

I have a pipe that seems to be set up fine, but it just isn't working.

I ran

select system$pipe_status('"MY_DB"."MY_SCHEMA".MY_PIPE_NAME');

i'm getting back a growing number of numOutstandingMessagesOnChannel

Can someone please explain what that means?

Is that rows that will be processed? Do I expect this number to go down? Is there a number where it's too high?

Is there something / some way to track why/when it goes up?

The documentation says merely,

numOutstandingMessagesOnChannel
Number of messages in the queue that have been queued but not received yet.

Answer 1

numOutstandingMessagesOnChannel => Number of messages in the external cloud provider queue that have been queued but not received by Snowflake yet. ( this is not real-time, it's an approximate value)

The numOutstandingMessagesOnChannel should fluctuate if continuously ingesting at a very high rate and Snowflake is not able to process, throttling would occur.

Best to open a case with Snowflake Support, and we can look into the pipe status in more detail for you.

Answer 2

Whenever there is a new file on the storage (e.g.: a new file was added to your AWS S3 bucket), the file path will be sent to Snowflake to be added to the PIPE QUEUE. Later, the PIPE will go over the queue and copy each to the target location. If the PIPE does not work, there might be two causes:

1. The PIPE does not have enough permissions to get the file.
2. The PIPE does not have enough permissions to save it to the target. You might have configured the SQS to get the notifications correctly, but something is wrong with the pipe permissions. You can check the configuration according to this post: https://aws.plainenglish.io/aws-s3-integration-with-snowflake-276e04e1e57e

Also, please check if the file is encrypted with anything other than the S3 Server Side description. If you used KMS to encrypt the file, the Snowflake role will need permission to decrypt using your key.

Usually the issue is with the role policy, you can add it here (delete any identified information)