Guardrails for AI-Written SQL: Sandboxing, Cost Caps, Row Limits & Approval Gates

AI-written SQL guardrails are the layer of controls that stand between a language model's confident-looking query and your production data — because the moment you let a text-to-SQL feature turn a natural-language question into SQL and run it, you have accepted a query that nobody reviewed, that may reference columns that do not exist, that may scan a fact table with no filter, that may quietly contain a DROP or an UPDATE, and that will happily bill you thousands of dollars for a single misjudged join. A human's SQL goes through review; an AI's SQL arrives as untrusted input, and the whole discipline of guardrails is treating it exactly like a request from a stranger on the internet: parse it, bound it, sandbox it, and make someone sign off before it does anything irreversible.

This guide is the senior-data-engineering walkthrough for building that safety envelope — framed the way interviewers actually probe it. It covers static validation (parse the SQL into an abstract syntax tree, reject anything that is not a single read, allowlist the tables and columns it may touch, and inject a row limit so nothing comes back unbounded); sandboxing (run the query as a least-privilege read-only role, scope every row with row-level security, isolate the compute, and set a per-query timeout); cost caps (estimate the scan with a dry-run before executing, then cap bytes and rows at the engine); and approval gates with an audit trail (risk-classify each query, route the dangerous ones to a human, log everything, and rate-limit the loop). Each section 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 it works.

When you want hands-on reps immediately after reading, drill the text-to-SQL practice library →, harden your instincts on the defensive-coding practice library →, and sharpen the checks on the data-validation practice library →.

---

On this page

• Why AI-written SQL needs guardrails
• Static validation — parse, block writes, allowlist, inject a LIMIT
• Sandboxing and scoping — read-only roles, RLS, isolated compute
• Cost and row caps — dry-run estimates, byte and row limits, timeouts
• Approval gates and audit — human review, logging, rate limits
• Cheat sheet — AI-SQL guardrails
• Frequently asked questions
• Practice on PipeCode

---

1. Why AI-written SQL needs guardrails

The trust inversion — a human query is reviewed; an AI query is untrusted input

The one-sentence invariant: an AI-written query must run with no more trust, privilege, or budget than a request from an anonymous stranger, because a text-to-SQL model produces text that looks like reviewed SQL but carries none of the guarantees — it can hallucinate schema, scan without bounds, emit destructive statements, and exfiltrate data — so the guardrail stack replaces the human reviewer with four automated control planes: static validation of what the SQL says, sandboxing of what it can touch, caps on how much it can consume, and approval plus audit for who signs off and what is recorded. The failure mode is treating the model's fluency as correctness; the discipline is treating its output as hostile until proven bounded.

The failure catalogue interviewers expect you to name.

• Hallucinated schema. The model invents a customers.ssn column or a revenue_2026 table that never existed, and the query either errors loudly or, worse, silently matches a real-but-wrong identifier. Guardrail: an allowlist of real tables and columns, checked against the parsed query.
• Unbounded scans. A SELECT * with no WHERE and no LIMIT over a billion-row fact table is syntactically perfect and operationally catastrophic. Guardrail: inject a row limit and estimate the scan before running.
• Destructive DDL/DML. A prompt like "clean up the old orders" can produce a DELETE or DROP. Nothing about the SQL text warns you. Guardrail: parse and reject anything that is not a single read, and run as a role that physically cannot write.
• Cost blowups. A cross join, a missing partition filter, or a scan of an unpartitioned petabyte table turns a "quick question" into a five-figure bill. Guardrail: a dry-run cost estimate and hard byte caps.
• Data exfiltration. A query that reads another tenant's rows, or dumps a whole PII column, is a breach even if it never writes. Guardrail: row-level security and column masking in the database itself.

The four control planes — defence in depth, not a single check.

• Static validation (what the SQL says). Parse the query into an AST and reason about it structurally: is it a single read, does it touch only allowlisted identifiers, does it have a bounded row limit? This is the cheapest gate and it runs before anything executes.
• Sandboxing (what the SQL can touch). Execute as a least-privilege, read-only role, scoped by row-level security, on isolated compute, with a per-query timeout. The database is the last line of defence and the only one an attacker cannot talk their way past.
• Cost and row caps (how much it can consume). Estimate the scan with a dry-run, cap the bytes the engine will bill, and cap the rows returned — so even a valid query cannot become a cost incident.
• Approval and audit (who signs off, what is logged). Risk-classify each query; auto-run the safe, gate the risky for a human, deny the forbidden; log the prompt, the SQL, the verdict, the cost, and the identity behind it.

What interviewers listen for.

• Do you say the model's output is untrusted input and design as if it were adversarial? — senior signal.
• Do you name more than one layer — not just "I check for DROP" but validation and a read-only role and cost caps? — required answer.
• Do you put authorization in the database (RLS/roles), not only in the application prompt or a string check? — required answer.
• Do you insist on an audit log so every AI query is attributable and replayable? — senior signal.
• Do you treat a runaway cost as a first-class failure, not just correctness? — senior signal.

Worked example — mapping each failure to the control plane that stops it

Detailed explanation. The single most useful artifact for this interview is a memorised mapping of failure → guardrail. When an interviewer names a risk, you should answer with the layer that structurally prevents it, and note that most risks are caught by more than one layer — which is the point of defence in depth.

• The risks. Hallucinated schema, unbounded scan, destructive write, cost blowup, cross-tenant read.
• The layers. Static validation, sandbox (role/RLS/timeout), cost caps, approval + audit.
• The rule. Every risk should be stopped by at least one layer that cannot be bypassed by cleverer prompting.

Question. For each failure mode, name the primary guardrail that stops it and a second layer that backs it up.

Input.

Failure mode	Primary guardrail	Backup layer
Hallucinated table/column	allowlist in static validation	read-only role errors on unknown object
Unbounded scan	inject LIMIT + dry-run estimate	statement timeout kills a runaway
Destructive DDL/DML	reject non-SELECT in the parser	read-only role cannot write at all
Cost blowup	dry-run cost gate + byte cap	resource monitor suspends the warehouse
Cross-tenant read	row-level security in the DB	audit log flags the anomaly

Code.

# The guardrail router in one glance: each risk is checked by a layer that
# does NOT depend on the model behaving well.
def classify_and_guard(sql: str, ctx) -> str:
    static_validate(sql, allowlist=ctx.allowlist)   # parse: single read, known ids, LIMIT
    est_bytes = dry_run_cost(sql, ctx)               # estimate BEFORE executing
    if est_bytes > ctx.byte_cap:
        raise GuardrailError("scan estimate exceeds cost cap")
    verdict = risk_route(sql, est_bytes, ctx)        # auto-run | human-approve | deny
    audit_log(ctx.user, ctx.prompt, sql, verdict, est_bytes)
    if verdict != "auto-run":
        return verdict                               # stop; a human decides / it is denied
    # Executed as a read-only, row-scoped role on an isolated warehouse with a timeout.
    return run_readonly(sql, role=ctx.readonly_role, timeout_s=ctx.timeout_s)

Step-by-step explanation.

1. static_validate is the first and cheapest gate: it parses the SQL and rejects writes, unknown identifiers, and missing limits — catching hallucinations and destructive statements before any engine is touched.
2. dry_run_cost estimates the scan without executing it, so a cost blowup is caught by a number, not by the invoice — the gate that turns "quick question" back into "no" when the estimate exceeds the cap.
3. risk_route classifies the query into auto-run, human-approve, or deny, so the dangerous-but-sometimes-legitimate cases (a write, a sensitive table, an expensive scan) get a human instead of a blanket block.
4. audit_log records every query and verdict before execution, so even auto-run queries are attributable and replayable — the accountability layer that survives an incident review.
5. Only if the verdict is auto-run does the query execute, and even then it runs as a read-only, row-scoped role on isolated compute with a timeout — so the sandbox is a backstop under every earlier check, not an alternative to them.

Output.

Query the model emits	Which layer stops it	Result
DROP TABLE orders	parser (non-SELECT)	denied before execution
SELECT * FROM orders (no bound)	LIMIT injection + dry-run	clamped, or rejected if huge
SELECT ssn FROM custmers	allowlist (unknown identifier)	denied (hallucination)
valid read, 900 GB scan	dry-run cost gate	denied over cap
valid read, other tenant	row-level security	returns only caller's rows

Rule of thumb. Answer every AI-SQL risk with a layer, and make sure at least one of those layers lives in the database where prompting cannot reach it. The model can be talked into anything; a read-only role and an RLS policy cannot.

Worked example — the interview escalation on an AI-SQL feature

Detailed explanation. The senior version of this interview escalates from an innocent opener to progressively nastier failure modes. The candidates who pre-empt each layer without being prompted score highest, because they demonstrate they have shipped this rather than imagined it.

• Ambiguous opener. "We want users to ask questions in English and get data back. Just run the SQL the model writes?"
• Follow-up 1. "What if it writes DELETE FROM users?" — probes validation + read-only role.
• Follow-up 2. "What if it scans a petabyte?" — probes cost caps.
• Follow-up 3. "What if tenant A's question returns tenant B's rows?" — probes RLS.
• Follow-up 4. "Who is accountable when it goes wrong?" — probes audit + approval.

Question. Draft a 5-minute senior answer that pre-empts all four follow-ups without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Run the SQL?	"yes, the model is good now"	"no — treat it as untrusted; validate, sandbox, cap, gate"
Destructive write	"I check the string for DELETE"	"parse it; and run as a role that cannot write"
Huge scan	"we'll notice on the bill"	"dry-run estimate + byte cap before it runs"
Cross-tenant	"the prompt says only their data"	"row-level security in the database"
Accountability	"we trust the users"	"audit every query; gate risky ones for a human"

Code.

Senior AI-SQL guardrail answer template (5 minutes)
===================================================

Minute 1 — name the trust inversion
  "The model's SQL is untrusted input, not reviewed code. I treat it like
   a request from a stranger: validate it, sandbox it, cap it, and gate it."

Minute 2 — static validation
  "I parse the SQL into an AST — never regex. It must be a single SELECT,
   touch only allowlisted tables/columns, and carry a LIMIT I inject."

Minute 3 — sandboxing
  "It runs as a read-only role scoped by row-level security, on an isolated
   warehouse, with a statement timeout. The DB is the last line of defence."

Minute 4 — cost caps
  "I dry-run to estimate bytes scanned and reject over a cap; the engine also
   enforces maximum_bytes_billed and a resource monitor, plus a row cap."

Minute 5 — approval + audit
  "Risky queries — writes, sensitive tables, big spends — go to a human. And
   every query, prompt, verdict, and cost is written to an append-only log."

Step-by-step explanation.

1. Minute 1 frames the whole answer around the trust inversion. Weak candidates want to run the SQL; naming "untrusted input" signals you understand the security model, not just the demo.
2. Minute 2 shows you validate structurally — parsing, not string-matching — which is the difference between a guardrail and a bypassable filter.
3. Minute 3 pre-empts the destructive-write and cross-tenant follow-ups at once: a read-only, row-scoped role means the database refuses the dangerous operation regardless of what the SQL says.
4. Minute 4 pre-empts the cost follow-up by volunteering the dry-run and the hard caps before the interviewer mentions the bill — the tell of someone who has been paged for a runaway query.
5. Minute 5 closes on accountability: approval for the risky slice and an audit log for everything, which is the sentence that separates a platform engineer from someone who bolted an LLM onto a database.

Output.

Grading criterion	Weak score	Senior score
Names untrusted-input framing	rare	mandatory
Parses instead of regex	occasional	mandatory
Authorization in the DB	rare	senior signal
Cost caps before execution	rare	senior signal
Audit + human approval	rare	senior signal

Rule of thumb. The senior AI-SQL answer is a 5-minute monologue covering the trust inversion, static validation, sandboxing, cost caps, and approval/audit — delivered before the follow-ups arrive. Rehearse it once; it survives every "but what if the model…" the interviewer can throw.

Worked example — why a string check is not a guardrail

Detailed explanation. The most common junior mistake is "I'll just block the query if it contains the word DELETE." This feels like a guardrail and is trivially bypassable. Walk through why substring checks fail and what replaces them.

• The naive check. Reject the SQL if it contains drop, delete, update, or ;.
• The bypasses. Comments, casing, whitespace, string literals, and stacked statements all defeat substring matching.
• The replacement. Parse the SQL and reason about statement types and identifiers, not text.

Question. Show three inputs that defeat a substring-based block, then state the structural check that stops all three.

Input.

Bypass technique	Example the model can emit	Why substring fails
Comment injection	SELECT 1 /* delete */ FROM t	delete is in a comment, not a statement
Casing / whitespace	DeLeTe\t FROM t	naive contains("delete") may miss variants
String literal	SELECT 'please delete' AS note	the word is data, not a keyword
Stacked statement	SELECT 1; DROP TABLE t	the ; split is what actually matters

Code.

# WRONG: a substring filter. Looks safe, is not.
BANNED = ("drop", "delete", "update", "insert", ";")
def naive_block(sql: str) -> bool:
    low = sql.lower()
    return not any(b in low for b in BANNED)   # rejects harmless comments, misses real writes

# RIGHT: parse and reason about STRUCTURE, not text.
import sqlglot
from sqlglot import exp

def is_single_read(sql: str, dialect: str = "postgres") -> bool:
    stmts = sqlglot.parse(sql, read=dialect)          # splits into real statements
    if len(stmts) != 1:                               # stacked statements -> reject
        return False
    tree = stmts[0]
    if not isinstance(tree, exp.Select):              # only a top-level SELECT is a read
        return False
    # No write/DDL node anywhere in the tree (e.g. a write hidden in a CTE).
    forbidden = (exp.Insert, exp.Update, exp.Delete, exp.Drop,
                 exp.Create, exp.Alter, exp.Merge, exp.Command)
    return not any(isinstance(n, forbidden) for n in tree.walk())

Step-by-step explanation.

1. naive_block rejects a harmless query that merely mentions "delete" in a comment or string, and — far worse — can be tricked by casing or by a write it did not anticipate, so it is both annoying and unsafe.
2. sqlglot.parse splits the input into real statements; if there is more than one, the query is a stacked-statement attack and is rejected outright — the ; problem solved structurally.
3. isinstance(tree, exp.Select) confirms the single statement is a top-level read; a DELETE or DROP is a different node type and fails this check regardless of how it is spelled, cased, or commented.
4. tree.walk() traverses the whole AST, so a write hidden inside a CTE or a subquery (WITH x AS (DELETE ...)) is caught — substring matching cannot see structure, but the parser can.
5. The lesson generalises: validation must operate on the parsed meaning of the SQL, never its text, because text is infinitely mutable and meaning is not.

Output.

Input	naive_block verdict	is_single_read verdict
SELECT 1 /* delete */ FROM t	blocked (false positive)	allowed (correct)
SELECT 1; DROP TABLE t	blocked only if ; banned	rejected (stacked)
WITH x AS (DELETE FROM t ...) SELECT 1	may pass (no top-level DELETE word matched loosely)	rejected (write in CTE)
SELECT 'delete' AS note	blocked (false positive)	allowed (correct)

Rule of thumb. Never validate SQL with substring or regex checks — they produce false positives on harmless text and false negatives on real attacks. Parse into an AST and reason about statement types and identifiers; structure is the only thing you can trust.

Senior interview question on the end-to-end AI-SQL guardrail stack

A senior interviewer might open with: "Your product lets users ask questions in English; a model turns each into SQL that runs against the warehouse. Design the whole guardrail stack — not one check, but the layered defence that makes this safe: what happens to a query between the model emitting it and a result coming back, where each risk (a hidden write, a hallucinated column, a petabyte scan, a cross-tenant read, an unaccountable query) is stopped, and why no single layer is enough."

Solution Using four control planes — validate, sandbox, cap, gate — as one pipeline

# The full guardrail pipeline: a query passes through four control planes in order,
# and NO plane trusts the model or the plane before it.
def handle_ai_query(prompt: str, ctx) -> Result:
    sql = model.generate_sql(prompt, schema=ctx.allowlist_schema)

    # PLANE 1 — static validation (what the SQL says): parse, single read,
    # allowlist tables/columns, inject/clamp LIMIT. Runs before any engine.
    safe_sql = validate_ai_sql(sql, ceiling=1000, dialect=ctx.dialect)

    # PLANE 3 (estimate) — cost: dry-run for exact bytes BEFORE executing.
    est_bytes = dry_run_bytes(ctx.client, safe_sql)

    # PLANE 4 — approval + audit: rate-limit, risk-classify, log EVERY query.
    rate_check(ctx.limits, est_bytes)                     # per-identity count + cost
    verdict = risk_route(safe_sql, ctx.tables, est_bytes, is_write=False)
    audit_log(ctx.user, prompt, safe_sql, verdict, est_bytes)
    if verdict == "deny":
        raise GuardrailError("denied by policy")
    if verdict == "human-approve":
        return enqueue_for_review(ctx, safe_sql, est_bytes)   # a human decides

    # PLANE 2 — sandbox (what the SQL can touch): read-only, row-scoped role,
    # isolated compute, hard byte ceiling + statement timeout.
    return run_sandboxed(
        safe_sql,
        role=ctx.readonly_role,        # cannot write or reach base tables
        tenant=ctx.session.tenant_id,  # forced RLS scopes rows
        max_bytes=ctx.byte_ceiling,    # maximum_bytes_billed backstop
        timeout_s=ctx.timeout_s,       # engine kills a runaway
    )

# Where each risk dies — no risk depends on a single layer:
hidden write (in a CTE)   -> PLANE 1 (parser rejects) AND PLANE 2 (read-only role)
hallucinated column       -> PLANE 1 (allowlist) AND PLANE 2 (engine errors on unknown)
petabyte scan             -> PLANE 3 (dry-run reject) AND PLANE 2 (maximum_bytes_billed)
cross-tenant read         -> PLANE 2 (forced RLS on session tenant)
unaccountable query       -> PLANE 4 (append-only audit + human gate)
agent-loop DoS / cost spike -> PLANE 4 (per-identity rate limit by count + cost)

Step-by-step trace.

Control plane	What it checks	Primary risk stopped
1. Static validation	single read, allowlist, LIMIT	writes, hallucinations, unbounded rows
3. Cost estimate	dry-run bytes vs cap	the expensive scan, before it runs
4. Approval + audit	rate limit, risk route, log	DoS, unaccountable/risky queries
2. Sandbox	read-only role, RLS, ceiling, timeout	anything that slipped through 1/3/4

After deployment, a model-generated query is first parsed and validated (rejecting writes, hallucinated identifiers, and unbounded results with an explainable error), then dry-run for an exact byte estimate and rejected if over the cap, then rate-checked and risk-classified with every query logged to an append-only trail and the risky slice routed to a human — and only a query that survives all of that executes inside a sandbox where a read-only, tenant-scoped role on isolated compute with a hard byte ceiling and a statement timeout is the final backstop. Every named risk is stopped by at least two independent layers, so no single bug, edge case, or cleverer prompt is sufficient to cause harm.

Output:

Metric	Run the model's SQL directly	Four-plane guardrail stack
Destructive write	executes	rejected (parser) + refused (role)
Hallucinated identifier	hits the DB	rejected (allowlist) + errors (engine)
Petabyte scan	billed	rejected (dry-run) + capped (engine)
Cross-tenant read	leaks	filtered (forced RLS)
Accountability	none	full (append-only audit + approval)

Why this works — concept by concept:

• Layered control planes — validation, cost estimation, approval, and sandboxing each stop a different class of failure, and because they are independent, a query must defeat all of them to cause harm — defence in depth, not a single gate.
• No layer trusts the model — each plane treats its input as untrusted, so a bug or an edge case in one plane is caught by another; the model's fluency is never mistaken for a guarantee at any step.
• The engine is the final backstop — the sandbox (read-only role, forced RLS, hard caps, timeout) is enforced by the database regardless of what the SQL says, so it holds even when every application-side check is fooled.
• Everything is accounted for — an append-only audit log plus human approval for the risky slice means every query is attributable and the dangerous ones are reviewed, turning a black-box feature into a governable one.
• Cost — one parse, one free dry-run, two token-bucket checks, a log write, and a sandboxed execution per query, versus the cost of a single destructive, runaway, or leaking query in production. The eliminated cost is a breach, an outage, or a runaway bill — O(layers) of cheap checks against O(catastrophe) unguarded.

Defensive coding
Topic — defensive-coding
Defensive-coding problems on validating untrusted input

Practice →

SQL generation Topic — sql-generation SQL-generation problems on text-to-SQL correctness

Practice →

---

2. Static validation — parse, block writes, allowlist, inject a LIMIT

Parse the SQL into an AST, reject anything that is not a bounded, allowlisted read

The mental model in one line: static validation is the gate that runs before execution — it parses the AI-written SQL into an abstract syntax tree with a real SQL parser (sqlparse or sqlglot), asserts the query is a single SELECT with no DDL/DML anywhere in the tree, checks every referenced table and column against an allowlist of real, permitted identifiers so hallucinations and unauthorized reads are rejected by name, and rewrites the tree to inject or clamp a LIMIT so no query can return an unbounded result — turning the model's text into a structurally proven, bounded read or a hard rejection, with no engine touched either way. Get static validation right and most of the failure catalogue never reaches the database.

Parse first — never regex.

• Use a real parser. sqlglot builds a typed AST you can traverse and rewrite; sqlparse tokenises. Regex cannot understand nesting, comments, or string literals, so it is unsafe for validation.
• Split statements. A validated query must be exactly one statement. More than one is a stacked-statement attack; reject it before looking at anything else.
• Reason on nodes, not text. Ask "is the root a Select? is there any write node in the tree?" — questions about structure that text matching cannot answer.

Reject anything that is not a single read.

• Block DDL/DML. INSERT, UPDATE, DELETE, MERGE, DROP, CREATE, ALTER, TRUNCATE, and vendor commands (COPY, GRANT) have no place in a read feature — reject on node type.
• Look inside CTEs and subqueries. A write can hide in a WITH clause or a data-modifying CTE. Walk the whole tree, not just the top node.
• Deny multi-statement and procedural bodies. No ;-separated batches, no DO blocks, no dynamic SQL.

Allowlist tables and columns — kill hallucinations by name.

• Extract identifiers from the AST. Collect every referenced table and column; compare against a catalogue of what this feature is allowed to read.
• Deny unknowns. An unknown table or column is either a hallucination or an attempt to reach something off-limits; either way it is a rejection, not a repair.
• Column-level control. Keep PII and internal columns off the allowlist so the model cannot select them even from a permitted table.

Inject or clamp a row limit.

• Add a LIMIT if missing. Rewrite the AST to append a bounded LIMIT so a forgotten bound never becomes an unbounded pull.
• Clamp an over-large one. If the model asked for LIMIT 1000000, reduce it to your ceiling rather than trusting its number.
• Rewrite, then re-serialise. Emit the SQL from the modified AST, so what runs is provably the bounded version.

The failure modes senior engineers pre-empt.

• Regex validation. Bypassable by comments, casing, and literals. Mitigation: parse into an AST and reason on nodes.
• Top-node-only checks. A write inside a CTE slips past a check that only inspects the root. Mitigation: walk the entire tree.
• String-concatenated LIMIT. Appending " LIMIT 1000" to text breaks on trailing semicolons, existing limits, or comments. Mitigation: modify the parsed AST and re-serialise.

Common interview probes on static validation.

• "How do you validate the SQL is read-only?" — parse it; assert a single Select node and no write nodes anywhere in the tree.
• "How do you stop hallucinated columns?" — allowlist real identifiers extracted from the AST; deny unknowns.
• "How do you guarantee a row bound?" — inject/clamp a LIMIT by rewriting the AST, not by string append.
• "Why not regex?" — comments, casing, literals, and nesting defeat it; only a parser understands structure.

Worked example — reject any statement that is not a single SELECT

Detailed explanation. The first gate: parse, confirm exactly one statement, confirm it is a top-level read, and confirm no write node exists anywhere in the tree. This catches destructive statements, stacked statements, and writes hidden in CTEs in one pass.

• The parse. sqlglot.parse returns a list of statement trees.
• The checks. Exactly one statement; root is Select; no write node in walk().
• The result. A boolean plus a reason, so rejections are explainable.

Question. Write a validator that accepts a single read and rejects writes, DDL, and stacked statements — including a write buried in a CTE.

Input.

Query	Should be
SELECT id FROM orders WHERE region='EU'	accepted
DELETE FROM orders WHERE id=1	rejected (write)
SELECT 1; DROP TABLE orders	rejected (stacked)
WITH d AS (DELETE FROM orders RETURNING id) SELECT * FROM d	rejected (write in CTE)

Code.

import sqlglot
from sqlglot import exp

WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge,
               exp.Drop, exp.Create, exp.Alter, exp.TruncateTable,
               exp.Command)   # exp.Command covers COPY/GRANT/CALL/etc.

def assert_single_read(sql: str, dialect: str = "postgres") -> None:
    try:
        statements = sqlglot.parse(sql, read=dialect)
    except sqlglot.errors.ParseError as e:
        raise GuardrailError(f"unparseable SQL: {e}")

    statements = [s for s in statements if s is not None]
    if len(statements) != 1:
        raise GuardrailError(f"expected 1 statement, got {len(statements)} (stacked?)")

    root = statements[0]
    if not isinstance(root, exp.Select):
        raise GuardrailError(f"not a read: top-level node is {type(root).__name__}")

    for node in root.walk():                       # traverse the ENTIRE tree
        if isinstance(node, WRITE_NODES):
            raise GuardrailError(f"write/DDL node found in tree: {type(node).__name__}")


class GuardrailError(Exception):
    """Raised when a query fails a guardrail; carries an explainable reason."""

Step-by-step explanation.

1. sqlglot.parse turns the text into a list of statement trees and raises on invalid SQL, so malformed or truncated model output is rejected immediately rather than being partially interpreted.
2. Filtering None and requiring exactly one statement rejects stacked statements (SELECT 1; DROP ...) — the classic way a write rides in behind a read.
3. isinstance(root, exp.Select) rejects any query whose top node is not a read: a DELETE, UPDATE, or DROP never passes, no matter how it is cased or commented.
4. root.walk() visits every node in the tree, so a data-modifying CTE (WITH d AS (DELETE ... RETURNING ...)) is caught even though the root is a SELECT — the check top-node-only validators miss.
5. Every rejection raises a GuardrailError with a reason string, so the feature can log why a query was blocked and, if appropriate, tell the model to try again — rejections are explainable, not silent.

Output.

Query	Verdict	Reason
SELECT id FROM orders WHERE region='EU'	accepted	single read, no write nodes
DELETE FROM orders WHERE id=1	rejected	top-level node is Delete
SELECT 1; DROP TABLE orders	rejected	2 statements (stacked)
WITH d AS (DELETE ... RETURNING id) SELECT * FROM d	rejected	Delete node found in tree

Rule of thumb. Assert exactly one statement whose root is a Select and which contains no write node anywhere in its tree. Walking the full AST — not just the root — is what stops a write from hiding in a CTE or subquery.

Worked example — enforce a table and column allowlist from the AST

Detailed explanation. A read-only query can still be dangerous: it might read a table this feature should never touch, or select a PII column. The allowlist gate extracts every referenced identifier from the AST and rejects anything not explicitly permitted — which also kills hallucinated schema for free.

• The catalogue. A map of allowed tables to their allowed columns.
• The extraction. Pull table and column nodes from the parsed tree.
• The rule. Every referenced identifier must be in the catalogue, or reject.

Question. Reject a query that references any table or column outside an allowlist, so both unauthorized reads and hallucinated identifiers fail.

Input.

Allowlist	Query	Verdict
orders(id, region, total)	SELECT id, total FROM orders	accepted
orders(id, region, total)	SELECT ssn FROM orders	rejected (column)
orders(id, region, total)	SELECT * FROM payroll	rejected (table)
orders(id, region, total)	SELECT id FROM ordrs	rejected (hallucinated)

Code.

import sqlglot
from sqlglot import exp

# What this feature is ALLOWED to read. PII/internal columns are simply absent.
ALLOWLIST = {
    "orders":   {"id", "region", "total", "created_at"},
    "customers": {"id", "name", "region"},         # note: no email/ssn here
}

def assert_allowlisted(sql: str, dialect: str = "postgres") -> None:
    tree = sqlglot.parse_one(sql, read=dialect)

    # 1) Every table referenced must be allowlisted.
    tables = {t.name for t in tree.find_all(exp.Table)}
    unknown_tables = tables - ALLOWLIST.keys()
    if unknown_tables:
        raise GuardrailError(f"table(s) not allowlisted: {sorted(unknown_tables)}")

    # 2) Reject SELECT * — it defeats a column allowlist by construction.
    if any(isinstance(s, exp.Star) for s in tree.find_all(exp.Star)):
        raise GuardrailError("SELECT * is not permitted; list explicit columns")

    # 3) Every referenced column must be allowlisted for SOME referenced table.
    permitted_cols = set().union(*(ALLOWLIST[t] for t in tables))
    for col in tree.find_all(exp.Column):
        if col.name not in permitted_cols:
            raise GuardrailError(f"column not allowlisted: {col.name}")

Step-by-step explanation.

1. parse_one gives a single tree; find_all(exp.Table) collects every table the query touches, including those in joins and subqueries — the full read surface, not just the FROM clause.
2. Any table not in ALLOWLIST is a rejection: this simultaneously blocks reads of off-limits tables (payroll) and hallucinated tables (ordrs), because both are "not in the catalogue."
3. SELECT * is rejected outright, because a star expands to every column at runtime and would silently pull columns the allowlist was meant to hide — forcing explicit column lists makes the column check meaningful.
4. find_all(exp.Column) collects every referenced column, and each must appear in the permitted set for the referenced tables; a PII column like ssn is absent from the allowlist, so selecting it fails even though orders itself is permitted.
5. The same mechanism kills hallucinations: a column or table the model invented is, by definition, not in the catalogue, so it is rejected by name rather than being sent to the database to fail (or worse, to match something real-but-wrong).

Output.

Query	Verdict	Reason
SELECT id, total FROM orders	accepted	all identifiers allowlisted
SELECT ssn FROM orders	rejected	column not allowlisted
SELECT * FROM payroll	rejected	table not allowlisted (and star)
SELECT id FROM ordrs	rejected	table not allowlisted (hallucinated)

Rule of thumb. Build an allowlist of tables and their permitted columns, extract every identifier from the AST, and reject anything not on it — and ban SELECT *, which would otherwise smuggle hidden columns past a column allowlist. Keep PII columns off the list so they cannot be selected at all.

Worked example — inject or clamp a LIMIT by rewriting the AST

Detailed explanation. The last static gate guarantees a bounded result. Rather than appending " LIMIT 1000" to text — which breaks on semicolons, comments, and existing limits — you modify the parsed tree: add a LIMIT if absent, clamp it if too large, and re-serialise. What runs is provably bounded.

• The absent case. No LIMIT node → add one at the ceiling.
• The over-large case. LIMIT 1000000 → clamp to the ceiling.
• The emit. Re-serialise from the modified AST, not the original text.

Question. Guarantee every validated read carries a LIMIT no larger than a ceiling, by rewriting the AST.

Input.

Input SQL	Ceiling	Output SQL
SELECT id FROM orders	1000	SELECT id FROM orders LIMIT 1000
SELECT id FROM orders LIMIT 50	1000	unchanged (50 ≤ 1000)
SELECT id FROM orders LIMIT 999999	1000	clamped to LIMIT 1000

Code.

import sqlglot
from sqlglot import exp

def enforce_limit(sql: str, ceiling: int = 1000, dialect: str = "postgres") -> str:
    tree = sqlglot.parse_one(sql, read=dialect)

    existing = tree.args.get("limit")
    if existing is None:
        # No LIMIT -> inject the ceiling.
        tree.set("limit", exp.Limit(expression=exp.Literal.number(ceiling)))
    else:
        n = int(existing.expression.name)          # the requested row count
        if n > ceiling:
            # Too large -> clamp to the ceiling. Never trust the model's number.
            existing.set("expression", exp.Literal.number(ceiling))

    return tree.sql(dialect=dialect)               # re-serialise from the AST


# Usage: after assert_single_read + assert_allowlisted, ALWAYS pass through this.
safe_sql = enforce_limit("SELECT id FROM orders", ceiling=1000)
# -> "SELECT id FROM orders LIMIT 1000"

Step-by-step explanation.

1. parse_one gives a mutable tree, and tree.args.get("limit") inspects whether a LIMIT node already exists — a structural question the text form cannot answer reliably (a trailing ; or comment would break a naive check).
2. When no limit exists, tree.set("limit", ...) attaches a LIMIT node at the ceiling, so a query the model forgot to bound is now bounded — the single most important protection against an accidental full-table pull.
3. When a limit exists but exceeds the ceiling, the code overwrites the literal with the ceiling value: the model's LIMIT 999999 becomes LIMIT 1000, because the model's number is a suggestion, not an authority.
4. When the existing limit is within the ceiling, it is left untouched — the guardrail only ever tightens, never loosens, so a legitimately small page size is respected.
5. tree.sql(...) re-serialises the query from the modified AST, so the string that actually executes is provably the bounded one — the rewrite happens on structure and the emit is derived from it, eliminating the string-append bugs entirely.

Output.

Input	Existing limit	Emitted SQL
SELECT id FROM orders	none	... LIMIT 1000 (injected)
SELECT id FROM orders LIMIT 50	50	... LIMIT 50 (kept)
SELECT id FROM orders LIMIT 999999	999999	... LIMIT 1000 (clamped)
SELECT id FROM orders;	none	... LIMIT 1000 (no semicolon bug)

Rule of thumb. Guarantee a row bound by modifying the parsed AST — inject a LIMIT when absent, clamp it when over the ceiling, and re-serialise — never by concatenating text. The guardrail should only ever tighten the bound, and what executes must be derived from the tree you validated.

Senior interview question on static validation of AI-written SQL

A senior interviewer might ask: "An LLM turns user questions into SQL that you then run against a warehouse. Before any execution, design the static-validation layer: how you prove the query is a single read with no destructive statements even if a write hides in a CTE, how you stop hallucinated or off-limits tables and columns, and how you guarantee every query returns a bounded number of rows — all without a single regex and without trusting the model's output."

Solution Using a parser, a write-node check, an identifier allowlist, and AST-based LIMIT injection

import sqlglot
from sqlglot import exp

class GuardrailError(Exception):
    pass

WRITE_NODES = (exp.Insert, exp.Update, exp.Delete, exp.Merge, exp.Drop,
               exp.Create, exp.Alter, exp.TruncateTable, exp.Command)

ALLOWLIST = {
    "orders":    {"id", "region", "total", "created_at"},
    "customers": {"id", "name", "region"},          # PII columns deliberately absent
}

def validate_ai_sql(sql: str, ceiling: int = 1000, dialect: str = "postgres") -> str:
    # 1. Parse; exactly one statement; must be a top-level SELECT.
    stmts = [s for s in sqlglot.parse(sql, read=dialect) if s is not None]
    if len(stmts) != 1:
        raise GuardrailError("expected exactly one statement")
    tree = stmts[0]
    if not isinstance(tree, exp.Select):
        raise GuardrailError(f"not a read: {type(tree).__name__}")

    # 2. No write/DDL anywhere in the tree (covers CTEs/subqueries).
    for node in tree.walk():
        if isinstance(node, WRITE_NODES):
            raise GuardrailError(f"write node in tree: {type(node).__name__}")

    # 3. No SELECT *; every table and column must be allowlisted.
    if list(tree.find_all(exp.Star)):
        raise GuardrailError("SELECT * not permitted")
    tables = {t.name for t in tree.find_all(exp.Table)}
    if tables - ALLOWLIST.keys():
        raise GuardrailError(f"table not allowlisted: {sorted(tables - ALLOWLIST.keys())}")
    permitted = set().union(*(ALLOWLIST[t] for t in tables))
    for col in tree.find_all(exp.Column):
        if col.name not in permitted:
            raise GuardrailError(f"column not allowlisted: {col.name}")

    # 4. Inject or clamp a LIMIT, then emit from the AST.
    limit = tree.args.get("limit")
    if limit is None:
        tree.set("limit", exp.Limit(expression=exp.Literal.number(ceiling)))
    elif int(limit.expression.name) > ceiling:
        limit.set("expression", exp.Literal.number(ceiling))
    return tree.sql(dialect=dialect)

# What the model emits  ->  what validate_ai_sql does
"SELECT id, total FROM orders WHERE region='EU'"
   -> pass 1 (1 SELECT), pass 2 (no writes), pass 3 (allowlisted),
      pass 4 (inject LIMIT 1000)  =>  RUN "SELECT id, total FROM orders WHERE region = 'EU' LIMIT 1000"

"DELETE FROM orders"                    -> GuardrailError: not a read
"SELECT 1; DROP TABLE orders"           -> GuardrailError: expected exactly one statement
"SELECT ssn FROM orders"                -> GuardrailError: column not allowlisted: ssn
"SELECT * FROM payroll"                 -> GuardrailError: SELECT * not permitted / table not allowlisted
"SELECT id FROM orders LIMIT 999999"    -> clamp  =>  RUN "... LIMIT 1000"

Step-by-step trace.

Gate	Check	Blocks
Parse	one statement, root is Select	stacked statements, non-reads
Walk	no write node in the tree	writes hidden in CTEs/subqueries
Star	reject SELECT *	hidden-column smuggling
Allowlist	tables + columns must be known	off-limits reads, hallucinations
Limit	inject/clamp to ceiling	unbounded result sets

After validation, the only thing that can execute is a single SELECT that touches allowlisted identifiers, selects explicit columns, and carries a LIMIT no larger than the ceiling. A destructive statement, a stacked batch, a write inside a CTE, an off-limits or hallucinated identifier, and an unbounded scan each raise an explainable GuardrailError before the database is ever contacted. The emitted SQL is re-serialised from the validated tree, so what runs is provably what passed the gates.

Output:

Metric	Naive (run the model's SQL)	Static-validated
Destructive statements	possible	impossible (parser rejects)
Stacked-statement attack	possible	rejected (one statement only)
Hallucinated identifiers	hit the DB and error/mis-match	rejected by allowlist
Hidden PII columns	selectable	absent from allowlist
Unbounded result	possible	bounded (injected/clamped LIMIT)

Why this works — concept by concept:

• Parse, not regex — building a real AST lets validation reason about statement types and identifiers instead of matching text, so comments, casing, and string literals cannot bypass it and nesting cannot hide from it.
• Full-tree write check — walking every node, not just the root, means a data-modifying CTE or subquery is caught, closing the gap that top-node-only validators leave open.
• Identifier allowlist — comparing every referenced table and column against a catalogue rejects off-limits reads and hallucinations by the same mechanism, and keeping PII columns off the list makes them unselectable.
• AST-based LIMIT injection — rewriting the parsed tree and re-serialising guarantees a bounded result without the semicolon, comment, and existing-limit bugs that plague string concatenation.
• Cost — one parse and a few tree traversals per query, entirely in the application before any engine is touched, versus the cost of a destructive statement or an unbounded scan reaching production. The eliminated cost is a DROP, a full-table pull, or a data leak — O(nodes) validation against O(catastrophe) at execution.

Data validation
Topic — data-validation
Data-validation problems on parsing and allowlisting input

Practice →

Defensive coding Topic — defensive-coding Defensive-coding problems on rejecting unsafe statements

Practice →

---

3. Sandboxing and scoping — read-only roles, RLS, isolated compute

Run generated SQL where it physically cannot write, see other tenants, or hog production

The mental model in one line: sandboxing is the layer that makes the database itself refuse to do anything dangerous, regardless of what the AI-written SQL says — the query executes as a least-privilege, read-only role that has been granted SELECT on exactly the permitted objects and nothing that can write or alter schema; row-level security (Postgres) or a row access policy (Snowflake/BigQuery) scopes every returned row to the caller's tenant so no query can exfiltrate another tenant's data; the query runs on isolated compute — a read replica or a dedicated warehouse — so it can never contend with production workloads; and a per-query statement timeout lets the engine kill a runaway before it does damage — because static validation can be fooled by an edge case, but a role that lacks the privilege to write cannot be talked into writing. The database is the last line of defence and the only one immune to a cleverer prompt.

Least privilege — a read-only role that cannot write.

• Grant only SELECT. The role backing the AI feature has SELECT on the allowlisted views and no INSERT/UPDATE/DELETE/DDL — so a write that slips past validation still fails at the engine with a permission error.
• Expose views, not raw tables. Grant on a curated ai schema of views, keeping base tables private, so the surface the model can reach is a deliberate contract.
• Default-deny future objects. Set default privileges so new tables are not automatically readable — the allowlist grows only by intention.

Row-level security — scope every read to the caller.

• A policy per table. ENABLE ROW LEVEL SECURITY plus a USING clause keyed on the caller's tenant means every SELECT is silently filtered to that tenant's rows, unbypassable by any query shape.
• Bind the identity. Set the tenant from the authenticated session (a SET/session variable), not from anything the model controls, so the policy cannot be spoofed by the SQL.
• Column masking too. Combine RLS with column grants (or masking policies) so PII is filtered by row and by column.

Isolated compute — never share production.

• A separate warehouse / replica. Point the AI feature at a dedicated Snowflake warehouse or a Postgres read replica, so an expensive AI query never steals resources from production jobs.
• Right-size it. A small warehouse that auto-suspends bounds idle cost; a replica isolates read load from the primary.
• Kill switch. Because it is separate, you can suspend or throttle the AI compute independently during an incident.

Timeouts — let the engine stop a runaway.

• Per-query statement_timeout. Set a short timeout on the role/session so a query that escapes the row/byte caps is still killed by wall-clock time.
• Idle and transaction timeouts. Bound idle-in-transaction and lock waits so a stuck query cannot hold resources.
• Fail closed. A timeout is a rejection, logged and surfaced — not a silent retry loop.

The failure modes senior engineers pre-empt.

• Over-privileged service account. Running AI SQL as an admin/owner role means validation is the only thing between a prompt and a DROP. Mitigation: a dedicated read-only role with minimal grants.
• RLS forgotten on a table. One table without a policy leaks every tenant's rows. Mitigation: default-deny, enable RLS on every exposed relation, and test with a low-privilege session.
• Shared warehouse contention. AI queries on the production warehouse cause noisy-neighbour incidents. Mitigation: a separate, auto-suspending warehouse or replica.

Common interview probes on sandboxing.

• "How do you stop a write that passes validation?" — run as a role with no write privilege; the engine refuses it.
• "How do you prevent cross-tenant reads?" — row-level security keyed on the authenticated session, not the SQL.
• "How do you keep AI queries off production?" — a dedicated warehouse or read replica, auto-suspending.
• "How do you kill a runaway?" — a per-query statement timeout that fails closed.

Worked example — a least-privilege read-only role in Postgres

Detailed explanation. The backstop under static validation: a role that has SELECT on the AI view schema and nothing else. Even if a write reached the engine, this role cannot execute it. Build the role and lock down defaults.

• The role. ai_readonly, NOLOGIN, granted SELECT on the ai schema only.
• The revocation. No privileges on base schemas; no create; default-deny future objects.
• The proof. A write attempt returns a permission error, not a success.

Question. Create a Postgres role that can only read the curated ai views and provably cannot write or reach base tables.

Input.

Grant	Value
Role	ai_readonly (NOLOGIN)
Readable	SELECT on schema ai (views only)
Writable	nothing
Future objects	default-deny

Code.

-- 1. A login-less role the app assumes via SET ROLE after authenticating.
CREATE ROLE ai_readonly NOLOGIN;

-- 2. Read the curated view schema ONLY; base tables stay private.
GRANT USAGE ON SCHEMA ai TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA ai TO ai_readonly;   -- views live in `ai`

-- 3. Explicitly deny everything else: no access to raw data schemas.
REVOKE ALL ON SCHEMA public, analytics FROM ai_readonly;
REVOKE CREATE ON SCHEMA ai FROM ai_readonly;              -- cannot create objects

-- 4. Default-deny FUTURE objects so the surface never grows by accident.
ALTER DEFAULT PRIVILEGES IN SCHEMA ai
  GRANT SELECT ON TABLES TO ai_readonly;                  -- only new VIEWS in `ai`
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  REVOKE ALL ON TABLES FROM ai_readonly;                  -- never analytics tables

-- 5. A tight per-statement timeout for anything this role runs.
ALTER ROLE ai_readonly SET statement_timeout = '5s';

-- Proof: as ai_readonly, a write fails at the ENGINE, not just at validation.
SET ROLE ai_readonly;
SELECT id FROM ai.orders LIMIT 5;      -- OK
DELETE FROM ai.orders WHERE id = 1;    -- ERROR: permission denied for view orders
INSERT INTO analytics.orders ...;      -- ERROR: permission denied for schema analytics

Step-by-step explanation.

1. ai_readonly is NOLOGIN and assumed via SET ROLE only after the application authenticates the user, so the AI feature never holds a directly-loginable, over-privileged credential.
2. The role is granted SELECT on the ai schema of views and nothing else; because base tables live in analytics/public, the model cannot reach raw data even by naming it — the grant is the allowlist, enforced by the engine.
3. REVOKE CREATE and the revocations on other schemas mean the role cannot create, write, or read outside its lane; a write that slipped past static validation returns permission denied instead of executing.
4. ALTER DEFAULT PRIVILEGES makes the posture default-deny for the future: new views in ai are readable, but new tables in analytics are never auto-granted, so the surface only grows when someone deliberately curates a view.
5. statement_timeout = '5s' on the role means every query this feature runs is bounded by wall-clock time at the engine, so a runaway is killed even if it passed every other gate — the timeout is the last backstop under the last backstop.

Output.

Operation as ai_readonly	Result
SELECT ... FROM ai.orders	allowed
DELETE FROM ai.orders	permission denied
INSERT INTO analytics.orders	permission denied (schema)
a 30s runaway query	killed at 5s (timeout)

Rule of thumb. Run AI-written SQL as a dedicated, login-less role granted SELECT on a curated view schema and nothing else, with default privileges set to deny future objects and a short statement_timeout. The role is the guardrail the model cannot argue with — a write it cannot perform is a write it will never perform.

Worked example — row-level security scoping reads to the caller's tenant

Detailed explanation. A read-only role still reads every tenant's rows unless you scope it. Row-level security filters every query to the caller's tenant, keyed on the authenticated session rather than anything the SQL can set — so no query shape can exfiltrate another tenant's data.

• The policy. USING (tenant_id = current_setting('app.tenant')).
• The binding. The app sets app.tenant from the verified session, not from the model.
• The guarantee. The predicate is AND-ed onto every read, unbypassable.

Question. Ensure an AI query submitted by tenant acme can never return tenant globex's rows, enforced in the database.

Input.

Piece	Value
Protected table	ai.orders (has tenant_id)
Policy predicate	tenant_id = app.tenant
Identity source	server-set session var, verified
Bypassable by SQL?	no

Code.

-- 1. Turn on RLS and default-deny the base relation.
ALTER TABLE analytics.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE analytics.orders FORCE ROW LEVEL SECURITY;   -- applies even to table owner

-- 2. A policy that scopes every read to the caller's tenant.
CREATE POLICY ai_tenant_read ON analytics.orders
  FOR SELECT
  USING (tenant_id = current_setting('app.tenant', true));

-- 3. The curated view the AI role reads (security_invoker so the CALLER's policy applies).
CREATE VIEW ai.orders WITH (security_invoker = true) AS
  SELECT id, tenant_id, region, total, created_at FROM analytics.orders;

# 4. The app sets the tenant from the VERIFIED session — never from the model's SQL.
def run_ai_query(conn, sql: str, session):
    with conn.cursor() as cur:
        cur.execute("SET ROLE ai_readonly;")
        # set_config with is_local=true scopes the setting to this transaction.
        cur.execute("SELECT set_config('app.tenant', %s, true);", (session.tenant_id,))
        cur.execute(sql)                     # RLS AND-s tenant_id = <session tenant>
        return cur.fetchall()

Step-by-step explanation.

1. ENABLE plus FORCE ROW LEVEL SECURITY means the policy applies to every reader including the owner, so there is no role for which the filter is silently skipped.
2. The ai_tenant_read policy ANDs tenant_id = current_setting('app.tenant') onto every SELECT, so the predicate is part of the query plan itself — no WHERE, UNION, or subquery the model writes can widen the scope.
3. The ai.orders view uses security_invoker = true, so the policy is evaluated as the calling role rather than the view owner — the caller's tenant scope applies, not a privileged owner's.
4. The application sets app.tenant from session.tenant_id, which came from the verified auth token, using a transaction-local set_config; the model's SQL has no way to change this session variable, so it cannot spoof another tenant.
5. The result: an identical query submitted by acme and globex returns each caller's own rows and nothing of the other's — tenant isolation is a property of the database, not of the correctness of the generated SQL.

Output.

Caller (session tenant)	Query	Rows returned
acme	SELECT id FROM ai.orders	only acme's rows
globex	SELECT id FROM ai.orders	only globex's rows
acme (adds OR 1=1)	SELECT id FROM ai.orders WHERE tenant_id='globex' OR 1=1	still only acme's (RLS AND-ed)
no session tenant set	any read	none (predicate is NULL/false)

Rule of thumb. Enforce tenant isolation with row-level security keyed on a server-set session variable, and FORCE it so even the owner is scoped. Because the predicate is AND-ed onto every read and the identity comes from the verified session — never the SQL — no generated query can exfiltrate another tenant's data.

Worked example — isolated compute and a statement timeout in Snowflake

Detailed explanation. On a warehouse, the two sandbox controls are isolation and a timeout: a dedicated, auto-suspending warehouse keeps AI queries off production, and a statement timeout kills a runaway. Configure both for a Snowflake AI feature.

• The warehouse. A small, auto-suspending AI_WH used only by the AI role.
• The timeout. STATEMENT_TIMEOUT_IN_SECONDS on the warehouse and role.
• The isolation. Production warehouses are untouched by AI load.

Question. Give an AI feature its own bounded, auto-suspending Snowflake compute with a hard per-statement timeout, separate from production.

Input.

Control	Setting
Warehouse	AI_WH, XS, auto-suspend 60s
Timeout	STATEMENT_TIMEOUT_IN_SECONDS = 30
Role	AI_READONLY uses only AI_WH
Production	separate warehouses, unaffected

Code.

-- 1. A small, isolated warehouse that auto-suspends when idle (bounds idle cost).
CREATE WAREHOUSE IF NOT EXISTS AI_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60            -- seconds idle before it parks
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE
  STATEMENT_TIMEOUT_IN_SECONDS = 30       -- hard per-statement wall-clock cap
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 10;

-- 2. A read-only role for the AI feature; grant it ONLY the AI warehouse + views.
CREATE ROLE IF NOT EXISTS AI_READONLY;
GRANT USAGE ON WAREHOUSE AI_WH TO ROLE AI_READONLY;      -- cannot use PROD_WH at all
GRANT USAGE ON SCHEMA analytics.ai TO ROLE AI_READONLY;
GRANT SELECT ON ALL VIEWS IN SCHEMA analytics.ai TO ROLE AI_READONLY;

-- 3. Belt and braces: also cap the timeout on the role itself.
ALTER ROLE AI_READONLY SET STATEMENT_TIMEOUT_IN_SECONDS = 30;

-- 4. A row access policy scopes reads to the caller's tenant (Snowflake RLS).
CREATE ROW ACCESS POLICY ai_tenant_policy AS (tenant_id STRING) RETURNS BOOLEAN ->
  tenant_id = current_account_session_tenant();          -- from the session, not the SQL
ALTER TABLE analytics.orders ADD ROW ACCESS POLICY ai_tenant_policy ON (tenant_id);

Step-by-step explanation.

1. AI_WH is an XS warehouse dedicated to the AI feature; because the AI_READONLY role is granted USAGE on AI_WH only and not on production warehouses, an AI query physically cannot run on production compute — isolation by grant, not by convention.
2. AUTO_SUSPEND = 60 and INITIALLY_SUSPENDED = TRUE mean the warehouse parks when idle and resumes on demand, so isolation does not cost a permanently-running warehouse — the bill tracks actual AI usage.
3. STATEMENT_TIMEOUT_IN_SECONDS = 30 on both the warehouse and the role caps every statement's wall-clock time, so a query that escaped the row and byte caps is still killed at 30 seconds — a runaway cannot run indefinitely.
4. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 10 bounds how long a query waits for a slot, so a burst of AI queries fails fast instead of piling up — backpressure rather than an unbounded queue.
5. The row access policy adds Snowflake's equivalent of RLS, scoping every read of orders to the session's tenant, so isolation of compute is paired with isolation of data — the AI feature can neither hog production nor read across tenants.

Output.

Concern	Without isolation	With AI_WH + timeout
Production contention	AI query slows prod jobs	AI runs on its own warehouse
Idle cost	warehouse always on	auto-suspends at 60s idle
Runaway query	runs until it finishes	killed at 30s
Cross-tenant read	possible	blocked by row access policy

Rule of thumb. Give the AI feature its own auto-suspending warehouse (or read replica) that its role is the only thing granted to use, and cap STATEMENT_TIMEOUT_IN_SECONDS on both the warehouse and the role. Isolated compute stops noisy-neighbour incidents; the timeout stops runaways; a row access policy stops cross-tenant reads.

Senior interview question on sandboxing AI-generated SQL

A senior interviewer might ask: "Static validation can be fooled by an edge case, so design the database-side sandbox that makes AI-written SQL safe even if a bad query gets through: how the query runs at least privilege so a write is refused by the engine, how you guarantee it can only ever see the caller's tenant, how you keep it off production compute, and how a runaway is killed — all enforced in the data layer, not the application."

Solution Using a read-only role, forced RLS, isolated compute, and a statement timeout

-- 1. Least-privilege role: SELECT on curated views only; no writes, no base tables.
CREATE ROLE ai_readonly NOLOGIN;
GRANT USAGE ON SCHEMA ai TO ai_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA ai TO ai_readonly;
REVOKE ALL ON SCHEMA public, analytics FROM ai_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics REVOKE ALL ON TABLES FROM ai_readonly;
ALTER ROLE ai_readonly SET statement_timeout = '5s';   -- runaway kill switch

-- 2. Tenant isolation forced in the database, keyed on the session (not the SQL).
ALTER TABLE analytics.orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE analytics.orders FORCE ROW LEVEL SECURITY;
CREATE POLICY ai_tenant_read ON analytics.orders FOR SELECT
  USING (tenant_id = current_setting('app.tenant', true));

-- 3. The curated view the role reads, running RLS as the caller.
CREATE VIEW ai.orders WITH (security_invoker = true) AS
  SELECT id, tenant_id, region, total, created_at FROM analytics.orders;

# 4. Execute on an ISOLATED read replica, as the read-only role, tenant-scoped.
def run_ai_query(sql: str, session):
    conn = connect(REPLICA_DSN)            # a replica, never the primary/production
    with conn, conn.cursor() as cur:
        cur.execute("SET ROLE ai_readonly;")
        cur.execute("SELECT set_config('app.tenant', %s, true);", (session.tenant_id,))
        cur.execute("SET LOCAL statement_timeout = '5s';")
        cur.execute(sql)                   # validated upstream; DB enforces the rest
        return cur.fetchmany(1000)         # never fetch unbounded even if LIMIT slipped

Step-by-step trace.

Layer	Control	Guarantee
Privilege	ai_readonly, SELECT-only	a write is refused by the engine
Isolation (data)	forced RLS on tenant_id	only the caller's rows are returned
Isolation (compute)	run on a read replica	production is never contended
Identity	server-set app.tenant	the SQL cannot spoof the tenant
Runaway	statement_timeout = 5s	a long query is killed
Fetch	fetchmany(1000)	client-side row bound backstop

After deployment, an AI query assumes ai_readonly, which can only SELECT from the ai view schema; forced row-level security ANDs the caller's tenant onto every read using a session variable the SQL cannot set; execution happens on a read replica so production compute is untouched; a 5-second statement timeout kills any runaway; and the client fetches at most 1,000 rows regardless. A destructive statement, a cross-tenant read, a production-contending scan, and an infinite query are each stopped by the database, independent of whether static validation caught them first.

Output:

Metric	Over-privileged run	Sandboxed run
Write that passed validation	executes	permission denied
Cross-tenant read	returns other rows	filtered by forced RLS
Production contention	AI load hits primary	isolated on replica
Runaway query	runs to completion	killed at 5s
Unbounded fetch	client OOMs	capped at 1,000 rows

Why this works — concept by concept:

• Least-privilege role — granting only SELECT on curated views means the engine itself refuses writes and off-limits reads, so the sandbox holds even when an upstream check is fooled — the model cannot use a privilege it was never given.
• Forced row-level security — a policy keyed on a server-set session variable and FORCEd for every role scopes every read to the caller's tenant in the query plan, so no generated SQL shape can widen its own scope.
• Isolated compute — running on a read replica or a dedicated warehouse keeps AI queries from contending with production, converting a potential noisy-neighbour outage into, at worst, a slow AI response.
• Statement timeout + fetch cap — an engine-enforced wall-clock limit kills a runaway and a client-side fetchmany bounds rows, giving two independent stops for the "it ran but never ends / never fits" failure.
• Cost — a handful of grants, one policy per table, and an auto-suspending replica, versus the cost of a leaked tenant, a corrupted table, or a production outage. The eliminated cost is a breach or an incident — O(policies) of setup against O(disaster) at runtime.

Design
Topic — design
Design problems on least-privilege access and isolation

Practice →

Data validation Topic — data-validation Data-validation problems on row scoping and access control

Practice →

---

4. Cost and row caps — dry-run estimates, byte and row caps

Estimate the scan before you run it, then cap bytes and rows at the engine

The mental model in one line: cost caps protect the bill and the cluster from a query that is perfectly valid but ruinously expensive — you estimate before executing by asking the engine for a dry-run (BigQuery returns the exact bytes a query would scan without running it), reject anything whose estimate exceeds a cost cap, then set hard engine-level ceilings so even an under-estimate cannot run away: maximum_bytes_billed fails a BigQuery job that would exceed its byte budget, a Snowflake resource monitor suspends the warehouse at a credit quota, a statement timeout bounds wall-clock time, and a row limit bounds the result set — because an AI-written query has no intuition for cost, and a single missing partition filter or accidental cross join can turn a one-cent question into a four-figure scan. Estimate, cap hard, bound the rows — three independent brakes on spend.

Estimate before executing.

• Dry-run the query. BigQuery's dry-run returns total_bytes_processed without running the query or being billed — the cheapest possible way to know a query's cost.
• Gate on the estimate. Reject (or escalate to approval) any query whose estimate exceeds a per-query byte cap; the number decides, not the invoice.
• Explain the rejection. Surface the estimate so a user (or the model) understands why a query was too big and can narrow it.

Hard caps at the engine.

• maximum_bytes_billed. Set it on every BigQuery job so a query that would exceed the byte budget fails instead of running — a hard ceiling under the estimate, in case the estimate was low.
• Resource monitors. A Snowflake resource monitor suspends the warehouse at a credit threshold, so a runaway spend across many queries is capped at the account/warehouse level.
• Timeouts. A statement timeout bounds a single query's time, complementing the byte cap for engines that meter by time rather than bytes.

Result-row caps.

• Cap rows returned. Even an in-budget scan can return millions of rows; a LIMIT (injected in static validation) plus a client-side fetchmany ceiling bounds what reaches the application.
• Cap payload size. Bound the serialised result bytes so a wide row set cannot OOM the client or the transport.
• Paginate for legitimate bulk. If a large result is genuinely needed, force keyset pagination rather than one unbounded pull.

The failure modes senior engineers pre-empt.

• Estimating a partition-blind query. A query without a partition filter dry-runs as a full-table scan; the estimate is correct but huge. Mitigation: require partition filters on large tables, or reject the estimate.
• No monthly ceiling. Per-query caps still allow a loop of medium queries to blow the monthly budget. Mitigation: a resource monitor / project-level quota as a backstop.
• Unbounded result set. A valid, cheap scan can still return everything. Mitigation: a row cap and a payload cap, always.

Common interview probes on cost caps.

• "How do you know a query's cost before running it?" — a dry-run returns bytes scanned without executing.
• "What stops an under-estimated query?" — maximum_bytes_billed fails the job at a hard byte ceiling.
• "How do you cap spend across many queries?" — a resource monitor / project quota suspends at a credit/byte threshold.
• "How do you bound the result?" — an injected LIMIT plus a client-side row/payload cap.

Worked example — a BigQuery dry-run cost gate

Detailed explanation. The estimate-first gate: run the query as a dry-run, read the bytes it would scan, and reject if that exceeds the cap — all without executing or being billed. This turns cost from a surprise into a pre-condition.

• The dry-run. dry_run=True returns total_bytes_processed, runs nothing.
• The cap. A per-query byte budget (e.g. 50 GB).
• The verdict. Under cap → allow; over → reject with the estimate.

Question. Reject any AI-generated BigQuery query whose estimated scan exceeds a byte cap, before it runs.

Input.

Piece	Value
Cap	50 GB (50 * 2**30 bytes)
Estimate source	dry-run total_bytes_processed
Billed by dry-run?	no (free)
Over-cap action	reject (or escalate to approval)

Code.

from google.cloud import bigquery

BYTE_CAP = 50 * 2**30      # 50 GiB per query

def dry_run_bytes(client: bigquery.Client, sql: str) -> int:
    # dry_run compiles + estimates WITHOUT executing or billing.
    cfg = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
    job = client.query(sql, job_config=cfg)
    return job.total_bytes_processed        # exact bytes the real query would scan

def cost_gate(client: bigquery.Client, sql: str) -> None:
    est = dry_run_bytes(client, sql)
    if est > BYTE_CAP:
        gib = est / 2**30
        raise GuardrailError(
            f"query would scan {gib:.1f} GiB, over the {BYTE_CAP/2**30:.0f} GiB cap")
    # else: safe to execute (still with a hard cap — see next example)

Step-by-step explanation.

1. QueryJobConfig(dry_run=True) tells BigQuery to compile the query and compute its scan size without running it; the call is free and returns in milliseconds, so estimating costs effectively nothing.
2. job.total_bytes_processed is the exact number of bytes the real query would read given current partitioning and clustering — not a guess, but the same figure BigQuery would bill on.
3. use_query_cache=False ensures the estimate reflects a genuine scan rather than a cached result, so the gate is conservative and does not under-estimate a cold query.
4. If the estimate exceeds BYTE_CAP, the query is rejected with a human-readable size, so the caller learns why — a partition-blind or cross-join query reveals itself as hundreds of GiB before a cent is spent.
5. Because the dry-run happens before execution, a query that would scan a petabyte never runs; the cost is caught by a number in the application, which is the entire point of estimating first.

Output.

Query	Dry-run estimate	Verdict
SELECT id FROM t WHERE _PARTITIONDATE = '2026-08-01'	1.2 GiB	allowed
SELECT * FROM t (no partition filter)	480 GiB	rejected (over cap)
SELECT a.* FROM t a CROSS JOIN big b	9 TiB	rejected (over cap)
SELECT count(*) FROM t WHERE region='EU'	12 GiB	allowed

Rule of thumb. Dry-run every AI-generated query first and reject on the estimated bytes — it is free, exact, and catches the expensive query before it runs. Surface the estimate in the rejection so a partition-blind or cross-join query can be narrowed rather than silently blocked.

Worked example — a hard byte ceiling with maximum_bytes_billed

Detailed explanation. A dry-run estimate can occasionally be lower than reality, or a query can be submitted without the gate. maximum_bytes_billed is the engine-enforced backstop: BigQuery fails any job that would scan more than the ceiling, so there is a hard wall under the estimate.

• The setting. maximum_bytes_billed on the job config.
• The behaviour. The job errors if it would exceed the ceiling — it does not run partially.
• The layering. Estimate gate + hard cap = two independent brakes.

Question. Guarantee that even an ungated or under-estimated query cannot scan more than a hard byte ceiling, enforced by BigQuery itself.

Input.

Piece	Value
Hard ceiling	50 GiB (maximum_bytes_billed)
Over-ceiling behaviour	job fails (no partial run)
Applies when	every executed job
Relationship to dry-run	backstop under the estimate

Code.

from google.cloud import bigquery

HARD_CAP = 50 * 2**30      # 50 GiB — the engine will REFUSE to bill past this

def run_capped(client: bigquery.Client, sql: str):
    cfg = bigquery.QueryJobConfig(
        maximum_bytes_billed=HARD_CAP,     # BigQuery FAILS the job if it would exceed this
        use_query_cache=True,
        labels={"source": "ai_sql"},       # tag for cost attribution in billing
    )
    job = client.query(sql, job_config=cfg)
    try:
        return list(job.result(max_results=10_000))   # row cap on top of the byte cap
    except bigquery.exceptions.BadRequest as e:
        # Raised when the scan would exceed maximum_bytes_billed.
        raise GuardrailError(f"job exceeded byte ceiling: {e}")

Step-by-step explanation.

1. maximum_bytes_billed=HARD_CAP instructs BigQuery to refuse any job whose scan would exceed 50 GiB; the job fails up front rather than running partially, so there is no way to be billed past the ceiling.
2. This is a backstop under the dry-run gate: if the estimate was somehow low, or a code path skipped the gate, the engine still enforces the hard ceiling — two independent brakes on the same failure.
3. labels={"source": "ai_sql"} tags every AI query in the billing export, so AI-feature spend is attributable and monitorable separately from the rest of the warehouse — you can see and alert on it.
4. job.result(max_results=10_000) layers a row cap on top of the byte cap, so even a cheap scan that returns many rows is bounded to 10,000 at the API — bytes and rows are different limits and both matter.
5. The BadRequest handler converts the engine's cap violation into a GuardrailError, so an over-ceiling query becomes an explainable rejection in the same shape as every other guardrail failure.

Output.

Scenario	Without hard cap	With maximum_bytes_billed
gate skipped, 900 GiB scan	runs, billed	job fails immediately
dry-run under-estimated	runs over budget	capped at ceiling
cheap scan, 5M rows	5M returned	capped at 10,000
cost attribution	mixed in	labelled ai_sql

Rule of thumb. Always set maximum_bytes_billed on AI-query jobs as a hard ceiling under the dry-run estimate, cap max_results for a row bound, and label jobs for cost attribution. The estimate gate catches the expected expensive query; the hard cap catches the one that slipped through.

Worked example — a Snowflake resource monitor and row cap

Detailed explanation. Per-query caps do not stop a loop of medium queries from blowing the budget. A Snowflake resource monitor suspends the warehouse at a credit quota — an account-level backstop — and a statement timeout plus a row cap bound each query. Configure all three.

• The monitor. Suspend AI_WH at a daily credit quota.
• The timeout. Already set per statement in the sandbox layer.
• The row cap. LIMIT (from validation) plus a ROWS_PER_RESULTSET guard.

Question. Cap total AI-feature spend across many queries with a resource monitor, and bound each query's rows and time.

Input.

Control	Setting
Daily credit quota	20 credits → suspend
Warning at	75% (notify)
Per-statement timeout	30 s
Row cap	ROWS_PER_RESULTSET + LIMIT

Code.

-- 1. A resource monitor that SUSPENDS the AI warehouse at a daily credit quota.
CREATE RESOURCE MONITOR ai_daily_cap WITH
  CREDIT_QUOTA = 20                 -- credits per...
  FREQUENCY = DAILY
  START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 75 PERCENT DO NOTIFY                 -- warn the team at 75%
    ON 100 PERCENT DO SUSPEND               -- stop new queries at 100%
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;    -- kill running queries at 110%

ALTER WAREHOUSE AI_WH SET RESOURCE_MONITOR = ai_daily_cap;

-- 2. Per-query bounds (belt-and-braces with static validation's injected LIMIT).
ALTER WAREHOUSE AI_WH SET
  STATEMENT_TIMEOUT_IN_SECONDS = 30
  ROWS_PER_RESULTSET = 100000;      -- hard row ceiling per result set

-- 3. Attribute AI spend with a query tag for later cost analysis.
ALTER SESSION SET QUERY_TAG = 'ai_sql_feature';

Step-by-step explanation.

1. ai_daily_cap sets a daily credit quota on the AI warehouse, so the cumulative spend of many queries — not just one — is bounded; a loop of AI requests cannot silently run up the bill past 20 credits a day.
2. The tiered triggers escalate gracefully: notify at 75% so the team investigates, SUSPEND at 100% so no new queries start, and SUSPEND_IMMEDIATE at 110% so even running queries are killed if spend overshoots — warn, stop, kill.
3. Binding the monitor to AI_WH scopes the cap to the AI feature's isolated warehouse, so exceeding the AI budget suspends only AI queries and never touches production warehouses — isolation makes the cap safe to enforce hard.
4. STATEMENT_TIMEOUT_IN_SECONDS and ROWS_PER_RESULTSET bound each query's time and rows at the warehouse level, complementing the LIMIT static validation injected — three layers all agreeing that a single query is bounded.
5. QUERY_TAG labels every AI query so its cost and volume are visible in QUERY_HISTORY, closing the loop: you can monitor the feature's spend, tune the quota, and prove the caps are working.

Output.

Spend / query	Without monitor	With ai_daily_cap
1 huge query	runs	timeout + row cap bound it
500 medium queries/day	unbounded credits	suspended at 20 credits
overshoot to 110%	keeps running	running queries killed
cost visibility	mixed	tagged ai_sql_feature

Rule of thumb. Cap cumulative AI spend with a resource monitor (notify → suspend → suspend-immediate), bound each query with a statement timeout and a row-per-result-set ceiling, and tag queries for attribution. Per-query caps stop one expensive query; the monitor stops a thousand medium ones.

Senior interview question on cost-capping AI-generated queries

A senior interviewer might ask: "An AI feature lets users ask questions that become warehouse queries, and one bad question can scan a petabyte or loop into a huge bill. Design the cost controls: how you know a query's cost before running it, what stops an under-estimated or ungated query at the engine, how you cap total spend across many queries, and how you keep even a cheap query from returning millions of rows — all tied to a budget, not a hope."

Solution Using a dry-run gate, a hard byte ceiling, a resource monitor, and row caps

# 1. Estimate-first gate: dry-run returns exact bytes; reject over the per-query cap.
from google.cloud import bigquery

PER_QUERY_CAP = 50 * 2**30        # 50 GiB estimate cap
HARD_CEILING  = 50 * 2**30        # engine-enforced backstop

def cost_guard_and_run(client, sql, on_over_cap):
    # a) free estimate, no execution
    est = client.query(
        sql, bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
    ).total_bytes_processed
    if est > PER_QUERY_CAP:
        return on_over_cap(est)   # escalate to approval OR reject

    # b) execute WITH a hard byte ceiling and a row cap
    cfg = bigquery.QueryJobConfig(
        maximum_bytes_billed=HARD_CEILING,       # job fails if it would exceed this
        labels={"source": "ai_sql"},
    )
    return list(client.query(sql, cfg).result(max_results=10_000))   # row cap

-- 2. Account-level backstop: a resource monitor caps CUMULATIVE spend.
CREATE RESOURCE MONITOR ai_daily_cap WITH
  CREDIT_QUOTA = 20 FREQUENCY = DAILY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 75 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND
           ON 110 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE AI_WH SET RESOURCE_MONITOR = ai_daily_cap
  STATEMENT_TIMEOUT_IN_SECONDS = 30 ROWS_PER_RESULTSET = 100000;

Step-by-step trace.

Brake	Control	Stops
Estimate	dry-run total_bytes_processed	the expected expensive query
Hard byte cap	maximum_bytes_billed	an under-estimated / ungated query
Cumulative cap	resource monitor (credits)	a loop of medium queries
Time cap	statement timeout	a slow runaway
Row cap	LIMIT + max_results	a cheap-but-huge result set

After deployment, every AI query is first dry-run for a free, exact byte estimate and rejected (or escalated to approval) if it exceeds the per-query cap; whatever executes carries maximum_bytes_billed as a hard ceiling and max_results as a row cap; a resource monitor suspends the warehouse if cumulative daily spend crosses the credit quota; and a statement timeout bounds each query's time. No single query can scan a petabyte, no loop can blow the daily budget, and no result can flood the client — each failure has an independent brake.

Output:

Metric	Uncapped AI feature	Cost-capped
Cost known before run	no (bill is the signal)	yes (dry-run estimate)
Under-estimated query	runs, billed	fails at hard ceiling
Daily spend	unbounded	capped by resource monitor
Result rows	unbounded	capped (LIMIT + max_results)
Cost attribution	mixed	labelled / tagged ai_sql

Why this works — concept by concept:

• Dry-run estimate — asking the engine for exact bytes scanned without executing turns cost into a pre-condition, so the expected expensive query is rejected by a free number rather than discovered on the invoice.
• Hard byte ceiling — maximum_bytes_billed makes the engine refuse a job that would overrun, giving a backstop under the estimate for the query that was under-estimated or skipped the gate.
• Resource monitor — a cumulative credit quota with tiered notify/suspend/kill triggers caps the spend of many queries, closing the loop-of-medium-queries hole that per-query caps leave open.
• Row and time caps — an injected LIMIT, a max_results fetch bound, and a statement timeout keep even a cheap, valid query from returning millions of rows or running forever.
• Cost — a free dry-run and a few engine settings per query, versus a single unbounded scan or a runaway loop. The eliminated cost is a four-figure query or a blown monthly budget — O(1) estimation against O(petabyte) execution.

Optimization
Topic — optimization
Optimization problems on scan cost and query bounds

Practice →

Defensive coding Topic — defensive-coding Defensive-coding problems on caps and safe defaults

Practice →

---

5. Approval gates and audit — human review, logging, rate limits

Auto-run the safe, route the risky to a human, and log every query with who ran it

The mental model in one line: approval gates and audit are the accountability layer — you risk-classify every AI-written query and split it three ways: auto-run the provably safe (read-only, allowlisted, in-budget), route the risky-but-sometimes-legitimate (a write, a sensitive table, a high-cost scan) to a human for human-in-the-loop approval, and hard-deny the forbidden; you write an append-only audit log of the prompt, the generated SQL, the verdict, the estimated cost, the rows returned, and the identity behind every query so the feature is attributable and replayable after an incident; and you rate-limit per user and role so an agent loop or a runaway retriever cannot turn the feature into a self-inflicted denial-of-service or a cost incident — because the earlier layers make queries safe, and this layer makes them accountable and bounded over time. Classify by risk, gate the dangerous, log everything, throttle the loop.

Risk-classify every query.

• Three verdicts. Auto-run (safe reads within budget), human-approve (writes, sensitive tables, high cost), deny (blocked identifiers, over hard limits) — a router, not a binary block.
• Signals. Statement type, tables touched (sensitivity tier), estimated bytes, requested rows, and the caller's role all feed the score.
• Avoid approval fatigue. Gating everything trains reviewers to rubber-stamp; gate only the genuinely risky slice so approvals stay meaningful.

Human-in-the-loop approval.

• Who approves. A data owner or on-call approver, with the prompt, the SQL, the estimate, and the reason it was flagged — enough context to decide in seconds.
• What is gated. Anything that writes (if writes are allowed at all), touches a sensitive/PII table, or exceeds a cost band — the small set where a human's judgement adds real safety.
• Time-box it. An unapproved query expires rather than lingering, so a stale approval cannot be replayed later.

Audit everything.

• The record. Timestamp, user, role, prompt, generated SQL, verdict, estimated and actual cost, rows returned, and outcome — for every query, including auto-runs.
• Append-only. The log is immutable and separate from the app database, so it survives and cannot be edited to hide an incident.
• Use it. Feed anomaly detection (a user suddenly scanning sensitive tables), incident review, and cost attribution from the same log.

Rate limits.

• Per user and role. A token bucket per identity so a loop cannot fire thousands of queries a minute.
• Per cost, not just count. Budget by estimated bytes/credits as well as request count, so a few huge queries are throttled like many small ones.
• Fail closed and visible. Over the limit returns a clear 429-style error and is logged, never a silent drop.

The failure modes senior engineers pre-empt.

• Approval fatigue. Gating every query makes humans approve blindly. Mitigation: auto-run the safe majority; gate only the risky slice.
• Unlogged auto-runs. Logging only the gated queries leaves the majority invisible. Mitigation: log every query and verdict, auto-runs included.
• No rate limit on the loop. An agent that retries or fans out can DoS the warehouse or spike cost. Mitigation: per-identity token buckets budgeted by count and cost.

Common interview probes on approval and audit.

• "When does an AI query need a human?" — when it writes, touches sensitive data, or exceeds a cost band.
• "What do you log?" — prompt, SQL, verdict, cost, rows, identity — for every query, append-only.
• "How do you avoid approval fatigue?" — auto-run the safe majority; gate only the risky slice.
• "How do you stop an agent loop from DoS-ing you?" — per-identity rate limits budgeted by count and cost.

Worked example — a risk-scoring router

Detailed explanation. The router decides each query's fate from structural signals: statement type, table sensitivity, estimated cost, and role. Safe reads auto-run; risky queries escalate; forbidden ones deny. This keeps humans focused on the few queries that need judgement.

• The signals. is-write, sensitive-table, estimated bytes, caller role.
• The verdicts. auto-run, human-approve, deny.
• The goal. Auto-run the safe majority; gate only the risky slice.

Question. Classify a query into auto-run, human-approve, or deny from its statement type, tables, estimate, and the caller's role.

Input.

Query profile	Verdict
read, allowlisted, 2 GiB, analyst	auto-run
read, touches payroll (sensitive), 2 GiB	human-approve
read, allowlisted, 120 GiB (high cost)	human-approve
any write	deny (feature is read-only)

Code.

SENSITIVE = {"payroll", "customers_pii", "salaries"}
COST_BAND_BYTES = 100 * 2**30      # >100 GiB needs a human even if under the hard cap

def risk_route(sql: str, tables: set[str], est_bytes: int, is_write: bool) -> str:
    # 1. Forbidden: writes are never allowed in a read-only feature.
    if is_write:
        return "deny"
    # 2. Escalate: sensitive tables need an owner's sign-off.
    if tables & SENSITIVE:
        return "human-approve"
    # 3. Escalate: high-cost (but under the hard cap) needs a human.
    if est_bytes > COST_BAND_BYTES:
        return "human-approve"
    # 4. Everything else — read-only, non-sensitive, in-budget — runs automatically.
    return "auto-run"

Step-by-step explanation.

1. The first check denies any write outright: in a read-only feature there is no legitimate write, so it is a hard deny rather than an escalation — the router never asks a human to approve something the policy forbids.
2. The second check escalates queries that touch a sensitive table (payroll, PII): these may be legitimate but warrant a data owner's judgement, so they route to human-approve rather than auto-running.
3. The third check escalates a query whose estimated cost exceeds a band — even if it is under the hard byte cap, a 120 GiB scan is worth a human glance, so cost is a risk signal, not just a hard limit.
4. Everything that survives — a read, on non-sensitive tables, within the cost band — is auto-run, which is deliberately the majority case so that approvals stay rare and meaningful.
5. The design directly avoids approval fatigue: by auto-running the safe majority and gating only writes, sensitive tables, and big spends, the human queue contains only queries where judgement actually adds safety.

Output.

Query	Signals	Verdict
SELECT region, sum(total) FROM orders ...	read, allowlisted, 2 GiB	auto-run
SELECT * FROM ai.payroll_summary	read, sensitive table	human-approve
SELECT ... FROM events (120 GiB)	read, high cost	human-approve
UPDATE orders SET ...	write	deny

Rule of thumb. Route queries by risk: deny writes, escalate sensitive tables and high-cost scans to a human, and auto-run the safe majority. Gating only the risky slice keeps approvals meaningful — an approver who sees ten real decisions a day stays sharp; one who rubber-stamps a thousand does not.

Worked example — an append-only audit log

Detailed explanation. The audit log is the accountability layer: one immutable record per query capturing who asked what, the SQL, the verdict, and the cost. It powers incident review, anomaly detection, and cost attribution — and must be append-only and separate from the app so it cannot be tampered with.

• The record. user, role, prompt, sql, verdict, est/actual bytes, rows, timestamp.
• The store. An append-only table (no UPDATE/DELETE grant) or a log sink.
• The scope. Every query — auto-runs included, not just gated ones.

Question. Write an append-only audit record for every AI query, capturing enough to attribute and replay it.

Input.

Field	Example
user / role	u_142 / analyst
prompt	"top regions by revenue"
sql	SELECT region, sum(total) ... LIMIT 1000
verdict	auto-run
est_bytes / rows	2 GiB / 843

Code.

-- 1. An append-only audit table: the AI role can INSERT but never UPDATE/DELETE.
CREATE TABLE audit.ai_sql_log (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ts           timestamptz NOT NULL DEFAULT now(),
  user_id      text NOT NULL,
  role         text NOT NULL,
  prompt       text NOT NULL,
  generated_sql text NOT NULL,
  verdict      text NOT NULL,          -- auto-run | human-approve | denied | approved
  est_bytes    bigint,
  actual_bytes bigint,
  rows_returned integer,
  error        text
);
GRANT INSERT ON audit.ai_sql_log TO ai_service;   -- INSERT only
REVOKE UPDATE, DELETE ON audit.ai_sql_log FROM ai_service;  -- immutable to the app

# 2. Log EVERY query — before execution (verdict) and after (cost/rows/errors).
def audit(conn, ctx, sql, verdict, est_bytes, actual=None, rows=None, error=None):
    conn.execute(
        """INSERT INTO audit.ai_sql_log
           (user_id, role, prompt, generated_sql, verdict,
            est_bytes, actual_bytes, rows_returned, error)
           VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)""",
        (ctx.user, ctx.role, ctx.prompt, sql, verdict,
         est_bytes, actual, rows, error),
    )

Step-by-step explanation.

1. The ai_sql_log table captures the full context of each query — identity, prompt, generated SQL, verdict, and both estimated and actual cost — which is exactly what an incident review needs to answer "who ran what, and what did it cost?"
2. GRANT INSERT with REVOKE UPDATE, DELETE makes the log append-only to the application: the AI service can add records but cannot alter or erase them, so the log cannot be doctored to hide a bad query after the fact.
3. Logging happens for every query, not just gated ones: an auto-run is recorded with its verdict and cost too, so the majority of activity is visible rather than invisible — the common mistake is logging only the exceptions.
4. Capturing both est_bytes and actual_bytes lets you detect estimate drift and cost anomalies (a query that estimated small but scanned large), feeding both cost attribution and alerting.
5. Because the log is a separate audit schema and immutable to the app, it can be shipped to a SIEM or warehouse for anomaly detection — a user suddenly querying sensitive tables, or a spike in denied queries, surfaces from the same record.

Output.

Query event	Logged fields
auto-run read	user, role, prompt, sql, auto-run, est/actual bytes, rows
gated → approved	...approved, approver id, est bytes
denied write	...denied, error reason
execution error	...error text, rows NULL

Rule of thumb. Write one immutable, append-only audit record per query — prompt, SQL, verdict, cost, rows, and identity — for every query including auto-runs, in a store the app can insert to but never edit. The log is the accountability layer: without it, an AI-SQL incident is unattributable and unreplayable.

Worked example — a per-user token-bucket rate limiter

Detailed explanation. The earlier layers make each query safe; the rate limiter stops a flood of them. A per-identity token bucket caps queries per minute, and budgeting by estimated cost as well as count stops a few huge queries from slipping through a count-only limit.

• The bucket. N tokens per user, refilled at a fixed rate.
• The cost budget. Also debit estimated bytes, so cost is bounded, not just count.
• The failure. Over the limit → a clear, logged rejection.

Question. Rate-limit AI queries per user by both request count and estimated cost, failing closed when either budget is exhausted.

Input.

Budget	Limit
Requests	30 / minute / user
Estimated bytes	200 GiB / minute / user
Refill	continuous (token bucket)
Over limit	reject (429), logged

Code.

import time

class TokenBucket:
    def __init__(self, capacity: float, refill_per_sec: float):
        self.capacity = capacity
        self.tokens = capacity
        self.refill = refill_per_sec
        self.ts = time.monotonic()

    def take(self, cost: float) -> bool:
        now = time.monotonic()
        self.tokens = min(self.capacity, self.tokens + (now - self.ts) * self.refill)
        self.ts = now
        if self.tokens >= cost:
            self.tokens -= cost
            return True
        return False        # budget exhausted -> reject

# Per user: one bucket for request COUNT, one for estimated BYTES.
def make_limits():
    return {
        "count": TokenBucket(capacity=30, refill_per_sec=30/60),          # 30/min
        "bytes": TokenBucket(capacity=200*2**30, refill_per_sec=200*2**30/60),  # 200 GiB/min
    }

def rate_check(limits, est_bytes: int) -> None:
    if not limits["count"].take(1):
        raise GuardrailError("rate limit: too many queries this minute")
    if not limits["bytes"].take(est_bytes):
        raise GuardrailError("rate limit: query-cost budget exhausted this minute")

Step-by-step explanation.

1. The TokenBucket refills continuously at a fixed rate and allows a request only if it has enough tokens, giving a smooth per-identity limit rather than a hard reset every minute — bursts are allowed up to the capacity, then throttled.
2. Each user gets two buckets: one debited one token per request (a count limit) and one debited the query's estimated bytes (a cost limit), so both "too many queries" and "too expensive a mix" are bounded.
3. rate_check takes from the count bucket first, then the cost bucket, and raises a GuardrailError the moment either is exhausted — failing closed, so an over-limit user is rejected rather than served.
4. Budgeting by estimated bytes closes the hole a count-only limiter leaves: 30 tiny queries and 3 enormous ones both matter, and the byte budget catches the latter that a request count would wave through.
5. Because the rejection is a GuardrailError, it is logged in the audit trail like any other verdict, so a user or agent hitting the limit is visible — a sudden wall of rate-limit rejections is itself an anomaly signal (an agent stuck in a loop).

Output.

Activity in one minute	Count budget	Byte budget	Result
10 small queries	ok	ok	all run
40 small queries	exhausted at 30	ok	31st rejected
4 × 60 GiB queries	ok	exhausted at ~3.3	4th rejected (cost)
agent loop (1000/min)	exhausted fast	—	throttled + logged

Rule of thumb. Rate-limit per identity by both request count and estimated cost with token buckets, and fail closed with a logged rejection. Budgeting by cost as well as count is what stops a handful of huge queries — or an agent loop — from becoming a DoS or a bill spike that a count-only limit would miss.

Senior interview question on approval gates and audit for AI SQL

A senior interviewer might ask: "You have validation, sandboxing, and cost caps on your AI-SQL feature. Now design the accountability layer: how you decide which queries a human must approve without drowning reviewers, what you log for every query so an incident is attributable and replayable, and how you stop an agent loop from turning the feature into a denial-of-service or a runaway bill — and explain how each keeps the feature governable at scale."

Solution Using a risk router, human-in-the-loop approval, an append-only log, and cost-aware rate limits

# 1. Risk router + rate limit + audit, composed into one gate.
def approval_gate(ctx, sql, tables, est_bytes, is_write):
    # a) throttle per identity (count AND cost) — fail closed, logged.
    try:
        rate_check(ctx.limits, est_bytes)
    except GuardrailError as e:
        audit(ctx.conn, ctx, sql, "rate-limited", est_bytes, error=str(e))
        raise

    # b) classify by risk.
    verdict = risk_route(sql, tables, est_bytes, is_write)   # auto-run|human-approve|deny
    audit(ctx.conn, ctx, sql, verdict, est_bytes)            # log the verdict for EVERY query

    if verdict == "deny":
        raise GuardrailError("query denied by policy")
    if verdict == "human-approve":
        return enqueue_for_review(ctx, sql, est_bytes)       # time-boxed; returns a ticket
    return "auto-run"                                        # safe: proceed to sandboxed run

-- 2. Human-in-the-loop queue: a time-boxed approval record (expires if not actioned).
CREATE TABLE audit.ai_approvals (
  ticket      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  requested_by text NOT NULL,
  generated_sql text NOT NULL,
  est_bytes   bigint,
  reason      text NOT NULL,             -- 'sensitive table' | 'high cost'
  status      text NOT NULL DEFAULT 'pending',   -- pending|approved|rejected|expired
  approver    text,
  created_at  timestamptz DEFAULT now(),
  expires_at  timestamptz DEFAULT now() + interval '15 minutes'
);

# 3. The flow, end to end:
query -> rate_check (count+cost)     -> over? 429 + logged
      -> risk_route
           deny          -> logged + rejected
           human-approve -> ticket (15-min expiry); approver sees prompt+sql+cost
           auto-run      -> sandboxed execution (read-only role, RLS, timeout)
      -> audit(prompt, sql, verdict, est/actual cost, rows, identity)   # ALWAYS

Step-by-step trace.

Stage	Control	Effect
Throttle	per-identity token buckets (count + cost)	an agent loop is capped and logged
Classify	risk_route	writes deny, sensitive/high-cost escalate, rest auto-run
Gate	time-boxed approval ticket	a human signs off risky queries; stale tickets expire
Audit	append-only log, every query	attributable + replayable after an incident
Execute	sandboxed run (only if auto-run/approved)	earlier layers still apply

After deployment, every query is first rate-checked by count and cost and rejected-with-a-log if a budget is exhausted; a risk router denies writes, escalates sensitive-table and high-cost reads to a time-boxed human approval, and auto-runs the safe majority; every query and verdict — including auto-runs — is written to an append-only audit log; and only auto-run or human-approved queries proceed to sandboxed execution. Reviewers see only the risky slice, an agent loop is throttled and visible, and any query can be attributed and replayed from the log.

Output:

Metric	No approval layer	With approval + audit
Risky queries reviewed	none / all (fatigue)	only the risky slice
Attribution after incident	none	full (append-only log)
Agent-loop DoS / cost spike	possible	throttled + logged
Stale approvals replayed	possible	expire in 15 min
Auto-runs visible	no	yes (logged)

Why this works — concept by concept:

• Risk router — classifying by statement type, table sensitivity, and cost auto-runs the safe majority and escalates only the risky slice, so human review stays meaningful instead of degenerating into rubber-stamping.
• Human-in-the-loop approval — a time-boxed ticket puts a data owner's judgement on writes, sensitive tables, and big spends, and expiring stale tickets stops an old approval from being replayed later.
• Append-only audit log — one immutable record per query, auto-runs included, makes the feature attributable and replayable, and feeds anomaly detection and cost attribution from the same source of truth.
• Cost-aware rate limits — per-identity token buckets budgeted by count and estimated cost fail closed on both a flood of small queries and a few huge ones, so an agent loop cannot DoS the warehouse or spike the bill.
• Cost — a classifier, a queue, an append-only table, and two token buckets per user, versus the cost of an unaccountable feature, an unreviewed sensitive-data query, or an agent-loop bill spike. The eliminated cost is a governance failure — O(risky slice) of human review against O(everything) or O(nothing).

Design
Topic — design
Design problems on approval workflows and audit trails

Practice →

Optimization
Topic — optimization
Optimization problems on rate limiting and throttling

Practice →

---

Cheat sheet — AI-SQL guardrails

• The trust inversion. AI-written SQL is untrusted input, not reviewed code. Treat a model's query like a request from a stranger: validate it, sandbox it, cap it, gate it. Never let it run with more trust, privilege, or budget than an anonymous caller.
• The four control planes. Static validation (what the SQL says) → sandboxing (what it can touch) → cost/row caps (how much it consumes) → approval + audit (who signs off, what is logged). Defence in depth — each risk is stopped by at least one layer that prompting cannot bypass.
• Static validation. Parse with sqlglot/sqlparse — never regex. Require exactly one statement whose root is a SELECT; reject any write node anywhere in the tree (covers CTEs); ban SELECT *; allowlist every table and column (kills hallucinations and off-limits reads); inject or clamp a LIMIT by rewriting the AST and re-serialising. Emit only what passed the gates.
• Sandboxing. Run as a least-privilege, login-less, read-only role granted SELECT on curated views only, default-deny future objects — a write is refused by the engine. Force row-level security keyed on a server-set session variable so no query can read another tenant. Execute on an isolated warehouse/read replica. Set a per-query statement_timeout.
• Cost caps. Dry-run first (BigQuery total_bytes_processed) and reject over a per-query byte cap — free and exact. Set maximum_bytes_billed as a hard engine ceiling under the estimate. Cap cumulative spend with a Snowflake resource monitor (notify → suspend → suspend-immediate). Bound rows with an injected LIMIT + max_results/ROWS_PER_RESULTSET.
• Approval + audit. Risk-route: deny writes, escalate sensitive tables and high-cost scans to a time-boxed human approval, auto-run the safe majority (avoid approval fatigue). Write an append-only audit record — prompt, SQL, verdict, est/actual cost, rows, identity — for every query, in a store the app can insert to but never edit. Rate-limit per identity by count and cost; fail closed and log it.
• Never validate with substrings. Comments, casing, string literals, and stacked statements defeat text matching. Parse and reason on structure — statement types and identifiers.
• Authorization lives in the database. A read-only role and an RLS/row-access policy are the only guardrails a cleverer prompt cannot talk its way past — the application checks are convenience; the engine grants are the guarantee.
• Estimate before you execute. A dry-run turns cost from an invoice surprise into a pre-condition. The expensive query is rejected by a free number, not discovered on the bill.
• Log everything, auto-runs included. The common mistake is logging only the gated queries. Attribution and anomaly detection need the majority — the auto-runs — too.
• Rate-limit by cost, not just count. A count-only limiter waves through a few huge queries; budget estimated bytes/credits per identity so an agent loop cannot DoS the warehouse or spike the bill.
• The one-line invariant. Parse it, bound it, run it powerless, estimate it, and make someone accountable — because a model's fluency is not a guarantee, and the database is the only line of defence immune to a better prompt.

Frequently asked questions

What are guardrails for AI-written SQL, and why can't I just trust the model?

Guardrails are the automated controls that stand in for the human reviewer a piece of SQL would normally get before it runs. A text-to-SQL model produces text that looks like reviewed SQL but carries none of its guarantees: it can reference columns and tables that do not exist, scan a fact table with no filter, quietly emit a DELETE or DROP, run up a four-figure bill on a single join, or read another tenant's data. The model's fluency is not correctness, and no amount of prompt engineering makes its output trustworthy, because the same feature that lets a user ask a helpful question lets them ask a harmful one. Guardrails treat the query as untrusted input and apply four control planes — static validation of what the SQL says, sandboxing of what it can touch, caps on how much it can consume, and approval plus audit for accountability — so the feature is safe by construction, not by hoping the model behaves.

How do I stop an AI query from running a DROP or DELETE?

Two independent layers, because either alone can fail. First, static validation: parse the SQL into an abstract syntax tree with a real parser (sqlglot), require exactly one statement whose root node is a SELECT, and reject if any write or DDL node (Delete, Drop, Update, Insert, Merge, Create, Alter) appears anywhere in the tree — including inside a CTE or subquery, which a top-node-only check would miss. Never use a substring or regex check for this: comments, casing, string literals, and stacked statements all defeat text matching. Second, run the query as a least-privilege, read-only database role that has been granted SELECT on curated views and nothing that can write or alter schema. Then a destructive statement that somehow slips past validation still fails at the engine with a permission error, because the role physically lacks the privilege. The parser is your first line; the role is the one a cleverer prompt cannot argue with.

Static validation or a read-only role — which matters more?

You need both, but if forced to keep only one, keep the read-only role — because it is the guardrail that lives in the database, where prompting cannot reach it. Static validation is cheaper and catches more classes of problem earlier (hallucinated identifiers, unbounded scans, stacked statements), and it produces explainable rejections before any engine is touched, so it should always run first. But validation is code, and code has edge cases; a parser quirk or an unhandled dialect feature could let a bad query through. The read-only role, forced row-level security, and a statement timeout are enforced by the engine regardless of what the SQL says, so they hold even when validation is fooled. The senior framing is defence in depth: validation makes most queries fail fast and legibly, and the sandbox guarantees that the ones that slip through still cannot write, cross tenants, or run forever.

How do I cap the cost of an AI-generated query before it runs?

Estimate first, then cap hard. On BigQuery, submit the query as a dry-run (dry_run=True), which returns total_bytes_processed — the exact bytes the real query would scan — without executing or billing anything; reject (or escalate to human approval) any query whose estimate exceeds a per-query byte cap. Because the estimate can occasionally be low or a code path can skip the gate, also set maximum_bytes_billed on every executed job so the engine refuses a query that would overrun a hard ceiling. To bound cumulative spend across many queries — a loop of medium queries that per-query caps miss — use a Snowflake resource monitor (or a BigQuery project quota) that notifies, then suspends, then kills at credit thresholds. Finally, cap the result with an injected LIMIT and a max_results/ROWS_PER_RESULTSET bound, so even a cheap scan cannot return millions of rows. Estimate, hard-cap bytes, cap cumulative credits, cap rows — four independent brakes on spend.

When should an AI query need human approval?

Only for the genuinely risky slice, because gating everything trains reviewers to rubber-stamp and destroys the value of approval. Auto-run the safe majority — read-only, allowlisted, within the cost band — and escalate to a human only when a query writes (if writes are permitted at all), touches a sensitive or PII table, or exceeds a cost band even while under the hard cap. The approver should see the prompt, the generated SQL, the estimated cost, and the reason it was flagged, so they can decide in seconds, and the approval should be time-boxed so a stale ticket cannot be replayed later. Risk-routing this way keeps the human queue small and every decision meaningful: an approver who sees ten real decisions a day stays sharp, while one who waves through a thousand auto-approvals adds no safety at all. The router is what makes human-in-the-loop scale.

What do I have to log for an AI-SQL feature?

Everything needed to attribute and replay any query after an incident: the timestamp, the user and role, the natural-language prompt, the generated SQL, the verdict (auto-run, approved, denied, rate-limited), the estimated and actual cost, the rows returned, and any error — for every query, including the auto-runs, not just the gated ones. Write it to an append-only store that the application can insert to but cannot update or delete (revoke UPDATE/DELETE from the service role, or use a log sink), and keep it separate from the app database so it survives and cannot be doctored to hide a bad query. That single record powers three things from one source of truth: incident review ("who ran what, and what did it cost?"), anomaly detection (a user suddenly querying sensitive tables, or a spike in denied queries), and cost attribution (tagging AI-feature spend in the billing export). Logging only the exceptions is the common mistake — the majority of activity, the auto-runs, is exactly what you need to see the baseline and spot the deviation.

Practice on PipeCode

• Drill the text-to-SQL practice library → for the query-generation, correctness, and schema-grounding problems that make an AI-SQL feature safe to build on in the first place.
• Harden your instincts on the defensive-coding practice library → for the untrusted-input, safe-default, and reject-early patterns that static validation and cost caps are built from.
• Sharpen the architecture axis with the system design practice library → for the least-privilege, isolation, approval-workflow, and audit-trail trade-offs a guardrailed feature must get right.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the parsing, allowlisting, row-level-security, and rate-limiting patterns against real graded inputs — validation, RBAC, cost bounds, and accountability.

Lock in AI-SQL guardrail muscle memory

Docs explain sqlglot, RLS, dry-runs, and resource monitors. PipeCode drills explain the decision — when a model's SQL must be parsed instead of trusted, when a `read-only role` beats a string check, when a dry-run must reject a scan before it runs, and when a risky query has to wait for a human. Pipecode.ai is Leetcode for Data Engineering — guardrail practice tuned for the production trade-offs senior data engineers actually face.

Practice text-to-SQL problems →
Practice defensive-coding problems →