Delta Lake Table Optimization

-- Partition by columns used in WHERE clauses
-- Best for date/time filters
CREATE TABLE catalog.schema.events (
  event_id STRING,
  event_type STRING,
  user_id STRING,
  event_timestamp TIMESTAMP,
  payload STRING
)
USING DELTA
PARTITIONED BY (event_date DATE)
TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact' = 'true'
);

-- Rule: Only partition if column has < 10,000 distinct values
-- Rule: Partitions should be > 1GB each for optimal performance

-- Z-ORDER by columns used in JOINs and filters (not partition columns)
OPTIMIZE catalog.schema.events
ZORDER BY (user_id, event_type);

-- Run after large writes or on a schedule
-- Z-ORDER is most effective with 2-4 columns

-- OPTIMIZE compacts small files into larger ones
OPTIMIZE catalog.schema.events;

-- Check file sizes before optimization
DESCRIBE DETAIL catalog.schema.events;
-- Look at: numFiles, sizeInBytes

-- Target: files between 256MB-1GB after compaction

-- Remove files older than retention period
VACUUM catalog.schema.events RETAIN 168 HOURS; -- 7 days

-- Check history before vacuuming
DESCRIBE HISTORY catalog.schema.events;

-- Set default retention
ALTER TABLE catalog.schema.events
SET TBLPROPERTIES ('delta.deletedFileRetentionDuration' = 'interval 7 days');

ALTER TABLE catalog.schema.events SET TBLPROPERTIES (
  'delta.autoOptimize.optimizeWrite' = 'true',
  'delta.autoOptimize.autoCompact' = 'true',
  'delta.targetFileSize' = '256mb',
  'delta.tuneFileSizesForRewrites' = 'true',
  'delta.deletedFileRetentionDuration' = 'interval 7 days',
  'delta.logRetentionDuration' = 'interval 30 days'
);