topic Re: DELETE Managed DELTA Table in Data Engineering

DELETE Managed DELTA Table

smpa01 — Tue, 13 Aug 2024 03:11:56 GMT

During a DEV, I am trying to ingest rows in a delta table and then refining the code further and then delete the rows from the table before running the next ingestion for a follow-up code DEV.

I am observing that even though, I run the following, the table is not entirely deleting all the rows in a lot of instances. When I refresh the lakehouse and that delta table to verify if it actually deleted all the rows, to my suprise I see the default view of 1000 rows. In reality, I expect it to be 0 rows.

%%sql --DELETE all rows DELETE FROM Staging.Lakehouse.dev_ingestion_tbl

It is causing me (time) to manually Delete (SQL TRUNCATE) the table from Lakehouse, run a schema building code to build an empty table to be ready for next test ingestion for enhanced DEV code.

Why does it keep on happening. I expect DELETE to completely delete all the rows each time I run it, ithout having me to verify the action.

Do I need to run (e.g. VACCUM) anything in conjunction to DELETE to make sure it does what I expect it to do.

Thank you in advance.

Re: DELETE Managed DELTA Table

Anonymous — Tue, 13 Aug 2024 07:57:57 GMT

Hi, @smpa01

After testing, after executing this T-SQL, the data in this table can be deleted normally, and then refresh, there is no data in the table in Lakehouse.Delete works fine in Notebook. Make sure you have permission to modify the table, retry deleting and refreshing.

Notebook:

Lakehouse:

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: DELETE Managed DELTA Table

smpa01 — Tue, 13 Aug 2024 13:02:23 GMT

@Anonymous I see this however.

" Make sure you have permission to modify the table, retry deleting and refreshing" - yes, I am the admin

Re: DELETE Managed DELTA Table

frithjof_v — Wed, 14 Aug 2024 06:16:23 GMT

Perhaps it's the sync between Lakehouse and SQL Analytics Endpoint which is taking a long time in your case.

What if you run

%%sql

SELECT Count(*) FROM tableName

in the Notebook in the next cell after you run the truncate or delete rows. Perhaps this will show that the table is indeed empty, even if the SQL Analytics Endpoint doesn't know that yet.

https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance

Re: DELETE Managed DELTA Table

smpa01 — Wed, 14 Aug 2024 13:55:30 GMT

@frithjof_v "What if you run" - it will probably show 0. But it is extremely annoying.

But that is not onlly issue. assuming there is a lag between sql end point and lakehouse, I do expect the the lakehouse table view to show a 0 row table. Bu that is not the case sadly. Also, I don't want to truncate the table as it holds the required schema and partitions. I just need it to delete all the rows.

I am currently not being able to verify that either by SQL end point (does not show 0) or visually by looking at the lakehouse explorer (does not show an empty table)

Re: DELETE Managed DELTA Table

frithjof_v — Wed, 14 Aug 2024 15:17:55 GMT

Did you try clicking all the refresh buttons in the Lakehouse view?

Re: DELETE Managed DELTA Table

smpa01 — Wed, 14 Aug 2024 16:13:58 GMT

Goes without saying

Re: DELETE Managed DELTA Table

frithjof_v — Wed, 14 Aug 2024 16:32:51 GMT

I'd suggest you create a support ticket to highlight this issue.

Also look at the recommendations in the docs if there's anything to be gained from there: https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-analytics-endpoint-performance

Perhaps you could also create an Idea at Fabrics Idea to highlight the need for faster sync between the different user interfaces.