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.
Defaults, limits and preview status change often. Check the current Databricks docs before quoting a number.
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
| Table | Changes | Strategy |
|---|---|---|
| Bronze, batch files (Auto Loader, daily) | Daily, large appends | Optimized writes on; Predictive Optimization (or weekly OPTIMIZE); VACUUM weekly |
| Bronze, streaming (seconds) | Constant tiny commits | Do not rely on auto compaction. Bigger triggers; separate scheduled OPTIMIZE of recent data (see below) |
| Silver with MERGE or CDC | Frequent updates | Deletion vectors on; liquid clustering on the merge key; Predictive Optimization; PURGE only for erasure |
| Gold facts and dimensions | Rebuilt or merged on a schedule | OPTIMIZE right after the build in the same job; liquid clustering on the filter columns; ANALYZE |
| Streaming tables and materialized views in pipelines | Pipeline-managed | Pipelines run maintenance themselves (documented as at least daily; check) — do not add your own jobs unless you must |
| Small dimension (thousands of rows) | Rare | Nothing special; one file is fine |
| External tables | Any | Scheduled jobs you own (see below) |
| Hot table, many readers, strict latency | Constant | Compact recent partitions often; keep clustered; watch commit conflicts |
| Cold archive | Never | VACUUM 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.
| Fix | How it helps |
|---|---|
| Fewer, bigger micro-batches | Use a longer trigger(processingTime=…), or availableNow on a schedule; set maxFilesPerTrigger or maxBytesPerTrigger so each batch carries more data |
| Fewer writer tasks | Each task writes a file per batch. Match shuffle partitions to the data volume, not the cluster default |
| Scheduled compaction on recent data | A 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 Optimization | On managed tables it does this for you |
| Liquid clustering, not partitioning | No files-per-partition explosion |
| Accept small files in bronze | Compact downstream; read bronze as a stream so silver is built from larger batches |
| Fix the source | If 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).
| Task | Approach |
|---|---|
| OPTIMIZE | A scheduled job driven by a list of tables. Check DESCRIBE DETAIL first and run only where files are many or average size is small |
| VACUUM | Weekly; retention at least 7 days and longer than your slowest reader, stream or external engine |
| ANALYZE | After big loads, for tables used in large joins |
| Write-time settings | Table properties for optimized writes and auto compaction still apply |
| Ownership | One team owns the job. If other engines also write, agree who compacts |
| Tagging | Tag tables with their maintenance tier (hot, warm, cold) so the job can read the schedule from metadata |
| DROP | Dropping 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?
| Question | Answer |
|---|---|
| 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' view | Managed is the recommended default (check current guidance). |
Practice
Questions to rehearse
- Auto, scheduled or both: how do you decide?
- Why is auto compaction a poor fit for a one-second stream?
- How do you compact a table that is being streamed into?
- What is your maintenance plan for external tables?
- Does an external table save money?
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