Is Your Text-to-SQL Agent Just a Fancy Way to Drop Your Production Tables?

The most dangerous myth in the current AI hype cycle is that a RAG-based Text-to-SQL agent is "smart enough" to know the difference between a read-only reporting schema and your customer PII tables. Spoiler: it isn't, and it doesn't care.

Why I chose this topic: I spent the last three months watching an LLM try to "helpfully" join our users table with our transaction_logs in a way that would have leaked HIPAA-regulated data if the database user permissions hadn't been tighter than a vault. We need to stop pretending that prompt engineering replaces architectural guardrails.

If you believe that a clever system prompt and a few-shot examples are enough to put a Text-to-SQL agent in front of your business analysts, you are one hallucinated JOIN away from a compliance nightmare. I’ve seen teams ship agents that, when asked "How many users do we have?", helpfully executed a SELECT * on the entire metadata catalog, nearly crashing the warehouse in the process.

Why the common approach falls short

Most tutorials on this topic focus on the "magic" of getting the SQL right. They talk about LangChain agents, vectorizing schema definitions, and tweaking temperature settings. This is the wrong layer of the stack.

The common failure mode is treating the LLM as an authorized user. If your agent connects to your Snowflake or Databricks cluster using a service account with SELECT access to the entire RAW schema, you have effectively turned your LLM into a high-speed injection attack vector.

When you define a table context for an LLM—even with sophisticated RAG—you are essentially giving it a map of your house. If you don't lock the doors to the rooms that hold the sensitive data, the LLM will eventually walk into them because the user asked it to "find all info related to customer X."

Photo by REGINE THOLEN on Unsplash

The view-based gatekeeper pattern

Never point your agent at base tables. Ever. If you are doing SELECT * FROM production.users, stop. You need a dedicated "AI-Consumption" schema.

Create a specific layer of views that exist solely for the agent. In Snowflake, I use a combination of ROW_ACCESS_POLICY and SECURE VIEW.

-- The only view the LLM sees
CREATE OR REPLACE SECURE VIEW ai_reporting.daily_transactions AS
SELECT 
    transaction_id, 
    transaction_amount, 
    transaction_date, 
    category 
FROM prod.transactions
WHERE transaction_date >= DATEADD(month, -1, CURRENT_DATE());

By forcing the agent to query only this view, you achieve two things: you limit the scope of available columns to only what is necessary, and you apply a hard temporal filter. If the LLM tries to query customer_ssn, it literally can't find it. The schema doesn't exist to it. Do not rely on the LLM to "ignore" columns; rely on the database engine to "not provide" them.

The circuit breaker at the middleware layer

Even with restricted views, LLMs are prone to "runaway queries." An agent might decide that the best way to answer "What is the average transaction value?" is to pull 50 million rows into memory because it doesn't understand the performance implications of a table scan on an unclustered column.

You need a hard circuit breaker between the agent and the database. I use a custom wrapper in Python that intercepts the generated SQL before it hits the execute() method.

def validate_sql(sql_query):
    # Prohibit dangerous keywords
    forbidden = ['DROP', 'TRUNCATE', 'ALTER', 'GRANT', 'DELETE']
    if any(keyword in sql_query.upper() for keyword in forbidden):
        raise PermissionError("Illegal operation detected.")

    # Force a limit if one isn't present
    if "LIMIT" not in sql_query.upper():
        return sql_query + " LIMIT 100"
    return sql_query

Is this a hack? Yes. Does it prevent a junior engineer's LLM agent from nuking a production table? Absolutely. In a production environment, I also inject a QUERY_TAG into the SQL comment block so I can track the agent’s behavior in the warehouse query history: /* AGENT_ID: finance_bot_v2 */. If I see a spike in compute costs, I know exactly which agent is acting up.

The semantic proxy layer

If you are using something like LlamaIndex or LangChain's SQLDatabaseChain, you are likely passing the entire DDL to the prompt. This is a recipe for context window bloat and hallucination.

Instead, build a "Semantic Proxy." Create a YAML or JSON manifest that describes your data in human terms, but keep the actual column names abstracted.

# semantic_manifest.yaml
tables:
  - name: tx_summary
    description: "Contains aggregated transaction data for the last 30 days."
    columns:
      - alias: "total_revenue"
        column_name: "amount_usd"
        type: "numeric"

The LLM queries against total_revenue, and your proxy translates that to amount_usd before hitting the database. This decouples your internal schema changes from your agent’s capabilities. When you rename amount_usd to gross_revenue_base, you update the manifest, not the prompt. The agent doesn't even know the underlying column name changed.

Photo by Markus Spiske on Unsplash

The objections (and my answers)

Objection: "But this limits the power of the LLM. If I restrict the schema, the agent can't answer complex cross-functional questions."

My answer: That is a feature, not a bug. In financial services, "cross-functional" usually means "compliance violation." If you need to join HR data with marketing data, that should be a curated data product, not an ad-hoc query generated by a probabilistic model. If the answer isn't in the governed view, the agent should return "I don't have access to that information" rather than trying to guess a join path.

Objection: "Isn't the cost of maintaining these views and manifest files too high? Why not just use a semantic layer like dbt or Cube?"

My answer: You should absolutely use dbt or Cube. If you’re already using them, you’re halfway there. But those tools are for human analysts. An LLM doesn't have the context of why a table was built. You still need an explicit, LLM-optimized subset of those models. Don't dump your entire dbt dbt_prod schema into an LLM context window and call it a day.

Objection: "What if the LLM generates syntactically correct but logically wrong SQL (e.g., summing a column that shouldn't be summed)?"

My answer: This is the hardest problem to solve. My solution is the "Human-in-the-loop" validation for non-idempotent queries. If the query is just a SELECT, let it rip. If the agent generates a query that implies a complex aggregation on a sensitive metric, I force the agent to output the SQL to a UI where a human has to click "Approve" before it runs. If you aren't doing this for business-critical reporting, you're playing Russian Roulette with your KPIs.

Conclusion

Text-to-SQL is only viable if you treat the LLM as an untrusted user. Stop trying to make the LLM "smarter" and start making your database "dumber"—by which I mean, simpler, constrained, and incapable of executing anything outside of a predefined, safe sandbox.

If you aren't ready to build the middleware, the proxy, and the view layer, you aren't ready to deploy. Keep the LLM in the playground until you’ve built the fences.

---

Tags: #sql #llm #governance #data

Cover photo by Brice Cooper on Unsplash.

Comments

[DEV SUPPORTS]

You need to verify your account.

tr.ee/dev-to