Unexpected error: Object '{schema_name}' already exists during Snowflake CREATE SCHEMA

[aaronsteers]

@Jan Soutusta in slack reported this error:

2023-03-06T08:03:44.213924Z [info     ] sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 002002 (42710): 01aac2a3-0001-008b-0001-aed20033302a: SQL compilation error: cmd_type=elb consumer=True name=target-snowflake-sfdc producer=False stdio=stderr string_id=target-snowflake-sfdc
2023-03-06T08:03:44.214106Z [info     ] Object 'SFDC_INPUT_STAGE' already exists. cmd_type=elb consumer=True name=target-snowflake-sfdc producer=False stdio=stderr string_id=target-snowflake-sfdc
2023-03-06T08:03:44.214274Z [info     ] [SQL: CREATE SCHEMA "SFDC_INPUT_STAGE"] cmd_type=elb consumer=True name=target-snowflake-sfdc producer=False stdio=stderr string_id=target-snowflake-sfdc
2023-03-06T08:03:44.214440Z [info     ] (Background on this error at: https://sqlalche.me/e/14/f405) cmd_type=elb consumer=True name=target-snowflake-sfdc producer=False stdio=stderr string_id=target-snowflake-sfdc

https://meltano.slack.com/archives/C01TCRBBJD7/p1678089929508169?thread_ts=1678088654.698399&cid=C01TCRBBJD7

[aaronsteers]

First thing to confirm is Snowflake permissions. I've seen this error occur when a user cannot see the schema due to permissions, but then fails to create the schema due to it already existing.

Second thing we could double-check is that the schema might exist and just be not located during discovery. A mitigation that doesn't really solve the underlying root cause would be to change the creation script to use CREATE SCHEMA IF NOT EXISTS <name> per https://docs.snowflake.com/en/sql-reference/sql/create-schema

[miloszszymczak]

I've already fixed this issue locally - it's caused by upper- and lowercase handling by the loader. When the SDK checks for the existing schema it compares uppercase and lowercase names of schema resulting in attempt to create a new schema which already exists.

[aaronsteers]

I've already fixed this issue locally - it's caused by upper- and lowercase handling by the loader. When the SDK checks for the existing schema it compares uppercase and lowercase names of schema resulting in attempt to create a new schema which already exists.

Thanks, @miloszszymczak. For casing, general best practice with snowflake is to assume (and prefer) uppercase convention. Sounds like we should definitely make the comparison operation case insensitive (if another contributor hasn't done so already) and it might be worth considering doing one or both of the following:

1. Printing a warning message if the provided schema name is not provided in allcaps.
2. Coerce the schema name to all-caps if it isn't already.

I'm not actively working on this as of now, but if anyone else wants to jump in with a PR, it might be helpful.

cc @pnadolny13, @visch for their thoughts as well.

[pnadolny13]

I think this is a similar issue to the one in target-postgres MeltanoLabs/target-postgres#119. It seems like the code to check existence vs create are using different logic so its causing a mismatch.