Databricks Table Maintenance

Delta tables grow and change every day. Streaming jobs add small files, updates leave old files behind, and query patterns shift over time. Regular maintenance keeps tables fast and storage costs reasonable. Databricks offers manual commands and automatic features for the job.

The Garden Care Analogy

A garden looks tidy after planting, but weeds grow, branches spread, and paths fill with leaves. Regular care keeps the garden healthy. A Delta table needs the same habit. Small files pile up like leaves, old versions take space like cut branches, and poor organization slows every visit.

MAINTENANCE TASKS AT A GLANCE
──────────────────────────────────────────────────────────
Too many small files  ──► OPTIMIZE
Old unused files      ──► VACUUM
Slow filtered queries ──► Liquid clustering
Poor query planning   ──► ANALYZE TABLE (statistics)
Hard to track         ──► DESCRIBE DETAIL / DESCRIBE HISTORY
Manual effort         ──► Predictive optimization

Inspect a Table First

Two commands reveal the health of a table before you change anything.

-- Size, file count, and location
DESCRIBE DETAIL main.shop.orders;

-- Every change with time, user, and operation
DESCRIBE HISTORY main.shop.orders;

DESCRIBE DETAIL shows the number of files and the total size. A table with thousands of tiny files needs compaction.

OPTIMIZE: Compact Small Files

OPTIMIZE merges many small files into fewer, larger ones. Spark opens fewer files, so queries finish faster.

OPTIMIZE main.shop.orders;

BEFORE:  2,000 files × 2 MB
AFTER:      20 files × 200 MB

Run OPTIMIZE after heavy write periods, such as streaming loads or large merges.

VACUUM: Remove Old Files

Updates and deletes leave old data files on storage. VACUUM deletes files that no active table version needs after the retention period. The default retention period is seven days.

VACUUM main.shop.orders;

-- Dry run: list files without deleting
VACUUM main.shop.orders DRY RUN;

Time Travel stops working for versions older than the retention window after a vacuum. Match the retention period to your recovery needs, and use DRY RUN when you feel unsure.

Liquid Clustering: Smart Data Layout

Older designs used partitions and ZORDER to organize data. Liquid clustering is a newer approach that arranges rows by chosen columns and adapts when your needs change. You pick clustering columns that appear often in filters.

-- Create a table with clustering
CREATE TABLE main.shop.orders_clustered (
  order_id   BIGINT,
  city       STRING,
  order_date DATE,
  amount     DECIMAL(10,2)
)
CLUSTER BY (city, order_date);

-- Change clustering columns later
ALTER TABLE main.shop.orders_clustered CLUSTER BY (order_date);

-- Apply the layout
OPTIMIZE main.shop.orders_clustered;

CLUSTERED LAYOUT
──────────────────────────────────────────────────────────
Query: WHERE city = 'Pune' AND order_date = '2026-03-01'

File 1: Delhi rows      ──► skipped
File 2: Mumbai rows     ──► skipped
File 3: Pune, March     ──► read
File 4: Pune, April     ──► skipped

Avoid over-partitioning small tables. Too many tiny partitions slow queries instead of helping them.

Statistics for Better Plans

Query engines use statistics about row counts and value ranges to pick efficient plans. Delta tables collect statistics on the first columns automatically. Run ANALYZE TABLE when the data changes sharply.

ANALYZE TABLE main.shop.orders COMPUTE STATISTICS FOR ALL COLUMNS;

Predictive Optimization: Let Databricks Handle It

Predictive optimization runs OPTIMIZE, VACUUM, and statistics tasks automatically on Unity Catalog managed tables. The platform decides when each task adds value and runs it in the background. Administrators enable the feature for an account, catalog, or schema.

ALTER SCHEMA main.shop ENABLE PREDICTIVE OPTIMIZATION;

Check the documentation for the current availability and requirements in your region.

A Simple Maintenance Plan

Table Type	Suggested Approach
Managed table with active use	Predictive optimization on
Streaming target table	Auto compaction and scheduled OPTIMIZE
Large table with common filters	Liquid clustering on filter columns
Rarely used archive table	Occasional VACUUM and storage lifecycle rules

Example: Weekly Maintenance Job

A retail team keeps a sales table that receives streaming rows all day. A weekly job reads the table details, compacts files, and removes old files after the retention period. The team stores the job as a workflow task so it runs without anyone's attention.

WEEKLY MAINTENANCE JOB
──────────────────────────────────────────────────────────
Sunday 2 AM
   │
   ├── DESCRIBE DETAIL   (record file count)
   ├── OPTIMIZE          (compact files)
   ├── VACUUM            (remove old files)
   └── DESCRIBE DETAIL   (record the improvement)

Key Points

• Delta tables need regular care to stay fast and affordable.
• DESCRIBE DETAIL and DESCRIBE HISTORY reveal table size and past changes.
• OPTIMIZE compacts small files, and VACUUM removes old unused files.
• Liquid clustering organizes data by filter columns and adapts to new needs.
• Predictive optimization automates maintenance on managed tables.
• A short retention period saves space and limits Time Travel.