Delta Lake Commands Reference: OPTIMIZE, VACUUM, MERGE Complete Guide

The SQL commands you use to operate on Delta Lake are the single most heavily tested topic across the Databricks certifications. On the Data Engineer Associate exam, roughly 30-40% of the questions involve Delta Lake, and your understanding of the exact syntax and behavior of OPTIMIZE, VACUUM, MERGE INTO, DESCRIBE HISTORY, RESTORE, and DESCRIBE DETAIL often decides pass vs. fail. This article walks through each command's syntax, behavior, gotchas, and exam patterns in full detail.

Delta Lake Core Commands at a Glance

VACUUM is not recorded in the transaction log, so it never shows up in DESCRIBE HISTORY output. This is a classic exam gotcha.

OPTIMIZE

OPTIMIZE is the compaction command that merges accumulated small Parquet files into larger ones. When Structured Streaming micro-batches or frequent INSERTs generate large numbers of small files, metadata overhead balloons and query performance degrades, so periodic OPTIMIZE runs are recommended.

-- 基本構文：テーブル全体を最適化
OPTIMIZE my_catalog.my_schema.sales

-- WHERE句でパーティションを絞り込み
OPTIMIZE my_catalog.my_schema.sales
  WHERE region = 'APAC' AND order_date >= '2026-01-01'

-- Z-ORDERと組み合わせ（最大4カラム、推奨2〜3カラム）
OPTIMIZE my_catalog.my_schema.sales
  ZORDER BY (customer_id, product_id)

-- Liquid Clustering対応テーブルではZORDER不要
-- OPTIMIZE実行時にCLUSTER BY設定に基づき自動配置
OPTIMIZE my_catalog.my_schema.events

Z-ORDER vs Liquid Clustering

Z-ORDER lays out data along a space-filling curve based on the specified columns to maximize data skipping. But Z-ORDER is only applied when OPTIMIZE runs, so its effect fades with subsequent INSERTs. Liquid Clustering is a newer approach available in Databricks Runtime 13.3+ that automatically places data on write based on the columns specified in CLUSTER BY.

-- Liquid Clusteringテーブルの作成
CREATE TABLE events (
  event_id BIGINT,
  user_id BIGINT,
  event_type STRING,
  event_date DATE
)
CLUSTER BY (user_id, event_date)

-- 既存テーブルにLiquid Clusteringを設定
ALTER TABLE events CLUSTER BY (user_id, event_date)

-- Liquid Clusteringを解除
ALTER TABLE events CLUSTER BY NONE

VACUUM

VACUUM physically deletes old Parquet files that are no longer referenced by the current table version. It cuts storage costs, but be aware that any Time Travel or RESTORE targeting versions tied to the deleted files will fail.

-- デフォルト保持期間（168時間 = 7日間）で実行
VACUUM my_catalog.my_schema.sales

-- 保持期間を30日に指定
VACUUM my_catalog.my_schema.sales RETAIN 720 HOURS

-- DRY RUN：削除対象のファイル一覧を確認（実際には削除しない）
VACUUM my_catalog.my_schema.sales DRY RUN

The default retention period is 168 hours (7 days). Specifying a shorter period would return an error because in-flight readers might still be holding references to those files. You can force a shorter window with the setting below, but it is not recommended in production:

-- 保持期間チェックの無効化（非推奨）
SET spark.databricks.delta.retentionDurationCheck.enabled = false;
VACUUM my_catalog.my_schema.sales RETAIN 0 HOURS

MERGE INTO

MERGE INTO joins source data with a target Delta Table on a matching condition and performs UPDATE, INSERT, and DELETE as a single atomic operation based on match/no-match outcomes. It is the most important command at the heart of CDC pipelines and upsert patterns.

MERGE INTO gold.customers AS target
USING silver.customers_updates AS source
ON target.customer_id = source.customer_id

WHEN MATCHED AND source.is_deleted = true THEN
  DELETE

WHEN MATCHED THEN
  UPDATE SET
    target.name       = source.name,
    target.email      = source.email,
    target.updated_at = source.updated_at

WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, created_at, updated_at)
  VALUES (source.customer_id, source.name, source.email,
          source.created_at, source.updated_at)

WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET target.is_active = false

Behavior of Each Clause

DESCRIBE HISTORY

DESCRIBE HISTORY shows the change history recorded in a Delta Table's transaction log. You can inspect each version's operation, user, and parameters — useful for debugging and auditing.

-- 全履歴を表示
DESCRIBE HISTORY my_catalog.my_schema.sales

-- 直近10件の履歴を表示
DESCRIBE HISTORY my_catalog.my_schema.sales LIMIT 10

Output Columns

RESTORE TABLE

RESTORE TABLE rolls a Delta Table back to a previous version or timestamp. Use it to undo mistaken DELETEs or UPDATEs. RESTORE creates a new version — it does not rewrite history; instead it appends the restored state as a new version.

-- バージョン番号で復元
RESTORE TABLE my_catalog.my_schema.sales TO VERSION AS OF 5

-- タイムスタンプで復元
RESTORE TABLE my_catalog.my_schema.sales
  TO TIMESTAMP AS OF '2026-03-20 10:00:00'

If the target version needs Parquet files that VACUUM has already deleted, RESTORE fails with FileNotFoundException. For important tables, set a longer VACUUM retention period.

DESCRIBE DETAIL

DESCRIBE DETAIL shows a Delta Table's physical metadata (file location, size, partition info, etc.). Use it to diagnose table health.

DESCRIBE DETAIL my_catalog.my_schema.sales

Output Columns

numFiles being unusually high suggests you should run OPTIMIZE, while sizeInBytes being larger than expected may mean VACUUM has not been run. DESCRIBE DETAIL is a handy command for checking table health.

Time Travel Queries

Time Travel lets you read past versions of a Delta Table. Unlike RESTORE, it does not change the table's current state.

-- VERSION AS OF 構文
SELECT * FROM my_catalog.my_schema.sales VERSION AS OF 3

-- TIMESTAMP AS OF 構文
SELECT * FROM my_catalog.my_schema.sales
  TIMESTAMP AS OF '2026-03-20 10:00:00'

-- @ 構文（簡略表記）
SELECT * FROM my_catalog.my_schema.sales@v3

Sample Question

Frequently Asked Questions