DeltaTable.forName Doesn't work outside of default schema

[higgy7]

I have been using DeltaTable.forName within a function to merge some data into a delta table in a lakehouse. When i don't pass the schema and defaults to dbo the function works correctly. However when i try using a custom schema it fails. The work around seems to be using forPath but should forName work?

This works:

gold_table_name = "abc"

gold_df = DeltaTable.forName(spark, gold_table_name)

This fails

gold_table_name = "xyz.abc"

gold_df = DeltaTable.forName(spark, gold_table_name)

I have tried multiple ways such as gold_table_name = "`xyz.abc`" but nothing seems to work. Using forPath isn't as clean as teh table is registered in the catalog.

[higgy7 · Accepted solution]

Hi, I should expand further on my issues. When using the 3 part name with the forName i can see that it does work. Howveer i run into more issues when trying to use this in conjuction with .merge. Attached is my latest attempt, where i have tried to set the current database. I have also tried using the catalog, scheema and tabe for the gold table name but ran into the same issue.  I get this error:

IllegalArgumentException: java.io.IOException: Invalid input length 3

[BhaveshPatel]

For that, You should always use bronze and silver layer. We don't need a Gold layer. Make as simple as you can to visualize your data.

Bronze Table = Raw data and then silver table use materilized lake view to get an Extract, Transform and Load ( LEFT JOIN ) and For Semantic Model, Use Direct Lake to get your data in Power BI.

For Silver table, Nowadays Materilized Lake Views is available in silver table. 

[v-lgarikapat]

HI @higgy7 ,

Thanks for reaching out to the Microsoft fabric community forum.

@mabdollahi ,

Thanks for your prompt response

 @higgy7 You're right, DeltaTable.forName("abc") works when the table is in the default schema like dbo, but if you're using a custom schema like xyz.abc, it might fail unless you include the full path including the catalog. In environments like Microsoft Fabric, you may need to write it as DeltaTable.forName("lakehouse.xyz.abc") where lakehouse is the catalog, xyz is your schema, and abc is the table. If you're not sure what the catalog is, you can try checking with SHOW TABLES IN xyz. If nothing else works, forPath() is a good backup, but forName() is better if the table is registered in the catalog.

Schema enforcement - Azure Databricks | Microsoft Learn

Lakehouse schemas (Preview) - Microsoft Fabric | Microsoft Learn

Solved: merge or update/insert lakehouse table from anothe... - Microsoft Fabric Community

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[higgy7 · Accepted solution]

As an update on this.  The issue seems to be when i try and use this in conjunction with .merge. The screenshot shows my inputs and my function. When i call it the first time it will run the else and will be successful. However on the second run when the table actually exists. i get the attached error. When i have a search for this error it says DeltaTable.forName doesn't accept a dot-qualified name so how can i pass the fully qualified name? The issue seems to be when i try and use this in conjunction with .merge. The screenshot shows my inputs and my function. When i call it the first time it will run the else and will be successful. However on the second run when the table actually exists. i get the attached error. When i have a search for this error it says DeltaTable.forName doesn't accept a dot-qualified name so how can i pass the fully qualified name?

However, i have used co-pilot and found a workaround. forName DOES NOT seem to work as all. However if i modify the function as seen in the image the path will now work but i would say this is a work around rather than a solution. Is forPath the only solution adn forName just will not work?

[v-lgarikapat]

Hi @higgy7 ,

Thanks for the confirmation and for sharing the workaround. It's really appreciated

Use forName if your tables are persistently registered, especially in a controlled catalog environment.

Use forPath when working with raw or temporary data or when you want to avoid catalog dependencies.

Please consider the Accepted Solution . This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana.

[higgy7]

Hi Lakshmi,

So from my testing " In environments like Microsoft Fabric, you may need to write it as DeltaTable.forName("lakehouse.xyz.abc") where lakehouse is the catalog, xyz is your schema, and abc is the table." will not work as DeltaTable.forName doesn't accept a dot-qualified name therefore there is no way to use a fully qualified catalog.schema.table in Fabric even if the table is registered and a managed table within the tables folder? The only option outside of the default schema is to use the forPath?

[v-lgarikapat]

Hi @higgy7 ,

I wanted to follow up and confirm whether you’ve had a chance to review the information we shared. If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know — we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi Narayana

[higgy7 · Accepted solution]

Hi, I should expand further on my issues. When using the 3 part name with the forName i can see that it does work. Howveer i run into more issues when trying to use this in conjuction with .merge. Attached is my latest attempt, where i have tried to set the current database. I have also tried using the catalog, scheema and tabe for the gold table name but ran into the same issue.  I get this error:

IllegalArgumentException: java.io.IOException: Invalid input length 3

[mabdollahi]

Hi @higgy7 ,

Have you tried setting the current db before calling forName?
It may help to set the schema name(xyz), then calling forName.
spark.catalog.setCurrentDatabase(schema) --> 

spark.catalog.setCurrentDatabase("xyz")
gold_df = DeltaTable.forName(spark, "abc")

Regards,

Mehrdad Abdollahi