OPTIMIZE plus VACUUM minimizes query time on a table of 2,000 one-MB files

DP-700 By islever Editorial Last updated

Optimize performance

Short answer

C OPTIMIZE consolidates the 2,000 one-MB files and VACUUM removes the unreferenced old files, minimizing Table1 query time.

Question

You have a Fabric workspace that contains a lakehouse named Lakehouse1. Lakehouse1 contains a Delta table named Table1. You analyze Table1 and discover that Table1 contains 2,000 Parquet files of 1 MB each. You need to minimize how long it takes to query Table1. What should you do?
  1. Disable V-Order and run the OPTIMIZE command.
  2. Disable V-Order and run the VACUUM command.
  3. Run the OPTIMIZE and VACUUM commands. Correct Answer

Key Takeaways

The small-file problem is fixed by OPTIMIZE; VACUUM cleans up the files OPTIMIZE leaves unreferenced. Keeping V-Order (on by default) preserves the optimized file layout that speeds reads.

Table1's 2,000 Parquet files of 1 MB each create a small-file problem: OPTIMIZE consolidates them into fewer large files for faster scans, and VACUUM then removes the old unreferenced files, minimizing how long queries take.

Common mistake: Disabling V-Order as part of the fix; V-Order improves read performance and is enabled by default, so disabling it would degrade queries rather than speed them up.

Expert Analysis

Why the Answer Is Correct

With 2,000 Parquet files of 1 MB each, every query against Table1 must open thousands of tiny files. Running OPTIMIZE compacts them into fewer, larger files, dramatically reducing scan overhead, and running VACUUM afterwards deletes the old files no longer referenced by the Delta log. Together they minimize query time on Table1.

Why the Other Options Are Wrong

Options A and B both disable V-Order first, which is counterproductive: V-Order is enabled by default and optimizes the Parquet layout for fast reads, so disabling it hurts performance. Option B also uses VACUUM alone, which removes unreferenced files but never consolidates the live small files.

Community Comment Notes

The community is unanimous (C 100). GHill1982 (7 likes) explains OPTIMIZE compacts the small files while VACUUM removes old unneeded versions, and prabhjot (2 likes) echoes the same pairing for storage efficiency and readability.

Official Reference

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-table-maintenance

This link opens the official vendor documentation in a new tab.

Community Votes

C
100%

100% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 3 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 3

  1. GHill1982 👍 7 Answered C
    OPTIMIZE: This will help compact the small Parquet files into larger files, improving the efficiency of queries. VACUUM: This will remove old versions of files that are no longer needed, freeing up storage and further optimizing performance.
  2. prabhjot 👍 2 Answered C
    Optimize command solves the "small file" problem and the "VACUUM" command erases the older versions of the parquet files thus optimizing on storage and better readability
  3. Tuki93 👍 2 Answered C
    Provided answer C is correct

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

A 500-million-row fact table exceeds the F32 Direct Lake guardrail, so increase the capacity to F64

S-grade

Shortcut caching supports only S3/GCS shortcuts and excludes files larger than 1 GB

S-grade

Updating warehouse tables requires at least the Contributor workspace role because Write is not shareable to a Viewer

S-grade

Run T-SQL in a KQL database only by prefixing the query with a -- comment line

S-grade

← Back to DP-700 Study Guide