How to optimize Delta tables in a Lakehouse: step by step

Over time, a Delta table in a Lakehouse builds up many small Parquet files, the result of frequent loads and updates. Lots of small files make reads slow and waste compute resources. Optimizing Delta tables in a Lakehouse with the OPTIMIZE command compacts those files and, together with VACUUM, removes the old ones that are no longer needed — keeping your queries fast.

Prerequisites

• A Microsoft Fabric workspace with active capacity (trial or paid).
• An existing Lakehouse with at least one Delta table (for example, loaded from a CSV).
• Permissions to run maintenance tasks on that Lakehouse.
• Basic Spark SQL knowledge, only if you choose the notebook path.

Step 1: Understand when to optimize

The most common problem has a name: the "small files" problem. Each data load can create dozens of small Parquet files, and when the engine reads the table it has to open all of them — which is slow. The OPTIMIZE command fixes this by merging many small files into a few large ones. The V-Order option goes further: it sorts, encodes and compresses the data so engines such as Power BI in Direct Lake and the SQL analytics endpoint can read faster. A good rule of thumb is to run maintenance after large loads or updates, or whenever you notice queries getting slower.

Step 2: Run OPTIMIZE and V-Order from the interface

The simplest way needs no code. Open your Lakehouse and go to the tab that shows the tables. Hover over the table you want to optimize, click the three dots (…) and choose Maintenance. In the dialog that appears, set the following:

1. Turn on Run OPTIMIZE command now to compact the files.
2. Check the Apply V-Order box if you want the extra read gain. V-Order has about a 15% impact on write time but can deliver up to 50% more compression.
3. If you also want to clean up old files now, enable VACUUM and confirm the retention threshold.
4. Click Run now.

Fabric launches a Spark job in the background. You can close the dialog and follow the progress in the run monitor.

Step 3: The notebook alternative with Spark SQL

If you prefer to automate, you can do the same in a notebook attached to the Lakehouse. Create a notebook, make sure the Lakehouse is attached in the left pane, and run a SQL cell. Replace vendas with the name of your table:

%%sql
OPTIMIZE vendas;

This command compacts the table's small files. On write-heavy tables, running OPTIMIZE regularly prevents performance from degrading over time.

Tip: don't optimize for the sake of it. On small or rarely written tables, running OPTIMIZE too often brings no visible gains and wastes compute.

Step 4: Clean up old files with VACUUM

OPTIMIZE creates new files, but the old ones stay around to support Delta Lake time travel. To free up space, VACUUM removes files that are no longer referenced and that are past the retention period. The default value is seven days. In a notebook cell:

%%sql
VACUUM vendas RETAIN 168 HOURS;

168 hours equals seven days. Do not set a value below seven days without understanding the impact: you can break time travel and affect readers in progress. The recommended order is simple — run OPTIMIZE first and only then VACUUM.

Check the result

To confirm the optimization worked, check how many files the table has now. In a notebook, run:

%%sql
DESCRIBE DETAIL vendas;

Look at the numFiles column: after OPTIMIZE it should be much smaller than before, while sizeInBytes reflects larger, more efficient files. In practice, you will notice faster queries and reports. You can also confirm in the Fabric monitor that the Spark job finished successfully.

Conclusion

With OPTIMIZE, V-Order and VACUUM you have everything you need to keep your Lakehouse Delta tables fast and tidy. The next step is to make it automatic: use the Lakehouse Maintenance activity in a Data Factory pipeline, or schedule a notebook to run after your bigger loads. How often do you think your busiest tables need maintenance — daily, weekly, or only after large loads?