How I made one report SQL builder trustworthy
Hello. I'm Seungjae Lee, an aspiring ultra-SSS-tier developer. This piece is a record of how I made one SQL builder — the one that produces ad reports — trustworthy.
The service I work on has a builder that produces reports using SQL. When a user makes a request in natural language, like "show me conversions by campaign for last month," an AI agent interprets that sentence and extracts dimensions, metrics, period, and filters as parameters. The builder takes those parameters, assembles the SQL to run on Athena clause by clause, and returns the execution result as a report. A single sentence of natural language becomes SQL, and then, right away, the numbers the advertiser sees.
The problem is that with this kind of code, a mistake leaves no trace. Unless the SQL syntax breaks, the query runs just fine and the table fills in neatly. Even if revenue gets double-counted and inflated twofold, no one notices — only the numbers are quietly wrong.
So I set out to think through the tests that would make this builder trustworthy. This piece is a record of that thinking.
The way I see it, tests fall broadly into two kinds: unit tests and integration tests. If you asked me which of the two matters more, I would answer that both matter. They serve different purposes.
A unit test is verification of a contract. It confirms the contract that "given this input, it returns this output" — in other words, the spec. Because it doesn't pull in real infrastructure, it's cheap and fast, but its environment differs from reality. In production, code calls infrastructure, modules coordinate with each other, and requests travel out over the network. Unit tests don't. On top of that, unit tests mostly pass. That's by design — you split success cases from failure cases and set them up with mocks precisely to check the spec. So the role of unit tests lies in spec verification and in catching how a change propagates when you modify code.
Integration tests, by contrast — to varying degrees — try to match the real production environment as closely as possible. They can test actual behavior, but they're correspondingly heavy and, in some cases, they incur charges.
For real code to go out to production, integration tests are indispensable. And these two must always be developed together for the test code to earn its keep.
When it came time to actually test, there was a problem. AWS infrastructure like S3 and Athena was in place, but there was no data to test on top of it.
So I started with mock data. I created small fake data, worked out its values by hand with pandas, then fed the same input into the builder and matched the results of the SQL it produced against them. Checking the builder's numbers not with the builder itself but against an independent calculation like this is called the test oracle technique. The two paths are completely independent, and if even a single cell disagrees, it fails.
At this stage I also looked at data consistency, but what I mainly wanted to confirm was the spec. Whether the SQL breaks, whether the builder branches correctly according to conditions, whether it picks the right table. I was trying to verify the shape of the SQL that the builder logic produces.
Even the moment real data was loaded, I could just build a new test oracle. Fortunately it verified well on real data too, but before long I ran into another problem. Requirements always change, and bugs are found. Every time, the process of refactoring the code and re-fixing the tests is too repetitive and too tedious.
In designing the tests, I held three things important. First, they should be reproducible whenever run. Second, it should be traceable what was changed and why. Third, these two should run automatically, not by human hand.
First principle: reproducibility. I fix the dataset used for verification, and on top of it I re-run every case each time. Whenever I touch the builder logic, I throw the same cases at the same data to confirm that what used to be right is still right. It's a kind of regression test. I also made the verification scripts permanent inside the repository, so I can re-run that exact round just from the file name.
Second principle: traceability. Every time I run the tests, I leave verification documents. One is the narrative: the goal, the data source, the formulas, the per-round results, and — when something failed — the cause and how it was fixed. The other is the test set: the full text of every SQL run so far, each result, and how to reproduce it. Neither is ever deleted — I only append.
Third principle: automation. No one can do all of this by hand every time. So I built a test agent and skill dedicated to the SQL builder. The agent lays out the cases, and the skill walks through the verification procedure in the same order every time. Reproducibility and traceability become the default behavior of a tool rather than a matter of human willpower.
Writing tests always seems harder than writing the implementation. A well-written test lets you infer the original code just by reading it, and becomes an executable spec in its own right.
If I really had to rank developers by tier, I'd say the developer who writes meaningful test code well is precisely the ultra-SSS-tier developer I mentioned earlier.
That's why, these days, I spend a lot of thought on how to test and how to verify.
Thank you for reading.