What Predictive Optimization is doing to your Delta tables and what it costs. Clustering / compaction / VACUUM history, table inventory, time-travel retention and Iceberg / UniForm metadata.
The audit log of every maintenance operation that Predictive Optimization (and automatic/liquid clustering) performed on your Unity Catalog managed tables — OPTIMIZE/compaction, liquid CLUSTERING, VACUUM, and data-skipping/clustering column-selection decisions — each stamped with an estimated DBU cost.
table_id), of a given operation_type, at a given start_time/end_time. The queries here aggregate that grain up to per-table / per-op-type / per-status.account_id, workspace_id, metastore_name, catalog_name, schema_name, table_name, table_id — where the table lives; table_id is the stable identity (name can be reused/renamed).operation_type — the maintenance kind: CLUSTERING, VACUUM, AUTO_CLUSTERING_COLUMN_SELECTION, DATA_SKIPPING_COLUMN_SELECTION (and OPTIMIZE/compaction). Each type populates a different set of keys in operation_metrics.operation_status — outcome; observed enum is SUCCESSFUL or 'FAILED: INTERNAL_ERROR' (note the embedded colon). Drives per-table success-rate / failing-table findings.operation_metrics — map<string,string> of per-op counters; must be cast for numeric use. Documented keys by type: CLUSTERING → number_of_removed_files, number_of_clustered_files, amount_of_data_removed_bytes, amount_of_clustered_data_bytes; VACUUM → number_of_deleted_files, amount_of_data_deleted_bytes; AUTO_CLUSTERING_COLUMN_SELECTION → has_column_selection_changed, old_clustering_columns, new_clustering_columns, additional_reason (all categorical strings; old_clustering_columns = 'None' when previously unpartitioned); DATA_SKIPPING_COLUMN_SELECTION → added_/removed_/new_data_skipping_columns (string lists), amount_of_scanned_bytes, number_of_scanned_files (+ an old_data_skipping_columns key not selected here).usage_quantity + usage_unit — the ESTIMATED_DBU consumed by the op (SUM only; it is an estimate, not the billed line). Value may lag the operation row up to ~24h as billing populates, so the most recent day is provisional.start_time, end_time — op window; used for the 30-day filter and for MIN/MAX first/last-op times.SELECT on system.storage. If PO is off or nothing ran in-window, the query returns zero rows (valid, not an error); if the schema isn't enabled you may get TABLE_OR_VIEW_NOT_FOUND.The Unity Catalog catalog-of-tables view — the inventory of every table/view the querying principal can see.
table_type — MANAGED, EXTERNAL, or VIEW (doc-confirmed). EXTERNAL = not PO-eligible, so this bounds the PO coverage gap.table_catalog, table_schema — location (plausible information_schema names; unverified in this repo's doc audit).data_source_format — e.g. DELTA / ICEBERG, used to flag Iceberg/UniForm candidates (unverified name/presence).COUNT(*) as table_count — inventory size per group.information_schema under Unity Catalog (per-catalog view exposed under system.information_schema for the metastore). Privilege-aware — a principal sees only tables it has privileges on, so counts reflect the collector's grants, not necessarily the whole metastore. Carries no size columns (size requires the ANALYZE probe below). Requires UC; empty/partial if the principal lacks broad grants.> Note: system.storage.table_metrics_history is a primary table of this domain but is not referenced by any query in this folder — physical/size metrics here come from the on-demand ANALYZE probe instead (see Notes).
## Non-system-table probes (per-table, run on a warehouse)
These are not system tables — they are on-demand statements the collection engine runs per target table because the data does not exist in any system table.
storage_breakdown_analyze)Returns a single row of physical size: total_bytes, num_total_files, active_bytes, num_active_files, vacuumable_bytes, num_vacuumable_files, time_travel_bytes, num_time_travel_files. GA but DBR 18.0+ only. Computed at run time, not stored in UC and not returned by DESCRIBE EXTENDED — no history/trend, the engine must run it per-table and persist the result itself.
table_props_time_travel_config, iceberg_uniform_metadata)Delta table properties (delta.logRetentionDuration, delta.deletedFileRetentionDuration, delta.dataSkippingNumIndexedCols, delta.dataSkippingStatsColumns, delta.autoOptimize.optimizeWrite, delta.autoOptimize.autoCompact, delta.enableDeletionVectors) and the "Delta Uniform Iceberg" metadata section. Retention values are CalendarInterval strings (e.g. 'interval 30 days') that must be parsed to days.
system.* schemas aren’t uniformly available — several (e.g. system.query, system.lakeflow) are still in preview or must be enabled per-metastore. A query that returns nothing usually means its schema isn’t enabled for your account yet.system.billing.list_prices) unless a query explicitly joins account_prices.For a single target table, returns its full extended metadata rows so the "Delta Uniform Iceberg" section can be read to detect whether the table is UniForm / managed-Iceberg enabled.
One row per key = a DESCRIBE EXTENDED output line for the target table. The rows under the 'Delta Uniform Iceberg' section are what matter - their presence (and their format-name value) tells you whether the table is UniForm-enabled / has a synced Iceberg metadata pointer.
RequiresDESCRIBE / SELECT privilege on the target table; Unity Catalog required (UniForm / managed-Iceberg detection is a UC feature)
UniForm and managed-Iceberg tables are governed and billed differently from plain Delta, and they never appear as Predictive Optimization-eligible managed tables, so they are a blind spot in the automatic-maintenance cost picture. Knowing which external/non-Delta tables actually expose Iceberg metadata tells an admin where cross-engine (Iceberg-reader) access is live and which tables sit outside PO's OPTIMIZE/VACUUM coverage. It is the detection step that turns a raw "EXTERNAL / non-DELTA" inventory flag into a confirmed UniForm finding.
-- Run per target table; substitute your catalog.schema.table for main.sales.orders.
DESCRIBE EXTENDED main.sales.orders;catalog.schema.table), the column list plus the detailed/table-information section.| Column | How to read it |
|---|---|
| col_name | The name of each column in the table, followed in later rows by detailed-metadata labels (e.g. table properties and the 'Delta Uniform Iceberg' section header). Scan this column for the UniForm/Iceberg entry. |
| data_type | For real columns, the column's data type; for the extended detail rows, the value paired with the label in col_name (e.g. the Iceberg metadata value). |
| comment | The column or property comment when one exists, otherwise null; usually blank for the extended metadata rows. |
Inventory / reference query — it returns state to read, not a pass/fail band.
| col_name | data_type | comment |
|---|---|---|
| order_id | bigint | NULL |
| # Detailed Table Information | NULL | NULL |
| Delta Uniform Iceberg | metadata_location=s3://warehouse/main/sales/orders/metadata/00042.metadata.json | NULL |
Run this only on tables the inventory query flagged as EXTERNAL / non-DELTA, then scan the returned rows for the "Delta Uniform Iceberg" section to confirm which tables are UniForm / managed-Iceberg enabled and record the exact section key strings so the parser can be finalized; treat presence of the section as the signal, not table size.
Feeds into: Iceberg / external metadata (UniForm / managed-Iceberg detection)
Per managed Unity Catalog table, a 30-day summary of how many liquid/automatic CLUSTERING operations ran, the files and bytes they compacted (removed vs. clustered), and the estimated DBUs that clustering consumed.
system.storage.predictive_optimization_operations_historyOne row = a table's incremental liquid-clustering (CLUSTERING) activity over the window. The column that matters is clustering_estimated_dbu - the DBUs Predictive Optimization spent compacting/re-clustering that table. Some clustering activity is normal; a table burning outsized DBUs on it repeatedly is the signal worth chasing.
RequiresSELECT on system.storage; Public Preview (system.storage.predictive_optimization_operations_history), regional
Automatic liquid clustering silently rewrites your managed tables and bills the DBUs to your account, so this is where you see which tables are absorbing the most maintenance compute and whether that work is productive. A table with a high op_count and large removed_bytes but poorly chosen clustering keys can burn DBUs re-clustering the same data repeatedly - money spent with little layout benefit. By ranking tables on clustering_estimated_dbu and comparing clustered vs. removed volume, an admin can spot runaway maintenance cost and decide whether to pin clustering columns manually or throttle PO. The DBU figure is an estimate, so it flags candidates for cost review rather than serving as the billed number.
SELECT catalog_name, schema_name, table_id, table_name, operation_type,
COUNT(*) AS op_count,
SUM(CAST(operation_metrics['number_of_removed_files'] AS BIGINT)) AS removed_files,
SUM(CAST(operation_metrics['number_of_clustered_files'] AS BIGINT)) AS clustered_files,
SUM(CAST(operation_metrics['amount_of_data_removed_bytes'] AS BIGINT)) AS removed_bytes,
SUM(CAST(operation_metrics['amount_of_clustered_data_bytes'] AS BIGINT)) AS clustered_bytes,
SUM(CAST(usage_quantity AS DECIMAL(38,6))) AS clustering_estimated_dbu,
-- status: worst-first band on clustering DBU spend per table (field heuristic; :warn_clustering_dbu / :crit_clustering_dbu).
CASE
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) IS NULL THEN 'NOT_ASSESSED'
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :crit_clustering_dbu THEN 'CRITICAL'
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :warn_clustering_dbu THEN 'WARN'
ELSE 'OK'
END AS status
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'CLUSTERING'
AND start_time >= current_date() - INTERVAL :period_days DAYS AND start_time < current_date()
GROUP BY catalog_name, schema_name, table_id, table_name, operation_type
ORDER BY clustering_estimated_dbu DESC:period_daysdefault 30rolling window in days:warn_clustering_dbudefault 50estimated clustering DBUs per table over the window that flags WARN:crit_clustering_dbudefault 200estimated clustering DBUs per table over the window that flags CRITICALtable_id.op_count) and how much it churned - files and bytes removed vs. clustered back - so you can see the compaction ratio per table.| Column | How to read it |
|---|---|
| catalog_name | Unity Catalog catalog the clustered table lives in. |
| schema_name | Schema (database) within the catalog. |
| table_id | Stable UC table identity (UUID); use this to track a table across renames, not table_name. |
| table_name | Human-readable table name at report time; may have changed if the table was renamed. |
| operation_type | Always 'CLUSTERING' here - the query is scoped to incremental liquid/automatic clustering compaction. |
| op_count | Number of CLUSTERING operations run on this table in the 30-day window; a high count means PO is compacting this table frequently. |
| removed_files | Total small/fragmented files PO removed across those ops - the input churn being compacted away. |
| clustered_files | Total files PO wrote as the newly clustered output; compare with removed_files to see the compaction ratio. |
| removed_bytes | Total bytes in the removed files (bytes, not GB) - the data volume read and rewritten. |
| clustered_bytes | Total bytes written as clustered output (bytes); usually close to removed_bytes since clustering rewrites rather than deletes data. |
| clustering_estimated_dbu | Summed ESTIMATED_DBU consumed by these clustering ops - DBUs (an estimate), not dollars and not the billed amount; multiply by your DBU rate for an approximate cost. |
clustering_estimated_dbu below :warn_clustering_dbu DBUs per table over the window (field heuristic - tune :warn_clustering_dbu for your account).
clustering_estimated_dbu at/above :warn_clustering_dbu (WARN) or :crit_clustering_dbu (CRITICAL) - field heuristic; also compare clustered_bytes against removed_bytes - if clustered_bytes is small relative to removed_bytes and DBU is high, clustering may be thrashing rather than compacting.
| schema_name | table_name | op_count | removed_files | clustered_files | removed_bytes | clustering_estimated_dbu |
|---|---|---|---|---|---|---|
| sales | fct_orders | 42 | 18400 | 2100 | 512000000000 | 86.500000 |
| marketing | web_events | 9 | 3200 | 640 | 94000000000 | 12.250000 |
| finance | gl_journal | 1 | 120 | 30 | 1800000000 | 0.400000 |
For each table, compare clustered_files/clustered_bytes against removed_files/removed_bytes to judge how much data churn liquid clustering is chewing through, then multiply clustering_estimated_dbu by your maintenance DBU rate to get an approximate maintenance cost; pin clustering keys manually or throttle PO on any table whose DBU is high but whose clustered vs. removed volume looks like unproductive churn, and reconcile the DBU against system.billing.usage before quoting a dollar figure.
Feeds into: clustering activity (automatic clustering / predictive optimization); clustering/PO maintenance cost
Per managed table (and per distinct old-to-new/reason signature), the auto-clustering column-selection decisions over the last 30 days — whether the keys changed, the old to new column sets, the stated reason, when it last happened, and how many times.
system.storage.predictive_optimization_operations_historyOne row = a table + old-to-new clustering-column signature (or "no change") seen at least once in the window. selection_event_count is how many times that exact old-to-new (or unchanged) selection happened; has_column_selection_changed = 'true' rows are where Predictive Optimization actually swapped the clustering keys - repeated true rows on the same table with a rising event count mean the keys keep getting relitigated, which is layout instability, not a one-off tuning pass.
RequiresSELECT on system.storage; Public Preview (system.storage.predictive_optimization_operations_history), regional
Liquid/automatic clustering keys that keep changing mean Databricks is repeatedly re-laying-out the same table's data, which burns maintenance DBUs and never lets data-skipping settle, so queries stay slow. A table whose auto-selected columns churn every few days is a strong signal the workload's access pattern doesn't match any single key, or that the auto-selector is chasing noise. Spotting these lets an admin pin the clustering columns manually and stop paying for layout thrash that delivers no lasting pruning benefit.
SELECT catalog_name, schema_name, table_id, table_name,
operation_metrics['has_column_selection_changed'] AS has_column_selection_changed,
operation_metrics['old_clustering_columns'] AS old_clustering_columns,
operation_metrics['new_clustering_columns'] AS new_clustering_columns,
operation_metrics['additional_reason'] AS additional_reason,
MAX(end_time) AS last_selection_time,
COUNT(*) AS selection_event_count,
-- status: worst-first band on repeated true churn events per table (field heuristic; :warn_churn_events / :crit_churn_events).
CASE
WHEN operation_metrics['has_column_selection_changed'] IS NULL THEN 'NOT_ASSESSED'
WHEN operation_metrics['has_column_selection_changed'] = 'true' AND COUNT(*) >= :crit_churn_events THEN 'CRITICAL'
WHEN operation_metrics['has_column_selection_changed'] = 'true' AND COUNT(*) >= :warn_churn_events THEN 'WARN'
ELSE 'OK'
END AS status
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'AUTO_CLUSTERING_COLUMN_SELECTION'
AND start_time >= current_date() - INTERVAL :period_days DAYS AND start_time < current_date()
GROUP BY catalog_name, schema_name, table_id, table_name,
operation_metrics['has_column_selection_changed'], operation_metrics['old_clustering_columns'],
operation_metrics['new_clustering_columns'], operation_metrics['additional_reason']
ORDER BY CASE status WHEN 'CRITICAL' THEN 0 WHEN 'WARN' THEN 1 WHEN 'OK' THEN 2 ELSE 3 END, selection_event_count DESC:period_daysdefault 30rolling window in days:warn_churn_eventsdefault 2column-selection-changed events per table over the window that flags WARN:crit_churn_eventsdefault 5that flags CRITICALhas_column_selection_changed) and the old to new column sets, plus the reason the selector gave.selection_event_count on a changed row means the auto-selector keeps swapping keys, i.e. an unstable layout or a poor key choice.old_clustering_columns = 'None' marks tables that were previously unclustered.| Column | How to read it |
|---|---|
| catalog_name | Unity Catalog catalog the table lives in. |
| schema_name | Schema (database) within the catalog. |
| table_id | Stable UC table identity; use this to track a table across renames rather than the name. |
| table_name | Human-readable table name at selection time (can be renamed/reused, so treat as a label). |
| has_column_selection_changed | Categorical string 'true'/'false': whether this column-selection event actually swapped the clustering keys. 'true' is the churn signal. |
| old_clustering_columns | The clustering columns before this decision; the literal string 'None' means the table was previously unpartitioned/unclustered. |
| new_clustering_columns | The clustering columns the auto-selector chose in this event. |
| additional_reason | Categorical string explaining why the selector acted (e.g. query-pattern shift); context for the change. |
| last_selection_time | MAX(end_time): most recent end time an event with this exact table + old-to-new/reason/changed combination occurred within the window. |
| selection_event_count | COUNT(*): count of column-selection events matching this table and this exact old/new/reason/changed combination over the 30 days; a high count on a 'true' row means the layout is thrashing. |
no has_column_selection_changed = 'true' rows, or fewer than :warn_churn_events change events per table over the window (field heuristic - a single key selection/change is normal, especially on a newly clustered table).
has_column_selection_changed = 'true' with selection_event_count at/above :warn_churn_events (WARN) or :crit_churn_events (CRITICAL) - field heuristic; repeated churn on the same table means the clustering keys are unstable, which burns DBUs in po_clustering_activity without settling into a stable layout.
| catalog_name | schema_name | table_name | has_column_selection_changed | old_clustering_columns | new_clustering_columns | selection_event_count |
|---|---|---|---|---|---|---|
| prod | sales | orders | true | order_date | customer_id | 7 |
| prod | finance | ledger_entries | true | None | posting_date | 1 |
| analytics | web | page_events | false | session_id | session_id | 3 |
Find tables where `has_column_selection_changed` is `true` with high `selection_event_count` (the auto-clusterer keeps re-picking keys). For those, review the workload's join/filter patterns and pin the clustering columns manually with `ALTER TABLE ... CLUSTER BY` so the layout stops thrashing; leave stable tables (change = `false`) alone.
Feeds into: clustering-column churn (unstable/poorly chosen liquid-clustering keys); clustering activity
One row per managed table and distinct data-skipping column-change set, showing which stat columns Predictive Optimization added, removed, or newly indexed for pruning, plus the bytes and files it scanned to do so, over the trailing 30 days.
system.storage.predictive_optimization_operations_historyOne row = a table + distinct added/removed/new data-skipping-column set seen in the window. scanned_bytes is how many bytes Predictive Optimization had to scan to decide on (or confirm) data-skipping columns for that table; new_data_skipping_columns is what it landed on. A row with a large scanned_bytes but an empty new_data_skipping_columns means the backfill ran and paid its scan cost but did not end up adding any pruning columns for that table.
RequiresSELECT on system.storage; Public Preview (system.storage.predictive_optimization_operations_history), regional
Data skipping is what lets Databricks prune files at query time instead of scanning whole tables, so knowing that Predictive Optimization picked the right stat columns is the difference between a full-table scan and a targeted read on your most expensive queries. This view shows which columns PO chose to add or drop, which is the first thing to check when a table's queries suddenly cost more or a dashboard slows down. If the same table keeps having columns removed and re-added, the auto-selection is thrashing and you may be paying repeatedly to rebuild stats that never settle. It does not prove pruning improved -- and because it does not filter operation_status, a listed decision may even have failed -- but it tells you whether the stats selection is being maintained at all.
SELECT catalog_name, schema_name, table_id, table_name,
operation_metrics['added_data_skipping_columns'] AS added_data_skipping_columns,
operation_metrics['removed_data_skipping_columns'] AS removed_data_skipping_columns,
operation_metrics['new_data_skipping_columns'] AS new_data_skipping_columns,
SUM(CAST(operation_metrics['amount_of_scanned_bytes'] AS BIGINT)) AS scanned_bytes,
SUM(CAST(operation_metrics['number_of_scanned_files'] AS BIGINT)) AS scanned_files,
MAX(end_time) AS last_event_time,
-- status: worst-first band on scan cost with zero data-skipping-column gain (field heuristic; :warn_scan_gb / :crit_scan_gb).
CASE
WHEN SUM(CAST(operation_metrics['amount_of_scanned_bytes'] AS BIGINT)) IS NULL THEN 'NOT_ASSESSED'
WHEN (operation_metrics['new_data_skipping_columns'] IS NULL OR operation_metrics['new_data_skipping_columns'] = '')
AND SUM(CAST(operation_metrics['amount_of_scanned_bytes'] AS BIGINT)) >= :crit_scan_gb * 1e9 THEN 'CRITICAL'
WHEN (operation_metrics['new_data_skipping_columns'] IS NULL OR operation_metrics['new_data_skipping_columns'] = '')
AND SUM(CAST(operation_metrics['amount_of_scanned_bytes'] AS BIGINT)) >= :warn_scan_gb * 1e9 THEN 'WARN'
ELSE 'OK'
END AS status
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'DATA_SKIPPING_COLUMN_SELECTION'
AND start_time >= current_date() - INTERVAL :period_days DAYS AND start_time < current_date()
GROUP BY catalog_name, schema_name, table_id, table_name,
operation_metrics['added_data_skipping_columns'], operation_metrics['removed_data_skipping_columns'],
operation_metrics['new_data_skipping_columns']
ORDER BY CASE status WHEN 'CRITICAL' THEN 0 WHEN 'WARN' THEN 1 WHEN 'OK' THEN 2 ELSE 3 END, scanned_bytes DESC:period_daysdefault 30rolling window in days:warn_scan_gbdefault 100GB scanned by a backfill event that added no new data-skipping columns, over the window, that flags WARN:crit_scan_gbdefault 500GB that flags CRITICAL| Column | How to read it |
|---|---|
| catalog_name | Unity Catalog catalog the table lives in. |
| schema_name | Schema (database) within the catalog. |
| table_id | Stable UC table identifier -- the reliable key to join or track a table across renames. |
| table_name | Human-readable table name; can be renamed or reused over time, so treat table_id as the true identity. |
| added_data_skipping_columns | Column(s) newly given data-skipping stats in this selection (string list). Empty/None means nothing was added this round. |
| removed_data_skipping_columns | Column(s) whose data-skipping stats were dropped (string list). Recurring removals signal auto-selection churn. |
| new_data_skipping_columns | The full resulting set of columns now carrying data-skipping stats after this decision (string list). |
| scanned_bytes | SUM of amount_of_scanned_bytes across the grouped ops, in BYTES (not DBUs, not dollars). |
| scanned_files | SUM of number_of_scanned_files across the grouped ops -- an integer file count. |
| last_event_time | MAX(end_time) -- timestamp of the most recent column-selection event in this group; how current the decision is. |
new_data_skipping_columns is populated whenever a backfill event scans data, or scanned_bytes stays under :warn_scan_gb GB for events with no new columns (field heuristic - tune :warn_scan_gb for your account).
no new_data_skipping_columns AND scanned_bytes at/above :warn_scan_gb GB (WARN) or :crit_scan_gb GB (CRITICAL) - field heuristic; this only tells you stats were (re)computed, not whether pruning actually improved - cross-check query_pruning_effectiveness for the payoff.
| catalog_name | schema_name | table_id | table_name | added_data_skipping_columns | removed_data_skipping_columns | new_data_skipping_columns | scanned_bytes | scanned_files | last_event_time |
|---|---|---|---|---|---|---|---|---|---|
| prod | sales | a1f3c8e0-2b44-4d91-9a77-0e51d2c6b8aa | orders | [order_date, customer_id] | [] | [order_date, customer_id, region] | 48318382080 | 1240 | 2026-07-05 03:12:44 |
| prod | telemetry | 7c9e10b2-5f88-4a3e-bb21-9d4407e1f2c3 | events_raw | [event_ts] | [] | [event_ts] | 9663676416 | 512 | 2026-07-04 22:47:09 |
| analytics | finance | 3d2b6f14-8c07-4e59-9f10-6a1b2c7d0e45 | ledger_txn | [] | [legacy_region] | [account_id, txn_ts] | 1073741824 | 64 | 2026-07-02 06:05:31 |
For each table with churning stat columns (a non-empty `removed_data_skipping_columns` or repeated `new_data_skipping_columns`), confirm a data-skipping column-selection decision ran, then pivot to `system.query.history` for the same tables to check whether the added columns actually improved prune ratio -- pin `delta.dataSkippingStatsColumns` manually if the auto-selection is thrashing. Because this query does not filter `operation_status`, cross-check status before assuming a listed decision actually completed.
Feeds into: data-skipping / pruning maintenance (which columns got stats backfilled); search optimization (data-skipping coverage)
One row per table x maintenance-type x outcome over the last 30 days, giving the operation count, summed estimated maintenance DBUs, and first/last op times.
system.storage.predictive_optimization_operations_historyOne row = a table x operation_type (CLUSTERING/VACUUM/etc) x operation_status combo over the window. estimated_dbu is the DBUs Predictive Optimization spent maintaining that table for that operation type - the columns that matter are operation_status (SUCCESSFUL vs a 'FAILED: INTERNAL_ERROR' row, note the embedded colon) and estimated_dbu; a table with FAILED rows means maintenance is not actually completing even though you may be paying for the attempts.
RequiresSELECT on system.storage; Public Preview (system.storage.predictive_optimization_operations_history), regional, 180-day retention
This is the master view of what automatic table maintenance is actually costing you: it turns Predictive Optimization's background OPTIMIZE, clustering, and VACUUM work into a summed DBU line per table so you can see where maintenance spend concentrates. It simultaneously exposes reliability, because splitting each table by `operation_status` surfaces tables whose maintenance ops are landing as `FAILED: INTERNAL_ERROR` rather than succeeding. A table stuck in repeated failures is silently accumulating un-optimized files and storage bloat, while a table with runaway DBUs is a cost outlier worth investigating. The DBU figure is an estimate for triage, not the billed charge.
SELECT account_id, workspace_id, metastore_name, catalog_name, schema_name, table_id, table_name,
operation_type, operation_status, usage_unit,
COUNT(*) AS operation_count,
SUM(CAST(usage_quantity AS DECIMAL(38,6))) AS estimated_dbu,
MIN(start_time) AS first_op_time, MAX(end_time) AS last_op_time,
-- status: worst-first band; any FAILED row is CRITICAL, else banded on estimated_dbu (field heuristic; :warn_maint_dbu / :crit_maint_dbu).
CASE
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) IS NULL THEN 'NOT_ASSESSED'
WHEN operation_status LIKE 'FAILED%' THEN 'CRITICAL'
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :crit_maint_dbu THEN 'CRITICAL'
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :warn_maint_dbu THEN 'WARN'
ELSE 'OK'
END AS status
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= current_date() - INTERVAL :period_days DAYS
AND start_time < current_date()
GROUP BY account_id, workspace_id, metastore_name, catalog_name, schema_name, table_id, table_name,
operation_type, operation_status, usage_unit
ORDER BY CASE status WHEN 'CRITICAL' THEN 0 WHEN 'WARN' THEN 1 WHEN 'OK' THEN 2 ELSE 3 END, estimated_dbu DESC:period_daysdefault 30rolling window in days:warn_maint_dbudefault 20estimated maintenance DBUs per table+operation+status group over the window that flags WARN:crit_maint_dbudefault 100that flags CRITICAL (a FAILED operation_status always flags CRITICAL regardless of these)SUCCESSFUL vs. FAILED: INTERNAL_ERROR maintenance is visible side by side for a per-table success-rate read.| Column | How to read it |
|---|---|
| account_id | Databricks account UUID that owns the maintenance activity. |
| workspace_id | Workspace the operation was attributed to; join keys elsewhere are workspace-scoped. |
| metastore_name | Unity Catalog metastore hosting the table. |
| catalog_name | UC catalog the table lives in. |
| schema_name | UC schema (database) the table lives in. |
| table_id | Stable UC identity of the table; use this over the name, since names can be renamed or reused. |
| table_name | Human-readable table name at the time of the op (not guaranteed stable). |
| operation_type | The maintenance kind rolled up in this row: e.g. `CLUSTERING`, `VACUUM`, `AUTO_CLUSTERING_COLUMN_SELECTION`, `DATA_SKIPPING_COLUMN_SELECTION` (and OPTIMIZE/compaction). |
| operation_status | Outcome for this slice; observed values are `SUCCESSFUL` or `FAILED: INTERNAL_ERROR` (note the embedded colon). Compare counts across statuses for the same table to read its success rate. |
| usage_unit | Unit of the summed quantity; here always `ESTIMATED_DBU` — an estimate, not the billed unit. |
| operation_count | Number of maintenance operations (COUNT(*)) in this table/type/status slice over the window. |
| estimated_dbu | Sum of estimated DBUs (not dollars) consumed by those operations; a triage cost line to reconcile against `system.billing.usage`. |
| first_op_time | Earliest operation start_time in this slice — when maintenance of this kind first ran in-window. |
| last_op_time | Latest operation end_time in this slice — recency signal; stale tables lag here. |
no FAILED rows for the table, and estimated_dbu under :warn_maint_dbu per operation+status group over the window (field heuristic - tune :warn_maint_dbu for your account).
any operation_status LIKE 'FAILED%' row (CRITICAL - maintenance is not completing on that table), or estimated_dbu at/above :warn_maint_dbu (WARN) / :crit_maint_dbu (CRITICAL) - field heuristic.
| catalog_name | schema_name | table_name | operation_type | operation_status | operation_count | estimated_dbu | usage_unit |
|---|---|---|---|---|---|---|---|
| prod_sales | curated | orders_fact | CLUSTERING | SUCCESSFUL | 42 | 318.740000 | ESTIMATED_DBU |
| prod_sales | curated | orders_fact | VACUUM | SUCCESSFUL | 6 | 54.120000 | ESTIMATED_DBU |
| prod_sales | raw | events_stream | CLUSTERING | FAILED: INTERNAL_ERROR | 11 | 27.500000 | ESTIMATED_DBU |
Sort by `estimated_dbu` to find your costliest maintenance tables, then filter to `operation_status = 'FAILED: INTERNAL_ERROR'` to catch tables whose PO ops are failing — investigate those (they accrue un-optimized files and storage bloat) and reconcile the top DBU consumers against billed spend before acting.
Feeds into: clustering/PO maintenance cost by table/op; estimated maintenance DBU as a cost line; maintenance success-rate / failing-table; automatic table-maintenance coverage & cost
One row per managed table showing how many successful VACUUM operations ran in the last 30 days and how many files, bytes, and estimated DBUs they reclaimed.
system.storage.predictive_optimization_operations_historyOne row = a table's successful VACUUM activity over the window: how many VACUUM operations ran (vacuum_op_count), how many files and bytes they deleted (total_deleted_files, total_deleted_bytes), and the estimated DBUs they cost (vacuum_estimated_dbu). The pairing that matters is total_deleted_bytes against vacuum_estimated_dbu - a table that keeps vacuuming successfully but reclaiming (near) zero bytes is paying maintenance cost for no storage benefit.
RequiresSELECT on system.storage; Public Preview (system.storage.predictive_optimization_operations_history), regional
VACUUM is what actually deletes stale time-travel and tombstoned data files from your Delta tables, and reclaimed bytes translate directly into cloud object-storage dollars you stop paying every month. This view quantifies how much bloat Predictive Optimization's automatic VACUUM genuinely removed per table, and — read alongside a table's remaining `vacuumable_bytes` — exposes tables where reclaimable waste is piling up because VACUUM rarely or never runs. Databricks has no fail-safe, so storage never cleaned here is storage you keep renting indefinitely.
SELECT catalog_name, schema_name, table_id, table_name,
COUNT(*) AS vacuum_op_count,
SUM(CAST(operation_metrics['number_of_deleted_files'] AS BIGINT)) AS total_deleted_files,
SUM(CAST(operation_metrics['amount_of_data_deleted_bytes'] AS BIGINT)) AS total_deleted_bytes,
SUM(CAST(usage_quantity AS DECIMAL(38,6))) AS vacuum_estimated_dbu,
-- status: worst-first band on DBU spent with zero bytes reclaimed (field heuristic; :warn_noop_dbu / :crit_noop_dbu).
CASE
WHEN SUM(CAST(usage_quantity AS DECIMAL(38,6))) IS NULL THEN 'NOT_ASSESSED'
WHEN SUM(CAST(operation_metrics['amount_of_data_deleted_bytes'] AS BIGINT)) = 0
AND SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :crit_noop_dbu THEN 'CRITICAL'
WHEN SUM(CAST(operation_metrics['amount_of_data_deleted_bytes'] AS BIGINT)) = 0
AND SUM(CAST(usage_quantity AS DECIMAL(38,6))) >= :warn_noop_dbu THEN 'WARN'
ELSE 'OK'
END AS status
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'VACUUM' AND operation_status = 'SUCCESSFUL'
AND start_time >= current_date() - INTERVAL :period_days DAYS AND start_time < current_date()
GROUP BY catalog_name, schema_name, table_id, table_name
ORDER BY CASE status WHEN 'CRITICAL' THEN 0 WHEN 'WARN' THEN 1 WHEN 'OK' THEN 2 ELSE 3 END, vacuum_estimated_dbu DESC:period_daysdefault 30rolling window in days:warn_noop_dbudefault 5estimated VACUUM DBUs spent on a table that reclaimed zero bytes over the window that flags WARN:crit_noop_dbudefault 20that flags CRITICALtable_id alongside its catalog, schema, and name.| Column | How to read it |
|---|---|
| catalog_name | Unity Catalog catalog the VACUUMed table lives in. |
| schema_name | Schema (database) within that catalog. |
| table_id | Stable UC identifier for the table — use this to join to inventory or storage-size probes, not the name. |
| table_name | Table name at the time of the operation; can be renamed or reused, so treat `table_id` as the true identity. |
| vacuum_op_count | Number of successful VACUUM operations on this table in the window; zero-count tables are absent entirely, and a low count on a large table hints VACUUM is lagging. |
| total_deleted_files | Total data files physically removed by those VACUUM runs — a file-level measure of cleanup volume. |
| total_deleted_bytes | Total bytes reclaimed (freed from object storage) across all successful VACUUMs — the headline storage-savings figure; bytes, not dollars. |
| vacuum_estimated_dbu | Summed **ESTIMATED_DBU** these VACUUM ops consumed — DBUs (not dollars, not billed spend), an estimate that can lag its operation row by up to ~24h. |
total_deleted_bytes > 0 for vacuum activity, or vacuum_estimated_dbu under :warn_noop_dbu when reclaimed bytes are zero (field heuristic - tune :warn_noop_dbu for your account; a well-tuned table that rarely deletes rows can legitimately show low/zero reclaim).
total_deleted_bytes = 0 (no reclaim) with vacuum_estimated_dbu at/above :warn_noop_dbu (WARN) or :crit_noop_dbu (CRITICAL) - field heuristic; note this query only covers tables that WERE successfully vacuumed - a table missing entirely from this result may simply never run VACUUM, which is a coverage gap you can only see by pairing this against storage_breakdown_analyze's vacuumable_bytes for the same table.
| catalog_name | schema_name | table_id | table_name | vacuum_op_count | total_deleted_files | total_deleted_bytes | vacuum_estimated_dbu |
|---|---|---|---|---|---|---|---|
| prod_analytics | events | a1b2c3d4-1111-4a2b-9c3d-0e1f2a3b4c5d | clickstream_raw | 12 | 48210 | 1897654320000 | 6.482100 |
| prod_analytics | finance | b2c3d4e5-2222-4b3c-8d4e-1f2a3b4c5d6e | ledger_snapshots | 3 | 920 | 41287340000 | 0.734000 |
| sandbox | scratch | c3d4e5f6-3333-4c4d-7e5f-2a3b4c5d6e7f | model_features_v2 | 1 | 57 | 1204880000 | 0.041500 |
For each table, compare `total_deleted_bytes` (what VACUUM actually reclaimed over 30 days) against the table's current `vacuumable_bytes` from the `storage_breakdown_analyze` probe: a high remaining vacuumable figure with few or zero recent VACUUM ops flags unreclaimed storage bloat where VACUUM is lagging or not running — schedule/verify PO VACUUM or a manual VACUUM there.
Feeds into: time-travel / storage bloat reclaimed (VACUUM bytes); PO coverage gap (pairs reclaimed bytes with vacuumable_bytes)
A single row of one Delta table's true on-disk footprint, split into active data, time-travel history, and reclaimable (vacuumable) bytes and file counts.
ANALYZE TABLE … COMPUTE STORAGE METRICS computes and persists table statistics for the target table — a write side effect, unlike every other query in this library (all plain SELECTs). It also needs DBR 18.0+ and runs per target table on a warehouse. Point it only at tables you are allowed to analyze.One result row per run = one table's true on-disk footprint at the moment you ran it, split into active_bytes (live data), time_travel_bytes (retained history), and vacuumable_bytes (already-deleted files still on disk, reclaimable by VACUUM). The columns that matter are vacuumable_bytes and time_travel_bytes relative to total_bytes - a high share of either means you are paying to store data nobody is reading.
Requiresprivilege sufficient to run ANALYZE TABLE on the target table; GA but requires DBR 18.0+ (COMPUTE STORAGE METRICS is not available on older runtimes)
This is the only way to learn what a Delta table actually costs to store — Unity Catalog system tables carry no physical size, so without it you are guessing. It separates live data you need from time-travel history and dead files still on disk, letting you see how much of your storage bill is bloat rather than useful data. Pair `vacuumable_bytes` with your storage rate to put a dollar figure on unreclaimed waste, and use `time_travel_bytes` to decide whether retention settings are quietly inflating spend.
-- Result columns to read: total_bytes, num_total_files, active_bytes, num_active_files,
-- vacuumable_bytes, num_vacuumable_files, time_travel_bytes, num_time_travel_files.
-- Run per target table; substitute your catalog.schema.table for main.sales.orders.
ANALYZE TABLE main.sales.orders COMPUTE STORAGE METRICS;VACUUM could delete) bytes, plus a total.| Column | How to read it |
|---|---|
| total_bytes | Total on-disk size of the table in bytes, across all versions and files (active + time-travel + vacuumable). This is the number that maps to your storage bill. |
| num_total_files | Total count of data files backing the table. Very high counts relative to size signal small-file bloat that OPTIMIZE/compaction would help. |
| active_bytes | Bytes of live, current-version data — what a normal read of the table actually needs. Everything above this is history or dead files. |
| num_active_files | Number of files holding the active (current) data. |
| vacuumable_bytes | Bytes of dead/tombstoned files that a VACUUM could physically delete right now — this is directly reclaimable waste. Multiply by your storage rate to dollarize it. |
| num_vacuumable_files | Count of files eligible for reclamation by VACUUM. |
| time_travel_bytes | Bytes retained purely to support time travel / rollback within the retention window. Shrinks only if you lower deletedFileRetentionDuration/logRetentionDuration. |
| num_time_travel_files | Number of files held for time-travel history. |
Inventory / reference query — it returns state to read, not a pass/fail band.
| total_bytes | num_total_files | active_bytes | num_active_files | vacuumable_bytes | num_vacuumable_files | time_travel_bytes | num_time_travel_files |
|---|---|---|---|---|---|---|---|
| 12000000000 | 1200 | 8000000000 | 800 | 1000000000 | 100 | 3000000000 | 300 |
| 540000000 | 45 | 500000000 | 40 | 0 | 0 | 40000000 | 5 |
| 96000000000 | 9600 | 20000000000 | 1200 | 56000000000 | 6400 | 20000000000 | 2000 |
If `vacuumable_bytes` is large, run VACUUM to reclaim that storage immediately; if `time_travel_bytes` dominates, review and tighten `deletedFileRetentionDuration`/`logRetentionDuration`; if `num_total_files` is high relative to size, schedule OPTIMIZE/compaction.
Feeds into: storage breakdown (active / time-travel / vacuumable / total); time-travel bloat (size side); PO coverage gap (vacuumable_bytes to dollarize unreclaimed storage)
A catalog/schema-level census of every table and view the collector can see, counted by managed/external/view type and by data source format.
system.information_schema.tablesOne row = a catalog + schema + table_type + data_source_format combination, with the count of tables/views matching it. table_type tells you managed vs external vs view; data_source_format (when it resolves) flags DELTA vs ICEBERG. EXTERNAL tables are not eligible for Predictive Optimization, so a high EXTERNAL count here caps how much of po_maintenance_cost_by_table's coverage can ever reach 100%.
RequiresSELECT on system.information_schema; Unity Catalog required
This is the baseline map of your Unity Catalog estate: it tells you how many tables live where and, critically, how many are **EXTERNAL** rather than **MANAGED**. Only managed tables are Predictive Optimization-eligible, so the EXTERNAL count directly bounds the PO coverage gap — the slice of your data that automatic OPTIMIZE/VACUUM/clustering will never touch and whose maintenance you must plan and pay for yourself. The `data_source_format` split also flags **ICEBERG**/UniForm candidates that need different handling. Because the view is privilege-aware, the counts also reveal how much of the metastore the collector principal can actually see.
SELECT table_catalog, table_schema, table_type, data_source_format,
COUNT(*) AS table_count
FROM system.information_schema.tables
GROUP BY table_catalog, table_schema, table_type, data_source_format
ORDER BY table_catalog, table_schema, table_type| Column | How to read it |
|---|---|
| table_catalog | The Unity Catalog catalog the group of tables lives in. Column name is plausible but UNVERIFIED in this repo's doc audit — a real run confirms it resolves. |
| table_schema | The schema (database) within the catalog. Also an UNVERIFIED information_schema column name pending a customer run. |
| table_type | MANAGED, EXTERNAL, or VIEW (doc-confirmed). EXTERNAL tables are not PO-eligible; VIEW rows are logical objects with no storage of their own. |
| data_source_format | The storage format, e.g. DELTA or ICEBERG, used to flag Iceberg/UniForm candidates. Presence and exact name are UNVERIFIED. May be NULL/blank for views or formats that don't report it. |
| table_count | Number of tables/views in that catalog + schema + type + format group. An integer object count, not bytes and not cost. |
Inventory / reference query — it returns state to read, not a pass/fail band.
| table_catalog | table_schema | table_type | data_source_format | table_count |
|---|---|---|---|---|
| prod_analytics | sales | MANAGED | DELTA | 142 |
| prod_analytics | sales | EXTERNAL | ICEBERG | 17 |
| prod_analytics | reporting | VIEW | NULL | 23 |
Read off the EXTERNAL vs MANAGED split to quantify how much of your estate is outside Predictive Optimization's reach, then plan manual maintenance (or migration to managed) for those tables; use the ICEBERG rows to shortlist UniForm/Iceberg candidates for the iceberg_uniform_metadata probe. If key columns come back unresolved on the real run, treat the column names as needing correction before relying on the breakdown.
Feeds into: Iceberg / external tables inventory (MANAGED vs EXTERNAL vs VIEW); PO coverage gap (EXTERNAL = not PO-eligible)
For one managed Delta table, the seven Delta properties that govern time-travel retention, auto-optimize, data-skipping indexing, and deletion vectors, each with its effective value or default.
One row block per target table = its current time-travel retention (delta.logRetentionDuration, delta.deletedFileRetentionDuration), data-skipping indexing config, auto-optimize flags, and deletion-vector flag. The properties that matter most for storage bloat are delta.logRetentionDuration and delta.deletedFileRetentionDuration - values are CalendarInterval strings (e.g. 'interval 30 days') that you need to parse to days yourself.
RequiresDESCRIBE / SELECT privilege on the target table; GA (standard Delta Lake table properties)
Over-long `delta.logRetentionDuration` and `delta.deletedFileRetentionDuration` are the most common silent cause of Delta storage bloat: every extra day of retention keeps deleted/superseded files on disk (and out of reach of VACUUM), inflating your storage bill for time-travel you may never use. This probe is the only place to see those retention windows plus whether auto-optimize (write-side compaction), data-skipping indexing, and deletion vectors are actually configured on a table. Reading it tells an admin whether a table is set up to stay compact and prunable, or quietly accumulating cost.
-- Delta table properties to read from the output (name -> documented default):
-- delta.logRetentionDuration (default 'interval 30 days')
-- delta.deletedFileRetentionDuration (default 'interval 1 week')
-- delta.dataSkippingNumIndexedCols (default 32)
-- delta.dataSkippingStatsColumns (default none)
-- delta.autoOptimize.optimizeWrite
-- delta.autoOptimize.autoCompact
-- delta.enableDeletionVectors
-- Run per target table; substitute your catalog.schema.table for main.sales.orders.
DESCRIBE EXTENDED main.sales.orders;optimizeWrite, autoCompact) — whether Databricks compacts small files on write for this table.| Column | How to read it |
|---|---|
| delta.logRetentionDuration | How long the Delta transaction-log history is kept, as a CalendarInterval string like `interval 30 days` (default). Parse to days; longer = more time-travel but more log bloat. |
| delta.deletedFileRetentionDuration | How long deleted/superseded data files are retained before VACUUM may remove them, e.g. `interval 1 week` (default). Long values keep dead files on disk and are the main storage-bloat lever. |
| delta.dataSkippingNumIndexedCols | Number of leading columns for which min/max stats are collected for data-skipping (default 32). Fewer indexed columns can weaken query pruning. |
| delta.dataSkippingStatsColumns | Explicit list of columns to collect data-skipping stats on (default none/unset). When set, it overrides the leading-N-columns behavior. |
| delta.autoOptimize.optimizeWrite | Whether write-side optimized writes are on (`true`/`false`), producing fewer, larger files at write time. Unset means inherit runtime default. |
| delta.autoOptimize.autoCompact | Whether automatic post-write compaction of small files is enabled (`true`/`false`). Unset means inherit runtime default. |
| delta.enableDeletionVectors | Whether deletion vectors (soft-delete markers instead of file rewrites) are enabled. No fixed default — depends on workspace/DBR runtime, so treat blank as runtime-determined, not off. |
Inventory / reference query — it returns state to read, not a pass/fail band.
| delta.logRetentionDuration | delta.deletedFileRetentionDuration | delta.dataSkippingNumIndexedCols | delta.autoOptimize.optimizeWrite | delta.enableDeletionVectors |
|---|---|---|---|---|
| interval 30 days | interval 7 days | 32 | true | true |
| interval 365 days | interval 90 days | 32 | false | false |
| interval 30 days | interval 7 days | 8 | true | (unset) |
For any table where `deletedFileRetentionDuration` or `logRetentionDuration` is far longer than your recovery needs, lower it with `ALTER TABLE … SET TBLPROPERTIES` and run VACUUM to reclaim the newly-eligible dead files; while there, confirm auto-optimize and data-skipping are configured to keep the table compact and prunable.
Feeds into: time-travel config (long retention = bloat risk); search optimization / data-skipping coverage