Copy Data from fabric Lakehouse to snowflake: Schema Auto-create

[BrunoR]

Hi, 
I just posted this message in an existing post (in a reply) but because it was solved I created a new one:

I created a copy job option to copy a lakehouse table to Snowflake and specified my destination table: <my_schema>.<my_table>
The issue is that the copy job try to recreate the schema I provided by running the statement:
"CREATE SCHEMA IF NOT EXISTS ...." in Snowflake.
The copy job ends up thus with a failed status.
The role used (default role for <my_user> ) doesn't allow to create a schema in the given database. 
Also I don't get to see "Additional connection properties" in my Snowflake connection (wrong licence type)?
Without it I can't specify a Pre-copy script that might prevent the "create schema" issue.
So I'm kind of stuck. 
Is there a way to disable the schema auto-create option I'm dealing with here? My Snowflake admin won't allow me to create my own schema like this. 

Bruno

[ajarora]

We are fixing this issue very soon, by not requiring accountadmin permissions due to "create schema if not exists".

For now have a custom role which has this permission.

Sorry for the inconvenience.

[v-kpoloju-msft]

Hi @BrunoR,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @Thomaslleblanc, for his inputs on this thread.

The issue happens because the Copy Data activity automatically tries to run CREATE SCHEMA IF NOT EXISTS in Snowflake before writing data. Since your user role doesn’t have permission to create schemas, the job fails.

Currently, the “auto-create schema” behaviour in the Snowflake connector isn’t configurable, and since you’re not seeing the Additional connection properties section, this might relate to your current Fabric licensing setup or connector experience.

Ask your Snowflake admin to pre-create the target schema (e.g., <my_schema>) and grant your role USAGE and INSERT permissions. Then, update the Copy Data sink to point to that existing schema. If available in your environment, you can add a Pre-copy script like:

USE SCHEMA <database>.<schema>;

to ensure the connector uses the existing schema.

Refer these links:
1. https://learn.microsoft.com/en-us/fabric/data-factory/connector-snowflake-copy-activity 
2. https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[BrunoR]

Hi @v-kpoloju-msft ,
Thanks for your reply.
I already have a schema (and table) that I specified in my copy job. 
The default role of the user have USAGE and INSERT permissions. 
The only things blocking me is this: CREATE SCHEMA IF NOT EXISTS that fails although I specify an existing schema. 
As a workaround, I tried to alter the Copy Job by adding a preCopyScript ("SELECT 1") via the Fabric API but I'm not authorized to do that either.

[v-kpoloju-msft]

Hi @BrunoR,

Thanks for confirming the details that’s really helpful.

Since the schema and table already exist and your Snowflake role has USAGE and INSERT permissions, the issue is indeed caused by the connector still issuing CREATE SCHEMA IF NOT EXISTS. Unfortunately, this behaviour isn’t currently configurable in the Fabric UI or API.

Because you are also unable to add a preCopyScript through the Fabric API, the next best step is to raise a Microsoft Support ticket so the Fabric engineering team can review your workspace logs and confirm whether this is expected connector behaviour or a product limitation.

You can raise a support ticket through this link: Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn 

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @BrunoR,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear. And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[Thomaslleblanc]

create a support ticket with microsoft