Category: DuckDB

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.

How far Python alone can take you on Delta

1. delta-rs is an ACID Delta writer

delta-rs implements the Delta Lake protocol natively. merge, update, and delete go through optimistic concurrency control on every commit. No external coordinator, no catalog service. Two writers race for the same version of the log, one wins, the other retries.

All you need is a path. No metastore to provision, no catalog endpoint, no JDBC connection, no warehouse to wake up. A folder on disk (or on ADLS / S3 / GCS) is the whole interface.

Setup: B is a Delta table being fed a series of CSV batches (batch_001.csv, batch_002.csv, …). Each merge should ingest only files B hasn’t seen yet.

A naming note: the project is delta-rs but the Python package is deltalake (pip install deltalake). On Fabric, stick with what’s preinstalled — Python notebooks already ship with deltalake and OneLake access configured.

From the notebook:

# Bootstrap target B with batch_001 already ingested
write_deltalake(Target_PATH, pa.table({...}), mode="overwrite")
vB = DeltaTable(Target_PATH).version()   # v0
# Compute the rows to ingest from the target's current state
con.sql(f"ATTACH '{Target_PATH}' AS tgt (TYPE delta, VERSION {vB});")
our_rows = con.sql("""
    SELECT s.id, s.value, parse_filename(s.filename) AS filename
    FROM read_csv_auto('source_csv/*.csv', filename=true) s
    WHERE parse_filename(s.filename) NOT IN (SELECT DISTINCT filename FROM tgt)
""").arrow()
# → 80 new rows from batch_002..005
# First merge: 80 inserts, commits cleanly
DeltaTable(Target_PATH).merge(
    source=our_rows,
    predicate="t.filename = s.filename",
    source_alias="s", target_alias="t",
).when_not_matched_insert_all().execute()
# Same merge re-run: 0 inserts. The predicate is idempotent.
DeltaTable(Target_PATH).merge(...).when_not_matched_insert_all().execute()

Two commits, both correct. The second run does nothing because the predicate already sees the rows. The transaction model travels with the table itself: move the folder, open it from another machine, and the next writer continues from the last commit.

write_deltalake(mode="append") and write_deltalake(mode="overwrite") are blind on purpose. Blind append means N concurrent appenders all succeed and the result is the union of their rows — exactly what you want for event streams or log ingestion. Blind overwrite means the new data wins and whatever was there is gone — what you want when the writer is the authoritative source for the table. OCC only kicks in for operations that actually read the target (merge, update, delete), since those are the only ones where a concurrent change can invalidate what you just computed.

2. I want the full read-to-write transaction, Python API is fine

A common pattern: DuckDB or Polars reads, transforms, and hands an Arrow table to delta-rs to commit. The notebook above is exactly that shape — DuckDB computes “filenames not yet in B” and delta-rs merges the result.

Inside delta-rs, OCC still works. What it cannot see is the read on the other side of the engine boundary. delta-rs knows about the merge it is about to commit; it does not know that DuckDB read B at version vB thirty seconds ago.

Carry the snapshot across the boundary by pinning both sides to the same version:

vB = DeltaTable(Target_PATH).version()
import duckdb
con = duckdb.connect()
con.sql(f"ATTACH '{Target_PATH}' AS tgt (TYPE delta, VERSION {vB});")
our_rows = con.sql("SELECT ...").arrow()
DeltaTable(Target_PATH, version=vB).merge(   # ← pinned
    source=our_rows,
    predicate="t.filename = s.filename",
    source_alias="s", target_alias="t",
).when_not_matched_insert_all().execute()

The OCC check now compares against vB instead of HEAD. If another process touched B in the meantime — say a parallel job deleted batch_001.csv — the pinned merge raises:

Failed to commit transaction: Commit failed: a concurrent transaction deleted data this operation read.

Catch it, recompute the diff against fresh state, retry. On the Polars side, pl.read_delta(path, version=vB) accepts the same pin, so the pattern works for any reader that exposes versioned reads.

The pin is just a number. No new infrastructure, no shared coordinator, still path-based.

3. I don’t want the Python API, I want SQL only

If you would rather write SQL — say, drive the pipeline from dbt — your options on Delta today are Spark and Fabric Data Warehouse. Both have supported dbt adapters and work great in production. I have to admit, I was hoping DuckDB would fill that gap, since it is a database and SQL-level transactions are what you expect from a database. The market went the other way: investment is going into catalog-based lakehouse formats (DuckLake, Iceberg), and the DuckDB Delta writer that does exist is tied to Unity Catalog and limited to blind appends. I don’t see them investing in a file-based conflict resolver any time soon 🙂 Lakesail seems interested in this use case, but it is still too early to call.

Takeaway

I personally use delta-rs for CSV ingestion, appends, and recording results from high-concurrency performance tests — it is fast, cheap, and bullet-proof in those scenarios. The open source maintainers are very helpful and care deeply about the product, as they use it themselves in production. But it is not the right tool for every case; Data Warehouse and Spark are more appropriate for complex workloads. With time you intuitively pick the tool that makes sense for a particular job and how much compute you can spend. None of that has to be an either/or: at the end of the day it is a lakehouse, and the whole concept of a lakehouse is having the option to choose the engine. That option matters — if we say only one engine (open source or not) is blessed for writes, then there is no point in the concept of a lakehouse.

Notebook: https://github.com/djouallah/Fabric_Notebooks_Demo/blob/main/TableFormat/delta/occ.ipynb

Thanks Raki for keeping me honest:)

Thanks to Ion for explaining how version worked when doing merge: https://www.linkedin.com/in/ionkoutsouris/

Edit : how about Spark

Thanks to Frithjof for explaining Spark behaviour : The merge fixes one snapshot at transaction start (current HEAD = post-delete) and uses it for both its scan and its conflict check. Internally consistent — but bound to HEAD-at-merge-start, which Spark chose, not to the state our read saw, same behaviour when using delta_rs with a lazy dataframe : https://github.com/djouallah/Fabric_Notebooks_Demo/blob/main/TableFormat/delta/occ_spark.ipynb

Ensuring safe single-writer for DuckLake on OneLake using file lease

DuckLake supports multi-writer just fine — but only if your catalog is a real database, like Postgres (there’s some interest in SQL Server support too). But if all you have is object storage and a SQLite or DuckDB file as the catalog, you’re stuck with single-writer: object stores aren’t real filesystems, so the DB file can’t be locked. Nothing stops two processes from writing to it at the same time and corrupting it.

If single-writer is enough for you (one notebook, one pipeline, one user), you don’t need to stand up a database server. You just need accidental concurrent runs to fail fast.

The trick: take a blob lease

OneLake speaks the ADLS API, so you can take a lease on a blob — a mutex for free (it seems S3 needs DynamoDB and GCS needs a homemade lock object). Each run does:

  1. Acquire a lease on metadata.db in abfss://.
  2. Download it to local disk of the notebook.
  3. Point DuckLake at the local copy and do the work.
  4. Upload the modified file under the lease.
  5. Release the lease.

A second notebook that starts while the lease is held fails immediately on acquire_lease. It can’t even read a stale copy. and you can’t delete the file using the UI , I can see already some uses cases here:)

What about crashed runs?

ADLS leases are either 15–60 seconds fixed, or infinite. Fixed leases need a heartbeat — annoying inside a notebook. Infinite leases work until something crashes — then the file is stuck.

The fix: take an infinite lease, but stamp acquired_at = <utc iso> into the blob’s own metadata when you acquire. When the next run hits a lease conflict, read that timestamp. Older than 12 hours? Call break_lease and re-acquire. A crashed run self-heals within 12 hours. You can shorten that window, or break the lease manually with a one-line script if you can’t wait — there’s a snippet in the README.

Code is here.

The Boring Reason Iceberg Matters

TL;DR: Iceberg’s value is sociological, not technical. And if you care about lightweight, single-process engines like datafusion and duckdb, it’s probably your best shot at first-class lakehouse support with Wide interoperability.

The first real data engineering work I did was an ingestion pipeline built on pandas and Parquet with Hive-style partitioning — an environment where 512 MB of memory was a genuine architectural constraint, not a rounding error. That experience shaped how I think about data tooling: the engine matters, but so does the ability to swap it out. Engine independence is something I care about more than most people I know, which is probably why I find myself paying close attention to Iceberg. Not for the reasons most people cite, though. It’s not the spec. It’s where the engineering hours are landing.

Getting query engines and catalogs to talk to each other is genuinely hard work. Most of it is unglamorous: error envelope parsing, metadata round-tripping, commit response shapes, partition spec edge cases, auth token quirks between vendors. None of it ships a feature anyone demos. None of it makes a good blog post. It’s the maintenance work that quietly determines whether your stack actually functions.

This is the part that’s easy to miss. Standards don’t converge because the spec is good. They converge because enough people, at enough companies, decide to put sustained hours into the interop bugs — year after year, across release cycles, through personnel changes and shifting priorities.

Look at the Iceberg committer list: Netflix, Apple, Databricks, Snowflake, AWS, Dremio, Microsoft. No single employer controls what gets merged. The incentive to fix cross-vendor interoperability bugs is distributed across the committer base itself. The governance isn’t just a formality — it’s what makes it possible for engineers from genuinely different setups to find, reproduce, and fix the same bug together.

There is one specific layer worth watching: the Iceberg REST catalog specification. It has become the canonical standard for how engines and catalogs communicate. Adoption is real: Polaris, lakekeeper, Gravitino, and a growing list of vendor-managed catalogs implement it.

But adoption and interoperability are not the same thing.

In practice, vendors still interpret parts of the specification differently. Engines end up handling quirks like slightly different response shapes, undocumented authentication flows, or inconsistent error handling. The nearest analogy is ODBC — a real standard, widely implemented, and still years of painful work before the “connect to anything” promise actually held up in practice.

The Iceberg REST catalog ecosystem feels earlier in that curve. The gap between specification and implementation is exactly where a lot of the maintenance work is happening right now. And closing that gap is precisely the kind of work Iceberg’s governance model is designed to support, because the people hitting the bugs are often the same people with commit access to fix them.

This is where the stakes become concrete, especially for lightweight engines.

For cloud warehouses and large JVM-based systems, the maintenance burden is manageable. There are full-time teams paid to absorb it. For the newer generation of small, single-process engines, the situation is very different. These are compact teams building engines with a specific focus: query latency, memory efficiency, embedded analytics, local execution.

Every hour spent chasing interoperability edge cases is an hour not spent improving the engine itself.

Several of these engines already support Iceberg in some form. But broad, reliable lakehouse support depends on the ecosystem doing its part: stable specifications, faithful implementations, bugs surfaced and fixed upstream.

A well-maintained standard is not just a convenience for these projects. It’s what makes serious lakehouse support achievable without hollowing out the team building the engine.

There is also a broader cost to fragmentation that rarely gets discussed directly. Every hour the ecosystem spends maintaining incompatible metadata layers is an hour not spent making lakehouse systems actually better. That cost doesn’t show up clearly in any individual issue tracker, but it accumulates across the entire ecosystem.

That’s the real argument for Iceberg.

Not that it’s a particularly clever format. Formats are mostly boring by design.

The real advantage is that Iceberg has assembled the right kind of maintenance coalition: enough companies with genuinely different incentives, governance that distributes merge authority, and enough independent implementations that bugs surface from the edges instead of only the center.

Whether that coalition survives long term as the market consolidates is still an open question. But right now, Iceberg is the ecosystem where the boring interoperability work is most likely to get done by someone other than you.

And in infrastructure, that’s close to everything.

That’s also why this feels personal to me.

The 512 MB pipeline I started with wrote Parquet files and hoped for the best — no transactions, no snapshot isolation, just partitions and careful scheduling to avoid stepping on yourself.

What I actually wanted, and couldn’t realistically have at the time, was proper ACID semantics with snapshot isloation end to end from something small and cheap. A cloud function. A tiny process with almost no memory to spare.

Iceberg is the closest thing to a realistic path toward that today. Not because the specification is especially elegant, but because it’s where the maintenance work is happening.

And eventually, ecosystems catch up to where the maintenance happens.

Special thanks to Raki Rahman for a few conversations that genuinely reshaped how I think about this space.

Ideas are mine; writing assisted by AI.