V2 Snowflake connector: How to copy data into non-uppercase database objects?
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.
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?