Stop Letting LLMs Write Raw SQL Against Your Production Lakehouse

You ship the job. It passes CI. The integration tests green-light the agent. Then, at 2:00 AM on a Tuesday, a junior analyst asks the bot, "Show me all transactions for the last year," and your Databricks cluster spins up a 400-node task because the LLM generated a Cartesian join on an unpartitioned 50TB fact table.

Your boss isn't asking about the "power of GenAI" anymore. They’re asking why the cloud bill spiked by four figures in three hours.

I’ve spent six years in financial services and healthcare. I’ve seen what happens when you treat an LLM like a junior DBA who doesn't know the schema and definitely doesn't know when to use a LIMIT clause. If you want to put Text-to-SQL in production, stop building demos and start building guardrails. Here is how you stop the bleeding.

1. The Deny-List is Your First Line of Defense

Never let an LLM touch your raw catalog. If you give an agent access to information_schema, it will eventually try to DROP or TRUNCATE something because it hallucinated a table name that sounded like a temp file. Use a hard-coded schema allow-list. If it isn't in the config, the agent doesn't know it exists.

# Use a strict Pydantic model for your allowed tables
class AgentSchema(BaseModel):
    allowed_tables: List[str] = ["fact_transactions", "dim_customers"]
    forbidden_columns: Dict[str, List[str]] = {
        "fact_transactions": ["ssn", "credit_card_number"]
    }

# Inject this into your system prompt
system_prompt = f"""
You are a read-only SQL assistant.
You have access to: {", ".join(AgentSchema().allowed_tables)}.
NEVER query columns: {AgentSchema().forbidden_columns}.
"""

Photo by Pierre Bamin on Unsplash

2. Force the LIMIT Clause at the Engine Level

LLMs love to perform "select star" queries. In a governed lakehouse, that is a death sentence. You cannot trust the LLM to remember to add LIMIT 100. If you don't enforce this, your compute costs will be the least of your worries—the driver node OOMs will be. Don't rely on the LLM to behave; rely on the SQL proxy.

-- Wrap your LLM-generated SQL in a CTE or a view
SELECT * FROM (
    /* LLM OUTPUT HERE */
    SELECT * FROM fact_transactions WHERE region = 'EMEA'
) LIMIT 100;

Better yet, use Spark session configs to cap the row return if you’re using a notebook-based agent. Set spark.sql.execution.maxOutputRows to 1000 and let the driver kill the query before it hits the network stack.

3. The "Semantic Router" Pattern

Don't let the LLM generate SQL for complex analytical queries. Use a router. If the user asks for "total revenue by region," the LLM should hit a pre-defined, optimized SQL template or a stored procedure, not a raw SELECT SUM(...). Raw SQL generation should be reserved for simple data discovery only. If the query requires a window function or a complex join, point the agent toward a curated view.

4. Column-Level Security is Non-Negotiable

In healthcare, if the LLM hallucinates and includes a WHERE clause on a patient_dob column that shouldn't be exposed, you’re looking at a HIPAA violation. Implement an interceptor that validates the generated AST (Abstract Syntax Tree) against a security policy before execution. Use sqlglot to parse the LLM's output.

import sqlglot
from sqlglot import exp

def validate_query(sql_statement):
    parsed = sqlglot.parse_one(sql_statement)
    for column in parsed.find_all(exp.Column):
        if column.name in ["ssn", "medical_record_id"]:
            raise SecurityException(f"Illegal column access: {column.name}")
    return True

If the LLM tries to touch PII, kill the process. Don't log it; block it.

5. Human-in-the-Loop for DDL and Mutations

If your agent is capable of running INSERT, UPDATE, or DELETE commands, you have already failed. A production-safe Text-to-SQL agent should be strictly SELECT only. If you absolutely must have the agent update records, implement a "Human-in-the-Loop" (HITL) step where the SQL is rendered in a UI for a human to click "Approve" before the spark.sql() command is ever triggered.

6. Token Budgeting as a Circuit Breaker

LLMs are verbose. They love to explain their SQL. In a production pipeline, this is just noise. Set a max_tokens limit on your response, but more importantly, measure the length of the generated SQL string. If your agent generates a 500-line query for a simple question, it’s likely looping or confused. Treat long queries as a failure state and trigger a fallback to a "I don't understand" response.

Photo by Ilnur on Unsplash

7. Audit Logging the Latent Space

You need to log the prompt, the raw generated SQL, and the query execution stats (rows scanned, bytes spilled). When a query takes 10 minutes to run, you need to know exactly what the LLM thought it was doing. Use a decorator to log these to your observability platform (Datadog, Honeycomb, or even a simple ELK stack).

def log_agent_execution(func):
    def wrapper(*args, **kwargs):
        start = time.time()
        result = func(*args, **kwargs)
        log.info(f"Query: {args[0]} | Duration: {time.time() - start}")
        return result
    return wrapper

Conclusion

Text-to-SQL is not magic; it’s just a dangerous way to compile natural language into high-cost compute operations. If you don't build these guardrails, you’re essentially giving your entire organization a blank check to empty your data warehouse budget.

Start by parsing the SQL before it runs, enforce row limits, and keep the agent away from your PII. The LLM is the engine, but the guardrails are the steering wheel. Without them, you’re just driving toward a wall at 100 miles per hour.

When was the last time your LLM agent generated a query that cost you more than $50 in compute time?

---

Tags: #sql #llm #data #security

Cover photo by Tyler on Unsplash.