Task 2.4: Optimize performance

• ARun OPTIMIZE on FactOrders after each overwrite

  Incorrect

  OPTIMIZE rewrites files as well, so it adds another reload instead of avoiding one.

• BWrite only the new rows with mode("append")

  Correct

  Appending adds Parquet files without touching existing ones, so incremental framing keeps the loaded column segments.

• CRun VACUUM on FactOrders after each overwrite

  Incorrect

  VACUUM removes unreferenced old files; it doesn't stop the overwrite from replacing the current ones.

• DTurn off Keep your Direct Lake data up to date on the model

  Incorrect

  That only changes when framing happens; the next framing would still reload everything after an overwrite.

Direct Lake avoids full cold-state reloads through incremental framing, reusing in-memory data that is still valid. Using the Overwrite option on an existing table erases the Delta log, so Direct Lake can't frame incrementally and must reload all data, dictionaries and join indexes. Incremental loading, which only inserts new data without affecting existing Parquet files and row groups, works well with incremental framing.

• ARun OPTIMIZE before each VACUUM so that fewer files are removed

  Incorrect

  Compaction replaces files with new ones; the old files that the framed version still reads would then be vacuumed.

• BTurn on Keep your Direct Lake data up to date and keep the hourly 1-hour VACUUM

  Incorrect

  Automatic updates would reframe whenever the tables change, so the data would no longer change only at 06:00.

• CKeep a retention longer than the time between framings, like the default one

  Correct

  A framed model reads the Delta version of its last framing, so files of that version must outlive the gap until the next refresh.

• DRun the same VACUUM from the lakehouse Maintenance option instead of a notebook

  Incorrect

  Where VACUUM runs doesn't matter; a 1-hour retention still removes files that the framed version needs.

Vacuuming removes Parquet files that aren't in the current Delta commit version and are older than the retention threshold. Because a framed Direct Lake model references a particular commit version, the table must keep that version until the model is framed again; otherwise queries fail when the model tries to read Parquet files that no longer exist.

• ARun OPTIMIZE on the table to rebalance its file sizes

  Incorrect

  OPTIMIZE compacts small files into bigger ones; it doesn't split files that are already far above the target.

• BRewrite it with CREATE OR REPLACE TABLE AS SELECT

  Correct

  Rewriting with adaptive target file size on produces files sized for the table, which restores scan parallelism.

• CRun VACUUM on the table with the default retention

  Incorrect

  VACUUM deletes unreferenced files; it doesn't change the files of the current version.

• DEnable auto compaction on the table for future writes

  Incorrect

  Auto compaction runs when there are too many small files, which isn't this table's problem.

The SQL analytics endpoint and Spark parallelize scans at the Parquet file level, so too few very large files limit parallelism just as too many small files add overhead. For legacy oversized files that stay far above the current adaptive target, Fabric guidance is to rewrite the table with an overwrite or CREATE OR REPLACE TABLE AS SELECT with adaptive target file size enabled. Auto compaction and OPTIMIZE address small files, and VACUUM only reclaims storage.

• AEnable fast copy for the eleven Azure SQL queries in the same dataflow

  Incorrect

  The queries would still move their data through the gateway, which stays the bottleneck.

• BAdd a second gateway member to the cluster and keep one dataflow

  Incorrect

  More gateway capacity could help, but the cloud data would still travel through the on-premises gateway unnecessarily.

• CTurn off staging for the on-premises query

  Incorrect

  Staging affects where transformations run, not which queries move their data through the gateway.

• DMove the on-premises query into a separate dataflow

  Correct

  When one query of a dataflow uses a gateway, all its queries use that gateway, so separating it frees the cloud queries.

If at least one query of a Dataflow Gen2 is configured to use a gateway, all queries of that dataflow use the gateway for data movement. Large cloud sources then travel through the on-premises gateway as well, which can saturate it. Splitting queries that need the gateway into a separate dataflow lets the cloud queries refresh without it.

• AMove lakehouses into separate workspaces so that automatic metadata discovery can scale

  Correct

  Metadata discovery runs as a single instance per workspace, so many lakehouses in one workspace can increase sync latency.

• BHave the pipeline call the Refresh SQL analytics endpoint metadata REST API after the Spark job

  Correct

  An on-demand metadata sync makes the endpoint reflect the committed changes before the next step reads it.

• CTurn on V-Order for all tables so that the endpoint reads them faster

  Incorrect

  V-Order affects read performance of the files, not how fast metadata changes are discovered and synced.

• DCreate one more SQL analytics endpoint for each busy lakehouse

  Incorrect

  Each lakehouse has exactly one SQL analytics endpoint; you can't add more.

• ESwitch the endpoints to a case-insensitive collation

  Incorrect

  Collation affects comparisons and sorting, not metadata sync latency.

A background process keeps each SQL analytics endpoint in sync with the changes committed to its lakehouse, normally in under a minute. Automatic metadata discovery is a single instance per Fabric workspace, so a large number of lakehouses in one workspace can increase latency; guidance is to consider moving lakehouses to separate workspaces. You can also start an on-demand sync in the portal or with the Refresh SQL analytics endpoint metadata REST API.

• AA nightly OPTIMIZE on the table from a Spark notebook

  Incorrect

  OPTIMIZE is for Spark-managed lakehouse tables; the warehouse handles its own compaction.

• BA weekly VACUUM with a 7-day retention

  Incorrect

  VACUUM is a lakehouse maintenance task; warehouse cleanup is system-managed.

• CAuto compaction turned on for the table

  Incorrect

  Auto compaction is a Spark feature for lakehouse tables.

• DNone, because warehouse compaction is automatic

  Correct

  Fabric Data Warehouse manages its Delta layout and compaction itself.

Fabric Data Warehouse manages the physical Delta table layout regardless of the ingestion method: compaction, file sizes and the cleanup of unreferenced files are automatic. Spark features such as auto compaction, OPTIMIZE and VACUUM belong to lakehouse table maintenance.

• ABetween 1 million and 16 million rows

  Correct

  Direct Lake prefers large row groups in that range; much smaller ones create many small segments.

• BBetween 1,000 and 10,000 rows

  Incorrect

  Row groups that small create many tiny column segments and slow queries.

• CExactly one row group per Parquet file

  Incorrect

  The guidance is about rows per group, not one group per file.

• DAs small as possible, to speed up incremental framing

  Incorrect

  Small row groups add segments and transcoding overhead.

Direct Lake generally performs best with row groups between 1 million and 16 million rows. The row group count of a Delta table maps to the column segment count of the model table, so many small row groups mean many small segments and more dictionary merging, which hurts performance. Fabric might use larger row groups for large, compressible tables.

• AA single query evaluation can last at most 8 hours

  Incorrect

  That limit applies to one query's evaluation, not the whole refresh.

• BA dataflow can refresh at most 48 times a day

  Incorrect

  That's the number of scheduled slots, not a duration limit.

• CA single refresh can last at most 24 hours

  Correct

  The total refresh time of a single dataflow refresh is limited to 24 hours.

• DA refresh can last at most 5 hours on F SKUs

  Incorrect

  No five-hour limit is listed for Dataflow Gen2; its refresh limits don't depend on the SKU.

Dataflow Gen2 refreshes have documented limits: a single evaluation of a query can run up to 8 hours, and the total time of a single refresh is limited to 24 hours. A refresh schedule can define up to 48 time slots a day.

• AChecking its file layout with DESCRIBE DETAIL

  Incorrect

  Inspecting the layout is fine; the restriction is on rewriting the table.

• BRunning OPTIMIZE or REORG TABLE on it

  Correct

  Lakehouse destinations that use Dataflow Gen2 incremental refresh don't support OPTIMIZE or REORG TABLE.

• CReading it from the SQL analytics endpoint

  Incorrect

  Reads are unaffected; the restriction concerns maintenance commands.

• DRunning VACUUM within its retention requirements

  Incorrect

  VACUUM removes unreferenced files and isn't the operation the guidance excludes.

Some destination modes impose maintenance restrictions on lakehouse tables. Dataflow Gen2 lakehouse destinations that use incremental refresh don't support OPTIMIZE or REORG TABLE; follow the Dataflow Gen2 incremental refresh limitations.