Launch Deal · 50% off 1st month ·

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.

See plans

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.