Text-to-SQL that can't leak data it shouldn't

Text-to-SQL demos well. Wire a model to a warehouse, it turns English into SQL, and on a clean schema it looks like magic. Point it at a real warehouse and it falls over, and the size of that gap is now measured instead of anecdotal.

Spider 2.0 rebuilt the benchmark around actual enterprise schemas from Snowflake and BigQuery. GPT-4o answers about 86% of the classic single-database questions but about 10% of these; o1-preview lands near 17%. The schemas are the hard part, and no prompt fixes a schema.

The failures are also silent. A query that reads the wrong one of two similar tables does not raise. It returns a number of the right type, in the right shape, that a dashboard renders without complaint, and governance sharpens that, because now a wrong number can also be a number the asker was never cleared to see.

Almost every failure worth writing about here lives at a seam: a place where one layer’s contract silently decides another layer’s outcome, and the two layers answer to different people. This post is built around five of them on BigQuery. The one worth stating first: a dry run is two controls, cost and permission, and most self-correcting agents wire up only the cost half.

Who each part runs as

A question passes through retrieval, generation, and a dry run before a single row is read. Whatever fails goes back to the model with the error attached. The model either names a governed metric or writes raw SQL, and both routes end at the same gate.

flowchart TD
  U[User identity] --> C[Downscoped client<br/>only this user's grants]
  C --> R[Retrieval<br/>tables the caller may read]
  R -->|nothing readable| AR[Access request]
  R --> M[LLM]
  M -.names a metric.-> S[Semantic layer<br/>governed joins and grain]
  M -.or writes SQL.-> G[Dry run<br/>cost + permission]
  C --> S
  C --> G
  G -->|fails cap rounds| H[Escalate to human]
  G --> X[Execution]
  S --> X
  M -.never holds credentials.-> C
  style C fill:#0f766e22,stroke:#0f766e,stroke-width:2px
  style M fill:#dc262622,stroke:#dc2626,stroke-width:2px

The decision that carries the most weight is which identity each box holds. Every BigQuery call in the pipeline uses a per-request client built from the asking user’s downscoped credentials, so there is no shared service account anywhere in the path. The model sits outside that boundary. It can be jailbroken, it can hallucinate a table name, and a prompt-injection string buried in a question can talk it into writing SELECT * FROM payroll. That achieves nothing across users: retrieval never offered payroll to this person, the dry run under their credentials returns a 403, and the denial goes back into the loop as text. The model cannot escalate privileges by being persuaded, because it never holds any.

The limit of that property is narrower than it sounds, and worth stating plainly. It stops a user reaching data other users can see, but it does nothing about a user being talked into dumping a table they legitimately may read into a place where people without that grant can read it. More on that near the end.

Retrieval scoped to what the asker may see

Schema linking decides which tables reach the prompt. Ours has 312 tables, and even when they fit in the context window, every irrelevant one is a distractor the model might link to by mistake.

The governed version restricts the candidate pool to what this user may read before ranking, so a forbidden table never becomes a candidate and cannot appear in a join.

Determining that is where the obvious answer is wrong. INFORMATION_SCHEMA.OBJECT_PRIVILEGES looks like the right view, but it shows only the bindings set explicitly on an object. Grants inherited from the project or folder do not appear, and neither do group memberships, so a user who has access through group:analytics@ looks unauthorised. In most enterprises that is how the majority of access is held, so a filter built on that view excludes the normal case and admits the exception.

The call that answers the real question is tables.testIamPermissions, which returns the permissions the caller holds, resolved through the whole hierarchy including inherited roles and group expansion. Because retrieval already runs as the user, this is a direct question the user asks about themselves. Build retrieval on a shared client instead and you have to introspect grants about a person with Policy Analyzer, which means handing that service account the power to enumerate everyone’s access.

import asyncio
from cachetools import TTLCache
from google.cloud import bigquery
from rank_bm25 import BM25Okapi
from sentence_transformers import CrossEncoder

reranker = CrossEncoder("cross-encoder/ms-marco-MiniLM-L-6-v2")
NEEDED = ["bigquery.tables.getData"]
_readable_cache: TTLCache = TTLCache(maxsize=4096, ttl=300)   # per-user, 5 min

async def readable(bq: bigquery.Client, user: str, fq_names: list[str]) -> set[str]:
    """Tables the calling credentials may read. test_iam_permissions resolves
    inherited and group-derived grants that INFORMATION_SCHEMA.OBJECT_PRIVILEGES
    omits. There is no batch form, so the calls fan out concurrently and the
    result is cached per user: a table check per catalog entry on every question
    would be hundreds of blocking round-trips, which is a latency and quota
    problem before it is a correctness one."""
    if user in _readable_cache:
        return _readable_cache[user]
    async def ok(fq: str) -> str | None:                 # fq = "project.dataset.table"
        granted = await asyncio.to_thread(bq.test_iam_permissions, fq, NEEDED)
        return fq if set(NEEDED) <= set(granted.get("permissions", [])) else None
    results = await asyncio.gather(*(ok(fq) for fq in fq_names))
    allowed = {fq for fq in results if fq}
    _readable_cache[user] = allowed
    return allowed

async def retrieve_tables(question: str, bq: bigquery.Client, user: str, k: int = 8):
    allowed = await readable(bq, user, CATALOG_FQ_NAMES)
    docs = [d for d in CATALOG_DOCS if d.fq_name in allowed]
    if not docs:
        return []
    corpus = [d.text.lower().split() for d in docs]      # lowercase: EMEA must match emea
    bm25 = BM25Okapi(corpus)
    scored = sorted(zip(docs, bm25.get_scores(question.lower().split())),
                    key=lambda p: -p[1])
    shortlist = [d for d, _ in scored[:50]]
    ce_scores = reranker.predict([(question, d.summary) for d in shortlist])
    return [d for _, d in sorted(zip(ce_scores, shortlist), key=lambda p: -p[0])][:k]

The reranker detail is the one that costs quality silently. A cross-encoder has a 512-token limit, so on a table with hundreds of columns it reads the first few dozen and truncates the rest without saying so. Rank on a short catalog summary and not the raw DDL, which is why d.summary feeds the reranker.

The cache TTL is not only a latency knob. It is also the window in which a revoked grant still looks live, which is why it is short. The control that actually blocks a leak is the dry run under the current identity, and never this cache.

Two properties of testIamPermissions decide how much weight it can carry. It is documented as a helper for building permission-aware UIs, with an explicit warning against treating it as authorization, and it may fail open. And on a resource that does not exist it returns an empty permission set instead of a 404, so retrieval cannot distinguish a table the caller may not read from a table that is not there.

Both are fine in a filter whose job is keeping forbidden tables out of the prompt. Neither belongs in the thing you rely on to stop a leak, which is why the dry run under the same identity does that job. Retrieval decides what the model is allowed to think about; the dry run decides what runs.

Scoping the pool also makes retrieval measurably better, because it removes distractor tables before ranking. On our eval set, narrowing the pool from 312 tables to the 38 a typical analyst may read lifted recall at every cutoff.

A denser pool gives the reranker fewer near-miss tables to confuse, so the same model links the right one more often. When a safety control also improves your accuracy numbers, it survives the roadmap meeting nobody else’s control survives.

The dry run is two controls

Most self-correcting SQL agents use the database to check whether a query is valid. That is half of what the call gives you.

A BigQuery dry run reads no rows and consumes no slots. It returns the byte estimate, and it performs the access check as the calling credentials, so one call answers both what will this cost and is this person allowed to touch what it references. The permission half is the one people leave on the table, and it is free.

from dataclasses import dataclass
from google.cloud import bigquery
from google.api_core.exceptions import BadRequest, Forbidden, NotFound

GIB = 1024 ** 3

@dataclass
class GateResult:
    ok: bool
    est_bytes: int = 0
    reason: str = ""

def cost_gate(bq: bigquery.Client, sql: str, budget_bytes: int = 50 * GIB) -> GateResult:
    cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
    try:
        job = bq.query(sql, job_config=cfg)
    except Forbidden as e:                 # 403: no grant on a referenced object
        return GateResult(False, reason=f"permission denied: {e.message}")
    except NotFound as e:                  # 404: table or dataset does not exist
        return GateResult(False, reason=f"unknown table: {e.message}")
    except BadRequest as e:                # 400: syntax, or unknown column
        return GateResult(False, reason=f"invalid query: {e.message}")

    est = job.total_bytes_processed
    if est > budget_bytes:
        return GateResult(False, reason=f"estimated {est / GIB:.1f} GiB scanned "
                          f"> budget {budget_bytes / GIB:.0f} GiB; add a partition filter")
    return GateResult(True, est_bytes=est)

use_query_cache=False is not incidental. With the cache on, a dry run can report a cache hit with zero estimated bytes, and a budget gate reading that number waves the query through no matter what it would really scan. There is a second zero with the same effect: a dry run over an external data source may report a lower bound of nothing at all, because how much the federated side reads is not known until the query finishes, and running it still costs money. A gate that reads zero as cheap has two documented ways to be wrong.

The exception types matter for a reason specific to this application. A hallucinated table name raises NotFound where a reader expects BadRequest, and a wrong schema link is the single most common failure class in text-to-SQL. Catch only BadRequest and your correction loop dies on the exact error it exists to repair.

That split has a consequence. Forbidden and NotFound are the distinction retrieval refused to make, and the dry run hands it back: a name that returns 403 exists and is off limits, a name that returns 404 does not exist. The loop passes that verdict to the model as text, so every table name it guesses at is an existence probe answered under the user’s own credentials, and the answer stays in the context window for the rest of the session.

What the dry run will not save you from

Here the obvious reading of the docs runs backwards, and it took me a while to see it.

The intuition from Postgres is that a planner estimate can be badly wrong in the expensive direction, because cardinality estimation on skewed correlated data is hard. That is not how BigQuery’s number is produced. BigQuery’s dry run is metadata arithmetic over the referenced columns and partitions, documented as an upper bound: on clustered tables the actual bytes billed can come out lower, because block pruning happens during execution, after the estimate is fixed. The estimate does not blow past itself.

Which is reassuring right until you reach for a backstop. The natural instinct is to put maximum_bytes_billed under the soft gate, so a bad estimate gets killed by the engine rather than by a budget alert the next morning. It doesn’t work that way. When the cap is set, the bytes a query will read are estimated before execution, and if that estimate exceeds the cap the query fails. It is enforced against the same pre-execution number the dry run just returned. If the dry run says 40 GiB and your cap is 50 GiB, the cap can never fire. You haven’t added a second control; you have run the first one twice.

On clustered tables it is worse than redundant. Because the estimate is an upper bound while the actual scan can be lower, a maximum_bytes_billed cap can fail a query whose real cost would have sat comfortably under it. The backstop you added for safety becomes a source of false denials on exactly the tables you cluster for performance.

There is also a class of table where the number is not imprecise but withheld. BigQuery hides sensitive statistics on queries against a table with row-level security: bytes processed, bytes billed, partitions read, query plan stages. The reason, given in the same document, is that those numbers are a side channel into the rows you were filtered away from. The consequence lands on the cost side. total_bytes_billed and total_bytes_processed come back empty in INFORMATION_SCHEMA.JOBS for those jobs, so the scan distribution you monitor is a distribution over your least governed tables, and a byte gate is asking the warehouse for a number it has classified as sensitive. The security team added the row policy, the platform team owns the budget, and neither review mentions the other.

The remediation weakens on those tables too. A row access policy filter does not participate in partition or cluster pruning, so the estimate cannot be brought down by adding a partition filter, which is the correction the gate’s own error message asks the model to make.

The exposure that a byte cap cannot touch is time-of-check to time-of-use. The dry run describes the table as it was; execution runs against it as it is. On an append-heavy events table with a busy streaming buffer, minutes of drift is ordinary, and a correction loop that dry-runs four times before executing widens that window each round. The controls that bound spend independent of your estimate are the ones the platform enforces, not the ones your process computes: a custom cost quota at the project or user level, a reservation that caps slots, and job_timeout_ms. The quota is the one to set first, because it is the only one that fails closed on a number you did not compute yourself.

The bytes are not where you think

On a columnar engine the bytes are a property of the columns and partitions touched. The size of the table never enters, and neither does the number of rows you get back. Both halves catch people.

Take our events table: 4.2 billion rows, about 1.4 TB, date-partitioned.

SELECT COUNT(*) FROM fct_events WHERE device_type = 'mobile';

This looks like a full scan and is not. COUNT(*) needs no column values, so the only column read is the one in the filter. device_type is a STRING, billed at 2 bytes plus its length, so at roughly 8 bytes per row this scans about 34 GB. At the on-demand rate of $6.25 per TiB that is around 19 cents, and it sits well under a 50 GiB budget. The gate does not fire, and it should not.

Now the one that does bite:

SELECT * FROM fct_events WHERE device_type = 'mobile' LIMIT 100;

To a reader this looks cheaper than the first query, but it is more than forty times more expensive, close to $8. SELECT * reads every column, and LIMIT does not reduce bytes scanned at all: BigQuery scans the matching columns and partitions, then throws away everything past the hundredth row. LIMIT 100 on an unpartitioned terabyte still bills a terabyte. The row count of your result has almost nothing to do with the cost of producing it.

This is the failure to build the gate around, because it is exactly what a model writes when a user says show me some examples. It is also what an analyst writes while exploring, which is why the estimate belongs in front of every query, including all the ones that look harmless.

The distribution is the argument for a gate over a review process. Almost everything is cheap, the entire bill lives in a tail that is a few percent of queries, and that tail is not visually distinctive in the SQL. The median query scans under a gigabyte; the ones that would blow the budget look about the same on the page.

Metrics the model does not invent

The other silent failure is arithmetic. Asked for net revenue in a category, a model will join an order-grain table to a line-item-grain table and sum an order-level column, counting each order once per line item. The result is inflated by the average fan-out and looks plausible to anyone who does not already know the answer. The fix is that the model does not get to write revenue arithmetic at all: a semantic layer defines the metric once, with the right joins and grain, reviewed by someone in finance, and the model’s job shrinks to naming a metric and some dimensions.

The mechanism that prevents fan-out gets misattributed to the join engine, and it is worth pinning down. What stops the double-count is where each additive measure is defined. Every component of net revenue is a SUM on the line-item cube, at the grain where those columns live, so under a one-to-many join each line row still counts once. The declared one_to_many relationship tells Cube the join is safe to make, but it does not rewrite an order-grain measure into a subquery. Reference a SUM defined on the order cube across this same join and it fans out exactly as the hand-written SQL did. Grain placement is the control:

cubes:
  - name: order_lines
    sql_table: analytics.fct_order_lines
    measures:
      - name: gross_revenue
        type: sum
        sql: line_amount
      - name: refunds
        type: sum
        sql: refund_amount
      - name: discounts
        type: sum
        sql: discount_amount
      - name: tax
        type: sum
        sql: tax_amount

  - name: orders
    sql_table: analytics.fct_orders
    joins:
      - name: order_lines
        relationship: one_to_many          # safe join; measures still sum at their own grain
        sql: "{CUBE}.order_id = {order_lines}.order_id"
    measures:
      - name: net_revenue
        type: number
        sql: "{order_lines.gross_revenue} - {order_lines.refunds} - {order_lines.discounts} - {order_lines.tax}"
    dimensions:
      - name: region
        type: string
        sql: region
      - name: order_date
        type: time
        sql: order_date

The model then emits a structured query naming orders.net_revenue by region, and the layer compiles the SQL. The user’s JWT rides in the Authorization header and carries the security context, so row-level rules apply on top of whatever the model asked for. (Cube’s default checkAuth reads a bearer token there; if you run a custom checkAuth that expects the raw JWT, drop the Bearer prefix.)

import httpx, time

def run_semantic_query(user_jwt: str, query: dict, timeout_s: float = 60.0) -> list[dict]:
    deadline = time.monotonic() + timeout_s
    while True:
        resp = httpx.post(
            f"{CUBE_URL}/cubejs-api/v1/load",
            headers={"Authorization": f"Bearer {user_jwt}"},
            json={"query": query},
            timeout=30.0,
        )
        resp.raise_for_status()
        body = resp.json()
        # Cube answers a still-computing query with HTTP 200 and
        # {"error": "Continue wait"}. The client is expected to re-POST the
        # identical query until data comes back. Treating this 200 as the
        # result is the standard way to KeyError in production.
        if body.get("error") == "Continue wait":
            if time.monotonic() > deadline:
                raise TimeoutError("semantic layer still computing")
            continue
        return body["data"]

Not every question maps to a governed metric, and for the rest the model still writes SQL. But for the numbers a VP reads off a board deck, binding to the layer moves the failure from hidden arithmetic to a visible metric choice, which a human reviewer can check.

Masking belongs in the database

Scoped retrieval keeps forbidden tables out of the prompt. Column masking is the next layer down: tables a user may see where individual columns must be hashed, truncated, or nulled for their role. Do this in the warehouse, with policy tags and column-level access control, so the mask holds no matter who queries or through which client.

Then work out what it does to your answers, because this is the one control here that fails silently by design. Masking takes precedence over the other operations in a query, so the mask is what the rest of the SQL sees. A masked reader filtering on email = 'jane.doe@example.com' compares against the mask and gets nothing back, which the docs spell out for search: with a hash rule you are expected to put the hash in your predicate. The default rule substitutes a type-shaped placeholder, 0 for INTEGER, empty string for STRING, 1970-01-01 for DATE. So average salary by region comes back as zeroes, earliest signup comes back as 1970, both queries are permitted, both are under budget, and neither raises. A security control working exactly as specified has produced a confident wrong number.

There is a flag for it. A finished query job carries dataMaskingStatistics and rowLevelSecurityStatistics in its job statistics, each a single boolean saying whether the result you are holding was masked or filtered. The Python client exposes neither, so read them off the raw job resource and carry them with the answer.

The denial path leaks in the other direction. A user without the fine-grained reader role who runs SELECT * gets an error listing the columns they cannot access and naming the policy tag they would need, and the documented remedy is to rewrite it as SELECT * EXCEPT(ssn). One rejected query teaches the model that the column exists, what it is called, and which taxonomy protects it; the retry succeeds; the job history keeps the successful version. The more helpful the refusal, the more of the schema it hands over.

That is also most of the argument against masking in your own code. The tempting version parses the generated query and rewrites sensitive columns before execution, and it looks like twenty lines of sqlglot. Generated SQL is almost always aliased, so a column reference gives you i.national_id and sqlglot reports its table as i, with dim_customer_identity nowhere in the tree. A rule keyed on real table names matches nothing, and a masking rule that matches nothing lets everything through. Wrapping a column in a function and aliasing it is also legal only in the SELECT projection:

COUNT(DISTINCT SHA256(i.national_id) AS national_id)   -- ParseError
WHERE NULL AS ssn = '123'                              -- ParseError

So a correct rewriter qualifies the tree against a live catalog, walks only the projection expressions, replaces in place without an alias everywhere else, and still has to reason about types, because BigQuery’s SHA256 takes STRING or BYTES and returns BYTES, which breaks a join key the moment it masks one. That is a real piece of software in the path of every query, whose bugs are silent and whose failure direction is leaks. Keep it only for engines that have no column-level controls of their own.

The correction loop

The pieces compose: retrieve, generate, gate, and send any failure back to the model as text.

import re
from openai import OpenAI
from google.cloud import bigquery

llm = OpenAI()
EXEC_CFG = bigquery.QueryJobConfig(job_timeout_ms=120_000)   # per-job wall-clock cap

def extract_sql(text: str | None) -> str:
    if not text:
        raise EmptyCompletion()
    fenced = re.search(r"```(?:sql)?\s*(.+?)```", text, re.S | re.I)
    return (fenced.group(1) if fenced else text).strip()

async def answer(question: str, bq: bigquery.Client, user: str, max_rounds: int = 4):
    tables = await retrieve_tables(question, bq, user)   # scoped to the caller's grants
    if not tables:
        raise AccessRequest(question)                    # nothing readable matched -> grant request

    route = classify(question, tables)                   # metric or raw SQL
    if route.metric:
        # a governed metric skips the SQL loop entirely: the layer owns the joins,
        # the grain, and the arithmetic, and the user's JWT carries row-level rules
        return run_semantic_query(user_jwt(user), route.metric_query)

    errors: list[str] = []
    for _ in range(max_rounds + 1):
        try:
            sql = extract_sql(llm.chat.completions.create(
                model="gpt-4o", temperature=0,
                messages=[{"role": "system", "content": SYSTEM_PROMPT},
                          {"role": "user", "content": render(question, tables, errors)}],
            ).choices[0].message.content)
        except EmptyCompletion:
            errors.append("empty completion")            # a blank turn spends a retry, not the loop
            continue

        gate = cost_gate(bq, sql)
        if gate.ok:
            job = bq.query(sql, job_config=EXEC_CFG)     # the exact string the gate approved
            rows = list(job.result())                    # result() refreshes job statistics
            q = job.to_api_repr()["statistics"]["query"]
            # neither statistic has a property on the client; read the raw resource
            mask = q.get("dataMaskingStatistics", {})
            rls = q.get("rowLevelSecurityStatistics", {})
            return {"rows": rows, "sql": sql,
                    "masked": mask.get("dataMaskingApplied", False),
                    "filtered": rls.get("rowLevelSecurityApplied", False)}
        errors.append(gate.reason)                       # accumulate, never overwrite
    raise EscalateToHuman(question, sql, errors)

The branch at the top is the whole point of putting a semantic layer in the path: a question that resolves to a governed metric never enters the SQL loop, so there is no arithmetic for the model to get wrong. Everything else falls through to the loop, and the details there are deliberate. An empty completion consumes a retry instead of killing the loop, because a blank turn happens and the loop exists to survive it. Errors accumulate rather than overwrite, because a model that sees only its most recent error will oscillate between two bad queries forever; it needs the history to stop repeating itself. The string that executes is the one the gate approved, byte for byte. Rewrite the SQL after gating, for masking or anything else, and you have validated a different statement than the one you run, so the permission check no longer describes what executes. And the masked and filtered flags are read off the executed job, because that is the only place they exist.

The accumulating error list is also the part of my own design I’d flag in a review. BigQuery’s guidance on row-level security classifies query error messages and byte counts as side channels and tells you to keep both away from people who should only see filtered data. This loop keeps every rejection and feeds the growing pile back into the model’s context each round, which is a slow scan of the boundary conducted by the thing you least want holding a map of it. The escalation path is worse: EscalateToHuman hands a support engineer a transcript of everything the warehouse declined to answer. Capping the retries bounds that, which is a reason to keep the cap low that has nothing to do with latency or spend.

The loop converges quickly because the errors are specific. Column national_id is not available to your role, or estimated 1.4 TB scanned over a 50 GiB budget, add a partition filter, are messages a model repairs on the next attempt. Over a week of traffic the disposition looks like this.

Two exits matter as much as the executed middle. The 34 questions that match nothing readable are not failures; they are people asking for data they lack a grant to, and the right response is an access request. Anything the system could say to them instead would be invented. The 38 that never resolve inside the cap go to a human with the error history attached.

The wrong answer that clears every gate

Everything so far checks two questions: may this person see what the query touches, and what will it cost. Neither asks whether the query answers the question that was asked. A model that joins the wrong one of two similar tables, or filters on the wrong status code, produces a number the asker is fully cleared to see, at a cost well under budget, that is simply not the answer. Every gate in this post passes it. This is the 10% Spider 2.0 measures, and nothing here moves that number.

For governed metrics the semantic layer closes most of it: naming orders.net_revenue removes the degrees of freedom a model uses to be wrong. The raw-SQL tail is where the exposure stays open, and it is the larger share of real traffic.

Two things help, and neither is a gate. Make the model name the tables, columns and join keys it used and return that with the answer, so a wrong table is visible to a reviewer instead of buried in a plausible number. And where a question can be answered both ways, run it through the semantic layer and the raw-SQL path and compare. A disagreement is a flag, not a tiebreak. What you cannot do is gate your way to correctness.

What none of this protects

The controls above stop one user reaching another user’s data. They do not, however, make the system safe, and the gap is worth naming because most write-ups stop here.

Rows come back. If your product answers in natural language, and every product people want does, those rows go to a model, which means they leave the warehouse and the governance boundary in the same instant. Every row-level policy you enforced at the database holds right up to the point where results are pasted into a prompt and sent to a third-party API. That is the dominant exfiltration path in governed text-to-SQL, and it is created by the feature everyone asks for. If that is unacceptable, the summarising model has to run inside the boundary, which is an infrastructure decision and not a prompt one.

Result size is its own exposure. A user cleared to read a table one row at a time is usually not meant to extract all of it, and select everything I am allowed to see is a legitimate query under every control described here. Cap the returned rows, and treat a large authorised export as an event worth recording.

The output channel is a second identity boundary nobody configures. An answer computed under Alice’s grants, posted into a shared Slack channel or pinned to a dashboard, is now readable by people who do not have Alice’s access. The query was governed; the artifact is not.

And an audit trail is not optional on a regulated warehouse: who asked, the natural-language question, the exact SQL, the identity it ran under, the bytes billed, and what the gate decided. Don’t assume the warehouse is keeping it for you. INFORMATION_SCHEMA.JOBS holds the full query text and is read with a project-level job-listing permission, so every literal the model ever wrote into a WHERE clause sits in a view whose access has nothing to do with the policy tags on the column it came from. BigQuery also keeps six months of job history, which is shorter than any retention obligation I’ve been handed. If you can’t answer who asked what, and under whose grants it ran, for an arbitrary row a year later, the governance story has a hole in the middle no matter how good the controls around it are.

How it decays

None of this is hard to stand up. It degrades in ways specific to it, and the decay is where the maintenance cost lives.

Schemas drift. Someone renames a column, deprecates a table, splits one in two, and your retrieval index and cached DDL go stale. A stale index sends the model to a table that moved, which reads as a model regression when it is a catalog sync problem. Rebuild on a schedule tied to catalog changes, and read a spike in NotFound corrections as a drift alarm, not a model alarm.

Grants drift too, and that drift is a security event. If access is revoked while your candidate cache still lists a table as readable, the model proposes a query the dry run will block but the prompt should never have suggested. Nothing leaks, because the gate holds, but you are leaning on the gate. Never let a convenience cache decide what is safe.

The semantic layer is a dependency with opinions. When finance changes the net_revenue definition, every answer given last quarter used the old one, which is correct behaviour, although it also means your eval’s gold answers go stale without anyone noticing. Version the definitions and stamp the version onto every answer.

Budgets are not global constants. Fifty gibibytes feels generous until someone lands a 40 TB table. Make them per-role or per-workload, and watch the p99 of the scan distribution you can still see. A mean over a distribution this skewed moves for the wrong reasons. When the p99 creeps, either the questions got harder or the gate got leaky.

The last thing is that sometimes you should not use any of this. If a question is one of fifty a team asks every week, build fifty parameterised queries behind a dropdown and skip the model: deterministic, free, auditable, no scan surprises. Save the agent for the long tail of novel questions, which is where turning English into SQL earns its complexity. A good share of the text-to-SQL demand I have seen was really demand for a metrics catalog with a search box.