OPTIMIZE is only supported for Delta tables error on Delta Lake

Use CREATE OR REPLACE TABLE when moving Delta tables from one storage location to another.

Written by mathan.pillai

Last published at: February 3rd, 2023

Problem

You run OPTIMIZE on a Delta table and get an error message saying it is only supported on Delta tables.

Error: `<database-name>`.`<table-name>`is not a Delta table. OPTIMIZE is only supported for Delta tables.

Cause

This can happen if the target table's storage location was modified and the table was recreated with a new storage location before you tried to run OPTIMIZE.

If you review the driver logs, you see that there is no Delta log for the table at the old location.

INFO DeltaLog: No delta log found for the Delta table at <old-location>

This means the metadata is still pointing to the old table location. It has not been updated with the new (current) table location.

Solution

  1. Ensure the Delta table is recreated in the new location using CREATE OR REPLACE TABLE (AWS | Azure | GCP). This replaces the Delta table.
  2. After the Delta table has been moved, run FSCK REPAIR TABLE (AWS | Azure | GCP).
    FSCK REPAIR TABLE `<database-name>`.`<table-name>`
  3. Run OPTIMIZE to optimize the Delta table. It should run successfully complete.
    OPTIMIZE `<database-name>``<table-name>