Run NL-to-SQL Self-Serve with a Known-Good Cross-Check

Workflow area: Analytics & Experimentation · Tier: Foundation · Hands-on time: ~30 minutes

The dangerous thing about NL-to-SQL is that it almost never fails loudly. The query runs, the chart renders, the number is plausible — and the join double-counted users with multiple workspaces, or "active" meant something different from your dashboard's "active." A wrong number delivered confidently is worse than no number, because it ships upward in a slide. Every step here exists to catch the quiet failure.

When to use — and when not to

• Use it for exploratory questions"how many orgs hit the approval limit last month?" — where waiting a week for the data team means the question dies unasked.
• Use it to draft queries for data-team reviewarriving with a working draft turns their week into an hour.
• Use it when you can name a cross-checkthe discipline only works if some trusted number (a certified dashboard, a finance report) overlaps your question's territory.
• Skip it for board, revenue, and compliance numbersanything with money or legal consequences goes through the data team's certified pipeline, full stop.
• Skip it if you can't read SQL at allyou don't need to write it, but you need to follow a JOIN and a WHERE well enough to inspect one; spend the hour learning that first, it's the highest-ROI hour in this track.

The recipe

1. 1.

   Assemble schema context once. Table names, column meanings, known traps — the tribal knowledge every data team carries: "use events_prod not events_raw; user_id is null for anon sessions; timestamps are UTC; org-level metrics must dedupe on org_id." Store this as a reusable block (or rely on Snowflake Cortex Analyst / BigQuery's Gemini features, which read schema metadata directly — and still benefit from the traps list).

2. 2.

   Ask with the question pinned down. In Claude, or your warehouse's native NL interface:

   Copy

   Write a SQL query ([DIALECT: Snowflake/BigQuery/Postgres]) to
   answer this question.
   
   Question: [PLAIN ENGLISH — e.g. "How many distinct organizations
   had at least one bulk approval in July 2026?"]
   Definitions: [PIN THE AMBIGUITY — e.g. "organization = org_id in
   orgs table, excluding internal/test orgs (is_internal = true);
   July = July 1-31 UTC"]
   
   Schema context:
   [PASTE SCHEMA BLOCK]
   
   Rules: before the SQL, restate the question as you understood it,
   in one sentence. Comment every JOIN and WHERE with why it's there.
   State any assumption you made where my definitions were silent.
   

3. 3.

   Inspect before you run. Good output looks like:

   Copy

   Understood question: count of distinct non-internal orgs with ≥1
   expense_bulk_approved event, 2026-07-01 to 2026-07-31 UTC.
   
   SELECT COUNT(DISTINCT e.org_id)
   FROM events_prod e
   JOIN orgs o ON o.org_id = e.org_id      -- org attributes
   WHERE e.event_name = 'expense_bulk_approved'
     AND o.is_internal = FALSE              -- excl. test orgs
     AND e.event_ts >= '2026-07-01'
     AND e.event_ts < '2026-08-01';         -- July UTC, exclusive end
   

   Read three things every time: the restated question (is it your question?), the date boundaries (inclusive/exclusive, timezone), and every JOIN (does it fan out rows before a COUNT?). Assumptions the model stated are decisions you now make explicitly.

4. 4.

   Run the known-good cross-check — the step that earns trust. Before quoting the result anywhere, reconcile it against a number you already trust. Ask the model to write a second query reproducing a certified dashboard figure (say, "total active orgs in July") using the same tables and filters. If the reproduction matches the dashboard, your filters and joins agree with the certified definition and your novel number inherits that trust. If it doesn't match, stop — nothing from this session travels until you know why.

5. 5.

   Sanity-check the magnitude yourself. Is the answer physically plausible — more bulk-approving orgs than total orgs? A number 40x last month's? Cheap arithmetic against known totals catches the fan-out joins the eye missed.

6. 6.

   Log what you learned. Every trap you catch ("events_prod has dupes on retry — dedupe on event_id") goes into the schema-context block. Your context file compounds; within a month it's the best onboarding doc the data team never wrote.

Make it yours

• Snowflake Cortex Analyst / BigQuery Gemini in-warehouse: the native routes read real metadata and never move data out — best default where available; keep your traps list in the semantic model or as a prefixed comment, and keep the inspection habit identical.
• Claude Project as your query workbench: schema block, traps list, and certified-definition notes as project knowledge; every question inherits them, and follow-ups ("same but weekly, excluding orgs created in July") stay consistent.
• Team scale: share one schema-context file and one cross-check registry ("these five dashboard numbers are certified — reconcile against them") in the team wiki. Self-serve spreads safely only when the guardrails travel with it.

Quality check

• The restated question matches your questionmost wrong answers are right answers to a different question; this line is where you catch it.
• Date logic verified by handtimezone, inclusive/exclusive bounds, and "last 30 days" vs "last calendar month" checked against the definition you pinned.
• Joins can't fan outevery JOIN checked for one-to-many blowup ahead of a COUNT or SUM; it's the classic silent doubler.
• The cross-check reconcileda known-good number reproduced within tolerance before the new number was used; no reconciliation, no citation.
• The number survives magnitude sensecompared against a known total and last period's value before it entered any narrative.