Letting a model write SQL directly isn't a problem of model strength. It's that its solution space contains a pile of things it should never touch: physical table names, column names, join keys. While those remain in the space, it has some probability of inventing a column that doesn't exist — and inventing it convincingly.
There are two ways to remove that. The industry consensus is the first: build a semantic layer. Write metric definitions, dimension hierarchies, the join graph and synonyms as structured definitions; let the model face only business semantics; and have a deterministic compiler translate that into SQL. dbt MetricFlow, Cube and Snowflake Semantic Views are that road, productised.
We took the second, and so far haven't turned back.
Enumerate what can be queried, as parameters
Our data agent doesn't emit SQL. It emits a structured record: fifty-odd typed, named parameters. Which measure, which dimensions to group by, which filters, what to exclude, how wide the time window — each is a field, and nothing proceeds until the schema validates. Downstream is a function with no model in it at all, which compiles that record into a ClickHouse query.
The model never sees a table name.
In OLAP terms, that parameter set is the query surface of a cube: two count measures, seventeen grouping dimensions, a set of slicers, plus a drill-through mode for detail rows. The model is writing a slice specification; the syntax just happens to be JSON. So it isn't text-to-SQL. It's text-to-cube-query.
On a single fact table this is close to free: no ontology to maintain, type checking supplied by the framework, and hallucinated column names structurally impossible.
Where it deserves to be challenged
The cost is equally clear: one function is one cube.
We now have three — historical flights, empty legs, medically configured aircraft. Three independent query surfaces, each defining its own measures. Which raises an obvious suspicion:
Would the same business question get two different answers from two cubes?
That isn't a theoretical worry. The three cubes describe the same aircraft, the same airports, the same operators, and "what counts as one aircraft" is a line of code inside each. One of the main reasons semantic layers exist is to prevent exactly this drift — they supply conformed dimensions, so a concept means the same thing everywhere.
So I reconciled them: take a set of aircraft that fall inside two cubes, and ask each one how many aircraft there are.
The two numbers matched.
Why they matched
Not because we were more disciplined. Because normalisation happens on the write side, not the read side.
The database stores two columns: the raw registration, and one already-normalised registration — hyphens, spaces, dots and underscores stripped, lowercased. Every cube keys on the second one. Normalising again at read time is idempotent; it changes nothing.
In other words, identity isn't computed at query time. It was materialised at write time.
Generalised, that is the whole of this piece:
A conformed dimension can be a column, not a layer.
Aircraft models, airports, operators, manufacturers, cities and countries all have canonical IDs, handled the same way. Those IDs can't drift, because there is no such thing as "two implementations" of them — there is no implementation, only a column of data. They need no execution engine either, because joins key straight onto them.
Trading a column of canonical keys for a set of semantic-layer definitions is a good deal in this setting.
But it has a hard boundary
I don't want to write this up as a universal conclusion, because it rests on one firm precondition:
The concept must have a unique canonical key.
Aircraft do — the registration. Airports do — the ICAO code. So do operators, models and cities. What they share is that each points at something objective in the world with a clear boundary. The canonical key can be materialised because it already existed.
"Active business" doesn't. Neither does "customer value", or "a real task". Those are composite concepts, defined by a chain of conditions, and that chain is a decision people made and will change as the business moves. You can't materialise it into a column, because today it equals one expression and next quarter it may equal another.
That is where a semantic layer is genuinely irreplaceable: it doesn't govern who is who, it governs what counts. The first can be frozen into data; the second can only be accumulated as versioned definitions.
So the test isn't how many tables you have. It's: does the concept you need consistency on have a canonical key you can materialise? If yes, use a column. If no, you need a layer.
Our weakest point
Holding ourselves to the same standard: part of our definitional content has been neither materialised nor defined. It lives in a prompt string.
The code mapping for aircraft categories (which number means light jet) and the region ID mapping both sit in the parameter documentation we hand the model. They are textbook semantic-layer content — enumerations and synonyms — living in the worst possible place: a block of text that can't be tested, can't be meaningfully diffed, and whose edits require re-validating model behaviour.
That one I intend to fix. The canonical-key part I don't plan to touch, but the enumerations have to come out of the prompt.
One aside: not letting a model write SQL, and not letting a video model render readable text, are the same move — take the part that has to be exactly right out of the generative model's solution space. It looks completely different in different media, and it is one judgement.