<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Vladimir Gribanov on Medium]]></title>
        <description><![CDATA[Stories by Vladimir Gribanov on Medium]]></description>
        <link>https://medium.com/@gribanov.vladimir?source=rss-670032929ddc------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*SwZ5PkUg-ur9GBCL.jpg</url>
            <title>Stories by Vladimir Gribanov on Medium</title>
            <link>https://medium.com/@gribanov.vladimir?source=rss-670032929ddc------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 19:04:03 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@gribanov.vladimir/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[One login, your role’s secrets: DuckDB with tresor]]></title>
            <link>https://medium.com/@gribanov.vladimir/one-login-your-roles-secrets-duckdb-with-tresor-1fb5a05447c5?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/1fb5a05447c5</guid>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[oidc]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Tue, 27 Oct 2026 02:01:02 GMT</pubDate>
            <atom:updated>2026-10-27T02:01:02.595Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_yED5Q5XaG8-zGYzt6whMA.png" /></figure><h3>The problem</h3><p>DuckDB went from a laptop toy to the engine behind notebooks, BI extracts, dbt models and data APIs. Its secrets did not grow with it. CREATE SECRET wants the key itself, so the key ends up where DuckDB runs: in a notebook cell, a .env, a CI variable, a container image. Every copy is one more place to rotate, one more place to leak, and nobody can say who used which credential last Tuesday.</p><p>The rest of the stack solved this long ago: people log in with the company identity, services use workload identities, and a vault hands out short-lived credentials by role. DuckDB just had no way to plug into it.</p><h3>What tresor does</h3><p>tresor is a DuckDB extension (MIT) that makes an <strong>external secrets service</strong> part of DuckDB’s own secret lookup:</p><pre>ATTACH &#39;tresor:secrets.corp.example&#39; AS corp;   -- log in once: your company&#39;s identity provider<br>FROM &#39;s3://lake/sales/*.parquet&#39;;                -- the right S3 secret is found, as any DuckDB secret</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ela0xUI2z2hK9cL7VCClJg.png" /></figure><ul><li><strong>You log in with your identity provider directly</strong> — browser or device code, once; the login is remembered in the OS keychain (single sign-on across DuckDB processes). tresor never sends your IdP token anywhere but its audience.</li><li><strong>The secrets your roles may use appear in DuckDB’s lookup</strong>, matched by scope like any secret. Nothing is written to disk; material is fetched when a query needs it and kept in memory briefly.</li><li><strong>Administrators manage, roles use.</strong> Secrets are created in the service and granted to roles and groups — from DuckDB itself (CREATE PERSISTENT SECRET … IN corp, grant_secret) or the service&#39;s console.</li></ul><p>The service behind it speaks an open protocol, duckdb-secrets/1 — a short HTTP spec. <strong>tresor-server</strong> is our production implementation; anything that implements the spec works.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*aJjAPm5WQ20oDIzza-90IA.png" /></figure><h3>Who uses it, and how</h3><h4>An analyst</h4><p>Install once, attach, work. No keys, no tickets for a new bucket — the admin grants the role, the next query sees it.</p><pre>INSTALL tresor FROM community;          -- DuckDB 2.0<br>ATTACH &#39;tresor:secrets.corp.example&#39; AS corp;<br>FROM corp.secrets();                     -- what my roles may use<br>FROM &#39;s3://lake/sales/*.parquet&#39;;</pre><p>From DuckDB UI, Jupyter or DBeaver, where nobody sees a terminal: FROM tresor_login(&#39;tresor:…&#39;) returns the link and the code as a row; with login := &#39;device&#39; it is a short link and a code, which read best in a result grid.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*IVoGFfEmjoJYBvnBEHND7g.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*w7st_OBGDogaaZUpTKWNAw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/618/1*6pBWJvObZLC5oRoD8KnE0g.png" /></figure><p>→ docs: <a href="https://hugr-lab.com/docs/tresor/clients/"><em>From DuckDB UI, Python and IDEs</em>.</a></p><h4>An administrator</h4><pre>CREATE PERSISTENT SECRET lake IN corp (TYPE s3, KEY_ID &#39;…&#39;, SECRET &#39;…&#39;, REGION &#39;eu-west-1&#39;,<br>                                       SCOPE &#39;s3://lake&#39;);<br>CALL corp.grant_secret(&#39;lake&#39;, &#39;role:analysts&#39;, [&#39;use&#39;]);</pre><p>Better still, the material never touches DuckDB: the service holds a <strong>reference</strong> to your vault (ref+vault://…, ref+azkv://…, AWS/GCP secret managers) and resolves it per read.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-6iHlbh-vkgbx3Q7PV14vA.png" /><figcaption>the key referenced in Vault, the grant, and the admin holding no use</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tvYzUPZ1_1GxPa8157w1Lw.png" /><figcaption>the same secret in the web console</figcaption></figure><h4>A pipeline</h4><p>A job logs in as <strong>its own identity</strong>, never as a person: GitHub Actions’ OIDC token, a Kubernetes service account, an Azure managed identity — no stored password at all. dbt-duckdb works through its profile.</p><pre>CREATE SECRET ci (TYPE tresor, SCOPE &#39;tresor:secrets.corp.example&#39;, FLOW &#39;federated&#39;,<br>                  ASSERTION_SOURCE &#39;github_actions&#39;, CLIENT_ID &#39;…&#39;);<br>ATTACH &#39;tresor:secrets.corp.example&#39; AS corp;</pre><p>→ docs: <a href="https://hugr-lab.com/docs/tresor/pipelines/"><em>Pipelines</em></a> (a recipe per executor).</p><h4>Your own application, and the browser</h4><p>DuckDB is a library first: Python, Go, Rust, .NET and Java applications embed it, and DuckDB-wasm runs it inside a web page. tresor works the same in all of them, and an application that already logs its users in (MSAL, oidc-client-ts, any OIDC library) does not make them log in again. It hands tresor the access token it holds for the secrets service, and each renewed one:</p><pre>con.execute(&quot;LOAD tresor&quot;)<br>con.execute(&quot;FROM tresor_web_token(?, ?)&quot;, [service, token])   # the app&#39;s own token, a parameter<br>con.execute(f&quot;ATTACH &#39;{service}&#39; AS corp&quot;)</pre><p>The same three lines in Go, Rust and .NET; in the browser the helper attachTresor adds the extension&#39;s signature check and hands over each silent renewal. tresor keeps the token in memory, never sees the app&#39;s refresh token, refuses a token meant for another audience, and when another user&#39;s token arrives it drops everything cached for the previous one.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4gh2dytij8-9tIP4--P-Og.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DyH66mLUvXjHrJ5fB8PGig.png" /><figcaption>the same flow from Python, Go and Rust</figcaption></figure><p>→ docs: <a href="https://hugr-lab.com/docs/tresor/embedding/"><em>In your application</em></a>, <a href="https://hugr-lab.com/docs/tresor/web/"><em>In a web application</em></a>.</p><h4>A server that serves many users</h4><p>A DuckDB server (with <strong>duckdb-acl</strong>) runs every user’s query with <em>that user’s</em> rights: tresor exchanges the user’s session for a delegation, and the secrets served are the ones the service allows for the user’s statement — not the server’s own bag of keys. Every use is audited with the user’s name.</p><h3>Short-lived by design: dynamic secrets</h3><p>A secret’s parameter can be <strong>generated per caller</strong>: an Azure token for the user via token exchange, an S3 credential minted for this session, a claim of the user’s token — and tresor caches it only until shortly before it expires. httpfs refreshes S3 credentials on its own when they run out. Variables work the same way, so even a tool that reads a <em>setting</em> gets a per-user token:</p><pre>LOAD motherduck;<br>SET motherduck_token = corp.variable(&#39;md_token&#39;);   -- minted for you by the service</pre><h3>Deploying tresor-server</h3><p>One container, stateless apart from its store; pick your cloud:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/874/1*y4zvfPIhLSFehunbtfm5sQ.png" /></figure><p>What you decide, not what you type:</p><ol><li><strong>The identity provider</strong>: register the service’s API and the DuckDB client app (Entra, Keycloak, Okta…); map a claim to roles.</li><li><strong>Who are admins</strong>: a role (policy.admins).</li><li><strong>Where material lives</strong>: sealed in the service’s store under your KMS key, or referenced in your vault.</li><li><strong>Network</strong>: the service is an HTTPS endpoint inside your network; DuckDB reaches it and the IdP. A private CA? SET ca_cert_file. A locked-down DuckDB (enable_external_access = false)? Allow the two hosts.</li></ol><p>→ tresor-server docs: getting started (a laptop in 5 minutes), configuration, each cloud.</p><h3>Security in one paragraph</h3><p>No secret material on disk on the client; nothing logged; your IdP token goes only to its audience; roles get use only by explicit grant (an admin role implies none); a server acting for users never uses its own identity for a user&#39;s query; every lookup is audited and exportable over OpenTelemetry. The protocol is version 1, normative, open.</p><h3>Try it</h3><ul><li>Info: <a href="https://hugr-lab.com/tresor">https://hugr-lab.com/tresor</a></li><li>5 minutes on a laptop: Keycloak in docker + tresor-server + DuckDB → tresor-server <a href="https://hugr-lab.com/docs/tresor-server/getting-started/"><em>Getting started</em></a>.</li><li>Docs: <a href="https://hugr-lab.github.io/tresor/">https://hugr-lab.com/docs/tresor/</a> · Protocol: <a href="https://hugr-lab.com/docs/tresor/protocol/">https://hugr-lab.com/docs/tresor/protocol/</a>· tresor-server: <a href="https://hugr-lab.com/docs/tresor-server/">https://hugr-lab.com/docs/tresor-server/</a></li><li>tresor is MIT; tresor-server is BUSL (free for your own production use; not to be offered as a service to others).</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1fb5a05447c5" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Same Hardware, Two Catalogs: DuckLake on SQL Server and PostgreSQL, Measured in Azure]]></title>
            <link>https://medium.com/@gribanov.vladimir/same-hardware-two-catalogs-ducklake-on-sql-server-and-postgresql-measured-in-azure-b31788dad4af?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/b31788dad4af</guid>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[hugr]]></category>
            <category><![CDATA[ducklake]]></category>
            <category><![CDATA[data-lake]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Thu, 15 Oct 2026 20:30:11 GMT</pubDate>
            <atom:updated>2026-10-15T20:30:11.343Z</atom:updated>
            <content:encoded><![CDATA[<p><em>Two identical VMs, one workload, three runs with the order alternated: a DuckLake catalog in SQL Server finished in 63% of the time the same catalog took in PostgreSQL. Here is how it was measured, what changed in the extension to get there, and what broke along the way.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DH-qXvxuFiI_tuK_VuMqrg.png" /></figure><p><a href="https://ducklake.select/">DuckLake</a> keeps a lakehouse’s catalog in a database rather than in files beside the data. That catalog holds snapshots, data files, statistics and schema versions. <a href="https://github.com/hugr-lab/mssql-ducklake">mssql_ducklake</a> is a DuckDB extension that puts that catalog in Microsoft SQL Server, next to DuckLake’s built-in managers for DuckDB, PostgreSQL and SQLite.</p><p>When we released v0.1.0, <a href="https://medium.com/medium-sql-server-lakehouse-catalog.md">the article that came with it</a> ended on an honest number. On a laptop, with both servers in Docker, the SQL Server catalog took <strong>1.54×</strong> the PostgreSQL catalog’s time on our 1000-table benchmark. Commits were at parity and compaction was faster, but the whole run was slower.</p><p>This article is the sequel, with two changes to how the number was produced:</p><ul><li>The extension has moved to the DuckDB 2.0 line, and its catalog path has been rebuilt twice since.</li><li>The measurement left the laptop. Each server ran on its own identical VM in Azure, with the client on a third machine beside them. The data sat in Azure storage, as it would in a real lake.</li></ul><h3>The setup</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OZFQABToqF9m7Dvv3zeuww.png" /></figure><p><strong>Both servers were tuned.</strong> SQL Server sizes its memory itself. PostgreSQL’s defaults (shared_buffers=128MB) would have compared a tuned server with an untuned one, so PostgreSQL got the settings any DBA would give a 12 GB, 4-core box.</p><p><strong>The client</strong> was DuckDB with the extension, running as an Azure Container Apps job on a dedicated 4-vCPU profile. It shared a VNet with the two servers, and the servers had no public IP. Both lakes wrote their Parquet to one storage account, on an Azure Files share. The findings below explain why it was a share and not Blob.</p><p><strong>The image</strong> was built by a GitHub Actions workflow and pulled from GHCR, so no paid build agent was involved. Everything lived in one resource group with a budget alert, and the last step of the run deleted that group. That step ran inside Azure, from the job’s own managed identity, so the run finished and cleaned up with the laptop offline.</p><p>The whole thing cost about <strong>$7</strong>. The scripts are in the repository under <a href="https://github.com/hugr-lab/mssql-ducklake/tree/main/scripts/bench/azure">scripts/bench/azure/</a>: up.sh, run.sh and down.sh.</p><h3>The method</h3><p>The benchmark is the one from the first article, at <strong>500 tables</strong> of 41 columns across 10 schemas. The findings below explain why not 1000. Each phase is a single statement, run once:</p><ul><li>create the tables;</li><li>the first insert into each table (inlined into the catalog);</li><li>the second insert (a Parquet file);</li><li>flush the inlined data;</li><li>compact;</li><li>partitioned tables;</li><li>three tables taking 1000 commits each;</li><li>100 schema changes on one table;</li><li>reads at old snapshots;</li><li>re-attaches.</li></ul><p>It ran <strong>three times</strong>, with the order of the backends alternated: SQL Server first, then PostgreSQL first, then SQL Server first. Every number below is the median, with the range of the three runs in brackets. Before any of it, a parity check asked each server what it actually had: CPU count, memory target, version and settings.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F7-H8zzvIR1DlrhjQR91lw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PmYL5on9R0QIbEiGVtHaIg.png" /></figure><p><strong>Where the time goes into writing Parquet, the two are even:</strong> compaction rewrites files, and partitioned commits write one file per partition. That is as it should be, because the data path is identical: the same DuckDB writes the same files to the same share.</p><p><strong>A few small reads went the other way</strong>, such as re-attaching and listing table info. They take tens of milliseconds, and PostgreSQL is a little quicker there.</p><h4>Concurrency</h4><p>Separate writers committed to one catalog at once, with 1, 4 and 16 threads, 20 operations each. The scenarios were own tables with inlined rows, one shared table with Parquet writes, and writers mixed with readers.</p><ul><li><strong>SQL Server</strong> completed all 12 configurations without losing a commit.</li><li><strong>PostgreSQL</strong> failed two of them, both at 16 writers. Commits ended in Failed to commit DuckLake once their retries ran out.</li><li><strong>Mixed load with 16 threads:</strong> SQL Server ran 19.9 operations a second, PostgreSQL 15.7.</li></ul><p>This was one run, so treat the throughput as indicative. The commits that were lost are not something a rerun would explain away.</p><h3>What changed since 1.54×</h3><p>There was no single fix. There were three, each measured on its own before it was kept.</p><p><strong>1. The commit batch is our T-SQL.</strong> This was already in v0.1.0. DuckLake’s commit is a closed list of statement shapes; we recognise each one exactly and send it as one T-SQL batch, so there is no transpiler. That took a commit from 25 ms to 11 ms, and it is why the commits started at parity.</p><p><strong>2. The stats of every table, cached.</strong> On the DuckDB 2.0 line, DuckLake reads every table’s global statistics once per snapshot, and every commit is a new snapshot. So every commit read 1000 tables × 41 columns of statistics.</p><p>The manager now keeps those statistics per attached catalog. It brings them up to date from ducklake_snapshot_changes, rereading only the tables that changed since, whichever node made the change. The read per commit went from 16–30 ms back to about 1 ms.</p><p><strong>3. The catalog, loaded in one statement, then kept.</strong> This was the big one. Whenever its schema version moves, DuckLake rebuilds its in-memory catalog: after every DDL, after a table’s first inlined insert, and, in a flush or a compaction, once per table, at the schema version that table was written at. Its own cache keeps only the newest version, so a flush of 1000 tables rebuilt the whole catalog 1000 times. Each rebuild was seven queries through DuckDB’s catalog path, with the joins on the client and a round trip per sub-scan.</p><p><em>Phase A</em> made a rebuild one statement: a UNION ALL with one branch per catalog table in a single tagged row shape, and the joins done on the client. Two details mattered more than the rewrite itself:</p><ul><li><strong>The NULL placeholders.</strong> A UNION column takes the widest type of its branches. A CAST(NULL AS VARCHAR(MAX)) placeholder therefore made every string column MAX, at 26 ms of server CPU per load. VARCHAR(1) placeholders brought it to 14 ms.</li><li><strong>The </strong><strong>ORDER BY.</strong> Sorting every row with its wide strings on the server cost most of what was left. Without it, a load was 9 ms, and the client sorts a few thousand rows for free.</li></ul><p><em>Phase B</em> keeps the rows. Each attached catalog holds every row of its history in memory and catches up with one small statement: the rows that began or ended after the last snapshot it has seen. A flush asks for old schema versions, which are already there.</p><p>Other nodes cannot make the copy wrong, for three reasons:</p><ul><li><strong>Committed history does not change.</strong> A row’s begin snapshot is written once, and its end snapshot is set once.</li><li><strong>Snapshot ids are gap-free.</strong> A commit takes the next id under a primary key, so once a snapshot is visible as committed, everything before it is too.</li><li><strong>The mirror only answers for snapshots it has caught up to.</strong> A node that is behind pays one small query; it never returns a wrong answer.</li></ul><p>The design missed one exception: the inlined-data tables are registered outside the commit, without a snapshot. A verification switch caught it on the first run. That switch answers every load both ways, from the mirror and from DuckLake’s own seven queries, and fails on the first field that differs. Every load is now checked against a fingerprint of that table, at the cost of one tiny query.</p><p>On the laptop, at 1000 tables, the three steps took the benchmark from 650 s to 599 s (phase A) and then to 420 s (phase B). The flush and compactions became 32% faster again on top of phase A.</p><h3>What broke along the way</h3><p>Bench runs are where assumptions go to die. Five of ours did.</p><p><strong>DuckLake’s flush needs memory that grows about quadratically with the number of tables.</strong> The first runs at 1000 tables were OOM-killed in the flush, on 12 and then 14 GiB, on Blob and on Azure Files alike. Measured locally, the peak was 973 MB at 150 tables and 3024 MB at 300. That is the same with our catalog mirror as without it, and the PostgreSQL catalog pays it too. The likely cause is that the flush keeps a full catalog pinned for each schema version it flushes, and each of those catalogs holds every table. Extrapolated, 1000 tables needs 20–30 GB, which is why this benchmark ran at 500. It is upstream’s to fix, and we will report it.</p><p><strong>The azure extension’s credential chain does not find a user-assigned managed identity.</strong> CHAIN &#39;default&#39; builds DefaultAzureCredential with default options, which ignores AZURE_CLIENT_ID. PROVIDER managed_identity, CLIENT_ID &#39;…&#39; works.</p><p><strong>Blob storage is slow for this benchmark’s writes.</strong> The benchmark writes thousands of tiny files, one per commit. On Blob, 3000 commits into 3 tables took 370 s at 200 tables. The data path is the same for both catalogs, so it would not have changed the comparison. It would have stretched every run, and a lake that commits a few rows at a time is the case DuckLake’s inlining exists for.</p><p><strong>az containerapp job start --args --tables 1000 treats </strong><strong>--tables as its own option.</strong> The bench now starts executions through the REST API, with the container spelled out.</p><p><strong>A new subscription meets quotas before it meets problems.</strong> westeurope refused new resources, and swedencentral had no vCPU quota for the Dsv5 family but room for Dsv6. Hence the v6 VMs, which also turned out cheaper.</p><h3>What this does not show</h3><ul><li><strong>The DuckDB 2.0 line is not released yet.</strong> The build is a development commit of DuckDB with DuckLake’s main branch, pinned. The numbers will move with the release, in both directions.</li><li><strong>One region, one VM size, one run of the concurrency test.</strong></li><li><strong>Self-managed servers, not managed services.</strong> Azure SQL Database and Azure Database for PostgreSQL are not sized alike: General Purpose with 4 vCores gives about 20 GB against 16 GB. That comparison is scripted (BENCH_MANAGED=1) but was not run.</li><li><strong>500 tables, not 1000.</strong> The reason is DuckLake’s flush, not either catalog.</li><li><strong>SQL Server Developer edition.</strong> It is the Enterprise engine under a non-production license; nothing here depends on an edition feature.</li></ul><h3>Reproduce it</h3><pre>git push origin bench/&lt;name&gt;                        # GitHub Actions builds the image to GHCR<br>BENCH_REF=&lt;commit&gt; scripts/bench/azure/up.sh        # ~25 min: VNet, two VMs, storage, Container Apps<br>scripts/bench/azure/run.sh info                     # what each server actually has<br>scripts/bench/azure/run.sh scale --tables 500       # three times, alternating --backends<br>scripts/bench/azure/run.sh concurrent --threads 1,4,16<br>scripts/bench/azure/down.sh                         # deletes the resource group</pre><p>Every number in this article traces to a run in <a href="https://github.com/hugr-lab/mssql-ducklake/blob/main/specs/021-azure-bench/spec.md">specs/021</a>, and every change to a measured spec: <a href="https://github.com/hugr-lab/mssql-ducklake/blob/main/specs/019-global-stats-cache/spec.md">019</a> (the stats cache) and <a href="https://github.com/hugr-lab/mssql-ducklake/blob/main/specs/020-catalog-load/spec.md">020</a> (the catalog load and its mirror).</p><p><a href="https://hugr-lab.github.io/mssql-extension/next/">mssql-extension</a> and <a href="https://hugr-lab.github.io/mssql-ducklake/next/">mssql-ducklake</a> for DuckDB 2.0 ships with the DuckDB 2.0 release. v0.1.1 is on the community repository for DuckDB 1.5.6:</p><pre>INSTALL mssql FROM community;<br>INSTALL mssql_ducklake FROM community;<br>LOAD mssql_ducklake;<br>ATTACH &#39;ducklake:mssql:Server=host,1433;Database=lake_meta;User Id=…;Password=…&#39; AS lake (DATA_PATH &#39;az://container/lake/&#39;);</pre><p><em>Built for </em><a href="https://hugr-lab.github.io/"><em>hugr</em></a><em>, an open-source data mesh platform; works standalone with any DuckDB.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b31788dad4af" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What SQL Server Wants From a Lakehouse Catalog: Seven Measurements From Putting DuckLake On It]]></title>
            <link>https://medium.com/@gribanov.vladimir/what-sql-server-wants-from-a-lakehouse-catalog-seven-measurements-from-putting-ducklake-on-it-999838c954c8?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/999838c954c8</guid>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[microsoft-sqlserver]]></category>
            <category><![CDATA[ducklake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Fri, 11 Sep 2026 13:44:38 GMT</pubDate>
            <atom:updated>2026-09-11T13:44:38.002Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_ujXVB9Tp93477j0xPM3hw.png" /></figure><p><a href="https://ducklake.select/">DuckLake</a> is a lakehouse format with an unusual premise: the catalog is not a pile of JSON manifests in object storage but ordinary tables in a database — snapshots, files, statistics, schema versions — and the data is Parquet wherever you keep it. DuckDB ships managers for three catalog databases: DuckDB itself, PostgreSQL and SQLite.</p><p>We added a fourth, <strong>Microsoft SQL Server</strong> (and Azure SQL), in a DuckDB extension called <a href="https://github.com/hugr-lab/mssql-ducklake">mssql_ducklake</a>. It embeds the complete, unmodified DuckLake source at a pinned release and registers a SQL Server metadata manager beside the built-in ones; the manager talks to the server through the <a href="https://hugr-lab.github.io/mssql-extension/">mssql extension</a> — native TDS, no ODBC. The pitch is simple: teams whose transactional data, permissions and DBAs already live in SQL Server get a lakehouse catalog in the same place.</p><figure><img alt="Inside the DuckDB process, the mssql_ducklake extension holds DuckLake and the SQL Server metadata manager; the manager talks through the MSSQL extension over TDS to the metadata catalog in SQL Server or Azure SQL, while Parquet data is read and written in object storage — Azure Blob or S3" src="https://cdn-images-1.medium.com/max/1024/1*8SPib9FbCGIiJhqkKDBqdQ.png" /></figure><p>The first version worked in a day. Making it <em>*not slower than PostgreSQL*</em> took the rest of the project, and every step was a measurement that surprised us. This article is those measurements. Most of them are not about DuckLake at all — they are about what SQL Server does with the SQL a client sends it, and they apply to any tool that sends SQL Server statements with literals in them.</p><h3><strong>The fixture</strong></h3><p>One benchmark, run the same way every time: a catalog of <strong>**1000 tables**</strong> with 40 columns each across 10 schemas, 20 partitioned tables with 25 partitions, three tables with 1000 snapshots, one table with 100 schema versions. Every phase — create the tables, the first insert into each (inlined), the second (a Parquet file), re-attach, reads at old snapshots, flushes, compactions, expiry — is one statement, run once, cold. The PostgreSQL backend runs the identical workload on the same machine (SQL Server 2025 and PostgreSQL 18 in Docker), with the arms alternated so neither warms the other.</p><p>The absolute numbers are conservative — client and both servers on one laptop, SQL Server in a 2 GB VM. The <em>*ratios*</em> are the story, and the story in one line:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/524/1*5X3cWlBAiRlUX0KGEMc_ng.png" /></figure><p>Where the remaining 1.54× lives is at the end. Here is how the 2.32× became it.</p><h3>1. SQL Server caches plans by text, and your literals are the text</h3><p>The finding that changed the most was found last, by accident, while chasing something else.</p><p>Every first write into a table cost 57 ms; every later write into the same table, 37 ms. We assumed the difference was the mssql extension loading the new table’s metadata — a schema listing, a column query. MSSQL_DEBUG=2 showed exactly one new thing on the first write: a single-table metadata query, <strong>40 ms</strong>. The same query for a table the session had already seen: <strong>1 ms</strong>.</p><p>Not the table — the <em>text</em>. SQL Server keeps compiled plans keyed by the statement’s text (and a few session options). The extension’s metadata query puts the table name into the text as a literal (WHERE o.object_id = OBJECT_ID(&#39;[dbo].[ducklake_inlined_data_1053_1206]&#39;)), so every table is a different statement, and every different statement is compiled on first use. A join over five catalog views compiles in about 30 ms. We proved it four ways:</p><ul><li>sys.dm_exec_query_optimizer_info: the optimizations counter goes up by one on the first execution of a text and by zero on the second.</li><li>SET STATISTICS TIME ON: <em>parse and compile time 28 ms</em>, execution 1 ms.</li><li>The same query through sp_executesql with the name as a parameter: compiled once, then 0–1 ms for every other table.</li><li>ALTER DATABASE … SET PARAMETERIZATION FORCED, with the extension unchanged: 38 ms for the first table, 1 ms for the next two.</li></ul><p>Why every session paid it again: our test server’s entire plan cache held <strong>53 plans</strong>. At 611 MB of server memory the cache thrashes, and per-table plans do not survive to the next session. A larger server keeps them longer — but with optimize for ad hoc workloads on, which is common in production, the first execution stores a stub and the <em>second</em> compiles again, so a touch-each-table-once workload compiles for every table regardless.</p><p>Then the real surprise. We set PARAMETERIZATION FORCED on the catalog&#39;s database and ran the whole benchmark: <strong>937 s → 686 s</strong>. The extension&#39;s two per-table queries could not account for that. DuckLake&#39;s own queries could: WHERE table_id = 1053, WHERE snapshot_id = 6411, the inlined-table names — every one of them a distinct ad-hoc text, every one compiled on first use. Forced parameterization turns all of them into one plan per shape.</p><p>The extension now applies that option when it creates a catalog (best-effort — a login that may create tables but not alter the database gets a working catalog and a logged hint). If you run <em>anything</em> that sends SQL Server literal-carrying statements — an ORM, a BI tool, a hand-rolled ETL — check is_parameterization_forced in sys.databases for that database and think about it.</p><h3>2. Collation is correctness, not tuning</h3><p>DuckLake reads a table by first asking the catalog which files can contain matching rows: it keeps per-file, per-column minimum and maximum values and prunes with min_value &lt;= x AND max_value &gt;= x. Those values are strings — DuckLake stores every type&#39;s statistics as text in DuckDB&#39;s canonical form — and when a filter is pushed down to SQL Server, the <em>server</em> compares them, under the column&#39;s collation.</p><p>A database’s default collation is usually a linguistic one (SQL_Latin1_General_CP1_CI_AS). Under it, &#39;a&#39; &lt; &#39;B&#39; and &#39;e&#39; = &#39;E&#39;. DuckDB computed those statistics in byte order, where &#39;B&#39; &lt; &#39;a&#39; and no two distinct strings are equal. A filter answered under the wrong order prunes the wrong files and <strong>drops rows from the result</strong> — silently.</p><p>So the catalog stores every string as VARCHAR(MAX) COLLATE Latin1_General_100_BIN2_UTF8: UTF-8, so nothing is transcoded from what DuckDB writes, and binary, so the server&#39;s order is DuckDB&#39;s. Never NVARCHAR. This is why the extension needs SQL Server 2019 or later (UTF-8 collations arrived there), and it checks for the collation before it creates anything.</p><p>One consequence for the write side: a plain &#39;…&#39; literal in a T-SQL batch is decoded in the <em>database&#39;s</em> code page before it reaches a UTF-8 column, so every string literal the manager sends is N&#39;…&#39; — UTF-16 on the wire, converted by the server, no legacy code page in between.</p><h3>3. The engine needs keys the data does not</h3><p>DuckDB’s write path against an attached SQL Server table goes through the mssql extension’s DML operators, and the extension builds a row’s identity out of the table’s primary key. Without one, UPDATE and DELETE are refused. DuckLake&#39;s catalog DDL declares primary keys on a few tables and none on the ones it updates most — ducklake_table_stats on every write past a table&#39;s first, a dozen tables on expiry and cleanup.</p><p>So the manager <em>shapes</em> the catalog after DuckLake creates it: a primary key on every table DuckLake updates or deletes from, key columns made NOT NULL, the name-keyed tables (ducklake_metadata, the tag tables) given a VARCHAR(200) key inside SQL Server&#39;s 900-byte index limit. The shaping is idempotent and stamped with a version, so a catalog created by an older build is brought up to shape on its next attach.</p><p>The keys had one blind spot we found only when we captured every write DuckLake can make (section 5): the table that holds inlined <em>deletions</em> of file-backed rows is created by DuckLake with a CREATE TABLE written <em>into the commit batch</em> — by a static function the manager cannot override — and a flush later DELETEs from it. Keyless, the flush failed. The manager now takes that one statement out of the batch and creates the table itself, keyed. A test suite that never deleted from a file-backed table before a flush had never seen it.</p><h3>4. Indexes the other managers never add</h3><p>Neither the PostgreSQL nor the SQLite manager adds an index to the catalog. On SQL Server three of them measured:</p><p><strong>The visibility index.</strong> Almost every DuckLake read carries begin_snapshot &lt;= s AND (end_snapshot IS NULL OR end_snapshot &gt; s). The obvious index is a <em>filtered</em> one, WHERE end_snapshot IS NULL — small, serves the current state. It also serves nothing else: a read at an older snapshot wants rows whose end_snapshot is set, which the filter excludes, so it scans. Measured over 300,000 files across 1000 tables, half of them superseded:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/708/1*D4QMHX3Vzonb6O4PLR3_og.png" /></figure><p>The unfiltered one does what both do. Time travel is the obvious beneficiary; so is every maintenance function that walks history.</p><p><strong>The per-column statistics index.</strong> The largest table in the catalog holds a row per file per column, and a filtered read asks it WHERE column_id = ? AND table_id = ?. The primary key leads on data_file_id, which is not in that predicate. An index on (table_id, column_id): <strong>0.015 s → 0.001 s</strong>, over 1.23 million statistics rows. The trap when measuring it: with every row under one table_id, a scan is competitive and the index looks worthless; a real catalog spreads its rows over its tables.</p><p><strong>The partition index.</strong> Partition pruning is SELECT data_file_id FROM ducklake_file_partition_value WHERE table_id = ? AND partition_key_index = ? AND partition_value IN (…), the same leading-column mismatch. The value column had to be bounded to VARCHAR(200) to be a key at all — a MAX column is a LOB, and a LOB can be neither a key nor an INCLUDE.</p><h3>5. One round trip, without a transpiler</h3><p>After the keys, DuckLake’s commit ran — and cost 25 ms where PostgreSQL’s cost 9. A commit is one string of DuckDB SQL: an INSERT into ducklake_snapshot, the data-file rows, the statistics update, the schema-change rows, the changes log. Through an attached catalog, DuckDB runs it <em>statement by statement</em> through the extension&#39;s DML operators: ~19 round trips per commit, plus — the expensive one — the statistics update, which is UPDATE … FROM new_values WHERE table_id = tid AND column_id = cid, a join, so nothing is pushed down and every commit scans the whole ducklake_table_column_stats table: 41,800 rows on the 1000-table catalog, growing with it. PostgreSQL&#39;s manager hands the whole batch to postgres_execute in one call.</p><p>The obvious fix is a transpiler: parse DuckDB SQL, emit T-SQL. We had rejected that in the project’s first week — it grows with every DuckLake release and fails silently in the gap between what it handles and what it has not seen — and we did not build one now.</p><p>Instead we <strong>captured the batch</strong>. One session running every write DuckLake can make — every inlined type, inserts, UPDATE, DELETE, MERGE, every ALTER, renames, views, macros, tags, partitioning, sort keys, a multi-statement transaction, flush, expiry, compaction, cleanup, drops — with DuckLake&#39;s metadata log on, produced <strong>57 batches, 247 statements, 74 distinct shapes</strong>. All but one family are catalog statements over columns whose types are DuckLake&#39;s own DDL: INSERT … VALUES into a known table, UPDATE/DELETE with numeric predicates, four CTE updates, DROP TABLE IF EXISTS. The one exception is the INSERT of a user&#39;s inlined rows, whose values are the user&#39;s in every DuckDB literal form.</p><p>So the manager overrides the seam where the batch arrives and recognises each statement <strong>exactly</strong>: the fixed text of the family, its literals read by a tokenizer that knows five kinds (integer, decimal, &#39;string&#39;, NULL, true/false) and NOW(), the tuple width checked against the table&#39;s column list. A match becomes the manager&#39;s own T-SQL — N&#39;…&#39; strings, BIT for booleans, SYSDATETIMEOFFSET() for NOW(), DELETE t FROM x t for the alias form SQL Server refuses, VALUES behind a SELECT in a CTE. Contiguous matches go to the server in one mssql_exec on the transaction&#39;s connection. Anything that is not a match — the user&#39;s rows, a statement a future DuckLake writes — goes the ordinary way, in order. A statement matches to the character or it is not touched.</p><p>Three things only running it taught: key is a reserved word in T-SQL and a column name in DuckLake (bracketed now); SQL Server takes at most 1000 rows in one VALUES list, and a commit of 200 files writes 8000 statistics rows (chunked now, still one round trip); and DuckLake&#39;s expiry sends its DELETEs through a different entry point than the commit, which the first cut missed.</p><p>The guard is a switch the test suite runs with: an unrecognised catalog statement is an <em>error naming the statement</em> rather than a fallback, so a DuckLake release that adds a shape fails the suite instead of quietly slowing down. It earned its place on its first run — it named the tag statement with key in it.</p><p>The result: a file-backed commit <strong>25.5 ms → 11.4 ms</strong> (PostgreSQL 9.0); a thousand commits into one table 78 s → 35 s (30); and compaction across 1000 tables, which rewrites file metadata in bulk, <strong>34 s → 3.4 s</strong> — six times faster than PostgreSQL, whose manager runs that batch through a scanner. The benchmark: 698 s → 577 s.</p><p>We also measured the alternative for data-file rows — DuckDB’s appender, one DML round trip per table, versus putting them in the batch as literals: the appender won on every phase it touches (612 s vs 578 s overall with it off). Fewer round trips cost more than they saved in compiled literal statements; and the mssql extension’s bulk-load insert, coming with the DuckDB 2.0 line, makes the appender path BCP with no code on our side.</p><h3>6. Concurrency is a consistency question</h3><p>Several DuckDB processes writing to one catalog is supported: DuckLake allocates snapshot ids optimistically and retries a commit that lost the race. To decide, a retry asks one question:</p><pre>FROM ducklake_snapshot WHERE snapshot_id = (SELECT MAX(snapshot_id) FROM ducklake_snapshot)</pre><p>Through an attached catalog those are <strong>two</strong> SELECTs to the server, materialised independently on the same connection, with no consistent read between them. A commit landing in the gap makes them disagree — measured, the subquery came back with 13 snapshots while the outer scan had seen 12 — the predicate matches nothing, and DuckLake&#39;s parser reads a NULL snapshot id as an integer and crashes the writer. Over 14 rounds of four concurrent writers, <strong>six rounds lost a writer</strong>; the PostgreSQL backend, whose two scans share one transaction snapshot, lost none.</p><p>The fix is the same question as <strong>one statement</strong> (TOP 1 in a derived table, a JOIN, CAST(NULL AS …) for the union&#39;s types), which the server evaluates against one state. It also happens to be 14 batches instead of 25 on a retry — but that is not why it was written. A concurrency test with four writers now runs in CI, and it was made to fail first: an off switch for the rewrite exists so that the test can be shown to catch the crash.</p><h3>7. What is left, and why</h3><p>Honesty about the remainder is the most useful part of a benchmark page, so the docs carry it. At 1.54× overall:</p><ul><li><strong>The first inlined write into a table, 2.9×.</strong> The user’s rows still take DuckDB’s path (their literals are theirs), one boundary in the batch, and the table’s first touch loads its metadata.</li><li><strong>Commits of many files, 5×.</strong> The appender’s per-table inserts; the bulk-load insert on the 2.0 line is the cure.</li><li><strong>The attach, 0.8 s.</strong> Not the 2000-object schema listing — that is 39 ms. Three TDS logins at ~175 ms each; a warm pool is the lever.</li><li><strong>A table with 100 schema versions, 1.8×.</strong> DuckLake reads each version’s inlined data with the catalog as of that version: 155 full catalog loads for one read, 14 s here against 8 s on PostgreSQL. Upstream’s design; our lever is the price of one load.</li></ul><p>And one we could not close: on the Linux CI runner, a DELETE through the DML path on a string-keyed table failed intermittently with a DuckDB-side <em>invalid unicode</em> error — two runs out of three, never on macOS, never in isolation. The T-SQL path made it moot for the catalog and the report went to the extension; it is a reminder that a fallback path is only as good as the last time it ran.</p><h3>The checklist, for anyone sending SQL to SQL Server</h3><ol><li><strong>Look at </strong><strong>is_parameterization_forced</strong> for any database that receives literal-carrying statements from a tool. If the plan cache is small or optimize for ad hoc workloads is on, every distinct literal is a compile.</li><li><strong>Collate for correctness before speed.</strong> If a client computes statistics or sort keys in byte order, a linguistic collation on the server changes answers, not just timings.</li><li><strong>Know which engine builds the row identity.</strong> A tool that updates through DuckDB’s DML path needs a key on the table; the data model may not have one.</li><li><strong>Index the predicate, then measure both shapes</strong> — filtered indexes serve the current state and nothing else.</li><li><strong>Recognise before you translate.</strong> A closed list of statements, matched exactly, with a guard that fails loudly on a new one, beats a transpiler that fails quietly.</li><li><strong>Two reads on one connection are not one read.</strong> If a correctness decision needs a consistent view, ask it in one statement.</li></ol><p>mssql_ducklake v0.1.0 is <a href="https://github.com/hugr-lab/mssql-ducklake">on GitHub</a> with binaries for Linux, macOS and Windows, INSTALL mssql_ducklake FROM community on DuckDB 1.5.5, and <a href="https://hugr-lab.github.io/mssql-ducklake/">documentation</a> whose limitations page is the first thing to read. Every number above traces to a spec in the repository, with the script that produced it.</p><pre>INSTALL mssql FROM community;<br>INSTALL mssql_ducklake FROM community;<br>LOAD mssql_ducklake;<br>ATTACH &#39;ducklake:mssql:Server=host,1433;Database=lake_meta;User Id=…;Password=…&#39; AS lake (DATA_PATH &#39;s3://bucket/lake/&#39;);</pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=999838c954c8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The PLP Tax and Seven Other Levers: Optimizing Bulk Loads into SQL Server with DuckDB]]></title>
            <link>https://medium.com/@gribanov.vladimir/the-plp-tax-and-seven-other-levers-optimizing-bulk-loads-into-sql-server-with-duckdb-da549a338f3e?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/da549a338f3e</guid>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[bulk-loading]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Tue, 11 Aug 2026 11:18:30 GMT</pubDate>
            <atom:updated>2026-08-11T11:18:30.415Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/980/1*v2n0ZFBz1Ut-kUUOz5hocw.png" /></figure><p>Bulk-loading data into SQL Server looks like a solved problem: open a BCP stream, push rows, done. It isn’t. Between “it works” and “it’s fast” sit a half-dozen decisions — string typing, parallelism, target shape, lock hints, batch boundaries — and most of them fail silently: the load completes, just three to six times slower than it could.</p><p>While preparing the v0.2.3 release of the <a href="https://github.com/hugr-lab/mssql-extension">DuckDB MSSQL extension</a>, we measured all of them on one honest fixture: a 44-column, 38-million-row table with every type family SQL Server has — integers, decimals up to (38,10), datetime2 in several precisions, uniqueidentifier, varbinary, and nine string columns including pure-Cyrillic and pure-CJK ones. This article is the result: each lever, what it did on our table, and why.</p><p>All numbers are from the <a href="https://github.com/hugr-lab/mssql-extension/blob/main/test/bench/bench_results_v023_report.md">published benchmark report</a> — two interleaved rounds, wall-clock medians, reproducible harness in the repo. The absolute numbers are conservative (client and server shared one machine, the server emulated under Rosetta); the <em>*ratios*</em> are the story.</p><h3><strong>The baseline</strong></h3><pre>INSTALL mssql FROM community;<br>LOAD mssql;<br>ATTACH &#39;Server=myserver,1433;Database=Analytics;User Id=…;Password=…&#39; AS ms (TYPE mssql);<br>COPY (FROM read_parquet(&#39;events.parquet&#39;))<br>TO &#39;ms.dbo.Events&#39; (FORMAT bcp, CREATE_TABLE true, REPLACE true);</pre><p>One statement: DuckDB reads the parquet, the extension speaks the TDS `INSERT BULK` protocol — the same wire path the `bcp` utility and `SqlBulkCopy` use, with no ODBC or JDBC in between. On v0.2.2 this took <strong>933 seconds</strong> for our 38M rows. On v0.2.3, with everything below applied, the same data lands in <strong>96 seconds</strong>.</p><p>Here is where those seconds went.</p><h3>Lever 1: parallel bulk sessions (up to 4.5×)</h3><p>A single INSERT BULK stream is bounded by what one SQL Server session can ingest — not by the network, and (since v0.2.3&#39;s columnar encoder) not by the client either. In our runs the client sat at 7% CPU while the wall clock was dictated entirely by the server draining one session.</p><p>The fix is architectural, not incremental: open more sessions. SQL Server parallelizes ingest <em>across</em> sessions happily, so the extension splits the stream across independent bulk-load connections:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/242/1*XSSnbNKhINkmraGX_PnBrg.png" /></figure><pre>SET mssql_copy_parallel_writers = 8; -- default 0 = derive from threads, cap 8</pre><p>The default derives from DuckDB’s thread count, so out of the box you already get this. Two caveats worth knowing:</p><ul><li><strong>Inside an explicit transaction the setting is ignored.</strong> A transaction pins one connection; a second writer would sit outside it and its rows would not roll back with the rest. Bulk-load in autocommit when you can.</li><li>Narrow tables plateau earlier (our 15-column bench flattened at 4 writers; the 44-column one kept scaling to 8). More columns per row = more server-side work per row = more headroom for parallelism.</li></ul><h3>Lever 2: the PLP tax — size your strings (2.45×)</h3><p>This is the one to remember. When a string column has no declared length, it becomes NVARCHAR(MAX) on the server. MAX types are stored and shipped as PLP — <em>partially length-prefixed</em> LOB streams: every value carries an 8-byte total-length header plus 4-byte chunk framing, and the server routes it through the LOB write path instead of the in-row fast path.</p><p>On our table — nine string columns averaging 7–51 characters, none longer than 123 — the difference between NVARCHAR(MAX) and NVARCHAR(200) targets was:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/295/1*Ze1e9sSelJ1ZKPOMjWMlmg.png" /></figure><p>Same data. Same bytes on disk afterwards. <strong>2.45× faster</strong>, from framing alone.</p><p>Two ways to get it:</p><pre>-- global: every unannotated VARCHAR gets this length<br>SET mssql_default_string_length = 200;<br><br>-- per column: declared types travel into the CREATE TABLE<br>COPY (SELECT name::MSSQL_NVARCHAR(100) AS name, code::MSSQL_VARCHAR(16) AS code, ...)<br>TO &#39;ms.dbo.Events&#39; (FORMAT bcp, CREATE_TABLE true);</pre><p>Three things worth knowing about these numbers:</p><ul><li><strong>They are annotations, not constraints.</strong> MSSQL_NVARCHAR(n) and MSSQL_VARCHAR(n) bind to a plain DuckDB VARCHAR — nothing on the DuckDB side is bounded, and every string function keeps working. The number exists to shape the <em>SQL Server</em> target: it travels into the generated CREATE TABLE and into the client-side bound applied during the load.</li><li><strong>The unit is SQL Server’s own, and it is not “characters”.</strong> NVARCHAR(n) counts UTF-16 code units — an emoji is a surrogate pair, so it costs 2. VARCHAR(n) under a UTF-8 collation counts <strong>bytes</strong> — Cyrillic is 2 per character, CJK is 3, an emoji is 4. The same n in the two types does not hold the same data: VARCHAR(50) fits about 16 Cyrillic characters.</li><li><strong>An over-long value is truncated, not rejected</strong> — the same thing SQL Server’s own CONVERT does to a declared bound. The cut lands on a character boundary: a multi-byte UTF-8 sequence or a surrogate pair that doesn&#39;t fit whole is dropped whole, so you never get invalid UTF-8 or a lone surrogate the server would accept and every later read would report as damage.</li></ul><p>If your data genuinely holds multi-kilobyte strings — keep MAX; the tax buys you unbounded length. But pay it knowingly.</p><h3>Lever 3: VARCHAR vs NVARCHAR (−23% disk, same speed)</h3><p>NVARCHAR is UTF-16: two bytes per character for ASCII-heavy data. A VARCHAR column with a UTF-8 collation holds the same Unicode range at roughly half the bytes for Latin text. In v0.2.3:</p><pre>COPY (SELECT s::MSSQL_VARCHAR(200) AS s, ... ) TO &#39;ms.dbo.T&#39; (FORMAT bcp, CREATE_TABLE true);</pre><p>creates VARCHAR(200) COLLATE Latin1_General_100_BIN2_UTF8. On our table this cut the <em>stored</em> size from 23.1 GB to 17.8 GB (−23%). Wall time stayed ~92–96 s — at 4 writers the bound was no longer bytes — but on a real network, and for every scan that follows, those bytes are gone for good.</p><p>A word on that collation, because it governs every comparison ever run against the column, not just the load. The default (mssql_utf8_collation setting) is Latin1_General_100_BIN2_UTF8: binary ordering, case- and accent-<strong>sensitive</strong> — which is exactly how DuckDB and Postgres compare strings, and it is Microsoft Fabric Warehouse&#39;s own default. The flip side: WHERE name = &#39;abc&#39; no longer matches &#39;ABC&#39; on such a column. If your consumers expect SQL Server&#39;s classic linguistic matching, pick a CI collation instead — globally via the setting, or per column with the optional second argument:</p><pre>COPY (SELECT <br>  city::MSSQL_VARCHAR(100, &#39;Cyrillic_General_100_CI_AS_SC_UTF8&#39;) AS city, ...)<br>TO &#39;ms.dbo.Events&#39; (FORMAT bcp, CREATE_TABLE true);</pre><p>Setting mssql_utf8_collation to an empty string adds no COLLATE clause at all, so the column inherits the database default — the right call when that default is already UTF-8. A non-UTF-8 collation is accepted too, but it means what it says: the column stores that code page, and SQL Server silently replaces everything outside it with ? on insert. What the extension will never do is make that choice <em>for</em> you — an unannotated column gets a UTF-8 collation, never the database&#39;s code page. (NVARCHAR takes no collation argument: it stores UTF-16 and has no code page to get wrong.)</p><p>One honesty note: our Cyrillic and CJK columns stayed NVARCHAR in the code-page variant of this test — a legacy code page cannot hold them and SQL Server would silently replace them with ?. UTF-8 collations hold everything; that is why they are the default.</p><h3>Lever 4: columnstore targets that arrive compressed (4.5× smaller)</h3><p>If the table’s future is analytics, create it as a clustered columnstore <em>before</em> loading, not after:</p><pre>COPY src TO &#39;ms.dbo.Events&#39; (FORMAT bcp, CREATE_TABLE true, table_kind &#39;columnstore&#39;);</pre><p>The same hint works everywhere a table gets created — explicit DDL takes it as a WITH clause, together with the type annotations from the levers above, so you can spell out the whole target shape in one statement and then load into it:</p><pre>CREATE TABLE ms.dbo.Events (<br>    id    BIGINT,<br>    name  MSSQL_NVARCHAR(100),<br>    code  MSSQL_VARCHAR(16)<br>) WITH (table_kind = &#39;columnstore&#39;);<br><br>-- rowstore shapes are available the same way:<br>--   WITH (clustered_index = &#39;id&#39;, data_compression = &#39;PAGE&#39;)</pre><p>There’s also a session-wide default (mssql_default_table_kind) if every table you create should be a columnstore without saying so each time.</p><p>The trick that makes this work is the batch size. SQL Server writes a bulk-load batch directly into a <strong>compressed</strong> rowgroup only when the batch carries ≥ 102,400 rows; below that, rows land in the delta store and stay there uncompressed. The extension’s mssql_copy_flush_rows default is exactly 102,400 for this reason. Our results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/505/1*E11FArNHX1xyhlBEX3Y-Pg.png" /></figure><p>4.5× less disk, every rowgroup compressed on arrival, no post-load index rebuild. The load itself costs ~30% more wall on our (CPU-constrained, emulated) server — on real hardware with spare cores the gap narrows. A curiosity from the data: UTF-16 nvarchar compressed <em>better</em> inside columnstore than UTF-8 varchar (5.2 vs 5.7 GB) — the columnstore dictionary likes the wider-but-more-uniform encoding.</p><h3>Interlude: loading into a table that already exists</h3><p>Every example so far creates its target (CREATE_TABLE true is the default — created if missing, reused if present). Real pipelines mostly reload tables that already exist, and that mode has its own switches:</p><pre>-- Reload without losing indexes, permissions or partitioning<br>COPY data TO &#39;ms.dbo.facts&#39; (FORMAT bcp, TRUNCATE true);<br><br>-- Strict append into an existing table: target must exist as declared<br>COPY data TO &#39;ms.dbo.facts&#39; (FORMAT bcp, CREATE_TABLE false);</pre><p>TRUNCATE and REPLACE both destroy the rows you don&#39;t name, but they are not interchangeable: REPLACE drops and recreates, so the table&#39;s <em>definition</em> — indexes, grants, partitioning, carefully chosen collations — goes with it. TRUNCATE empties the table and keeps all of that, which is what a scheduled reload almost always wants. The truncate runs on the same connection as the load itself, so an aborted COPY can&#39;t leave the table both empty and unloaded. (SQL Server refuses to truncate a table referenced by a foreign key — that case still needs DELETE or REPLACE.)</p><p>One more thing that saves pipelines from silent corruption: against an existing table, columns are matched <strong>by name</strong> (case-insensitive), not by position. The SELECT list’s order doesn’t matter, source columns the target lacks are ignored, and nullable target columns the source doesn’t mention get NULL — a schema drift that would misalign a positional load either maps correctly or fails loudly with “no matching columns”.</p><h3>Lever 5: TABLOCK — by target shape, not by habit</h3><p>The classic advice “always bulk-load with TABLOCK” is half right. Concurrent bulk sessions on a <strong>heap</strong> take mutually compatible BU locks under TABLOCK — the hint is what <em>lets</em> them run in parallel. Against a <strong>clustered</strong> target (rowstore <em>or</em> columnstore), the same hint serializes them: we measured a columnstore load at 8.9 s with the hint pinning the server to one core, versus 5.2 s without it, three cores busy, identical compression.</p><p>The extension’s mssql_copy_tablock default is auto: heap → on, anything clustered → off, decided from the target&#39;s actual catalog shape. Leave it alone unless you know better for a specific table.</p><h3>Lever 6: TDS packet size (−27% client CPU)</h3><p>Every TDS frame is one send(). At the protocol default of 4096 bytes, a 25 GB load is ~6.5 million syscalls. v0.2.3 raised the default request to 16384 (mssql_tds_packet_size), and our /usr/bin/time counters showed exactly what you&#39;d expect: <strong>4× fewer packets sent</strong> and −27% client CPU on the write path. The server grants min(requested, its max), so this is safe against any server; the cost is 16 KB instead of 4 KB of server memory per pooled connection.</p><h3>Lever 7: don’t fight the log</h3><p>Two server-side settings dominate everything above when they’re wrong:</p><ul><li><strong>Recovery model.</strong> Under FULL recovery with no log backups, bulk loads are fully logged and the log file grows without bound — we measured the write path effectively doubling. If the database is a landing zone, SIMPLE (or BULK_LOGGED) recovery makes bulk loads minimally logged.</li><li><strong>Pre-size the data file.</strong> Autogrow events serialize everything. Our 38M-row loads reused pre-grown space; the first cold run pays for growth.</li></ul><p>Neither is an extension setting — but no client-side flag recovers what these cost.</p><h3>The one that isn’t a lever: correctness at scale</h3><p>While benchmarking this release we found that v0.2.2 <strong>segfaulted</strong> on any full-scale COPY whose source was a parquet file or a DuckDB table. The cause is instructive: at scale, DuckDB’s parquet reader and storage engine hand the sink <em>dictionary-encoded</em> vectors, and the old row encoder indexed them as if they were flat — dereferencing string content as a pointer. Small tests passed (small reads come out flat); the first real workload crashed.</p><p>v0.2.3’s columnar encoder normalizes every vector encoding, and the fix is pinned three ways: a CI test that constructs dictionary and constant vectors explicitly, an end-to-end round-trip test, and per-column hash-signature verification of all 44 columns at 38M rows. If you bulk-load from parquet through 0.2.2 today — upgrade before you scale.</p><h3>A note on Microsoft Fabric</h3><p>Until recently everything above applied to SQL Server and Azure SQL but <em>not</em> to Fabric Data Warehouse — Fabric simply didn’t accept the TDS bulk-load protocol, and bulk ingestion meant staging files for COPY INTO. That changed: Microsoft has enabled the <a href="https://learn.microsoft.com/en-us/fabric/data-warehouse/ingest-data-bulk-copy">Bulk Copy API in Fabric Warehouse (preview)</a> — the same INSERT BULK path used by bcp.exe, SqlBulkCopy, and this extension.</p><p>Practical notes for Fabric targets: authentication is Entra ID only (the extension’s Azure auth — Service Principal, CLI, Device Code — covers this), TableLock and similar bulk hints are accepted but ignored (so the TABLOCK lever is moot there), and Microsoft&#39;s own guidance is to prefer <em>large</em> batches — their sweet spot of 150 MB–1 GB per batch is bigger than the columnstore-threshold default discussed above, so for Fabric consider raising mssql_copy_flush_rows. Collations are simple there: Fabric supports exactly two, both UTF-8, and its default is the same Latin1_General_100_BIN2_UTF8 the extension uses — so created tables match native Fabric tables out of the box, and the extension refuses any non-Fabric collation before the DDL is even built. When you can stage files in OneLake/ADLS, COPY INTO remains Microsoft&#39;s recommended highest-throughput path; the BCP API is for data that lives in your client tier — which is exactly what a DuckDB pipeline is.</p><h3>The checklist</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/794/1*8gQDNNRos2UmVS1rXfNO3g.png" /></figure><p>Stacked end to end against v0.2.2’s single-writer NVARCHAR(MAX) load: <strong>933 s → 96 s</strong> on 38 million real-shape rows. Nearly 10×, and every step is one setting or one cast.</p><p><em>The extension is open source: </em><a href="https://github.com/hugr-lab/mssql-extension"><em>hugr-lab/mssql-extension</em></a><em>, docs at </em><a href="https://hugr-lab.github.io/mssql-extension/"><em>hugr-lab.github.io/mssql-extension</em></a><em>. Built for </em><a href="https://hugr-lab.github.io/"><em>hugr</em></a><em>, an open-source data mesh platform — works standalone with any DuckDB. The full benchmark methodology, raw CSVs, and the harness are in the repo under </em><em>test/bench/.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=da549a338f3e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Building a Full-Featured DuckDB Kernel for Jupyter — With a Database Explorer You’ll Actually Use]]></title>
            <link>https://medium.com/@gribanov.vladimir/building-a-full-featured-duckdb-kernel-for-jupyter-with-a-database-explorer-youll-actually-use-baa6f569e439?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/baa6f569e439</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[jupyter-notebook]]></category>
            <category><![CDATA[golang]]></category>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[hugr]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Fri, 13 Mar 2026 14:54:06 GMT</pubDate>
            <atom:updated>2026-03-13T14:54:06.067Z</atom:updated>
            <content:encoded><![CDATA[<p><strong><em>SQL notebooks deserve better tooling. We built a DuckDB kernel with interactive Perspective viewers and a database explorer that works in both JupyterLab and VS Code.</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/1*UfPMLagKzPFq0RSwb-93eg.png" /></figure><p>DuckDB already has a fantastic UI — a powerful web-based interface served by MotherDuck that runs as a standalone HTTP server inside the DuckDB process. It’s feature-rich and well-designed. But it’s built as a standalone experience, not as something that plugs into the Jupyter ecosystem. It doesn’t run inside JupyterLab or VS Code, it’s hard to integrate into your own products, and it’s not open source.</p><p>We wanted something different: a lightweight, open source kernel that follows the Jupyter standard — so it works everywhere notebooks work. In JupyterLab, in VS Code, in JupyterHub, and in custom products that embed Jupyter. That’s why we built <strong>duckdb-kernel</strong>.</p><h3>What is duckdb-kernel?</h3><p>It’s a native Jupyter kernel written in Go that runs DuckDB queries and returns results as Apache Arrow IPC streams. No Python wrapper, no magic commands — just SQL.</p><pre>FROM test</pre><p>The kernel shows up in JupyterLab’s launcher alongside your Python kernels — ready to go.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X-8uMDTXEuMGb90YnB9i2A.png" /></figure><h4>Interactive Results with Perspective</h4><p>Query results aren’t rendered as static HTML tables. Instead, they’re streamed as Arrow IPC to <a href="https://perspective.finos.org/">Perspective</a>, a high-performance data visualization engine built on WebAssembly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-Nspcns7GPPjmaMdrbGxJA.png" /></figure><p>That’s <strong>5 million rows, 237 MB</strong> — queried in 238 ms and rendered interactively less 5 seconds. You can:</p><ul><li><strong>Sort and filter</strong> millions of rows without re-running queries</li><li><strong>Group By</strong> to create instant rollups and pivots</li><li><strong>Chart</strong> results — bar, line, scatter, heatmap, treemap — all from the same output</li><li><strong>Stream</strong> large datasets chunk by chunk via Arrow IPC</li></ul><p>The same viewer works in VS Code, with the full chart type selector:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/936/1*8IwOILLy34J7cNcNf0xsmw.png" /></figure><h4>The Database Explorer</h4><p>This is the feature I’m most excited about. Both extensions include a sidebar panel for browsing your database:</p><p><strong>In JupyterLab</strong> — a rich sidebar with typed column icons, inline refresh and info buttons:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/854/1*V-fwPFSMYHVLXSw6v-c8dw.png" /></figure><p><strong>In VS Code</strong> — the same data in a familiar tree view:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/442/1*wZjCU0ArDgnS2g7JE7oQ7A.png" /></figure><p>Both show:</p><ul><li><strong>Session</strong> — DuckDB version, session ID, memory usage</li><li><strong>Catalog</strong> — databases → schemas → tables/views with column types</li><li><strong>Extensions</strong> — loaded, installed, and available extensions</li><li><strong>Secrets</strong> — configured secrets (for S3, Azure, etc.)</li><li><strong>Settings</strong> — all DuckDB configuration settings</li><li><strong>System Functions</strong> — every built-in function</li><li><strong>Memory</strong> — allocation breakdown by component (see that 295 MB for IN_MEMORY_TABLE?)</li><li><strong>Result Files</strong> — Arrow spool files from your query history</li></ul><h4>Detail Panels</h4><p>Click on any table to see full metadata. The <strong>Summarize</strong> tab gives you instant data profiling — approx_unique, avg, min, max, count — without writing a single query:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/736/1*SSzcaiTvwiATWWJzOJM60A.png" /></figure><h4>Multi-Kernel Support</h4><p>Each notebook runs its own DuckDB instance. When you switch between notebook tabs, the explorer automatically switches to the correct kernel.</p><h3>Architecture</h3><p>Here’s how the pieces fit together:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/477/1*8ZqtVl3RZH9ny65bFxPHnA.png" /></figure><p>The kernel exposes an HTTP server on localhost that serves:</p><ul><li>/arrow/stream — chunked Arrow IPC for result rendering</li><li>/introspect — JSON metadata for the explorer (wraps DuckDB system functions like duckdb_tables(), duckdb_schemas(), etc.)</li><li>/static/perspective/ — Perspective JS/WASM files</li></ul><h3>Getting Started</h3><h4>One-Click Install from VS Code</h4><p>The VS Code extension includes an <strong>Install / Update DuckDB Kernel</strong> command. It downloads the latest release from GitHub — binary, kernel.json, and Perspective static files — and configures everything. No terminal needed.</p><p><strong>JupyterLab</strong>:</p><pre>pip install hugr-perspective-viewer</pre><p><strong>Kernel only</strong> (if you already have it set up):</p><pre>curl -fsSL https://raw.githubusercontent.com/hugr-lab/duckdb-kernel/main/install.sh | bash</pre><h3>What’s Next</h3><ul><li>Query autocompletion powered by the catalog metadata</li><li>Notebook-level .duckdb file attachment</li><li>Query history with result caching</li></ul><h3>Why We Built This</h3><p><strong>duckdb-kernel</strong> is part of <a href="https://hugr-lab.github.io/">Hugr</a> — an open source Data Mesh platform. Hugr uses DuckDB as its query engine, and we needed a first-class notebook experience for data engineers and analysts working with the platform: interactive exploration of federated data sources, instant profiling of tables across multiple databases, and a viewer that handles millions of rows without breaking a sweat.</p><p>Instead of building yet another proprietary UI, we made it a standard Jupyter kernel — so it works everywhere notebooks work. Whether you’re exploring a local Parquet file or querying a federated data mesh through Hugr, the experience is the same.</p><p>Learn more about Hugr: <a href="https://hugr-lab.github.io/">hugr-lab.github.io</a></p><p><em>Built with Go, DuckDB, Apache Arrow, Perspective, TypeScript, and a healthy dislike of plain-text SQL output.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=baa6f569e439" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From 51 Seconds to 1 Second: How We Made DuckDB + SQL Server 49x Faster with ORDER BY Pushdown]]></title>
            <link>https://medium.com/@gribanov.vladimir/from-51-seconds-to-1-second-how-we-made-duckdb-sql-server-49x-faster-with-order-by-pushdown-8274fa0ee594?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/8274fa0ee594</guid>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[hugr]]></category>
            <category><![CDATA[sql-server]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Tue, 10 Mar 2026 09:34:09 GMT</pubDate>
            <atom:updated>2026-03-10T09:34:09.242Z</atom:updated>
            <content:encoded><![CDATA[<p><em>How pushing ORDER BY + LIMIT to SQL Server eliminated full table scans across 100 million rows</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*kwOUNBWDnod1qYxkR-Dmeg.png" /></figure><h3>The Problem That Shouldn’t Exist</h3><p>Here’s a query that should be instant:</p><pre>SELECT order_id, customer_id, order_date, total_amount<br>FROM orders<br>ORDER BY order_date DESC<br>LIMIT 100;</pre><p>SQL Server has an index on order_date. The execution plan is an index seek — 100 rows, sub-millisecond. But run this through DuckDB&#39;s federated query engine against an attached SQL Server database, and it takes <strong>51 seconds</strong>.</p><p>Why? Because DuckDB doesn’t know about SQL Server’s indexes. It generates SELECT ... FROM orders, streams all 100 million rows over the network, sorts them locally, and then takes the top 100. The sorting happens in DuckDB — not on SQL Server where the index lives.</p><p>We fixed this. The same query now runs in <strong>1 second</strong>. Here’s how.</p><h3>How Federated Queries Work (And Why They’re Slow)</h3><p>DuckDB’s extension system lets you ATTACH remote databases and query them as if they were local. Our <a href="https://github.com/hugr-lab/mssql-extension">mssql extension</a> implements this for SQL Server using a custom TDS protocol — no ODBC, no FreeTDS, just raw protocol packets.</p><p>When you write ORDER BY ... LIMIT N, DuckDB&#39;s query planner creates a logical plan like this:</p><pre>LogicalTopN (LIMIT 100, ORDER BY order_date DESC)<br>    └── LogicalGet (mssql_catalog_scan → SELECT ... FROM orders)</pre><p>The extension only controls what happens inside LogicalGet — the table scan. It generates a SELECT statement, streams rows back, and feeds them to DuckDB. Everything above the scan (sorting, limiting, joining) happens in DuckDB.</p><p>This means SQL Server sends <strong>all 100 million rows</strong> over the wire, and DuckDB sorts them just to keep 100.</p><h4>Before: Without Pushdown</h4><pre>DuckDB                              SQL Server<br>┌───────────────┐                 ┌────────────────┐<br>│  LIMIT 100    │                 │                │<br>│      ↑        │                 │   100M rows    │<br>│  ORDER BY     │  ◄── scan all   │   full table   │<br>│      ↑        │     100M rows   │   scan         │<br>│  TABLE SCAN   │ ◄───────────────│                │<br>└───────────────┘                 └────────────────┘<br><br>Network: ~100,000,000 rows transferred<br>Time: 51 seconds</pre><h4>After: With Pushdown</h4><pre>DuckDB                              SQL Server<br>┌───────────────┐                 ┌────────────────┐<br>│               │                 │  TOP 100       │<br>│   Results     │  ◄── 100 rows   │  ORDER BY      │<br>│  (100 rows)   │ ◄───────────────│  (index seek)  │<br>└───────────────┘                 └────────────────┘<br><br>Network: 100 rows transferred<br>Time: 1 second</pre><p>The fix is conceptually simple: rewrite the SQL sent to SQL Server from SELECT ... to SELECT TOP 100 ... ORDER BY order_date DESC, letting SQL Server use its index. The implementation, however, required solving several interesting problems.</p><h3>How the Optimizer Works</h3><p>DuckDB provides an OptimizerExtension API that lets extensions register custom optimization passes. Our pass runs after DuckDB&#39;s built-in optimizers (important — DuckDB may have already merged ORDER BY + LIMIT into a LogicalTopN node).</p><p>The optimizer walks the logical plan looking for three patterns:</p><pre>Pattern A: ORDER BY only (no LIMIT)<br>    LogicalOrder<br>        └── [LogicalProjection] ← optional<br>              └── LogicalGet (mssql_catalog_scan)<br><br>Pattern B: ORDER BY + LIMIT (separate nodes)<br>    LogicalLimit<br>        └── LogicalOrder<br>              └── [LogicalProjection] ← optional<br>                    └── LogicalGet (mssql_catalog_scan)<br><br>Pattern C: TopN (DuckDB pre-merged ORDER + LIMIT)<br>    LogicalTopN<br>        └── [LogicalProjection] ← optional<br>              └── LogicalGet (mssql_catalog_scan)</pre><p>When a pattern matches, the optimizer rewrites the scan’s SQL from:</p><pre>SELECT [order_id], [customer_id], [order_date], [total_amount]<br>FROM [dbo].[orders]</pre><p>to:</p><pre>SELECT TOP 100 [order_id], [customer_id], [order_date], [total_amount]<br>FROM [dbo].[orders]<br>ORDER BY [order_date] DESC</pre><p>If all ORDER BY columns are pushed successfully and the LIMIT is constant, the LogicalOrder and LogicalLimit nodes are <strong>removed from the plan entirely</strong>. DuckDB doesn&#39;t need to sort — the data arrives pre-sorted from SQL Server.</p><h4>The NULL Ordering Problem</h4><p>There’s a subtle incompatibility. SQL Server and DuckDB disagree about where NULLs sort:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/281/1*wGnFECShQs-0SZU1v7EHog.png" /></figure><p>And SQL Server has no NULLS FIRST / NULLS LAST syntax.</p><p>Our optimizer handles this per-column: if a column is NOT NULL, the mismatch is irrelevant — push it down. If it&#39;s nullable, we only push when the requested NULL order matches SQL Server&#39;s behavior (ASC NULLS FIRST or DESC NULLS LAST). Otherwise, we stop pushing at that column and let DuckDB handle the rest.</p><h4>Partial Pushdown</h4><p>ORDER BY columns are processed in sequence. If column 3 out of 5 can’t be pushed (say, it uses an expression we can’t translate), we push columns 1–2 to SQL Server and let DuckDB handle the full sort. The data arrives partially pre-sorted, which still helps DuckDB’s sort algorithm.</p><pre>-- User writes:<br>SELECT order_id, customer_id FROM orders ORDER BY total_amount DESC LIMIT 20<br><br>-- Without pruning: scan fetches order_id, customer_id, AND total_amount<br>-- With pruning: scan fetches only order_id, customer_id (total_amount handled server-side)</pre><p>This reduces network traffic — fewer columns transferred per row.</p><h3>Benchmark Results: 100 Million Rows</h3><p>We benchmarked against a SQL Server instance with 100M rows in the orders table, with indexes on order_date, status + order_date, customer_id, and region + category + total_amount.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/599/1*7nW04Xj_1D-sNK9hb9y3Rw.png" /></figure><p><strong>Key takeaways:</strong></p><ul><li><strong>Scenarios 1–4</strong>: Dramatic speedups (12x-49x) — these are the common real-world patterns where you have an index and want the top N rows.</li><li><strong>Scenario 3</strong>: Projection pruning means even sorting by a column not in your SELECT list gets the full benefit.</li><li><strong>Scenario 5</strong>: Composite index on (region, category, total_amount) helps but mixed ASC/DESC prevents a pure index seek — still 6x faster.</li><li><strong>Scenarios 6–7</strong>: Without LIMIT or without an index, pushdown can’t help. SQL Server still does a full sort.</li><li><strong>Scenario 8</strong>: When WHERE is already selective enough (1K rows out of 100M), both paths are fast.</li></ul><p>The sweet spot is clear: <strong>ORDER BY + LIMIT on indexed columns</strong> is where pushdown transforms performance.</p><h3>Working Around JOINs</h3><p>ORDER BY pushdown is blocked when a JOIN sits between the ORDER BY and the MSSQL scan — the optimizer can’t push through it because the sort might reference columns from both tables.</p><p>If you’re joining an MSSQL table with a local table and want pushdown on the MSSQL side, use a CTE to pre-sort:</p><pre>-- Direct JOIN: pushdown blocked, scans 100M rows<br>SELECT o.*, c.segment<br>FROM mssql.orders o<br>JOIN customer_summary c ON o.customer_id = c.customer_id<br>ORDER BY o.order_date DESC<br>LIMIT 1000;<br>-- Time: ~63 seconds<br><br>-- CTE workaround: pushdown inside CTE, JOIN on pre-sorted result<br>WITH top_orders AS MATERIALIZED (<br>    SELECT order_id, customer_id, order_date, total_amount<br>    FROM mssql.orders<br>    ORDER BY order_date DESC<br>    LIMIT 1000<br>)<br>SELECT t.*, c.segment<br>FROM top_orders t<br>JOIN customer_summary c ON t.customer_id = c.customer_id;<br>-- Time: ~23 seconds (2.4x faster)</pre><p>The CTE’s inner query gets pushdown (SQL Server returns 1,000 pre-sorted rows), then the JOIN runs locally against the small result.</p><h3>Bonus: Bulk Catalog Loading for Large Databases</h3><p>While building ORDER BY pushdown, we also shipped bulk catalog loading — solving a different performance problem.</p><p>Regular queries don’t need the full catalog. The extension loads metadata lazily — only the tables you actually query. Your first SELECT runs immediately regardless of database size.</p><p>But database tools are different. DBeaver, DuckDB UI, and similar GUI clients request <strong>all</strong> catalog metadata upfront to populate schema trees, table lists, and column explorers. For an ERP database with hundreds of schemas and thousands of tables, that’s a lot of individual metadata round-trips.</p><p>We now handle this with a bulk approach: when a tool requests the full catalog, the extension automatically uses the same strategy as mssql_preload_catalog() — one query per schema that fetches all tables, columns, types, and statistics in a single round trip. It uses streaming group-by parsing (results ordered by schema_name, object_name, column_id) so it doesn&#39;t buffer the full result set in memory.</p><p>You can also trigger this explicitly:</p><pre>ATTACH &#39;Server=myserver;Database=BigDB;...&#39; AS db (TYPE mssql);<br><br>-- Preload everything (all schemas):<br>SELECT mssql_preload_catalog(&#39;db&#39;);<br><br>-- Or preload a single schema:<br>SELECT mssql_preload_catalog(&#39;db&#39;, &#39;dbo&#39;);</pre><p>We benchmarked this on a database with <strong>200K tables</strong> (100K per schema, ~2.1M columns each):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/1*pMQTtRMDA26m2eGjgGmuxg.png" /></figure><p>Before the fix, a single SELECT triggered cardinality statistics queries for every table in the schema — 100K individual round-trips. Now it loads only the one table you need. And when a GUI tool does request everything, bulk loading handles 100K tables with 2.1M columns in a single 15-second round trip.</p><p>You can also narrow the visible catalog with <strong>schema and table filters</strong> — regex patterns that limit which schemas and tables the extension exposes:</p><pre>ATTACH &#39;Server=...;Database=BigDB;...&#39; AS db<br>  (TYPE mssql, schema_filter &#39;^(dbo|sales)$&#39;, table_filter &#39;^Order&#39;);</pre><h3>Enterprise and Cloud: Why This Matters</h3><p>For large enterprise databases (ERP, CRM systems with hundreds of schemas and thousands of tables), these improvements transform the day-to-day experience:</p><ul><li><strong>GUI tools work smoothly</strong> — DBeaver, DuckDB UI, and similar clients populate schema trees in seconds instead of hanging for minutes.</li><li><strong>Schema/table filters reduce noise</strong> — in a 500-table ERP database, expose only the schemas your team needs. Less clutter, faster metadata loading.</li><li><strong>Potential cloud cost savings</strong> — on Azure SQL Database and Microsoft Fabric, you pay for compute and data transfer. ORDER BY pushdown reduces streamed data from millions of rows to just the top N. Catalog filters and bulk loading reduce metadata round-trips. Less data transferred means lower costs on metered cloud databases.</li></ul><h3>How to Enable</h3><p>ORDER BY pushdown is available in mssql-extension v0.1.18+ (For DuckDB 1.5+). Enable it per-database or globally:</p><pre>-- Per-database (ATTACH option):<br>ATTACH &#39;Server=...;Database=...;User Id=...;Password=...&#39;<br>AS mydb (TYPE mssql, order_pushdown true);<br><br>-- Global setting:<br>SET mssql_order_pushdown = true;</pre><p>The feature is opt-in because it changes the SQL sent to SQL Server and we want users to validate it against their workloads before enabling broadly.</p><p>We originally built the mssql-extension for <a href="https://hugr-lab.github.io/">hugr</a> — an open-source data mesh platform that provides a unified GraphQL API across distributed data sources (databases, files, APIs) with cross-source queries, geospatial processing, and fine-grained access control. We needed SQL Server as a first-class data source in hugr, so we built this DuckDB extension from scratch.</p><p>That said, the extension is fully standalone — you can use it independently with any DuckDB setup, no hugr required. Source code: <a href="https://github.com/hugr-lab/mssql-extension">hugr-lab/mssql-extension</a>. Contributions welcome.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8274fa0ee594" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Querying Azure SQL and Microsoft Fabric from DuckDB with Azure EntraID Authentication]]></title>
            <link>https://medium.com/@gribanov.vladimir/querying-azure-sql-and-microsoft-fabric-from-duckdb-with-azure-entra-id-authentication-56e0a67a0d9b?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/56e0a67a0d9b</guid>
            <category><![CDATA[entra-id]]></category>
            <category><![CDATA[microsoft-fabric]]></category>
            <category><![CDATA[hugr]]></category>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[azure]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Fri, 06 Feb 2026 22:11:22 GMT</pubDate>
            <atom:updated>2026-02-07T16:16:27.868Z</atom:updated>
            <content:encoded><![CDATA[<p><em>How to connect DuckDB to Azure cloud databases without ODBC drivers</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c_lE1Q4wAmYZLEjOoUdfOw.png" /></figure><h3>Introduction</h3><p>DuckDB has become the go-to embedded analytics database for data engineers and analysts. But what if your data lives in Azure SQL Database or Microsoft Fabric? Until now, connecting DuckDB to Azure required complex ODBC setups or manual data exports.</p><p>With version 0.1.16 of the <strong>DuckDB MSSQL Extension</strong>, you can now connect directly to Azure cloud databases using Azure Entra ID (formerly Azure AD) authentication — no ODBC drivers required.</p><h3>What’s New in v0.1.16</h3><p>This release introduces comprehensive Azure Entra ID support:</p><ul><li><strong>Service Principal Authentication</strong> — Perfect for CI/CD pipelines and automated jobs</li><li><strong>Azure CLI Integration</strong> — Use your existing az login credentials</li><li><strong>Device Code Flow</strong> — Interactive authentication with MFA support</li><li><strong>Environment Variables</strong> — Azure SDK-compatible credentials (AZURE_TENANT_ID, etc.)</li><li><strong>Direct Token Support</strong> — Bring your own access tokens</li></ul><h4>Lazy Metadata Loading</h4><p>Previously, attaching to a SQL Server database would eagerly load all schema metadata upfront — slow for databases with many tables. Now metadata is loaded on-demand:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/465/1*DsAKxH2ikxstDdUHRyh0-w.png" /></figure><p>This means you can attach to large enterprise databases and start querying immediately — only the tables you actually use are discovered.</p><h3>Getting Started</h3><pre>-- Install the extensions<br>INSTALL mssql FROM community;<br>LOAD mssql;<br><br>-- required for duckdb azure secret type<br>INSTALL azure;<br>LOAD azure;</pre><h3>Authentication Methods</h3><h4>1. Service Principal (Best for Production)</h4><pre>-- Create Azure secret with service principal<br>CREATE SECRET azure_prod (<br>    TYPE azure,<br>    PROVIDER service_principal,<br>    TENANT_ID &#39;your-tenant-id&#39;,<br>    CLIENT_ID &#39;your-client-id&#39;,<br>    CLIENT_SECRET &#39;your-client-secret&#39;<br>);<br><br>-- Connect to Azure SQL Database<br>ATTACH &#39;Server=myserver.database.windows.net;Database=mydb&#39;<br>    AS azure_db (TYPE mssql, AZURE_SECRET &#39;azure_prod&#39;);</pre><h4>2. Azure CLI (Best for Development)</h4><pre>-- Use your existing az login credentials<br>CREATE SECRET azure_dev (<br>    TYPE azure,<br>    PROVIDER credential_chain,<br>    CHAIN &#39;cli&#39;<br>);<br><br>ATTACH &#39;Server=myserver.database.windows.net;Database=mydb&#39;<br>    AS azure_db (TYPE mssql, AZURE_SECRET &#39;azure_dev&#39;);</pre><h4>3. Environment Variables (Best for CI/CD)</h4><pre># Set Azure SDK standard environment variables<br>export AZURE_TENANT_ID=&quot;your-tenant-id&quot;<br>export AZURE_CLIENT_ID=&quot;your-client-id&quot;<br>export AZURE_CLIENT_SECRET=&quot;your-client-secret&quot;</pre><pre>CREATE SECRET azure_ci (<br>    TYPE azure,<br>    PROVIDER credential_chain,<br>    CHAIN &#39;env&#39;<br>);</pre><h4>4. Interactive Device Code (Best for MFA)</h4><pre>CREATE SECRET azure_interactive (<br>    TYPE azure,<br>    PROVIDER credential_chain,<br>    CHAIN &#39;interactive&#39;<br>);<br><br>ATTACH &#39;Server=myserver.database.windows.net;Database=mydb&#39;<br>    AS azure_db (TYPE mssql, AZURE_SECRET &#39;azure_interactive&#39;);<br>-- When you attach, you&#39;ll see a prompt like:<br>-- &quot;To sign in, open https://microsoft.com/devicelogin<br>--  and enter the code ABCD1234&quot;</pre><h4>5. Access Token (Best for Fabric Notebooks)</h4><p>If you already have an access token — for example, from a Fabric notebook’s built-in token provider — you can pass it directly without creating an Azure secret:</p><pre>ATTACH &#39;Server=xyz.datawarehouse.fabric.microsoft.com;Database=my_warehouse&#39;<br>    AS fabric (TYPE mssql, ACCESS_TOKEN &#39;eyJ0eXAiOi...your-jwt-token&#39;);</pre><p>You can also get a token via the Azure CLI:</p><pre>az account get-access-token \<br>    --resource https://database.windows.net \<br>    --query accessToken -o tsv</pre><h3>Using DuckDB in Microsoft Fabric Notebooks</h3><p>Fabric notebooks provide a built-in token you can use to query other Fabric warehouses or Azure SQL databases directly from DuckDB — no secrets or extra credentials needed.</p><h4>Python Example</h4><pre>import duckdb<br><br># Get access token from the Fabric notebook&#39;s built-in credential provider<br>access_token = notebookutils.credentials.getToken(<br>    &quot;https://database.windows.net/&quot;<br>)<br><br>conn = duckdb.connect()<br>conn.execute(&quot;INSTALL mssql FROM community; LOAD mssql;&quot;)<br><br># Attach using the token<br>conn.execute(f&quot;&quot;&quot;<br>    ATTACH &#39;Server=xyz.datawarehouse.fabric.microsoft.com;Database=my_warehouse&#39;<br>        AS wh (TYPE mssql, ACCESS_TOKEN &#39;{access_token}&#39;)<br>&quot;&quot;&quot;)<br><br># Query Fabric tables from DuckDB<br>df = conn.execute(&quot;SELECT * FROM wh.dbo.sales LIMIT 1000&quot;).fetchdf()<br>df.head()</pre><h4>SQL-Only Example (Fabric Notebook %%sql cell)</h4><p>If you prefer pure SQL in a notebook cell, obtain the token in a Python cell first and store it, then reference it:</p><pre># Cell 1 (Python): get the token<br>access_token = notebookutils.credentials.getToken(<br>    &quot;https://database.windows.net/&quot;<br>)<br>spark.conf.set(&quot;spark.duckdb.token&quot;, access_token)</pre><pre>-- Cell 2 (SQL via DuckDB): use the token<br>INSTALL mssql FROM community;<br>LOAD mssql;<br><br>ATTACH &#39;Server=xyz.datawarehouse.fabric.microsoft.com;Database=my_warehouse&#39;<br>    AS wh (TYPE mssql, ACCESS_TOKEN &#39;${spark.conf.get(&quot;spark.duckdb.token&quot;)}&#39;);<br><br>SELECT * FROM wh.dbo.sales LIMIT 100;</pre><p><strong>Tip:</strong> Tokens expire (typically after 1 hour). For long-running notebooks, re-acquire the token before each ATTACH or use the Azure secret approach with credential_chain for automatic refresh.</p><h4>Connecting to Microsoft Fabric</h4><p>Microsoft Fabric Data Warehouses work seamlessly:</p><pre>-- Connect to Fabric Data Warehouse<br>ATTACH &#39;Server=xyz.datawarehouse.fabric.microsoft.com;Database=my_warehouse&#39;<br>    AS fabric (TYPE mssql, AZURE_SECRET &#39;my_azure&#39;);<br><br>-- Query Fabric tables<br>SELECT * FROM fabric.dbo.sales_data LIMIT 100;</pre><h3>Real-World Examples</h3><h4>Azure SQL Database</h4><pre>-- Connect to Azure SQL<br>ATTACH &#39;Server=myserver.database.windows.net;Database=mydb&#39;<br>    AS azuresql (TYPE mssql, AZURE_SECRET &#39;my_azure&#39;);<br><br>-- Query data<br>SELECT * FROM azuresql.dbo.customers LIMIT 100;<br><br>-- Bulk load 10K rows (uses BCP protocol - very fast!)<br>CREATE TABLE azuresql.dbo.analytics_results AS<br>SELECT * FROM my_analysis_results;</pre><h4>Microsoft Fabric: NYC Taxi Analysis</h4><p>Here’s a complete example querying the NYC Taxi dataset from Microsoft Fabric:</p><pre>-- Setup<br>INSTALL azure; LOAD azure;<br>INSTALL mssql FROM community; LOAD mssql;<br><br>-- Create service principal secret<br>CREATE SECRET fabric_auth (<br>    TYPE azure,<br>    PROVIDER service_principal,<br>    TENANT_ID &#39;your-tenant-id&#39;,<br>    CLIENT_ID &#39;your-client-id&#39;,<br>    CLIENT_SECRET &#39;your-client-secret&#39;<br>);<br><br>-- Connect to Fabric warehouse<br>ATTACH &#39;Server=xyz.datawarehouse.fabric.microsoft.com;Database=nyc_taxi&#39;<br>    AS taxi (TYPE mssql, AZURE_SECRET &#39;fabric_auth&#39;);<br><br>-- Analyze payment methods<br>SELECT<br>    PaymentType,<br>    COUNT(*) AS trips,<br>    AVG(TripDistanceMiles) AS avg_distance,<br>    SUM(TotalAmount) AS total_revenue<br>FROM taxi.dbo.Trip<br>GROUP BY PaymentType<br>ORDER BY trips DESC;</pre><p><strong>Results:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/436/1*D9dA7nvXwkkOmGRk_jVOVw.png" /></figure><h3>Performance Benchmarks</h3><p>We tested connection and query performance against both Azure SQL Database and Microsoft Fabric:</p><h4>Connection Performance</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/599/1*3n04hmKfbS1GaM6NCifuuQ.png" /></figure><p><em>With lazy metadata loading, schema discovery happens on-demand, not during ATTACH.</em></p><h4>Query Performance</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/349/1*xgqOuMNTPi_wgM71qaru3Q.png" /></figure><h4>Bulk Load Performance (CTAS)</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/385/1*WtQaajjZQKVSgO3s3EgZXw.png" /></figure><p><em>Azure SQL uses native BCP protocol for 10–20x faster bulk loads. Benchmarks run from local machine (Europe) to Azure West Europe region.</em></p><h3>Supported Azure Services</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*XArT5mChyTOOHEKqLPD17A.png" /></figure><h3>How It Works</h3><p>The extension implements the TDS (Tabular Data Stream) protocol natively in C++ — no ODBC or FreeTDS dependencies. Azure Entra ID authentication uses the FEDAUTH token flow:</p><ol><li>Token acquired via Azure Identity (OAuth2 client credentials, CLI, or device code)</li><li>Token encoded as UTF-16LE and sent in TDS LOGIN7 packet</li><li>SQL Server/Fabric validates token with Azure AD</li><li>Connection established with user’s permissions</li></ol><h3>Security Best Practices</h3><ol><li><strong>Never commit secrets to source control</strong> — Use environment variables or Azure Key Vault</li><li><strong>Use service principals for production</strong> — Interactive auth is for development only</li><li><strong>Rotate secrets every 90 days</strong> — Azure recommends this cadence</li><li><strong>Grant least privilege</strong> — Only db_datareader/db_datawriter as needed</li><li><strong>Enable Azure AD audit logs</strong> — Monitor authentication attempts</li></ol><h3>Conclusion</h3><p>The DuckDB MSSQL Extension v0.1.16 makes it easy to query Azure cloud databases directly from DuckDB. Whether you’re building data pipelines, exploring data interactively, or running analytics, you can now leverage DuckDB’s powerful query engine with your Azure data.</p><p>For <a href="https://github.com/hugr-lab/hugr">Hugr</a>, this opens the door to full-fledged support for SQL Server, Azure SQL, and Microsoft Fabric as data sources. Expect this in an upcoming release.</p><p><strong>Get Started:</strong></p><pre>INSTALL mssql FROM community; LOAD mssql;</pre><p><strong>Resources:</strong></p><ul><li>GitHub: <a href="https://github.com/hugr-lab/mssql-extension">github.com/hugr-lab/mssql-extension</a></li><li>Documentation: <a href="https://github.com/hugr-lab/mssql-extension/blob/main/AZURE.md">AZURE.md</a></li><li>Hugr Data Mesh Platform: <a href="https://hugr-lab.github.io/">hugr-lab.github.io</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=56e0a67a0d9b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[DuckDB SQL Server extension: : UPDATE, DELETE, Transactions, and 1.2M Rows/Sec Bulk Loading]]></title>
            <link>https://medium.com/@gribanov.vladimir/duckdb-sql-server-extension-update-delete-transactions-and-1-2m-rows-sec-bulk-loading-a1a921fce647?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/a1a921fce647</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[data-mesh]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Sun, 01 Feb 2026 19:26:38 GMT</pubDate>
            <atom:updated>2026-02-01T19:26:38.053Z</atom:updated>
            <content:encoded><![CDATA[<h3>DuckDB SQL Server extension: <strong>: UPDATE, DELETE, Transactions, and 1.2M Rows/Sec Bulk Loading</strong></h3><p><strong>DuckDB MSSQL Extension v0.1.14</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fscirlZ_6Jz7MhwepVmrXA.png" /></figure><p>In the <a href="https://medium.com/@gribanov.vladimir/bringing-microsoft-sql-server-to-duckdb-a-native-tds-extension-d069bf49d8d2">first article</a>, I introduced the DuckDB MSSQL extension — a native TDS protocol implementation that lets DuckDB talk directly to SQL Server without ODBC, JDBC, or any external drivers.</p><p>At the end of that article, I shared a roadmap:</p><p><em>Planned features include: UPDATE/DELETE, Transactions, CTAS, MERGE/UPSERT, BCP/COPY — High-throughput bulk insert via native BCP protocol for 10M+ rows</em></p><p>Today, I’m happy to report: most of that roadmap is now reality. Let’s walk through what’s been delivered.</p><h3><strong>UPDATE and DELETE: Full DML Support</strong></h3><p>The foundation for write operations was establishing <strong>row identity</strong>. Tables with primary keys now expose a virtual <strong><em>rowid</em></strong> column:</p><pre>-- Scalar primary key returns the native type<br>SELECT rowid, name FROM sqlserver.dbo.customers;<br>-- rowid: 1, 2, 3...<br><br>-- Composite keys return a STRUCT<br>SELECT rowid, quantity FROM sqlserver.dbo.order_items;<br>-- rowid: {&#39;tenant&#39;: &#39;ACME&#39;, &#39;item_id&#39;: 1}</pre><p>With row identity in place, UPDATE and DELETE work exactly as you’d expect:</p><pre>-- Update with expressions<br>UPDATE sqlserver.dbo.products<br>SET price = price * 1.10<br>WHERE category = &#39;premium&#39;;<br><br>-- Delete with subquery<br>DELETE FROM sqlserver.dbo.order_items<br>WHERE order_id IN (<br>    SELECT id FROM sqlserver.dbo.orders WHERE status = &#39;cancelled&#39;<br>);</pre><p>Operations are automatically batched for large datasets. Configure batch size via <strong><em>mssql_dml_batch_size</em></strong> (default: 500 rows).</p><h3><strong>Transactions: BEGIN, COMMIT, ROLLBACK</strong></h3><p>Real-world data operations require atomicity. The extension now fully supports DuckDB’s transaction model:</p><p>Behind the scenes, the extension implements <strong>connection pinning</strong>: when you start a transaction, a specific connection from the pool is dedicated to that transaction until COMMIT or ROLLBACK. All statements execute on the same SQL Server session.</p><p><strong>Temp Tables Just Work</strong></p><p>This is particularly useful for temp table workflows:</p><pre>BEGIN;<br><br>-- Create temp table via COPY<br>COPY staging_data TO &#39;mssql://sqlserver/#import_batch&#39; (FORMAT &#39;bcp&#39;);<br><br>-- Process it<br>SELECT mssql_exec(&#39;sqlserver&#39;, &#39;<br>    INSERT INTO dbo.products (sku, name, price)<br>    SELECT sku, name, price FROM #import_batch<br>    WHERE NOT EXISTS (SELECT 1 FROM dbo.products p WHERE p.sku = #import_batch.sku)<br>&#39;);<br><br>COMMIT;</pre><p>When connections return to the pool, they’re flagged for session reset via TDS <strong><em>RESET_CONNECTION</em></strong>. Temp tables don’t leak between operations.</p><h3><strong>CREATE TABLE AS SELECT (CTAS)</strong></h3><p>Need to materialize query results in SQL Server? CTAS provides a clean syntax:</p><pre>CREATE TABLE sqlserver.dbo.daily_summary AS<br>SELECT region, COUNT(*) as orders, SUM(amount) as revenue<br>FROM sqlserver.dbo.orders<br>WHERE order_date = today()<br>GROUP BY region;<br><br>-- Create from local CSV<br>CREATE TABLE sqlserver.dbo.imported_data AS<br>FROM read_csv(&#39;data.csv&#39;);<br><br>-- Replace existing table<br>CREATE OR REPLACE TABLE sqlserver.dbo.daily_report AS<br>FROM sqlserver.dbo.transactions WHERE date = today();</pre><p>DuckDB types are automatically mapped to SQL Server equivalents:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/552/1*klchCNA_d5ETdMK0s6Ecew.png" /><figcaption>Types mapping</figcaption></figure><p>The operation streams data without buffering the entire result set — memory usage stays bounded regardless of dataset size.</p><h3><strong>High-Performance COPY TO: The BulkLoadBCP Protocol</strong></h3><p>This is the headline feature. Remember the roadmap item about “10M+ rows”? We delivered.</p><p><strong>Why BulkLoadBCP?</strong></p><p>INSERT statements have overhead: SQL parsing, parameter binding, transaction logging. For bulk operations, SQL Server offers the BulkLoadBCP protocol (TDS packet type 0x07) — a binary format designed for maximum throughput.</p><p>The extension implements this protocol directly.</p><p>Additional characteristics:</p><ul><li><strong>Bounded memory</strong> regardless of dataset size</li><li><strong>15–30% faster</strong> with TABLOCK hint (enabled by default)</li></ul><p><strong>Basic Usage</strong></p><pre>-- Copy local table to SQL Server<br>COPY my_local_table TO &#39;mssql://sqlserver/dbo/target&#39; (FORMAT &#39;bcp&#39;);<br><br>-- Copy query results<br>COPY (SELECT * FROM source WHERE year = 2024)<br>TO &#39;mssql://sqlserver/dbo/archive_2024&#39; (FORMAT &#39;bcp&#39;);<br><br>-- Alternative catalog syntax<br>COPY data TO &#39;sqlserver.dbo.target&#39; (FORMAT &#39;bcp&#39;);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*4KvhKiKaSQthlEfUPYPSNQ.png" /><figcaption>Copy options</figcaption></figure><p><strong>Smart Column Mapping (New in v0.1.13)</strong></p><p>When copying to existing tables, columns are now matched <strong>**by name**</strong>, not position:</p><pre>-- Target table: (id INT, name VARCHAR(50), value FLOAT)<br>-- Source has different column order and is missing &#39;name&#39;<br><br>CREATE TABLE source AS SELECT 1.5 AS value, 1 AS id;<br>COPY source TO &#39;mssql://sqlserver/dbo/target&#39; (FORMAT &#39;bcp&#39;, CREATE_TABLE false);<br><br>-- Result: id=1, name=NULL, value=1.5</pre><p>This handles:</p><ul><li><strong>Column reordering</strong> — source columns can be in any order</li><li><strong>Subset mapping</strong> — missing target columns receive NULL</li><li><strong>Extra columns</strong> — source columns not in target are ignored</li><li><strong>Case-insensitive</strong> — `id` matches `ID` or `Id`</li></ul><p>No more rigid position-based matching. The extension figures out the mapping automatically.</p><h3><strong>Benchmarks: Real Numbers</strong></h3><p>All benchmarks run on macOS ARM64 (Apple Silicon), SQL Server 2022 in Docker, localhost network.</p><p><strong>COPY/BCP Throughput (10M rows each)</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zXX3fzpl8WZsEAZXa8-Elg.png" /></figure><p>The large table benchmark transferred ~65 GB of data in under 6 minutes.</p><p><strong>Query Performance (10M row table)</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/886/1*f4PLddnI2NFMBh53FEezFA.png" /></figure><p>Filter and projection pushdown means SQL Server handles the filtering — only matched rows cross the network.</p><p><strong>Hybrid Analytics: JOIN with Local Tables</strong></p><p>Here’s where it gets interesting. You can join SQL Server data with local DuckDB tables:</p><pre>-- Local lookup table (5 rows)<br>CREATE TABLE status_lookup AS SELECT status, multiplier FROM ...;<br><br>-- Join 10M SQL Server rows with local reference data<br>SELECT m.id, m.amount, l.multiplier, m.amount * l.multiplier AS adjusted<br>FROM sqlserver.dbo.transactions m<br>  JOIN status_lookup l ON m.status = l.status<br>GROUP BY l.status;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/852/1*VUZBCYS2LD_zFpCjydEJtw.png" /></figure><p>The JOIN adds minimal overhead (~0.1s) compared to a pure SQL Server aggregate. Data streams from SQL Server and joins in DuckDB’s engine — no temp tables, no staging, just a single query.</p><h3><strong>Connection Management Improvements</strong></h3><p><strong>Fail-Fast Validation</strong></p><p>Connections are now validated at ATTACH time:</p><pre>-- Invalid hostname fails immediately<br>ATTACH &#39;Server=nonexistent.host;...&#39; AS db (TYPE mssql);<br>-- Error: Cannot resolve hostname &#39;nonexistent.host&#39;<br><br>-- Bad credentials fail immediately<br>ATTACH &#39;Server=localhost;User Id=wrong;Password=wrong;...&#39; AS db (TYPE mssql);<br>-- Error: Authentication failed for user &#39;wrong&#39;</pre><p>No more silent failures or confusing errors on the first query.</p><p><strong>Pool Statistics</strong></p><p>Monitor your connection pool:</p><pre>FROM mssql_pool_stats(&#39;sqlserver&#39;);</pre><p>Columns:</p><ul><li>total_connections — Current pool size</li><li>idle_connections — Available connections</li><li>active_connections — Currently in use</li><li>pinned_connections — Bound to transactions</li><li>acquire_timeout_count — Pool exhaustion events</li></ul><h3><strong>Multi-Statement Batches</strong></h3><p>The <strong><em>mssql_scan()</em></strong> function now supports batches where intermediate statements don’t return results:</p><pre>-- Create temp table, populate it, query it — all in one call<br>FROM mssql_scan(&#39;sqlserver&#39;, &#39;<br>    SELECT * INTO #filtered FROM dbo.large_table WHERE region = &#39;&#39;US&#39;&#39;;<br>    CREATE INDEX ix_temp ON #filtered(customer_id);<br>    SELECT * FROM #filtered ORDER BY created_at<br>&#39;);</pre><p>The constraint: only one statement in the batch may produce a result set.</p><h3><strong>Platform Support</strong></h3><p>The extension builds and tests on:</p><ul><li><strong>macOS</strong> (ARM64)</li><li><strong>Linux</strong> (x86_64, ARM64)</li><li><strong>Windows</strong> (MSVC, MinGW)</li></ul><p>All platforms include TLS encryption via OpenSSL.</p><h3><strong>What’s Still on the Roadmap</strong></h3><ul><li><strong>MERGE/UPSERT</strong> — Batched upsert using SQL Server’s MERGE statement</li><li><strong>Azure SQL and Fabric SQL</strong> — Cloud-native SQL Server variants</li><li><strong>Named instances support</strong> — Connect to `server\instance` syntax</li></ul><h3><strong>Try It Now</strong></h3><pre>INSTALL mssql FROM community;<br>LOAD mssql;<br><br>CREATE SECRET my_sql (<br>    TYPE mssql,<br>    host &#39;localhost&#39;,<br>    port 1433,<br>    database &#39;mydb&#39;,<br>    user &#39;sa&#39;,<br>    password &#39;YourPassword&#39;<br>);<br><br>ATTACH &#39;&#39; AS db (TYPE mssql, SECRET my_sql);<br><br>-- Full DML<br>INSERT INTO db.dbo.products (name, price) VALUES (&#39;Widget&#39;, 29.99) RETURNING id;<br>UPDATE db.dbo.products SET price = price * 1.1 WHERE category = &#39;premium&#39;;<br>DELETE FROM db.dbo.products WHERE discontinued = true;<br><br>-- Bulk load at up to 1.2M rows/sec<br>COPY local_data TO &#39;mssql://db/dbo/target&#39; (FORMAT &#39;bcp&#39;);<br><br>-- Transactions with temp tables<br>BEGIN;<br>COPY staging TO &#39;mssql://db/#temp&#39; (FORMAT &#39;bcp&#39;);<br>SELECT * FROM mssql_scan(&#39;db&#39;, &#39;SELECT COUNT(*) FROM #temp&#39;);<br>COMMIT;</pre><p><strong>Resources</strong></p><ul><li>GitHub: <a href="https://github.com/hugr-lab/mssql-extension">github.com/hugr-lab/mssql-extension</a></li><li>Hugr Platform: <a href="https://hugr-lab.github.io/">hugr-lab.github.io</a></li></ul><p>If you found this useful, <strong>a star on GitHub really helps</strong> ⭐</p><p>We welcome contributions, bug reports, and feedback. Let us know how it works for your use case.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a1a921fce647" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bringing Microsoft SQL Server to DuckDB: A Native TDS Extension]]></title>
            <link>https://medium.com/@gribanov.vladimir/bringing-microsoft-sql-server-to-duckdb-a-native-tds-extension-d069bf49d8d2?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/d069bf49d8d2</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[sql-server]]></category>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[data-mesh]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Thu, 22 Jan 2026 10:01:16 GMT</pubDate>
            <atom:updated>2026-01-22T10:01:16.490Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Query, insert, and copy data from MSSQL — no ODBC, no JDBC, no external drivers</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fscirlZ_6Jz7MhwepVmrXA.png" /></figure><h3><strong>The Motivation</strong></h3><p>DuckDB has excellent extensions for PostgreSQL and MySQL that provide seamless, native integration. But many in the community have been asking: what about Microsoft SQL Server?</p><p>Existing solutions like ODBC and ADBC (notably from Queryfarm) are powerful and universal. They work across many databases and platforms. But universality comes with trade-offs: you need to install and configure drivers, manage connection strings, and accept the overhead that any abstraction layer introduces.</p><p>We wanted something simpler — the same native experience that PostgreSQL and MySQL users already enjoy. Direct TDS protocol communication with SQL Server, no external dependencies, no driver setup headaches.</p><p>In our case, this also enables SQL Server as a first-class data source in <a href="https://hugr-lab.github.io/"><strong>Hugr</strong></a>, our open-source data mesh platform (more on that below).</p><p>The result is <strong>mssql-extension</strong> — a DuckDB community extension that speaks native TDS protocol directly to SQL Server.</p><h3><strong>What Makes This Different</strong></h3><p>This extension implements the TDS (Tabular Data Stream) protocol natively. This means:</p><ul><li><strong>Zero external dependencies</strong> — no driver installations, no environment variables, no connection string nightmares</li><li><strong>Full TLS/SSL support </strong>— secure connections out of the box</li><li><strong>Connection pooling</strong> — built-in pool management for concurrent workloads</li><li><strong>Filter and projection pushdown</strong> — queries are optimized at the SQL Server level, not just fetched and filtered locally</li><li><strong>True catalog integration</strong> — browse schemas and tables with DuckDB’s standard functions</li></ul><h3><strong>Getting Started</strong></h3><p>Installation is straightforward:</p><pre>FORCE INSTALL mssql FROM community;<br>LOAD mssql;</pre><p><strong>Connecting to SQL Server</strong></p><p>You have several options for connecting. The simplest is using a URI:</p><pre>ATTACH &#39;mssql://sa:YourPassword123@localhost:1433/master&#39; AS sqlserver (TYPE mssql);</pre><p>For production environments, use secrets to keep credentials secure:</p><pre>CREATE SECRET my_sqlserver (<br>    TYPE mssql,<br>    host &#39;sql-server.example.com&#39;,<br>    port 1433,<br>    database &#39;ProductionDB&#39;,<br>    user &#39;app_user&#39;,<br>    password &#39;SecurePassword&#39;,<br>    use_encrypt true<br>);<br><br>ATTACH &#39;&#39; AS sqlserver (TYPE mssql, SECRET my_sqlserver);</pre><p><strong>Querying Data</strong></p><p>Once attached, SQL Server tables are accessible using familiar three-part naming:</p><pre>-- Browse available schemas<br>SELECT schema_name <br>FROM duckdb_schemas() <br>WHERE database_name = &#39;sqlserver&#39;;<br><br>-- List tables in the dbo schema<br>SELECT table_name <br>FROM duckdb_tables() <br>WHERE database_name = &#39;sqlserver&#39; <br>  AND schema_name = &#39;dbo&#39;;<br><br>-- Query with full pushdown optimization<br>SELECT customer_id, name, email, created_at<br>FROM sqlserver.dbo.customers<br>WHERE status = &#39;active&#39;<br>  AND created_at &gt;= &#39;2024-01-01&#39;<br>LIMIT 1000;</pre><p>The extension pushes filters, column selections, and limit clauses directly to SQL Server. Your database does the heavy lifting, and only the required data travels over the network.</p><p><strong>Supported Pushdown Operations</strong></p><ul><li>Equality and comparisons: <em>=, &gt;, &lt;, &gt;=, &lt;=, &lt;&gt;, BETWEEN</em></li><li>IN clauses: <em>column IN (val1, val2, …)</em></li><li>NULL checks: <em>IS NULL, IS NOT NULL</em></li><li>Logical operators: <em>AND, OR</em></li><li>Date functions: <em>year(), month(), day()</em></li></ul><p><strong>DDL Operations</strong></p><p>The extension supports standard DuckDB DDL syntax, automatically translating to T-SQL:</p><pre>-- Standard DuckDB syntax - automatically translated to T-SQL<br>CREATE TABLE sqlserver.dbo.events (<br>    id INTEGER,<br>    event_type VARCHAR,<br>    payload VARCHAR,<br>    created_at TIMESTAMP<br>);<br><br>-- For SQL Server-specific features like identity columns or constraints, use `mssql_exec()`:<br><br>SELECT mssql_exec(&#39;sqlserver&#39;, &#39;<br>    CREATE TABLE dbo.products (<br>        id INT IDENTITY(1,1) PRIMARY KEY,<br>        sku NVARCHAR(50) NOT NULL UNIQUE,<br>        name NVARCHAR(200) NOT NULL,<br>        price DECIMAL(10,2) DEFAULT 0.00,<br>        created_at DATETIME2 DEFAULT GETDATE()<br>    )<br>&#39;);<br><br>-- Add a column<br>ALTER TABLE sqlserver.dbo.events ADD COLUMN processed BOOLEAN;<br><br>-- Rename a column<br>ALTER TABLE sqlserver.dbo.events RENAME COLUMN payload TO data;<br><br>-- Drop a table<br>DROP TABLE sqlserver.dbo.old_table;<br><br>-- Create a schema<br>CREATE SCHEMA sqlserver.analytics;</pre><p><strong>Inserting Data</strong></p><p>The extension supports INSERT with automatic batching for large datasets:</p><pre>-- Single row insert<br>INSERT INTO sqlserver.dbo.events (event_type, payload)<br>VALUES (&#39;user_signup&#39;, &#39;{&quot;user_id&quot;: 123}&#39;);<br><br>-- Bulk insert from local data<br>INSERT INTO sqlserver.dbo.events (event_type, payload, created_at)<br>SELECT event_type, data, timestamp<br>FROM local_events_staging;</pre><p><strong>INSERT with RETURNING</strong></p><pre>INSERT INTO sqlserver.dbo.products (sku, name, price)<br>VALUES (&#39;SKU-001&#39;, &#39;Widget Pro&#39;, 29.99)<br>RETURNING id, sku, created_at;</pre><p>The extension automatically handles identity columns — they’re excluded from INSERT statements and returned via RETURNING.</p><p><strong>Copying Data from MSSQL to DuckDB</strong></p><p>Here’s where it gets powerful. You can copy data directly from SQL Server into DuckDB’s native storage:</p><pre>-- Copy entire table to local DuckDB<br>CREATE TABLE local_customers AS <br>SELECT * FROM sqlserver.dbo.customers;<br><br>-- Copy with transformation<br>CREATE TABLE customer_summary AS<br>SELECT <br>    region,<br>    COUNT(*) as customer_count,<br>    SUM(lifetime_value) as total_value<br>FROM sqlserver.dbo.customers<br>GROUP BY region;<br><br>-- Copy to Parquet files<br>COPY (SELECT * FROM sqlserver.dbo.orders WHERE year(order_date) = 2024)<br>TO &#39;orders_2024.parquet&#39;;<br><br>-- Copy to partitioned dataset<br>COPY (SELECT * FROM sqlserver.dbo.events)<br>TO &#39;events&#39; (FORMAT PARQUET, PARTITION_BY (event_date));</pre><p><strong>Beyond DuckDB: DuckLake, Iceberg, LanceDB</strong></p><p>The real power comes from DuckDB’s ecosystem. With additional DuckDB extensions, you can write directly to:</p><ul><li><strong>DuckLake</strong> — versioned data lake with time travel</li><li><strong>Apache Iceberg</strong> — open table format for analytics</li><li><strong>LanceDB</strong> — vector database for ML workloads</li></ul><p>This turns SQL Server into a source for modern data lakehouse architectures without complex ETL pipelines.</p><p><strong>Cross-Database Joins</strong></p><p>Join SQL Server data with local DuckDB tables or other attached databases:</p><pre>-- Join MSSQL customers with local analytics<br>SELECT <br>    c.customer_id,<br>    c.name,<br>    a.segment,<br>    a.predicted_churn<br>FROM sqlserver.dbo.customers c<br>JOIN local_analytics.customer_segments a <br>    ON c.customer_id = a.customer_id<br>WHERE a.predicted_churn &gt; 0.7;</pre><p><strong>Configuration Options</strong></p><pre>-- Increase connection pool for high concurrency<br>SET mssql_connection_limit = 20;<br><br>-- Adjust batch size for large inserts<br>SET mssql_insert_batch_size = 500;<br><br>-- Enable detailed statistics for query optimization<br>SET mssql_statistics_level = 2;</pre><h3><strong>Roadmap</strong></h3><p>The extension is under active development. Planned features include:</p><ul><li><strong>UPDATE/DELETE</strong> — DML support with primary key-based row identification</li><li><strong>Transactions</strong> — BEGIN/COMMIT/ROLLBACK with savepoints</li><li><strong>CTAS</strong> — CREATE TABLE AS SELECT with optimized execution</li><li><strong>MERGE/UPSERT</strong> — Insert-or-update using SQL Server MERGE</li><li><strong>BCP/COPY</strong> — High-throughput bulk insert via native BCP protocol for 10M+ rows</li></ul><p>Contributors are welcome! Whether it’s bug reports, feature requests, or code contributions — we’d love your help.</p><h3><strong>Why We Built This for Hugr</strong></h3><p><a href="https://hugr-lab.github.io/"><strong>Hugr</strong></a> is a GraphQL-based data mesh platform that unifies data sources through a single API. With the mssql-extension, SQL Server joins PostgreSQL, MySQL, and other sources as a first-class citizen in the data mesh.</p><p>For organizations with mixed database environments, this means:</p><ul><li>No data movement required for cross-database analytics</li><li>Consistent API layer regardless of underlying database</li><li>Leveraging DuckDB’s analytical engine across all sources</li></ul><h3><strong>Resources</strong></h3><ul><li>DuckDB: <a href="https://duckdb.org/">duckdb.org</a></li><li>GitHub: <a href="https://github.com/hugr-lab/mssql-extension">github.com/hugr-lab/mssql-extension</a></li><li>Hugr Platform: <a href="https://hugr-lab.github.io/">hugr-lab.github.io</a></li><li>Query Farm (20+ DuckDB Extensions, Millions of Downloads): <a href="https://query.farm/">query.farm</a></li></ul><p>If you find this useful, <strong>a star on GitHub really helps</strong> ⭐ — it motivates us and helps others discover the project.</p><p>We welcome contributions, bug reports, and feedback. If you’re running SQL Server and want native DuckDB integration, give it a try and let us know how it works for your use case.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d069bf49d8d2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Making Your Service Data Accessible for Analysis in DuckDB]]></title>
            <link>https://medium.com/@gribanov.vladimir/making-your-service-data-accessible-for-analysis-in-duckdb-1c22d578be59?source=rss-670032929ddc------2</link>
            <guid isPermaLink="false">https://medium.com/p/1c22d578be59</guid>
            <category><![CDATA[apache-arrow]]></category>
            <category><![CDATA[golang]]></category>
            <category><![CDATA[data-mesh]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Vladimir Gribanov]]></dc:creator>
            <pubDate>Tue, 30 Dec 2025 23:44:00 GMT</pubDate>
            <atom:updated>2025-12-30T23:44:00.128Z</atom:updated>
            <content:encoded><![CDATA[<p>How to expose any data source to DuckDB using Apache Arrow Flight and Go.</p><p><strong>The Problem</strong></p><p>You have data locked in your services: user analytics in Redis, product catalogs in PostgreSQL, events in Kafka, metrics in InfluxDB, blockchain transactions, trading data. Your analysts want to query it all with SQL, but:</p><ul><li><strong>Direct database access is risky</strong> — credentials, security, schema coupling</li><li><strong>Data exports are stale — </strong>CSV dumps are outdated before they land</li><li><strong>ETL pipelines are expensive</strong> — infrastructure, maintenance, data duplication</li><li><strong>APIs don’t speak SQL</strong> — REST/GraphQL can’t do JOINs across services</li><li><strong>Custom functions are locked away</strong> — geocoding, weather lookups, calculations live in your backend</li></ul><p>What if analysts could query your service data directly from DuckDB, with real-time access, without exposing your internals?</p><p><strong>The Solution</strong></p><p><strong>Apache Arrow Flight</strong> is a high-performance protocol for transferring columnar data. <a href="https://airport.query.farm/"><strong>DuckDB Airport extension</strong></a> lets <a href="https://duckdb.org/">DuckDB</a> attach to any Flight server as a remote database.</p><p><a href="https://github.com/hugr-lab/airport-go"><strong>airport-go</strong></a> is a Go package that makes building Flight servers simple:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fvtcW8CZ88OllQWvyqv2ZA.png" /></figure><p>Instead of moving data to analysts, you bring the query interface to your data.</p><p><strong>Use Cases</strong></p><p><strong>1. Analytics on Operational Data</strong></p><p>Expose your PostgreSQL replica to analysts without giving them direct access:</p><ul><li>Query production-like data with familiar SQL</li><li>Apply row-level security based on user identity</li><li>No credential sharing, no schema coupling</li></ul><p><strong>2. Crypto and Blockchain Data</strong></p><p>Expose trading data, wallet balances, transaction history:</p><ul><li>Real-time market data from exchanges</li><li>On-chain analytics without running full nodes</li><li>Cross-chain queries with unified SQL interface</li></ul><p><strong>3. Expose Domain Functions</strong></p><p>Make your backend capabilities available as SQL functions:</p><ul><li><strong>Geocoding</strong>:<br><em>SELECT geoservices.geocoder.GEOCODE(address) FROM locations</em></li><li><strong>Weather</strong>:<br><em>FROM weatherservices.owm.GET_WEATHER(‘NYC’, ‘2024–01–15’)</em></li><li><strong>Realtime data</strong>: <br><em>FROM iotservice.sensordata.GET_SENSOR_READINGS([‘sensor1’, ‘sensor2’], ‘2024–01–01’, ‘2024–01–31’)</em></li><li><strong>ML Scoring</strong>:<br><em>FROM PREDICT_CHURN(SELECT id FROM users)</em></li><li><strong>Calculations</strong>: Custom business logic as scalar or table functions</li></ul><p><strong>4. Real-Time Dashboards</strong></p><p>Connect Grafana or Metabase to DuckDB, which queries your Flight server:</p><ul><li>Fresh data on every query</li><li>No ETL delays</li><li>Combine data from multiple services in one dashboard</li></ul><p><strong>5. Data Mesh / Data Products</strong></p><p>Each team exposes their domain data as a Flight endpoint:</p><ul><li>Teams own their data contracts (Arrow schemas)</li><li>Consumers query via standard SQL</li><li>Decentralized data, centralized access</li></ul><p><strong>6. Large Dataset Exploration</strong></p><p>Expose billion-row tables with filter pushdown:</p><ul><li>WHERE clauses execute on your backend</li><li>Only matching rows transfer over the network</li><li>Interactive queries on massive datasets</li></ul><p><strong>Simple Example</strong></p><p>Let’s create a Flight server that exposes a users table:</p><pre>package main<br><br>import (<br>    &quot;context&quot;<br>    &quot;log&quot;<br>    &quot;net&quot;<br><br>    &quot;github.com/apache/arrow-go/v18/arrow&quot;<br>    &quot;github.com/apache/arrow-go/v18/arrow/array&quot;<br>    &quot;github.com/apache/arrow-go/v18/arrow/memory&quot;<br>    &quot;google.golang.org/grpc&quot;<br><br>    &quot;github.com/hugr-lab/airport-go&quot;<br>    &quot;github.com/hugr-lab/airport-go/catalog&quot;<br>)<br><br>func main() {<br>    // Define table schema<br>    schema := arrow.NewSchema([]arrow.Field{<br>        {Name: &quot;id&quot;, Type: arrow.PrimitiveTypes.Int64},<br>        {Name: &quot;name&quot;, Type: arrow.BinaryTypes.String},<br>        {Name: &quot;email&quot;, Type: arrow.BinaryTypes.String},<br>    }, nil)<br><br>    // Define scan function - this is where you fetch from your data source<br>    scanUsers := func(ctx context.Context, opts *catalog.ScanOptions) (array.RecordReader, error) {<br>        // In real code: query your database, API, cache, etc.<br>        // Use the opts.Filter to reduce amount of data fetched.<br>        // Use opts.Columns to return only projected columns values.<br>        builder := array.NewRecordBuilder(memory.DefaultAllocator, schema)<br>        defer builder.Release()<br><br>        builder.Field(0).(*array.Int64Builder).AppendValues([]int64{1, 2, 3}, nil)<br>        builder.Field(1).(*array.StringBuilder).AppendValues([]string{&quot;Alice&quot;, &quot;Bob&quot;, &quot;Charlie&quot;}, nil)<br>        builder.Field(2).(*array.StringBuilder).AppendValues([]string{&quot;alice@example.com&quot;, &quot;bob@example.com&quot;, &quot;charlie@example.com&quot;}, nil)<br><br>        record := builder.NewRecord()<br>        return array.NewRecordReader(schema, []arrow.Record{record})<br>    }<br><br>    // Build catalog with one schema and one table<br>    cat, _ := airport.NewCatalogBuilder().<br>        Schema(&quot;app&quot;).<br>        SimpleTable(airport.SimpleTableDef{<br>            Name:     &quot;users&quot;,<br>            Schema:   schema,<br>            ScanFunc: scanUsers,<br>        }).<br>        Build()<br><br>    // Start gRPC server<br>    grpcServer := grpc.NewServer()<br>    airport.NewServer(grpcServer, airport.ServerConfig{Catalog: cat})<br><br>    lis, _ := net.Listen(&quot;tcp&quot;, &quot;:50051&quot;)<br>    log.Println(&quot;Flight server listening on :50051&quot;)<br>    grpcServer.Serve(lis)<br>}</pre><p>Now query from DuckDB:</p><pre>-- Install Airport extension (once)<br>INSTALL airport FROM community;<br>LOAD airport;<br><br>-- Attach your Flight server<br>ATTACH &#39;&#39; AS myservice (TYPE AIRPORT, LOCATION &#39;grpc://localhost:50051&#39;);<br><br>-- Query!<br>SELECT * FROM myservice.app.users WHERE name LIKE &#39;A%&#39;;</pre><p>That’s it. Your service data is now queryable with SQL.</p><h3><strong>Extended Features</strong></h3><p>airport-go supports a rich set of features for production use:</p><ul><li><strong>Authentication — </strong>Protect your data with bearer token authentication. Users pass tokens via DuckDB secrets (see: <a href="https://github.com/hugr-lab/airport-go/examples/auth">examples/auth</a>).</li><li><strong>Filter Pushdown — </strong>For large tables, push WHERE clauses to your backend. Parse DuckDB’s filter JSON and encode it to SQL for your data source. Query billion-row tables in milliseconds (see: <a href="https://github.com/hugr-lab/airport-go/examples/filter">examples/filter</a>).</li><li><strong>DML Operations (INSERT, UPDATE, DELETE) — </strong>Support writable tables with `InsertableTable`, `UpdatableTable`, and `DeletableTable` interfaces. (see: <a href="https://github.com/hugr-lab/airport-go/examples/dml">examples/dml</a>).</li><li><strong>DDL Operations (CREATE SCHEMA, CREATE TABLE, ALTER TABLE, …) — </strong>Support dynamic schemas with `DynamicCatalog`, `DynamicSchema`, and `DynamicTable` interfaces. Create tables, add/remove columns, rename fields (see: <a href="https://github.com/hugr-lab/airport-go/examples/ddl">examples/ddl</a>).</li><li><strong>Scalar Functions — </strong>Expose custom calculations: <em>SELECT myservice.app.my_function(col1, col2) FROM tabl</em>e. Implement the `ScalarFunction` interface with your business logic (see: <a href="https://github.com/hugr-lab/airport-go/examples/functions">examples/functions</a>)</li><li><strong>Table Functions — </strong>Return dynamic result sets: <em>FROM myservice.app.my_table_func(‘2024–01’)</em>. Schema can depend on parameters. Or accept table data as argument: <em>FROM</em> <em>myservice.app.my_table_func2(FROM users) </em>(see: <a href="https://github.com/hugr-lab/airport-go/examples/functions">examples/functions</a>).</li><li><strong>Time Travel Queries — </strong>Support point-in-time queries via `TimePoint` in scan options. Query data as it existed at a specific timestamp or version (see: <a href="https://github.com/hugr-lab/airport-go/examples/timetravel">examples/timetravel</a>).</li><li><strong>Transaction Support — </strong>Coordinate multi-statement transactions with `TransactionManager` interface. Support BEGIN, COMMIT, ROLLBACK (see: <a href="https://github.com/hugr-lab/airport-go/examples/dml">examples/dml</a>).</li><li><strong>Column Statistics — </strong>Provide statistics for query optimization via `StatisticsTable` interface. Help DuckDB make better query plans.</li></ul><p><strong>Performance Tips</strong></p><ol><li><strong>Batch size</strong>: Return 10,000–100,000 rows per batch for optimal throughput</li><li><strong>Streaming</strong>: Don’t load all data into memory; stream batches from your source</li><li><strong>Filter pushdown</strong>: Try to apply filters for tables over 100K rows</li><li><strong>Connection pooling</strong>: Reuse database connections across scan calls</li><li><strong>Context cancellation</strong>: Respect `ctx.Done()` to stop work when clients disconnect</li></ol><p><strong>Getting Started</strong></p><pre>go get github.com/hugr-lab/airport-go@latest</pre><p><strong>Links</strong></p><ul><li>GitHub: <a href="https://github.com/hugr-lab/airport-go">github.com/hugr-lab/airport-go</a></li><li>DuckDB Airport Extension: <a href="https://airport.query.farm/">airport.query.farm</a></li><li>Apache Arrow Flight: <a href="https://arrow.apache.org/docs/format/Flight.html">arrow.apache.org/docs/format/Flight.html</a></li></ul><p>Stop moving data. Start querying it where it lives. airpot-go is developed by <a href="https://hugr-lab.github.io/">Hugr-lab</a>. <a href="https://hugr-lab.github.io/"><strong>Hugr is an Open Source Data Mesh Platform and high-performance GraphQL Backend based on DuckDB</strong></a><strong>.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/632/1*xoi4ERQO4uUePMETHfVqvA.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1c22d578be59" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>