Getting Started with DuckLake 1.0: A SQL-Based Data Lake Format

<h2 id="overview">Overview</h2> <p>DuckLake 1.0 introduces a fresh approach to managing data lake metadata. Instead of scattering metadata across numerous files in object storage, it centralizes table metadata in a SQL database—making updates, sorting, and partitioning more efficient. Built as a DuckDB extension, DuckLake integrates seamlessly with existing workflows and offers compatibility with Iceberg-style features. This guide walks you through its setup, core operations, and common pitfalls.</p><figure style="margin:20px 0"><img src="https://res.infoq.com/news/2026/05/ducklake-sql-catalog/en/headerimage/generatedHeaderImage-1776423164012.jpg" alt="Getting Started with DuckLake 1.0: A SQL-Based Data Lake Format" style="width:100%;height:auto;border-radius:8px" loading="lazy"><figcaption style="font-size:12px;color:#666;margin-top:5px">Source: www.infoq.com</figcaption></figure> <h2 id="prerequisites">Prerequisites</h2> <ul> <li><strong>DuckDB</strong>: Version 0.10.0 or higher (command-line interface or Python binding).</li> <li><strong>Object Storage</strong>: A bucket or directory (e.g., S3, MinIO, local filesystem) for storing parquet files.</li> <li><strong>SQL Database</strong>: For the catalog—DuckDB itself works for local testing; production uses PostgreSQL or MySQL.</li> <li><strong>DuckLake Extension</strong>: Install via <code>INSTALL ducklake; LOAD ducklake;</code>.</li> </ul> <h2 id="step-by-step">Step-by-Step Instructions</h2> <h3 id="install-extension">1. Install and Load the DuckLake Extension</h3> <p>Open DuckDB and run:</p> <pre><code>INSTALL ducklake FROM community; LOAD ducklake;</code></pre> <p>This registers DuckLake’s functions and types. Verify with <code>SELECT * FROM ducklake_version();</code></p> <h3 id="create-catalog">2. Create a DuckLake Catalog</h3> <p>A catalog holds all table metadata. Use <code>CREATE DUCKLAKE CATALOG</code>:</p> <pre><code>CREATE DUCKLAKE CATALOG my_catalog DATABASE 'duckdb' -- can be 'postgresql' or 'mysql' CONNECTION_STRING 'file:///path/to/catalog.db'; -- Switch to the catalog USE my_catalog;</code></pre> <p><em>Tip</em>: For remote databases, use a connection string like <code>postgresql://user:pass@host/db</code>.</p> <h3 id="create-table">3. Create a DuckLake Table</h3> <p>Define a table with partitioning and sorting:</p> <pre><code>CREATE DUCKLAKE TABLE sales ( order_id INTEGER, amount DECIMAL(10,2), order_date DATE, region VARCHAR ) PARTITIONED BY (region) SORTED BY (order_date);</code></pre> <p>This creates a logical table. Data is stored as Parquet files in your object storage.</p> <h3 id="write-data">4. Insert Data</h3> <p>Insert directly or from a SELECT:</p> <pre><code>INSERT INTO sales VALUES (1, 150.00, '2025-01-15', 'East'), (2, 200.50, '2025-01-16', 'West');</code></pre> <p>DuckLake automatically writes new Parquet files per partition and updates the catalog.</p> <h3 id="read-data">5. Query the Table</h3> <p>Standard SQL works—DuckLake reads the catalog to locate files:</p> <pre><code>SELECT region, SUM(amount) AS total_sales FROM sales WHERE order_date >= '2025-01-01' GROUP BY region;</code></pre> <p>Partition pruning and sorting are applied automatically.</p><figure style="margin:20px 0"><img src="https://imgopt.infoq.com/fit-in/100x100/filters:quality(80)/presentations/game-vr-flat-screens/en/smallimage/thumbnail-1775637585504.jpg" alt="Getting Started with DuckLake 1.0: A SQL-Based Data Lake Format" style="width:100%;height:auto;border-radius:8px" loading="lazy"><figcaption style="font-size:12px;color:#666;margin-top:5px">Source: www.infoq.com</figcaption></figure> <h3 id="manage-partitions">6. Manage Partitions and Small Updates</h3> <p>DuckLake supports incremental updates without rewriting whole partitions. Use <code>MERGE</code> or <code>DELETE</code>:</p> <pre><code>DELETE FROM sales WHERE order_id = 1; MERGE INTO sales AS target USING (VALUES (3, 300.00, '2025-01-20', 'East')) AS src ON target.order_id = src.column1 WHEN MATCHED THEN UPDATE SET amount = src.column2 WHEN NOT MATCHED THEN INSERT (order_id, amount, order_date, region) VALUES (src.column1, src.column2, src.column3, src.column4);</code></pre> <p>The catalog tracks these small changes efficiently.</p> <h3 id="iceberg-compat">7. Iceberg Compatibility</h3> <p>DuckLake can read Iceberg tables if you enable compatibility mode:</p> <pre><code>SET ducklake_iceberg_compat = true; SELECT * FROM iceberg_scan('s3://bucket/iceberg_table');</code></pre> <p>Write support is limited to DuckLake-native tables.</p> <h2 id="common-mistakes">Common Mistakes</h2> <ul> <li><strong>Forgetting to load the extension</strong>: Always run <code>LOAD ducklake;</code> after installation.</li> <li><strong>Wrong catalog connection string</strong>: Ensure the path or database URL is correct and accessible.</li> <li><strong>Partition key mismatch</strong>: When inserting, include the partition column; missing it causes errors.</li> <li><strong>Overwriting small files</strong>: DuckLake handles small updates, but avoid frequent tiny inserts—compact periodically with <code>OPTIMIZE TABLE sales;</code>.</li> <li><strong>Ignoring sorting</strong>: Define a sort column to speed up range queries; otherwise full scans occur.</li> </ul> <h2 id="summary">Summary</h2> <p>DuckLake 1.0 simplifies data lake management by storing metadata in SQL, enabling faster updates and smarter partitioning. With its DuckDB extension, you get a lightweight yet powerful alternative to Hive or Iceberg for analytical workloads. Start small, tune your partitions, and enjoy seamless SQL-driven data lakes.</p>