Home › Learn › Databricks interview prep › Maintenance strategy

Databricks interview prep · data engineering

Maintenance strategy: automatic, scheduled or both

How to choose between automatic and scheduled table maintenance by layer, table type and change rate, fixing tiny streaming files, and external table care.

Maintenance commandsRow-level securityCompute choiceJoinsSkew and shuffleAQE, Photon, POCost and performance leversDeclarative and semanticAsset BundlesLakeflow ConnectUniFormVector Searchdbt and ODCSEgress and ingressMaintenance in picturesClustering and compactionMaintenance strategySlow writesBackup and recoveryTaggingQuestion bank

Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.

How often does the tablechange?Which layer is it?Managed or external?Pick: automatic,scheduled, or both

Four questions pick the strategy.

The short answer

Automatic where you can, scheduled where you must, and never nothing

Use Predictive Optimization for Unity Catalog managed tables: it runs OPTIMIZE, VACUUM and ANALYZE only when they pay off, so it is the default. Add scheduled jobs for what it cannot cover: external tables, tables that need maintenance at a specific time, hot partitions that need compacting hourly, and anything you need to guarantee before a deadline. Add write-time settings (optimized writes, auto compaction) only where the trade-off below is acceptable. Check availability and cost of Predictive Optimization on your account.

Decide

By layer, table type and how often it changes

TableChangesStrategy
Bronze, batch files (Auto Loader, daily)Daily, large appendsOptimized writes on; Predictive Optimization (or weekly OPTIMIZE); VACUUM weekly
Bronze, streaming (seconds)Constant tiny commitsDo not rely on auto compaction. Bigger triggers; separate scheduled OPTIMIZE of recent data (see below)
Silver with MERGE or CDCFrequent updatesDeletion vectors on; liquid clustering on the merge key; Predictive Optimization; PURGE only for erasure
Gold facts and dimensionsRebuilt or merged on a scheduleOPTIMIZE right after the build in the same job; liquid clustering on the filter columns; ANALYZE
Streaming tables and materialized views in pipelinesPipeline-managedPipelines run maintenance themselves (documented as at least daily; check) — do not add your own jobs unless you must
Small dimension (thousands of rows)RareNothing special; one file is fine
External tablesAnyScheduled jobs you own (see below)
Hot table, many readers, strict latencyConstantCompact recent partitions often; keep clustered; watch commit conflicts
Cold archiveNeverVACUUM once; consider cheaper storage tier; no OPTIMIZE

Topology matters too: if several teams or engines write to one table, agree one owner for maintenance, and make VACUUM retention longer than the slowest reader.

Streaming tiny files

Auto optimize is not good for streams: what to do instead

Why it disappoints: optimized writes add a shuffle to every micro-batch, and auto compaction runs inside the stream after commits, which adds latency and can collide with other writers. A stream that commits every second with 8 tasks makes about 691,000 files a day (86,400 × 8) before any compaction.

FixHow it helps
Fewer, bigger micro-batchesUse a longer trigger(processingTime=…), or availableNow on a schedule; set maxFilesPerTrigger or maxBytesPerTrigger so each batch carries more data
Fewer writer tasksEach task writes a file per batch. Match shuffle partitions to the data volume, not the cluster default
Scheduled compaction on recent dataA separate job runs OPTIMIZE on today's data every hour: OPTIMIZE t WHERE event_date = current_date(). OPTIMIZE does not rewrite data, so it rarely conflicts with the appending stream
Predictive OptimizationOn managed tables it does this for you
Liquid clustering, not partitioningNo files-per-partition explosion
Accept small files in bronzeCompact downstream; read bronze as a stream so silver is built from larger batches
Fix the sourceIf upstream lands tiny files or one-record Kafka batches, batch them there. Auto Loader with file notification mode avoids listing millions of small files

Measured on a local table: 60 small appends made 60 files; with auto compaction (threshold lowered to 10 files) they became 6. That is useful, but it only works because the writer waits for the compaction.

External tables

How to maintain them

An external table keeps its files in a path you control. Unity Catalog governs access but does not own the data lifecycle. Predictive Optimization covers managed tables; for external tables plan to run maintenance yourself (check the current documentation, because coverage changes).

TaskApproach
OPTIMIZEA scheduled job driven by a list of tables. Check DESCRIBE DETAIL first and run only where files are many or average size is small
VACUUMWeekly; retention at least 7 days and longer than your slowest reader, stream or external engine
ANALYZEAfter big loads, for tables used in large joins
Write-time settingsTable properties for optimized writes and auto compaction still apply
OwnershipOne team owns the job. If other engines also write, agree who compacts
TaggingTag tables with their maintenance tier (hot, warm, cold) so the job can read the schedule from metadata
DROPDropping the table does not delete the files; clean up separately

Choose

When to use external tables, and does it save money or keep data open?

QuestionAnswer
Use external when…Another engine or team writes to the same path; you are migrating an existing lake; the location must stay under separate ownership or retention rules; DROP must never delete data.
Does it save cost?Not by itself. The bytes cost the same. You trade Predictive Optimization (and its automatic tuning) for jobs you run and maintain. Managed tables are usually cheaper to operate; external tables are cheaper only when the control or sharing they give you has value.
Is it more open?Both are open formats (Parquet files with a Delta or Iceberg log) in your own cloud account. External tables give you a plain path other tools can use directly. For managed tables, outside engines should go through open interfaces (such as UniForm or the Iceberg REST catalog) instead of reading storage directly.
Databricks' viewManaged is the recommended default (check current guidance).

Practice

Questions to rehearse

  1. Auto, scheduled or both: how do you decide?
  2. Why is auto compaction a poor fit for a one-second stream?
  3. How do you compact a table that is being streamed into?
  4. What is your maintenance plan for external tables?
  5. Does an external table save money?
In an interview, say: Automatic by default on managed tables; scheduled jobs for external tables, hot partitions and deadlines; and for streams, bigger batches plus a separate compaction job.

FAQ

Questions people ask

Can OPTIMIZE run while a stream writes?

Yes. OPTIMIZE does not change data, so it rarely conflicts with appends.

Do external tables get Predictive Optimization?

Check the current docs; treat it as your job to maintain them.

Next

Where to go next

All Databricks interview topics · All data engineering · Databricks on this site