Snowflake return error "Number of auto-ingest pipes on location <bucket name> cannot be greater than allowed limit: 50000"

Question

I have a lambda function load data into an AWS GLUE table a few times a day, then at the end, it create an external table on snowflake. the function is running ok for some time but now it starts to return this error to me every now and then: Number of auto-ingest pipes on location <bucket name> cannot be greater than allowed limit: 50000

the create table sql is like the following:

create or replace external table table_name(
row_id STRING as (value:row_id::string), 
created TIMESTAMP as (value:created::timestamp)
...
) with location = @conformedstage/table_name/ file_format = (type = parquet);

I have googled this issue and almost all the answers are referring to sonwpipe, however, it doesn't use snowpipe at all.

any ideas are appreciated

Answer 1

When an external table is created with auto_refresh, Snowflake creates an internal pipe to process the events, and you have probably lots of external tables using the same bucket.

Can you try to set AUTO_REFRESH=false?

create or replace external table table_name(
row_id STRING as (value:row_id::string), 
created TIMESTAMP as (value:created::timestamp)
...
) with location = @conformedstage/table_name/ file_format = (type = parquet) auto_refresh=false;

https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html