'CREATE OR REPLACE' SQL error in a Delta table

Correct the job schedule to ensure that only one query is executed at a time for a specific table.

Written by lakshay.goel

Last published at: September 23rd, 2024

Problem 

When trying to run the CREATE or REPLACE statement against a Delta table, you may encounter the following issue: 

[TABLE_OR_VIEW_ALREADY_EXISTS] Cannot create table or view <catalog>.<schema>.<table-name> because it already exists. Choose a different name, drop or replace the existing object, add the IF NOT EXISTS clause to tolerate pre-existing objects, or add the OR REFRESH clause to refresh the existing streaming table.

Cause

When multiple simultaneous CREATE OR REPLACE queries run, a table or view may be created before the previous process has finished, leading to conflicts and errors.

Solution

  • Correct the job schedule to ensure that only one query is executed at a time for a specific table.
  • Make sure the catalog name, schema name and table name values are specified correctly.

In general, avoid running multiple CREATE OR REPLACE queries simultaneously. Running parallel queries in notebooks and DBSQL targeting the creation or modification of a table might conflict.