Deploy Guardrailed Text-to-SQL on a Read Replica

Tier: Advanced · Workflow area: Governed Data Access · ~45 minutes

"Let people query in plain English" is now a stakeholder demand, and the naive version — an LLM pointed at 400 raw tables — produces confident wrong answers that erode trust in the data team. The DBA's contribution is the part no vendor demo shows: shrinking the surface to curated views the model can actually reason about, fencing the blast radius, and refusing to launch below a measured accuracy bar.

When to use

• Recurring ad-hoc query load on a few well-understood subject areassales, tickets, inventory. High question volume over a curatable scope is exactly where this pays.
• Analysts wait days for simple pullsthe backlog of "just give me a number" requests is the business case; the eval scorecard is how you ship it responsibly.
• A vendor NL feature is being switched on anywayOracle Select AI, SSMS Copilot NL query, a BI copilot. This playbook's scoping and eval discipline is how you evaluate it before enablement, not after complaints.

When NOT to use

• Questions whose answers drive regulated filings or money movementthose queries need a named human author and reviewer. NL-to-SQL serves exploration, not attestation.
• Over a schema nobody can describeif pb-27's dictionary does not exist, build it first; text-to-SQL over undocumented columns automates the misunderstanding.
• For write access, everthe phrase "natural-language UPDATE" should end the meeting. This pattern is read-only by construction.

The recipe

1. 1.

   Curate semantic views — the accuracy lever nobody wants to hear about. Pick one subject area and build 3–6 views that pre-join tribal knowledge, rename cryptic columns, and pre-apply business rules:

   Copy

   CREATE VIEW rpt.sales_orders AS
   SELECT o.id            AS order_id,
          o.ordered_at    AS order_date,
          c.region        AS customer_region,
          c.segment       AS customer_segment,
          ol.amount       AS line_amount,
          o.status        AS order_status   -- 'open','shipped','refunded'
   FROM orders o
   JOIN customers c   ON c.id = o.customer_id
   JOIN order_lines ol ON ol.order_id = o.id
   WHERE NOT c.is_test;
   

   Comment every view and column (pb-27 output goes here). The model will only ever see these views — raw tables are out of scope by design.

2. 2.

   Fence the execution surface on a replica. A dedicated role, on a read replica, with hard limits:

   Copy

   CREATE ROLE nl_query LOGIN PASSWORD '...' ;
   GRANT USAGE ON SCHEMA rpt TO nl_query;
   GRANT SELECT ON ALL TABLES IN SCHEMA rpt TO nl_query;
   ALTER ROLE nl_query SET statement_timeout = '15s';
   ALTER ROLE nl_query SET work_mem = '64MB';
   ALTER ROLE nl_query SET default_transaction_read_only = on;
   

   Row caps live in the harness (append LIMIT 1000 if absent), and every executed statement is logged with the question that produced it. The blast radius is now: a slow read on a replica, killed at 15 seconds.

3. 3.

   Write the system prompt as a contract, not a vibe.

   Copy

   You translate business questions into PostgreSQL SELECTs over ONLY
   these views (DDL + column comments below). Rules:
   - SELECT only; single statement; no DDL/DML/system functions.
   - Use only listed views and columns. Never invent names.
   - If the question is ambiguous or unanswerable from these views,
     return REFUSE: <one-line reason> instead of SQL.
   - Prefer explicit date ranges; 'last quarter' = last full calendar
     quarter.
   [paste view DDL with comments]
   

   The REFUSE path is a feature: an honest "cannot answer" beats plausible wrong SQL every time, and you will score refusals in the eval.

4. 4.

   Build the eval set before launch — 25 questions with expected results. Source them from real analyst requests: 15 straightforward, 5 requiring business-rule knowledge ("net of refunds"), 5 deliberately ambiguous or out-of-scope (correct answer: REFUSE). For each, store the expected result set or aggregate from a hand-written query. Score by execution accuracy — does the generated SQL's result match the expected result — not by whether the SQL text looks right.

5. 5.

   Measure, curate, measure again. Run the eval through the full pipeline. Typical honest first pass: 17/25 correct, 3/5 ambiguous correctly refused. Read every failure; most trace to a missing column comment, an uncaptured business rule, or a view gap — fix the views and comments, not the prompt, and rerun. A worthwhile result: pre-curation 68% → post-curation 88%, refusal precision 5/5. Set the launch bar in writing (say, ≥85% with zero confident-wrong on the ambiguous set) and hold it.

6. 6.

   Launch narrow, display the SQL, keep scoring. Roll out to a pilot group, one subject area. Always show the generated SQL and the row count alongside results — sunlight keeps trust calibrated. Log every question; misses become next month's eval additions and view fixes. Expansion to a second subject area repeats this recipe, not just the prompt.

Make it yours

• Oracle Select AIthe same discipline maps directly: a profile scoped to curated views (sql_only first), object lists as your fence, and your own eval set run before enablement; Select AI's generated-SQL visibility is your display-the-SQL step.
• SSMS Copilot / Fabric NLscope through a database role that can only see the curated schema; the eval harness runs the generated T-SQL against expected results identically.
• Semantic-layer shopsif a dbt/LookML layer already encodes business rules, point the model at that layer's definitions instead of duplicating them in views; execution accuracy is still the score.
• BI copilots (Power BI, etc.)you often cannot control the prompt, but you still control the exposed model and can still run the eval; a copilot that fails your 25 questions does not get rolled out to finance.

Quality check

• Accuracy is execution-measured against expected results"the SQL looks correct" is not a metric. Result-set comparison against hand-written ground truth is.
• Ambiguous questions produce refusals, not confident answersrefusal behavior was explicitly tested. A system that never says "cannot answer" is unsafe at any accuracy.
• The role fence was attack-testedyou tried DML, system catalogs, cross-schema reads and a runaway cross join through the pipeline and watched each die at the role, timeout or row cap.
• The launch bar was set before the measurementdeciding the threshold after seeing the score is how 68% ships. Bar first, then eval, then the go/no-go writes itself.