All 33 lessons
Start here: cloud and data basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. The three languages: SQL, PySpark and KQL side by side
- 6. Getting data in: the map of Fabric tools
Store, ingest and transform batch data (exam area 2, 30-35%)
- 7. Choosing a data store and a transformation tool
- 8. Shortcuts and mirroring: use data without copying it
- 9. Ingest data with pipelines and Copy job
- 10. Transform with PySpark and SQL: join, denormalize, aggregate
- 11. Messy data: duplicates, missing values and late arrivals
- 12. Full and incremental loads, and loading a star schema
Orchestrate and stream (exam areas 1 and 2)
Set up and secure a Fabric solution (exam area 1, 30-35%)
- 18. Workspace settings: Spark, domains, OneLake and data workflows
- 19. Version control with Git and database projects
- 20. Deployment pipelines: development, test and production
- 21. Who can see what: workspace roles and item permissions
- 22. Fine-grained security: rows, columns, objects, files and masking
- 23. Governance: sensitivity labels and endorsement
Monitor, fix and optimize (exam area 3, 30-35%)
- 24. Monitoring hub, alerts and Activator
- 25. Fixing errors in pipelines, dataflows and notebooks
- 26. Fixing errors in Eventhouse, Eventstream and T-SQL
- 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
- 28. Optimize Spark, pipelines and warehouses
- 29. Optimize eventstreams, eventhouses and queries
Exam practice and final review
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.
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.
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.
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.
-- Compact small filesOPTIMIZE dbo.trips;-- Compact and colocate rows by the columns you filter onOPTIMIZE dbo.trips ZORDER BY (city, trip_date);-- Compact and apply V-Order to the rewritten filesOPTIMIZE dbo.trips VORDER;-- All three at onceOPTIMIZE dbo.trips ZORDER BY (city, trip_date) VORDER;
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
WHEREclause to limit it.
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 | |
|---|---|
| Writes | About 15% slower on average. |
| Compression | Up to 50% more. |
| Power BI Direct Lake | 40-60% faster cold-cache queries. |
| SQL analytics endpoint | About 10% faster reads. |
| Spark reads | No 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.
-- Session level: every Parquet write in this Spark sessionSET spark.sql.parquet.vorder.default=TRUE;-- Table level: future INSERT, UPDATE and MERGE on this tableALTER TABLE dbo.trips_gold SET TBLPROPERTIES("delta.parquet.vorder.enabled" = "true");-- Write level (PySpark): .option("parquet.vorder.enabled", "true")
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.
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. VACUUMdoesn't delete_delta_logfiles. Log entries are kept 30 days by default (delta.logRetentionDuration).- Run
VACUUMafterOPTIMIZE, to remove the small files that compaction replaced.
-- Default retention: 7 days (168 hours)VACUUM dbo.trips;-- Same, stated explicitly; preview firstVACUUM dbo.trips RETAIN 168 HOURS DRY RUN;-- Under 7 days fails unless the safety check is turned offVACUUM dbo.trips RETAIN 24 HOURS;
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.
Try it in Fabric
You can run both commands without code. Table maintenance works on Delta tables only.
- Open your Lakehouse.
- In the Explorer, under Tables, right-click a table (or select its ellipsis).
- Select Maintenance.
- Under the optimize option, select On to compact small files. Optionally select Apply V-order.
- Under the vacuum option, select On and keep the retention at 7 days.
- Select Run now.
- 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.
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 column | Bad 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.
CREATE TABLE dbo.trips (trip_id BIGINT,trip_date DATE,city STRING,fare DECIMAL(10,2))USING DELTAPARTITIONED BY (city);
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.
Optimize write and auto compaction
Exam depth: recognize it.
These two settings prevent small files instead of fixing them later.
| Feature | When it acts | Setting |
|---|---|---|
| Optimize write | Before the write: shuffles (moves data between nodes) into fewer, larger files. | delta.autoOptimize.optimizeWrite (table). |
| Auto compaction | Right 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.
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.
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.
Worth keeping
- OPTIMIZE compacts small current files; VACUUM deletes old unreferenced ones.
- Z-ORDER helps filters skip files; name the columns on every run.
- V-Order speeds Direct Lake reads at a write cost; new workspaces have it off.
- VACUUM keeps 7 days by default, and that sets your time-travel window.
- 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.
- [4]Microsoft Learn: Study Guide for Exam DP-700
- [33]Microsoft Learn: Lakehouse and Delta Lake tables
- [34]Microsoft Learn: Query Delta tables with time travel
- [250]Microsoft Learn: .show ingestion failures command
- [255]Microsoft Learn: Transactions in Fabric Data Warehouse
- [259]Microsoft Learn: Compacting Delta tables
- [260]Microsoft Learn: Tune the size of Delta table data files
- [261]Microsoft Learn: Optimize Delta Lake tables with V-order
- [262]Microsoft Learn: Spark monitoring and performance optimization best practices
- [263]Microsoft Learn: Cross-workload table maintenance and optimization in Microsoft Fabric
- [264]Microsoft Learn: Performance guidelines in Fabric Data Warehouse
- [266]Microsoft Learn: VACUUM Delta tables
- [267]Microsoft Learn: Run Delta table maintenance in lakehouse
- [268]Microsoft Learn: Partitioning for Delta tables
- [269]Microsoft Learn: Z-Order for Delta tables
- [270]Microsoft Learn: Understand Direct Lake query performance