A lakehouse can store correct data and still perform badly. The cause may be hundreds of tiny files, wide shuffles in PySpark, skewed keys, inefficient partitioning or a read pattern incompatible with how data is laid out. DP-700’s monitoring and optimization domain expects data engineers to distinguish these causes instead of applying a generic “turn on caching” answer.
Diagnose the workload before tuning the table
Begin with a baseline query: which columns are selected, which predicates filter the rows, how many files are scanned, how much data is shuffled and how long the job spends in each stage? Compare input volumes to prior successful runs. If a daily query grew from 10 million to 80 million rows, the slowdown might be proportional to business growth rather than a bad Spark configuration.
For a fictional orders table, test two queries separately. One filters to yesterday’s transactions and groups by region; the other joins all historical orders with a customer dimension. The first may benefit from file pruning and layout improvements, while the second may suffer from join skew or oversized shuffles. Measure both before and after each change. Never claim a faster query proves that output totals remained correct.
Delta OPTIMIZE and VACUUM serve different purposes
Delta tables use a transaction log to track changes to data files. Frequent small writes can create many small files, increasing metadata and scan overhead. An OPTIMIZE operation compacts and reorganizes data files for more efficient reads; VACUUM removes unreferenced old files under retention rules. These two operations should never be conflated.
OPTIMIZE silver_orders;
This is a conceptual Delta maintenance example; actual syntax and supported settings depend on the Fabric Spark runtime and table context. Test maintenance in an environment with representative table size, permissions and consumers. Before VACUUM, review retention expectations for time travel, incremental readers and rollback. Deleting retained historical files can make recovery paths impossible.
The official Fabric Delta optimization and V-Order guide explains these mechanisms and the supported configuration options.
V-Order is a file layout choice, not a correctness feature
Fabric V-Order optimizes Parquet write layout to improve downstream reads across compatible engines and access patterns. It does not validate source data, guarantee referential integrity or eliminate every expensive query plan. Apply it where measured read behavior justifies additional write-side work. Distinguish V-Order from Z-Order: the concepts address different layout considerations and can complement each other in supported scenarios.
A customer dimension used heavily by SQL consumers may benefit differently from a rapidly appended Bronze event table. The latter might prioritize ingest throughput and reserve compaction/layout work for a curated layer. Document the trade-off in terms of read frequency, query latency, data volume and maintenance windows rather than turning every optimization switch on indiscriminately.
Design partitioning for access patterns, not visual neatness
Partitioning by a low-cardinality field such as order date can help prune irrelevant data for date-bounded queries. Partitioning by a near-unique transaction ID can explode the number of partitions and files. Conversely, forcing all multi-year data into one partition can make selective pruning ineffective. Choose based on actual query predicates, distribution and data arrival pattern.
Before repartitioning, inspect the physical plan, number of partitions and skew. A partition key that looks reasonable from a domain-model perspective can still be uneven if one region accounts for most traffic. Salting, range partitioning, filtering before joins, or broadcasting an appropriately small dimension can help particular workloads, but each adjustment needs a before/after comparison.
Understand shuffle as the price of distributed transformations
Spark transformations that require data movement between executors—joins on non-aligned keys, aggregations, sorts and some distinct operations—can be significantly more expensive than narrow filters or projections. Study the stage timeline and skewed tasks. If one executor processes disproportionately large data, adding more workers may not solve the tail latency. Simplify the transformation, filter early, control join inputs, and check whether an estimated small dimension can safely be broadcast.
For a notebook that joins 100 million orders with a 100-thousand-row store dimension, first verify key types and cardinalities. A many-to-many relationship introduced by duplicate store IDs can multiply rows. That is a data quality defect masquerading as a Spark performance problem. The lakehouse data quality article shows why key validation must precede optimization.
Streaming writes create a maintenance rhythm
A stream with a five-second trigger may generate small output files and frequent commits. If analysts query the destination every hour, less frequent commit intervals or scheduled compaction may reduce cost without violating freshness requirements. The Structured Streaming checkpoint article explores how trigger choice, replay and Delta sinks interact. A maintenance policy must respect active readers and write coordination.
Monitor target file counts and distribution over time. A table that was efficient after a one-time OPTIMIZE can degrade again as ingestion accumulates small files. Schedule maintenance based on observed growth and read performance, and alert when expected compaction does not complete.
Build a measured optimization experiment
Create a table with one million synthetic orders distributed across 30 dates. Load it first in many tiny append batches, record file count and benchmark a selective aggregate. Optimize or compact in a supported Fabric test environment, repeat the same query and compare outputs and timings. Then run a join with a deliberately skewed customer key to show why compaction does not fix every Spark problem.
Keep the experiment’s variables controlled: similar input volumes, the same query semantics, recorded runtime configuration and a documented snapshot of table versions. If the query improves from 12 seconds to 5 seconds but returns different totals, the experiment is invalid. If the improvement only occurs on one run under varying capacity contention, collect more evidence before attributing it to a setting.
Where the slow workload runs in a Fabric warehouse or Eventhouse rather than Spark, use the Warehouse and Eventhouse query-optimization checklist to inspect its distinct execution signals. The DP-700 skill is to form a hypothesis from a diagnostic signal, choose one relevant optimization, prove that data remained correct and quantify the benefit. A list of Fabric features is not a tuning method.