Delta tables on autopilot: liquid clustering and predictive optimization in Databricks

#1. Why change

#2. Liquid clustering

New table:

CREATE TABLE prod_sales.orders.orders (
  order_id BIGINT, customer_id BIGINT, order_date DATE, region STRING, amount DECIMAL(18,2))
CLUSTER BY (order_date, customer_id);

Existing unpartitioned Delta table:

ALTER TABLE prod_sales.orders.orders CLUSTER BY (order_date, customer_id);
OPTIMIZE prod_sales.orders.orders FULL;   -- recluster existing data once (can be heavy)

Partitioned tables need to be rewritten (CTAS / deep clone into a clustered table) to switch.

Automatic liquid clustering lets Databricks choose keys from query patterns:

ALTER TABLE prod_sales.orders.orders CLUSTER BY AUTO;

#Choosing keys (if you choose them)

• 1–4 columns used most in filters and joins (dates, IDs, region).
• Avoid very low-cardinality alone (a 3-value flag) and columns nobody filters on.
• Change keys when query patterns change — only new data is clustered by the new keys until optimised.

#3. Predictive optimization

Enable at account, catalog or schema level (managed tables only):

ALTER CATALOG prod_sales ENABLE PREDICTIVE OPTIMIZATION;
ALTER SCHEMA prod_sales.staging DISABLE PREDICTIVE OPTIMIZATION;   -- opt out where needed

It runs OPTIMIZE, VACUUM and statistics collection (ANALYZE) when the platform predicts a benefit, using serverless compute billed to your account.

Check what it did:

SELECT table_name, operation_type, operation_status, start_time, usage_quantity
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= current_date() - 7
ORDER BY start_time DESC;

#4. Retire the old jobs

Once predictive optimization is on for a catalog, remove your scheduled OPTIMIZE/VACUUM jobs for those tables (running both wastes compute). Keep explicit maintenance only for external tables and special cases.

#5. Things to keep in mind

• VACUUM retention still follows table properties (delta.deletedFileRetentionDuration) — set it for tables that need longer time travel.
• Streaming tables with very frequent small writes benefit most from optimised writes and auto compaction.
• Measure: query duration and bytes read for top queries before and after.

Tip

Turn on predictive optimization for a non-critical catalog first, review the operations history and cost for two weeks, then enable it account-wide with opt-outs where needed.