OPTIMIZE

In the Ultra Transcenders books

DP-750DP-600DP-700

Each book explains OPTIMIZE in context, with comparison tables and the common traps.

Terms in this definition

• Serverless

  Compute tier for single Azure SQL databases that scales automatically, pauses when idle and charges by the second. It is offered in General Purpose and Hyperscale, not Business Critical, and reserved capacity does not apply.

• Unity Catalog

  Azure Databricks' governance solution covering both data and AI in one place, with centralised permissions, auditing, data discovery and lineage.

• Predictive optimization

  Unity Catalog managed tables can have OPTIMIZE, VACUUM and ANALYZE scheduled and run for them on serverless compute without manual effort. Accounts created on or after 11 November 2024 get this turned on by default.

Related terms

• Adaptive target file size

  A Spark setting in Fabric that chooses the target file size for each Delta table based on how big the table is, starting at 128 MB for small tables and rising towards 1 GB for very large ones, and reconsiders the choice every time OPTIMIZE runs. It is enabled by default in Runtime 2.0 and removes the need to tune the older delta.targetFileSize property by hand.

• Auto compaction

  Delta Lake merges small files as soon as a write has succeeded, using the same cluster, and ignores files it has already compacted. History shows each run as an OPTIMIZE triggered automatically.

• Deletion vectors

  Lets Delta Lake and Iceberg tables flag removed or changed rows through metadata rather than rewriting complete Parquet files, a soft delete that speeds up deletes, updates and merges. The physical rewrite happens later, when OPTIMIZE or a REORG TABLE purge runs.

• Fast optimize

  With spark.microsoft.delta.optimize.fast.enabled switched on, Fabric Spark's OPTIMIZE skips compacting small-file groups unlikely to hit the target size, so fewer files are rewritten. The setting has no bearing on Z-Order or liquid clustering.

• File-level compaction targets

  A Fabric Spark option that leaves alone, during OPTIMIZE, any file that previously hit half or more of an older target size, so growing adaptive target sizes don't trigger endless rewriting. Runtime 2.0 onwards has it switched on.

• Lakehouse maintenance activity

  Preview pipeline step for scheduled upkeep of lakehouse Delta tables, running OPTIMIZE (optionally with V-Order) followed by VACUUM.

• Lakehouse table maintenance

  Running OPTIMIZE (V-Order optional) and VACUUM against a Delta table with no Spark coding, from the Maintenance dialog in lakehouse explorer. The same job can also be triggered by a pipeline activity or through a REST API.

• Optimization presets

  Choices on Power BI Desktop's Optimize ribbon that tweak how a report behaves, such as whether visuals cross-highlight and whether Apply buttons appear, so that fewer queries reach the model.