Load data to different schema in Fabric Lakehouse

[BashirIsmail]

Greeting all !
When defining a Lakehouse with a schema as the destination in a Copy activity within a Fabric pipeline like this Mylakehouse.mySchema.myTable, I encountered an error indicating that the dot (.) in the schema.table format is being recognized as part of the table name, which is not allowed.

How can I upload a table to a specific schema in a Fabric Lakehouse, rather than defaulting to the dbo schema?

[Anonymous · Accepted solution]

Hi @BashirIsmail ,

Currently, the default behavior is to place tables in the dbo schema, and there isn’t direct support for specifying a different schema during the Copy activity. But I think you can work around this limitation by using the SQL endpoint to move tables to a different schema after they have been created.

You can follow the steps below:

1. Create the table in the dbo schema using your Copy activity.

2. Use the SQL endpoint to transfer the table to your desired schema.

ALTER SCHEMA mySchema TRANSFER dbo.myTable;

Also I think you can look at this topic: Solved: Schema on lakehouse sql endpoint - Microsoft Fabric Community

I think this document may help you too: Lake database in serverless SQL pools - Azure Synapse Analytics | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[frithjof_v · Accepted solution]

@BashirIsmail 

Click '+ New'

Then add a schema name (perhaps it is best to try only lowercase letters, something like 'myschema') and a table name

Click 'Create'

Now it looks like this:

This works for me.

Perhaps you should also go to Mapping and click 'Import schemas' (I don't know if this is necessary):

Still, the Lakehouse schemas (preview) has some important limitations: Lakehouse schemas (Preview) - Microsoft Fabric | Microsoft Learn

I think preview features are generally not recommended for use in production scenarios.

[tkiwi]

Hi, this is not working for me 😞 

[frithjof_v]

Hi @tkiwi ,

Could you describe step by step your process and if possible share screenshots? (remember to not reveal any sensitive information)

It can make it easier for others community members to try to replicate the issue you are facing and attempt to solve your issue.

Has your lakehouse enabled the schemas (preview) feature?

[tkiwi]

Hi,

my screenshots would look like yours 🙂

I tested every possible solution

1) create schema first in lakehouse

2) create schema by pipeline (Button "New" in Copy Activity)

3) name of schema only letters in lowercase

[frithjof_v]

Hm... Sorry to hear that. Maybe not rolled out in all regions yet. 

Anyway, the Lakehouse schemas feature has some important limitations at the moment and it is only a preview feature so I probably wouldn't have used it anyway: 

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-schemas#public-preview-limitatio...

[Anonymous]

Hi @BashirIsmail ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

[Anonymous · Accepted solution]

Hi @BashirIsmail ,

Currently, the default behavior is to place tables in the dbo schema, and there isn’t direct support for specifying a different schema during the Copy activity. But I think you can work around this limitation by using the SQL endpoint to move tables to a different schema after they have been created.

You can follow the steps below:

1. Create the table in the dbo schema using your Copy activity.

2. Use the SQL endpoint to transfer the table to your desired schema.

ALTER SCHEMA mySchema TRANSFER dbo.myTable;

Also I think you can look at this topic: Solved: Schema on lakehouse sql endpoint - Microsoft Fabric Community

I think this document may help you too: Lake database in serverless SQL pools - Azure Synapse Analytics | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.