Hey LLM, Listen to Me — From Anxious to Steady Developer: SQL Verification

Part 1 · Freedom to the LLM, accuracy to the system

Introduction

Hi, I'm Seungjae Lee, an anxious developer who wants to keep LLMs under control. This post is a record of how I came to control LLM-written SQL deterministically. I plan to write it in two parts: this first part covers everything from the moment the LLM writes the SQL until it runs, and the second part covers getting the LLM to write the SQL right.

I build an ad management service used by agency marketers. My part of it is a feature for asking about performance in chat. Marketers used to download performance data and aggregate it with Excel formulas, and I wanted that to take a single question.

Why won't this work?

At first the SQL was built in code. It was the SQL builder from my previous post: an AI pulled dimensions, metrics, periods and filters out of the sentence, and the builder assembled the SQL clause by clause from those values. The same input always produced the same SQL, so it was deterministic and easy to test.

But it took too much work. Every new requirement meant large changes to the parameters and assembly rules. Above all, users' questions never end, while the builder could only answer the branches built in advance. A question like “Among the keywords whose conversions dropped since last week, raise the budget by 15% and then show the top 10 by ad spend” meant writing a new branch every time.

So I switched to having the LLM write the SQL itself.

High freedom, but deterministic, 99% accurate, and please drop a result file too

But the requirements stayed the same. These are numbers advertisers see, so the SQL had to be deterministic and at least 99% accurate. Yet an LLM writes different SQL for the same question every time. It is not a function that maps the same input to the same output, so that is only natural, and there is no guarantee it always writes correct SQL either.

The hardest cases are when the syntax is fine but the SQL is not what we meant, and when the SQL is right but the LLM's answer is wrong. For example, if you join performance and conversions row by row instead of aggregating each first, the ad spend rows are duplicated once per conversion type, yet the query runs fine and the table fills in plausibly.

Many advertisers share one table, so a single wrong account condition mixes in someone else's data, and there were date mistakes too, such as taking “the last 7 days” as 6 days.

The answers were wrong too. Even when the SQL ran correctly, the model slipped while relaying the results: it called the sum of only 30 rows the account total, or lumped uneven values like 35, 114 and 81 into “around 100 a month”.

Asking the same model to review its work just repeated the same mistakes. So I decided to leave interpreting the question and writing the SQL to the LLM as much as possible, and to control everything until that SQL runs strictly in code.

Controlling the SQL

This section covers only what happens after the LLM finishes writing the SQL until it runs. What happens before the SQL is written comes in part 2.

The LLM does not write the account ID or the period into the SQL; it only leaves placeholders such as :id, :start_date and :end_date. These are not SQL syntax but a convention inside our system meaning “the server fills this in”. The ID is filled with the account the user picked on screen, passed by the backend through the session, and for the period the LLM passes a parameter such as period_days=7 and the server computes the dates and fills them in.

The completed SQL is parsed with sqlglot and checked on its syntax tree. Write queries, SQL missing a partition condition, and SQL reading an ID other than the session's are rejected. What I cared about most was JOINs. Joining performance and conversions row by row without aggregating each side first is treated as a JOIN whose grains don't match, and is rejected. The LLM often uses CTEs as well, so this check runs for each CTE separately.

The same thing happens when you look at campaign A's conversions by type. Attach ad spend to each per-type row and the same spend repeats, inflating the moment you total it in the result file or Excel. So the checker rejects such SQL and returns the reason: spread the types into columns. The fixed SQL keeps one row per campaign and puts purchase, cart and sign-up into separate columns.

SQL that passes the rules is checked once more with Athena's EXPLAIN. It only builds an execution plan without reading data, so there is no execution overhead in cost or time, and it catches engine errors such as missing columns or comparisons with mismatched types in advance.

Oh, come to think of it, could the answer include insights too?

There was also a request to include light insights in the answers. So that the LLM can give a rich answer, the server builds a summary query wrapping that SQL and computes the row count, totals, distributions and top and bottom rows along with it. The LLM uses these values to point out what is large and what is empty.

The summary query is built by rule from the kinds of output columns read with sqlglot. Additive metrics get totals and distributions, ratios get sum of numerators ÷ sum of denominators, and names and IDs get distinct counts. Comparisons between rows use only values from the calculation tool my colleague built, and whether the numbers in the answer exist in the result is matched with regular expressions and logged.

Closing

I recently went to WOOWACON, a conference hosted by Woowa Brothers. There were many LLM-based services, and while talking with a speaker who presented a translation feature, I heard the phrase “an LLM is a translator that translates the system” and strongly agreed.

I think the same. An LLM translates the user's request into our system's language. Tell it the system's intent and purpose, who uses it and what it can do, and the LLM interprets requests on that basis and answers. In a way, you could also call it a compiler that compiles the service.

In part 2, I'll cover getting this translator to write the SQL right.

Thanks for reading.