Predictive
Optimization.

What it is

Analogy Use case

Predictive optimization (PO) is Databricks running your table maintenance for you, automatically, on Unity Catalog managed tables (Delta and Iceberg). It watches how each table is written and queried, then runs the three commands you'd otherwise schedule by hand:

• OPTIMIZE — compacts small files into right-sized ones (Lesson 04), and for liquid-clustered tables groups data by the clustering keys (Lesson 08).
• VACUUM — deletes old, unreferenced data files to reclaim storage.
• ANALYZE — collects/refreshes the statistics that power data skipping (Lesson 03).

It also collects stats on write and eliminates manual maintenance on managed tables. PO runs on serverless compute in the background, so it never competes with your clusters.

Analogy. It's a self-cleaning oven: you cook (write + query); the oven decides when it's dirty enough and runs a cleaning cycle quietly on its own. You never schedule "clean the oven Tuesdays at 2am" again.

Use case. A platform team owns 4,000 UC managed tables. Hand-scheduling OPTIMIZE/VACUUM/ANALYZE per table is unmaintainable. Enable PO once at the catalog level; every table inherits it and Databricks runs the right maintenance on each — no jobs, no schedules.

Why it matters

• Manual maintenance doesn't scale and silently rots. Hand-scheduled jobs are guesswork — too often burns compute, too rarely slows queries and bloats storage. PO decides which tables benefit and when, per table, from real signals.
• It closes the loop on the whole track. Small files → OPTIMIZE. Stale skipping stats → ANALYZE. Dead files → VACUUM. Liquid-clustered tables → PO triggers clustering and drives automatic key selection for CLUSTER BY AUTO.
• Default-on going forward. PO is enabled by default for accounts created on/after Nov 11, 2024, with a gradual rollout to existing accounts (expected complete ~Aug 2026).

See it work — enablement inheritance

Enablement flows down a hierarchy: account → catalog → schema → table. Lower levels INHERIT by default; a table's effective state is the nearest explicit ENABLE/DISABLE above it. Click a level to set its state and watch the effective state on the table change. Note the trap: a child DISABLE wins over a parent ENABLE.

Simplified illustration of the inheritance model. "Effective" = the nearest explicit ENABLE/DISABLE walking up from the table; the account has no INHERIT (it's the root). Verify real state with DESCRIBE … EXTENDED.

See it work — the maintenance queue

Once enabled, PO scans your managed tables, queues the ones that would benefit, and runs the right operation on serverless. Step through a cycle and watch which table gets which op — and which gets skipped because it's already healthy.

Simplified illustration. Real PO uses internal heuristics over write/query signals to decide which tables to maintain and when; it runs asynchronously on serverless.

How it works — sub-topic by sub-topic

01What PO runs: OPTIMIZE, VACUUM, ANALYZE (+ stats on write)

On UC managed tables (Delta + Iceberg), PO automatically runs OPTIMIZE (compaction; clustering for LC tables), VACUUM (remove unreferenced files), and ANALYZE (refresh skipping stats), and collects stats on write. These three commands are the entire manual-maintenance burden — automating them means no jobs to author. PO is the operational layer; you still pick the layout (liquid clustering vs. legacy partitioning/Z-order) and PO keeps it healthy.

SQL-- There is no "run PO" command — it runs automatically once enabled.
-- These are the ops PO performs FOR you on a managed table; you stop scheduling them:
--   OPTIMIZE  analytics.prod.events;                        -- compaction (+ clustering for LC)
--   VACUUM    analytics.prod.events;                        -- reclaim files past retention
--   ANALYZE   analytics.prod.events COMPUTE DELTA STATISTICS; -- refresh skipping stats
-- After enabling PO, DELETE the hand-scheduled jobs that ran the above.

02Prerequisites

• Workspace on the Premium plan.
• A supported region.
• A SQL warehouse or DBR 12.2 LTS+.
• The table is a Unity Catalog managed table (PO's maintenance runs on serverless).

If any prerequisite is missing (external table, hive_metastore, non-Premium, unsupported region), PO simply won't run there — you're back to manual maintenance for those tables.

03The enablement inheritance model

Set a state at the account, catalog, or schema level; lower levels inherit by default, and a table's effective state is the nearest explicit ENABLE/DISABLE above it. States: ENABLE (on here + below), DISABLE (off here + below), INHERIT (defer to parent). A child DISABLE wins over a parent ENABLE — set the level back to INHERIT/ENABLE to re-cover it.

SQL-- CATALOG level: turn PO on for the whole catalog; schemas/tables inherit unless overridden.
ALTER CATALOG analytics ENABLE PREDICTIVE OPTIMIZATION;

-- SCHEMA / DATABASE level: explicitly OFF for one schema (this wins over the catalog ENABLE).
ALTER SCHEMA analytics.staging DISABLE PREDICTIVE OPTIMIZATION;

-- Go back to inheriting the parent's decision (removes the explicit override).
ALTER SCHEMA analytics.staging INHERIT PREDICTIVE OPTIMIZATION;

04Account-level enablement is UI-driven

The account-level toggle lives in the accounts console, not in SQL. Catalog/schema overrides are then done in SQL (above).

Step-by-step UI actions (account-level):

• Sign in to the Databricks accounts console as an account admin.
• In the left sidebar, click Settings.
• Open the Feature enablement tab.
• Find Predictive optimization.
• Set the enablement state (enable for the account). Save/confirm.
• (Optional) Override below with SQL ALTER CATALOG … / ALTER SCHEMA … to ENABLE / DISABLE / INHERIT specific catalogs or schemas.

Verify the exact labels against the current docs — accounts-console wording drifts.

05Privileges

• Account state → account admin (accounts console).
• Catalog state → catalog owner.
• Schema state → schema owner.

PO enablement is a governance decision tied to UC ownership — if you're not the owner, request the owner (or an account admin) to set it.

06PO's OPTIMIZE does NOT run ZORDER — use liquid clustering

The OPTIMIZE PO runs does compaction (and clustering for LC tables) but does not run ZORDER — it ignores Z-ordered files. For colocation-for-skipping under PO, use liquid clustering; PO even drives automatic key selection for CLUSTER BY AUTO. If you rely on ZORDER and expect PO to maintain it — it won't.

SQL-- WRONG expectation: PO will NOT maintain this Z-order layout (it ignores Z-ordered files).
-- OPTIMIZE legacy_tbl ZORDER BY (customer_id);   -- manual-only; PO does not run ZORDER

-- RIGHT for PO: use liquid clustering so PO's OPTIMIZE colocates data by the keys.
CREATE OR REPLACE TABLE analytics.prod.events (
  event_id   BIGINT,
  event_type STRING,
  event_date DATE
)
CLUSTER BY (event_type, event_date);   -- PO maintains this; CLUSTER BY AUTO lets PO pick keys

07VACUUM retention: delta.deletedFileRetentionDuration (default 7 days)

PO's VACUUM deletes unreferenced files older than the retention window set by delta.deletedFileRetentionDuration (default 7 days). Time travel can only reach as far back as files still exist, so the window bounds how far back you can travel. If you need longer time travel, raise the property BEFORE enabling PO — otherwise PO can delete files you expected to reach.

SQL-- Need 30 days of time travel? RAISE retention BEFORE enabling PO,
-- or PO's VACUUM (default 7 days) reclaims files you wanted to keep.
ALTER TABLE analytics.prod.events
  SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = '30 days');

-- THEN enable PO (e.g. at schema or catalog level) so it respects the new window.
ALTER SCHEMA analytics.prod ENABLE PREDICTIVE OPTIMIZATION;

08Runs on serverless compute (serverless jobs SKU)

PO executes on serverless compute, billed under the serverless jobs SKU, asynchronously in the background — it never runs on, or competes with, your interactive/job clusters. It's not free, but it's typically cheaper and more correct than over-running hand-scheduled jobs. Monitor cost via the system table (below).

09Monitor with the system table

Every PO operation is logged to system.storage.predictive_optimization_operations_history — query which tables PO touched, which op ran, and the metrics/cost.

SQL-- What has PO done lately, and what did it cost? Audit + cost attribution.
SELECT operation_type, catalog_name, schema_name, table_name,
       operation_status, start_time
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= current_timestamp() - INTERVAL 7 DAYS
ORDER BY start_time DESC;

10Verify the effective state with DESCRIBE … EXTENDED

Use DESCRIBE (CATALOG | SCHEMA | TABLE) EXTENDED name to read the "Predictive Optimization" field and see the effective state (including whether it's inherited). Because of inheritance + overrides, the effective state isn't always obvious — this is the source of truth per object.

SQL-- Confirm PO is in effect (and whether it's inherited) at each level:
DESCRIBE CATALOG EXTENDED analytics;                 -- catalog-level state
DESCRIBE SCHEMA  EXTENDED analytics.prod;            -- schema-level state
DESCRIBE TABLE   EXTENDED analytics.prod.events;     -- table "Predictive Optimization" field

11What PO does NOT run on

• OpenSharing (Delta Sharing) recipient tables — managed by the provider.
• EXTERNAL tables — managed via your own external storage/jobs.

PO needs ownership/lifecycle control over the table's files. For external/legacy tables you still schedule maintenance manually, or migrate them to UC managed tables to get PO.

PO vs. hand-scheduled maintenance

Uses, edge cases & limitations

Uses (when to reach for it)

• Default for UC managed tables. Enable at catalog/schema level and let PO run OPTIMIZE/VACUUM/ANALYZE — especially across many tables where hand-scheduling doesn't scale.
• Pairs with liquid clustering. PO maintains LC tables and drives CLUSTER BY AUTO key selection — the modern "managed table + LC + PO" default.
• When NOT to: external/hive_metastore tables, OpenSharing recipient tables, or when you need manual control over VACUUM timing.

Edge cases an interviewer probes

• Child DISABLE vs. later parent ENABLE → the DISABLE wins; set to INHERIT/ENABLE to re-cover.
• You relied on ZORDER → PO won't maintain it (ignores Z-ordered files); migrate to liquid clustering.
• You need long time travel → raise deletedFileRetentionDuration before enabling PO.
• External or shared-in table → PO doesn't run there at all.
• Cost surprise → PO bills serverless jobs DBUs; check the system table.
• Not owner → you can't flip the toggle (need catalog/schema owner or account admin).

Limitations

• UC managed tables only (Delta + Iceberg); not external, not OpenSharing recipients.
• Requires Premium plan, supported region, SQL warehouse or DBR 12.2 LTS+.
• PO's OPTIMIZE does not run ZORDER.
• Account-level enablement is UI-only (accounts console); catalog/schema via SQL.
• Runs on serverless (billed serverless jobs SKU).
• VACUUM is bound by delta.deletedFileRetentionDuration (default 7 days).

Common gotchas

• Raise retention BEFORE enabling PO if you need long time travel — PO's VACUUM uses the 7-day default.
• PO does not Z-order. Use liquid clustering (and let PO drive CLUSTER BY AUTO).
• A child DISABLE beats a parent ENABLE. Set the level back to INHERIT/ENABLE to re-cover it.
• Managed-only. External, hive_metastore, and OpenSharing recipient tables get nothing — keep manual maintenance or migrate.
• Account toggle is in the accounts console, not SQL — catalog/schema overrides are SQL.
• It's billed. PO consumes serverless jobs DBUs; monitor system.storage.predictive_optimization_operations_history.
• Verify, don't assume. Use DESCRIBE … EXTENDED to confirm the effective state per object.

Whole-track recap — the decision ladder

This is the final lesson, so here is the entire track as one decision ladder. Each rung solves a problem the previous rung exposed.