How are source schema names reflected in Snowflake?

[pnadolny13]

After digging in a bit this is how I understand it:

• If you set default_target_schema it will override any schema name that came from the mysql source. For example the mysql table schema_1.table_1 gets translated into the stream name schema_1-table_1 and with the setting default_target_schema=my_schema_override it would create a table in snowflake calledtable_1 within the my_schema_override schema. Similarly if you kept the same settings and synced ``schema_2-table_1it would append to thetable_1` table in snowflake like you described.
• if you dont set default_target_schema then it will split the stream name on - and take the second to last value as the schema name. For example schema_1.table_1 would again become the stream schema_1-table_1 and would create a table in snowflake called table_1 within the schema schema_1. Then syncing schema_2-table_1 you'd see another schema in snowflake get created schema_1 within table_1 inside it.
• I also noticed that in the transferwise hub definition we're setting the default_target_schema for you by default https://github.com/meltano/hub/blob/36db3f80f6616a4234dcdf1913ac2766ef01c0c4/_data/meltano/loaders/target-snowflake/transferwise.yml#L86. I wonder if you overrode that to be None if it would act the same way as I described above 🤔 .
• In the meltanolabs variant we aren't setting that value by default so it would work as you expected without modification.

[jlloyd-widen]

@pnadolny13 I think you've got a couple of typos in there. One of which in the second bullet point:

Then syncing schema_2-table_1 you'd see another schema in snowflake get created schema_1 within table_1 inside it.

should probably be:

Then syncing schema_2-table_1 you'd see another schema in snowflake get created: schema_2 with table_1 inside it.

Other than that, I think this is an accurate reflection of the current state.

I'm an advocate for being able to pass the source schema name (i.e., the "second to last value" in the stream name when split on -) to a metadata value in the target table, whether or not you are using the default_target_schema option.

[mjsqu]

I came across this thread as we have encountered a problem with permissions on existing schemas with matching names.

For the scenario:

• SQL Server has a schema called sql_schema
• Snowflake has a schema called sql_schema
• target-snowflake config is:

{
    "schema": "new_schema",
}

If the Snowflake account doesn't have access to the sql_schema then it is unable to create the file format - because the file format schema is determined by:

target-snowflake/target_snowflake/sinks.py

Line 57 in 54020bb

schema = super().schema_name or self.config.get("schema")

I think this should possibly be the other way around, i.e.:

        schema = self.config.get("schema", super().schema_name)

if the user supplies a schema then target tables AND file formats should be routed to that schema - if not, use the schema coming from the stream.

We have got around this by setting default_target_schema but in doing so I'm questioning the need for both a schema and a default_target_schema config property

[edgarrmondragon]

I'm questioning the need for both a schema and a default_target_schema config property

Hi @mjsqu!

I think what could've happened here is simply poor communication, we could certainly revisit and clean up what the SDK does upstream and at the same time unify and simplify the approaches taken both there and here in target-snowflake so they're easier to reason about. I think it's at least worth a bug report in the SDK.

[mjsqu]

Thanks @edgarrmondragon - no doubt you've been notified multiple times, but here's the link so the issue trail's joined together: meltano/sdk#2766