2.9 — Optimize Loading Performance and Delta File Sizes
DP-600 Domain 2 · Skill 2.9
What You Need to Know
DP-600 asks you to find and fix bottlenecks in dataflows, notebooks and SQL, and to fix Delta tables whose files are too small or too many. These tables slow down every engine that reads them, Direct Lake included.
Dataflow Gen2
| Bottleneck | Fix |
|---|---|
| Query folding is lost (steps run in the mashup engine instead of the source) | Put foldable steps (filters, column selection, joins) first. Check the folding indicators. Push complex logic into a source view |
| Large data copied with few transformations | Turn on Fast copy, which uses the pipeline copy engine for large volumes |
| Several queries reuse the same expensive source | Staging: stage the shared query in the staging lakehouse and reference it, instead of reading the source repeatedly |
| Full reload every time | Incremental refresh in Dataflow Gen2 (on a date column), or a Copy job for incremental copies |
| Slow or failing gateway sources | Scale the gateway (cluster) and keep it close to the source |
Notebooks (Spark)
| Bottleneck | Fix |
|---|---|
| Slow session start | Starter pool, high concurrency |
| A shuffle-heavy join with a small table | Broadcast the small table |
| Skew (one task much longer than the rest) | Adaptive query execution, salting, repartitioning |
| Reading too much | Select only needed columns, filter on partition or clustered columns |
| Driver out of memory | Avoid collect() and toPandas() on big data |
| Generally slow Spark SQL and DataFrame jobs | Turn on the native execution engine; choose the right resource profile |
SQL (warehouse)
| Bottleneck | Fix |
|---|---|
| Many single-row INSERTs and UPDATEs | Batch: INSERT ... SELECT, CTAS, COPY INTO |
Oversized data types (varchar(max), string keys) |
The smallest types that fit, and integer keys |
| Stale statistics after big loads | UPDATE STATISTICS |
| Large scans filtered on a column | Data clustering (CLUSTER BY) |
| Finding the slow queries | Query insights: queryinsights.long_running_queries |
Delta table file sizes
Small-file problems come from frequent small appends, streaming, over-partitioning and many MERGE operations. Symptoms are slow reads, slow SQL endpoint metadata sync, and Direct Lake guardrail warnings (too many files or row groups per table).
| Fix | What it does |
|---|---|
OPTIMIZE table |
Compacts small files into large ones (around 1 GB) |
OPTIMIZE table VORDER |
Compacts and applies V-Order, which is best for Direct Lake and the SQL endpoint |
| Optimize write / auto compaction | Writes fewer, larger files from the start |
| Re-partition sensibly | Remove high-cardinality partitions; partition only big tables by low-cardinality columns |
VACUUM |
Removes unreferenced old files (default 7-day retention). It frees storage but doesn't compact |
| Table maintenance | The lakehouse UI's Maintenance command, or the pipeline Lakehouse Maintenance activity, on a schedule |
Key Concept: In dataflows, keep folding and use fast copy. In Spark, broadcast, handle skew and prune. In SQL, load in batches with small types. For Delta tables, OPTIMIZE (with VORDER) to compact, and VACUUM only to clean up old files.
Exam Traps
- Adding a step that breaks folding (such as a custom function) before the filters
- Running VACUUM to fix too many small files
- Partitioning a medium-sized table by a high-cardinality column "for speed"
- Loading a warehouse row by row from a loop
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Query folding in Power Query, Microsoft Learn
- Fast copy in Dataflow Gen2, Microsoft Learn
- Optimize Delta Lake tables with V-Order, Microsoft Learn
- Performance guidelines in Fabric Data Warehouse, Microsoft Learn
- Run Delta table maintenance in lakehouse, Microsoft Learn