Snowflake Snowpipe not working , "executionState":"STOPPED_MISSING_TABLE"

Question

I am using Snowpipe with Auto Ingest True . I have followed all the steps, but When I upload files in S3 , it does not load into Snowflake. I checked status with below query:

select system$pipe_status('DB.PUBLIC.mypipe');


{"executionState":"STOPPED_MISSING_TABLE","pendingFileCount":0,"notificationChannelName":"arn:aws:sqs:region:xxxx:xxxx","numOutstandingMessagesOnChannel":0,"lastReceivedMessageTimestamp":"2021-03-29T13:00:07.443Z","lastForwardedMessageTimestamp":"2021-03-29T13:00:07.443Z"}

It looks like it is not able to find the target table, but table exists and current user has access to table. When I run just the underlying Copy, it works fine and files are loaded.

Can someone suggest what can be issue.

create or replace pipe DB.public.mypipe auto_ingest=true as
  copy into DB.public.table
  from @DB.public.table
  file_format = (type = 'CSV' error_on_column_count_mismatch=false)  ON_ERROR="CONTINUE";

there is a little mismatch between the two pieces of code in the question: is the table name DB.public.S3_Snowpipe_new or DB.public.table? Either way, have you checked your stage @DB.public.table? you can run a SHOW STAGES IN SCHEMA public; to do so. — Francesco Quaratino
I have fixed it now. Since I removed actual table name while posting this so it was out of sync. It matches now. Also, I can see stage using Show stages. Also, when I run the copy command of Snowpipe manually it works and loads the data. — PythonDeveloper

FKayani FKayani · Accepted Answer · 2021-03-30T12:25:31

The STOPPED_MISSING_TABLE error message usually refers to the insufficient privilege for the role to access the table in question.

The necessary privileges required for the role to perform the tasks per the document link below.

https://docs.snowflake.com/en/user-guide/data-load-snowpipe-auto-s3.html#step-3-configure-security

Snowflake Snowpipe not working , "executionState":"STOPPED_MISSING_TABLE"

1 Answers