Read and write catalog-managed Delta tables

Create catalog-managed Delta tables in Unity Catalog, and read and write them through Spark or DuckDB.

For catalog-managed Delta tables, the catalog brokers all reads and writes, enabling advanced features, better performance, and higher reliability. See External tables and catalog-managed Delta tables for details.

Set up the client

uv run --with pyspark==4.1.0 python

from pyspark.sql import SparkSession  # noqa: E402

spark = (
    SparkSession.builder.appName("managed-delta-tables")
    .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.inventory", "io.unitycatalog.spark.UCSingleCatalog")
    .config("spark.sql.catalog.inventory.uri", "http://localhost:8080")
    .config("spark.sql.catalog.inventory.auth.type", "static")
    .config("spark.sql.catalog.inventory.auth.token", "not-used")
    .getOrCreate()
)

uv run --with duckdb==1.5.6 python

import duckdb  # noqa: E402

con = duckdb.connect()
con.execute("INSTALL delta")
con.execute("LOAD delta")
con.execute("FORCE INSTALL unity_catalog FROM core_nightly")
con.execute("LOAD unity_catalog")
con.execute("""
    CREATE SECRET (
        TYPE unity_catalog,
        TOKEN 'not-used',
        ENDPOINT 'http://localhost:8080'
    )
""")
con.execute(
    "ATTACH 'inventory' AS inventory (TYPE unity_catalog, DEFAULT_SCHEMA 'stock')"
)

When an engine reads or writes the table, it asks the server for credentials scoped to the table's location; see Tune storage credentials for Spark's settings and Credential vending for what they protect.

Create a managed table

Create the table in Spark:

spark.sql("""
    CREATE TABLE inventory.stock.items (
        sku STRING,
        qty INT
    ) USING DELTA
""")

The connector creates it as a catalog-managed table and sets delta.feature.catalogManaged for you. You don't choose where it lives: the server allocates a path under the nearest storage root.

DuckDB can't create tables.

Creating a table from a query (CREATE TABLE … AS SELECT) is covered in the guide Replace or overwrite a managed Delta table.

Required table features

Before Spark writes a new table's first version, it asks the server to stage the table. The server's answer lists the table features and properties the table must have, and the ones it suggests. Spark turns on every requirement, and every suggestion you don't override.

The Delta protocol requires little of a catalog-managed table: reader version 3, writer version 7, and the catalogManaged and inCommitTimestamp features. Which other features a table needs is up to the catalog.

The Unity Catalog server requires catalogManaged, deletionVectors, v2Checkpoint, vacuumProtocolCheck, and inCommitTimestamp, and the properties that turn them on, such as delta.enableDeletionVectors. It suggests column mapping in name mode, row tracking, and domain metadata. To override a suggestion, set its property yourself, such as 'delta.columnMapping.mode' = 'none'. A requirement can't be overridden.

To list a table's protocol versions and features, run:

spark.sql("""
    DESCRIBE DETAIL inventory.stock.items
""").select("minReaderVersion", "minWriterVersion", "tableFeatures").show(
    truncate=False
)

A create that turns off deletion vectors fails:

try:
    spark.sql("""
        CREATE TABLE inventory.stock.returns (sku STRING, qty INT)
        USING DELTA
        TBLPROPERTIES ('delta.enableDeletionVectors' = 'false')
    """)
except Exception as err:
    print(err)

Cannot create table inventory.stock.returns: catalog requires table property 'delta.enableDeletionVectors'='true' but the caller supplied 'delta.enableDeletionVectors'='false'. Remove the conflicting TBLPROPERTIES entry and retry.

Write to the table

Append rows with INSERT INTO:

spark.sql("""
    INSERT INTO inventory.stock.items VALUES
        ('apple', 10),
        ('pear', 4),
        ('plum', 0)
""")

con.execute("INSERT INTO inventory.stock.restock VALUES ('fig', 7)")

con.execute("DETACH inventory")
con.execute(
    "ATTACH 'inventory' AS inventory (TYPE unity_catalog, DEFAULT_SCHEMA 'stock')"
)
con.execute("INSERT INTO inventory.stock.restock VALUES ('lime', 3)")

Update and delete rows in Spark:

spark.sql("UPDATE inventory.stock.items SET qty = qty + 5 WHERE sku = 'pear'")
spark.sql("DELETE FROM inventory.stock.items WHERE qty = 0")

DuckDB can't update or delete rows.

The server accepts one commit for each version, so when two writers race for the same version, one of them fails. See Why a catalog should accept commits.

Read the table

Query the table by its three-part name:

spark.sql("""
    SELECT sku, qty
    FROM inventory.stock.items
    ORDER BY sku
""").show()

+-----+---+
|  sku|qty|
+-----+---+
|apple| 10|
| pear|  9|
+-----+---+

con.sql("""
    SELECT sku, qty
    FROM inventory.stock.items
    ORDER BY sku
""").show()

┌─────────┬───────┐
│   sku   │  qty  │
│ varchar │ int32 │
├─────────┼───────┤
│ apple   │    10 │
│ pear    │     9 │
└─────────┴───────┘

Read an earlier version

To query the table as it was at an earlier version, add VERSION AS OF in Spark:

spark.sql("""
    SELECT sku, qty
    FROM inventory.stock.items VERSION AS OF 1
    ORDER BY sku
""").show()

+-----+---+
|  sku|qty|
+-----+---+
|apple| 10|
| pear|  4|
| plum|  0|
+-----+---+

Version 1 is the table after the first insert, before the update and delete.

Don't write to the table's path

Drop a managed table

Drop the table in Spark:

spark.sql("DROP TABLE inventory.stock.items")

The server deletes the files only if it can write to them.

Required privileges

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

See Ownership and privileges.

Next steps

• Create and update a catalog-managed Delta table walks the same lifecycle step by step.
• Use Spark with Unity Catalog configures authentication and credential options for the connector.
• Use DuckDB with Unity Catalog covers DuckDB's secrets and table listing.
• External tables and catalog-managed Delta tables explains how the server accepts commits.