Lakehouse tables still visible in SQL endpoint view - how do I remove them?

[nioj2S]

I had tables in my lakehouse which I right-clicked and deleted. However, they are still listed in the lakehouse when I look at the SQL analytics endpoint view, as well as when I do dataflow gen2 and they are visible in the list when connecting a lakehouse:

It doesn't seem to be issues with refreshing of the lakehouse, as they have been there for weeks since I deleted them.

Lakehouse view shows this:

... just a list of tables collected from source under "Tables" and under "Files" there is nothing. But when I look at it from the SQL analytics endpoint view, I can still see the old schema (FIRMA1) and table names that I have deleted:

When I click the table to show data it just loads for a while until I cancel:

Why are these ghost tables still there and how can I remove them completely?

[v-pnaroju-msft · Accepted solution]

Hi nioj2S,

Kindly contact your tenant administrator or Fabric administrator for further assistance in resolving the issue.

If the issue persists, we sincerely recommend reaching out to the Microsoft Support Team through the link provided below. Submitting a support ticket will allow you to receive personalized insights related to your account and potential resolutions:
Microsoft Fabric Support and Status | Microsoft Fabric

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[nioj2S · Accepted solution]

I was in touch with Microsoft Support and the issue was that the original dataflow had managed to create a schema in the lakehouse, which was a lakehouse that did not support schemas. The steps they came up with to resolve the issue was:

1 - Create a Lakehouse with schema support.

2 - Using a Fabric notebook:

2.1 - Create the FIRMA1 schema in the new Lakehouse.

2.2 - Create a table called bl_project in the new schema with only 1 column.

2.3 - Copy the contents of the folder into the Lakehouse with the orphan tables. 

3 - Wait for metadata synchronization to occur and check whether the orphan table returns the expected test column.

4 - Using a Fabric notebook:

4.1 - In the Lakehouse with the orphan tables, delete the bl_project folder.

5 - After metadata synchronization, check whether the bl_project table was removed.

6 - Repeat this process for all orphan tables.

7 - At the end, delete the FIRMA1 schema from the SQL endpoint.

Also, there was some issue with casing, so we had to use a piece of code to make sure that Spark ignored/didn't ignore capital ("BL_PROJECT" vs "bl_project").

This solved the issue. 🙂

[v-pnaroju-msft]

Hi nioj2S,

Thank you for the update.

If the support team provides a solution, please share it with the community. This will assist others facing similar challenges and benefit the broader community.

Thank you.

[v-pnaroju-msft]

Hi nioj2S,

We are following up to check whether you have raised the support ticket. If you have already done so, we kindly request you to share your feedback on the issue raised.
If a solution has been provided, we would appreciate it if you could share it with the community and mark it as the accepted solution. This will help others facing similar challenges and benefit the broader community.

Thank you.

[nioj2S]

Hi! Support ticket is raised, three people are working on it as it seems to be a very unusual bug. No solution as of today.

[v-pnaroju-msft]

Hi nioj2S,

Based on my understanding, the issue may be caused by external policies applied to the resource, resulting in a denial of permission to drop the table, as indicated in the attached screenshot:

Kindly check with your Organisation Administrator or Tenant Administrator to verify if any external policies have been enforced on the resource.

If the issue persists, we recommend reaching out to the Microsoft Support Team through the link provided below. Raising a support ticket will allow you to receive personalised insights related to your account and possible resolutions:
Microsoft Fabric Support and Status | Microsoft Fabric

If you find the response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.
Thank you.

[v-pnaroju-msft · Accepted solution]

Hi nioj2S,

Kindly contact your tenant administrator or Fabric administrator for further assistance in resolving the issue.

If the issue persists, we sincerely recommend reaching out to the Microsoft Support Team through the link provided below. Submitting a support ticket will allow you to receive personalized insights related to your account and potential resolutions:
Microsoft Fabric Support and Status | Microsoft Fabric

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[nioj2S]

I have already contacted the Fabric admin and looked for role assignments, but we saw nothing obvious, as I am already a workspace admin. Please provide more information about what they Fabric admin should do and where in order to give me the correct permissions to delete these ghost tables.

[v-pnaroju-msft]

Hi nioj2S,

Being a Workspace Admin in Fabric does not automatically confer permissions to drop tables or schemas in the SQL Analytics endpoint.

To drop a table or schema, you must have CONTROL permissions on the table, schema, and database or be a member of the DB_OWNER role.

If the necessary permissions are not granted, a SQL administrator must explicitly assign them using the SQL GRANT CONTROL command or by adding the user to the DB_OWNER role. SQL Server permissions, including ALTER, DELETE, and CONTROL, are essential for modifying database objects.

If you continue to face restrictions despite these measures, kindly reach out to your tenant administrator or Fabric administrator for further assistance.

If you find our response helpful, please mark it as the accepted solution and provide kudos. Your feedback will assist other community members encountering similar queries.

Thank you.

[nioj2S]

Couldn't find anything clear in the Azure admin portal, so I accessed the lakehouse via Azure Data Studio, gave myself access to delete but still nothing. I have permission_name "ALTER", "CONTROL", "DELETE" etc. and when I run "CROP TABLE [FIRMA1].[BL_PROJECT]" I get the same error message, it does not exist or I don't have permission.

[v-pnaroju-msft]

Hi nioj2S,

Thank you for your update.

Before deleting a schema, it is essential to first remove any dependent objects within the schema. Failure to do so will result in the DROP SCHEMA command failing.

Based on the error message in the screenshot you have shared, it appears that you do not have the necessary permissions to delete the specific table (e.g., BL_PROJECT), which is a dependent object within the schema FIRMA1. If you do not have the required permissions to delete tables or schemas, kindly contact your administrator and request access. Additionally, please check with your administrator for any external policies that may be applied to the resource.

Once all policies have been reviewed and the required permissions have been granted, please re-run the queries and verify the results.

If you find our response helpful, we request you to mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[nioj2S]

I am a workspace admin, is that not enough?

[v-pnaroju-msft]

Hi nioj2S,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft]

Hi @nioj2S,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

The Lakehouse and the SQL Endpoint in Microsoft Fabric represent different ways of interacting with the same underlying data. Below is an explanation of their functionalities and steps to resolve your issue:

1. Lakehouse View:
• This provides a file-system-oriented perspective where data is organized into folders and files, such as Parquet and Delta Lake formats.
• Actions performed in the Lakehouse View primarily affect the underlying data storage.
2. SQL Endpoint:
• This offers a SQL-based interface (using T-SQL) to query and manage data in the Lakehouse.
• The SQL Endpoint maintains its own metadata catalog, which includes schemas, tables, and views, independent of the file system structure.

When you delete a table through the Lakehouse View, only the associated data files are removed. However, the schema associated with the table remains intact in the SQL Endpoint's metadata. This behavior is intentional, as it allows users to recreate tables or reuse schemas for other purposes.

• Schemas in SQL are logical containers for database objects, such as tables, views, and stored procedures.
• Even if a schema is empty (i.e., contains no objects), it still exists as a logical entity in the SQL Endpoint's metadata.
• The Lakehouse View focuses on data files and does not provide an option to delete schemas, as it does not manage SQL metadata.

To remove the schema, you need to use T-SQL within the SQL Endpoint. Please follow the steps below:

1. Open a query editor connected to your Lakehouse's SQL Endpoint.
2. Execute the following T-SQL command to delete the schema:
   DROP SCHEMA SchemaName;
   Replace SchemaName with the actual name of the schema you wish to delete.
3. If the schema contains any other objects (ex: views or stored procedures), the DROP SCHEMA command will fail. You must delete those objects first using appropriate T-SQL commands.
   

If you find the response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[Ramanareddyp]

Hi Friends,

Any luck on this?

Thanks

Ramana

[nioj2S]

Hello!

It does not seem to work.

Trying to drop schema via SQL query in the SQL analytics endpoint:

Trying to drop table the same way:

Trying to drop schema via notebook using Spark SQL:

Trying to drop table via notebook using Spark SQL: