Getting started with Delta Lake basics

[rachelnguyen]

Hi all — I am working through the Delta Lake intro and wanted to share a short note on using OPTIMIZE after merge. Happy to hear better patterns.

[data_pulse]

Did you forgot to share the short note? 🙂

If you are referring to running optimize after Merge, its worth mentioning that it doesn't need to be run after every Merge.

MERGE can create or rewrite files, but whether OPTIMIZE is needed immediately depends on the table size, write frequency, file layout, and query pattern. For frequently updated tables, its better to avoid.

Better options are: Databricks can already reduce small-file issues with optimized writes and auto compaction.

ALTER TABLE catalog.schema.table_name
SET TBLPROPERTIES (
  delta.autoOptimize.optimizeWrite = true,
  delta.autoOptimize.autoCompact = true
);

Then use OPTIMIZE based on actual file/query behavior or let Predictive Optimization manage maintenance automatically for UC managed tables. Reference

[balajij8]

@rachelnguyen MERGE operation is inherently expensive as it searches the entire target table to find matches, then shuffles data multiple times to write updates/inserts. It generates many small files - especially on partitioned tables where each shuffle task can write to multiple partitions. You can run OPTIMIZE to compact these small files back into larger ones to improve reads on subsequent queries. Without compaction, each successive MERGE compounds the small file issue ultimately degrading query performance.

You can rely on Liquid Clustering and Predictive Optimization

• Liquid Clustering (CLUSTER BY AUTO) replaces partitioning and Z-ordering for most cases. It works naturally with OPTIMIZE which respects clustering keys.

ALTER TABLE workspace.default.files CLUSTER BY AUTO​

• Predictive optimization automatically runs OPTIMIZE, VACUUM and ANALYZE on Unity Catalog managed tables and you can prefer it over manual maintenance.
• Low Shuffle Merge provides an optimized MERGE implementation that preserves existing data layout (including liquid clustering) on unmodified rows. Periodic OPTIMIZE remains useful.