Does anyone have any insight on what the actual prerequisites are to use target-snowflake?

@nevin_morgan Are you having trouble getting it to work? Are you using our https://gitlab.com/meltano/target-snowflake, https://github.com/datamill-co/target-snowflake, or https://github.com/transferwise/pipelinewise-target-snowflake?

I am using the meltano one @douwe_maan. I set up the DB in snowflake and used the owners credentials to connect from meltano, but it keeps erroring out. Relevant error meesages

CRITICAL (snowflake.connector.errors.ProgrammingError) 002003 (02000): SQL compilation error:
CRITICAL Schema 'TEST."bingads"' does not exist or not authorized.
CRITICAL [SQL: GRANT USAGE ON SCHEMA "TEST"."bingads" TO ROLE MELTANO;]
CRITICAL (Background on this error at: <http://sqlalche.me/e/13/f405>)

@nevin_morgan Just a hunch: Does it work if you explicitly set the

schema

setting to an all-caps

BINGADS

? You can use the

SF_SCHEMA

env var if you like.

I haven't tried, but I can here in a little bit. I will probably fork that and force it to upper since I am pretty sure snowflake wants it to be uppercase

(As I'm sure is clear by now, I know Singer and Meltano pretty well, but Snowflake is also a mystery to me 😛)

My hope was to rely on the automatic schema naming so that I didn't need to modify env per pipeline

Yeah, makes sense. Updating the target to force that works for me, but we could also have Meltano apply the upper conversion using a new

value_processor: upper

setting property (we already have

value_processor: nest_object

, so adding "upper" or "upcase_string" or something to that effect wouldn't be too difficult)

seems that this is the answer

Snowflake uses Standard (case-sensitive) SQL comparison semantics. Snowflake automatically converts unquoted, case-insensitive identifiers to uppercase internally, so an unquoted object name appears in upper case in the Information Schema views.

Interesting! So I'm guessing that https://gitlab.com/meltano/target-snowflake/-/blob/master/target_snowflake/snowflake_loader.py#L185 ends up passing

self.table.schema

unquoted, ignoring the lower case and actually creating an uppercase schema, while https://gitlab.com/meltano/target-snowflake/-/blob/master/target_snowflake/snowflake_loader.py#L394 quotes

self.table.schema

, expecting it to exist in the lowercase version

I definitely like upper casing the schema since it matches what happens if anything passes a non quoted string to snowflake. On the snowflake end it apparently is case insensitive and does a conversion regardless of what is sent to uppercase when an object name isn't a quoted string

(This conversation continued in https://meltano.slack.com/archives/C013Z450LCD/p1596746888047500)