Validating SQL: Prove the Result
Concept. Test SQL as you test other code: run the query on a test suite and compare its output with expected rows. A regression suite repeats those checks as definitions change, and tested queries compose into a library you can build on.
Intuition. A debug table helps you derive the expected rows. An executable unit test runs the query and checks that it returns those rows.
Unit Test: One Query
Debug Tables showed how to trance queries; Query Equivalence showed how to triage between multiple plausible queries. A unit test turns that check into code. It is a query plus the rows it must return. Fix the input (the course data), work out the expected rows once by hand-tracing, and write them down.
-- Test: who listens to Taylor Swift, on the course data?
SELECT name FROM Users
WHERE user_id IN (SELECT user_id FROM listener_of('Taylor Swift'))
ORDER BY name;
-- Expected: Daffy, Mickey, Minnie
A test runner, such as a notebook cell, pytest, runs the query and compares: same rows, pass; any difference, fail. If a later edit made listener_of also catch Pluto, this query would return four names, and the test would fail that day.
Regression: The Eval Set
A unit test checks one query today. A regression suite re-runs every such test each time a definition changes. Grow your tests 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 shifts unexpectedly fails a test that day.
Figure: One test today, a suite that holds over timeA left-to-right timeline of validation. First, one unit test checks that non-Taylor listeners return Pluto on a fixed fixture. Next, the eval set grows into a stack of tests. Then every change reruns the suite, and an unexpected definition change makes one test fail. Color key: blue marks the stages and time axis, green marks a passing test, and red marks a regression.
Figure 1. Validation over time. One test checks a query on a fixed fixture. Keep it, add more cases, and rerun the suite after each change. Tests provide regression evidence for the behavior they specify; they do not prove correctness for every possible dataset.
Verified Queries Compose
A tested query can serve as a reusable building block. When a new query composes several blocks, add tests for the new layer and keep running the tests for every dependency beneath it.
Figure: Tested definitions composeTwo tested library definitions, listener_of(artist) and active_since(days), compose into a tested query, active_since(7) EXCEPT listener_of('Taylor Swift'): active users who skip Taylor. When listener_of changes, its test and the composed test re-run; the active_since test is unaffected.
Figure 2. Tested definitions compose into a tested query. Change listener_of and its test re-runs, along with every test built on it; active_since's test is unaffected.