Overview

A year ago, DuckDB could read Delta tables. Today, it can insert data into them, travel through their history, and query and write through a governed catalog — without the experimental caveat on any of it.

DuckDB’s Delta Lake extension received significant updates in v1.5.3, bringing the following core capabilities:

• Write Support: Direct data insertion into Delta tables via INSERT INTO
• Unity Catalog Integration: Data governance and permission management through Databricks Unity Catalog
• Catalog Managed Table (CMT): Multi-writer coordination and conflict detection
• Time Travel Queries: Historical data queries by version number or timestamp
• Incremental Snapshot Loading: Significantly faster time travel across multiple versions

This article provides an in-depth exploration of these features with complete executable SQL examples to help you build production-grade data pipelines.

Delta Lake Writes: From Read-Only to Full Write Capability

Writing to Delta Tables

DuckDB’s Delta extension now supports direct writes to Delta tables. You can append data using standard INSERT INTO statements:

-- Connect to a Delta table
ATTACH 's3://my-bucket/delta-table' AS delta_db (TYPE delta);

-- Insert data into the Delta table
INSERT INTO delta_db.my_table
SELECT
 gen_random_uuid()::VARCHAR AS id,
 ['Beijing', 'Shanghai', 'Guangzhou', 'Shenzhen', 'Hangzhou'][1 + (random() * 4)::INT] AS city,
 (1000 + random() * 9000)::INT AS amount,
 CURRENT_DATE AS sale_date
FROM range(1000);

-- Verify the write results
SELECT count(*) AS total_rows FROM delta_db.my_table;

After writing, you can see newly generated Parquet files and Delta logs in the local data directory:

data
└── delta-table
 ├── _delta_log
 │ ├── 00000000000000000000.json
 │ ├── 00000000000000000001.json
 │ └── 00000000000000000002.json
 ├── part-00000-xxxx.parquet
 └── part-00001-yyyy.parquet

Comparison with Traditional Tools

Unity Catalog Integration: Enterprise-Grade Data Governance

What is Unity Catalog?

Unity Catalog is Databricks’ unified data governance solution, supporting cross-workspace permission management, metadata management, and audit trails. Through DuckDB’s Unity Catalog extension, you can directly query Unity Catalog-managed Delta Lake tables in a local environment.

Configuration and Connection

Before using Unity Catalog, you need to configure the following credentials:

-- Create Unity Catalog secrets
CREATE SECRET (
 TYPE unity_catalog,
 KEY_ID 'your-access-key',
 SECRET 'your-secret-key',
 TOKEN 'your-session-token'
);

-- Attach Unity Catalog data source
ATTACH 'unity://catalog' AS uc (TYPE unity_catalog);

-- List available schemas
SHOW SCHEMAS FROM uc;

Querying Unity Catalog Managed Tables

-- Query tables managed by Unity Catalog
SELECT * FROM uc.my_catalog.my_schema.sales LIMIT 10;

-- Cross-schema aggregation analysis
SELECT
 product_category,
 COUNT(*) AS order_count,
 SUM(amount) AS total_revenue,
 AVG(amount) AS avg_order_value
FROM uc.analytics.ecommerce.orders
WHERE sale_date >= DATE '2024-01-01'
GROUP BY product_category
ORDER BY total_revenue DESC;

Catalog Managed Table (CMT)

Catalog Managed Table is the core feature of Unity Catalog integration. It achieves multi-writer coordination through the Catalog Commits mechanism:

-- Enable CMT attribute via Spark or UC CLI
-- CREATE TABLE my_schema.concurrent_tbl (...)
-- TBLPROPERTIES ('delta.feature.catalogManaged' = 'supported');

-- After enabling, DuckDB writes automatically go through UC commit orchestration
INSERT INTO uc.my_catalog.my_schema.concurrent_tbl
VALUES (gen_random_uuid()::VARCHAR, 'Product A', 999, TRUE);

How Catalog Commits Works:

1. Each writer stages its commit to the _staged_commits/ directory
2. Registers the commit with Unity Catalog
3. UC acts as the arbiter: only one writer wins per version
4. Other writers receive conflict errors and can retry

Under high concurrency scenarios (e.g., 20 parallel writers), CMT ensures data consistency:

[worker 6] OK - inserted 5 rows
[worker 5] CONFLICT - another writer won this version, retry needed
[worker 2] CONFLICT - another writer won this version, retry needed
[worker 1] OK - inserted 5 rows
[worker 16] OK - inserted 5 rows

Final result: 10 initial rows + (5 successful writes × 5 rows) = 35 rows, with no data loss or duplicates.

Time Travel Queries: Navigating Delta Table History

Querying by Version Number

The core concept of Delta Lake is time travel. DuckDB supports querying historical data by version number:

-- Attach a Delta table at a specific version
ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, VERSION 16);

-- Query data at version 16
SELECT count(*) FROM t; -- Returns data count at that version

-- Attach another version
ATTACH 's3://my-bucket/delta-table' AS t2 (TYPE delta, VERSION 20);
SELECT count(*) FROM t2; -- Returns data count at version 20

Querying by Timestamp

-- Attach a Delta table at a specific point in time
ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, TIMESTAMP '2024-03-15 10:30:00');

-- Query data state at that point in time
SELECT * FROM t WHERE sale_date >= DATE '2024-03-15';

Incremental Snapshot Loading

When time traveling across multiple versions in a Delta lake, incremental snapshot loading can significantly improve query performance:

-- First query version 16
ATTACH 's3://my-bucket/delta-table' AS t16 (TYPE delta, VERSION 16);
SELECT count(*) FROM t16;

-- Then query version 20 - incremental loading won't re-read old logs
ATTACH 's3://my-bucket/delta-table' AS t20 (TYPE delta, VERSION 20);
SELECT count(*) FROM t20;

-- Verify incremental loading effect
SET enable_logging = true;
SET delta_kernel_logging = true;
CALL enable_logging('DeltaKernel', level = 'trace');

ATTACH 's3://my-bucket/delta-table' AS t (TYPE delta, VERSION 20);
SELECT count(*) FROM t;

-- Check if old log files were re-read
SELECT count() FROM duckdb_logs
WHERE type = 'DeltaKernel'
 AND message LIKE '%00000000000000000%.json%';
-- Returns 0, meaning cached snapshot was reused

In Delta lakes with thousands or millions of snapshots, incremental loading provides a massive performance win for workloads spanning multiple versions.

Time Travel in Practice: Data Recovery Scenario

-- Simulate data recovery after accidental deletion
-- 1. Check current data
ATTACH 's3://my-bucket/sales-data' AS current (TYPE delta);
SELECT count(*) FROM current.sales; -- Assume 10,000 rows

-- 2. Accidental deletion occurs...
DELETE FROM current.sales WHERE region = 'East China';

-- 3. Roll back to pre-deletion version
ATTACH 's3://my-bucket/sales-data' AS backup (TYPE delta, VERSION 42);
SELECT count(*) FROM backup.sales; -- Still 10,000 rows

-- 4. Restore data from backup
INSERT INTO current.sales
SELECT * FROM backup.sales
WHERE region = 'East China'
 AND id NOT IN (SELECT id FROM current.sales);

DuckDB vs Traditional Delta Lake Toolchains

Summary

DuckDB’s Delta Lake integration has evolved from initial read-only support to full data writing, governance, and time travel capabilities. Combined with Unity Catalog’s enterprise-grade governance and Catalog Managed Table’s concurrency control, DuckDB has become an ideal choice for building modern data lake architectures.

Whether you’re a data engineer, analyst, or developer, mastering the DuckDB + Delta Lake + Unity Catalog combination will unlock new possibilities for your career and business growth.

Next Steps: Install the Delta extension in your DuckDB environment today, try writing to Delta tables, and experience the power of time travel.

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。