topic Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse in Data Warehouse

Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Wed, 04 Dec 2024 15:35:52 GMT

Hi team,

I hope this email finds you well.

I am encountering an issue in MS Fabric Warehouse where old Parquet files are not being deleted after executing an INSERT OVERWRITE ( Delete & Insert) operation. Instead of replacing the existing data, the new files are created alongside the old ones, resulting in redundant storage.

Could you please investigate this issue and clarify:

Is this the expected behavior for INSERT OVERWRITE in the Warehouse?
If not, how can we ensure that old Parquet files are cleaned up automatically after an overwrite?
Are there any recommended workarounds or configurations to address this issue?

Let me know if you need further details or logs to assist with the investigation.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

lbendlin — Wed, 04 Dec 2024 20:48:01 GMT

Are you sure this is a Warehouse issue?

Microsoft Fabric Lakehouse OPTIMIZE and VACUUM for Table Maintenance

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Thu, 05 Dec 2024 04:17:16 GMT

Yes it's a MS Fabric warehouse issue.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

Kanna123 — Thu, 05 Dec 2024 04:19:33 GMT

Hi lbendlin

This is a warehousing problem only. I'm having the identical storage problem with my ongoing project.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

govindarajan_d — Thu, 05 Dec 2024 05:37:22 GMT

Hi datakulture,

Delta tables have version mechanism and that is how it supports this feature - Time travel in Warehouse within Microsoft Fabric - Microsoft Fabric | Microsoft Learn.

Using time travel feature, you can query and clone data as it was in the past with a limit of upto 30 days. So the retention time is 30 days. While in Lakehouse you can manually run VACUUM to clear up the old files with whichever retention period you need but in Warehouse currently it is not possible.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

Anonymous — Thu, 05 Dec 2024 06:33:35 GMT

Thank govindarajan_d for the valuable insights and document link.

Here is my understanding:

This is expected behavior from the perspective of delta tables and parquet files. Because the parquet file cannot be modified, all operations on the data, including inserts, deletes, and updates, will generate new parquet files instead of updating the existing parquet file. Meanwhile, due to the time travel function, old parquet files cannot be deleted, making it possible to query prior versions of data.

Currently, the data retention for time travel queries in Fabric Warehouses is thirty calendar days. However Fabric doesn't allow us to modify the retention duration for Warehouses.

Best Regards,
Jing
Community Support Team

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Fri, 06 Dec 2024 06:17:42 GMT

Thanks for your valuable insights.

The data is still persisting in Delta tables for more than 30 days. Please find the screenshot below for your reference. The August 2024 data is still exists in the one lake.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Wed, 11 Dec 2024 11:28:39 GMT

Hello, everyone,

Any updates on this? My One Lake Storage is growing enormously every day, and we need a solution as soon as possible. I look forward to hearing from you.

Thanks!

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Wed, 11 Dec 2024 11:38:13 GMT

Hi Jing,

We have data older than 30 days, which is causing the enormous growth in OneLake. Please find the below screenshot for your references.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

govindarajan_d — Wed, 11 Dec 2024 11:53:06 GMT

datakulture How are you saying this particular folder is from Aug? The folders that you see most likely would be partitions. And a file generated in Aug doesn't mean that it is up for deletion. Let's say you deleted some records or did some OPTIMIZE on top of a delta table that reduced the number of files, if you run VACUUM and if the file is older than retention period, then it will be deleted.

But if you still believe your delta table is not working properly, I would recommend creating a MS support ticket.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Wed, 11 Dec 2024 12:11:18 GMT

govindarajan_d

Firstly, since it's a Fabric Warehouse, VACUUM is completely ruled out.

I am performing a truncate-and-load operation for the particular table, and the folder belongs to that specific table. Auto-deletion should occur for files older than 30 days, but that is not happening currently. Let me reach out to Microsoft for a resolution. Thanks.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

datakulture — Wed, 11 Dec 2024 12:13:05 GMT

govindarajan_d Question - How are you saying this particular folder is from Aug? Please refer above screenshot.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

govindarajan_d — Wed, 11 Dec 2024 12:16:59 GMT

datakulture Yeah I understand VACUUM is not possible manually, but what I meant was the automatic run process. If it is a truncate and load operation, then definitely the previous version files should be there if it is older than 30 days.

Re: Issue with Old Parquet Files Persisting After INSERT OVERWRITE in MS Fabric Warehouse

mikeburek — Wed, 21 May 2025 19:09:12 GMT

I had the same problem with some staging tables that have a truncate and load pattern.

What I'm doing now is to drop the table and recreate it. That appears to have gotten rid of all the delta parquet history files. This does mean also recreating any indexes, and any table-level permissions, so it's not the normal pattern on a T-SQL database, but the Fabric Warehouse is not a T-SQL database.

I used this to find the real size of the Fabric Warehouse, since the Azure Storage Explorer doesn't count it all.

"Whats the size of my Fabric Workspace / Lakehouse / Warehouse" - Mark Pryce-Maher

Another thought I had was to put the staging table in a Fabric Lakehouse, because that should be reachable from T-SQL in the Fabric Warehouse using a 3-part name. Then I would be able to adjust the retention and/or manually run the vacuum command. But the drop and recreate was fully T-SQL and had less setup. I do have an existing Fabric Lakehouse in the same workspace, but I didn't fully look into if adjusting the retention was table specific, or applies to the whole Lakehouse, meaning I might need a Lakehouse solely for staging tables.