DEV Community

albe_sf
albe_sf

Posted on

Text-to-SQL is still brittle. Snowflake's Cortex Sense is a new take.

#ai #programming #devtools #machinelearning

Natural language to SQL has always been a brittle last mile for enterprise AI. Snowflake's new Cortex Sense proposes a different approach: instead of you manually defining a semantic layer, it automatically builds a working model of your business by observing how analysts and tools already query your data. This moves the bottleneck from manual curation to automated inference, tackling the context problem head-on.

the accuracy floor

The core problem with text-to-SQL is context, not syntax. Large language models are perfectly capable of writing SQL. What they lack is the deep, implicit knowledge of your business encoded in your database schema: which user_id joins to which account_id, what a "power user" actually means, and which cryptic enum value signifies a churned customer.

Without this semantic grounding, agent accuracy is predictably low. Internal Snowflake data and independent measurements from Anthropic both put the baseline accuracy for text-to-SQL agents without a context layer at around 21-25%. This is simply not reliable enough for production business intelligence. The traditional answer has been a manually curated semantic layer, but this creates its own bottleneck and struggles to keep pace as the business changes.

mining the semantic layer

Cortex Sense is designed to solve this by creating the semantic layer automatically. Instead of being a static catalog of tables you maintain, it builds a working model of your business by observing the signals your organization already produces.

This includes:

  • Queries your analysts have run in the past.
  • Models defined in your transformation tools.
  • Metrics that already live in your BI dashboards.

This context can be ingested via Snowflake Horizon Connectors, creating a dynamic understanding of your data landscape. The goal is to give an AI agent the same institutional knowledge a human analyst builds over time by observing how data is actually used in practice.

from naive to contextual queries

The difference in output is significant. A naive LLM might generate a syntactically correct query that fails because it doesn't understand your business's specific conventions. An agent grounded by Cortex Sense can translate vague business language into precise SQL.

Consider a request like, "Show me our top 10 customers in the northeast."

A naive model might produce something that looks right but fails on your schema.

-- Naive LLM attempt
SELECT
  customer_name,
  SUM(order_value) as total_spend
FROM orders
WHERE region = 'northeast' -- Fails if region is an enum or stored in another table
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

An agent with the mined context from Cortex Sense understands the necessary joins and filter logic because it has seen similar queries before.

-- Agent with Cortex Sense context
SELECT
  c.customer_name,
  SUM(o.order_value) as total_spend
FROM app_data.orders o
JOIN app_data.customers c ON o.customer_id = c.id
JOIN app_data.locations l ON c.location_id = l.id
WHERE l.state_code IN ('NY', 'MA', 'VT', 'NH', 'ME', 'CT', 'RI', 'PA', 'NJ') -- Contextual understanding of 'northeast'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;
Enter fullscreen mode Exit fullscreen mode

the risk of being confidently wrong

This automated approach is not without risk. The source material—past queries and BI dashboards—may contain errors, outdated logic, or inefficient patterns. A system that automatically mines this context could potentially learn and propagate bad habits, being confidently wrong without human oversight.

This is the fundamental trade-off. You exchange the slow, manual work of building a semantic layer for the speed and scale of automation, but you also accept the risk that the automated system will inherit the flaws of its source data. For any team implementing this, building in verification steps and human-in-the-loop review will be critical.

This is a move away from brute-force LLM intelligence and toward systems that ground AI in the lived reality of an organization's data culture. For builders, it’s a reminder that the most valuable context for an agent isn't in a prompt, but in the years of query logs you already have. It is an approach worth watching to see if automated semantic modeling can finally solve the text-to-SQL problem at scale.

Sources

Top comments (0)