Doing SQL work with LLM aids as a SQL addict

Last call! The call for speakers for the Data Behind The Scenes conference is ending July 31st! Tell your data friends!

A while ago I was testing out vibe coding and finding it a barely net positive experience with plenty of weird frustrations along the way. More recently I've been trying with seeing how these tools hold up for helping work in SQL since every engineer on the planet seems dead set on making LLMs write SQL for them.

In my experience, a lot of engineers don't like SQL. While there's plenty of devs who are comfortable using it, there's significantly more who seem to find it alien and will go to extraordinary lengths to avoid it. They'll create ORMs in an attempt to pretend the database is a familiar programming object. They'll treat it like flat file storage solution which spawns the endless admonishments to "never use select * ".

Given this aversion to SQL, untold numbers of devs have thought up the idea of having an LLM write their SQL for them. I know one senior engineer at another big company say they use one of the LLM services to write their SQL and it works once you take the time to give it all the table schema as context.

But let's face is, most data folks live and breathe SQL for large chunks of their day. The things we would demand of an LLM aren't going to be as easily satisfied. Right? So here's how it's been going for me over the past year working on SQL related tasks for work and personal stuff across a different models, vendors, tooling, and environments.

LLMs require so much context for mere passable SQL from scratch

If I wanted to take the most fundamental "go make me a query from scratch that does $thing" request from an LLM, the most obvious problem was that I needed to provide the system before it can get anywhere.

I'd have to know what tables it needs to touch so that I can provide it a copy of the table schema. I'd have to describe any non-obvious fields and any metrics definitions. If I'm doing some unintuitive custom logic to work around an issue, I'd obviously have to provide that info. In extreme situations I might have to hint at the size of the tables if I need certain optimizations.

In many situations, if I had all that information at my fingertips already, I would be most of the way there to writing the query myself. With all the logic and schema at my fingertips, the only remaining barrier is syntax and aside from dialect-specific quirks, SQL syntax isn't that difficult. It honestly would take more natural language words to describe the query I want than the actual query itself.

If anything the whole exercise makes me realize just how much sheer stuff I was juggling around in my head to write a query quickly. I have shorthands for visualizing some of the stuff in my head but it explains why editing old SQL queries can be so painful.

Overall, I really dislike this entire use case unless somehow these systems manage to pull in the context without your intervention. At the very least, having automatic access to table schema helps to at least let LLMs write basic demo queries that can maybe illustrate how two very unfamiliar tables might work.

LLMs (directly) refactoring complex queries seem more miss than hit

Once in a while I'm faced with wrangling with large, complex (> 200 lines) queries that need fixing – either there's been a breaking schema update or the query now runs too slowly and needs a refactor.

For small queries, I've occasionally just pasted in small queries with a request to "optimize this" and get something that works faster. Often it's smaller a tweak to a subquery/CTE to use or generate fewer rows by filtering earlier. But I've found that when queries get gnarly, with unions, full outer joins, very messy join conditions, etc. LLMs get increasingly dodgy. Sometimes I think the sheer length of a query can start causing issues. Requests to optimize eventually start breaking the query so that the output doesn't even match the original. Attempting to vibe-tweak the logic of one part of the query is going to have unintended side effects on everything else.

Given the length of the queries, the length of any schema context I'm providing, and all the interconnected context needed to write SQL (because the clause order requires bouncing back and forth), LLMs are surprisingly weak at this task. I suppose if a query is large enough to make my head hurt refactoring, an LLM doesn't do much better.

Overall, as with vibe-coding software, vibe-SQLing is leading to much more frustration than productivity. I hate it.

Things do improve when I stop attempting to be lazy and use LLMs more as a handy step stool.

LLMs are pretty decent at explaining SQL

For the same large messy queries, I've had surprising luck getting LLMs to explain what the queries are meant to do in easier-to-understand language. This is especially useful for massive queries that use multiple CTEs in a chained fashion that require a lot of scrolling around to make sense of. I would then go through and check the descriptions against the actual CTE logic and they matched quiet well.

Using this explaining functionality bootstrap my own mental model of a query so that I can plan out my own refactor works quiet well. Especially for queries written by other people.

I've also had a bit of luck pasting in a query and asking for ideas on how to optimize. While the suggestions are often pedantic things like "use fewer rows by filtering first here!" that don't actually work, it does occasionally suggest something that hadn't occurred to me before. Often it's merging some clauses together.

It's possible that all the SQL I ever write are shining examples of perfect optimization from the get-go and I'm just not seeing the full extent of what these models can do with poorly written SQL. But at least the process isn't a horrid game of "spot where the code changed".

LLMs are great for quickly translating across SQL dialects

Thanks to all the different SQL dialects I've crammed into my brain over the years I can never remember the correct way to express "the day before the current date" in whatever it is I'm using that day. So I find it extremely convenient to be able to ask what the equivalent SQL is for a given dialect. I used to do this regularly using just normal web search, so this saves me a few steps every time.

For tiny simple snippets it works well. I haven't really tried for more specialized requests since they rarely come up. I'd be cautious about those. I've tried translating entire larger queries before out of curiosity, I don't recommend it because at that point it becomes a large refactor with all the associated issues.

Selective refactoring can work

My complex work usually involves a number of subqueries to set up data how I need it, and I find that having an LLM refactor those individually by literally copy/pasting only the subquery in works decently well. Often I'm not directly trying to make the query faster, but instead need to rewrite the logic. An oddly common case is taking 2 small subqueries added at different tables and combining them to become a single subquery instead. Merging the logic together in a way that doesn't accidentally double or undercount things can sometimes be annoying to work out (I use ORs and CASEs a decent amount), so for me it's easier to review a proposed change than work it out from scratch.

Code completion is a quality of life boost at times

It mostly depends on a lot of specifics around what context your LLM tooling has available, but I've noticed that code completion suggestions on tools can be useful. Amusingly it's most useful on my "scratchpad" SQL file where I've got over a thousand lines of tiny ad-hoc queries built up over time, giving all sorts of interesting context over my most used tables. It's less "smart" when on a relatively new file. That said, for certain repetitive operations like where you add a new condition to include some new data, and then have to jump up to add the same condition to your SELECT clause, code completion seems to pick up on those changes and suggest them. It's a nice quality of life thing.

Overall

I still think it's a fool's errand to create a useful "text to SQL" engine. As with code, if we could formally state all the specifics and details we want in a program or query from the start, then we'd have the code already. Syntax is a relatively low barrier to productivity. It's in the process of writing the code do we think things through and fix all the gaps in our understanding.

But even if syntax is a relatively low point of friction, it's still a point of friction. So there's lots of smart ways to ease some of the bumps involved. So keep understanding and designing your SQL manually. That skill ain't turning obsolete anytime soon.