Delta History, Table Optimization and Query Fixes

What it is

Every write to a Delta table creates a new version in its transaction log. DESCRIBE HISTORY shows who changed the table, when and how, and time travel lets you query an earlier version to validate or compare results.

Predictive optimization and automatic Liquid Clustering keep Unity Catalog managed tables fast without manual maintenance. When a query is fast but wrong, the fix is in the SQL logic, usually a join, a filter or NULL handling.

How it works

Auditing with DESCRIBE HISTORY

DESCRIBE HISTORY main.sales.orders;
DESCRIBE HISTORY main.sales.orders LIMIT 5;

Each row is one version, newest first. The key columns are version, timestamp, userName, operation (such as WRITE, MERGE, DELETE or OPTIMIZE), operationParameters and operationMetrics, which holds counts such as rows written. This answers audit questions like who deleted rows yesterday.

Catalog Explorer shows the same information on the table's History tab.

Validating and comparing with time travel

SELECT COUNT(*) FROM main.sales.orders VERSION AS OF 12;
SELECT * FROM main.sales.orders TIMESTAMP AS OF '2026-10-01';
SELECT * FROM main.sales.orders
EXCEPT
SELECT * FROM main.sales.orders VERSION AS OF 12;

The EXCEPT query returns rows in the current version that were not in version 12, which validates what a load changed. Comparing an aggregate across two versions shows a trend or checks a report against last week's numbers.

If a bad write must be undone, RESTORE TABLE main.sales.orders TO VERSION AS OF 12; returns the table to that version and is itself recorded as a new version. Time travel only works while the old data files still exist; VACUUM removes unreferenced files older than the retention period, 7 days by default.

Predictive optimization

Predictive optimization runs OPTIMIZE, VACUUM and ANALYZE automatically on Unity Catalog managed tables, choosing when to run them from how the table is used. It does not apply to external tables.

It is on by default for accounts created on or after November 11, 2024, and can be set at account, catalog or schema level, with lower levels inheriting.

ALTER CATALOG main ENABLE PREDICTIVE OPTIMIZATION;
ALTER SCHEMA main.sales INHERIT PREDICTIVE OPTIMIZATION;

Its operations are recorded in system.storage.predictive_optimization_operations_history. With it on, scheduled OPTIMIZE jobs are no longer needed.

Automatic Liquid Clustering

Liquid Clustering arranges data by clustering keys so queries that filter on those columns skip files. With CLUSTER BY AUTO, Databricks reads the table's query history and picks the keys itself, changing them when the access pattern changes.

CREATE TABLE main.sales.orders_c (id BIGINT, region STRING, order_date DATE)
CLUSTER BY AUTO;
ALTER TABLE main.sales.orders CLUSTER BY AUTO;

It works on Unity Catalog managed tables and needs predictive optimization. Keys may not be chosen for very small tables, tables with few queries or tables that already cluster well.

Fixing a query

Most wrong results come from a handful of SQL patterns.

SELECT c.id, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.cust_id = c.id AND o.status = 'PAID'
GROUP BY c.id
HAVING COALESCE(SUM(o.amount), 0) < 100;

Here customers with no paid orders stay in the result with a total of 0. With o.status = 'PAID' in WHERE, they would disappear.

How the exam asks about this

• "Find who ran the DELETE on a table and how many rows it removed." DESCRIBE HISTORY, reading userName and operationMetrics.
• "Query filter columns change every quarter and no one wants to tune clustering keys." CLUSTER BY AUTO on the managed table.
• "A LEFT JOIN report lost customers with no orders after a status filter was added." Move the filter from WHERE to the ON clause.