V2 Snowflake connector: How to copy data into non-uppercase database objects?

Raphael Fritz 5 Reputation points
2024-03-20T13:37:27.2433333+00:00

I just switched to the new Snowflake V2-connector in ADF but ran into problems when trying to copy data into a table that has a non-uppercase name.

To give some context: I'm using a single dataset for my connection to Snowflake and parametrized schema and tables names so that I can specify the exact schema and table in the Copy activity. In my pipelines, I am using both uppercase and lowercase table names, depending on the object in Snowflake.

User's image

In the old implementation of the connector, all schema and table names had been wrapped in double quotes by the connector when sending a query to Snowflake, which made the table name case-sensitive (resulting in COPY INTO "EXAMPLE_SCHEMA"."example_table"). The new implementation doesn't seem to do that: When I use the value 'example_table' for the Table parameter in the Copy activity, the query that is executed in Snowflake is actually COPY INTO EXAMPLE_SCHEMA.example_table which is of course interpreted as uppercase by Snowflake. However, when I wrap the string of the parameter in double quotes (as seen in the screenshot), the query that Snowflake tries to execute becomes COPY INTO "EXAMPLE_SCHEMA".""example_table"" , so suddenly the double quotes are back in the business but the SQL statement becomes invalid.

Using single quotes or escaped double quotes to quote the lowercase table name doesn't help either.

This behavior is not the same in a Lookup activity. The Lookup activity on the same database object doesn't wrap anything in double quotes, which produces the query SELECT * FROM EXAMPLE_SCHEMA."example_table" . That's correct.

When using the "Preview data" function in the dataset, the behavior is the same as in the Lookup: 'example_table' results in SELECT * FROM EXAMPLE_SCHEMA.example_table while '"example_table"' results in SELECT * FROM EXAMPLE_SCHEMA."example_table".

Is it possible that the behavior of the Copy activity is inconsistent? Or am I missing something?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,020 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.