Validating SQL: Prove the Result

Concept. A query that runs is not a query that is right. You trust SQL the way you trust any code, with tests: a unit test proves one query returns the answer you expect, a regression suite proves it stays right as definitions change, and verified queries compose into a library you build on. All three need code; none of them work by arguing in English.

Intuition. The debug table is the unit test, the tests you keep are the regression suite, and the verified queries are the library you compose on.

You have a library of definitions, and a query composed from it. It runs. But "runs" only means the syntax parsed, not that the rows are the ones you meant. The same request often reads two ways, and both execute. Proving which one is correct is a separate step. A test does exactly that.

Unit Test: One Query

"Users who don't listen to Taylor Swift" reads two ways, and both run. One is the obvious guess, a raw LEFT JOIN that keeps a user if any of their plays is not Taylor. The other calls your library term: the users who are not in listener_of('Taylor Swift'). You know the answer to expect, only Pluto never played a Taylor song, so trace each the way the engine runs it, one clause at a time, and see which reproduces it.

Query A (LEFT JOIN) traced on one page. The joined plays show Mickey, Minnie, and Daffy each played a Taylor song and a Beatles song; Pluto played nothing. The WHERE clause strikes out the five Taylor rows, but every user still has a surviving row (a Beatles play, or Pluto's NULL), so GROUP BY returns all four users. Keeping the Taylor listeners is the bug.

Query B (NOT IN) traced on one page. listener_of('Taylor Swift') returns Mickey, Minnie, and Daffy; Pluto is not in it. NOT IN strikes out those three Taylor listeners, leaving only Pluto, which is exactly what the spec asked for.

Figure 1. Two executable readings traced clause by clause. Query A (the raw LEFT JOIN) removes the Taylor rows, but each user keeps another surviving row, so GROUP BY returns all four users. Query B (calling listener_of) removes the three Taylor listeners and returns only Pluto. A unit test resolves the ambiguity by checking the returned rows.

Regression: The Eval Set

A unit test proves the query is right today. Grow that into an eval set, a short list of questions with confirmed answers on a frozen slice of your data (non-Taylor listeners → Pluto; Taylor listeners → Mickey, Minnie, Daffy). Then every change re-runs the whole suite, and a definition that silently shifts fails a test that day, not months later after a dashboard has already lied.

A left-to-right timeline of validation. First, one unit test proves non-Taylor listeners return Pluto, with a green check. Next, you keep it and the eval set grows into a stack of verified tests. Then you re-run the whole suite on every change: a grid of runs by version, mostly green, until a definition drifts at v4 and test B fails red that day.

Figure 2. Validation over time. A unit test proves one query today (left); you keep it, so the eval set grows into a suite of verified questions (middle); then every change re-runs the whole suite (right), and a definition that drifts fails a test the day it drifts, not months later. The eval set answers "does the library still work" with a number instead of a hope.

Verified Queries Compose

A verified query is itself a function, and functions compose. You proved "non-Taylor listeners" returns just Pluto, so the next question builds on it, and you verify only the new layer.

Three columns of verified SQL blocks (blue, each with a green check). Base metrics on the left (active user, Taylor listener) compose into composed queries in the middle (weekly actives, non-Taylor listeners), which compose into a complex query on the right (weekly active non-Taylor listeners). Arrows run left to right, and each block is verified once and reused.

Figure 3. Each block is a verified query (blue, green check). Base metrics compose into bigger queries, which compose into a complex one. Every block is verified once and reused, so a new query verifies only its own layer. A library of verified SQL is worth more than any single query: you build on it instead of re-proving it.

Takeaways

  1. Runs is not right. Both readings of a request execute; only a test tells you which returns the rows you meant.

  2. Unit test. Trace one query against an expected result; arguing in English cannot settle which reading is right.

  3. The eval set. Keep the tests and re-run them, so a definition cannot drift without a test failing that day.

  4. Verified queries compose. Each is proven once and reused, so a bigger query only verifies its own new layer.