How I made AI Search “Whatever data you want to see…” for Find Me in Chicago.
Everyone knows LLMs can confidently hallucinate and then often refuses to admit its hallucination even when prompted with clear evidence. So it is important to use schema grounding for AI to have any chance to succeed at Text-to-SoQL.
There are different approaches to this, but at a minimum you need to provide a list of valid field names, an optional datatype, and a description of special rules for interpretation. For example, a sample of the beginning of the Traffic Crashes AI schema definition based on the Chicago Data Portal:
- crash_record_id (number)
- crash_date
- crash_date_est_i: ‘Y’ = Desk Report or Delayed Report filed days later.
- posted_speed_limit
- first_crash_type: Values: ‘PEDALCYCLIST’, ‘OTHER NONCOLLISION’, ‘OTHER OBJECT’, ‘HEAD ON’, ‘ANIMAL’, ‘PEDESTRIAN’, ‘SIDESWIPE SAME DIRECTION’, ‘SIDESWIPE OPPOSITE DIRECTION’, ‘REAR END’, ‘REAR TO FRONT’, ‘REAR TO SIDE’, ‘TURNING’, ‘TRAIN’, ‘ANGLE’, ‘PARKED MOTOR VEHICLE’, ‘FIXED OBJECT’, ‘OVERTURNED’.
- Etc…
This is a minimalist, high-signal schema. Explanations of columns and metadata fields are stripped to maximize self-attention weights. This is helpful across the CHAD challenges (Cost, Hallucinations, Ambiguity, and Determinism).
Assuming your column names are not opaque, LLMs are pretty good at inferring datatypes, which is why it I made them optional. An LLM will infer crash_date is a date, and posted_speed_limit is a number without explicit instruction. Id fields are often system defined and often show up as text, number or something else entirely different. If posted_speed_limit was, in fact, a text field for whatever reason, or if the AI started inferring it as a text field for whatever reason, just add that guidance as a special rule for interpretation.
An important aspect of this is the enumeration of certain field values. Without this the LLM has no reliable way to know what values exist for and will likely hallucinate values that don’t exist. A wrong guess from AI usually results in a misleading empty result. The free SODA API for the Chicago Data Portal doesn’t support “foreign keys” for public use, so Find Me in Chicago just enumerates the values.
There are numerous architectures and Text-to-SQL frameworks that insert a “dry run” before generating a query on the premise that there’s no way to verify correctness without execution. I appreciate the sentiment, but I am puzzled how “dry run” pre-processing helps much. SoQL database queries already go through mandatory pre-processing for schema and syntax before they are executed and return clear error messages when they cannot be executed. Doing a dry run just to confirm all the fields actually exist and the query is syntactically correct is a good example of a YAGNI (You Ain’t Gonna Need It) feature for most applications.