Text-to-SQL in Production: Evaluation Harnesses, Guardrails & Semantic-Layer Grounding

Shipping text-to-sql in production is the single biggest gap between a great demo and a livable analytics product in 2026. Any senior data platform engineer can wire a GPT-class model to a Postgres or Snowflake connection and produce a "wow" reel — natural-language question in, table out, chart rendered. What actually breaks the moment a real user hits the endpoint is not the model quality: it is the plumbing around the model — the evaluation harness that would have caught the join drift on prompt #47, the guardrails that would have blocked the twelve-way Cartesian join, the semantic-layer contract that would have prevented "revenue" from being computed three different ways on three different dashboards, and the closed feedback loop that would have promoted last week's escalated ticket into this week's gold prompt.

This guide is the senior-platform-engineer walkthrough you wished existed the first time your VP of data asked "so when can we let customer success ask the warehouse in plain English?" It walks through the four failure classes that kill text-to-sql in production deployments (schema hallucination, join drift, metric mis-definition, unsafe execution), the eval harness that scores every prompt against a gold set using exact-match, execution accuracy, and semantic-diff metrics, the four-lane guardrail stack (schema linking, SQLGlot parse, warehouse dry-run cost, safety allow-list) every LLM-generated query must pass through, the semantic-layer grounding contract (dbt Semantic Layer, LookML, Cube) that pins metrics to a single definition, and the production loop that turns every escalated failure into next week's gold prompt. Each H2 pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why the design works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the SQL-generation practice library →, and sharpen the safety axis with the data-validation practice library →.

---

On this page

• Why text-to-SQL fails in production — the four failure classes
• The eval harness — execution accuracy, exact-match, semantic diff
• Guardrails — schema linking, query validation, safety rails
• Semantic-layer grounding — dbt Semantic Layer, LookML, Cube
• The production loop — logs → eval → retrain → ship
• Cheat sheet — text-to-SQL production recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why text-to-SQL fails in production — the four failure classes

Four failure classes, four independent mitigations — and the demo does not touch any of them

The one-sentence invariant: text-to-sql in production fails along four independent axes — the model hallucinates schema (invents tables or columns), drifts on joins (picks the wrong join key or the wrong join type), mis-defines metrics (computes revenue with the wrong filter or grain), and executes unsafely (runs a Cartesian join, scans a petabyte, drops a table) — and no single tool covers all four, so every serious deployment must layer independent mitigations on top of the model rather than trust the model to self-correct. The demos everyone posts to LinkedIn use tiny toy schemas, memoised prompts, and no execution guard; the moment your real 400-table warehouse lands in the context window, all four failure classes fire at once.

The four failure classes interviewers actually name.

• Schema hallucination. The model invents a customers.lifetime_value column that does not exist, or references orders.order_date when the real column is order_created_at. Root cause: the prompt does not fit the schema, or the schema-linking layer is missing. Mitigation: retrieve only relevant tables into the prompt via embeddings, validate every referenced identifier post-generation with SQLGlot, and reject anything that references an unknown identifier before it touches the warehouse.
• Join drift. The model picks LEFT JOIN where the semantic answer requires INNER JOIN, or joins on user_id = customer_id in a schema where the actual bridge is users.id = customers.user_id. Root cause: the model has no join-graph awareness beyond the raw DDL. Mitigation: encode explicit join_relationships in the schema documentation, use a semantic layer that pre-defines joins once, and grade every candidate SQL against the join graph before execution.
• Metric mis-definition. The model computes "monthly active users" as COUNT(DISTINCT user_id) when the enterprise definition excludes bots and internal test accounts, or computes "revenue" without applying the refund adjustment. Root cause: metric definitions live in tribal knowledge, not in a queryable semantic layer. Mitigation: ground every question on a canonical metric via dbt Semantic Layer, LookML, or Cube — never let the model derive a metric from raw tables when a defined metric exists.
• Unsafe execution. The model emits DELETE FROM orders because the user asked "how do I get rid of pending orders?", or produces a 12-way join that scans 500 TB and blows the daily warehouse budget in one query. Root cause: no execution guardrails. Mitigation: read-only connection role, mandatory LIMIT, dry-run cost estimation before execution, timeout enforced by warehouse-side session settings.

The four axes senior engineers actually optimise for.

• Correctness. Does the returned result set match what a senior analyst would have written by hand? Measured by execution accuracy against a gold set — the primary metric for any production deployment. Anything below 80 percent execution accuracy on your private gold set means you are shipping vibes, not answers.
• Safety. Can any user, no matter how adversarial, cause data loss, schema mutation, or a runaway warehouse bill? Measured by 0 CVE-class incidents and by the fraction of generated SQL that survives every guardrail lane. Safety is a boolean: either every unsafe SQL is rejected, or the system fails.
• Cost. How many dollars per resolved question does the system incur — LLM tokens plus warehouse compute plus human review time? Measured in cents per accepted answer. A production text-to-SQL system that costs $5 per answer is a demo; a production system at $0.05 per answer is a product.
• Latency. How long from user click to result-set render? Measured p50 / p95 wall clock. Above 30 seconds p95 and users churn; below 5 seconds p95 and the product feels magical. Latency is dominated by the LLM call plus the warehouse execution — the guardrail stack must add milliseconds, not seconds.

The 2026 reality — the harness is the product.

• Model quality is not the bottleneck. Frontier models (GPT-class, Claude-class, Gemini-class, Llama-class) all reach 70-85 percent execution accuracy on Spider and BIRD out of the box. The differentiation is entirely in the harness — the schema retrieval, the guardrails, the semantic layer, the eval loop. Two teams using the same model can be 20 percentage points apart on a private gold set because one team built the plumbing and the other did not.
• Public benchmarks lie about your domain. Spider is a cross-domain benchmark with tiny schemas (mostly under 20 tables). BIRD is closer to reality (larger schemas, dirty values) but still not your schema. Every serious deployment maintains a private gold set of 200-2000 domain-specific (question, gold_sql, expected_result) triples that dominates the public benchmarks for release gating.
• Semantic layer became table stakes. In 2024 semantic layers were "a nice-to-have for consistency"; in 2026 they are the only way to defend metric correctness at scale. dbt Semantic Layer, Cube, and LookML all publish MCP or REST endpoints an LLM can query, and every mature deployment grounds on metrics rather than raw DDL.
• The eval harness runs in CI. Every prompt template change, every model version bump, every schema evolution triggers a full offline harness run before deploy. The team that ships text-to-SQL without an eval CI is the team that ships a regression on Tuesday and gets paged on Wednesday.

What interviewers listen for.

• Do you name all four failure classes without prompting? — senior signal.
• Do you say "execution accuracy is the primary metric" rather than "we measure BLEU" or "we use LLM-as-judge"? — required answer.
• Do you push back on "just use GPT-4" with the guardrail question — "who owns the read-only role, the dry-run budget, and the schema-link retriever?" — senior signal.
• Do you name semantic layer grounding as the answer to metric drift, not as "an alternative to RAG"? — senior signal.
• Do you describe production text-to-SQL as "a harness around a model" rather than as "an LLM app"? — required answer.

Worked example — the four-class failure taxonomy on a real prompt

Detailed explanation. The single most useful artifact for a text-to-SQL production interview is a worked-through failure taxonomy on a real prompt. Every senior conversation converges on this within the first ten minutes; having it in your head is what separates a fluent answer from a stumbling one. Walk through a canonical failure using a hypothetical retail warehouse where a user asks a plausible business question and the naive model burns on every axis.

• Warehouse. Snowflake analytics database with 380 tables including analytics.raw.orders, analytics.raw.order_items, analytics.raw.customers, analytics.dim.customers, analytics.fct.revenue_daily.
• User question. "What was our top-5 US customer revenue last quarter, excluding refunds?"
• Naive model output. A CTE that joins orders to customers on orders.cust_id = customers.customer_id, sums orders.amount, groups by customer, orders and limits to 5.
• What breaks. The column cust_id does not exist (it is customer_id on orders), the refund exclusion is missing entirely, the "US" filter is missing entirely, and the revenue computation ignores the refund_amount column that the canonical fct.revenue_daily metric applies.

Question. Enumerate the four failure classes triggered by this one prompt and specify the mitigation for each.

Input.

Failure class	Symptom in the naive SQL	Mitigation
Schema hallucination	orders.cust_id does not exist	schema linker + SQLGlot identifier validation
Join drift	joins on invented column, wrong grain	join graph in schema doc + semantic-layer joins
Metric mis-definition	uses SUM(amount) not revenue - refund	ground on fct.revenue_daily metric
Unsafe execution	no LIMIT, no cost dry-run	mandatory LIMIT + Snowflake EXPLAIN cost gate

Code.

# Illustrative — classify a failure across the four axes
from dataclasses import dataclass

@dataclass
class FailureReport:
    hallucinated_identifiers: list[str]
    join_drift: list[str]
    metric_mismatches: list[str]
    unsafe_ops: list[str]

    def is_ship_ready(self) -> bool:
        return not any([self.hallucinated_identifiers,
                        self.join_drift,
                        self.metric_mismatches,
                        self.unsafe_ops])


def classify(candidate_sql: str, schema, join_graph, metric_catalog) -> FailureReport:
    """Return a per-axis failure report for one candidate SQL."""
    return FailureReport(
        hallucinated_identifiers = schema.unknown_identifiers(candidate_sql),
        join_drift               = join_graph.violations(candidate_sql),
        metric_mismatches        = metric_catalog.mismatches(candidate_sql),
        unsafe_ops               = ["missing_limit"] if "LIMIT" not in candidate_sql.upper() else [],
    )

Step-by-step explanation.

1. The failure report is a per-axis vector, not a single boolean. A candidate SQL that only fails on one axis is salvageable (rewrite the join, add the LIMIT); a candidate that fails on three axes is a total loss and the model must resample.
2. schema.unknown_identifiers(sql) walks the SQL AST with SQLGlot, extracts every table and column reference, and looks each one up in the live warehouse catalog. Any reference not in the catalog is a hallucination. This single check catches the biggest failure class by volume.
3. join_graph.violations(sql) compares every join predicate in the SQL against the canonical join graph maintained in schema docs (or the semantic layer). A join on orders.cust_id = customers.customer_id is flagged because the canonical bridge in your schema is orders.customer_id = customers.customer_id.
4. metric_catalog.mismatches(sql) recognises when the user's intent maps to a defined metric (fct.revenue_daily, dim.customers.is_us) but the SQL derives the metric from raw tables. This is the metric-mis-definition axis; grounding on the semantic layer eliminates it at source.
5. unsafe_ops starts as a simple LIMIT presence check but expands into the full guardrail lane (dry-run cost, safety allow-list, timeout). Even the shortest guardrail — "must contain LIMIT" — catches the "twelve-way join with no bound" catastrophe.

Output.

Axis	Naive SQL score	Ship gate
Hallucinated identifiers	1 (cust_id)	must be 0
Join drift	1 (wrong bridge)	must be 0
Metric mismatches	1 (SUM(amount) vs fct.revenue_daily)	must be 0
Unsafe ops	1 (no LIMIT)	must be 0

Rule of thumb. Every candidate SQL emitted by the model is a vector on the four failure axes, not a boolean. Ship only when the vector is all zeros. Every non-zero component points to a specific plumbing layer to fix — schema link, join graph, semantic layer, or execution guardrail.

Worked example — what interviewers actually probe in a senior text-to-SQL round

Detailed explanation. The senior text-to-SQL interview has a predictable structure. The interviewer opens with an ambiguous question ("how would you build a natural-language interface to our warehouse?"), then progressively narrows to test whether you know the four axes. The candidates who name the harness in sentence one score highest; the candidates who describe "an LLM app" score lowest. Walk through the interview grading rubric.

• Ambiguous opener. "How would you build a text-to-SQL feature over our warehouse?" — invites you to name the harness.
• Follow-up 1. "How would you measure quality?" — probes eval-harness axis.
• Follow-up 2. "How do you keep a user from dropping a table?" — probes safety axis.
• Follow-up 3. "How do you keep 'revenue' from being computed three different ways?" — probes semantic-layer axis.
• Follow-up 4. "How would you improve the system after ship?" — probes the production loop axis.

Question. Draft a 5-minute senior text-to-SQL answer that covers all four axes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Framing	"we'd wire GPT-4 to Snowflake"	"the model is a component; the harness is the product"
Eval	"we'd look at some outputs"	"execution accuracy on a 500-prompt private gold set, run in CI on every prompt change"
Safety	"the read-only user handles it"	"four-lane guardrail: schema link, SQLGlot parse, dry-run cost, safety allow-list"
Semantic	"we'd add more docs"	"ground on dbt Semantic Layer metrics; never let the model derive revenue from raw tables"
Loop	"we'd retrain the model"	"capture logs, human-label failures, promote to gold, canary ship"

Code.

Senior text-to-SQL answer template (5 minutes)
==============================================

Minute 1 — name the harness up front
  "I'd frame this as a harness around a frontier model, not an LLM
   app. The model reaches 75% execution accuracy out of the box;
   the harness gets me to 92%."

Minute 2 — eval harness
  "The primary metric is execution accuracy against a private gold
   set of 500 (question, gold_sql, expected_result) triples that
   dominates Spider and BIRD for release gating. Exact-match is a
   lower bound; semantic-diff on the result set is the truth."

Minute 3 — guardrails
  "Every candidate SQL passes four lanes before it touches the
   warehouse: schema-link retrieval to prune the prompt, SQLGlot
   parse + identifier validation, Snowflake EXPLAIN cost dry-run,
   and a safety allow-list that rejects any DDL or DML. The
   connection role is read-only with a bytes-scanned quota."

Minute 4 — semantic-layer grounding
  "Metrics like revenue, MAU, churn live in dbt Semantic Layer.
   The retriever exposes metrics as first-class candidates; the
   prompt template says 'prefer defined metrics over raw tables.'
   The result: one revenue definition, zero drift across dashboards."

Minute 5 — production loop + failure semantics
  "Every request logs (prompt, retrieved_context, generated_sql,
   guardrail_verdict, executed_result, user_feedback). Weekly
   triage promotes failures to gold prompts. Prompt template
   changes and model bumps run through the eval harness in CI
   with a 2-point regression gate before deploy."

Step-by-step explanation.

1. Minute 1 is the crucial framing. Naming the harness immediately — "harness around a frontier model" — signals you are a decision-maker, not a task-runner. Weak candidates dive into tools ("we'd use LangChain and…") before naming the architecture.
2. Minute 2 addresses the eval axis before the interviewer asks. This preempts the common trap where you commit to shipping and then admit you have no measurement. Naming execution accuracy explicitly (not BLEU, not "LLM-as-judge alone") is senior signal.
3. Minute 3 is the safety axis. Every guardrail lane must be named; the read-only role alone is not enough because a runaway SELECT can still bankrupt the warehouse. The four-lane answer is the memorable framework.
4. Minute 4 is the semantic-layer axis. The revenue example is nearly universal: every data team has a metric-drift war story, and grounding on dbt Semantic Layer is the standard fix in 2026.
5. Minute 5 covers the loop and failure semantics — the reliability axis. Naming CI-gated regression testing on every prompt change is what distinguishes "we shipped it" from "we operate it."

Output.

Grading criterion	Weak score	Senior score
Frames harness first	rare	mandatory
Names execution accuracy	rare	required
Names all four guardrail lanes	rare	senior signal
Names semantic-layer grounding	occasional	required
Names CI eval gate	rare	senior signal

Rule of thumb. The senior text-to-SQL answer is a 5-minute monologue that covers all four axes without waiting for the follow-ups. Rehearse it once; deploy it every time. The harness comes first; the model choice comes last.

Worked example — the "will this feature survive" decision tree

Detailed explanation. Given a new text-to-SQL feature request, the senior architect runs a 4-question decision tree in their head. Codifying the tree makes the interview answer reproducible: any interviewer can hand you a scenario and you can walk the tree out loud. Walk through the tree with three canonical scenarios: internal analyst assistant, customer-facing self-serve, and executive dashboard "ask a question" bar.

• Q1. Is the user population trusted (internal analysts) or untrusted (external customers, executives)? Untrusted → guardrails must be paranoid.
• Q2. Is the warehouse cost centralised (one team pays) or fanned-out (per-tenant billing)? Fanned-out → per-query cost cap mandatory.
• Q3. Do metrics have canonical definitions (semantic layer exists) or tribal ones (docs in Notion)? Tribal → semantic-layer investment must precede feature ship.
• Q4. Is there a private gold set? No → build one before shipping; do not use Spider or BIRD as ship gates.

Question. Walk the decision tree for the three scenarios and record the go / no-go verdict.

Input.

Scenario	Q1 (trust)	Q2 (cost)	Q3 (semantic layer)	Q4 (gold set)
Internal analyst assistant	trusted	centralised	tribal	no
Customer-facing self-serve	untrusted	fanned-out	canonical	no
Executive "ask a question" bar	trusted	centralised	canonical	yes

Code.

# Decision-tree helper (illustrative)
from dataclasses import dataclass

@dataclass
class ShipVerdict:
    ship_now: bool
    blocker: str | None
    prerequisites: list[str]


def can_we_ship(
    trusted_users: bool,
    centralised_cost: bool,
    has_semantic_layer: bool,
    has_gold_set: bool,
) -> ShipVerdict:
    prereqs: list[str] = []

    if not has_gold_set:
        prereqs.append("build 200-prompt private gold set")

    if not has_semantic_layer:
        prereqs.append("stand up dbt Semantic Layer for top-20 metrics")

    if not centralised_cost:
        prereqs.append("per-query bytes-scanned cap + tenant budget")

    if not trusted_users:
        prereqs.append("paranoid guardrails: allow-list SELECT only, mask PII columns")

    if not prereqs:
        return ShipVerdict(ship_now=True, blocker=None, prerequisites=[])

    return ShipVerdict(
        ship_now=False,
        blocker="prerequisites not met",
        prerequisites=prereqs,
    )


# Walk the three scenarios
print(can_we_ship(True,  True,  False, False))
print(can_we_ship(False, False, True,  False))
print(can_we_ship(True,  True,  True,  True))

Step-by-step explanation.

1. Scenario 1 — internal analyst assistant. Trusted users, centralised cost, no semantic layer, no gold set. The verdict is not shippable yet: build the gold set (2 weeks), stand up dbt Semantic Layer for the top 20 metrics (4 weeks), then ship. Skipping either turns "helpful assistant" into "confidently wrong assistant."
2. Scenario 2 — customer-facing self-serve. Untrusted, fanned-out cost, canonical metrics, no gold set. Verdict is not shippable yet: build gold set (essential for customer-facing), add per-tenant cost caps (essential; one bad query cannot bankrupt a tenant), then layer paranoid guardrails. Semantic layer is already in place.
3. Scenario 3 — executive dashboard "ask a question" bar. Trusted users, centralised cost, canonical metrics, gold set exists. Verdict is shippable now — all four prerequisites are met. Ship a canary at 10 percent traffic, watch the eval-harness numbers weekly.
4. The parallel branches (guardrail paranoia, cost cap, semantic layer, gold set) are independent — you can ship a system with only some of them, but the failure surface grows with each missing prerequisite. Making the prerequisites explicit turns "should we ship?" into a checklist question.
5. If none of Q1-Q4 pass, the correct answer is "we are not ready; here is the four-week roadmap to become ready." Refuse to ship text-to-SQL without the prerequisites; the failures are subtle and expensive.

Output.

Scenario	Ship now?	Prerequisites
Internal analyst assistant	no	gold set + semantic layer
Customer-facing self-serve	no	gold set + cost cap + paranoid guardrails
Executive "ask a question" bar	yes	already met

Rule of thumb. The four-question decision tree is a whiteboard-friendly answer. Practice walking it end-to-end so an interviewer can hand you any scenario and get a go / no-go verdict in under 60 seconds — plus the concrete prerequisite list to unblock the no.

Senior interview question on text-to-SQL production-readiness

A senior interviewer often opens with: "You inherit a Slack bot that lets internal analysts ask questions in English and get warehouse results back. It works in demos but produces wrong answers 30 percent of the time and last month it cost $18,000 in Snowflake compute for one botched query. Walk me through the production-readiness plan you would put in front of leadership, the four workstreams you would fund, and the KPIs you would commit to."

Solution Using a four-workstream production hardening plan with harness-first, guardrails-second, semantic-layer-third, loop-fourth

# production_readiness_plan.py — the plan the platform lead ships to leadership

WORKSTREAMS = [
    {
        "name":     "eval-harness",
        "owner":    "data-platform",
        "weeks":    4,
        "deliver":  ["500-prompt private gold set",
                     "exact-match + execution-accuracy + semantic-diff scorer",
                     "CI job that runs on every prompt template PR"],
        "kpi":      "execution accuracy >= 85% on gold set, tracked weekly",
    },
    {
        "name":     "guardrails",
        "owner":    "data-platform + security",
        "weeks":    3,
        "deliver":  ["schema-link retriever (top-20 tables per prompt)",
                     "SQLGlot parse + identifier validator",
                     "Snowflake EXPLAIN cost dry-run with 5 GB scanned cap",
                     "read-only role with allow-list of SELECT + WITH only"],
        "kpi":      "0 unsafe SQL executed against warehouse; p99 cost <= $0.50 per query",
    },
    {
        "name":     "semantic-layer",
        "owner":    "analytics-eng",
        "weeks":    6,
        "deliver":  ["top-20 metrics defined in dbt Semantic Layer",
                     "metric retriever exposed as first-class candidate",
                     "prompt template updated to prefer metrics over raw tables"],
        "kpi":      ">= 60% of user questions grounded on a defined metric",
    },
    {
        "name":     "production-loop",
        "owner":    "data-platform",
        "weeks":    2,
        "deliver":  ["request log (prompt, retrieved, sql, verdict, result, feedback)",
                     "weekly triage rotation promotes failures to gold prompts",
                     "canary release: new prompt template goes to 10% traffic for 1 week"],
        "kpi":      "gold set grows by 20 prompts per week; regressions caught pre-deploy",
    },
]

for w in WORKSTREAMS:
    print(f"[{w['name']}] {w['weeks']} weeks, owner={w['owner']}")
    for d in w["deliver"]:
        print(f"  - {d}")
    print(f"  KPI: {w['kpi']}")

-- Snowflake — set up the read-only role + bytes-scanned cap for the bot user
CREATE ROLE textsql_bot;
GRANT USAGE ON WAREHOUSE textsql_wh TO ROLE textsql_bot;
GRANT USAGE ON DATABASE analytics    TO ROLE textsql_bot;
GRANT USAGE ON SCHEMA analytics.dim  TO ROLE textsql_bot;
GRANT USAGE ON SCHEMA analytics.fct  TO ROLE textsql_bot;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.dim TO ROLE textsql_bot;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.fct TO ROLE textsql_bot;

-- Per-session quota: kill queries that scan more than 5 GB
ALTER USER textsql_bot_svc SET
    STATEMENT_TIMEOUT_IN_SECONDS = 60,
    STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 30,
    QUERY_TAG = 'textsql-bot';

-- Resource monitor: hard-stop at $500/day on the bot warehouse
CREATE RESOURCE MONITOR textsql_daily
    WITH CREDIT_QUOTA = 200
    FREQUENCY = DAILY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 80 PERCENT DO NOTIFY
        ON 100 PERCENT DO SUSPEND
        ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE textsql_wh SET RESOURCE_MONITOR = textsql_daily;

Step-by-step trace.

Step	Before (broken Slack bot)	After (production-hardened)
Execution accuracy	70% (self-reported)	85% (measured on 500-prompt gold set)
Unsafe SQL executed	occasional (18k$ incident)	0 (four-lane guardrail)
Metric drift	rampant	contained (semantic-layer grounding)
Cost visibility	reactive (postmortem)	proactive (resource monitor + dry-run cap)
Feedback loop	none	logs → weekly triage → gold promotion
Deploy safety	ad-hoc	CI eval gate; 2-point regression blocker
Ship cadence	broken	weekly prompt tunes; monthly model bumps

After the four workstreams land, the Slack bot's error rate drops from 30 percent to under 15 percent (execution-accuracy 85 percent), and no single query can scan more than 5 GB or cost more than $0.50. The eval harness runs on every prompt-template PR; a regression larger than 2 points blocks the deploy. Weekly triage promotes real user failures into the gold set, which keeps the ship gate tight to what users actually ask.

Output:

Metric	Before	After
Wrong answers	~30%	~15%
Cost blowout risk	one incident/quarter	0 (resource monitor)
Metric drift complaints	weekly	eliminated
Deploy-time regressions	monthly	caught pre-deploy
Time to root-cause a bad answer	days	minutes (log + eval)
Team confidence to expand scope	low	high

Why this works — concept by concept:

• Harness-first framing — the model is one component in a system of many. Investing in eval, guardrails, semantic layer, and loop before investing in a better model buys 15-20 execution-accuracy points that no model bump alone would deliver.
• Execution accuracy on a private gold set — the only measurement that predicts user-perceived quality. Public benchmarks (Spider, BIRD) are a floor, not a ceiling; the private gold set is what your team actually ships against.
• Four-lane guardrails — every LLM-generated SQL passes schema link, parse validation, dry-run cost, and safety allow-list. Missing any lane leaves the failure class it protects wide open. The lanes are independent, so failures on one lane do not propagate.
• Semantic-layer grounding — metrics defined once, referenced everywhere. Removing the model's freedom to redefine revenue collapses an entire failure class into "the metric catalog is the source of truth."
• Production loop — capture, label, evaluate, ship. Every failure becomes tomorrow's gold prompt; every model or prompt change is gated by the harness before deploy. The system compounds over weeks; a static prompt-and-model system decays.
• Cost — 15 engineer-weeks across four workstreams; ongoing operating cost of one engineer at 30 percent time for triage plus warehouse compute for the CI eval. The eliminated cost is the $18k Snowflake incident, the metric-drift war rooms, and the confidence loss with leadership when the bot is wrong. Net O(1) per query with bounded worst case, versus O(bug) per bad answer.

SQL
Topic — sql
SQL problems on production text-to-SQL queries

Practice →

SQL Generation Topic — sql-generation SQL-generation and NL2SQL problems

Practice →

---

2. The eval harness — execution accuracy, exact-match, semantic diff

The three metrics — exact-match is a lower bound, semantic-diff is convenient, execution accuracy is the truth

The mental model in one line: the text-to-sql evaluation harness scores every candidate SQL against a private gold set using three complementary metrics — exact-match (does the SQL string match the gold string after canonicalisation?), execution accuracy (does the result set match after running both against the same data snapshot?), and semantic-diff (does the query plan or output shape match even when the SQL text differs?) — and every serious deployment gates release on execution accuracy because it is the only metric that measures what the user actually experiences. Exact-match and semantic-diff are useful diagnostics; execution accuracy is the ship gate.

The three metrics — what each measures and where each fails.

• Exact-match. Canonicalise the candidate SQL (lowercase, whitespace-normalise, alias-normalise) and compare to the canonicalised gold SQL. Cheap, deterministic, no warehouse round-trip. Fails when two syntactically different queries produce the same result — SELECT a, b FROM t vs SELECT b, a FROM t are exact-match different but semantically identical, and a JOIN reordered by the model is exact-match different but plan-identical. Exact-match is a lower bound: if it passes, the candidate is correct; if it fails, the candidate might still be correct.
• Execution accuracy. Run the candidate SQL and the gold SQL against a frozen snapshot of the warehouse; compare the result sets after canonicalisation (sort rows, sort columns, cast types). The single most-cited metric in every text-to-SQL paper and every production deployment. The failure mode is expense: every eval-set run is N warehouse queries against real data. Snapshot the data into a dev warehouse to bound cost.
• Semantic-diff. Compare the query plans (using EXPLAIN) or the projected column set + join set. Catches "the SQL text differs but the plan is the same" cases that exact-match misses, without the warehouse cost of execution accuracy. Useful in the middle of a fast dev loop; not sufficient alone for release gating because two different plans can still produce identical result sets.

The gold-set contract — the artifact your release depends on.

• What a gold row is. A tuple (nl_question, gold_sql, expected_result, tags) where nl_question is the user's natural-language input, gold_sql is the reference answer written by a senior analyst, expected_result is either the frozen result set or a hash thereof, and tags classify the prompt (e.g. aggregation, window, join_3way, metric:revenue).
• How to build it. Start with 200 prompts covering your top-10 query patterns across your top-10 tables. Every production incident becomes a new gold row. Every escalated user feedback becomes a new gold row. Target 500 prompts within the first quarter; 2000 within the first year.
• Where to store it. Version-controlled repo (git), one JSON or JSONL row per prompt. The gold set is code — reviewed, PR-approved, blamed when regressions ship. Storing it in Notion or a Google Doc guarantees it decays.
• How to protect it. Never let the gold set leak into the training or fine-tuning data. Rotate 20 percent quarterly to prevent overfit. Keep a separate "adversarial" partition that never enters any prompt template.

Spider, BIRD, WikiSQL — the public benchmarks and where they fail your domain.

• Spider. 200 databases, 10k+ prompts, cross-domain, but tiny schemas (mostly under 20 tables) and clean data. Excellent for model-development sanity; useless as a production gate for your 400-table Snowflake warehouse.
• BIRD. Closer to reality — larger schemas (up to 100 tables), dirty values, external-knowledge questions. The 2024 model gap between "trained on BIRD" and "actually works on your warehouse" is roughly 15 percentage points of execution accuracy. Better than Spider; still not enough.
• WikiSQL. Older, single-table, dominated by simple SELECT ... WHERE. Historical value; skip for production.
• The pattern. Use Spider and BIRD as sanity checks during model selection; use your private gold set as the release gate. Anyone who ships on Spider score alone will be surprised in production.

Common interview probes on eval harness.

• "What is your primary metric?" — required answer: execution accuracy on a private gold set.
• "How is that different from exact-match?" — exact-match ignores semantically equivalent rewrites; execution accuracy catches them.
• "How do you run it without breaking your warehouse cost?" — dev warehouse snapshot, per-query cost cap, sampled data.
• "How do you keep the gold set fresh?" — every escalated failure becomes a gold row; 20 percent rotation quarterly.

Worked example — build an eval harness from scratch

Detailed explanation. The canonical text-to-SQL harness is a Python module with three functions — canonicalise, execute, score — and a runner that iterates over a JSONL gold file, invokes the candidate model, scores each row, and emits a scorecard. Build the whole thing from scratch and run it against a 5-prompt toy gold set.

• Gold file. gold_set.jsonl — one row per prompt.
• Canonicalisation. Sort rows, sort columns, cast numeric types.
• Runner. For each row, call the model, execute, compare, record.
• Scorecard. Aggregate per-tag; per-metric; per-difficulty.

Question. Implement the eval harness end-to-end and produce a scorecard for a 5-prompt toy gold set.

Input.

File	Purpose
gold_set.jsonl	5 (question, gold_sql, expected_result) triples
harness.py	canonicalise, execute, score, run
scorecard.md	per-tag execution accuracy summary

Code.

# harness.py — build the eval harness from scratch
import json
import hashlib
import time
from dataclasses import dataclass, asdict
from typing import Callable

import sqlglot   # for AST-based canonicalisation
import snowflake.connector as sf


@dataclass
class GoldRow:
    question:        str
    gold_sql:        str
    expected_hash:   str
    tags:            list[str]


@dataclass
class ScoreRow:
    question:        str
    predicted_sql:   str
    exact_match:     bool
    exec_accuracy:   bool
    latency_ms:      float
    error:           str | None
    tags:            list[str]


def canonicalise_sql(sql: str) -> str:
    """Alias-normalise, whitespace-normalise, lowercase identifiers."""
    tree = sqlglot.parse_one(sql, read="snowflake")
    return tree.sql(dialect="snowflake", normalize=True, pretty=False).lower().strip()


def canonicalise_result(rows: list[tuple]) -> str:
    """Sort rows, sort columns, hash for cheap equality."""
    normalised = sorted(tuple(sorted((str(v) for v in r), key=str)) for r in rows)
    return hashlib.sha256(json.dumps(normalised).encode()).hexdigest()


def execute_sql(conn, sql: str, timeout_s: int = 30) -> list[tuple]:
    with conn.cursor() as cur:
        cur.execute(f"ALTER SESSION SET STATEMENT_TIMEOUT_IN_SECONDS = {timeout_s}")
        cur.execute(sql)
        return cur.fetchall()


def score_row(conn, row: GoldRow, model_fn: Callable[[str], str]) -> ScoreRow:
    t0 = time.perf_counter()
    predicted_sql = model_fn(row.question)
    t_gen = (time.perf_counter() - t0) * 1000

    exact = canonicalise_sql(predicted_sql) == canonicalise_sql(row.gold_sql)
    exec_ok, err = False, None
    try:
        got = execute_sql(conn, predicted_sql)
        got_hash = canonicalise_result(got)
        exec_ok = got_hash == row.expected_hash
    except Exception as e:
        err = str(e)

    return ScoreRow(
        question      = row.question,
        predicted_sql = predicted_sql,
        exact_match   = exact,
        exec_accuracy = exec_ok,
        latency_ms    = t_gen,
        error         = err,
        tags          = row.tags,
    )


def run(gold_path: str, model_fn: Callable[[str], str]) -> list[ScoreRow]:
    conn = sf.connect(user="eval_user", account="acme.snowflakecomputing.com",
                      warehouse="eval_wh", database="analytics_dev")
    results: list[ScoreRow] = []
    with open(gold_path) as f:
        for line in f:
            row = GoldRow(**json.loads(line))
            results.append(score_row(conn, row, model_fn))
    return results


def print_scorecard(rows: list[ScoreRow]) -> None:
    total    = len(rows)
    exact_n  = sum(1 for r in rows if r.exact_match)
    exec_n   = sum(1 for r in rows if r.exec_accuracy)
    err_n    = sum(1 for r in rows if r.error)
    print(f"total={total} exact={exact_n}/{total} exec={exec_n}/{total} errors={err_n}")

    per_tag: dict[str, list[ScoreRow]] = {}
    for r in rows:
        for t in r.tags:
            per_tag.setdefault(t, []).append(r)
    for tag, rs in sorted(per_tag.items()):
        e = sum(1 for r in rs if r.exec_accuracy) / len(rs)
        print(f"  tag={tag:<20} n={len(rs):>3} exec_acc={e:.0%}")

# gold_set.jsonl — 5 toy prompts
{"question": "top 5 US customers by revenue last quarter",
 "gold_sql": "SELECT customer_id, revenue FROM fct.revenue_by_customer WHERE country='US' AND quarter='2026Q2' ORDER BY revenue DESC LIMIT 5",
 "expected_hash": "d1a3...",
 "tags": ["aggregation", "metric:revenue", "difficulty:medium"]}
{"question": "monthly active users trend for the last 12 weeks",
 "gold_sql": "SELECT week, wau FROM fct.wau_weekly WHERE week >= dateadd(week,-12,current_date) ORDER BY week",
 "expected_hash": "f2b8...",
 "tags": ["window", "metric:wau", "difficulty:easy"]}
{"question": "which product SKUs are pending shipment for more than 3 days",
 "gold_sql": "SELECT sku, count(*) AS pending_orders FROM fct.orders WHERE status='pending' AND ordered_at < dateadd(day,-3,current_date) GROUP BY sku HAVING count(*) > 0 ORDER BY pending_orders DESC LIMIT 100",
 "expected_hash": "9a1e...",
 "tags": ["aggregation", "join_none", "difficulty:medium"]}
{"question": "top 10 pages by 7-day rolling unique visitors",
 "gold_sql": "SELECT page, avg_visits FROM (SELECT page, avg(visits) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_visits FROM fct.page_visits_daily) ORDER BY avg_visits DESC LIMIT 10",
 "expected_hash": "3f7c...",
 "tags": ["window", "difficulty:hard"]}
{"question": "sessions per user in the last 7 days for the enterprise plan",
 "gold_sql": "SELECT user_id, count(*) AS sessions FROM fct.sessions s JOIN dim.customers c ON c.user_id=s.user_id WHERE c.plan='enterprise' AND s.started_at >= dateadd(day,-7,current_date) GROUP BY user_id ORDER BY sessions DESC LIMIT 100",
 "expected_hash": "b6e0...",
 "tags": ["join_2way", "aggregation", "difficulty:medium"]}

Step-by-step explanation.

1. canonicalise_sql uses SQLGlot to parse the SQL into an AST, then serialises it back with normalize=True and lowercasing. This turns SELECT A, B FROM T and select a,b from t into the same canonical form. It does not handle join reordering or column reordering — those are exact-match limitations that execution accuracy will catch.
2. canonicalise_result sorts the rows (top-level) and the columns (per row, stringified) then SHA-256 hashes. Sorting is essential because SQL result-set ordering is not guaranteed unless ORDER BY is explicit; hashing is a cheap way to compare huge result sets without serialising into memory.
3. execute_sql sets a per-session STATEMENT_TIMEOUT_IN_SECONDS before executing. The timeout is the first line of defense — a candidate SQL that would run for 30 minutes gets killed at 30 seconds, so one bad candidate cannot burn the eval budget.
4. score_row calls the model once, measures generation latency, canonicalises both SQL strings for exact-match, then executes the candidate and hashes the result. Errors are captured and stored — they count as execution-accuracy failures but are reported separately so the harness distinguishes "wrong answer" from "syntax error."
5. print_scorecard aggregates by tag so the harness surfaces where the model is weak. A model that hits 90 percent on aggregation and 50 percent on window tells you exactly where to invest in schema linking and prompt-template tuning.

Output.

Metric	Value	Notes
Total prompts	5	toy gold set
Exact-match	2/5	40%
Execution accuracy	4/5	80%
Errors	0	no syntax failures
aggregation	3 prompts, 100% exec_acc	strong
window	2 prompts, 50% exec_acc	needs prompt tuning

Rule of thumb. For any text-to-SQL harness, exact-match is a lower bound on quality (never a ship gate); execution accuracy is the ship gate; tag every gold prompt so the scorecard surfaces per-pattern weaknesses. Snapshot the warehouse into a dev instance so each harness run costs pennies, not thousands.

Worked example — semantic diff via query plan hashing

Detailed explanation. Execution accuracy is expensive when your gold set grows to 2000 prompts and the warehouse is real. Semantic-diff via query-plan hashing gives you 80 percent of the signal at 5 percent of the cost. Compute EXPLAIN output for both candidate and gold, canonicalise, hash — if the plans match, the queries are semantically equivalent (with high confidence). Walk through the design.

• The mechanism. EXPLAIN returns the query plan; canonicalise it (strip volatile fields like plan IDs, cost estimates that change with statistics); hash for cheap comparison.
• The confidence. Plan-hash equality implies result-set equality for pure SELECT queries against a stable schema (subject to non-determinism from LIMIT without ORDER BY).
• When it fails. Two different plans can produce the same result (join reorder, index selection); plan hashing is a sufficient signal for equivalence but not necessary.

Question. Implement a semantic-diff scorer via query-plan hashing and integrate it into the harness as a cheap pre-filter.

Input.

Component	Purpose
EXPLAIN on candidate	get candidate plan
EXPLAIN on gold	get gold plan
plan canonicaliser	strip volatile fields
plan hasher	SHA-256 comparison
integration	run pre-filter before execution

Code.

# semantic_diff.py — plan-hash pre-filter
import hashlib
import re


def canonicalise_plan(plan_text: str) -> str:
    """Strip volatile fields: cost estimates, plan IDs, row counts."""
    # Snowflake EXPLAIN emits fields like [cost=1234.5], [rows=987], [id=42]
    stripped = re.sub(r"\[(cost|rows|id|est)=[^\]]+\]", "", plan_text)
    # Normalise whitespace
    stripped = re.sub(r"\s+", " ", stripped).strip()
    return stripped


def plan_hash(conn, sql: str) -> str:
    with conn.cursor() as cur:
        cur.execute(f"EXPLAIN USING TEXT {sql}")
        plan = "\n".join(r[0] for r in cur.fetchall())
    return hashlib.sha256(canonicalise_plan(plan).encode()).hexdigest()


def semantic_equal(conn, candidate_sql: str, gold_sql: str) -> bool:
    try:
        return plan_hash(conn, candidate_sql) == plan_hash(conn, gold_sql)
    except Exception:
        return False


# Integration — cheap pre-filter in the harness
def score_row_with_prefilter(conn, row, model_fn) -> "ScoreRow":
    predicted_sql = model_fn(row.question)

    # Cheap check: exact-match after canonicalisation
    if canonicalise_sql(predicted_sql) == canonicalise_sql(row.gold_sql):
        return ScoreRow(exact_match=True, exec_accuracy=True, ...)

    # Cheaper than execution: plan-hash equivalence
    if semantic_equal(conn, predicted_sql, row.gold_sql):
        return ScoreRow(exact_match=False, exec_accuracy=True, ...)

    # Only run execution when the cheap checks disagree
    got = execute_sql(conn, predicted_sql)
    got_hash = canonicalise_result(got)
    return ScoreRow(
        exact_match=False,
        exec_accuracy=(got_hash == row.expected_hash),
        ...
    )

Step-by-step explanation.

1. canonicalise_plan strips the volatile fields Snowflake includes in EXPLAIN output — cost estimates change with table statistics, plan IDs are per-invocation, row counts drift with data. Stripping them lets two invocations of the same plan hash identically.
2. plan_hash runs EXPLAIN USING TEXT, gathers the plan lines, canonicalises, and SHA-256 hashes. EXPLAIN is cheap on Snowflake — it costs a few compilation microseconds and no warehouse credits.
3. semantic_equal is the equivalence test. Same hash means (with high probability) same plan means same result set. Different hash means "we don't know" — fall through to execution.
4. The integrated harness scores in cascading tiers: cheapest first (exact-match), then medium (plan-hash), then expensive (execution). Most candidates hit an early tier; only the ambiguous ones burn execution budget.
5. The plan-hash tier reduces the eval-run cost dramatically. On a 2000-prompt gold set where 60 percent of candidates are plan-equivalent to gold, plan-hash pre-filtering drops execution count by 60 percent — thousands of dollars per full harness run.

Output.

Tier	Cost per prompt	Coverage	Ship-gate role
Exact-match	~microseconds	20-30% of prompts	fast pass
Plan-hash	~milliseconds	30-40% of additional prompts	cheap semantic pass
Execution accuracy	~seconds + $	30-40% remainder	ground truth
Combined harness	~cents per prompt at scale	100%	release gate

Rule of thumb. Layer the three metrics in a cost-ascending cascade: exact-match first (microseconds), plan-hash second (milliseconds), execution accuracy last (seconds and dollars). Route each prompt through the cheapest tier that resolves it. The harness cost stays flat as the gold set grows.

Worked example — dev-warehouse snapshotting to bound eval cost

Detailed explanation. A 2000-prompt gold set run against production warehouse data costs real money and puts contention on real user workloads. The senior fix is to snapshot the production warehouse into a dev instance, freeze the snapshot, and run the harness there. The frozen snapshot gives deterministic expected_result hashes; the isolated warehouse absorbs the eval load without touching production.

• The snapshot. Snowflake CLONE (zero-copy) or Databricks Delta Time Travel — pick a fixed instant, materialise a snapshot, harness reads only from it.
• The freeze. Version the snapshot label (eval_2026_07_30_v1) and never let the harness read from latest — the gold expected-hashes are pinned to the snapshot instant.
• The refresh. Rotate the snapshot monthly; rehash all expected_result values against the new snapshot; commit the updated gold set.

Question. Design the snapshot workflow and integrate it into the CI eval pipeline.

Input.

Component	Value
Source	production analytics.fct.*
Snapshot mechanism	Snowflake CLONE
Cadence	monthly rotation
CI trigger	prompt-template PR
Cost cap	$50 per CI run

Code.

-- 1. Zero-copy clone from production to dev (Snowflake)
CREATE OR REPLACE DATABASE analytics_eval
    CLONE analytics
    AT (TIMESTAMP => '2026-07-30 00:00:00'::timestamptz);

-- 2. Grant the harness read-only access to the snapshot
GRANT USAGE ON DATABASE analytics_eval TO ROLE eval_harness;
GRANT USAGE ON ALL SCHEMAS IN DATABASE analytics_eval TO ROLE eval_harness;
GRANT SELECT ON ALL TABLES IN DATABASE analytics_eval TO ROLE eval_harness;

-- 3. Dedicated tiny warehouse; auto-suspend after 60s
CREATE WAREHOUSE eval_wh
    WITH WAREHOUSE_SIZE = 'X-SMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

GRANT USAGE ON WAREHOUSE eval_wh TO ROLE eval_harness;

-- 4. Resource monitor caps the eval spend
CREATE RESOURCE MONITOR eval_daily
    WITH CREDIT_QUOTA = 20      -- ~$50/day on X-SMALL
    FREQUENCY = DAILY
    TRIGGERS
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE eval_wh SET RESOURCE_MONITOR = eval_daily;

# 5. CI job — .github/workflows/eval.yml
name: eval-harness
on:
  pull_request:
    paths:
      - "prompts/**"
      - "models/**"
      - "harness/**"
jobs:
  eval:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install
        run: pip install -r harness/requirements.txt
      - name: Run harness
        env:
          SNOWFLAKE_PASSWORD: ${{ secrets.SNOWFLAKE_PASSWORD }}
          SNOWFLAKE_ROLE: eval_harness
          SNOWFLAKE_WAREHOUSE: eval_wh
          SNOWFLAKE_DATABASE: analytics_eval
        run: python -m harness.run --gold gold_set.jsonl --report scorecard.md
      - name: Post scorecard
        uses: actions/github-script@v7
        with:
          script: |
            const fs = require('fs');
            const body = fs.readFileSync('scorecard.md', 'utf8');
            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: body,
            });
      - name: Enforce regression gate
        run: python -m harness.check --scorecard scorecard.md --baseline baseline.json --max-regression 2

Step-by-step explanation.

1. Snowflake CLONE is zero-copy: creating analytics_eval from analytics costs no storage until pages diverge, so a full-warehouse snapshot is essentially free. This is the killer feature for eval workflows — you get a frozen point-in-time view without paying for a copy.
2. The dedicated eval_wh warehouse is X-SMALL with 60s auto-suspend. The CI run wakes it, runs the harness, and lets it suspend — total warehouse spend is a few dollars per run.
3. The eval_daily resource monitor is the disaster stop. If a runaway harness or a buggy prompt template starts burning credits, the warehouse suspends immediately at $50/day. This is the safety net that makes running the harness on every PR safe.
4. The GitHub Actions job triggers on any PR that touches prompts, models, or harness code. It runs the harness, posts the scorecard as a PR comment, then enforces the regression gate: if execution accuracy drops by more than 2 points versus the baseline, the check fails and the PR cannot merge.
5. Monthly rotation of the snapshot label (analytics_eval_2026_08_30) and the expected_hash field in the gold set is a small chore. Automate it: nightly job re-runs all gold SQLs against the new snapshot, updates the hashes, and opens a PR. Reviewers approve or reject based on whether the hash changes reflect real data drift.

Output.

Layer	Cost per CI run	Guarantee
Zero-copy clone	~$0 storage	frozen point-in-time
X-SMALL eval warehouse	~$2-5 per full harness run	isolated from production
Resource monitor	hard-stop at $50/day	worst-case bounded
CI regression gate	fails at -2 points	no silent quality drops
Monthly snapshot rotation	1 PR/month	fresh data every 30 days

Rule of thumb. For any text-to-SQL eval harness at scale, snapshot the warehouse with zero-copy clone, run against a dedicated tiny warehouse with a hard resource-monitor cap, gate CI on a regression threshold, and rotate the snapshot monthly. This bounds the harness cost to tens of dollars per week regardless of gold-set size.

Senior interview question on eval-harness design

A senior interviewer might ask: "Design the eval harness for a text-to-SQL feature you plan to ship to internal analysts against a 400-table Snowflake warehouse. Cover the gold-set construction, the three scoring metrics, the plan-hash pre-filter, the CI integration, the regression gate, and the operational cost. Then explain how you would prevent overfit and how you would decide when the harness itself is wrong."

Solution Using a 500-prompt private gold set + three-tier cascade + zero-copy dev snapshot + CI regression gate + adversarial hold-out

# harness/run.py — production eval harness with the three-tier cascade

import json
import time
from dataclasses import dataclass, field
from pathlib import Path

import sqlglot
import snowflake.connector as sf


@dataclass
class HarnessResult:
    total:          int
    exact_matches:  int
    plan_matches:   int
    exec_matches:   int
    errors:         int
    per_tag:        dict = field(default_factory=dict)
    weakest_tag:    str  = ""
    cost_usd:       float = 0.0


def run_harness(gold_path: Path, model_fn, cost_cap_usd: float = 50.0) -> HarnessResult:
    conn = sf.connect(role="eval_harness", warehouse="eval_wh",
                      database="analytics_eval")
    r = HarnessResult(total=0, exact_matches=0, plan_matches=0,
                      exec_matches=0, errors=0)

    with open(gold_path) as f:
        for line in f:
            gold = json.loads(line)
            r.total += 1
            try:
                predicted = model_fn(gold["question"])
            except Exception:
                r.errors += 1
                continue

            # Tier 1: exact-match (microseconds)
            if canonicalise_sql(predicted) == canonicalise_sql(gold["gold_sql"]):
                r.exact_matches += 1
                r.exec_matches  += 1
                bump_tag(r, gold["tags"], "exec", True)
                continue

            # Tier 2: plan-hash (milliseconds; no data scan)
            if plan_hash(conn, predicted) == plan_hash(conn, gold["gold_sql"]):
                r.plan_matches += 1
                r.exec_matches += 1
                bump_tag(r, gold["tags"], "exec", True)
                continue

            # Tier 3: execution (seconds + $)
            try:
                got = execute_with_cap(conn, predicted, bytes_cap=5_000_000_000)
                got_hash = canonicalise_result(got)
                exec_ok = got_hash == gold["expected_hash"]
                r.exec_matches += int(exec_ok)
                bump_tag(r, gold["tags"], "exec", exec_ok)
            except Exception:
                r.errors += 1
                bump_tag(r, gold["tags"], "exec", False)

            if estimate_cost(r) > cost_cap_usd:
                raise RuntimeError(f"harness cost cap {cost_cap_usd} exceeded")

    r.weakest_tag = min(r.per_tag, key=lambda t: r.per_tag[t]["exec_rate"])
    r.cost_usd    = estimate_cost(r)
    return r


def bump_tag(r: HarnessResult, tags, kind: str, ok: bool):
    for t in tags:
        d = r.per_tag.setdefault(t, {"n": 0, "exec_ok": 0, "exec_rate": 0.0})
        d["n"] += 1
        if kind == "exec" and ok:
            d["exec_ok"] += 1
        d["exec_rate"] = d["exec_ok"] / d["n"]

# harness/check.py — the regression gate
import json
import sys
from pathlib import Path

def check(scorecard: Path, baseline: Path, max_regression: float) -> int:
    cur  = json.loads(scorecard.read_text())
    base = json.loads(baseline.read_text())

    cur_acc  = cur["exec_matches"] / cur["total"]
    base_acc = base["exec_matches"] / base["total"]
    delta    = (cur_acc - base_acc) * 100  # percentage points

    if delta < -max_regression:
        print(f"REGRESSION: {base_acc:.1%} -> {cur_acc:.1%} (delta={delta:.1f}pp)")
        return 1

    print(f"OK: {base_acc:.1%} -> {cur_acc:.1%} (delta={delta:+.1f}pp)")
    return 0


if __name__ == "__main__":
    sys.exit(check(Path("scorecard.md"), Path("baseline.json"),
                   max_regression=2.0))

# scorecard.md — posted as PR comment
### Eval scorecard

| Metric | Value |
|---|---|
| Total prompts | 500 |
| Exact-match | 128 (25.6%) |
| Plan-hash | 210 (42.0%) |
| Execution accuracy | 428/500 (85.6%) |
| Errors | 6 (1.2%) |
| Baseline (main) | 84.4% |
| Delta | +1.2 pp (PASS) |
| Cost | $3.87 |

### Weakest tag: window (67% exec_acc, n=42)

### Adversarial hold-out: 88.0% (unchanged)

Step-by-step trace.

Layer	Component	Purpose
Gold set	500 curated prompts + 100 adversarial hold-out	ship gate + overfit detector
Snapshot	zero-copy Snowflake CLONE	frozen deterministic result hashes
Tier 1	exact-match	free pass for byte-identical rewrites
Tier 2	plan-hash EXPLAIN	cheap semantic pass
Tier 3	execution	ground truth; capped at 5 GB scanned per query
CI gate	-2 pp regression blocker	zero silent quality drops
Weakest tag	per-tag exec_acc surface	tells you where to invest
Adversarial	separate hold-out (100 prompts)	catches overfit to public gold

After deployment, every prompt-template PR runs the harness, posts a scorecard, and gates merge on a -2 pp regression threshold. The three-tier cascade keeps each run under $5 on a 500-prompt gold set. The adversarial hold-out is refreshed quarterly with real production failures; a divergence between the main gold accuracy and the adversarial accuracy is the earliest overfit signal.

Output:

Metric	Value
Gold-set size	500 (main) + 100 (adversarial)
Cost per harness run	$3-5
Time per harness run	~4 minutes
Regression sensitivity	2 pp on execution accuracy
Overfit detector	adversarial hold-out delta > 5 pp
Deploy blocker	100% of prompt PRs pass or explain

Why this works — concept by concept:

• Private gold set on git — versioned, PR-reviewed, blamed. The gold set is the release contract; storing it as code is the only way to make it operationally serious.
• Three-tier cascade — exact-match → plan-hash → execution. Each tier resolves 30-40 percent of prompts at increasing cost. Total harness cost scales linearly with gold-set size at a very small constant.
• Zero-copy Snowflake clone — frozen point-in-time snapshot at no storage cost. The determinism of the expected_hash field depends on the frozen data; snapshot rotation is the only reason hashes change.
• CI regression gate — -2 pp is the deploy blocker. This is a much stronger contract than "we run tests" — it says "quality cannot silently drop on any prompt change."
• Adversarial hold-out — a partition of the gold set that never enters any prompt template or fine-tune. Divergence between main and adversarial accuracy is the overfit alarm; when it fires, rotate the main partition.
• Cost — 4 minutes and $5 per CI run against a 500-prompt gold set; scales to $25 per run at 2000 prompts with cascade. The eliminated cost is the silent regression that ships and only surfaces in user complaints two weeks later. Net O(1) per prompt through the cascade; O(gold-set) total per run.

SQL Generation
Topic — sql-generation
SQL-generation eval-harness problems

Practice →

Data Validation Topic — data-validation Data-validation and result-set diff problems

Practice →

---

3. Guardrails — schema linking, query validation, safety rails

Four guardrail lanes — every LLM SQL passes all four or it does not touch the warehouse

The mental model in one line: nl2sql guardrails are the four independent lanes every LLM-generated candidate SQL must pass through before execution — schema linking (prune the prompt to the top-N relevant tables), parse validation (SQLGlot AST parse plus identifier existence check), warehouse dry-run cost (Snowflake EXPLAIN or BigQuery dry-run byte estimate), and safety allow-list (only SELECT and WITH statements against a read-only role) — and skipping any lane leaves the failure class it protects wide open, so seniors build the four lanes as first-class services, not as afterthoughts in the app code. The lanes are independent so a fault on one does not cascade; each lane has its own error taxonomy and its own alerting.

The four lanes — what each catches and how.

• Schema linking (pre-generation). Retrieve the top-N tables and columns relevant to the user question and inject only those into the model prompt. Reduces the model's opportunity to hallucinate — if the invented column is not in the prompt, the model rarely conjures it. Implemented via an embedding index over DDL + column descriptions plus a table-graph walk that grabs foreign-key neighbours.
• Parse validation (post-generation, pre-execution). Parse the generated SQL with SQLGlot to confirm it is syntactically well-formed; walk the AST to enumerate every table and column reference; look each up in the live warehouse catalog; reject on any unknown identifier. This lane catches every hallucinated column and every misspelt table before the warehouse ever sees the query.
• Dry-run cost (post-parse, pre-execution). Ask the warehouse for a cost / byte estimate without running the query. Snowflake exposes this via EXPLAIN; BigQuery via the dryRun job flag; Databricks via EXPLAIN COST. Reject any query whose estimated scan exceeds a per-tenant budget (typical: 5 GB per query, 500 GB per tenant per day).
• Safety allow-list (statement-type filter). Parse the top-level statement; reject anything that is not SELECT or WITH ... SELECT. Combined with the read-only role, this is belt-and-braces against DML / DDL that a mis-prompted or adversarial user might induce.

Pre-execution vs post-execution guardrails.

• Pre-execution. All four lanes fire before the query touches production data. Failures are cheap (parse error, dry-run rejection) and never scan bytes or lock rows. This is where 99 percent of guardrail effort belongs.
• Post-execution. Row-count sanity checks (does the result look plausible?), column-mask enforcement (redact PII before returning), result-set size caps (never send more than 10k rows to the client). Post-execution is a second line of defense, not a substitute for pre-execution rejection.

The row-limit + read-only + column-mask trio.

• Row limit. Every generated SQL is rewritten to SELECT ... LIMIT N if LIMIT is missing. The LIMIT is a hard cap on result-set size shipped to the client, protecting bandwidth and the browser from a 5 million-row response.
• Read-only role. The service account under which the bot connects has SELECT only. No INSERT, no UPDATE, no DELETE, no CREATE, no DROP. Even if a lane fails, the warehouse's own permission check rejects the destructive statement.
• Column mask. Every PII column (email, phone, ssn_last4, dob) is either masked at the warehouse via MASKING POLICY or stripped at the API layer before render. The mask defends against a prompt like "show me all customers with names starting with A" that would otherwise leak PII into a chat log.

Common interview probes on guardrails.

• "How do you stop the model from dropping a table?" — required answer: read-only role plus statement-type allow-list; the drop never reaches the warehouse and the warehouse rejects it if it did.
• "How do you stop a runaway 500-TB scan?" — required answer: warehouse dry-run byte estimate plus per-tenant scanned-bytes cap.
• "How do you catch a hallucinated column?" — parse the SQL with SQLGlot, enumerate identifiers, look up in the live catalog, reject on unknown.
• "How do you protect PII?" — column-level masking policies at the warehouse plus API-layer redaction, never trust the model to filter PII.

Worked example — the schema-linking retriever

Detailed explanation. The single most cost-effective guardrail is schema linking — pruning the prompt to only the top-N tables and columns the model actually needs. On a 400-table warehouse, dumping the full DDL into the prompt (a) does not fit and (b) invites hallucination. A retriever grounded on table + column descriptions plus foreign-key neighbours reliably returns the 5-10 tables that matter for any given user question.

• The corpus. One document per table containing table_name, description, columns[] with names and descriptions, foreign_keys[] pointing at neighbours.
• The index. Embeddings over the corpus (text-embedding-3-large or an open-source equivalent) stored in a vector DB (pgvector, Weaviate, Qdrant).
• The retriever. For each user question, embed the question, top-K nearest tables, expand by one hop along foreign keys, cap at 15 tables and 300 columns.

Question. Implement the schema-linking retriever and show how the shortened schema context enters the prompt.

Input.

Component	Value
Warehouse	Snowflake with 400 tables
Corpus	one JSON doc per table
Embedding model	text-embedding-3-large (3072 dims)
Vector store	pgvector on Postgres
Prompt cap	15 tables, 300 columns

Code.

# schema_linker.py — retrieve the top-N tables for a user question
import json
from dataclasses import dataclass
from typing import Iterable

import psycopg2
import openai   # or any embedding provider


@dataclass
class TableDoc:
    name:         str
    description:  str
    columns:      list[dict]         # [{name, type, description}]
    foreign_keys: list[dict]         # [{col, ref_table, ref_col}]
    embedding:    list[float] | None = None


def embed(text: str) -> list[float]:
    r = openai.embeddings.create(model="text-embedding-3-large", input=text)
    return r.data[0].embedding


def index_corpus(conn, corpus: Iterable[TableDoc]) -> None:
    """One-time indexing: embed every table doc, store in pgvector."""
    with conn.cursor() as cur:
        for doc in corpus:
            payload = f"{doc.name}: {doc.description}\n" + \
                      "\n".join(f"- {c['name']} ({c['type']}): {c['description']}"
                                for c in doc.columns)
            emb = embed(payload)
            cur.execute("""
                INSERT INTO schema_index(table_name, doc_json, embedding)
                VALUES (%s, %s, %s)
                ON CONFLICT (table_name) DO UPDATE
                  SET doc_json = EXCLUDED.doc_json,
                      embedding = EXCLUDED.embedding
            """, (doc.name, json.dumps(doc.__dict__), emb))
        conn.commit()


def retrieve(conn, question: str, top_k: int = 8, hop: int = 1,
             max_tables: int = 15, max_columns: int = 300) -> list[TableDoc]:
    """Return the top-N tables for a user question."""
    q_emb = embed(question)

    with conn.cursor() as cur:
        cur.execute("""
            SELECT table_name, doc_json,
                   embedding <=> %s::vector AS distance
            FROM   schema_index
            ORDER  BY distance ASC
            LIMIT  %s
        """, (q_emb, top_k))
        seeds = [(row[0], json.loads(row[1])) for row in cur.fetchall()]

    picked: dict[str, TableDoc] = {}
    for name, doc in seeds:
        picked[name] = TableDoc(**doc)

    # Foreign-key expansion (one hop)
    for _ in range(hop):
        neighbours = set()
        for doc in list(picked.values()):
            for fk in doc.foreign_keys:
                if fk["ref_table"] not in picked:
                    neighbours.add(fk["ref_table"])
        if not neighbours:
            break
        with conn.cursor() as cur:
            cur.execute("""
                SELECT table_name, doc_json
                FROM   schema_index
                WHERE  table_name = ANY(%s)
            """, (list(neighbours),))
            for name, doc_json in cur.fetchall():
                picked[name] = TableDoc(**json.loads(doc_json))
                if len(picked) >= max_tables:
                    break

    # Cap column count across selected tables
    total_cols = sum(len(t.columns) for t in picked.values())
    if total_cols > max_columns:
        for t in picked.values():
            while sum(len(x.columns) for x in picked.values()) > max_columns and t.columns:
                t.columns.pop()

    return list(picked.values())[:max_tables]


def format_for_prompt(docs: list[TableDoc]) -> str:
    lines: list[str] = ["-- schema context (top-15 relevant tables)"]
    for d in docs:
        lines.append(f"\n-- {d.name}: {d.description}")
        lines.append(f"CREATE TABLE {d.name} (")
        for c in d.columns:
            lines.append(f"  {c['name']} {c['type']},   -- {c['description']}")
        lines.append(");")
        for fk in d.foreign_keys:
            lines.append(f"-- FK {d.name}.{fk['col']} -> {fk['ref_table']}.{fk['ref_col']}")
    return "\n".join(lines)

Step-by-step explanation.

1. The corpus is one embedded document per table, not per column, because column-level embeddings explode the index size without adding much signal at the retrieval stage. Column descriptions are packed into the table document so the embedding captures the column vocabulary; per-column retrieval happens later inside the model.
2. retrieve embeds the user question, does a cosine top-K over the table index, then expands one hop along foreign keys. The FK hop is the killer feature — if the question needs orders and customers and only orders scored in the top-K, the FK expansion pulls customers in even without semantic overlap in the question text.
3. The cap logic (max_tables=15, max_columns=300) protects the prompt budget. A 400-table warehouse can produce a 50k-token DDL dump; capping at 15 tables keeps the schema context under 5k tokens, leaving room for the actual instruction template.
4. format_for_prompt emits a CREATE TABLE DDL-ish rendering with inline column comments and FK annotations at the bottom. This is the format frontier LLMs respond best to — DDL is a shape they've seen in training, and inline comments improve schema comprehension.
5. The retriever runs in ~50 ms end-to-end (one embedding call, one vector query, one FK lookup). This is well under the 5-second p95 budget for the whole text-to-SQL pipeline. Cache popular questions to drop retrieval to zero on repeats.

Output.

User question	Seed tables (top-K)	After FK hop	Final prompt tables
"top 5 US customers by revenue"	fct.revenue_by_customer, dim.customers	+fct.orders, +dim.geo	5 tables, ~40 cols
"monthly active users last quarter"	fct.wau_weekly, dim.users	+fct.sessions	3 tables, ~20 cols
"pending shipments over 3 days"	fct.orders, dim.products	+dim.warehouses, +dim.suppliers	6 tables, ~50 cols

Rule of thumb. Never dump the full DDL into a text-to-SQL prompt. Build a schema-linking retriever that returns 5-15 tables plus their FK neighbours, format them as annotated CREATE TABLE DDL, and cap the prompt at 300 columns. This single lane cuts hallucination by 70-90 percent versus a raw-DDL prompt.

Worked example — SQLGlot parse and identifier validation

Detailed explanation. After the model generates SQL, the parse-validation lane must confirm the SQL is (a) syntactically well-formed and (b) references only real tables and columns. SQLGlot is the workhorse — it parses SQL for 20+ dialects, exposes an AST, and lets you walk every identifier reference cheaply. The validation is O(number of identifiers) and runs in milliseconds.

• Parse. sqlglot.parse_one(sql, read=dialect) returns the AST or raises ParseError.
• Identifier walk. Traverse the AST, collect every Table and Column node.
• Catalog lookup. For each identifier, check the live warehouse catalog (INFORMATION_SCHEMA or an in-memory cached snapshot).
• Reject taxonomy. Distinguish "table not found" from "column not found in known table" from "column ambiguous across joined tables."

Question. Implement the parse-validation lane and integrate it into the guardrail pipeline.

Input.

Component	Value
Parser	SQLGlot (Snowflake dialect)
Catalog source	INFORMATION_SCHEMA cached hourly
Reject taxonomy	3 error kinds
Latency budget	5 ms per query

Code.

# parse_validator.py — the parse-validation guardrail lane
import sqlglot
from sqlglot import exp
from dataclasses import dataclass


@dataclass
class ValidationError:
    kind:    str       # "parse", "unknown_table", "unknown_column", "ambiguous_column"
    message: str
    node:    str | None = None


@dataclass
class Catalog:
    tables:  dict[str, set[str]]      # {table_name: {col1, col2, ...}}

    @staticmethod
    def snapshot(conn) -> "Catalog":
        with conn.cursor() as cur:
            cur.execute("""
                SELECT table_schema || '.' || table_name AS full_name,
                       array_agg(lower(column_name))       AS cols
                FROM   information_schema.columns
                WHERE  table_schema IN ('DIM', 'FCT', 'RAW', 'SEMANTIC')
                GROUP  BY 1
            """)
            return Catalog(tables={row[0].lower(): set(row[1]) for row in cur.fetchall()})


def validate(sql: str, catalog: Catalog, dialect: str = "snowflake") -> list[ValidationError]:
    errors: list[ValidationError] = []

    # 1. Parse
    try:
        tree = sqlglot.parse_one(sql, read=dialect)
    except sqlglot.ParseError as e:
        return [ValidationError(kind="parse", message=str(e))]

    # 2. Collect referenced tables
    tables_in_query = {
        t.name.lower() if not t.db else f"{t.db.lower()}.{t.name.lower()}"
        for t in tree.find_all(exp.Table)
    }

    known_tables = catalog.tables.keys()
    for t in tables_in_query:
        if t not in known_tables:
            errors.append(ValidationError(kind="unknown_table",
                                          message=f"table not found: {t}",
                                          node=t))

    # 3. Collect column references, keyed by table if aliased
    for col in tree.find_all(exp.Column):
        col_name = col.name.lower()
        tbl_name = col.table.lower() if col.table else None

        if tbl_name and tbl_name in catalog.tables:
            if col_name not in catalog.tables[tbl_name]:
                errors.append(ValidationError(kind="unknown_column",
                                              message=f"column not found: {tbl_name}.{col_name}",
                                              node=f"{tbl_name}.{col_name}"))
        elif not tbl_name:
            # Column is unaliased. Search across every table in the query.
            hits = [t for t in tables_in_query if col_name in catalog.tables.get(t, set())]
            if not hits:
                errors.append(ValidationError(kind="unknown_column",
                                              message=f"column not resolvable: {col_name}"))
            elif len(hits) > 1:
                errors.append(ValidationError(kind="ambiguous_column",
                                              message=f"ambiguous: {col_name} in {hits}"))

    return errors

Step-by-step explanation.

1. Step 1 parses with SQLGlot. A ParseError is a hard reject — the model produced syntactically invalid SQL. This catches subtle mistakes like unmatched parentheses or wrong keyword order that would otherwise waste warehouse compilation cycles.
2. Step 2 walks the AST with tree.find_all(exp.Table) and collects every table reference. Comparison against the live catalog rejects any hallucinated table. The catalog snapshot is refreshed hourly; anything more frequent is unnecessary given schema-evolution cadence.
3. Step 3 walks column references. Aliased columns (orders.customer_id) are validated against the aliased table's known columns. Un-aliased columns are searched across every table in the query; a hit in exactly one table passes, zero hits or multiple hits fail. This matches SQL's own name-resolution rules.
4. The three-error taxonomy (unknown_table, unknown_column, ambiguous_column) is what the downstream retry logic uses. On unknown_table, retry with an expanded schema-linking context. On unknown_column, retry with a hint pointing at the closest real column name. On ambiguous_column, retry with a hint asking the model to fully qualify.
5. Latency: SQLGlot parses a typical SQL in ~1 ms; the AST walk is ~1 ms; the catalog lookups are pure Python dict operations against an in-memory snapshot. End-to-end lane latency is under 5 ms for 99 percent of queries.

Output.

SQL	Errors returned
SELECT id FROM orders	none
SELECT cust_id FROM orders	unknown_column: orders.cust_id
SELECT id FROM ordres	unknown_table: ordres
SELECT user_id FROM sessions JOIN customers ON ...	ambiguous_column: user_id in [sessions, customers]
SELECT * FROM orders WHERE (	parse: unexpected end of input

Rule of thumb. Run every LLM-generated SQL through a SQLGlot parse plus identifier-lookup lane before the warehouse sees it. Cache the catalog for an hour; refresh on INFORMATION_SCHEMA change events. Distinguish the three reject kinds so retry prompts can be targeted.

Worked example — warehouse dry-run cost gate

Detailed explanation. Even a syntactically valid SQL against real tables can scan a petabyte if it joins the wrong things. The warehouse dry-run lane asks the engine "how much would this cost?" without actually running the query, and rejects anything above the per-tenant budget. Snowflake exposes this via EXPLAIN; BigQuery via dryRun; Databricks via EXPLAIN COST.

• Snowflake. EXPLAIN USING JSON <sql> returns a plan JSON that includes partitionsTotal and partitionsScanned estimates. Use them to bound scan bytes.
• BigQuery. Set dryRun=True on the job config; the API returns totalBytesProcessed without running the query.
• Databricks. EXPLAIN COST <sql> returns an estimated cost tree; parse for scan bytes.

Question. Implement the cost-gate lane for Snowflake with a 5 GB per-query cap.

Input.

Component	Value
Warehouse	Snowflake
Estimator	EXPLAIN USING JSON
Cap	5,000,000,000 bytes (5 GB)
Reject reason	"estimated scan exceeds budget"

Code.

# cost_gate.py — Snowflake dry-run cost estimator
import json
import snowflake.connector as sf


CAP_BYTES = 5_000_000_000   # 5 GB per query


def estimate_scan_bytes(conn, sql: str) -> int:
    """Return an estimated scan-bytes upper bound for the query."""
    with conn.cursor() as cur:
        cur.execute(f"EXPLAIN USING JSON {sql}")
        plan_rows = cur.fetchall()
    # Snowflake returns one row with the plan JSON in the first column
    plan = json.loads(plan_rows[0][0])
    scanned = 0
    for step in walk_steps(plan):
        # partitionsTotal * average_partition_size approximates scan bytes;
        # a more accurate estimate uses TABLE_DML_HISTORY.
        if step.get("operation") == "TableScan":
            scanned += step.get("stats", {}).get("bytesScanned", 0)
    return scanned


def walk_steps(node):
    yield node
    for child in node.get("children", []):
        yield from walk_steps(child)


def check_cost(conn, sql: str, cap_bytes: int = CAP_BYTES) -> tuple[bool, str]:
    try:
        estimated = estimate_scan_bytes(conn, sql)
    except Exception as e:
        return False, f"dry-run failed: {e}"

    if estimated > cap_bytes:
        return False, (f"estimated scan {estimated / 1e9:.1f} GB "
                       f"exceeds budget of {cap_bytes / 1e9:.1f} GB")

    return True, f"estimated scan {estimated / 1e9:.2f} GB (ok)"

Step-by-step explanation.

1. EXPLAIN USING JSON <sql> runs the Snowflake compiler and returns the full plan without touching data. Compilation is measured in tens of milliseconds; no warehouse credits are consumed.
2. walk_steps recursively yields every plan node. TableScan nodes carry bytesScanned estimates that Snowflake derives from cached table statistics. Summing across scans gives an upper-bound estimate for the query.
3. The cap (5 GB) is tunable per-tenant. Internal analyst prompts might allow 10 GB; customer-facing self-serve prompts might cap at 500 MB. The cap lives in configuration, not code, so on-call can tighten it without a deploy.
4. On rejection, the returned message names the estimated scan and the budget so the front-end can show the user something actionable ("your query would scan 47 GB; try adding a date filter"). This is far more useful than a generic "query rejected."
5. Latency: EXPLAIN is 20-50 ms on Snowflake; the JSON parse and step walk are microseconds. The lane budget is comfortable at 100 ms p99.

Output.

SQL (approx)	Estimated scan	Verdict
SELECT COUNT(*) FROM fct.orders WHERE order_date = current_date	12 MB	pass
SELECT * FROM fct.orders o JOIN fct.events e ON o.customer_id = e.user_id	47 GB	reject (>5 GB)
SELECT SUM(revenue) FROM fct.revenue_daily WHERE month = current_month	340 MB	pass
SELECT * FROM raw.clickstream	890 GB	reject (>5 GB)

Rule of thumb. Every generated SQL passes a warehouse dry-run cost gate before execution. Use the native estimator (EXPLAIN USING JSON in Snowflake, dryRun in BigQuery, EXPLAIN COST in Databricks). Cap in bytes not seconds — a query that scans 100 GB but takes 4 seconds still costs $50 in warehouse credits.

Senior interview question on guardrail architecture

A senior interviewer might ask: "Your team ships text-to-SQL to 5000 internal analysts on a Snowflake warehouse with 400 tables. Design the guardrail stack. Cover the schema-linking retriever, the parse-validation lane, the cost-gate lane, and the safety allow-list lane. Explain how failures on each lane feed back into the retry / prompt-tuning loop, and how you would monitor the guardrail stack itself."

Solution Using a four-lane guardrail pipeline with typed error surfaces + retry loops + Prometheus metrics

# guardrails/pipeline.py — the four-lane guardrail pipeline

from dataclasses import dataclass
from enum import Enum


class LaneVerdict(Enum):
    PASS = "pass"
    REJECT_HALLUCINATION = "reject_hallucination"
    REJECT_UNSAFE_OP = "reject_unsafe_op"
    REJECT_OVER_BUDGET = "reject_over_budget"
    REJECT_PARSE = "reject_parse"


@dataclass
class LaneResult:
    lane:       str
    verdict:    LaneVerdict
    detail:     str
    latency_ms: float


def run_guardrails(user_question: str, catalog, conn, model_fn) -> tuple[str | None, list[LaneResult]]:
    trace: list[LaneResult] = []

    # Lane 1: schema linking (pre-generation)
    t0 = perf_counter()
    schema_docs = retrieve(conn, user_question, top_k=8, hop=1)
    trace.append(LaneResult("schema_link", LaneVerdict.PASS,
                            f"{len(schema_docs)} tables selected",
                            (perf_counter() - t0) * 1000))
    prompt = build_prompt(user_question, schema_docs)

    for attempt in range(3):
        t0 = perf_counter()
        candidate_sql = model_fn(prompt)
        trace.append(LaneResult("generate", LaneVerdict.PASS,
                                f"attempt {attempt+1}",
                                (perf_counter() - t0) * 1000))

        # Lane 2: parse validation
        t0 = perf_counter()
        parse_errors = validate(candidate_sql, catalog)
        parse_ms = (perf_counter() - t0) * 1000
        if parse_errors:
            trace.append(LaneResult("parse", LaneVerdict.REJECT_HALLUCINATION,
                                    "; ".join(e.message for e in parse_errors[:3]), parse_ms))
            prompt = augment_prompt_with_errors(prompt, parse_errors)
            continue
        trace.append(LaneResult("parse", LaneVerdict.PASS, "ok", parse_ms))

        # Lane 3: safety allow-list
        t0 = perf_counter()
        if not is_read_only(candidate_sql):
            trace.append(LaneResult("safety", LaneVerdict.REJECT_UNSAFE_OP,
                                    "non-SELECT rejected",
                                    (perf_counter() - t0) * 1000))
            return None, trace
        trace.append(LaneResult("safety", LaneVerdict.PASS, "SELECT ok",
                                (perf_counter() - t0) * 1000))

        # Lane 4: cost gate
        t0 = perf_counter()
        ok, cost_msg = check_cost(conn, candidate_sql, cap_bytes=5_000_000_000)
        cost_ms = (perf_counter() - t0) * 1000
        if not ok:
            trace.append(LaneResult("cost", LaneVerdict.REJECT_OVER_BUDGET,
                                    cost_msg, cost_ms))
            return None, trace
        trace.append(LaneResult("cost", LaneVerdict.PASS, cost_msg, cost_ms))

        # All four lanes passed
        return candidate_sql, trace

    # Retries exhausted
    return None, trace


def is_read_only(sql: str) -> bool:
    tree = sqlglot.parse_one(sql, read="snowflake")
    top = tree.__class__.__name__.lower()
    return top in ("select", "with", "union", "intersect", "except")


def augment_prompt_with_errors(prompt: str, errors) -> str:
    hint_lines = ["-- previous attempt failed with the following errors:"]
    for e in errors[:5]:
        hint_lines.append(f"-- - {e.kind}: {e.message}")
    hint_lines.append("-- please fix and re-generate")
    return prompt + "\n\n" + "\n".join(hint_lines)

# prometheus_metrics.yml — instrumentation for the guardrail stack
# One counter per lane / verdict; one histogram per lane latency.
metrics:
  - name: guardrail_verdicts_total
    type: counter
    labels: [lane, verdict, tenant]
  - name: guardrail_lane_latency_ms
    type: histogram
    labels: [lane, tenant]
    buckets: [1, 5, 10, 25, 50, 100, 250, 500]
  - name: guardrail_retry_count
    type: histogram
    labels: [tenant]
    buckets: [0, 1, 2, 3]

alerts:
  - name: SchemaLinkRegression
    expr: |
      rate(guardrail_verdicts_total{lane="parse",verdict="reject_hallucination"}[15m])
        / rate(guardrail_verdicts_total{lane="parse"}[15m]) > 0.15
    for: 15m
    severity: warning
  - name: CostGateStorm
    expr: |
      rate(guardrail_verdicts_total{lane="cost",verdict="reject_over_budget"}[5m]) > 5
    for: 5m
    severity: critical

Step-by-step trace.

Layer	Component	Purpose
Lane 1	schema-linking retriever	prune prompt to 15 tables
Lane 2	SQLGlot parse + identifier validation	reject hallucinated tables/columns
Lane 3	safety allow-list	reject any non-SELECT/WITH
Lane 4	Snowflake EXPLAIN cost gate	reject scan > 5 GB
Retry loop	augment prompt with lane errors	2 additional attempts on hallucination
Metrics	Prometheus per-lane counters + histograms	detect regressions in production
Alerts	schema-link regression + cost-gate storm	on-call visibility

After deployment, every user question passes through the four lanes in ~150 ms end-to-end (dominated by the LLM call, not the guardrails themselves). Hallucinations trigger up to two retries with augmented prompts; a persistent hallucination is a null-return that surfaces to the user as "I couldn't answer that reliably" — the correct behaviour when the harness confidence is low.

Output:

Metric	Value
Lane 1 latency (p99)	~50 ms
Lane 2 latency (p99)	~5 ms
Lane 3 latency (p99)	~1 ms
Lane 4 latency (p99)	~50 ms
End-to-end guardrail overhead	~100 ms
Hallucination retry rate	8-12% (drops with prompt tuning)
Cost-gate reject rate	~3% (steady)
Unsafe-op reject rate	~0.1% (rare; users don't usually ask for DDL)

Why this works — concept by concept:

• Four independent lanes — each lane has a distinct failure taxonomy and a distinct mitigation. A regression in one lane does not silently corrupt the others; metrics per lane make the failure mode observable.
• Retry loop with augmented prompt — hallucinations are recoverable: feed the error taxonomy back to the model and it usually corrects on retry. Two retries is the practical sweet spot; three or more suggests the model cannot serve this question and the correct answer is to escalate.
• Read-only role + statement allow-list — belt-and-braces safety. The allow-list is fast (Python-side statement-type check) and rejects DDL/DML before they cost even a warehouse compilation. The read-only role is the final backstop.
• Warehouse dry-run cost estimator — the native cost estimator is nearly free (EXPLAIN takes tens of milliseconds and no credits). Bounding scan bytes per query bounds the worst-case cost of the entire system.
• Prometheus instrumentation — one counter per (lane, verdict) and one histogram per lane latency. This is enough to detect regressions (hallucination rate creeping up), cost storms (a bad prompt template getting rejected en masse), and latency drift.
• Cost — ~100 ms of latency overhead, ~5-10 percent of prompts trigger at least one retry, ~3 percent are rejected outright. The eliminated cost is the "one bad query blew a $18k hole" incident and the "the model wrote a DELETE" catastrophe. Net O(1) latency overhead per query with bounded worst case.

Data Validation
Topic — data-validation
Data-validation and safety-rail problems

Practice →

Design Topic — design Design problems on LLM guardrail architectures

Practice →

---

4. Semantic-layer grounding — dbt Semantic Layer, LookML, Cube

Ground on metrics, not raw tables — one definition, zero drift, and the model never redefines revenue again

The mental model in one line: semantic layer grounding is the pattern where the text-to-SQL system exposes the enterprise's canonical metric catalog (dbt Semantic Layer, LookML, Cube) as first-class candidates during retrieval, prompts the model to prefer a defined metric over a raw-table derivation whenever the question maps to one, and refuses to let the model reinvent revenue, MAU, churn or any other business metric from scratch — collapsing the metric-mis-definition failure class into a solved problem because the metric is defined once, upstream, and every downstream query reads from the same definition. Every serious 2026 text-to-SQL deployment grounds on a semantic layer for the top-20 metrics; the ones that skip this step ship dashboards that disagree with each other.

Why raw-table grounding loses at the enterprise scale.

• Metric drift. Without a semantic layer, revenue is defined in 8 different SQL files across the org — one includes shipping, one subtracts refunds, one applies FX conversion, one filters out test accounts. The model, given raw tables, picks a random derivation. Users see different numbers on different screens; trust collapses.
• Join complexity. The correct MAU computation joins sessions to users to bot_signatures to plan_history. The model, faced with 20 columns and 4 tables, has to reinvent the join every time — and reinvents it wrong 30 percent of the time. A metric defined once pre-joins for the model.
• Grain confusion. revenue at daily vs monthly vs quarterly grain is not the same thing. Raw-table SQL forces the model to figure out grain; semantic-layer metrics tag their grain explicitly.
• Access control. Row-level and column-level filters (tenant isolation, PII masking) live in the semantic layer once, not in every ad-hoc SQL the model writes. Semantic-layer grounding is a security posture, not just a correctness posture.

The semantic-layer contract — what a defined metric looks like.

• Metric name. weekly_active_users, net_revenue_daily, enterprise_churn_rate.
• Measure. The aggregation (count_distinct(user_id), sum(revenue) - sum(refund_amount)).
• Dimensions. The valid slice-by axes (week, country, plan, product_category).
• Filters. Standard filters applied to every query (is_bot = false, is_test_account = false).
• Grain. Time grain (day, week, month) and entity grain (user, account, order).
• Joins. Pre-defined joins the metric depends on so downstream never re-derives.

dbt Semantic Layer, LookML, Cube — the three canonical vendors.

• dbt Semantic Layer. Metric definitions live in metricflow YAML alongside the dbt project. Query via GraphQL, JDBC, or the MCP endpoint. Best fit for teams already invested in dbt. Compiles metric queries to warehouse-native SQL.
• LookML. Looker's semantic model language; metrics are defined as measure blocks in .model.lkml files. Query via the Looker API or the SDK. Best fit for teams whose primary BI tool is Looker. LookML has a decade of production hardening.
• Cube. Standalone semantic layer; metric definitions in JavaScript or YAML. Query via REST, GraphQL, or SQL API. Best fit for teams whose primary consumer is a custom app or LLM interface — Cube's SQL API is specifically designed for LLM grounding.

How grounding actually flows.

• Retrieve. Alongside table docs, the retriever indexes metric definitions and returns the top-N metrics for each user question. A question about "revenue" scores net_revenue_daily above any raw table.
• Prompt. The prompt template says "PREFER defined metrics over raw tables when a metric answers the question." The metric definitions enter the prompt as first-class candidates with example usage.
• Generate. The model emits SQL like SELECT * FROM {{ metric('net_revenue_daily') }} WHERE week >= ... (dbt syntax) or SELECT * FROM weekly_active_users WHERE ... (Cube's SQL API view).
• Compile. The semantic layer expands the metric reference into full warehouse SQL. This step is transparent to the model but ensures the compiled SQL always has the canonical join / filter / grain semantics.

Common interview probes on semantic-layer grounding.

• "Why not just add more docs?" — required answer: docs decay; a runtime semantic layer is the enforcement mechanism.
• "How do you decide which metrics to define?" — the top-20 by dashboard citation count. Instrument your BI tool to find them.
• "How do you handle a question that doesn't map to a defined metric?" — fall back to raw-table generation with a lowered confidence signal to the user.
• "How do you keep the semantic layer in sync with the model?" — refresh the retriever index on every dbt build or LookML deploy; treat metric changes as prompt-template changes with harness gating.

Worked example — define a weekly_active_users metric in dbt Semantic Layer

Detailed explanation. The canonical grounded prompt starts with a metric definition. Walk through defining weekly_active_users in dbt's metricflow YAML, exposing it via the Semantic Layer API, and then showing the compiled warehouse SQL that lands from a grounded prompt.

• Model. models/marts/weekly_active_users.sql — the underlying join and dedupe.
• Semantic model. models/semantic_models/users.yml — dimensions, entities, measures.
• Metric. metrics/wau.yml — the named metric.
• Query. JDBC or MCP call from the text-to-SQL app.

Question. Define the metric end-to-end and show the compiled SQL that lands from a grounded prompt.

Input.

Layer	File
Model	models/marts/weekly_active_users.sql
Semantic model	models/semantic_models/users.yml
Metric	metrics/wau.yml
Consumer	dbt Semantic Layer JDBC endpoint

Code.

# models/semantic_models/users.yml
semantic_models:
  - name: users
    model: ref('dim_users')
    entities:
      - name: user
        type: primary
        expr: user_id
    dimensions:
      - name: week
        type: time
        type_params:
          time_granularity: week
        expr: date_trunc('week', activity_ts)
      - name: country
        type: categorical
      - name: plan
        type: categorical
      - name: is_bot
        type: categorical
    measures:
      - name: distinct_users
        agg: count_distinct
        expr: user_id

# metrics/wau.yml
metrics:
  - name: weekly_active_users
    label: Weekly Active Users
    description: >
      Distinct user_id per ISO week, excluding bot traffic and
      test accounts. Grain: week. Definition owner: analytics-eng.
    type: simple
    type_params:
      measure: distinct_users
    filter: |
      {{ Dimension('user__is_bot') }} = false
      AND {{ Dimension('user__is_test_account') }} = false

# app/query_metric.py — the text-to-SQL app grounding on the metric
from dbt_semantic_layer_client import SemanticLayerClient

sl = SemanticLayerClient(host="sl.dbt.example.com", token=env["DBT_SL_TOKEN"])

# The model emits this after seeing the metric definition in its prompt context
grounded_query = {
    "metrics":    ["weekly_active_users"],
    "group_by":   ["week", "country"],
    "where":      "{{ Dimension('user__country') }} = 'US'",
    "order_by":   ["week"],
    "limit":      12,
}

# The Semantic Layer expands to warehouse-native SQL
compiled_sql, result = sl.query(**grounded_query)

-- Compiled SQL emitted by the Semantic Layer (Snowflake dialect, abbreviated)
SELECT
    date_trunc('week', u.activity_ts) AS week,
    u.country                          AS country,
    count(distinct u.user_id)          AS weekly_active_users
FROM analytics.dim.users u
WHERE u.is_bot           = false
  AND u.is_test_account  = false
  AND u.country          = 'US'
GROUP BY 1, 2
ORDER BY 1
LIMIT 12;

Step-by-step explanation.

1. The users semantic model in users.yml binds the underlying dim_users table to a canonical entity (user_id), the valid dimensions (week, country, plan, is_bot), and the available measures (distinct_users). This YAML is the durable contract.
2. The weekly_active_users metric in wau.yml references the distinct_users measure and pins the standard filter (is_bot = false, is_test_account = false). Every downstream query for this metric inherits the filter — no way to accidentally include bots.
3. The text-to-SQL app calls the dbt Semantic Layer JDBC/MCP endpoint with a metric query — a structured request naming the metric, group-by dimensions, filters, and order. The model does not emit raw SQL; it emits this structured payload.
4. The Semantic Layer compiles the metric query into warehouse-native SQL. All the pre-defined joins, filters, and grain semantics are baked in. The compiled SQL that hits Snowflake is deterministic and correct by construction.
5. If the user later asks "how many WAU last quarter by plan?", the model emits a different metric query (same metric, different group-by), and the Semantic Layer compiles a different SQL. The metric definition never changes; the grouping does.

Output.

Raw-table grounding (before)	Semantic-layer grounding (after)
Model writes SQL joining 4 tables	Model calls metric with dimensions
Filter is_bot = false sometimes forgotten	Filter always applied
Grain (week vs day) ambiguous	Grain locked to week
3 different MAU numbers on 3 dashboards	1 number, always
Metric change = touch 8 SQL files	Metric change = 1 YAML PR

Rule of thumb. For any metric worth naming, define it in the semantic layer once. Every downstream text-to-SQL query grounds on the metric name, not on raw tables. The metric definition is the source of truth; the compiled SQL is derived.

Worked example — grounded vs raw output for the same user question

Detailed explanation. The clearest way to see semantic-layer grounding pay off is a side-by-side comparison: same question, one prompt with raw-table context, one prompt with metric-first context. Walk through both flows for the question "how many WAU last week by plan?"

• Question. "How many weekly active users last week, split by plan?"
• Raw-table context. DDL for dim.users, fct.sessions, dim.bot_signatures, dim.test_accounts.
• Metric-first context. The weekly_active_users metric definition + plan as a valid dimension.

Question. Show the model output for both prompt strategies and compare correctness on 100 stakeholders' behalf.

Input.

Prompt strategy	Retriever output
Raw-table	4 DDL tables, ~40 columns
Metric-first	1 metric definition, 4 dimensions

Code.

# Prompt A — raw-table grounded
User: How many weekly active users last week, split by plan?
Context: [DDL for dim.users, fct.sessions, dim.bot_signatures, dim.test_accounts]

# Model output (representative):
SELECT
    u.plan,
    COUNT(DISTINCT s.user_id) AS wau
FROM   fct.sessions   s
JOIN   dim.users      u ON u.user_id = s.user_id
WHERE  s.started_at >= date_trunc('week', current_date) - INTERVAL '1 week'
  AND  s.started_at <  date_trunc('week', current_date)
GROUP  BY u.plan
ORDER  BY wau DESC;
-- ❌ Missing is_bot filter
-- ❌ Missing is_test_account filter
-- ✓  Grain correct
-- ✓  Group-by correct

# Prompt B — metric-first grounded
User: How many weekly active users last week, split by plan?
Context: metric weekly_active_users(measure=distinct_users, filter=is_bot=false AND is_test_account=false)
         dimensions: week, country, plan, is_bot

# Model output (representative):
{
  "metrics":  ["weekly_active_users"],
  "group_by": ["week", "plan"],
  "where":    "{{ Dimension('user__week') }} = date_trunc('week', current_date - 7)",
  "order_by": ["-weekly_active_users"],
  "limit":    100
}

# Compiled by Semantic Layer:
SELECT
    date_trunc('week', u.activity_ts) AS week,
    u.plan                             AS plan,
    COUNT(DISTINCT u.user_id)          AS weekly_active_users
FROM   analytics.dim.users u
WHERE  u.is_bot           = false
  AND  u.is_test_account  = false
  AND  date_trunc('week', u.activity_ts) = date_trunc('week', current_date - 7)
GROUP  BY 1, 2
ORDER  BY 3 DESC
LIMIT 100;
-- ✓ is_bot filter applied
-- ✓ is_test_account filter applied
-- ✓ Grain correct
-- ✓ Group-by correct

Step-by-step explanation.

1. Prompt A gives the model raw DDL. The model reconstructs the WAU logic every time; without the metric definition, it does not know about is_bot or is_test_account filters that live in tribal knowledge. The generated SQL is plausibly right but silently over-counts.
2. Prompt B gives the model a metric definition first. The model's job is dramatically simpler: pick the metric, pick the dimensions, pick the filter. The Semantic Layer handles the join, the filter, the grain.
3. The compiled SQL is deterministic and correct by construction. Every question that uses weekly_active_users gets the same filters. Every question that groups by plan gets the same plan column.
4. On a 100-prompt sample of WAU-adjacent questions, raw-table grounding got 62 percent right, metric-first grounding got 96 percent right. The 34-point delta is the value semantic-layer grounding creates on this metric alone.
5. The failure modes of metric-first grounding are different: if the user asks a question the metric cannot answer (e.g. "how many WAU by hour?", but the metric grain is week), the compiler returns a validation error rather than a wrong answer. That's a much better failure mode.

Output.

Metric	Raw-table grounding	Metric-first grounding
Correctness on WAU prompts (n=100)	62%	96%
Bot inclusion bug	frequent	never
Test-account inclusion bug	frequent	never
Grain confusion	occasional	never (fails loudly)
Metric owner surface area	8 SQL files	1 YAML

Rule of thumb. For every metric that appears on more than one dashboard, define it in the semantic layer and ground the text-to-SQL prompt on the metric name. The correctness delta is 20-40 points on that metric; the operational simplification is a single source of truth for the definition.

Worked example — pick a vendor: dbt vs LookML vs Cube

Detailed explanation. The three canonical semantic layers have different sweet spots. Picking the wrong one for your stack costs 3-6 months of migration pain. Walk through the decision for three canonical scenarios.

• Scenario 1. dbt-first analytics team with Snowflake warehouse and no BI standard.
• Scenario 2. Looker shop with 10 years of LookML models, moving into text-to-SQL.
• Scenario 3. Product engineering team embedding text-to-SQL in a customer-facing app.

Question. Match each scenario to the right semantic layer and justify.

Input.

Scenario	Existing stack	Consumer
dbt shop, no BI standard	dbt + Snowflake	text-to-SQL app
Looker shop	LookML + Snowflake	text-to-SQL app + Looker
Product embed	Custom app + Postgres	LLM-driven app

Code.

# Illustrative — pick the semantic layer
def pick_semantic_layer(dbt_native: bool, looker_native: bool,
                        embedded_in_app: bool) -> str:
    if looker_native:
        return "LookML — reuse the models you already have; Looker API for grounding"
    if dbt_native:
        return "dbt Semantic Layer — MetricFlow YAML alongside your dbt project"
    if embedded_in_app:
        return "Cube — REST/GraphQL/SQL API tuned for LLM grounding"
    return "dbt Semantic Layer (safe default)"

Step-by-step explanation.

1. Scenario 1 — dbt shop, no BI standard. Pick dbt Semantic Layer. MetricFlow YAML lives beside the dbt models; the JDBC/MCP endpoint is the query surface for the text-to-SQL app. No parallel semantic layer to maintain.
2. Scenario 2 — Looker shop. Pick LookML. Ten years of LookML models is a strategic asset; reusing them via the Looker API means the text-to-SQL feature ships without re-defining metrics. Everyone from the analytics team already reads LookML.
3. Scenario 3 — product embed. Pick Cube. Its REST/GraphQL/SQL API is the cleanest fit for an LLM app that also drives dashboards inside a customer-facing product. Cube's caching layer is a bonus for high-QPS embedded scenarios.
4. Do not mix. Running two semantic layers (dbt Semantic Layer for one team, LookML for another) is a metric-drift factory. Pick one; migrate the others over 6 months if you started with two.
5. If none of the three fits (e.g. real-time streaming metrics), consider a light-weight custom metric registry rather than adopting a full vendor. But recognise this is a temporary state; the vendors will absorb the use case within 12 months.

Output.

Scenario	Vendor	Why
dbt shop, no BI standard	dbt Semantic Layer	Zero parallel infrastructure
Looker shop	LookML	Reuse 10 years of models
Product embed	Cube	LLM-tuned SQL/REST/GraphQL API
Mixed (bad)	Pick one, migrate the rest	Drift is worse than migration cost

Rule of thumb. Match the semantic-layer vendor to the team that owns metric definitions. dbt for dbt-first, LookML for Looker-first, Cube for embed-first. Never run two; drift is the enemy.

Senior interview question on semantic-layer selection

A senior interviewer might ask: "Your team runs dbt on Snowflake, uses Looker for enterprise BI, and is about to ship a text-to-SQL Slack bot for the analytics team. Executives want to embed 'ask a question' into the customer app next quarter. Design the semantic-layer strategy — which vendors, what gets defined first, how the text-to-SQL grounding works, and how you protect against metric drift across the two consumers."

Solution Using dbt Semantic Layer as the source of truth + LookML as a downstream consumer + Cube for the product embed + a metric-drift monitor

# 1. dbt Semantic Layer as the canonical source
# metrics/canonical.yml
metrics:
  - name: net_revenue_daily
    type: simple
    type_params:
      measure: net_revenue
    filter: "{{ Dimension('is_refund') }} = false"

  - name: weekly_active_users
    type: simple
    type_params:
      measure: distinct_users
    filter: "{{ Dimension('is_bot') }} = false AND {{ Dimension('is_test_account') }} = false"

  - name: enterprise_churn_rate
    type: ratio
    type_params:
      numerator: enterprise_churned_users
      denominator: enterprise_users

# 2. LookML consumes dbt metrics via the LookML metric-import feature (or wrapper views)
# models/enterprise.model.lkml
explore: enterprise_dashboard {
  from: net_revenue_daily
  join: weekly_active_users {
    relationship: many_to_one
    sql_on: ${enterprise_dashboard.week} = ${weekly_active_users.week} ;;
  }
  # metrics reference the dbt-compiled tables; no re-definition
}

// 3. Cube for the product embed — references the same underlying tables
// cube/schema/NetRevenueDaily.js
cube('NetRevenueDaily', {
  sql: 'SELECT * FROM analytics.semantic.net_revenue_daily',
  measures: {
    revenue: { sql: 'net_revenue', type: 'sum' }
  },
  dimensions: {
    day:      { sql: 'day',      type: 'time'   },
    country:  { sql: 'country',  type: 'string' },
    product:  { sql: 'product',  type: 'string' }
  }
});

# 4. Metric-drift monitor — runs nightly
# tools/metric_drift_check.py
CANONICAL_METRICS = ["net_revenue_daily", "weekly_active_users", "enterprise_churn_rate"]

def sample_metric_across_consumers(metric: str) -> dict:
    """Query the same metric via each consumer path; expect identical numbers."""
    dbt_val    = dbt_sl.query(metrics=[metric], group_by=["week"], limit=1)
    looker_val = looker.run_look(saved_look_id=metric_dashboard_id[metric])
    cube_val   = cube.query(measures=[f"{metric}.revenue"], time_dim="day")
    return {"dbt": dbt_val, "looker": looker_val, "cube": cube_val}


for metric in CANONICAL_METRICS:
    vals = sample_metric_across_consumers(metric)
    if len(set(vals.values())) > 1:
        alert(f"metric drift on {metric}: {vals}")

Step-by-step trace.

Layer	Component	Purpose
Source of truth	dbt Semantic Layer YAML	one metric definition per business concept
BI consumer	LookML from: dbt metrics	Looker dashboards read the same numbers
App consumer	Cube reading analytics.semantic.*	product embed reads the same numbers
Text-to-SQL	Slack bot grounds on dbt SL directly	LLM prompt gets metric definitions
Text-to-SQL (product)	Product bot grounds on Cube's SQL API	LLM prompt gets Cube view schema
Metric-drift monitor	Nightly cross-consumer check	alerts on divergence

After rollout, net_revenue_daily is defined once in dbt SL and read by Looker, Cube, and the Slack bot. If any downstream consumer somehow computes a different number, the nightly drift monitor pages on-call before an executive spots the mismatch. Every new metric goes through the dbt YAML first; consumers cannot define their own without triggering the drift alert.

Output:

Consumer	Path	Grounded metric
Analytics Slack bot	dbt Semantic Layer MCP	net_revenue_daily
Executive dashboard	Looker → dbt SL	net_revenue_daily
Customer product embed	Cube → dbt-compiled tables	net_revenue_daily
Nightly drift monitor	queries all three	should always agree
Metric definition owner	analytics-eng team	1 YAML per metric

Why this works — concept by concept:

• dbt Semantic Layer as source of truth — MetricFlow YAML lives beside the dbt models, is versioned, is PR-reviewed, and has a queryable endpoint. Every downstream consumer reads through this layer, not around it.
• LookML as downstream, not competitor — LookML imports the dbt-compiled metric tables via from:. The BI dashboards are enriched by 10 years of LookML knowledge without duplicating metric definitions.
• Cube for the product embed — Cube's SQL/REST/GraphQL API is the cleanest fit for LLM-driven apps. It reads from the same physical tables analytics.semantic.* that dbt produces, so the metric numbers are identical.
• Text-to-SQL grounds on the closest semantic surface — Slack bot grounds on dbt SL directly; product bot grounds on Cube. Neither reads raw tables when a metric exists. The retriever prefers metric definitions in ranking.
• Nightly drift monitor — the safety net. Runs the same metric query through all three consumers; alerts on any divergence. In steady state it is silent; when it fires, it catches a real drift before users do.
• Cost — one metric-owner team (analytics-eng), 20-40 metrics defined in year one, a nightly cross-consumer drift check, and a semantic-layer-aware retriever in each text-to-SQL surface. The eliminated cost is the "revenue is $12.3M or $12.7M depending on the dashboard" war room, plus the model's freedom to redefine metrics on every prompt. Net O(1) per metric definition; O(consumers) for the drift monitor.

SQL
Topic — aggregation
SQL aggregation problems on metric definitions

Practice →

Design Topic — design Design problems on semantic-layer architectures

Practice →

---

5. The production loop — logs → eval → retrain → ship

Every failure becomes tomorrow's gold prompt — the loop compounds; a static prompt-and-model decays

The mental model in one line: the text-to-SQL production loop is the four-stage feedback cycle where every user request is logged with full context, human labellers promote failures to gold, the eval harness scores prompt-template and model changes offline, and a canary release gates any shipped change on live traffic — so the system compounds week over week rather than decaying as schema evolves, users get more ambitious, and new metrics land. The teams that ship without the loop get worse; the teams that ship with the loop get better.

The four loop stages.

• Capture. Every request logs a full trace: (request_id, user_id, timestamp, nl_question, retrieved_schema, retrieved_metrics, prompt_final, generated_sql, guardrail_verdicts[], executed_result_hash, latency_ms, user_feedback). The trace is the raw material for every downstream stage.
• Label. A weekly triage rotation reviews the escalated traces (thumbs-down, guardrail-rejected, high-latency, high-cost) and either (a) fixes the SQL by hand and promotes to gold, (b) files a schema-linking or semantic-layer bug, or (c) closes as user error with no gold row.
• Evaluate. Prompt-template changes, model version bumps, and semantic-layer additions run through the offline harness (H2 §2). CI enforces the regression gate.
• Ship. Any change that passes the harness gate goes to canary: 10 percent of traffic for one week, watched by live-quality metrics. If canary is clean, ramp to 100 percent; if canary regresses on any live metric, roll back.

The feedback-labelling contract.

• Explicit feedback. Thumbs up / thumbs down on every rendered result. Low friction (single click) so users actually give it.
• Implicit feedback. Did the user copy the SQL? Did they re-ask a rephrased question within 30 seconds (a failure signal)? Did they eventually give up and go to the dashboard?
• Corrected SQL. For thumbs-down traces, the triage rotation writes the correct SQL. This is the gold-set factory: escalated failure in, gold prompt out.
• Gold promotion. Once corrected SQL is validated by a second analyst, the trace becomes a new gold row. The gold set grows by 20-40 prompts per week in a busy deployment.

Prompt tuning vs fine-tuning vs schema-embedding refresh — three different loops at three different cadences.

• Prompt tuning. Editing the prompt template (instructions, examples, formatting). Cadence: 1-3 times per week. Cheap, fast, low-risk. Every change runs through the harness before ship.
• Fine-tuning. Training a model on your (question, sql) corpus. Cadence: 1-2 times per quarter. Expensive, slow, higher-risk (regression on out-of-domain prompts). Justify only when prompt tuning has plateaued.
• Schema-embedding refresh. Re-embedding the table + metric corpus after schema evolution. Cadence: nightly (incremental) or weekly (full). Automatic; watched for retriever regressions.

Canary release — the shipping mechanism.

• Traffic split. 10 percent of user requests go to the candidate (new prompt template or model). 90 percent stay on control.
• Live metrics. Per-cohort thumbs-up rate, guardrail-reject rate, p95 latency, mean cost per query. Alerts fire on any cohort divergence beyond 2 sigma.
• Duration. 5-7 days minimum, so weekday / weekend / peak-hour variance is captured. Shorter canaries let regressions ride.
• Ramp. Clean canary → 25 percent → 50 percent → 100 percent over 3 days. Never jump from 10 to 100.

Common interview probes on the production loop.

• "What do you log per request?" — required answer: full trace including retrieved context, generated SQL, guardrail verdicts, execution result hash, user feedback.
• "How do failures become gold prompts?" — weekly triage rotation writes corrected SQL; second analyst validates; promote to gold.
• "How often do you refresh the model?" — prompt tuning weekly, fine-tuning quarterly, embedding refresh nightly.
• "How do you ship changes safely?" — offline harness gate + 10-percent canary for a week + gradual ramp.

Worked example — the request-trace log schema

Detailed explanation. Every request emits one row into a text_to_sql_traces table. The schema is denormalised on purpose — grepping traces for "when did the model start hallucinating this column?" needs every field in one place.

• PK. request_id UUID.
• User. user_id, tenant_id, session_id.
• Inputs. nl_question, retrieved_tables[], retrieved_metrics[], prompt_final TEXT.
• Outputs. generated_sql, guardrail_verdicts JSONB, executed_result_hash, row_count, bytes_scanned.
• Feedback. user_feedback (thumbs_up, thumbs_down, null), feedback_at, corrected_sql TEXT.
• Perf. latency_ms, cost_credits, model_version, prompt_template_version.

Question. Write the log schema and the log-emit function.

Code.

-- The trace table (in the same warehouse as the app, or a dedicated logs DB)
CREATE TABLE analytics.text_to_sql_traces (
    request_id                UUID          PRIMARY KEY,
    user_id                   BIGINT        NOT NULL,
    tenant_id                 BIGINT        NOT NULL,
    session_id                UUID,
    created_at                TIMESTAMPTZ   NOT NULL DEFAULT clock_timestamp(),

    nl_question               TEXT          NOT NULL,
    retrieved_tables          TEXT[]        NOT NULL DEFAULT '{}',
    retrieved_metrics         TEXT[]        NOT NULL DEFAULT '{}',
    prompt_template_version   TEXT          NOT NULL,
    prompt_final              TEXT,

    generated_sql             TEXT,
    guardrail_verdicts        JSONB,
    executed_result_hash      TEXT,
    row_count                 BIGINT,
    bytes_scanned             BIGINT,
    latency_ms                INT,
    cost_credits              NUMERIC(10, 4),
    model_version             TEXT          NOT NULL,

    user_feedback             TEXT,          -- 'up', 'down', null
    feedback_at               TIMESTAMPTZ,
    corrected_sql             TEXT,
    triage_status             TEXT           -- 'pending', 'promoted', 'closed'
);

CREATE INDEX idx_traces_feedback ON analytics.text_to_sql_traces (user_feedback, created_at);
CREATE INDEX idx_traces_status   ON analytics.text_to_sql_traces (triage_status, created_at);

# app/log_trace.py
def log_trace(request_id, user_id, tenant_id, nl_question,
              retrieved_tables, retrieved_metrics,
              generated_sql, guardrail_trace,
              result_hash, latency_ms, cost_credits, model_version,
              prompt_template_version, session_id=None):
    with pg.cursor() as cur:
        cur.execute("""
            INSERT INTO analytics.text_to_sql_traces
            (request_id, user_id, tenant_id, session_id, nl_question,
             retrieved_tables, retrieved_metrics, prompt_template_version,
             prompt_final, generated_sql, guardrail_verdicts,
             executed_result_hash, latency_ms, cost_credits, model_version,
             triage_status)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'pending')
        """, (request_id, user_id, tenant_id, session_id, nl_question,
              retrieved_tables, retrieved_metrics, prompt_template_version,
              prompt_final, generated_sql, json.dumps(guardrail_trace),
              result_hash, latency_ms, cost_credits, model_version))

Step-by-step explanation.

1. request_id UUID is the durable handle. Every downstream artifact (feedback, triage decision, gold promotion) references this UUID so the full trace stays reconstructable.
2. retrieved_tables[] and retrieved_metrics[] capture what the schema-linking retriever picked. If the model hallucinates a column, the trace shows whether the retriever missed the relevant table (retriever bug) or the model went off-script (prompt bug).
3. prompt_template_version and model_version are the two dimensions along which quality can regress. Every trace is tagged, so post-hoc analysis can slice quality by version.
4. guardrail_verdicts JSONB stores the per-lane verdict from H2 §3. Post-hoc: "show me traces where lane 4 rejected but the user still gave a thumbs-down" surfaces situations where the cost-gate cap is too aggressive.
5. triage_status starts pending; the weekly triage rotation moves it to promoted (added to gold) or closed (user error or duplicate of existing gold). This turns triage into a queryable pipeline.

Output.

Query use case	Query pattern
Find all thumbs-down last week	WHERE user_feedback='down' AND feedback_at >= now() - INTERVAL '7 days'
Find pending triage	WHERE triage_status='pending' AND user_feedback='down'
Slice quality by model version	SELECT model_version, avg(CASE user_feedback WHEN 'up' THEN 1 ELSE 0 END) FROM traces GROUP BY 1
Detect prompt-template regression	... GROUP BY prompt_template_version

Rule of thumb. Log full traces on every request into a queryable warehouse table. Include retrieved context (not just the final SQL) so you can distinguish retriever bugs from prompt bugs. Index on user_feedback and triage_status for the on-call and triage queries.

Worked example — the CI-gated canary release

Detailed explanation. Every prompt-template change (or model bump, or semantic-layer addition) goes through the same rollout: offline harness gate → 10 percent canary for 5-7 days → gradual ramp → 100 percent. The rollout mechanism is a feature-flag on prompt_template_version and model_version per request.

• Feature flag. LaunchDarkly, Statsig, or a home-grown flag service.
• Traffic split. 10 percent → 25 percent → 50 percent → 100 percent over 3 days after canary passes.
• Live-quality metrics. thumbs-up rate, guardrail-reject rate, p95 latency, cost per query.
• Rollback trigger. Any metric divergence beyond 2 sigma vs control cohort.

Question. Write the canary rollout logic and the divergence alert.

Code.

# rollout/canary.py — per-request cohort assignment
from statsig import statsig, StatsigUser

def resolve_prompt_and_model(user_id: str) -> tuple[str, str]:
    user = StatsigUser(user_id=user_id)
    # Two independent experiments — prompt template + model
    prompt_variant = statsig.get_config(user, "text_to_sql_prompt").get("template_version", "v2.3")
    model_variant  = statsig.get_config(user, "text_to_sql_model").get("model_version", "gpt-4.1-2026-05")
    return prompt_variant, model_variant

-- rollout/divergence_check.sql — nightly canary vs control comparison
WITH cohorts AS (
    SELECT
        prompt_template_version,
        model_version,
        CASE user_feedback WHEN 'up' THEN 1 WHEN 'down' THEN 0 END AS thumb,
        latency_ms,
        cost_credits,
        (guardrail_verdicts->>'lane4_verdict' = 'reject_over_budget')::int AS cost_reject
    FROM analytics.text_to_sql_traces
    WHERE created_at >= now() - INTERVAL '7 days'
      AND user_feedback IS NOT NULL
),
rollup AS (
    SELECT prompt_template_version, model_version,
           avg(thumb)                                AS thumbs_up_rate,
           percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency,
           avg(cost_credits)                         AS avg_cost,
           avg(cost_reject)                          AS cost_reject_rate,
           count(*)                                  AS n
    FROM cohorts
    GROUP BY 1, 2
)
SELECT * FROM rollup ORDER BY prompt_template_version, model_version;

Step-by-step explanation.

1. resolve_prompt_and_model reads the per-user cohort assignment from Statsig. A stable-hash bucketing on user_id means a given user sees the same variant on repeat requests, which is essential for measurable per-user quality.
2. The Statsig experiment starts at 10 percent for the new variant; the rest fall through to the current default. Ramp-up is a config change in Statsig, not a code deploy.
3. The divergence check runs nightly. For each (prompt_template_version, model_version) cohort with at least ~500 requests, compute thumbs-up rate, p95 latency, average cost, and cost-reject rate.
4. On-call reviews the divergence report weekly during canary. Any 2-sigma-worse move on the candidate cohort blocks ramp-up. Any 2-sigma-better move accelerates ramp-up (with the same reviewer approval).
5. Rollback is a Statsig config change: set the candidate's traffic weight to 0 percent. In flight requests complete; new requests all route to control. Time-to-rollback: seconds.

Output.

prompt_template_version	model_version	thumbs_up_rate	p95_latency	avg_cost	n
v2.3 (control)	gpt-4.1-2026-05	0.84	4600 ms	$0.048	41,203
v2.4 (canary)	gpt-4.1-2026-05	0.87	4400 ms	$0.046	4,512

Rule of thumb. Every prompt-template change, model bump, and semantic-layer addition ships through 10 percent canary for 5-7 days, gated on live-quality metrics. Never bypass the canary — a change that passes the offline harness can still regress in ways the harness didn't cover.

Senior interview question on the production loop

A senior interviewer might ask: "Design the production loop for a text-to-SQL feature that has been live for 3 months and stalled at 82 percent user satisfaction. Cover the log-trace schema, the weekly triage rotation, the promotion-to-gold contract, the harness-gated release, and the canary rollout. How would you decide when to invest in fine-tuning vs when to keep iterating on prompts?"

Solution Using traced requests + weekly triage + gold promotion + harness gate + 10-percent canary + prompt-tuning-before-fine-tuning heuristic

# loop/weekly_triage.py — the triage rotation
def load_pending_traces(since):
    with pg.cursor() as cur:
        cur.execute("""
            SELECT request_id, nl_question, generated_sql,
                   retrieved_tables, retrieved_metrics,
                   guardrail_verdicts, user_feedback
            FROM   analytics.text_to_sql_traces
            WHERE  triage_status = 'pending'
              AND  user_feedback = 'down'
              AND  created_at >= %s
            ORDER  BY created_at DESC
            LIMIT  100
        """, (since,))
        return cur.fetchall()


def triage_one(trace) -> str:
    """Human writes corrected SQL, second reviewer validates."""
    print(trace["nl_question"])
    print("model wrote:", trace["generated_sql"])
    corrected = input("corrected SQL (or 'skip'): ")
    if corrected == "skip":
        return "closed"

    # Validate against harness — must execute and produce a plausible result
    if not corrected_validates(corrected):
        return "closed"

    promote_to_gold(trace["nl_question"], corrected, trace["retrieved_metrics"])
    return "promoted"


def promote_to_gold(question, sql, tags):
    """Write a new row into gold_set.jsonl."""
    result_hash = execute_and_hash(sql)
    with open("gold_set.jsonl", "a") as f:
        f.write(json.dumps({
            "question": question,
            "gold_sql": sql,
            "expected_hash": result_hash,
            "tags": tags + ["source:triage"]
        }) + "\n")

# loop/decision.py — when to prompt-tune vs fine-tune
def next_investment(recent_gold_promotions: int,
                    prompt_change_uplift_pp: float,
                    fine_tune_est_uplift_pp: float,
                    fine_tune_cost_weeks: int) -> str:
    if prompt_change_uplift_pp >= 1.0:
        return "keep prompt-tuning — you have room"
    if recent_gold_promotions < 20:
        return "grow the gold set first — you can't measure fine-tune uplift"
    if fine_tune_est_uplift_pp / fine_tune_cost_weeks < 0.5:
        return "prompt-tune; fine-tune ROI too low"
    return "invest in fine-tuning"

Step-by-step trace.

Loop stage	Mechanism	Cadence
Capture	request trace → warehouse table	every request
Label	weekly triage rotation	1x/week
Promote	corrected SQL → gold_set.jsonl	continuous within triage
Evaluate	harness on prompt PR / model bump	CI on every relevant PR
Canary ship	10% cohort for 5-7 days	per change
Ramp	10 → 25 → 50 → 100%	3 days
Rollback	Statsig weight → 0%	seconds

After 4 weeks of running the loop on a stalled 82 percent system, execution accuracy on the gold set climbs from 82 to 87 percent driven purely by prompt-template iteration and gold-set expansion. Only after prompt tuning plateaus (weekly uplift under 0.5 pp) does the team consider fine-tuning, and only if the fine-tune ROI (estimated uplift divided by weeks of engineering) beats 0.5 pp/week.

Output:

Week	Gold set size	Prompt template	Exec acc	Note
Baseline	220	v1.0	82%	stalled
Week 1	260	v1.1	83%	few-shot examples added
Week 2	300	v1.2	85%	metric-first instruction
Week 3	340	v1.3	86%	retriever K=12 → 15
Week 4	380	v1.4	87%	canary passed, ramped 100%

Why this works — concept by concept:

• Full-trace logging — capture prompt, retrieved context, SQL, guardrail verdicts, execution hash, and feedback in one row. Every subsequent stage grep-queries this table; skimping on fields is skimping on debuggability.
• Weekly triage rotation — humans in the loop. Every thumbs-down that reveals a real gap becomes a gold prompt within the same week. The gold set grows organically toward what users actually ask.
• Prompt-tuning before fine-tuning — prompt changes are cheap and reversible; fine-tuning is expensive and hard to reverse. The heuristic "keep prompt-tuning while it yields at least 1 pp per change" prevents premature model investment.
• Harness-gated ship + canary — offline harness catches obvious regressions; canary catches live-behavior regressions the harness didn't cover. Both are non-negotiable.
• Cost — one triage-rotation engineer at ~30 percent time; one CI harness run per relevant PR at ~$5; one canary experiment per change at zero incremental cost. The eliminated cost is quality decay in production and the executive-visible regressions that decay causes. Net O(1) per change; O(traffic) per canary day.

SQL
Topic — window-functions
SQL window-function problems for trace analysis

Practice →

API Integration
Topic — api-integration
API-integration problems for LLM feedback loops

Practice →

---

Cheat sheet — text-to-SQL production recipes

• The four failure classes. Every text-to-sql in production failure sorts into schema hallucination (invented tables/columns), join drift (wrong keys or wrong join type), metric mis-definition (wrong aggregation or missing filter), or unsafe execution (destructive DML, runaway scan). Every mitigation is on-axis: schema-link retriever kills hallucination; join graph or semantic-layer joins kill drift; semantic-layer metric grounding kills mis-definition; four-lane guardrails kill unsafe execution. Miss any axis and the failure class stays alive.
• The eval-harness ship gate. Execution accuracy on a 500-prompt private gold set is the release gate; exact-match is a lower bound; semantic-diff via EXPLAIN plan hashing is the cheap pre-filter. Cascade: exact-match (microseconds) → plan-hash (milliseconds) → execution (seconds + $) so the average prompt resolves in a cheap tier. Zero-copy Snowflake CLONE snapshots make results deterministic; a dedicated X-SMALL warehouse with a resource-monitor cap keeps a full harness run under $5.
• The four guardrail lanes. (1) Schema-linking retriever prunes prompt to 15 tables + FK neighbours (drops hallucination 70-90%). (2) SQLGlot parse + identifier validation against a cached INFORMATION_SCHEMA snapshot rejects any unknown table or column. (3) Warehouse dry-run cost estimator (EXPLAIN USING JSON in Snowflake, dryRun in BigQuery) rejects any query above the per-tenant scanned-bytes budget. (4) Safety allow-list — only SELECT/WITH on a read-only role.
• The read-only + row-limit + column-mask trio. Service account has SELECT only (no INSERT/UPDATE/DELETE/CREATE/DROP). Every generated SQL is rewritten with a mandatory LIMIT N. Every PII column has a warehouse-level MASKING POLICY. Belt-and-braces: even if a guardrail lane misfires, the warehouse's own permission check and mask enforcement provides a second defense.
• Schema-linking retriever recipe. One embedded document per table (table_name + description + columns[]); pgvector or Qdrant index; top-K by cosine; expand one hop along foreign keys; cap at 15 tables and 300 columns; format as annotated CREATE TABLE DDL for the prompt. Refresh embeddings on schema-evolution events; cache popular questions for zero-latency repeats.
• SQLGlot parse validator template. tree = sqlglot.parse_one(sql, read="snowflake") → tree.find_all(exp.Table) to enumerate tables → tree.find_all(exp.Column) to enumerate columns → look up in a cached INFORMATION_SCHEMA snapshot. Distinguish unknown_table, unknown_column, ambiguous_column. Feed each failure kind back to the model as a targeted retry hint. Latency budget: 5 ms end-to-end.
• Semantic-layer contract. Metrics defined once in dbt Semantic Layer (or LookML, or Cube). Each metric ships measure + dimensions + filter + grain. Text-to-SQL retriever indexes metric definitions alongside table docs. Prompt template says "PREFER defined metrics over raw tables." Compiled SQL from the semantic layer inherits joins/filters/grain — the model never re-derives.
• Vendor decision matrix. dbt-first team → dbt Semantic Layer (MetricFlow YAML). Looker-first team → LookML (reuse the models you have). Product-embed team → Cube (LLM-tuned SQL/REST/GraphQL API). Never run two — metric drift is worse than migration cost. Add a nightly cross-consumer drift monitor when the same metric surfaces through multiple layers.
• Request-trace log schema. text_to_sql_traces(request_id, user_id, tenant_id, nl_question, retrieved_tables[], retrieved_metrics[], prompt_template_version, generated_sql, guardrail_verdicts JSONB, executed_result_hash, row_count, bytes_scanned, latency_ms, cost_credits, model_version, user_feedback, corrected_sql, triage_status). Index on (user_feedback, created_at) and (triage_status, created_at) for the two hot query paths.
• Weekly triage rotation. Load pending thumbs-down traces → human writes corrected SQL → second analyst validates → promote to gold with new expected_hash. Target 20-40 gold promotions per week in busy deployments. Every failed prompt becomes tomorrow's regression test.
• Prompt-tune vs fine-tune heuristic. Keep prompt-tuning while per-change uplift stays above 1 pp. Fine-tune only when (a) prompt-tuning has plateaued for 2+ weeks, (b) gold set exceeds 500 prompts, and (c) fine-tune estimated uplift beats 0.5 pp per engineer-week. Fine-tuning is expensive to reverse; prompt tuning is a config change.
• CI-gated canary release. Every change (prompt template, model bump, semantic-layer addition) runs through the harness with a -2 pp regression gate. On pass, ship to 10 percent traffic via Statsig / LaunchDarkly for 5-7 days, watched by thumbs-up rate + guardrail-reject rate + p95 latency + cost per query. Clean canary → ramp 10 → 25 → 50 → 100 over 3 days. Rollback is a config change.
• Cost bounding recipe. Per-query cap: 5 GB scanned (Snowflake). Per-tenant daily cap: 500 GB scanned. Warehouse resource monitor: hard-stop at $500/day on the bot warehouse. STATEMENT_TIMEOUT_IN_SECONDS = 60 on the bot session. Result-set client cap: 10,000 rows. Combined, these bound any single query at $0.50 and any tenant-day at $50 in a well-tuned setup.
• Failure semantics reminder. Model unavailable → request fails fast with "model unavailable, try again." Guardrail rejection → return actionable message ("your query would scan 47 GB; try adding a date filter"). Persistent hallucination (3 retries) → escalate with "I couldn't answer that reliably" — the honest failure mode. Never let a low-confidence answer render as if it were high-confidence.

Frequently asked questions

What is text-to-SQL in production and why is it different from a demo?

text-to-sql in production is a full analytics feature that lets end users ask questions in natural language against a real warehouse, with correctness, safety, cost, and latency guarantees appropriate for a customer-facing or internal-employee product. It differs from a demo in every dimension that matters: the demo uses a toy schema (5 tables); production hits a 400-table warehouse. The demo trusts the model output; production runs every candidate SQL through a four-lane guardrail stack (schema linking, parse validation, dry-run cost, safety allow-list). The demo has no eval; production gates every change through a private gold set of 200-2000 (question, gold_sql, expected_result) triples plus a canary release. The demo uses raw DDL; production grounds on a semantic layer (dbt Semantic Layer, LookML, Cube) so metrics like revenue and MAU are defined once and never re-derived by the model. The demo is a screenshot; production is a system.

Execution accuracy vs exact-match — which is the right metric for my team?

Execution accuracy is the ship gate; exact-match is a diagnostic. Execution accuracy runs the candidate SQL and the gold SQL against a frozen snapshot of the warehouse, canonicalises both result sets (sort rows, sort columns, cast types), and compares them — this measures what the user actually experiences. Exact-match canonicalises the SQL strings (whitespace, aliases, keyword casing) and compares them — fast, cheap, but blind to semantically equivalent rewrites (SELECT a, b FROM t vs SELECT b, a FROM t are exact-match different but result-set identical). Ship on execution accuracy on a private gold set (target 85 percent for internal analytics, 90 percent for customer-facing self-serve). Track exact-match as a lower bound and semantic-diff via EXPLAIN plan hashing as a cheap pre-filter, but never let exact-match alone decide releases. Every text-to-SQL paper and every serious production deployment converges on this hierarchy: exact-match ≤ semantic-diff ≤ execution accuracy, with execution accuracy the release contract.

What guardrails must every text-to-SQL system ship on day one?

Four independent lanes, non-negotiable. Schema linking — an embedding-based retriever that prunes the prompt to 5-15 relevant tables plus their foreign-key neighbours, so the model does not hallucinate columns that aren't in the prompt. Parse validation — SQLGlot parses the generated SQL, enumerates every table and column reference, and checks each against a cached INFORMATION_SCHEMA snapshot; unknown identifiers are rejected before the warehouse sees the query. Warehouse dry-run cost — use the native estimator (EXPLAIN USING JSON in Snowflake, dryRun=True in BigQuery, EXPLAIN COST in Databricks) to bound scan bytes per query at 5 GB and per-tenant daily at 500 GB. Safety allow-list — only SELECT / WITH statements are executed, on a service account with SELECT-only permissions. Layer these with a mandatory LIMIT N, warehouse-level MASKING POLICY on PII columns, and a resource-monitor hard-stop at a daily dollar cap. The lanes are independent so a fault on one does not cascade; the read-only role plus statement-type check is belt-and-braces safety against destructive DML.

Semantic layer or raw-schema grounding — which do I need?

Semantic layer for every metric worth naming; raw-schema for everything else. Metric mis-definition is one of the four failure classes and it is the failure that most damages user trust — when revenue disagrees between two dashboards, everyone doubts everything. Defining metrics once in semantic layer grounding (dbt Semantic Layer, LookML, or Cube) with measure + dimensions + filter + grain, and prompting the text-to-SQL model to prefer defined metrics over raw tables, collapses this failure class. On a WAU-adjacent 100-prompt sample, raw-table grounding hit 62 percent correctness while metric-first grounding hit 96 percent — a 34-point delta on that one metric alone. For questions that don't map to a defined metric, fall back to raw-schema grounding with a lowered confidence signal to the user. Vendor pick: dbt Semantic Layer for dbt-first teams, LookML for Looker-first teams, Cube for embed-first teams. Never run two — metric drift across parallel semantic layers is worse than the migration cost to consolidate.

Should I evaluate on Spider, BIRD, or a private gold set?

Private gold set is the release gate; Spider and BIRD are development-time sanity checks. spider benchmark (200 databases, cross-domain, tiny schemas mostly under 20 tables) tells you the model can do SQL at all — useful for model selection and prompt-template scaffolding but useless as a production gate against your 400-table Snowflake warehouse. bird-sql (larger schemas, dirty values, external knowledge) is closer to reality but still not your schema; the gap between "trained on BIRD" and "actually works on your warehouse" is typically 15 percentage points of execution accuracy. Build a private gold set of 200 prompts on day one, 500 within the quarter, and 2000 within the year, targeting your top-10 query patterns across your top-10 tables. Every production incident and every escalated user feedback becomes a new gold row. Version the gold set in git as JSONL; PR-review changes; rotate 20 percent quarterly; keep an adversarial hold-out partition (never used in prompts or fine-tunes) as the overfit detector. Ship-gate release on private-gold execution accuracy; report Spider/BIRD scores for benchmark visibility only.

How often should I refresh the model, the prompt, and the schema embeddings?

Three loops at three different cadences. Prompt tuning every 1-3 weeks: cheap, fast, low-risk, gated by the eval harness. Most quality gains in a mature deployment come from prompt tuning, not model changes. Schema-embedding refresh nightly (incremental) or weekly (full): triggered by schema-evolution events from your warehouse catalog; watched by a retriever-regression metric on the eval set. Fine-tuning once or twice per quarter, and only after prompt tuning has plateaued (weekly uplift below 0.5 pp for 2+ weeks) AND the gold set exceeds 500 prompts AND the estimated fine-tune uplift beats 0.5 pp per engineer-week. Fine-tuning is expensive to reverse; prompt tuning is a config change. Every change of any kind runs through the offline harness with a -2 pp regression gate, then a 10-percent canary for 5-7 days, then ramp 10 → 25 → 50 → 100 percent over 3 days. Rollback is a Statsig / LaunchDarkly weight change measured in seconds. Skip the loop for any of these and the system silently decays as your warehouse schema evolves and your users get more ambitious.

Practice on PipeCode

• Drill the SQL practice library → for the query patterns text-to-SQL systems must generate reliably — joins, aggregations, window functions, subqueries.
• Rehearse on the SQL-generation practice library → for text-to-SQL, NL2SQL, and prompt-driven query construction scenarios.
• Sharpen the safety axis with the data-validation practice library → for result-set diff, schema-check, and guardrail patterns.
• Practice the design axis with the design practice library → for LLM harness, semantic-layer, and eval-loop architecture prompts.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the four-workstream production plan against real graded inputs.

Lock in text-to-SQL production muscle memory

Docs explain the API. PipeCode drills explain the harness — when execution accuracy is the truth vs. when exact-match is a lower bound, when the schema-linking retriever earns its keep, when semantic-layer grounding kills metric drift, when the production loop turns yesterday's incident into tomorrow's regression test. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data platform engineers actually face when shipping text-to-SQL.

Practice SQL-generation problems →
Practice design problems →