IBM Support

Snowflake destination is failing with "File format 'db.schema.tmp__ does not exist or not authorized" when multiple snowflake stages are configured.

Troubleshooting


Problem

  • Upon reviewing the Snowflake query history page, it appears that Snowflake is attempting to locate a file format that has already been deleted and the same error is reflected into the data collector logs.

  • For example, following 2 file format is being created automatically for two snowflake destination :-

create file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_C387EB456FBC4822B5E1C853BAC8C839" type = CSV FIELD_DELIMITER=',' TRIM_SPACE = true FIELD_OPTIONALLY_ENCLOSED_BY = '\'' ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE';
create file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_5587E2AEB05B41F3A3F5CA472D5F4A9B" type = CSV FIELD_DELIMITER=',' TRIM_SPACE = true FIELD_OPTIONALLY_ENCLOSED_BY = '\'' ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE';

  • Each destination has its own file format. After stopping the pipeline preview, instead of Snowflake deleting the two mentioned file formats, it executes the "drop file format" query four times. We can verify the same from History page.

drop file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_C387EB456FBC4822B5E1C853BAC8C839" => Query Success.
drop file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_C387EB456FBC4822B5E1C853BAC8C839" => Query Failed!
drop file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_5587E2AEB05B41F3A3F5CA472D5F4A9B" => Query Success.
drop file format "STREAMSETS_DB"."AKSHAY_SCHEMA"."SDC_TEMP_5587E2AEB05B41F3A3F5CA472D5F4A9B" => Query Failed!

Symptom

The snowflake destination is failing with the following error when we preview the pipeline when two or more snowflake stage is being configured. There is no data loss due to this errors.

2024-02-22 06:44:32,501 [user:fe5e3ee7-266e-11ec-aa22-2501396a7c58@0ee6e198-266f-11ec-aa22-f58448645727] [pipeline:snowflake (SCH Test Run)/testRun__75bb7f5d-b731-4c65-be5b-ab081e646e82__0ee6e198-266f-11ec-aa22-f58448645727__fe5e3ee7-266e-11ec-aa22-2501396a7c58@0ee6e198-266f-11ec-aa22-f58448645727] [runner:0] [thread:webserver-230] [stage:Snowflake_4] WARN  SnowflakeBaseConfig - Could not drop file format 'SDC_TEMP_55AD8A4992FA4C18AA70FED4C35F3A17':
net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:
File format 'STREAMSETS_DB.AKSHAY_SCHEMA.SDC_TEMP_55AD8A4992FA4C18AA70FED4C35F3A17' does not exist or not authorized.
	at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub(SnowflakeUtil.java:129) ~[snowflake-jdbc-3.13.30.jar:3.13.30]
	at net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowException(SnowflakeUtil.java:70) ~[snowflake-jdbc-3.13.30.jar:3.13.30]
    at net.snowflake.client.core.StmtUtil.pollForOutput(StmtUtil.java:456) ~[snowflake-jdbc-3.13.30.jar:3.13.30]
	at net.snowflake.client.core.StmtUtil.execute(StmtUtil.java:366) ~[snowflake-jdbc-3.13.30.jar:3.13.30]

Resolving The Problem

The snowflake destination and pipelines incorporate retry capabilities to ensure seamless operation from the user's perspective, and considering there is no data loss happening the feature request has been raised.

Feature Request Link:- SX-I-169

Document Location

Worldwide

[{"Line of Business":{"code":"LOB76","label":"Data Platform"},"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSM7CU","label":"IBM StreamSets Data Collector"},"ARM Category":[{"code":"","label":""}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Document Information

Modified date:
15 March 2025

UID

ibm17186093

Page Feedback