Databricks interview questions: Predictive Optimization: stop cron-ing OPTIMIZE

Say this first: Databricks runs the maintenance itself, using Unity Catalog's usage and change statistics to decide which tables actually need it rather than touching all six hundred.

Why: The cron approach spends the same effort on a table that changed by four rows and a table that was half rewritten, because a loop cannot tell the difference. Predictive optimization uses the statistics Unity Catalog already keeps about how tables are written and queried, and schedules OPTIMIZE, VACUUM, and related maintenance where the predicted benefit justifies the cost. You enable it once at the account, catalog, or schema level and child objects inherit it. The operational win is that the nightly window stops being a fixed multi-hour block that grows with table count. The accounting change is that this work runs on serverless compute and shows up as its own usage, instead of hiding inside your ETL cluster's bill.

ALTER CATALOG prod ENABLE PREDICTIVE OPTIMIZATION;
ALTER SCHEMA prod.bronze INHERIT PREDICTIVE OPTIMIZATION

Common mistake: Assuming it is just a managed cron that runs the same commands on every table on a schedule.

Follow-up they will ask: Where would you look to see what it actually ran last week?

Say this first: Predictive optimization only covers Unity Catalog managed tables — external tables and anything still in the Hive metastore are skipped.

Why: The feature works on tables whose storage lifecycle Databricks owns, which means Unity Catalog managed tables. External tables point at a location you control and may be written by things Databricks cannot see, so it will not rewrite or vacuum their files. Tables still registered in hive_metastore are outside Unity Catalog entirely and are not candidates at all. Run DESCRIBE DETAIL or check the information schema to confirm whether the schema's tables are MANAGED or EXTERNAL before blaming the feature. The fix is usually a migration to managed tables, not a configuration flag — and until that happens, those tables need to keep their old maintenance job.

SELECT table_name, table_type
FROM prod.information_schema.tables
WHERE table_schema = 'bronze' AND table_type <> 'MANAGED'

Common mistake: Deleting the old OPTIMIZE job catalog-wide and assuming every table is now covered.

Follow-up they will ask: What is your migration path for the external tables in that schema?

Say this first: The predictive optimization operations history system table, which lists every operation it ran, on which table, and what it cost.

Why: system.storage.predictive_optimization_operations_history records the maintenance operations the feature performed — the operation type, the target table, the metrics, and the usage attributed to it. Join or compare it against system.billing.usage to put a currency number next to the DBUs. On the benefit side, pick the tables it touched most and compare query profiles or bytes scanned before and after, because the claim being made is that queries stop degrading, not that they get faster than they ever were. Present both halves: the maintenance DBUs it spent and the maintenance job it replaced. If you only show the new line on the bill you will lose the argument, because the cost it removed was buried inside an ETL cluster nobody itemised.

SELECT catalog_name, table_name, operation_type, usage_unit, SUM(usage_quantity) AS dbus
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= current_date() - INTERVAL 7 DAYS
GROUP BY ALL ORDER BY dbus DESC

Common mistake: Comparing the new serverless line against zero, as if the old nightly OPTIMIZE job had been free.

Follow-up they will ask: Which tables would you expect to dominate that list, and what would it mean if a cold table topped it?

Say this first: Maintenance keeps the layout you chose healthy; it does not choose better clustering keys, so a table clustered on the wrong column stays slow no matter how well it is compacted.

Why: There are two different problems that both present as a slow table. One is layout decay — too many small files, data no longer sorted by the keys you picked — and that is what predictive optimization fixes by scheduling incremental OPTIMIZE where it pays. The other is a wrong key: the table is clustered or Z-ordered on a column nobody filters on, so even a perfectly compacted table has to read most of its files. Look at the query profile for the ratio of files pruned to files read; if pruning is poor while file sizes are healthy, the keys are the problem. The fix is ALTER TABLE ... CLUSTER BY on the columns query history shows people actually filtering on, or CLUSTER BY AUTO so Databricks adjusts the keys from observed patterns. Predictive optimization then schedules the incremental clustering work for the new keys.

ALTER TABLE prod.gold.orders CLUSTER BY (order_date, region);
-- or let Databricks choose and adjust the keys
ALTER TABLE prod.gold.orders CLUSTER BY AUTO

Senior depth: I separate the two conversations deliberately, because they have different owners. Layout maintenance is a platform concern and should be automated out of every team's backlog — that is what predictive optimization buys. Key selection is a data modelling decision tied to how the table is actually queried, and it needs query history as evidence, not intuition. For a headline dashboard table I would set explicit keys and hold them to a measured pruning ratio; for the long tail nobody can characterise, AUTO plus predictive optimization is the right default. The failure I want to avoid is a team concluding automation is broken when it did exactly what it promised.

Common mistake: Expecting automated maintenance to compensate for a partition or clustering column nobody queries on.

Follow-up they will ask: How would you decide between explicit keys and CLUSTER BY AUTO for that table?

Say this first: It uses Unity Catalog's change and access statistics to estimate the benefit of maintenance against its cost, so churned-and-queried tables win and cold tables are left alone.

Why: Unity Catalog already knows how much data was written to each table and how often it is read, because every commit and every query goes through it. Predictive optimization uses those signals to rank candidate operations: a table taking constant small writes and serving many queries has a high expected payoff from compaction, while a table written once and never read has none. That is the whole point — a cron loop would have spent the same DBUs on both. The evidence for the auditor is system.storage.predictive_optimization_operations_history, which shows the operations and metrics per table, cross-referenced with query history to show the read volume that justified them. The honest caveat is that the decision is heuristic and tuned by Databricks, so you influence it by what you write and query, not by a knob.

Senior depth: The thing to be clear about is that this is an optimizer, not a scheduler, and you cannot give it a deadline. So for anything with a hard timing requirement — a table that must be compacted before a market-open dashboard — I keep an explicit OPTIMIZE in the job that produces it and let predictive optimization handle everything else. That hybrid is not a failure of the feature; it is recognising that an expected-value scheduler and an SLA are different contracts. I would document which tables are in the explicit set and review it quarterly, because that list quietly grows.

Common mistake: Assuming there is a per-table schedule or threshold you can configure directly.

Follow-up they will ask: If a table must be compacted before a specific hour each day, what would you do instead?

Say this first: No — VACUUM only removes files past the table's retention window, so the deletion deadline is set by delta.deletedFileRetentionDuration, not by the fact that VACUUM is automated.

Why: Deleting a row in Delta writes a new version that no longer references the old file; the file itself stays until VACUUM removes it, and VACUUM will not remove files newer than the retention period. The default retention is measured in days and is a table property, so if it is longer than your legal deadline, automated VACUUM still will not delete in time. You set delta.deletedFileRetentionDuration on the tables under the requirement, and you accept that time travel and restore beyond that window stop working for them. Deletion vectors add a wrinkle worth saying out loud: a deleted row can remain inside a data file until the file is rewritten, so the record is logically gone but physically present until maintenance rewrites it. The compliance answer is a per-table retention setting plus evidence from the operations history that VACUUM ran, not 'the platform handles it'.

ALTER TABLE prod.silver.customers SET TBLPROPERTIES(
  'delta.deletedFileRetentionDuration' = 'interval 3 days'
)

Senior depth: Compliance questions need a mechanism and a receipt, and automation only gives you the mechanism. I would define a 'right to erasure' table class with a short deletedFileRetentionDuration, confirm those tables are UC managed so predictive optimization actually covers them, and build a report from the operations history showing the last VACUUM per table in that class. I would also flag the time-travel tradeoff in writing to whoever owns incident recovery, because the day someone needs to restore a bad load on one of those tables, the history they expected will not be there.

Common mistake: Treating automated VACUUM as a GDPR control without ever checking the retention property.

Follow-up they will ask: How would you produce evidence for the auditor that a specific record's files are gone?

Say this first: Run both for a few weeks, then retire maintenance only for UC managed tables that show up in the operations history, keeping explicit jobs for external tables and deadline-bound tables.

Why: Enable it at the catalog level and leave the existing job running, because deleting the safety net before you have evidence is how you discover coverage gaps during an incident. After a couple of weeks, pull the set of tables that appear in system.storage.predictive_optimization_operations_history and intersect it with the table list your job iterates over. Anything in both can come out of the job. What stays is the external and hive_metastore tables, which were never covered, and any table with a hard timing requirement that an expected-value scheduler will not honour. Then shrink the job to that residual list and watch the bill shift from your ETL cluster's DBUs to the serverless maintenance line. Record the before-and-after so the cost conversation later is about a shift, not a new expense.

Senior depth: The risk in this migration is not that the new thing fails, it is that the old thing was quietly doing something nobody documented — a VACUUM with a non-default retention, a REORG, a stats refresh. So before touching the job I read what it actually executes per table, not what the README says. Then I retire in waves with the operations history as evidence, and I leave a weekly check that diffs the UC managed-table list against the tables maintenance touched in the last thirty days. That diff is the drift detector, and it is cheaper than finding out from a slow dashboard.

Common mistake: Switching the feature on and deleting the maintenance job in the same change, then finding the gaps in production.

Follow-up they will ask: How would you detect a table that silently fell out of coverage later?

Say this first: Enable at account level so new catalogs inherit it, override downward only where you have a reason, and attribute the serverless maintenance spend per catalog from the operations history.

Why: Inheritance is the whole design lever: enabling at the account means every catalog a team creates next quarter is covered without a ticket, which is the opposite of the per-table cron world. You then override at catalog or schema level for the exceptions — a sandbox catalog of throwaway tables where maintenance is wasted spend, or a catalog of external tables where it does nothing anyway. For attribution, the operations history carries the catalog and table, so you can roll maintenance DBUs up to whatever ownership mapping you keep, rather than leaving it as one unattributable platform line. The governance work that actually pays is pushing teams toward managed tables, since that is what decides coverage. And set the expectation early that maintenance spend scales with write churn, so a team that triples its ingest will see its maintenance line triple too.

SELECT catalog_name, SUM(usage_quantity) AS maintenance_dbus
FROM system.storage.predictive_optimization_operations_history
WHERE start_time >= date_trunc('MONTH', current_date())
GROUP BY catalog_name ORDER BY maintenance_dbus DESC

Senior depth: I treat it as a default-on platform policy with documented exceptions, because opt-in governance decays the moment the person who filed the tickets changes teams. The architecture work is less about the toggle and more about making managed tables the default landing place, since external tables are the real coverage hole. Then I make the spend visible per catalog monthly, so a team that sees its maintenance line jump asks the right question — what changed in our write pattern — rather than asking the platform team to turn it off.

Common mistake: Enabling per schema by request, which recreates the coverage-gap problem the feature was meant to remove.

Follow-up they will ask: Which catalog would you deliberately leave disabled, and how would you justify it?