All you need is Onelake

Yes, it’s clickbait. Of course you need more, but it’s hard to get anyone to read a blog these days 🙂

I built OneLake Studio, a free VS Code extension that treats your Fabric tenant as one catalog. Every table gets one unique name, lakehouse.workspace.item.schema.table (or warehouse.…), and every semantic model is semanticmodel.workspace.model. You explore all of it with SQL and can join across items and workspaces. It’s read only, and what you’ve opened stays cached on your machine. A semantic model (a Power BI .bim or an Apache Ossie .yaml) is a view over its tables. You can pivot it and publish it as a very basic Fabric app. The theory: if you like the app, your coding agent can turn it into a full-blown one.

Why VS Code? It handles sign-in for me (you use the account VS Code already has), and it’s where I spend all my time anyway. Authentication is still harder than it should be. I dream of a world where I never have to deal with it.

Here’s the tour: browse OneLake, look at a table, open a semantic model, explore it, see how its DAX becomes SQL, then publish it as a Fabric app your colleagues open in their browser. That last step is the point of the whole exercise. At the end is a short look under the hood.

A word of warning. This is a fun personal project, not an official Microsoft one. It computes a model’s numbers with its own engine, and matching Power BI 100% of the time is not a goal: by design it already differs, for example in case sensitivity and in how text is trimmed. The DAX compiler, dax-sql, was contributed by N.S. Devaraj.

1. Install and sign in

In VS Code, open Extensions (Ctrl+Shift+X), search for OneLake Studio and click Install. Click its icon in the activity bar, then Sign in to OneLake.

2. Browse OneLake

All your workspaces show up in one tree. Pin the ones you use most so they stay at the top. Each workspace lists its lakehouses, its Semantic models and its Apps. Mirrored databases show up too.

3. Look at a table

Click a table. Its icon tells you whether it was written as Delta or Iceberg, and it opens on three tabs.

Stats comes straight from the table’s metadata, before any data is read. For a Delta table, that’s its Delta log: version, rows, files, size, compression, last write, and features.

History lists what happened to the table, newest first: each commit, its operation, the rows and files it added, and which engine wrote it. Nothing is read until you open the tab.

Data shows the first rows. The query is right there to edit if you want more.

Every table, Delta or Iceberg, is read through Iceberg metadata. OneLake translates Delta to Iceberg v2 by default, as a background service, and you don’t need to do anything. When a table has no Iceberg metadata, OneLake Studio quotes Fabric’s conversion log so you can see why.

4. Open a semantic model

Under Semantic models, click one. In the background, OneLake Studio saves it into its lakehouse, and a check mark shows next to it in the tree. From then on it opens at once.

The Model page shows its tables and how they relate.

An Apache Ossie .yaml in a lakehouse’s Files opens the same way.

5. Explore it

Switch to Pivot. Drag fields onto Rows, Columns, Values and Filters, then pick a view: table, bar, line, area, heatmap and more.

Show SQL turns the pivot into a query you can edit and run. You ask the model with plain SQL. Pick your columns, wrap a measure in AGG(), and select FROM model. For example, on the NYC taxi model:

SELECT "dim_date"."year", "dim_zone"."Borough", AGG("Total Amount")
FROM model
GROUP BY ALL

AGG("Total Amount") is the measure exactly as it was defined in the model, in DAX. It’s computed in the context of each row (that year and that borough, plus any WHERE you add), just like a visual in Power BI. There’s no new syntax to change a measure on the fly, to add a filter or remove one, and I’m not interested in inventing one. If you need a different calculation, define it in the model.

Show SQL also shows where the time went.

Click Tree to see what actually ran. You never wrote a join, but the model knows how its tables relate, so the trips are joined to the zones on the location and to the calendar on the date. Only the columns the query needs are read.

6. DAX to SQL

Every measure goes through dax-sql, a compiler that turns DAX into plain SQL, which DuckDB then runs. Here it is on something less trivial than a SUM: each borough’s share of the year’s total.

DEFINE MEASURE fct_trips[Share of Borough] =
DIVIDE([Total Amount], CALCULATE([Total Amount], ALL(dim_zone[Borough])))
EVALUATE SUMMARIZECOLUMNS(dim_date[year], dim_zone[Borough], "Share", [Share of Borough])

That compiles to this (only the line breaks are mine):

WITH f3 AS MATERIALIZED (
SELECT j4."year" AS "g0", j5."Borough" AS "g1", SUM(t2."total_amount") AS "a0"
FROM "mart"."fct_trips" AS t2
LEFT JOIN "mart"."dim_date" AS j4 ON j4."date" = t2."pickup_date"
LEFT JOIN "mart"."dim_zone" AS j5 ON j5."LocationID" = t2."PULocationID"
GROUP BY j4."year", j5."Borough"),
f7 AS (SELECT f3.g0 AS g0, SUM(f3.a0) AS a0 FROM f3 GROUP BY f3.g0)
SELECT CAST(q10."year" AS BIGINT) AS "year", q10."Borough" AS "Borough",
CAST(q10."Share" AS DOUBLE) AS "Share"
FROM (SELECT s9."year", s9."Borough", s9."Share"
FROM (SELECT *
FROM (SELECT k1."year" AS "year", k1."Borough" AS "Borough",
(f3.a0 / NULLIF(f7.a0, 0)) AS "Share"
FROM (SELECT f3.g0 AS "year", f3.g1 AS "Borough" FROM f3) AS k1
LEFT JOIN f3 ON f3.g0 IS NOT DISTINCT FROM k1."year"
AND f3.g1 IS NOT DISTINCT FROM k1."Borough"
LEFT JOIN f7 ON f7.g0 IS NOT DISTINCT FROM k1."year") AS z8
WHERE z8."Share" IS NOT NULL) AS s9) AS q10

A few things to notice:

  • ALL(dim_zone[Borough]) doesn’t scan the trips again. f3 is the total by year and borough, and f7 adds the boroughs up for each year. The fact table is read once.
  • DIVIDE divides by NULLIF(…, 0), so a zero total gives a blank, not an error.
  • Blank rows are dropped, as SUMMARIZECOLUMNS does.

7. Publish it as a Fabric app

Click Export to Fabric. Confirm where the local copy goes and click Export.

Once it’s published, your colleagues get the same pages in their browser, without VS Code.

The app lives in your workspace, protected like any other Fabric item, so only people with access can open it (it shows under Apps in the tree, with a lock). It reads the model where it lives, so nothing is copied and the data is current. And a copy of the app stays in your folder, so you can treat it as a starting point: change it, then export again.

How it works

The same pages (Model, Pivot, Logs) run in two places, on DuckDB both times.

  • In VS Code, the extension runs a native DuckDB 2.0 inside VS Code itself. It’s multithreaded and reads OneLake directly, with the token from your VS Code sign-in. The panel you see is only a view.
  • In the Fabric app, there’s no VS Code, so the engine is DuckDB-WASM 1.5.6, running in the viewer’s browser. The app is a static page plus one small function. The function runs in Fabric: it reads the model from the lakehouse and tells the page where each table is. The page downloads the Parquet files it needs, and DuckDB reads them from the browser’s local storage. SQL typed in the app only sees those Parquet files and can’t read anything else.

DuckDB in the browser: single-threaded

DuckDB-WASM runs single-threaded here, so one query always runs on one core. A big scan is noticeably slower than the same query in VS Code. To keep the page responsive, the app runs a pool of two engines, each in its own web worker, fed by one queue. When a page draws several visuals, two queries run side by side, and a slow one holds up only its own engine. (I tried three. It wasn’t faster.)

I also tried a multithreaded build. It turned out to be more complex than it looks, so for now it stays single-threaded, and the pool of two does the job.

The cache

Iceberg files never change once they’re written, so almost everything can be kept.

  • In VS Code, listings, table metadata and first rows are kept on disk. They’re shown at once and refreshed in the background. Table data goes into DuckDB’s disk cache (64 GB by default, per account), so a table you’ve read before comes from your machine, even after a restart. Clear Cached OneLake Data empties it.
  • In the browser, there are four layers:
    • The function’s answer (where the tables are), kept for about an hour. A viewer calls the function about once an hour, not on every load.
    • The Iceberg manifests, so a new snapshot reads only the manifests it added.
    • The Parquet files, in the browser’s private file system (OPFS), up to 64 GB, shared by both engines and every tab. This is why the app needs Chrome or Edge 121 or later.
    • Query results, stored as Arrow and keyed by the SQL and the tables’ current snapshot. A reload answers every query it has already seen without running the engine. New data means a new key, so you never see a stale result.

Each app has its own cache, in each browser. The first visit pays for the download. Later visits are fast.

Limitations, by design

Everything runs on the client: the engine on your machine (or in your colleague’s browser, for the Fabric app) reads the data straight from OneLake.

  • Egress fees can become a problem. Every query pulls data out of OneLake to wherever the client is, and with big tables or users far from the data, that can cost money.
  • Row- and column-level security (RLS/CLS) aren’t supported. OneLake now has a table read API (in preview) that returns Apache Arrow with RLS and CLS applied. I’ve tested it with Python and it works as expected, but I haven’t tried it with WebAssembly yet.
  • It’s for small data. Don’t use it to download 10 GB tables.
  • Correct SQL is not the same as a good plan. Even when the generated SQL is right in theory, getting a well-optimized plan out of it is not obvious at all. A measure becomes CTEs, re-aggregations and joins that nobody would write by hand, and the engine has to see through them. Some queries will be slower than they should be.
  • DAX to SQL comes with no guarantee. dax-sql, contributed by N.S. Devaraj, compiles DAX as faithfully as it can, but there’s no guarantee that a result is correct, or that a given function or piece of syntax is supported. Some DAX isn’t supported at all.

So it’s not meant for production or any serious setup. But it’s an interesting experiment in what a Fabric app can do on its own, IMHO.

Funny enough, I wrote about this back in 2021: BI tools need a SQL generator that composes measures the way Power BI does, including the ones that ignore your filters. I had the idea, but obviously I couldn’t build it. Five years later, with AI, I could. If anything, this project is a testament to how far AI has come.

Try it

Install OneLake Studio from the VS Code Marketplace. It runs on Windows, macOS and Linux.

The Enterprise Context Layer

Edit : I vibe coded one, so you dont need to read this whole text

https://djouallah.github.io/fabric-context-layer

As a data analyst, I have been following the progress of LLMs in analytics, and although there has been some progress in accuracy and in the kinds of questions you can ask, overall, it hasn’t had the impact I was hoping for. That’s a totally different story for coding and data engineering, where AI is already changing daily workflows. The core idea is simple: if something is falsifiable, AI can be useful.

In the last couple of months, I keep hearing about some new terms which I never really understood: knowledge graphs, ontology (Andre, who is someone I respect a lot calls it the Enterprise brain), and so on. But I think there may be something genuinely interesting here for analytics.

There is so much data in an enterprise, and so much of it is still underused. Talking about a context layer is not new, and there is plenty of literature on it, but it is rare to see it from the end user perspective.

There are mainly two approaches. One requires modelling, which instinctively bothers me because it expects me to do more work upfront. Maybe it is an age thing, but I am not particularly excited about learning a new mental model. I have been doing semantic modelling for nearly a decade and I am quite happy with it.

The other approach is more interesting to me because it doesn’t ask users to create something new. It works with what they already have: semantic models, data pipelines, the data itself, unstructured data, PDFs, folders, and so on. It figures out what already exists and how the pieces relate to each other. When you ask a question, it helps route you to the right place.

There is no magic here. To get a correct answer, you still need the hard work that has already been done: good semantic modelling, good metadata, good data, and so on.

For me, a system that can simply pick the right dashboard to use is already extremely useful. In a data platform, sometimes we don’t even know where to ask a question or which dashboard is more useful than another, unless we ask an analyst or a colleague. In a sense, this is just formalising what already happens in real life.

Even if the system says, “This is the question people are asking, and I don’t know of a good answer,” that’s already very useful feedback for analysts.

And one may argue, what if two semantic models authored by different departments don’t have the same definition? A naive answer would be to create one giant semantic model that unifies everything, which will never happen because this is fundamentally a human and organisational problem, not a technical one.

Maybe the better approach is simply to formalise what we already do today. Whatever the CEO, manager, or wider organisation is actually using probably makes more sense to prioritise, regardless of whether it is objectively the truth. We can imagine the system using signals: a certified dataset might carry more weight, or perhaps the author of a dataset has more credibility.

This is not a problem specific to analytics. Ask two news channels to describe the same event and you will often get different interpretations.

But to be super clear, the numbers themselves should never be non-deterministic. The measures are authored in exisitng semantic models, not made up on the fly

The bigger idea is that the context could improve with usage. Data platforms already have an enormous amount of telemetry. They know who uses what, which reports are popular, which datasets are trusted, and which questions people keep asking. There is nothing particularly new about this data. What is new is the possibility of using it to help both agents and humans find answers based on what the organisation actually uses and trusts.

In other words, the context isn’t something you build once and then leave alone. It could continuously evolve based on how the organisation actually works.

Users, I think, will not care how those signals are calculated. Users are selfish (speaking for myself here) and care only whether it is useful or not. Companies may want a say in what gets prioritised and how it works, or they may want to export that knowledge somewhere else. But I suspect they will mostly be happy if it just works.

I think the reason this may work now is that AI is becoming good enough for a lot of things. What it needs is not necessarily more intelligence, but better grounding in the knowledge that already exists inside an organisation.

I came from the construction industry, and we had this thing called delay analysis. It is basically an attempt to prove that a project delay is not the contractor’s fault. Maybe the client kept changing requirements, failed to approve drawings on time, or introduced other changes that affected the schedule.

Companies spend a lot of money on consultants who come in, read thousands of documents and pieces of correspondence, and try to rebuild a simplified model of what happened.

I know this is a very specific example, but I think AI should be able to solve this surprisingly well.

I can imagine a data platform where a supervisor asks, “What’s going on? Why are we late?” and gets an answer based on the actual project data, with references to the relevant dashboard, emails, RFIs, documents, and other evidence.

That is the kind of thing I would pay money for.

Maybe that’s what an enterprise context layer really is: not another model people have to maintain, but a way of making the knowledge an organisation already has actually useful. I am not saying this is a solved problem, or even that it will work in practice, but I think it is something worth building.

duckrun

duckrun is a package I built using AI exclusively, to solve pain points I hit when using Fabric Python notebooks. I like DuckDB very much, but I was tired of manually discovering table names every time and writing long Python deltalake code just to write a Delta table, so I combined those two packages under one helper package to make my workflow smoother. Recently I discovered how awesome dbt is, so why not add a dbt adapter too. Then someone trolled me about the lack of snapshot isolation when doing read-modify-write on the same table. Luckily DuckDB now exposes the read version in delta_scan (before, you had to do the weird attach thing), so I have something that might actually be useful. The rest of the blog is written by AI. It wrote my code, so I don’t see the issue; it’ll even write my blog.

I also added a web page with some projects I built: dbt projects I ported, and some non-trivial SQL statements with proper snapshot isolation.

https://djouallah.github.io/duckrun

So yes, it’s a hack, until Iceberg matures or we get a better Delta write story in DuckDB.

At its core, duckrun is a four-part split: DuckDB executes the SQL, Arrow streams the result, delta-rs commits it to Delta, and dbt (optionally) orchestrates the whole DAG.

It’s storage-agnostic, running anywhere DuckDB and delta-rs can reach: local filesystem, S3, GCS, ADLS, OneLake. In practice I test it on the local filesystem and Microsoft Fabric OneLake. S3 and GCS use the same code path, but I barely touch them, so treat them as untested.

The gap it fills

DuckDB reads Delta well (delta_scan). It does not write it well: its Delta support is blind INSERT only (no UPDATE, DELETE, or MERGE), and its trajectory points at writing through Unity Catalog, which defeats the point of filesystem-native Delta. So if you want DuckDB’s engine but need upserts on Delta tables, there is no single tool that does both today.

ApproachReads DeltaWrites Delta (merge/update/delete)DuckDB SQL engine
DuckDB alone✅❌ (blind INSERT only)✅
delta-rs alone✅✅❌
duckrun✅✅✅

duckrun’s answer is a split:

  • DuckDB runs all SQL and model logic, and reads Delta through delta_scan views.
  • delta-rs handles every write: overwrite, append, merge, delete, update.
  • Arrow bridges the two: a DuckDB relation is streamed to delta-rs over the C-stream interface.
  • Snapshot isolation ties it together: each read pins a Delta version, and each read-modify-write commits against the version it read, so a concurrent commit errors instead of silently clobbering.

That’s the whole architecture. The README calls it glue, and that’s accurate: each layer does only the one thing it’s set up to do.

Two costs come with this. Two engines split one RAM budget with no shared allocator, and the Arrow handoff isn’t zero-copy: DuckDB’s native vector format isn’t Arrow, so each batch is decoded and re-encoded as it streams across the Arrow C Data Interface (a batch-at-a-time ArrowArrayStream, so even a large write never fully materializes in memory). duckrun manages this with a cgroup-aware memory split, sampled per job so it doesn’t get OOM-killed on Fabric/k8s, where DuckDB otherwise sees the whole node. The fractions are of the effective limit: on a merge, 0.3 to DuckDB and 0.6 to delta-rs spill, leaving 0.1 slack; on a plain write, 0.85 to DuckDB. The split leans toward delta-rs because that’s where the memory goes: profiling a merge attributes ~99% of resident memory to delta-rs and only ~15 MB to DuckDB. Keeping every write behind delta-rs also means the bridge (and the memory juggling) can be deleted the day DuckDB ships a real Delta writer, without touching the read or state model.

connect: read first

connect() is read-only by default, so you can point it at a lakehouse and explore with no chance of an accidental write. Tables are discovered for you, with no manual name bookkeeping:

import duckrun
conn = duckrun.connect("abfss://<ws>@onelake.dfs.fabric.microsoft.com/<lh>/Tables/dbo")
conn.sql("SHOW TABLES").show()
conn.sql("select status, count(*) from orders group by status").show()
df = conn.table("orders").toPandas() # or .toArrow() for a streaming reader
# time travel
from duckrun import DeltaTable
DeltaTable.forName(conn, "orders").history() # newest-first: version, timestamp, operation
conn.read.format("delta").option("versionAsOf", 0).load(".../Tables/dbo/orders").show()

Multiple catalogs: attach

Attach more lakehouses and query across them by three-part name. This is where the data warehouse case shows up: in Fabric a Warehouse is just a write-locked Lakehouse, so you attach it read_only=True next to a writable Lakehouse and join the two:

conn.attach("abfss://…/warehouse.Warehouse/Tables", name="warehouse", read_only=True)
conn.attach("/data/reference", name="local")
conn.sql("""
select *
from warehouse.mart.facts f
join local.dbo.lookup l on l.id = f.id
""").show()

Same code against a local path, s3://, gs://, or az://.

Writing: DML and merge

Opt into writes with read_only=False. Then just write SQL: plain DML routes straight to delta-rs, with no Python deltalake boilerplate:

conn = duckrun.connect("abfss://…/Tables/dbo", read_only=False)
conn.sql("create or replace table clean_orders as select * from orders where amount > 0")
conn.sql("insert into clean_orders select * from late_orders")
conn.sql("update clean_orders set status = 'shipped' where status = 'packed'")
conn.sql("delete from clean_orders where amount = 0")

MERGE works the same way; reference the literal target / source aliases:

conn.sql("""
merge into clean_orders as target
using updates as source
on target.id = source.id
when matched then update set *
when not matched then insert *
""")

…or, if you’d rather build it, the DeltaTable API mirrors Delta’s:

from duckrun import DeltaTable
src = conn.sql("select * from updates")
DeltaTable.forName(conn, "clean_orders").merge(src, "target.id = source.id") \
.whenMatchedUpdateAll().whenNotMatchedInsertAll().execute()

conn.sql accepts CREATE [OR REPLACE] TABLE AS, INSERT, UPDATE, DELETE, ALTER … ADD COLUMN, MERGE, and DROP (a soft tombstone: delta-rs has no drop, so data files persist until you purge them). CREATE TEMP TABLE and CREATE VIEW stay native DuckDB. Things that can’t be honored cleanly, like multi-statement strings or UPDATE … FROM, are rejected rather than silently mishandled.

The surface is small. It mirrors the Delta/DeltaTable API so notebook code reads familiarly, but there is no fluent transform builder and no second SQL engine. Transforms are SQL, run by DuckDB.

Snapshot isolation

This started as someone trolling the project over read-modify-write on the same table, and it turned into the guarantee I rely on most. Honestly, I come from a Dataflow Gen1 background (basically a single writer), so all this concurrency stuff never made much sense to me. In a Lakehouse, where anyone can write to a table, even accidentally, it suddenly becomes a real factor. (Thanks Raki for the harsh feedback, lol.) A lakehouse has no transaction manager and no single-writer guarantee: two pipelines, a double-fired job, or a notebook racing a scheduled run can all commit to the same table. The dangerous shape is read → compute → write: if someone commits in between, a naïve write at HEAD silently overwrites them, a lost update with no error. duckrun’s job is to turn that into a CommitFailedError.

What’s fenced, and what isn’t. delete, update, and merge are read-modify-writes (they read the current rows, compute a change, then commit), so duckrun pins each to the version it read and delta-rs’s OCC validates the commit over (read, HEAD]. A conflicting concurrent commit makes them fail. Plain append and overwrite are not fenced, by design: they match Spark’s SaveMode. An append rebases onto HEAD (appends don’t conflict), and an overwrite is last-writer-wins. So the fence is automatic exactly where a lost update is possible, and absent where it isn’t.

How the OCC works. There’s no lock manager anywhere. A Delta commit is the atomic creation of the next log entry, _delta_log/…{N+1}.json, so if a racing writer already wrote it, your put-if-absent loses and delta-rs raises CommitFailedError. Optimistic, filesystem-native, no coordinator. The gap OCC alone leaves: it only checks the commit instant, not the version you read.

Pinning the version you read, the same whether you write SQL or a DataFrame. A DeltaTable handle captures the version at forName() (call it vB), and every merge() / delete() / update() through it commits against vB, so OCC validates the whole (vB, HEAD] window, not just the instant of the commit. conn.sql("delete …" / "update …" / "merge …") funnels into the exact same engine path, pinned the same way: there is no second code path for SQL. And DuckDB exposing the read version in delta_scan(…, version => vB) (the reason for the duckdb >= 1.5.4 floor) lets the read sit on vB too, so a read-modify-write split across statements still lands on one snapshot. Spark/Delta fences only the commit instant; this fences the version you actually read.

append_if_unchanged / overwrite_if_unchanged are the fenced siblings of plain append/overwrite. I had to coin the terms, because Delta/Spark has no built-in fenced append (you’d hand-write a MERGE for it). They’re a version compare-and-swap: load the table at the version you read and pass max_commit_retries=0 so delta-rs won’t rebase. If anything committed since, the target version is already taken and the commit fails. For the watermark/idempotent-append case this is cheaper than a merge, with no target scan and no key join. (safeappend is the deprecated alias.)

The dbt adapter

A thin wrapper over dbt-duckdb that adds Delta-backed table and incremental materializations; everything else (views, seeds, sources, tests, plugins) is inherited. Point a profile at a lakehouse and dbt run:

my_project:
outputs:
dev:
type: duckrun
root_path: "abfss://<ws>@onelake.dfs.fabric.microsoft.com/<lh>/Tables"

dbt with no catalog. Normally dbt leans on a metastore to know what exists and to resolve {{ this }}, ref(), and is_incremental(); duckrun has none, and state still survives across separate dbt build processes. At run start the adapter discovers Delta tables on disk (glob for local/az/s3/gs; the OneLake DFS REST API for abfss, since DuckDB can’t glob it reliably) and registers each as a delta_scan view named to match dbt’s database.schema.identifier. That view is what makes those references resolve against real Delta tables. Each materialization pre-registers its own {{ this }} view before running, then recreates the view after the delta-rs write, since that write lands a new Delta version and the old view would otherwise point at stale files. The namespace is rebuilt from storage on every run instead of read from a catalog.

Incremental strategies:

StrategyBehavior
merge (default with unique_key)upsert
insertinsert new keys only
append (default without unique_key)blind append
append_if_unchanged / safeappendappend, commit only if version unchanged: cheap, no target scan, errors on conflict
microbatchdelete+insert per event_time window

Compaction and 7-day vacuum run automatically (every run for overwrites; past a file-count threshold for incrementals).

Two limits worth knowing up front. First, writes are single-threaded within a run (in-process delta-rs isn’t thread-safe; cross-process concurrency is fully supported). Second, constraints are enforced at the write boundary, not stored in the table: a contract with not_null columns is checked by a guard query before the write, so a null fails with NOT NULL constraint failed and the prior Delta version is left untouched. Two caveats there: delta-rs can’t persist column constraints into Delta metadata, and timestampNtz columns can’t be written yet.

On versions, duckrun is deliberately conservative because the underlying libraries move fast and break: duckdb >= 1.5.4 (first stable with delta_scan(version => N)) and deltalake == 1.5.0, the first release with MERGE max_spill_size. On Microsoft Fabric, pip install --upgrade and restart the kernel, since the bundled DuckDB is older than the floor.

Testing: the only way to trust AI code

If the AI writes the code, what makes it trustworthy? Not that it compiles, and not that the unit tests are green: an AI will happily write a test that passes for the wrong reason. The only signal I actually trust is integration testing: run a real dbt project end to end and check the tables it lands on real storage.

So that’s where most of the test weight sits. duckrun runs a few hundred tests across 26 files, but the ones that matter most are the 8 integration projects that build for real, most against live Microsoft Fabric OneLake (abfss://), not a mock. To keep them honest, with real models rather than toys I wrote to flatter the adapter, I ported existing dbt projects from the web: other people’s models kept as close to original as I could, with attribution:

  • sde_dbt_tutorial, a port of josephmachado/simple_dbt_project: raw tables → bronze typing → a Delta-backed SCD2 customer snapshot → a merge-incremental clickstream fact → an orders_obt gold mart.
  • coffee, ported from JosueBogran/coffeeshopdatageneratorv2: CSV ingest over https, a deduped SCD2 product dim, a region-partitioned fact, a revenue mart.
  • aemo, my own dbt_fabric_python_delta, built against live OneLake. The full run is published as browsable dbt docs: fct_scada is a 360M-row Delta table you can inspect yourself, not a screenshot.
  • snapshot_pin — a concurrent-writer test that asserts the guarantee above end to end: one writer reads a version, a second commits underneath it, and the first writer’s stale commit is rejected on real storage rather than silently overwriting.
  • plus a TPCH merge/append/overwrite spill benchmark, a connection-API demo on live NYC TLC taxi data, and a multi-catalog lakehouse + warehouse + local join.

The rendered catalogs for these (real Delta stats, row counts, last-modified) are on the project page.

On top of the projects, the adapter runs the official dbt adapter test suite (dbt-tests-adapter, the same conformance suite every dbt adapter is measured against) at 126/135 passing (93%), regenerated on every push to main. The documented failures are deliberate choices: no persistent views in open Delta, and rejecting merge configs that would silently diverge.

The evidence that matters is tables on real storage, hit the same way a user would, not a passing test count and not “the AI said it works.”

What does it mean to build a package you don’t understand?

I should be honest: I don’t understand the code in detail. But that was always true. duckrun is glue over DuckDB and delta-rs (written in C++ and Rust), and I don’t have the slightest idea how those work internally either. Almost nobody who builds on a library understands its guts. So what does “writing a package” actually mean?

For me, two things. Expressing the problem, knowing the pain well enough to say exactly what should happen, and making the design decisions that follow: delta-rs for every write, delta_scan views for reads, snapshot isolation as the contract. The AI writes the code; I own the problem and the shape of the solution.

The third thing is what makes it real: tests, and a lot of them. duckrun runs an extensive unit and integration suite, but I only actually trust it when I see tables land in OneLake. Code that passes locally and code that materializes correctly on real storage are not the same claim.

One trick I’ve learned: use a second agent to verify the first one’s work, not the agent that wrote it. The catch is that AI still cheats to make a test pass: it’ll weaken or game the check even when it plainly knows that isn’t the right thing. I hope that improves. Until it does: don’t trust anything it produces. Verify it against reality.

Text-to-SQL using Local LLMs

TL;DR : The high-end version of this problem is mostly solved. GPT-4 or Claude Opus paired with a mature proprietary semantic layer like Microsoft Power BI’s will handle natural language queries reliably in production, This blog is a much narrower use case: how do small language models (4B parameters, 4GB VRAM) perform when paired with open-source semantic layers? not great, not terrible as of April 2026 😦

Running a 4B model through an immature open-source semantic layer on a laptop GPU is not a production architecture — but it turns out to be a surprisingly useful lens for measuring fundamental SLM progress. How much reasoning capability fits in 4GB? How well do small models follow explicit constraints? Where do they break down under complexity?

Curiosity is a valid reason to run a benchmark. even for fun

The hardware constraint is real. An NVIDIA RTX A2000 Laptop has 4GB VRAM. Models above ~4GB Q4_K_M overflow into Windows virtual memory and run painfully slow. The practical ceiling is a 4B parameter model at Q4_K_M quantization.

The architecture looks like this:

 Natural language question
          │
          ▼
   ┌─────────────┐
   │     SLM     │  (4B params, 4GB VRAM)
   └──────┬──────┘
          │ reads                    ┌──────────────────────┐
          ├─────────────────────────►│  Semantic Model YAML │
          │                          │  measures, joins,    │
          │                          │  anti-patterns       │
          │                          └──────────────────────┘
          │ writes semantic query
          ▼
   ┌──────────────────┐
   │  Query Compiler  │  (Malloy / boring-semantic-layer / none)
   └──────┬───────────┘
          │ generates SQL
          ▼
   ┌─────────────┐
   │   Engine    │  (DuckDB)
   └──────┬──────┘
          │ reads
          ▼
   ┌─────────────┐
   │    Data     │  (star schema)
   └─────────────┘



The bet: give a small model a semantic model as its system prompt, and it should close the gap. Encode the domain knowledge — measure definitions, join rules, anti-patterns — so the model doesn’t have to infer them from a raw schema. Reduce the cognitive load, reduce the capability requirements, get closer to fast and correct.

On the benchmark chart, that target lives in a small green rectangle in the top-left corner: high accuracy, low latency. This post is about what it takes to reach it — and why we’re still not reliably there.


What the Tests Measure

20 questions, graded from trivial to genuinely hard. Every answer is judged by exact result matching: every row must be correct, column order ignored, 0.5% numeric tolerance for floating point aggregations. Partial credit doesn’t exist.

The easy end (Q1–Q5): total revenue, total quantity sold, list of store names. Any model that can read a schema gets these right.

The hard end (Q18–Q20):

  • Return rate broken down by customer age group × weekend/weekday flag, joined through two fact tables
  • Brand-level year-over-year return rate change filtered by store state
  • Average net sales per transaction by item class and customer preferred flag

These require correctly combining multiple joins, conditional aggregations, and derived measures — and producing results that exactly match the reference answer.

Each run is plotted as accuracy vs. average duration per question. Speed matters because on 4GB VRAM, model size and inference cost are real constraints, not theoretical ones.


Results

One experiment: strip the full YAML semantic model (31KB, two explicit fact tables, extensive field metadata) down to a simplified version (23KB, single logical model, reduced noise). Less token budget spent on metadata, more focus on the patterns that matter.

Results across 3 models × 3 semantic model variants, 24 total runs:

                         Accuracy     Speed      Consistency
qwen3-4b + full model    ~90%         ~10s       scattered
qwen3-4b + simplified    ~90–95%      ~5–8s      tight → green box
nemotron + either        40–85%       ~5–12s     high variance regardless
qwen3.5-4b               ~75–80%      ~45–55s    slow regardless






A capable LLM writing raw SQL can score well on individual runs — 90%+ accuracy on the best models. That sounds promising until you look at the consistency column. The same question, asked twice, can produce correct SQL one run and wrong SQL the next. That variance doesn’t go away with better prompting — it’s a fundamental property of using a probabilistic model for a deterministic task.

Simplifying the semantic model helped the strongest model converge into the green box — higher accuracy, faster, more consistent. For weaker models, variance stayed high regardless of which semantic model they received. The bottleneck had shifted from context quality to model capability.

You cannot simplify your way past a capability ceiling.


Why Raw LLM Breaks

The failure patterns are specific and instructive.

The CTE + FULL OUTER JOIN pattern. The semantic model is explicit: never join the two fact tables (store_sales and store_returns) directly. Always aggregate each separately in a CTE, then combine with FULL OUTER JOIN. Direct joins cause row multiplication. When a model gets this wrong, the result looks plausible — same columns, similar numbers — but every figure is wrong. Models violate this more than you’d expect, especially when the question doesn’t obviously signal that two fact tables are involved.

-- Wrong: direct join multiplies rows
SELECT s.s_store_name, SUM(ss_sales_price * ss_quantity) as total_sales,
SUM(sr_return_amt) as total_returns
FROM store_sales ss
JOIN store_returns sr ON ss.ss_item_sk = sr.sr_item_sk
JOIN store s ON ss.ss_store_sk = s.s_store_sk
GROUP BY s.s_store_name;
-- Right: aggregate each fact separately, then join the summaries
WITH sales AS (
SELECT ss_store_sk, SUM(ss_sales_price * ss_quantity) as total_sales
FROM store_sales GROUP BY ss_store_sk
),
returns AS (
SELECT sr_store_sk, SUM(sr_return_amt) as total_returns
FROM store_returns GROUP BY sr_store_sk
)
SELECT s.s_store_name,
COALESCE(sales.total_sales, 0),
COALESCE(returns.total_returns, 0)
FROM store s
LEFT JOIN sales ON s.s_store_sk = sales.ss_store_sk
LEFT JOIN returns ON s.s_store_sk = returns.sr_store_sk;

Literal instructions ignored. Q9 requires the raw flag values as group labels: 'Y' and 'N', not 'Preferred' and 'Non-Preferred'. The semantic model says this explicitly. LLMs substitute human-readable labels anyway — not because they misread the instruction, but because “Preferred / Non-Preferred” is the more natural output. The model understood what you wrote. It decided it knew better.

These failures share a structure: the semantic model content is correct, but LLM use of it is unreliable. The context is a hint, not a guarantee. You cannot build a production analytics system on a component whose failure modes are unpredictable. Reliability requires a deterministic layer — a compiler that enforces the patterns, not a model that may or may not follow them.


The Right Architecture: LLM Intent + Deterministic Compilation

The right split between LLM and algorithm is this: use the LLM for what only an LLM can do, and use deterministic code for everything else.

Natural language understanding is irreducibly fuzzy — only the LLM can map “show me underperforming stores last quarter” to the right intent. But once that intent is expressed as a structured query, everything downstream is deterministic. The join logic, the measure definitions, the SQL generation — these are mechanical translations that a compiler does perfectly, every time, for free. Running them through an LLM instead wastes token budget on a task that doesn’t need intelligence, while leaving less capacity for the task that does.

The ideal split: the LLM translates natural language into a compact domain-specific query expression. The compiler translates that into SQL. The LLM never touches raw schema column names; the compiler never sees ambiguous natural language.

A query compiler is the right architecture for this problem. It’s deterministic: the same query gives the same answer every time. It enforces join logic and measure definitions by construction, not by suggestion. You can build systems around deterministic limitations. You cannot build systems around random failures.

The DSL the LLM targets doesn’t need to be as simple as SELECT measure FROM model GROUP BY dimension. Users ask for year-over-year comparisons, cohort breakdowns, conditional aggregations — the DSL has to be expressive enough to capture these. But “expressive enough for analytics” is still a far smaller language than SQL. The LLM has a much easier job expressing intent in a constrained, familiar DSL than generating arbitrarily complex raw SQL correctly every time.


Why Open-Source Compilers Aren’t There Yet

The current open-source implementations just aren’t there yet.

Fact-first structural limitations. Most open-source semantic layers are fact-first by design: all queries anchor to the fact table. “List all stores” should return 8 stores. Running through boring-semantic-layer, it returns 6 — stores with no sales are excluded because they have no fact rows. This is not an edge case; it breaks a whole category of queries that any real analytics system must handle. (see example here: boring-semantic-layer #224) Dimension-first routing — queries that go directly to dimension tables when no fact aggregation is needed — is a fundamental capability gap.

Niche query languages. Malloy compiles high-level queries to SQL — the LLM writes Malloy, the compiler handles join logic. In theory this is exactly right: offload SQL generation to a trusted compiler, let the LLM express what to compute. In practice, LLMs have seen enormous amounts of SQL during training and almost no Malloy. Syntax errors were common, measure references wrong. The compiler generated correct SQL from correct Malloy — but getting correct Malloy out of the LLM was harder than getting correct SQL directly.

The proprietary proof that this architecture is buildable: Microsoft Power BI’s query planner handles multi-fact joins, period-over-period comparisons, and dimension-first routing reliably, backed by years of engineering investment. The open-source ecosystem isn’t there yet — and there’s no guarantee it gets there. The commercial incentive that drove Power BI and Looker to solve these problems doesn’t exist for open-source projects in the same way.


The Ceiling Is Implementation Quality

The semantic model doesn’t need to pre-declare every possible query pattern — that’s the point of having an LLM in the loop. “Sales performance in 2020 vs 2021” should be composable: the SLM reads the base measures (total_sales, date_dim keys, grain definitions) and constructs the year-over-year logic itself. Multi-period comparisons, cohort filters, conditional aggregations — these should emerge from creative composition of well-defined primitives, not from an ever-growing library of pre-baked templates. The semantic model’s job is to define what things mean; the SLM’s job is to figure out how to combine them. When that division holds, the system is genuinely expressive. When it breaks down — when the SLM can’t compose reliably from primitives.

Query planner quality degrades fast once you move beyond simple aggregations. The moment you introduce measures that ignore filter context — MAX(date) computed across the full table regardless of what the user filtered — the planner has to reason about predicate pushdown, materialization order, and query cost. A naive planner gets this wrong silently: it scans everything, computes the max, then filters afterward. A recent semi-proprietary semantic layer ( which should remain nameless), supposedly mature, did exactly this on a simple max date ignore filter measure , a sub-second query became a 30-second full table scan.


Where This Goes

There are two truths that can coexist here.

On one side, models are clearly improving. Techniques like aggressive quantization are delivering more reasoning capability within tight resource constraints, making smaller deployments increasingly viable. At the same time, the architectural pattern of separating LLM intent from deterministic compilation is sound. It reflects how the most reliable production systems are built today. In that sense, the open source ecosystem is moving in the right direction, even if current implementations are still maturing.

On the other side, there is a meaningful gap between something being buildable and being built correctly. That gap is easy to underestimate. What looks like a working system often breaks down under real-world conditions: edge cases, ambiguous semantics, inconsistent schemas, and the need for cost-aware execution planning.

Bridging that gap is not just a matter of better ideas. It requires depth. Years of iteration, careful handling of corner cases, and continuous refinement of query planning and semantic layers. Systems like Power BI or Looker did not arrive there by accident. They reflect sustained, focused investment in exactly these problems.

Whether the open source ecosystem will reach that same level of depth remains an open question. It is not a question of capability or intent, but of coordination, time, and sustained effort across a fragmented landscape.

So the current state is this: the architecture is directionally correct, but the hardest parts are still unresolved, and progress on those parts is inherently slow.

Edit: There is also a credible alternative trajectory. If smaller language models continue to improve, direct SQL generation may become reliable enough for a large class of use cases. In that world, the need for a semantic query compiler does not disappear, but it becomes less central, reserved for cases where correctness, governance, or performance guarantees truly matter.

Thanks to Stefan Suwelack for the push back 🙂