Tag: llm

Writing Databricks Parquet That VertiPaq Likes

This post, the repo and every number in it are personal opinion. Nothing here is a Fabric position, benchmark or recommendation.

My colleague Eiki published a post on Microsoft’s white paper on Power BI architecture choices for Azure Databricks. Read it first: it covers the four storage modes, the headline findings, and links to the paper. I assume you know what the paper measured.

I like the paper for a different reason. It benchmarks a lakehouse as designed: open files on object storage, one vendor writes, another reads, Parquet is the only contract. Such benchmarks are rare, hard to reproduce and usually opinionated. This one is deliberately neutral (maybe too neutral) , The dataset is so uniform that V-Order has little room to differentiate the writers, so all writers start on relatively equal ground.

I have written before about VertiPaq reading Parquet that other engines wrote: Optimizing Parquet Layout for Power BI Direct Lake Mode (Dec 2024), Just VOrder, don’t try to understand how VertiPaq works (Nov 2025) and Writing Parquet That VertiPaq Likes (Aug 2026).

The short version: VertiPaq reads Parquet from any producer. It does not need V-Order. It prefers a layout it can transcode fast and hold small, and V-Order is one way to produce it.

This post applies that idea to Spark on Databricks. I am not a Spark expert; I know delta-rs much better. The Parquet concepts carry over, the knobs do not, and Databricks has many settings that interact.

AI helped me navigate them. Where the repo gets Spark wrong, the mistake is mine. Corrections welcome.

What I did

The paper tunes the Databricks write with the mainstream, documented settings, as an industry paper has to. This is a personal blog, so I can be less orthodox and go low level 🙂 I tried a few little-known knobs instead.

All of them are cluster configuration for row-group size and dictionary encoding, the same lines for every table, plus one optional step per fact table: a single clustering key on the column the reports filter on (nothing fancy it is just a global sort).

I used the paper’s protocol: TPC-DS at SF100 and SF1000, its DAX capture, 20 concurrent readers, three load tests back to back on one model. Two changes: the semantic model is deleted after the three runs so the next run starts cold, and the OneLake cache is on because my Databricks tenant is in another region. Only the very first run at a scale factor reads across regions; every later run 1 reads from the cache, so it measures the transcode alone.

Note: I used Fabric layout as it is.

Databricks writes, Direct Lake reads through the mirrored catalog. The configuration, what each line does and what breaks it are in the repo. This post is results only.

The results

Each chart shows the configuration alone, the configuration with the clustering key, and the best layout of each other writer: delta-rs sorted on the date key, and the paper’s Fabric layout of one partition per date with Z-order and V-Order.

Run 1 pays the transcode; runs 2 and 3 rerun the same queries on the loaded model.

At SF100, the configuration alone produces dictionary encoding and large row groups, but is about three times slower than every ordered layout. Adding the clustering key on the date matches the paper’s V-Order layout, with row groups more than ten times larger.

At SF1000, the configuration alone never really warms up: each run is slower than the last, while the three ordered layouts do. That is why the clustering key is not just an optimisation in the recipe; it is what makes the layout usable at this scale. The clustered arm still trails delta-rs and the paper’s layout at SF1000: the clustering did not fully sort the rows at that scale, and the repo carries that as open. Why delta-rs has the best cold run at both scale factors is still a mystery to me.

The interesting part is that VertiPaq does not care who wrote the Parquet. It cares about the physical layout it has to transcode.

The repo

github.com/djouallah/parquet_layout_vertipaq_spark has the configuration, the clustering step, what undoes it, the numbers behind both charts and what is still open.

It also ships a Claude Code skill that applies the recipe.

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.

Power BI with DuckDB, 4 years later

Four years ago I wrote a blog about using DuckDB with Power BI in DirectQuery. It got a fair number of likes on LinkedIn 🙂 along with the one comment I didn’t want to hear: how does this work in production? (Craig, if you’re reading this, you were right.)

Back then I thought the technology was the hard part and the rest would sort itself out. It didn’t.

The ODBC driver never really worked in any non-trivial setup. Filters didn’t push down, decimal precision was buggy. It has gotten better since, but two show stoppers remained:

  • DuckDB is in-process, so the driver is the database. There’s no warm, long-running session. Every query starts from scratch.
  • I don’t think those drivers can realistically be certified (personal opinion). And Power BI Service, or any hosted BI service for that matter, is not going to host an in-process engine for free. An on-prem data gateway is not really a good option either.

In 2026 things are way better. MotherDuck (DuckDB’s SaaS) shipped a PostgreSQL endpoint. Problem solved: Power BI speaks Postgres, and it works out of the box.

Then last week DuckDB released Quack. For my own sanity I’ll just call it “DuckDB Server.” It is just an extension; a single function call and you have a server !!

My first reaction was annoyance. Four years of waiting, and they shipped a proprietary wire protocol. I was hoping for pg wire. I want my driver to work. I don’t really care about a 2x improvement if nothing interoperates.

Luckily I was partially wrong. Within two days there was an ADBC driver from gizmodata/adbc-driver-quack, and, to my surprise, a Power BI custom connector from Curt Hagenlocher (think of him as the Linus of Power Query). my understanding it is a side project, not official Microsoft.

And somehow, the whole thing worked. It was beautiful.

But lesson learned from last time: this is experimental, with no guarantee the connector will ever be certified.

The main change from the 2022 post is that instead of pointing at parquet files, I’m pointing at a catalog and getting tables back, like an actual database instead of a pile of files and duckdb got way better.

High level architecture

  1. OneLake Iceberg Catalog — OneLake exposes data as tables. You need three things:
    • Endpoint: https://onelake.table.fabric.microsoft.com/iceberg
    • An Entra ID auth token
    • Path to the Lakehouse/Warehouse: workspace_name/Lakehouse_name.Lakehouse

  1. DuckDB + iceberg extension — reads the catalog and the underlying parquet over HTTPS.

  1. Entra ID — az account get-access-token --resource https://storage.azure.com/ mints a short-lived bearer token. No service principal, no app registration. I have a script that grabs the token, and I opened duckdb-azure#170 hoping to make this much simpler.

  1. DuckDB Endpoint — turns the engine into a TCP server on 127.0.0.1:9494, speaking DuckDB’s native wire protocol (whatever that means).

  1. The ADBC Driver — Python client and Power BI share the same DLL, you need to manually install it from curt github page

You can download all the files here

Power BI

Let’s just share a video. Yes, 600M rows, warm run in my laptop

Python Notebook

TPC-H SF=10 (10 GB), 22 queries, run twice in the same session via client.ipynb. Numbers are seconds, copied straight from the notebook output.

ColdWarm
Total~5 min 29 s~30 s

Cold time is dominated by parquet I/O over HTTPS from OneLake. Bandwidth and seek count, not CPU. Warm runs hit DuckDB’s in-process buffer cache, Onelake endpoint is in another continent and my internet provider is horrible 🙂

Optimization on this stack should target bytes read and seeks (codec, row-group size, predicate pushdown, range prefetch), not query plans.

This is exactly why server mode make sense, as the warm cache is shared by all client (notebook, Power BI, AI Agent)

Not production ready

  • The Entra token has a ~1h TTL. As far as I can tell, DuckDB has no way to auto-refresh tokens.
  • The driver is not certified, so it can’t be used in the service, if you want it added to PowerBI, create an idea in Fabric forum and vote
  • DuckDB Server is new. Don’t expect SQL Server maturity yet 🙂
  • DuckDB’s remote file cache is RAM only. When you restart DuckDB, you lose it and have to deal with the cold-run pain again and egress fees 😦
  • The DuckDB Azure extension is still pretty rough in places. To be fair, they’ve openly said they don’t have the bandwidth.

Hopefully it won’t take another four years to make this production ready.

Still, seeing DuckDB as a single binary serving a 600M row table to Power BI was genuinely fun. and The Iceberg catalog is awesome !!!