Hey @notwarte ,

Maybe I can help you.

There are several likely factors contributing to this:

1. Predictive Optimization runs background jobs (OPTIMIZE, VACUM, ANALYZE) that rewrite data files to improve query performance. These optimized files coexist with the older files until vacuum cleans them up. This means that for a period of time, both the new and old data files are stored, increasing storage usage.

2. The DESCRIBE DETAIL command only reflects the current active data files. It does not include old files that have been replaced or marked for deletion. But Azure Storage Explorer shows everything that exists physically in the storage, including older data files, transaction logs, and temporary files.

3. Materialized views are also stored as physical Delta tables. They may not be included in your script if it only loops over regular tables. Since these views are refreshed automatically and can contain a lot of data, they could be taking up a substantial amount of space.

4. Temporary or intermediate files left behind from operations like OPTIMIZE, MERGE, or COPY INTO can also accumulate if they are not cleaned up, especially if jobs fail or get interrupted.

To investigate further, I suggest:

• Make sure your script includes materialized views in addition to regular tables.

• Use Azure Storage Explorer to sort folders by size or modification date. This will help you identify which datasets are generating the most residual data.
• Run VACUUM with the DRY RUN option on key tables to see how many stale files are still there
• Consider lowering the vacuum retention period if your use case allows it, especially for fast-changing tables.

Best, 🙂

Isi