Getting Started

Prerequisites

• DuckDB v2.0.0. On DuckDB v1.5.6, INSTALL gives v0.1.2 of this extension instead (Versions).
• The mssql extension v0.3.0 or newer, which is the mssql build for DuckDB 2.0. INSTALL mssql FROM community installs the right one for your DuckDB, and SELECT mssql_version() shows which one is loaded.
  • Since v0.3.0 the mssql extension verifies the server's TLS certificate by default.
  • A server on a self-signed certificate, such as a local Docker one, needs TrustServerCertificate=yes in the connection string.
• SQL Server 2019 or later, or Azure SQL Database — the catalog's strings need a UTF-8 collation (Requirements).
• A place for the data files: a local directory, S3, Azure Blob Storage — anything DuckDB can write to. DuckLake puts only the catalog in SQL Server.

Step 1: install and load

INSTALL mssql FROM community;
INSTALL mssql_ducklake FROM community;
LOAD mssql_ducklake;          -- loads mssql as well, and refuses if stock ducklake is already loaded

Two rules that come from embedding DuckLake:

• Never together with the stock ducklake extension. Both provide the same functions and the same ducklake: attach prefix; LOAD mssql_ducklake refuses with a clear message when stock ducklake is loaded.
• Load before the first ATTACH 'ducklake:…'. DuckDB autoloads an extension by attach prefix, and for ducklake: that is the stock one; once mssql_ducklake is loaded the prefix is taken.

Step 2: attach

ATTACH 'ducklake:mssql:Server=localhost,1433;Database=lake_meta;User Id=sa;Password=…' AS lake
    (DATA_PATH '/data/lake/');

ducklake:mssql: is followed by an ADO-style connection string, exactly what the mssql extension takes. The URI form works too, with the type spelled out — DuckDB deliberately does not treat mssql:// as a prefix:

ATTACH 'ducklake:mssql://sa:…@localhost:1433/lake_meta' AS lake
    (DATA_PATH 's3://my-bucket/lake/', META_TYPE 'mssql');

The first attach creates the catalog: DuckLake's tables in the database's dbo schema, then the keys, collations and indexes this extension adds (Shaping). Every later attach finds it and opens it. METADATA_SCHEMA puts the catalog in another schema; the other attach options are DuckLake's own.

Step 3: use it

CREATE SCHEMA lake.sales;
CREATE TABLE lake.sales.orders(id BIGINT, customer VARCHAR, amount DECIMAL(18, 2), day DATE);

INSERT INTO lake.sales.orders VALUES (1, 'acme', 10.5, DATE '2026-01-01');       -- inlined into the catalog
INSERT INTO lake.sales.orders
    SELECT i, 'c' || i, i * 1.5, DATE '2026-01-01' + i FROM range(10000) t(i);   -- a parquet file under DATA_PATH

UPDATE lake.sales.orders SET amount = amount * 2 WHERE id = 1;
DELETE FROM lake.sales.orders WHERE id > 9000;

SELECT * FROM ducklake_snapshots('lake');
SELECT count(*) FROM lake.sales.orders AT (VERSION => 2);
SELECT * FROM ducklake_table_info('lake');

Small inserts (10 rows by default) are inlined into the catalog — they land in a ducklake_inlined_data_* table in SQL Server — and larger ones go to Parquet files under DATA_PATH; ducklake_flush_inlined_data('lake') moves inlined rows to files. See Writing for what is specific to SQL Server on the write side.

Step 4: look at what landed

Attach the catalog database with the mssql extension directly and the catalog is ordinary tables:

ATTACH 'Server=localhost,1433;Database=lake_meta;User Id=sa;Password=…' AS meta (TYPE mssql);
SELECT table_name FROM meta.dbo.ducklake_table;
FROM mssql_scan('meta', 'SELECT name FROM sys.tables WHERE name LIKE ''ducklake%'' ORDER BY name');

Next: the catalog in SQL Server, performance, and the settings.