Task 3.3: Optimize performance

• ASet spark.sql.parquet.vorder.default to true in every ingestion session

  Incorrect

  That applies V-Order to all the bronze writes, adding the write overhead you want to avoid.

• BRun VACUUM on the gold table every hour

  Incorrect

  Cleaning up old file versions doesn't change the layout of the files that reads use.

• CPartition the gold table by its highest-cardinality column

  Incorrect

  Partitioning changes how files are grouped, not the read-optimized Parquet layout these queries lack.

• DSet delta.parquet.vorder.enabled on the gold table only

  Correct

  V-Order is off by default in new workspaces; a table property applies it to that table alone.

V-Order optimizes Parquet layout for faster reads by all Fabric engines at the cost of roughly 15% longer writes, and it's disabled by default in new Fabric workspaces to favor write-heavy work. You can enable it per table with the delta.parquet.vorder.enabled table property, or for a session with spark.sql.parquet.vorder.default.

• AVACUUM with the default retention after each micro-batch

  Incorrect

  VACUUM doesn't merge the small files the table still uses.

• BA nightly compaction job scheduled in a pipeline

  Incorrect

  A scheduled job is the separate maintenance the team doesn't want to run.

• CAuto compaction, which compacts after each write

  Correct

  It checks file health after each write and compacts small files without a separate schedule.

• DA new partition column for every minute

  Incorrect

  Partitioning by minute multiplies small files instead of reducing them.

Auto compaction evaluates partition health after each write and, when it finds too many small files, compacts them right after the write is committed. Microsoft recommends it for ingestion with frequent small writes, such as streaming or micro-batch, so file health is maintained without manual scheduling.

• ASchedule VACUUM on the table with RETAIN 720 HOURS

  Correct

  A 30-day retention removes older unreferenced files while keeping 30 days of versions.

• BRun VACUUM with RETAIN 0 HOURS after disabling the retention check

  Incorrect

  That deletes the files time travel needs and risks concurrent readers.

• CRun VACUUM from the lakehouse's SQL analytics endpoint

  Incorrect

  The SQL analytics endpoint doesn't support Spark Delta maintenance commands.

• DRun the VACUUM from a notebook, a Spark job or the lakehouse maintenance option

  Correct

  VACUUM is a Spark command; it doesn't run in the SQL analytics endpoint.

• ERun OPTIMIZE instead, which also deletes old versions

  Incorrect

  OPTIMIZE doesn't delete old file versions; they stay until VACUUM removes them.

VACUUM permanently removes files that the Delta table no longer references and that are older than the retention threshold, which also limits time travel to that window. Set the retention to the history you must keep, such as RETAIN 720 HOURS for 30 days, and run it with Spark: notebooks, Spark job definitions or the lakehouse maintenance option, not the SQL analytics endpoint.

• AUse the smallest string length that fits, such as varchar(100)

  Correct

  Precise lengths improve statistics, cost estimates and performance.

• BChange every string column to nvarchar(max)

  Incorrect

  Large object types are the slowest choice for these columns.

• CCreate manual statistics on every column and keep varchar(max)

  Incorrect

  Statistics help, but oversized types still hurt estimates and performance.

• DTurn off result set caching for the warehouse

  Incorrect

  Caching isn't what makes these plans' estimates poor.

Microsoft recommends using the smallest string column length that can hold the values, such as varchar(100) instead of varchar(8000) or varchar(max). Large string types, especially large objects, can perform poorly, and precise lengths make statistics and query cost estimation more accurate.

• APartition option set to Physical partitions of table

  Correct

  Partition options let the copy read the source's partitions in parallel.

• BStaging enabled on the Copy activity

  Incorrect

  Staging adds an interim hop; it doesn't parallelize the source read.

• CA larger Fabric capacity for the workspace

  Incorrect

  Both sides already have spare capacity; the single connection is the limit.

• DThe table action on the destination set to Overwrite

  Incorrect

  Destination table actions don't change read parallelism.

For SQL database sources, Copy activity performance depends on source partition options (None, Physical partition, Dynamic range) and the degree of copy parallelism. With a partitioned source table, choosing physical partitions lets the copy read several partitions in parallel instead of one connection.

• AConvert both tables to strings with tostring() before joining

  Incorrect

  Converting columns adds work and doesn't help the join strategy.

• BUse tolower() on the join key in Telemetry and Sites

  Incorrect

  Transforming the keys adds work and doesn't change which table drives the join.

• CAdd a take 1000 to Telemetry before the join

  Incorrect

  That changes the result instead of speeding up the full join.

• DPut Sites, the smaller table, on the left side of the join

  Correct

  KQL recommends the smaller table as the first, left-most table in a join.

KQL query best practices recommend selecting the table with the fewest rows as the first, left-most table in a join. Converting the join keys doesn't improve the join strategy, and taking a sample changes the result.

• AA 14-day caching policy and a 365-day retention policy

  Correct

  The cache keeps the recent 14 days fast to query; retention removes data after a year.

• BA 365-day caching policy and a 14-day retention policy

  Incorrect

  This deletes data after 14 days and keeps a year in costly hot cache.

• CA 14-day retention policy and a materialized view for the older data

  Incorrect

  Retention of 14 days removes the older data a view would need.

• DThe default policies, which already cache 14 days and keep one year

  Incorrect

  By default all data is cached, and retention is far longer than a year.

The caching policy sets how much recent data stays in the hot cache for the fastest queries; older data can still be queried, more slowly. The retention policy removes data after its age passes the limit. Neither default fits here: by default all data is cached, and retention is far longer than a year.

• AOne day (24 hours)

  Incorrect

  One day is shorter than the default and triggers the safety check.

• B14 days (336 hours)

  Incorrect

  14 days is a custom retention, not the default.

• CZero hours

  Incorrect

  Zero would remove files that time travel and readers may need.

• DSeven days (168 hours)

  Correct

  The default retention period is seven days.

If you don't specify a retention interval, VACUUM uses the default retention of seven days (168 hours). Shorter periods trigger the retention safety check unless it's disabled.

• AYes, by default for all Spark pools, with a 50% cache size

  Correct

  Intelligent cache is enabled by default with a 50% cache size.

• BNo, a workspace admin must turn the cache on per pool

  Incorrect

  It's on by default.

• COnly on custom Spark pools

  Incorrect

  It applies to all Spark pools, starter and custom.

• DOnly when V-Order is enabled

  Incorrect

  The cache doesn't depend on V-Order.

In Fabric, intelligent caching is enabled by default for all Spark pools with a 50% cache size. It caches files on each node's SSD, detects changes to the source files and evicts the least recently read files when full.

• A30 days

  Incorrect

  30 days is far shorter than the default.

• B365 days

  Incorrect

  One year isn't the default.

• C3,650 days

  Correct

  The default data retention policy is 3,650 days.

• D36,500 days

  Incorrect

  That's the maximum, not the default.

In a KQL database, the default data retention policy is 3,650 days, with a maximum of 36,500 days. You change it, and the caching policy, under Manage > Data policies.

• Ajoin kind=fullouter (Watchlist) on DeviceId

  Incorrect

  A full outer join keeps unmatched rows from both tables and adds Watchlist columns.

• Bunion Watchlist

  Incorrect

  union appends Watchlist's rows instead of filtering Telemetry.

• Cwhere DeviceId in (Watchlist | project DeviceId)

  Correct

  Best practices prefer in over a left semi join to filter by a single column.

• Dsummarize count() by DeviceId

  Incorrect

  Summarizing groups rows; it doesn't filter by another table.

KQL query best practices recommend the in operator instead of a left semi join when you filter by a single column, for example where DeviceId in (Watchlist | project DeviceId). It returns only the matching Telemetry rows, with no columns from the other table.