Predictive Optimization, VACUUM, and what your tables really weigh.

Storage cost hides in maintenance DBUs and table sprawl. The console shows neither well.

The console shows a bill and a chart. What a table actually weighs is a number nobody stores until you ask for it.

The Databricks console will show you a storage line on the bill and a Predictive Optimization toggle in the account console. It will not show you which of your tables Predictive Optimization actually touched last month, how many bytes VACUUM freed on any one of them, or which tables have sat untouched since a migration nobody remembers running. Those questions live in one maintenance-history table and one catalog-inventory table — both queryable with a plain SELECT, neither surfaced anywhere in the UI as a ranked list.

This is one of four posts drawn from the Storage & Optimization domain of the Databricks query library, the receipts unbundled, open on GitHub. This post walks what Predictive Optimization is costing and covering, what VACUUM reclaimed per table, what your tables weigh and which look dead, and whether liquid clustering is earning the DBU it spends.

"What is Predictive Optimization actually costing — and covering?"

Predictive Optimization runs VACUUM, COMPACTION, CLUSTERING, and ANALYZE against your managed tables in the background, and the account console shows a single toggle plus a rolled-up DBU line — not which operation type ran, on how many tables, or how often it failed. system.storage.predictive_optimization_operations_history is one row per maintenance operation, and its usage_unit column is fixed to ESTIMATED_DBU for every row, so a straight SUM never mixes units — it does mean the figure PO reports about itself is always an estimate, never a billed one.

SELECT
  operation_type,
  COUNT(*)                                                                   AS operation_count,
  COUNT(DISTINCT table_id)                                                   AS tables_covered,
  ROUND(100.0 * SUM(CASE WHEN operation_status = 'SUCCESSFUL'
                          THEN 1 ELSE 0 END) / COUNT(*), 1)                   AS success_pct,
  SUM(CAST(usage_quantity AS DECIMAL(38,6)))                                  AS estimated_dbu
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= dateadd(day, -30, current_date())
  AND start_time <  current_date()
GROUP BY operation_type
ORDER BY estimated_dbu DESC;

Filter carefully on operation_status: alongside SUCCESSFUL the enum's failure value is the literal string 'FAILED: INTERNAL_ERROR', embedded colon included. A naive != 'FAILED' comparison never matches it, and every failure silently counts as a non-failure. Read it like this: one row per operation type, tables touched, success rate, and summed estimated DBU descending — the type sitting at the top is the one actually earning the maintenance spend; a low success rate next to a high DBU figure is compute paid for work that didn't land.

"How much did VACUUM reclaim — per table?"

VACUUM deletes files no longer referenced by a live table version once they age past the retention window — the same files that, left alone, accumulate as the time-travel bloat behind an inflated storage bill. Every metric PO records lives in an operation_metrics column typed map<string,string>, so every figure inside it needs an explicit CAST. VACUUM rows carry exactly two documented subfields: number_of_deleted_files and amount_of_data_deleted_bytes — nothing more granular is exposed per run.

SELECT
  catalog_name, schema_name, table_name,
  COUNT(*)                                                                    AS vacuum_runs,
  SUM(CAST(operation_metrics['number_of_deleted_files']      AS BIGINT))      AS deleted_files,
  SUM(CAST(operation_metrics['amount_of_data_deleted_bytes'] AS BIGINT))      AS reclaimed_bytes,
  SUM(CAST(usage_quantity AS DECIMAL(38,6)))                                  AS vacuum_estimated_dbu
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type   = 'VACUUM'
  AND operation_status = 'SUCCESSFUL'
  AND start_time >= dateadd(day, -30, current_date())
  AND start_time <  current_date()
GROUP BY catalog_name, schema_name, table_name
ORDER BY reclaimed_bytes DESC;

Databricks has no fail-safe layer sitting behind this history — once VACUUM runs past the deletion-vector-aware retention window, the deleted bytes are gone, and this table is the only record they ever existed. Read it like this: one row per table, reclaimed bytes ranked descending, DBU spent alongside — divide the two and the ratio is the honest measure of whether VACUUM is earning the compute it consumes on that table, not just whether it ran on schedule.

"What do your tables really weigh, and which are dead?" Unity Catalog carries no byte column. You have to ask a table directly, or read the silence.

Here is the finding underneath the question: no system table stores a table's size. system.information_schema.tables gives you the inventory — catalog, schema, name, type, source format, created, last_altered — across every table the querying principal can see, in one pass. It does not carry a single bytes column. An actual weight means running ANALYZE TABLE ... COMPUTE STORAGE METRICS per table, on demand, on a recent-enough runtime — a result that is never persisted anywhere in Unity Catalog and has to be captured by the query that ran the ANALYZE.

SELECT
  table_catalog,
  table_schema,
  table_name,
  table_type,
  data_source_format,
  created,
  last_altered,
  datediff(current_date(), DATE(last_altered)) AS days_since_altered
FROM system.information_schema.tables
WHERE table_catalog NOT IN ('system')
  AND table_schema   <> 'information_schema'
  AND table_type      IN ('MANAGED', 'EXTERNAL')
ORDER BY days_since_altered DESC NULLS LAST;

information_schema is privilege-aware — a table the querying principal cannot see is simply absent from the result, not confirmed gone — and last_altered can be NULL or late-populated on some object types, which reads as age unknown, never as a finding on its own. Read it like this: one row per table, oldest-write-first; the names sitting at the top of that list are the sprawl candidates. Before dropping anything, pair the row with ANALYZE TABLE ... COMPUTE STORAGE METRICS to see its active, vacuumable, and time-travel bytes — "untouched for months" and "cheap to keep" are not the same finding, and only the second query tells them apart.

"Is liquid clustering — or data-skipping — paying off?" This domain proves the maintenance ran. It cannot prove it helped.

PO's CLUSTERING operations compact a table's files around its clustering keys; a separate DATA_SKIPPING_COLUMN_SELECTION operation type backfills the per-file min/max statistics the query engine prunes against. Both are recorded here, cast from the same operation_metrics map, and both cost estimated DBU whether or not a single downstream query ends up benefiting.

SELECT
  catalog_name, schema_name, table_name,
  COUNT(*)                                                                       AS clustering_runs,
  SUM(CAST(operation_metrics['number_of_clustered_files']      AS BIGINT))       AS clustered_files,
  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
FROM system.storage.predictive_optimization_operations_history
WHERE operation_type = 'CLUSTERING'
  AND start_time >= dateadd(day, -30, current_date())
  AND start_time <  current_date()
GROUP BY catalog_name, schema_name, table_name
ORDER BY clustering_estimated_dbu DESC;

Read it like this: one row per table, bytes clustered and DBU spent, ranked by spend — the cost half of the question, and the only half this domain can answer alone. Whether the clustering actually paid off lives one domain over: system.query.history (currently Public Preview) carries pruned_files against read_bytes per finished statement, and it is that ratio — not the clustering log — that tells you whether the keys you're paying to maintain are the keys your queries actually filter on. A table sitting at the top of this list with a flat pruning ratio on the query-history side is estimated DBU spent on a clustering key nobody's WHERE clause uses.

The receipt. The console shows a bill. The query shows what the tables are actually made of.

Four questions, four plain SELECTs, over two system tables and one on-demand ANALYZE that Unity Catalog will not run for you unprompted. None of it needed a dashboard, a login, or a consultant reading a chart over your shoulder — that is the whole case for owning the queries instead of the console's summary of them.

These four are one quarter of the domain. The full set — the query library — carries nine Storage & Optimization queries among ninety-six total across cost, performance, compute, jobs, serving, storage, and governance, each traced to the row that produced it, all open on GitHub. Read the README's Availability section first; it tells you, per table, which of these questions your account can actually answer.

Every query reads Databricks Unity Catalog system.* tables via plain SELECT; dollar estimates are est · at list, DBU-only, directional. Empty is treated as not assessed. — Crosshire