cc @cjohnhanson @edgarrmondragon

Fixed in 0.9.0

@edgarrmondragon I'm still getting errors when I try to sync a table called "ORDER". When I debug, strangely it doesnt seem like the quoted_name function is doing anything for the fully qualified name or even just the table name.

I'm also remembering now how tricky this casing and quoting logic is. What I'm observing is that full_table_name should be where we add these quotes so that its reflected across the merge/alter/update/etc. statements but the problem is that it seems the SQL is somehow being executed using different methods where some cases automatically add these quotes (i.e. the create table statement) but others do not (merge/alter/etc.). This makes it tricky because I tried changing the full_table_name property to add quotes when needed but that results in create statements like CREATE TABLE STAGING_RAW.TAP_JAFFLE_SHOP."""ORDER""".

For example I hard coded the merge statement to properly quote 'merge into STAGING_RAW.TAP_JAFFLE_SHOP."ORDER" d using ' and everything works but if I then drop a column from the table and run the sync again I get this error because theres other places like altering that are building SQL that arent yet handled:

sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 001003 (42000): 01b5edf2-0705-1fe2-0051-5c8309cf89f2: SQL compilation error:
syntax error line 1 at position 40 unexpected 'ORDER'.
[SQL: ALTER TABLE STAGING_RAW.TAP_JAFFLE_SHOP.ORDER ADD COLUMN store_id VARCHAR]
(Background on this error at: https://sqlalche.me/e/14/f405)

When the table is created at this point in the SDK connector class the full_table_name is unquoted 'STAGING_RAW.TAP_JAFFLE_SHOP.ORDER' (and the results of parse_full_table_name are also still not quoted) but the result is an info log showing that it automatically quoted the schema and table name 2024-07-26 12:01:14,077 | INFO | snowflake.connector.cursor | query: [CREATE TABLE "TAP_JAFFLE_SHOP"."ORDER" ( id VARCHAR NOT NULL, customer VARCHAR, ...].

I think the SDK needs to either use native sqlalchemy methods that automatically quote (i.e. stop building SQL statements manually) or stop using the native method thats auto quoting. I think the only place auto quoting is the create table statement and as a user its much easier to manually build SQL strings like we do for merge/copy/etc. in this target, so I'd prefer to avoid auto quoting in the create table statement. This way I can handle the quoting myself in the table_name or conform_name methods. What do you think?

@edgarrmondragon I gave this another shot and it doesnt seem like an easy fix. I was attempting to escape the reserved word consistently but now the problem is that full_table_name is being passed around and parsed to retrieve the table name string. That string would have a double quote around it so using it in any sqlalchemy functions like inspect arent working because the table isnt found. Again because sqlalchemy expects to do the escaping but we've already done it.

Yeah I've also been trying to replace our stringified SQL constructions with SQLAlchemy-native ones, but it's indeed painful to work around the full_table_name being passed instead of a more workable Table instance without a larger refactor 😬.

@pnadolny13 I think I got at least a path towards a solution in meltano/sdk#2601 and #242 🎉

https://pypi.org/project/meltanolabs-target-snowflake/0.10.0rc1/