Writing Parquet That VertiPaq Likes

This rabbit hole started with a simple observation: VertiPaq seemed to like parquet produced by delta-rs more than parquet produced by DuckDB — and that drove me nuts. delta-rs was at the time a niche library for nerds; Fabric didn’t even have a Python notebook. The mental model was simple: write with Spark, get V-Order, get the best possible layout for Power BI.

It is 2026; Fabric is more widespread, and there are simply more patterns and use cases:

  • New Fabric workspaces default Spark to the writeHeavy resource profile, which does not write V-Order.
  • Customers — especially on smaller SKUs — routinely write with delta-rs from Python notebooks.
  • Reading tables written by Snowflake, Databricks and BigQuery through Direct Lake is a production pattern.

So “what parquet is friendly to Power BI” is now a legitimate data engineering question — I can’t tell you how happy I was when I read this tweet 🙂

The short version

  1. Row groups of a few million rows : 2–6M rows per group; never go above 16M, VertiPaq’s segment ceiling.
  2. Dictionary-encode every column — and make the file footer say so.
  3. One global ORDER BY, lowest-cardinality columns first, date up front. No clustering, no Z-ordering, none of that.
  4. Delta vs Iceberg does not matter. Only the parquet inside the table matters.

Method

I treated VertiPaq as a black box and did what experimental science does with a phenomenon it doesn’t understand: change one variable, measure, repeat. Nothing here is confidential or internal — every number was measured from the outside, on tables anyone can rebuild.

One more thing changed this year: AI became genuinely useful for this kind of work, because it never gets bored. Sweeping writers × row-group sizes × file sizes × sort orders across hundreds of runs is exactly the tedium it doesn’t feel.

Two experiments. First: figure out why VertiPaq preferred delta-rs output. Second: run multiple writers and vary row-group count, file size and ordering, measuring cold, warm and hot query cost.

Caveat. Hot behaviour is well documented — after all, it is the same in-memory format as import mode — so I am more interested in cold runs (first touch of a fresh model), even though most real-world traffic is hot: a live model transcodes once and then serves from RAM.

The datasets are rather smallish — the biggest table here is around 600M rows. As a data analyst I have always dealt with small data, so I am optimising for the workload I actually care about.

How Direct Lake reads your parquet

The mechanism that explains almost every finding. Transcoding is per column, on demand: the first DAX query to touch a column that is not yet in memory converts that column only into VertiPaq’s in-memory format. The column’s per-row-group parquet dictionaries are merged into one global VertiPaq dictionary, and each row group of the column is loaded as one resident column segment, remapping parquet data IDs onto VertiPaq IDs on the way in. Every query after that scans the segments the transcode produced. Query latency — and capacity consumption — is therefore a property of how the parquet was written.

Findings

Dictionary encoding is the big one

VertiPaq is itself a dictionary-based engine. When a chunk arrives dictionary-encoded, the transcode merges the parquet dictionary into the column’s global one and remaps the data IDs — it never decodes the values. Anything else has to be decoded and re-hashed, value by value, at load time. On a single 144M-row DECIMAL(18,4) column, PLAIN measured 618.6 MB against 423.1 MB dictionary-encoded — ~200 MB extra and a re-encode, on one column.

The surprise is that the encoding alone isn’t enough: the declaration is part of the encoding. The engine takes the cheap remap path only when the footer’s encoding_stats prove a chunk is entirely dictionary-encoded without decoding its pages. DuckDB’s writer emitted no encoding_stats at all until duckdb#24957 (merged 2026-08-24, currently in main only). That PR measures the cold first-touch of a 142M-row dictionary string column falling from 10,857.5 ms to 689.3 ms — about 15×, with identical pages. The attribution was verified the hard way: synthesising only that footer field into an otherwise unmodified file reproduces the speedup. A second PR, duckdb#24645 (merged 2026-08-10), adds a data_page_size_limit option — before it, DuckDB often wrote one huge data page per column chunk.

Row-group size: a tension between cold and hot

There is no single best size, but both ends fail measurably. Every row group is one more dictionary merge and one more segment to set up per column, which is why tiny groups murder the cold tier: the same DuckDB in the same notebook was 3.5× slower cold (96,503 ms vs 27,785 ms) when a library default sliced a 144M-row table into 1,172 groups of ~123k rows. At the other end, 16M rows — VertiPaq’s segment ceiling — was the worst sorted geometry measured: nine segments starve the scan pool. Cold prefers slightly bigger groups than hot, but very big groups are bad for both.

Power BI doesn’t disclose how many cores it uses, so the practical rule is: enough row groups to keep the cores busy. On the 144M-row table, warm query time stepped down between 19 and 24 groups (≈5,700 ms → 3,221 ms), and 72 groups bought nothing over 24. Hence the plateau: 2–6M rows per group.

A global sort keeps paying after the data is in memory

Transcoding does not change row order — it is essentially a working data copy into memory. So a sort applied at write time survives into the resident segments, which is why ordering matters for hot runs too, not just for compression.

A single global ORDER BY with low-cardinality columns first (and a preference for date) produces long RLE runs. It is not V-Order — but in some cases it is good enough. When V-Order does engage, what it’s worth depends on the surface — column count × categorical skew — not row count: on a skewed 17-column taxi table it collapsed the most repetitive column to 3,371× fewer runs; on a near-unique 5-column table it left row order untouched and still shrank files 16%. One caution from the sweep: an alternative sort key cut file size a further 30% and bought statistically zero query time — sort for the columns your queries filter on, not for size on disk.

This is also the cleanest way to see what V-Order’s reorder actually is. A hand-written ORDER BY collapses the column you name and leaves the others fragmented; V-Order sorts by several columns at once, most repetitive first — the taxi measurement above is its signature, runs falling off exactly as an encoding-driven sort predicts.

VertiPaq doesn’t like ragged row groups

delta-rs closes a file the moment the size cap is hit, truncating the in-progress row group — measured writing groups at 0.43× their declared rows. A truncated group isn’t just small; it makes segment sizes uneven, exactly the non-uniform scan load you were sizing row groups to avoid. I’ve proposed a fix in delta-rs#4677 — still open, and opt-in — which rolls files only on row-group boundaries.

Query planners can’t estimate total rows correctly

My biggest surprise. I built a personal package, duckrun, using delta-rs and DuckDB and tried a clever optimisation. The plan: before writing a query’s result, estimate its row count and compute the perfect row-group size on a 1M–16M scale. It failed completely, because query planners are bad at estimating output size — DuckDB estimated ~14.9M rows for a table that actually held 143,980,961, 9.7× too low — so the “optimised” geometry was off by an order of magnitude. Derive geometry from an exact count (the table you’re rewriting, the Delta log) — never from an estimate. Not only that: in an initial version the cost of the estimation was nearly the same as writing the table 🙂

anyway, I endup writing 6M as the default row group everywhere.

Takeaway

V-Order is usually understood as row reordering — and the reordering is real; the hard part is not the reordering itself but doing it fast. But it is not only that: a V-Order write also sets the row-group geometry and runs the encoding pass, keeping a declared dictionary on every column.

Two consequences follow. If you write with a Fabric engine, turning it on is a no-brainer: measured here at ~8% of build compute for up to 2.8× less query capacity (1,332 vs 3,769 CU on identical data) — the write premium is paid once, while queries pay every day. I do wish it were simpler to turn on: changing the Spark write profile is not obvious, and a lot of users don’t even know it is there. As someone who used VertiPaq for more than a decade with zero knowledge of columnar data structures, I suspect there must be a better way.

And if you write with anything else — Snowflake, Databricks, delta-rs, DuckDB — my hope is that there will be more public specification on how to optimize parquet layout for VertiPaq.

Links

The Enterprise 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.

AI, dbt and Iceberg are already changing data engineering

I have been using AI in VS Code and Onelake, initially trying to make sense of Chat with your data (without much success, that thing is very hard and we need some breakthrough), and more recently for data engineering. I noticed something: AI has become useful enough in the last couple of months that it is changing my workflow.

None of the individual pieces here is new or particularly interesting by itself. But combine them and we have something!!! CI/CD, Iceberg REST catalog, Opus 4.8, me discovering how OIDC GitHub integration works in Fabric, and dbt, and suddenly everything makes more sense.

Let’s take a simple ETL job, read some CSV, clean it, and produce high quality Parquet files that humans and AI can consume.

From a human perspective, and although we like to think our judgement is based purely on cost and performance, that’s never really been the case. It is always influenced by personal bias: Spark people will always use Spark, T-SQL people will always use the DWH, and Pandas people don’t care, they will use their thing.

Now, if we imagine an AI doing that, the incentives are different. Yes, AI is biased by its training data, but it isn’t biased by tribe. Judging by my AI agent (I suspect it is telling me what I want to hear), it doesn’t care. It prefers short loops, and if you tell it “I want the cheapest option”, it is smart enough to try to do that.

1- ETL is just processing raw data into something coherent that can be consumed. It is a deterministic process, and as someone who mainly used GUI tools, it took me a while to get it: data engineering is just code!!! Those tools are writing code, it just happens that we don’t see it 🙂

2- All things considered, an AI agent has no personal attachment to an engine, assuming you give it a strict spec. An agent will not favour an engine because of familiarity, or because it spent so much time using it that it became tribal.

3- Opus 4.8 class AI is good enough for general purpose data engineering. The current issue is the cost. Right now you need a Max subscription in practise and it is just not sustainable, but I am confident the market will figure out a solution. We don’t need AGI, just a super cheap Opus 5 alternative 🙂

4- I am not saying all engines are equal, or that they become just a SQL runtime. That’s not true. But because the cost of switching from one SQL dialect to another is minimal, the human excuse of “I use what I know” (which is pretty tragic, when you think about it) will no longer be relevant.

Selecting an engine will be strictly based on facts: engine 1 can do efficient MERGE, engine 2 cannot, engine 1 wins. That’s fair.

And engines still have a lot to improve, see for example non-trivial incremental processing, async remote scans, partial caching, Dynamic Horizontal scaling etc.

And to be fair, the dialect alone is not the whole story. Subtle differences in engine behaviour, even when using the same SQL text, can be a pain. I learnt this the hard way. I spent too much time trying to debug a result, only to find out that two engines have different behaviour when doing a join using a word with padding: “spain” and “spain ” may or may not mean the same thing.

But the good thing is that a cross-engine parity check catches these subtleties automatically. Either the numbers match or they don’t (you can’t do that with Chat with your data).

5- Cloud storage vendors ultimately want to store more data, regardless of how it was processed — their incentives are aligned with this agentic trend. In my personal opinion, they need to spend less time on MCP and other AI stuff, and just focus on the boring stuff: authentication, documentation, and interoperability.

If you want to deviate from the catalog spec, at least spend some engineering time working with the open source engines to support your own variant.

6- The less a client has to do, the better for everyone: move more stuff to the catalog, including table scanning and server-side planning.

A read-only client has no interest in reading Avro files. Just give me a list of Parquet files and deletion vectors to read. Keep it opaque. Store it in a database, I don’t care. Just give me the data files to read.

7-I added this note because of early feedback on the blog, which was basically: “I don’t care about Iceberg, I use Delta.” That’s a fair reaction. The good news is it won’t really matter — Delta 5 will use the same adaptive metadata as Iceberg V4. They’re still two separate projects with different governance model(or rather, the lack of it), but ultimately they’ll produce the same thing in theory . Just give it another 2 years or so, there is a real table format fatigure, translating from one to another is a waste of everyone time, and to be honest, some vendors find it is much cheaper just to lock users using credential vending instead of weaponising the table format 🙂

Instead of another abstract “thought leadership” blog post 🙂 here’s a concrete example: the same dbt project, unchanged, runs against four different vendors’ Iceberg REST catalogs: OneLake, Cloudflare R2, S3 Tables and Snowflake Horizon, each in about a minute, on a throwaway GitHub Actions runner with DuckDB as the engine.

Switching catalog is literally changing one ATTACH in profiles.yml.

The whole thing is public: testing-iceberg-rest-catalog.

Hopefully, data engineering will move away from configuring and fine-tuning engines and towards talking more to end users and understanding what they need: agreeing on what the numbers are supposed to mean, and writing the tests that catch it when they are wrong.

The machine can build the pipeline, and it will probably be better than us at it, but it doesn’t know what “correct” means. I was tempted to write something about ontology but I am not going there 🙂

And getting access to that ERP will always require a human. That’s an organizational thing, and no AI can fix it.

As a data analyst by trade, I always found data engineering a chore. I never enjoyed it , and to be honest ,it does not bother me if writing transformation becomes fully automated

So I have a bias, to be honest, and maybe this blog is just wishful thinking. But what if it is true? I think, at least, pay attention to this new trend.

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.