Predictive Optimization & Table Maintenance

Databricks · Architecture, Performance & Cost

Theory

Delta tables need regular housekeeping. OPTIMIZE compacts many small files into fewer large ones and applies clustering. VACUUM deletes data files no longer referenced by the table and older than the retention period (7 days by default), which saves storage but limits time travel. ANALYZE TABLE ... COMPUTE STATISTICS collects statistics the optimizer uses to choose join strategies. Liquid clustering (CLUSTER BY columns, or CLUSTER BY AUTO to let Databricks pick keys) replaces partitioning and ZORDER for most tables. Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables, deciding when each is worth doing from usage, so you do not schedule maintenance jobs yourself. It can be enabled or disabled at account, catalog or schema level.

Example: after ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION, main.sales.orders is compacted and vacuumed automatically.

Why we use it

Streaming and frequent small writes create thousands of tiny files and stale versions, slowing queries and growing storage. Maintenance fixes that, and predictive optimization removes the need to guess schedules or write cleanup jobs.

When to use it

Use Unity Catalog managed tables with predictive optimization and liquid clustering as the default. Run OPTIMIZE or VACUUM by hand only for external tables or special cases. Avoid over-partitioning small tables; partitions under about 1 GB usually hurt more than help.

How it works

  1. Writes create small files and old versions
  2. Is it a Unity Catalog managed table with predictive optimization on? Yes: Databricks schedules OPTIMIZE, VACUUM, ANALYZE. No: Schedule them in a job yourself.
  3. OPTIMIZE compacts files and applies liquid clustering
  4. VACUUM deletes unreferenced files older than retention
  5. ANALYZE refreshes statistics for the optimizer
  6. Queries scan fewer, larger, better-clustered files

Syntax

ALTER CATALOG|SCHEMA <name> ENABLE | DISABLE | INHERIT PREDICTIVE OPTIMIZATION;
OPTIMIZE <table> [FULL];
VACUUM <table> [RETAIN <n> HOURS];
ANALYZE TABLE <table> COMPUTE STATISTICS [FOR ALL COLUMNS];
CREATE TABLE ... CLUSTER BY (<cols>) | CLUSTER BY AUTO;   ALTER TABLE <t> CLUSTER BY (<cols>);
Check: DESCRIBE DETAIL <table>;  system.storage.predictive_optimization_operations_history

Example

-- Let Databricks maintain every managed table in the catalog
ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION;

-- Liquid clustering on the columns used in filters
ALTER TABLE main.sales.orders CLUSTER BY (order_date, region);
-- or let Databricks choose keys from query patterns
ALTER TABLE main.sales.customers CLUSTER BY AUTO;

-- Manual maintenance (external tables or one-off fixes)
OPTIMIZE main.sales.orders;
VACUUM main.sales.orders;                 -- default 7-day retention
ANALYZE TABLE main.sales.orders COMPUTE STATISTICS FOR ALL COLUMNS;

DESCRIBE DETAIL main.sales.orders;        -- numFiles, sizeInBytes, clusteringColumns

Key points

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic