PrismPrism

“Text to SQL” demos work because the question is simple and the schema is small. Real warehouses have hundreds of tables, several plausible definitions of revenue, and columns nobody should be able to read.

The failure mode that matters is not a syntax error — those are caught immediately. It is a query that runs cleanly, returns a number, and quietly answers a different question from the one that was asked.

Authorisation first

The asker’s role determines which tables, rows and columns exist at all. Permissions are applied before the model sees the schema.

Certified metrics

Business terms have owned definitions. The system uses them and names them; it does not invent an interpretation.

Read-only, always

No component in the system holds a credential that can write.

Cost ceilings

A natural-language interface makes it trivially easy to ask an expensive question.

A question is resolved in two stages. First the system matches the words in the question against a metric layer — a small set of certified definitions with owners and review dates. Then it plans a query using only those definitions, the permitted schema subset, and an explicit grain.

The plan is produced and displayed before any SQL is generated. A visible plan turns a wrong answer into a debuggable one: you can see that the system chose weekly rather than monthly grain, or the uncertified metric rather than the certified one.

When a question cannot be mapped to a certified definition, Prism says so and offers the nearest definitions rather than guessing. Refusing to answer is a feature of an analytics tool, not a shortcoming.

Permission resolver

Produces a per-role schema view with row filters and column masks already applied.

Metric layer

Certified definitions with SQL fragments, exclusions, owners and review dates; retrieved by semantic match on the question.

Planner

Emits a structured plan — metric, grain, filters, comparison, breakdown — before any SQL exists.

Compiler

Deterministically renders the plan into SQL. The model does not write the final query text.

Execution sandbox

Read-only connection, statement timeout, row cap and scan-cost ceiling.

Result guards

Sample-size checks, mask-leak detection, and concentration analysis that flags when one segment drives a headline change.

Every answer arrives as four things at once: a chart, the definition used, the SQL that ran, and any caveat the guards raised. All four are visible without a click, because the caveat is the part people skip when it is hidden.

The concentration warning turned out to be the most valuable element. “Refunds rose 2.4 points” is a different fact from “one store accounts for 71% of that rise,” and a chart alone will not tell you which you are looking at.

Plan, then compile — don’t generate SQL directly

A structured plan is reviewable, diffable and testable. Raw generated SQL is none of those things.

A metric layer rather than a better prompt

Ambiguity about what “revenue” means is an organisational problem. Software should surface it, not paper over it.

Permissions resolved before schema exposure

Filtering results after the fact leaks the shape of data through error messages and row counts.

Vega-Lite specs, not chart images

A declarative spec is inspectable and re-renderable, and the chart choice can be validated as data.

We wrote an evaluation set of question–plan pairs rather than question–answer pairs. Asserting on the plan catches the dangerous class of failure: the right-looking number computed from the wrong definition or grain.

A second set contains questions the system should refuse — requests for masked columns, questions resting on uncertified metrics, and questions whose grain is genuinely ambiguous. Correct refusal is scored as a pass.

Permission behaviour is covered by conventional integration tests per role, asserting that forbidden tables are absent from the generated plan rather than merely absent from the result.