Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

20 min

Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions

Keep Delta tables fast and small with V-Order, compaction, vacuum and sensible partitions.

By the end of this lesson you can

  • Explain the small-file problem and fix it with OPTIMIZE and Z-ORDER
  • Decide when V-Order helps, and set it at session, table or write level
  • Run VACUUM safely and explain what retention does to time travel
  • Pick a good partition column and spot over-partitioning

From earlier lessons · 1 of 2

An Eventhouse batch load failed. Which command shows the cause and whether a retry might work?

Pick one answer.

From earlier lessons · 2 of 2

Two Warehouse updates touch different rows of one table at the same time. One fails with an update conflict. Why?

Pick one answer.

[250][255]

The Northwind Rides trip table gets new scooter events every minute. After three months, the ops dashboard takes 40 seconds to load. The table has 200,000 tiny files, and OneLake storage is three times the data's real size. Nothing is broken. The table just needs looking after.

[4]

Open the box: the small-file problem

Exam depth: know it cold.

A Delta table is a folder of Parquet data files plus a transaction log, _delta_log, that lists which files are current.

Every write adds new files. Frequent small writes, such as streaming, create thousands of small files. Each file costs time to open and track, so reads slow down.

Updates and deletes don't edit files in place. They write new files and mark the old ones as removed. The old files stay on disk for time travel, so storage grows.

That gives two separate jobs:

  • Too many small current files: compact them with OPTIMIZE.
  • Too many old, unreferenced files: delete them with VACUUM.

[259][260][266]

Rendering diagram…
OPTIMIZE rewrites many small files into a few large ones. VACUUM later deletes the old files once they pass the retention window.

[259][266]

OPTIMIZE: bin compaction

Exam depth: know it cold.

OPTIMIZE groups small files into bins and rewrites each bin as one larger file. This is bin compaction. The default target is 1 GB per file. Runtime 2.0 adapts the target to the table's size instead, from 128 MB for small tables.

Running it twice is safe: files that are already large are left alone. It returns metrics such as numFilesRemoved and numFilesAdded, which you can also see later in DESCRIBE HISTORY.

OPTIMIZE and VACUUM are Spark SQL commands. Run them in a Notebook, a Spark job definition or the Lakehouse Maintenance dialog. They don't run in the SQL analytics endpoint or the Warehouse editor.

A Warehouse compacts its own tables automatically. Manual maintenance is a Lakehouse job.

[259][260][262][264]

sql
-- Compact small files
OPTIMIZE dbo.trips;
-- Compact and colocate rows by the columns you filter on
OPTIMIZE dbo.trips ZORDER BY (city, trip_date);
-- Compact and apply V-Order to the rewritten files
OPTIMIZE dbo.trips VORDER;
-- All three at once
OPTIMIZE dbo.trips ZORDER BY (city, trip_date) VORDER;
Spark SQL in a notebook. With both options, Spark runs bin compaction, then Z-Order, then V-Order.

[259]

Z-ORDER: skip files on filters

Exam depth: know it cold.

ZORDER BY is an option of OPTIMIZE. It puts rows with similar values in the same files. Each file then covers a narrow range of values, so a filtered query can skip whole files. This is called file skipping.

Use it when queries often filter on one to four columns together, such as city and trip_date.

Two details the exam likes:

  • The Z-Order columns aren't saved with the table. You name them again every time you run OPTIMIZE.
  • Each run rewrites all eligible files, unless you add a WHERE clause to limit it.

[259][269]

V-Order: a write-time layout for fast reads

Exam depth: know it cold.

V-Order is a Fabric optimization applied when Parquet files are written. It sorts, encodes and compresses the data so Fabric engines read it faster. The files stay normal Parquet, so any engine can still read them.

Effect
WritesAbout 15% slower on average.
CompressionUp to 50% more.
Power BI Direct Lake40-60% faster cold-cache queries.
SQL analytics endpointAbout 10% faster reads.
Spark readsNo real benefit.

Decision rule: turn V-Order on for tables that Power BI reads through Direct Lake. Leave it off for tables only Spark reads, such as raw landing tables.

[261][262][263][267]

sql
-- Session level: every Parquet write in this Spark session
SET spark.sql.parquet.vorder.default=TRUE;
-- Table level: future INSERT, UPDATE and MERGE on this table
ALTER TABLE dbo.trips_gold SET TBLPROPERTIES("delta.parquet.vorder.enabled" = "true");
-- Write level (PySpark): .option("parquet.vorder.enabled", "true")
Three levels of control. A session setting of true wins over a table property of false.

[261]

[33][261][262][264]

You are a data engineer at Northwind Rides. A notebook writes a gold daily_trips table in a new workspace. A Power BI report reads it with Direct Lake, and cold-cache visuals are slow. Files are already compacted. You need faster report reads without changing the model. What should you do?

Pick one answer.

[259][263]

VACUUM and the 7-day retention

Exam depth: know it cold.

VACUUM deletes data files that the Delta log no longer references and that are older than the retention threshold. The default retention is 7 days (168 hours).

Retention sets your time-travel window. Time travel means querying an older version of the table. Once VACUUM deletes the files a version needs, you can't query that version again.

  • A retention under 7 days is blocked by a safety check, spark.databricks.delta.retentionDurationCheck.enabled. The Lakehouse Maintenance dialog and API reject it too.
  • VACUUM doesn't delete _delta_log files. Log entries are kept 30 days by default (delta.logRetentionDuration).
  • Run VACUUM after OPTIMIZE, to remove the small files that compaction replaced.

[34][266][267]

sql
-- Default retention: 7 days (168 hours)
VACUUM dbo.trips;
-- Same, stated explicitly; preview first
VACUUM dbo.trips RETAIN 168 HOURS DRY RUN;
-- Under 7 days fails unless the safety check is turned off
VACUUM dbo.trips RETAIN 24 HOURS;
Spark SQL. RETAIN takes hours. DRY RUN lists the files without deleting them.

[266]

Predict, then reveal

A colleague disables the safety check and runs VACUUM dbo.trips RETAIN 24 HOURS. The next day, an analyst queries the table as it was three days ago. Predict what happens.

[34][266]

OPTIMIZE packs the boxes. VACUUM takes out the trash. They are not the same chore. Picture: Retro pixel art, 16-bit video game style, two panels side by side.

Try it in Fabric

You can run both commands without code. Table maintenance works on Delta tables only.

  1. Open your Lakehouse.
  2. In the Explorer, under Tables, right-click a table (or select its ellipsis).
  3. Select Maintenance.
  4. Under the optimize option, select On to compact small files. Optionally select Apply V-order.
  5. Under the vacuum option, select On and keep the retention at 7 days.
  6. Select Run now.
  7. Track the job in the Monitor hub (older pages call it the Monitoring hub). Look for an activity name containing TableMaintenance.

For a recurring job, a pipeline's Lakehouse Maintenance activity (preview) runs the same options on a schedule.

[267]

Lakehouse Explorer with a table's right-click menu open and Maintenance selected. The maintenance pane shows Optimize file size set to On with Apply V-order checked, Remove unreferenced files set to On with Remove old files after 7 Days, Clean deletion vectors, and a Run now button.
Microsoft Learn, CC BY 4.0

Partitioning: when it helps and when it hurts

Exam depth: know it cold.

Partitioning splits a table into one subfolder per value of a column, such as city=Leeds. A query that filters on that column skips the other folders. This is partition pruning.

Pick a column with low cardinality, meaning few distinct values: tens to hundreds, not thousands. Aim for at least 1 GB of data per partition. For tables read by Direct Lake, the docs say fewer than 100-200 values.

Good partition columnBad partition column
city (12 values).trip_id or user_id (millions).
year on a huge table.trip_date on a small table.

Over-partitioning creates thousands of tiny folders and files, the exact small-file problem. You also can't change the partition column later without rewriting the table.

[268][270]

sql
CREATE TABLE dbo.trips (
trip_id BIGINT,
trip_date DATE,
city STRING,
fare DECIMAL(10,2)
)
USING DELTA
PARTITIONED BY (city);
Spark SQL. The partition column is fixed when you create the table.

[268]

You are a data engineer at Northwind Rides. A 4 TB trips table is queried by city, and Northwind runs in 15 cities. A colleague proposes partitioning by scooter_id, which has 80,000 values. You need fast city filters without a small-file problem. What should you partition by?

Pick one answer.

[268]

Optimize write and auto compaction

Exam depth: recognize it.

These two settings prevent small files instead of fixing them later.

FeatureWhen it actsSetting
Optimize writeBefore the write: shuffles (moves data between nodes) into fewer, larger files.delta.autoOptimize.optimizeWrite (table).
Auto compactionRight after the write: runs OPTIMIZE if the table has too many small files.delta.autoOptimize.autoCompact (table).

Optimize write helps partitioned tables, frequent small inserts, and MERGE, UPDATE or DELETE. The shuffle adds time, so use it selectively.

Decision rule: for streaming or frequent small writes, Microsoft recommends auto compaction. If writes must stay fast, schedule OPTIMIZE separately instead.

[259][260]

[268]

Exam-style practice

You are a data engineer at Northwind Rides. Auditors must be able to query the payments table as it was up to 5 days ago. Storage costs are rising from old files. You need to cut storage while keeping that history. What should you do?

Pick one answer.

[34][267]

You are a data engineer at Northwind Rides. A Spark streaming job writes trip events to a Delta table every minute. Small files build up within hours. You need to keep file sizes healthy with the least ongoing effort. What should you do?

Pick one answer.

[259]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[34][259][260][261][262][266][268][269]

Worth keeping

  1. OPTIMIZE compacts small current files; VACUUM deletes old unreferenced ones.
  2. Z-ORDER helps filters skip files; name the columns on every run.
  3. V-Order speeds Direct Lake reads at a write cost; new workspaces have it off.
  4. VACUUM keeps 7 days by default, and that sets your time-travel window.
  5. Partition only on low-cardinality filter columns with 1 GB or more per partition.

The trip table is healthy again. But the nightly run is still late: notebooks wait minutes to start, a copy of payments takes two hours, and one Warehouse query crawls. Next lesson gives you a symptom-to-fix table for all three.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (16)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [33]Microsoft Learn: Lakehouse and Delta Lake tables
  3. [34]Microsoft Learn: Query Delta tables with time travel
  4. [250]Microsoft Learn: .show ingestion failures command
  5. [255]Microsoft Learn: Transactions in Fabric Data Warehouse
  6. [259]Microsoft Learn: Compacting Delta tables
  7. [260]Microsoft Learn: Tune the size of Delta table data files
  8. [261]Microsoft Learn: Optimize Delta Lake tables with V-order
  9. [262]Microsoft Learn: Spark monitoring and performance optimization best practices
  10. [263]Microsoft Learn: Cross-workload table maintenance and optimization in Microsoft Fabric
  11. [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
  12. [266]Microsoft Learn: VACUUM Delta tables
  13. [267]Microsoft Learn: Run Delta table maintenance in lakehouse
  14. [268]Microsoft Learn: Partitioning for Delta tables
  15. [269]Microsoft Learn: Z-Order for Delta tables
  16. [270]Microsoft Learn: Understand Direct Lake query performance