Use DuckDB with Unity Catalog

Attach a Unity Catalog catalog in DuckDB, query its Delta tables with SQL, and append rows.

DuckDB queries Unity Catalog tables with SQL and appends rows to them, including catalog-managed Delta tables. DuckDB's unity_catalog extension attaches a catalog as a DuckDB database, and its delta extension reads and writes the Delta files. Appends to a catalog-managed table go through the server, like a Spark write.

Install the extensions

Open a Python session with DuckDB:

uv run --with duckdb==1.5.6 python

Install and load delta, then unity_catalog from DuckDB's core_nightly channel:

import duckdb

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")

Attach a catalog

Create a secret that holds the server's address and a token, then attach one Unity Catalog catalog as a DuckDB database:

con.execute("""
    CREATE SECRET (
        TYPE unity_catalog,
        TOKEN 'not-used',
        ENDPOINT 'http://localhost:8080'
    )
""")
con.execute("ATTACH 'retail' AS retail (TYPE unity_catalog, DEFAULT_SCHEMA 'sales')")

• ENDPOINT is the server's base URL, without /api/2.1/unity-catalog.
• TOKEN is sent as a bearer token. The local server has authorization disabled and accepts any value. On a secured server, use a Unity Catalog access token.
• DEFAULT_SCHEMA is the schema DuckDB uses for unqualified table names.

Leave the secret unnamed. With a named secret (CREATE SECRET uc (...)), ATTACH fails with Could not resolve hostname.

List tables

con.sql("SELECT database, schema, name FROM (SHOW ALL TABLES)").show()

┌──────────┬─────────┬─────────┐
│ database │ schema  │  name   │
│ varchar  │ varchar │ varchar │
├──────────┼─────────┼─────────┤
│ retail   │ sales   │ orders  │
└──────────┴─────────┴─────────┘

Query a table

Use three-part names, or just the table name for tables in the default schema:

con.sql("""
    SELECT customer, amount
    FROM retail.sales.orders
    WHERE amount > 5
    ORDER BY order_id
""").show()

┌──────────┬───────────────┐
│ customer │    amount     │
│ varchar  │ decimal(10,2) │
├──────────┼───────────────┤
│ ada      │          9.50 │
│ grace    │         20.00 │
└──────────┴───────────────┘

DuckDB asks the server for the table's location and for temporary credentials, then reads the Delta files directly from storage. For a catalog-managed table it reads the latest version the server has accepted. DuckDB reads tables with column mapping through Unity Catalog, too.

Append rows

INSERT adds rows to the table:

con.execute("INSERT INTO retail.sales.orders VALUES (4, 'barbara', 12.00)")

On a catalog-managed table, DuckDB writes the new data and commit file, then asks the server to accept the commit as the table's next version. From then on, Spark and every other client see the new rows.

DuckDB can't append to a partitioned table that uses column mapping. The insert fails with Not implemented Error: Inserting into a partitioned Delta table that uses column mapping is not supported, and the table doesn't change.

Create, change, and drop tables

DuckDB reads tables and appends rows. Create tables with Spark. Update, delete from, and drop them with Spark too, as in Create and update a catalog-managed Delta table.

Required privileges

With authorization enabled, reading needs USE CATALOG on the catalog, USE SCHEMA on the schema, and SELECT on the table. Appending also needs MODIFY on the table. Owners have all of these. See Core concepts.

Next steps

• External tables and catalog-managed Delta tables explains why an append from DuckDB is safe next to a Spark writer.
• DuckDB's Unity Catalog extension documentation covers its other secret options and cloud storage.