I set up Snowpipe to ingest JSON files from S3 using SQS notifications, but lately, there is a 10-minute lag between the file landing in S3 and appearing in Snowflake. How can I debug if the issue is on the AWS side or within the Snowpipe pipe object status?
3 answers
You can use SYSTEM$PIPE_STATUS to see the 'lastReceivedMessageTimestamp'. Comparing that to the S3 file timestamp will tell you exactly where the lag is happening.
First, check the PIPE_STATUS function in Snowflake to see if the pipe is 'RUNNING' or if it has encountered an execution error. If the status is fine, the bottleneck is often the SQS queue depth or a permission mismatch in the IAM policy. Also, remember that Snowpipe is intended for frequent, small files. If you are dumping massive 5GB files, the ingestion will naturally take longer. For JSON, ensure you aren't using an overly complex STRIP_OUTER_ARRAY transformation inside the pipe definition, as that adds overhead.
Are you using a single pipe for all your folders, or have you split your ingestion into multiple pipes to handle different prefixes in your S3 bucket?
Steven, we are using one giant pipe for the whole bucket. Do you think that’s causing a bottleneck in the notification processing? I didn't realize there was a limit to how many messages a single pipe could handle per second. If we move to a multi-pipe architecture, do we need to set up separate SQS queues for each prefix or can they all share the same notification channel?
Exactly, Richard. That specific metadata function is the fastest way to prove to the DevOps team that the delay is happening before the message even reaches Snowflake.