Replace or overwrite a managed Delta table

Replace a managed Delta table's definition and rows, all of its rows, or some of them with CREATE OR REPLACE, INSERT OVERWRITE, and REPLACE WHERE in Spark SQL.

To replace data in a catalog-managed Delta table in Unity Catalog, use CREATE OR REPLACE TABLE to change its columns and rows, INSERT OVERWRITE to swap all rows, and INSERT INTO … REPLACE WHERE or dynamic partition overwrite to swap some of them.

Set up the client

Open a Python session with PySpark:

uv run --with pyspark==4.1.0 python

The examples call spark.sql in a session whose default catalog is replace_demo:

from pyspark.sql import SparkSession

spark = (
    SparkSession.builder.appName("replace-orders")
    .config(
        "spark.jars.packages",
        "io.unitycatalog:unitycatalog-spark_4.1_2.13:0.7.0,"
        "io.delta:delta-spark_4.1_2.13:4.3.1",
    )
    .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
    .config(
        "spark.sql.catalog.spark_catalog",
        "org.apache.spark.sql.delta.catalog.DeltaCatalog",
    )
    .config("spark.sql.catalog.replace_demo", "io.unitycatalog.spark.UCSingleCatalog")
    .config("spark.sql.catalog.replace_demo.uri", "http://localhost:8080")
    .config("spark.sql.catalog.replace_demo.auth.type", "static")
    .config("spark.sql.catalog.replace_demo.auth.token", "not-used")
    .config("spark.sql.defaultCatalog", "replace_demo")
    .getOrCreate()
)

Use Spark with Unity Catalog explains each setting.

Choose how to replace data

The statements differ in what they need to exist and what they replace:

Create a table from a query

To create a managed table from the result of a query, use CREATE TABLE … AS SELECT without a LOCATION:

spark.sql("""
    CREATE TABLE sales.orders USING DELTA AS
    SELECT * FROM VALUES
        (1, 'ada',   9.50,  DATE'2026-10-01'),
        (2, 'grace', 20.00, DATE'2026-10-01'),
        (3, 'linus', 3.25,  DATE'2026-10-02')
    AS t(order_id, customer, amount, day)
""")

The new table is catalog-managed, like one made with CREATE TABLE, and its first version, 0, is a CREATE TABLE AS SELECT. It takes its four columns from the query and has no partition columns. Keep USING DELTA. Without it, Spark asks for the session's default format, and the statement fails with Unity Catalog does not support non-Delta managed table.

Replace a table's definition and rows

To change a table's columns or partitioning and rewrite its rows in one commit, use CREATE OR REPLACE TABLE … AS SELECT. This statement adds a tax column and partitions the table by day:

spark.sql("""
    CREATE OR REPLACE TABLE sales.orders USING DELTA
    PARTITIONED BY (day) AS
    SELECT *, amount * 0.20 AS tax FROM sales.orders
""")

The query reads the table it replaces. The result is version 1 of the same table, with the same table ID and location. Version 0 still reads with its old four columns.

REPLACE TABLE does the same for a table that must already exist. Use it when replacing a table that isn't there should be an error rather than a new table.

Overwrite all rows

To swap every row and keep the table's columns, use INSERT OVERWRITE:

spark.sql("""
    INSERT OVERWRITE sales.orders VALUES
        (4, 'ada',   12.00, DATE'2026-10-03', 2.40),
        (5, 'grace', 8.00,  DATE'2026-10-03', 1.60),
        (6, 'linus', 5.00,  DATE'2026-10-04', 1.00)
""")

The table now holds orders 4, 5, and 6. The new rows must have every column of the table. Two values for this five-column table fail with DELTA_INSERT_COLUMN_ARITY_MISMATCH: target table has 5 column(s) but the inserted data has 2 column(s). To change the columns as well, replace the table instead.

Overwrite some rows or partitions

Replace the rows that match a condition

To replace only the rows that match a condition, use INSERT INTO … REPLACE WHERE. This statement swaps the orders for October 3 for one new order:

spark.sql("""
    INSERT INTO sales.orders
    REPLACE WHERE day = DATE'2026-10-03'
    VALUES (7, 'ada', 30.00, DATE'2026-10-03', 6.00)
""")

Order 6, from October 4, stays. Every new row must match the condition. A row outside it fails the whole statement with DELTA_REPLACE_WHERE_MISMATCH, and the table doesn't change.

Overwrite the partitions the new rows write to

On a partitioned table, dynamic partition overwrite replaces only the partitions that the new rows land in. Turn it on for the session, run INSERT OVERWRITE, and turn it off again:

spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
spark.sql("""
    INSERT OVERWRITE sales.orders VALUES
        (8, 'grace', 4.00, DATE'2026-10-04', 0.80)
""")
spark.conf.unset("spark.sql.sources.partitionOverwriteMode")

The new order replaces order 6 in the October 4 partition. Order 7, on October 3, stays. While the setting is on, an INSERT OVERWRITE meant to replace the whole table keeps every partition its rows don't touch, so unset it after the write.

Check a replacement and undo it

To see what each statement did, list the table's history:

spark.sql("DESCRIBE HISTORY sales.orders").select("version", "operation").show(
    truncate=False
)

+-------+---------------------------------+
|version|operation                        |
+-------+---------------------------------+
|4      |WRITE                            |
|3      |WRITE                            |
|2      |WRITE                            |
|1      |CREATE OR REPLACE TABLE AS SELECT|
|0      |CREATE TABLE AS SELECT           |
+-------+---------------------------------+

The overwrites are all WRITE operations. The operationParameters column tells them apart: mode is Overwrite for version 2, version 3 records the REPLACE WHERE condition as predicate, and version 4 has isDynamicPartitionOverwrite set to true.

To read the table as it was before a change, query an earlier version:

spark.sql(
    "SELECT order_id, day FROM sales.orders VERSION AS OF 3 ORDER BY order_id"
).show()

+--------+----------+
|order_id|       day|
+--------+----------+
|       6|2026-10-04|
|       7|2026-10-03|
+--------+----------+

To undo a change, restore that version:

spark.sql("RESTORE TABLE sales.orders TO VERSION AS OF 3")

Restoring commits a new version, 5, whose operation is RESTORE. Versions 3 and 4 stay in the history.

What a replacement can't change

A replacement keeps the table's identity. Statements that try to change it fail before they commit, and the table keeps its rows, ID, and history:

Other table properties aren't protected this way. For example, a replacement with TBLPROPERTIES ('delta.enableDeletionVectors' = 'false') commits, and the table has deletion vectors off.

These statements are verified with Spark. DuckDB can't create a table from a query, and the deltalake-based clients can't open catalog-managed tables.

Required privileges

With authorization enabled, each task needs these privileges. Owners have all of them.

See Ownership and privileges.

Next steps

• Read an earlier version of a managed table, step by step.
• Learn how the catalog accepts each commit in External tables and catalog-managed Delta tables.
• To change rows in place, or a table's schema without rewriting it, see the guides Read and write catalog-managed Delta tables and Change a table's schema and properties.