A bookings table with three date columns has exactly one right answer to “bookings last month”, and the DDL doesn’t say which. Give a model created_at, booked_at and trip_start_date and it will pick one. It tends to pick created_at, I assume because that’s the column most of the SQL it has ever seen filters on.
Someone on your team knows the answer is booked_at. They also know that CANX means cancelled, and that joining to passengers can triple the revenue. The DDL says none of this. So the model writes SQL that parses, runs, returns a number, and is wrong.
My view is that most wrong SQL from a model is a documentation failure that looks like a reasoning failure. Swapping in a bigger model helps a little. Writing down what the team knows helps a lot, and you can measure how much each kind of note is worth.
Three kinds of knowledge the DDL leaves out
Column types and names are what the catalog gives you for free. What’s missing falls into three groups.
Meaning: which date is the business date, what the amount includes, which columns are legacy and should be left alone.
Values: a status column typed varchar tells a model nothing. Is cancelled CANCELLED, cancelled, CANX or 3? It will guess, and a wrong guess in a WHERE clause returns zero rows or, worse, all of them.
Join shape: foreign keys, where they exist, say two tables connect. They don’t say that one side has many rows per key, and that summing after the join double counts.
I put all three in one file per table and call it a schema card.
One YAML file per table
table: analytics.bookings
purpose: One row per booking a customer made, including cancelled ones.
grain: booking_id
columns:
booked_at:
note: When the customer paid. This is the booking date for revenue and "bookings last month".
created_at:
note: When the cart row was created. Can be days before booked_at. Not for reporting.
trip_start_date:
note: Local departure date. Use only for questions about travel dates.
status:
note: Lifecycle state. Revenue questions count CONF and COMP only.
sample_values: [PEND, CONF, COMP, CANX, RFND]
total_amount:
note: Gross amount in the booking currency, not converted. Never sum across currencies.
currency:
note: ISO 4217 code of total_amount.
sample_values: [USD, EUR, GBP]
joins:
- to: analytics.customers
condition: bookings.customer_id = customers.customer_id
cardinality: many_to_one
- to: analytics.booking_passengers
condition: bookings.booking_id = booking_passengers.booking_id
cardinality: one_to_many
warning: Multiplies booking rows. Aggregate passengers first, or do not sum total_amount after this join.
- to: analytics.fx_rates_daily
condition: bookings.currency = fx_rates_daily.from_currency and cast(bookings.booked_at as date) = fx_rates_daily.rate_date
cardinality: many_to_one
common_mistakes:
- Filtering on created_at when the question says "booked".
- Counting CANX and RFND bookings as revenue.
- Summing total_amount after joining booking_passengers.
The joins list is an allow list. If a join isn’t declared, the model is told not to invent it. That one rule removes a whole class of creative joins on columns that happen to share a name.
Sample values go only on low-cardinality columns such as status, currency, region and plan. Pasting sample rows of names and emails into a prompt is a privacy problem and adds little.
The card is built, not hand-maintained as a second copy. Descriptions should live where the table is defined: dbt model and column descriptions, column comments in the warehouse, or comments in Unity Catalog. A small builder job reads those, runs a select distinct on the flagged columns, and writes the card. Only the joins and the mistakes list are written by hand, because nothing in the catalog knows them.
From the catalog to a test set
The builder writes cards to a store. At question time a retriever picks the cards for the tables the question probably touches, the model writes SQL from those cards, and a dry run checks that the query compiles against the real schema before anything executes. I covered what should stand between that query and the database in an earlier post on the read-only guard.
With a dozen tables you can skip retrieval and send every card. Past fifty or so you can’t, and then retrieval becomes its own source of error: if the right card isn’t picked, nothing downstream can fix it. Score retrieval separately so you know which stage failed.
The last stop is a fixed set of questions, each with a reference query a person wrote and checked. A generated query passes if its result matches the reference result. Not if it looks right.
When a question fails for a schema reason, the fix goes into the card: a note, a sample list, a join warning. It doesn’t go into the prompt as a one-off instruction. That’s the thin loop in the diagram, from the test set back to the builder.
Skerry Trips, 60 questions, 14 tables
The run below belongs to Skerry Trips, a travel company I made up. The scores are the kind of spread I’d expect to see, not a measurement, so trust your own run over my table.
The team has 14 tables and writes 60 questions that people really ask, with reference queries. Then they run the set several times, changing only what the model is shown.
| What the model sees | Correct out of 60 |
|---|---|
| Bare DDL | 41 |
| DDL + column descriptions | 47 |
| + sample values on status and currency columns | 52 |
| + declared join paths (full card) | 57 |
| Full card, descriptions removed | 54 |
| Full card, sample values removed | 51 |
| Full card, join paths removed | 52 |
Read the top four rows and it looks like each section adds five or six questions. Read the bottom three and the picture changes.
Descriptions, the section everyone writes first and argues about longest, cost only three questions when removed. The sample values and the join warnings had been carrying part of their load: a model that sees CONF, COMP, CANX, RFND works out most of what the status description would have said. Sample values were the cheapest section to produce, one query per column, and removing them hurt most.
That’s the reason to run both directions. Adding sections one at a time credits whichever came first. Removing one at a time from the full card tells you what each is worth given the others. On a different schema the order will be different. A warehouse with clean enums and a tangle of bridge tables will lean on the join section instead. Once the sixty questions exist, an afternoon of runs will tell you.
The three that never pass
At 57, the remaining failures are a different sort. One question asks for “repeat customers”, and the reference query means two paid bookings within 12 months, while the model counted anyone with two bookings ever. Another asks for trip revenue, and finance and the product team disagree about whether the service fee belongs in it. The third says “last quarter” and nobody wrote down whether that’s the calendar quarter or the fiscal one.
No card fixes these, because the people don’t agree either. Putting a ruling in common_mistakes just hides the argument inside a prompt. Those terms need one owner and one definition, kept somewhere queries can be generated from, and that’s a metrics layer problem.
So I’d stop tuning cards once the failures stop being about the schema. What’s left on the list is the set of words your company hasn’t finished defining, and it’s useful to have that written down with question numbers next to it.