Optimizing Delta Tables

Delta performance comes down to two questions: how many files does a query open, and how many can it skip? Everything in this lesson is about improving one of those numbers.

The small file problem

A streaming job writing every minute produces a file per micro-batch:

describe detail bookshop.bronze.orders;

format  numFiles  sizeInBytes  minReaderVersion  location
------  --------  -----------  ----------------  -----------------------------
delta      44120  18402304512                 3  s3://bookshop-lake/bronze/orders

44,120 files for 17 GB — an average of 400 KB each. Every query opens all of them:

select count(*) from bookshop.bronze.orders where ordered_at = '2026-01-04';

count(1)
--------
   41209

-- 88.4 seconds

optimize bookshop.bronze.orders;

path                              metrics
--------------------------------  ------------------------------------------------------------
s3://bookshop-lake/bronze/orders  {numFilesAdded: 18, numFilesRemoved: 44120,
                                   filesAdded: {min: 892341122, max: 1073741824, avg: 1022350250},
                                   filesRemoved: {min: 204811, max: 512044, avg: 417100},
                                   partitionsOptimized: 0, numBatches: 6}

44,120 files became 18, averaging about 1 GB.

-- same query
count(1)
--------
   41209

-- 4.2 seconds

21× faster with no change to the query or the cluster. Per-file overhead — open, read footer, plan — dominates when files are small, and no amount of compute compensates.

Liquid clustering

OPTIMIZE packs files; it does not decide which rows sit together. CLUSTER BY does:

alter table bookshop.silver.orders cluster by (customer_id, ordered_at);
optimize bookshop.silver.orders;

path                              metrics
--------------------------------  ---------------------------------------------------
s3://bookshop-lake/silver/orders  {numFilesAdded: 22, numFilesRemoved: 184,
                                   clusteringMetrics: {numFilesClustered: 184,
                                   numBytesClustered: 21474836480}}

select count(*), sum(amount) from bookshop.silver.orders where customer_id = 481920;

count(1)  sum(amount)
--------  -----------
      42      1284.60

-- 0.9 seconds, 2 files read of 22

Rows for a customer now live together, so the file statistics exclude everything else.

Liquid clustering replaces both partitioning and Z-ordering, and its decisive advantage is that the keys are changeable:

alter table bookshop.silver.orders cluster by (status, ordered_at);

OK

No rewrite, no downtime — new data clusters on the new keys and OPTIMIZE gradually reorganises the rest. Changing a partition column, by contrast, means rebuilding the table.

create table bookshop.silver.events (
    event_id bigint, customer_id bigint, event_type string, occurred_at timestamp
) cluster by (customer_id, occurred_at);

Or let Databricks choose from observed query patterns:

alter table bookshop.silver.orders cluster by auto;

OK

Why not partition

-- the mistake
create table orders_partitioned (...) partitioned by (ordered_at);

describe detail orders_partitioned;

numFiles  sizeInBytes  partitionColumns
--------  -----------  ----------------
   12044  18402304512  [ordered_at]

One directory per day, each holding files far below the ideal size. The rules for partitioning, when you must:

• table is at least ~1 TB
• partition column is low cardinality — a month, a region, not a customer id or timestamp
• every partition holds at least 1 GB

Below that threshold, partitioning makes queries slower, not faster. For new tables, use liquid clustering and do not partition at all.

Measuring skipping

select * from bookshop.silver.orders where customer_id = 481920;

Open the query profile and read the scan node:

Scan bookshop.silver.orders
  files pruned: 20
  files read: 2
  bytes read: 84.2 MB
  rows output: 42

files pruned versus files read is the number to watch, exactly like Snowflake’s partitions_scanned. Reading every file for 42 rows means the clustering does not match the query.

Skipping depends on statistics, which Delta collects for the first 32 columns by default. A filter on column 40 of a wide table prunes nothing. Move the column, or raise the limit:

alter table bookshop.silver.orders
    set tblproperties ('delta.dataSkippingNumIndexedCols' = '48');

OK

Long string columns are the usual reason to lower it — collecting min/max on a 4 KB description column bloats the log for statistics nobody filters on.

Deletion vectors

alter table bookshop.silver.orders
    set tblproperties ('delta.enableDeletionVectors' = 'true');

delete from bookshop.silver.orders where status = 'cancelled';

num_affected_rows
-----------------
             1204

-- 1.1 seconds

Without deletion vectors, deleting 1,204 rows scattered across 22 files rewrites all 22 — tens of gigabytes for a few thousand rows. With them, Delta writes a small marker file and the data files are untouched.

The cost is that readers apply the vectors, so they accumulate. Materialise them periodically:

reorg table bookshop.silver.orders apply (purge);

path                              metrics
--------------------------------  ------------------------------------------
s3://bookshop-lake/silver/orders  {numFilesAdded: 21, numFilesRemoved: 22}

Letting Databricks do it

alter table bookshop.silver.orders
    set tblproperties ('delta.autoOptimize.optimizeWrite' = 'true',
                       'delta.autoOptimize.autoCompact'  = 'true');

OK

optimizeWrite sizes files sensibly as they are written; autoCompact merges small files after a write. Together they prevent most small-file problems rather than repairing them.

Predictive optimization goes further, running OPTIMIZE and VACUUM on managed tables automatically, based on usage:

alter catalog bookshop_prod enable predictive optimization;

OK

select 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 limit 3;

table_name      operation_type  usage_unit  dbus
--------------  --------------  ----------  -----
silver.orders   OPTIMIZE        DBU         12.44
bronze.orders   VACUUM          DBU          8.02
silver.events   OPTIMIZE        DBU          6.18

It is not free, but it is cheaper than the queries it speeds up, and it removes the maintenance job everyone forgets to write.

Cleaning up

vacuum bookshop.bronze.orders retain 168 hours dry run;

Found 44102 files (17.3 GB) that are safe to delete.

vacuum bookshop.bronze.orders;

path
--------------------------------
s3://bookshop-lake/bronze/orders

Those 44,102 files are the ones OPTIMIZE superseded — until vacuumed, you are paying to store both copies. The trade-off is Time Travel: vacuuming to 7 days means you cannot read a version older than that, whatever DESCRIBE HISTORY still lists.

An order of operations

1. Turn on optimizeWrite and autoCompact so the problem does not recur.
2. OPTIMIZE to fix the files you already have.
3. CLUSTER BY the columns your queries filter on — not the ones they select.
4. Measure files pruned versus read; if it did not change, the keys are wrong.
5. VACUUM on a schedule, with retention set above your longest job.
6. Do not partition unless the table is above a terabyte.

Practice

-- before
numFiles  sizeInBytes
--------  -----------
   44120  18402304512
-- after
numFiles  sizeInBytes
--------  -----------
      18  18395021312

-- before: files read 22, files pruned 0
-- after:  files read  2, files pruned 20

-- without: 22 files rewritten, 48.2s
-- with:     0 files rewritten,  1.1s

Found 44102 files (17.3 GB) that are safe to delete.

Next: structured streaming — continuous ingestion, checkpoints, and exactly-once sinks.