Category: sql

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.

A Non-scientific Benchmark of Text-to-SQL using Small Language Models

TL;DR ; This post shares a quick experiment I ran to test how effective (or ineffective) small language models are at generating SQL from natural language questions when provided with a well-defined semantic model. It is purely an intellectual curiosity; I don’t think we are there yet. Cloud Hosted LLMs are simply too good, efficient, and cost-effective.

You can download the notebook and the semantic model here.

⚠️ This is not a scientific benchmark.
I’m not claiming expertise here—just exploring what small-scale models can do to gain an intuition for how they work. Large language models use so much computational power that it’s unclear whether their performance reflects true intelligence or simply brute force. Small-scale models, however, don’t face this issue, making their capabilities easier to interpret.

Introduction

I used Ollama to serve models locally on my laptop and DuckDB for running the SQL queries. DuckDB is just for convenience—you could use any SQL-compatible database

For a start I used Qwen3, 4B, 8B and 14B, it is open weight and I heard good reviews considering it’s size, but the same approach will works with any models, notice I turn off thinking mode in Qwen.

To be honest, I tested other small models too, and they didn’t work as well. For example, they couldn’t detect my graphics card. I probably missed some configuration, but since I don’t know enough, I prefer to leave it at that.

0. Semantic Model Prompt


A semantic_model.txt file acts as the system prompt. This guides the model to produce more accurate and structured SQL outputs , the semantic model itself was generated by another LLM, it does include non trivial verified SQL queries ,sample values, relationships , measures etc, custom instructions

“no_think” is to turn off the thinking mode in Qwen3

1. Setup and Environment

  • The notebook expects an Ollama instance running locally, with the desired models (like qwen3:8b, qwen3:4b) already pulled using ollama run <model_name>.

2. How It Works

Two main functions handle the process:

  • get_ollama_response:
    This function takes your natural language question, combines it with the semantic prompt, sends it to the Local Ollama server, and returns the generated SQL.
  • execute_sql_with_retry:
    It tries to run the SQL in DuckDB. If the query fails (due to syntax or binding errors), it asks the model to fix it and retries—until it either works or hits a retry limit.

In short, you type a question, the model responds with SQL, and if it fails, the notebook tries to self-correct and rerun.

3. Data Preparation

The data was generated using a Python script with a scale factor (e.g., 0.1). If the corresponding DuckDB file didn’t exist, the script created one and populated it with the needed tables. Again, the idea was to keep things lightweight and portable.

Figure: Example semantic model

4. Testing Questions

Here are some of the questions I tested, some are simple questions others a bit harder and require more efforts from the LLM

  • “total sales”
  • “return rate”
  • “Identify the top 10 item categories with the highest total return amount from customers born in ‘USA’ who made returns in 2001.”
  • “customer age group with the worst return rate?”
  • “return rate per year”
  • “any days with unusual return rate?, use fancy statistics”

Each question was sent to different models (qwen3:14b, qwen3:8b, qwen3:4b) to compare their performance. I also used %%time to measure how long each model took to respond, some questions were already in the semantic model, verified query answers, so in a sense it is a test too to see how the model stick with the instruction

5. What Came Out

For every model and question, I recorded:

  • The original question
  • Any error messages and retries
  • The final result (or failure)
  • The final SQL used
  • Time taken per question and total time per model

6. Observations

Question 6 : about detecting unusual return rates with “fancy statistics “stood out:

  • 8B model:
    Generated clean SQL using CTEs and followed a star-schema-friendly join strategy. No retries needed.
  • 14B model:
    Tried using Z-scores, but incorrectly joined two fact tables directly. This goes against explicit instruction in the semantic model.
  • 4B model:
    Couldn’t handle the query at all. It hit the retry limit without producing usable SQL.

By the way, the scariest part isn’t when the SQL query fails to run, it’s when it runs, appears correct, but silently returns incorrect results

Another behavior which I like very much, I asked a question about customers born in the ‘USA’, the model was clever enough to leverage the sample values and use ‘UNITED STATES’ instead in the filter.

Execution Times

  • 14B: 11 minutes 35 seconds
  • 8B: 7 minutes 31 seconds
  • 4B: 4 minutes 34 seconds

Tested on a laptop with 8 cores, 32 GB RAM, and 4 GB VRAM (Nvidia RTX A2000), the data is very small all the time is spent on getting the SQL , so although the accuracy is not too bad, we are far away from interactive use case using just laptop hardware.

7- Testing with simpler questions Only

I redone the test with 4B but using only simpler questions :

questions = [
          'total sales',
          'return rate',
          "Identify the top 10 item categories with the highest total return amount from customers born in 'USA' who made returns in 2001.",
          'return rate per year',
          'most sold items',
          ]
ask_question(questions,'qwen3:4b')

the 5 questions took less than a minutes, that promising !!!

Closing Thought

instead of a general purpose SLM, maybe a coding and sql fine tuned model with 4B size will be an interesting proposition, we live in an interesting time

Experimenting with Text-to-SQL: Lessons from Optimizing Product Return Analysis

🌟 Introduction

While testing the DuckDB ODBC driver, which is getting better and better (not production ready but less broken compared to two years ago), I noticed something unexpected. Running queries through Power BI in DirectQuery mode was actually faster than executing them directly in the DuckDB native UI.

Naturally, that does not make sense !!!

What followed was an investigation that turned into a fun and insightful deep dive into text-to-SQL generation, Power BI’s query behavior, and the enduring relevance of manual SQL tuning

🧩 The Goal: Find the Worst Product by Return Rate

The task was straightforward:

Calculate total sales, total returns, and return rate by product. Rank the products and find the top 5 with the highest return rates.

To make it interesting, I decided to try:

  1. Letting an LLM generate the SQL by loading the semantic model.
  2. Using PowerBI in Direct Query Mode.
  3. Finally, manually tuning the query.

📝 Step 1: LLM-generated SQL — Clean and Understandable

chatgpt generated a good starting point:

WITH sales_by_product AS   (
    SELECT
        i.i_product_name AS product_name,
        SUM(ss.ss_sales_price * ss.ss_quantity) AS total_sales
    FROM store_sales ss
    JOIN item i ON ss.ss_item_sk = i.i_item_sk
    WHERE i.i_product_name IS NOT NULL
    GROUP BY i.i_product_name
),

returns_by_product AS   (
    SELECT
        i.i_product_name AS product_name,
        SUM(sr.sr_return_amt) AS total_returns
    FROM store_returns sr
    JOIN item i ON sr.sr_item_sk = i.i_item_sk
    WHERE i.i_product_name IS NOT NULL
    GROUP BY i.i_product_name
),

combined AS (
    SELECT
        COALESCE(s.product_name, r.product_name) AS product_name,
        COALESCE(s.total_sales, 0) AS total_sales,
        COALESCE(r.total_returns, 0) AS total_returns
    FROM sales_by_product s
    FULL OUTER JOIN returns_by_product r
        ON s.product_name = r.product_name
)

SELECT
    product_name,
    ROUND((total_returns / NULLIF(total_sales, 0)) * 100, 2) AS return_rate
FROM combined
WHERE total_sales > 0 -- Avoid divide by zero
ORDER BY return_rate DESC
limit 5 ;

✅ Pros:

  • Clean and easy to read.
  • Logically sound.
  • Good for quick prototyping.

🔍 Observation: However, it used product_name (a text field) as the join key in the combined table, initially I was testing using TPC-DS10, the performance was good, but when I changed it to DS100, performance degraded very quickly!!! I should know better but did not notice that product_name has a lot of distinct values.

the sales table is nearly 300 M rows using my laptop, so it is not too bad

and it is nearly 26 GB of highly compressed data ( just to keep it in perspective)

📊 Step 2: Power BI DirectQuery Surprises

Power BI automatically generate SQL Queries based on the Data Model, Basically you defined measures using DAX, you add a visual which generate a DAX query that got translated to SQL, based on some complex logic, it may or may not push just 1 query to the source system, anyway in this case, it did generated multiple SQL queries and stitched the result together.

🔍 Insight: Power BI worked exactly as designed:

  • It split measures into independent queries.
  • It grouped by product_name, because that was the visible field in my model.
  • And surprisingly, it was faster than running the same query directly in DuckDB CLI!

Here’s my screenshot showing Power BI results and DAX Studio:

🧩 Step 3: DuckDB CLI — Slow with Text Joins

Running the same query directly in DuckDB CLI was noticeably slower, 290 seconds !!!

⚙️ Step 4: Manual SQL Tuning — Surrogate Keys Win

To fix this, I rewrote the SQL manually:

  • Switched to item_sk, a surrogate integer key.
  • Delayed lookup of human-readable fields.

Here’s the optimized query:

WITH sales_by_product AS (
    SELECT
        ss.ss_item_sk AS item_sk,
        SUM(ss.ss_sales_price * ss.ss_quantity) AS total_sales
    FROM store_sales ss
    GROUP BY ss.ss_item_sk
),

returns_by_product AS (
    SELECT
        sr.sr_item_sk AS item_sk,
        SUM(sr.sr_return_amt) AS total_returns
    FROM store_returns sr
    GROUP BY sr.sr_item_sk
),

combined AS (
    SELECT
        COALESCE(s.item_sk, r.item_sk) AS item_sk,
        COALESCE(s.total_sales, 0) AS total_sales,
        COALESCE(r.total_returns, 0) AS total_returns
    FROM sales_by_product s
    FULL OUTER JOIN returns_by_product r ON s.item_sk = r.item_sk
)

SELECT
    i.i_product_name AS product_name,
    ROUND((combined.total_returns / NULLIF(combined.total_sales, 0)) * 100, 2) AS return_rate
FROM combined
LEFT JOIN item i ON combined.item_sk = i.i_item_sk
WHERE i.i_product_name IS NOT NULL
ORDER BY return_rate DESC
LIMIT 5;

🚀 Result: Huge performance gain! from 290 seconds to 41 seconds

Check out the improved runtime in DuckDB CLI:

🌍 In real-world models, surrogate keys aren’t typically used

unfortunately in real life, people still use text as a join key, luckily PowerBI seems to do better there !!!

🚀 Final Thoughts

LLMs are funny, when I asked chatgpt why it did not suggest a better SQL Query, I got this answer 🙂

I guess the takeaway is this:


If you’re writing SQL queries, always prefer integer types for your keys!

And maybe, just maybe, DuckDB (and databases in general) could get even better at optimizing joins on text columns. 😉

But perhaps the most interesting question is:
What if, one day, LLMs not only generate correct SQL queries but also fully performance-optimized ones?

Now that would be exciting.

you can download the data here, it is using very small factor : https://github.com/djouallah/Fabric_Notebooks_Demo/tree/main/SemanticModel

Edit : run explain analyze show that it is group by is taking most of the time and not the joins

the optimized query assumed already that i.i_item_sk is unique, it is not very obvious for duckdb to rewrite the query without knowing the type of joins !!! I guess LLMs still have a lot to learn

LLMs Are Getting Better at SQL

For no particular reason, I recently opened a Power BI report with two fact tables, exported the model definition to TMDL, saved it as a file, and loaded it into several state-of-the-art LLMs, including ChatGPT, Grok, Anthropic, and Qwen QWQ. I wasn’t particularly interested in comparing specific models—I just wanted to get a general sense of how they performed. To keep things simple, I used only the free tiers.

To my surprise, these models are getting really smart. They all recognized that the file represented a Power BI semantic model. They understood the concept of tables, columns, relationships, and measures, which was quite impressive.

You can download the Model here , i added a semantic Model as a text file in case you don’t have PowerBI

The Power of Semantic Models

Power BI, and before that PowerPivot, has supported multiple fact tables from day one. I remember the first time I used PowerPivot— a colleague showed me how to handle multiple fact tables: just don’t join fact to fact. Instead, use a common dimension, and everything will work fine. Back then, I had no idea about Kimball, multi-stage SQL queries, chasm trap etc. As a business user, I only cared that I got the correct results. It took me years to appreciate the ingenuity and engineering that went into making this work seamlessly. To me, this is an example of great product design: solve a very hard problem, and people will use it.

But this blog isn’t about Power BI and DAX—that’s a solved problem. Instead, I wanted to explore whether LLMs, by reading only metadata, could generate correct SQL queries for relatively complex questions.

Note: for people not familiar with TMDL, it is a human readable format of PowerBI semantic Model ( if human can read it, then LLMs will understand it too)

Testing LLMs with SQL Generation

I started with simple queries involving only one fact table, and the results were straightforward. The models correctly referenced measure definitions, avoiding column name hallucinations. Good news so far.

Things got more interesting when I asked for a query using a more complex measure, such as Net Sales:

Net Sales = [Total Sales] - [Total Returns]

This measure involves two tables: store_sales and store_returns.

Expected SQL Approach: Drilling Across

My expectation was that the models would use the Drilling Across technique. For example, when asked for net sales and cumulative sales by year and brand, a well-structured query would look like this:

  1. Compute total sales by year and brand.
  2. Compute total returns by year and brand.
  3. Join these results on year and brand.
  4. Calculate net sales as [Total Sales] - [Total Returns].
  5. Calculate cumulative Sales

Most LLMs generated a similar approach. Some required a hint (“don’t ever join fact to fact”), but the biggest issue was the final join. Many models defaulted to a LEFT JOIN, which is problematic—some returns for a brand might not occur in the same period as the sales, leading to incomplete results.

That said, some models got it right on the first attempt, which was very impressive. something like this:

WITH sales AS (
    SELECT
        d.d_year,
        i.i_brand,
        SUM(s.ss_sales_price * s.ss_quantity) AS total_sales
    FROM store_sales s
    JOIN item i ON s.ss_item_sk = i.i_item_sk
    JOIN date_dim d ON s.ss_sold_date_sk = d.d_date_sk
    GROUP BY d.d_year, i.i_brand
),
returns AS (
    SELECT
        d.d_year,
        i.i_brand,
        SUM(r.sr_return_amt) AS total_returns
    FROM store_returns r
    JOIN item i ON r.sr_item_sk = i.i_item_sk
    JOIN date_dim d ON r.sr_returned_date_sk = d.d_date_sk
    GROUP BY d.d_year, i.i_brand
)
SELECT
    COALESCE(s.d_year, r.d_year) AS year,
    COALESCE(s.i_brand, r.i_brand) AS brand,
    COALESCE(s.total_sales, 0) AS total_sales,
    COALESCE(r.total_returns, 0) AS total_returns,
    (COALESCE(s.total_sales, 0) - COALESCE(r.total_returns, 0)) AS net_sales,
    SUM(COALESCE(s.total_sales, 0) - COALESCE(r.total_returns, 0))
        OVER (PARTITION BY COALESCE(s.i_brand, r.i_brand) ORDER BY COALESCE(s.d_year, r.d_year)
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_net_sales
FROM sales s
FULL OUTER JOIN returns r
ON s.d_year = r.d_year AND s.i_brand = r.i_brand
ORDER BY COALESCE(s.i_brand, r.i_brand), COALESCE(s.d_year, r.d_year);

An Alternative Approach: UNION ALL

One LLM got creative and proposed a different solution using UNION ALL:

WITH combined AS (
    SELECT d.d_year, i.i_brand, 'sales' AS type, s.ss_sales_price * s.ss_quantity AS amount
    FROM store_sales s
    JOIN date_dim d ON s.ss_sold_date_sk = d.d_date_sk
    JOIN item i ON s.ss_item_sk = i.i_item_sk
    UNION ALL
    SELECT d.d_year, i.i_brand, 'returns' AS type, r.sr_return_amt AS amount
    FROM store_returns r
    JOIN date_dim d ON r.sr_returned_date_sk = d.d_date_sk
    JOIN item i ON r.sr_item_sk = i.i_item_sk
)
SELECT
    d_year AS year,
    i_brand AS brand,
    SUM(CASE WHEN type = 'sales' THEN amount ELSE 0 END) AS total_sales,
    SUM(CASE WHEN type = 'returns' THEN amount ELSE 0 END) AS total_returns,
    (SUM(CASE WHEN type = 'sales' THEN amount ELSE 0 END) - SUM(CASE WHEN type = 'returns' THEN amount ELSE 0 END)) AS net_sales,
    SUM(SUM(CASE WHEN type = 'sales' THEN amount ELSE 0 END) - SUM(CASE WHEN type = 'returns' THEN amount ELSE 0 END))
        OVER (PARTITION BY i_brand ORDER BY d_year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_net_sales
FROM combined
GROUP BY d_year, i_brand
ORDER BY i_brand, d_year;

Edit : people who know what they are doing , did not like the union all solution for performance reason

So, Is SQL a Solved Problem?

No, of course not. There’s still no guarantee of getting the same answer when asking the same question multiple times. Additionally, AI has no real understanding of the data. The moment you introduce filters, you can get all sorts of unexpected results,Although Providing sample values from columns into a semantic model helps a lot, still real-world models are far more complex and often contain ambiguities.

While LLMs generate correct SQL, they don’t always generate the most efficient queries, But you can argue, it is the Database problem to solve 🙂

Compute Considerations

I don’t know enough to make a strong statement about the current cost of running LLMs, but I ran a simple experiment: I tested QWQ-32 on my laptop (which has an NVIDIA RTX A2000 GPU with 4GB of dedicated memory). The good news? It worked. Running an open-source “thinking model” on a personal laptop is already impressive. The bad news? It was painfully slow—so much so that I’m not eager to repeat the test.

From a practical perspective, generating SQL queries this way seems extremely expensive. A decent BI tool can generate similar queries in milliseconds, using orders of magnitude less compute power, but as LLMs continue to improve in efficiency, maybe in a couple of years, the compute requirement to generate SQL Queries will become trivial ?

Final Thoughts

Having experimented with LLMs since 2023, I can say with confidence that they have improved significantly ( LLMs in 2023 were not very good to be honest). They are getting better and, more importantly, cheaper, opening the door for new applications.

The initial promise was that you could just throw data at an AI system, and it would figure out everything. I suspect that’s not true. In reality, to get useful results, you need more structure, and well-defined semantic models will play a crucial role in making asking your Data a reality.