Text-to-SQL Was Never a Model Problem

For about three years I watched the same demo. Someone types "show me revenue by region last quarter" into a chat box, a model spits out SQL, the room claps, and then nobody uses it again.

The demo always worked, but the product never did.

I have been at Sodali & Co for six months now, and I came in as Director of AI Product Management to lead AI product strategy across the firm. The way I have come to think about that job is narrower than the title: the work is moving a professional services firm toward being an intelligence company. That framing matters, because an advisory firm sells judgment. Partners carry the meaning of the business in their heads. That is fine when your deliverable is a senior advisor in a room with a client. It stops being fine the moment you want to sell intelligence at scale, because intelligence has to be queryable, testable, and reproducible by someone who is not the seven-year veteran.

This post is about the technical work underneath that transition, which is almost entirely a semantic layer problem.

The thing that convinced me

Snowflake ran the same LLM against BIRD-SQL twice. Once with just a schema. Once with a semantic model on top.

57 percent to 78 percent. Same model. Same questions. The only change was context about meaning.

Twenty-one points of accuracy from writing documentation.

Snowflake also published work on an agentic system that improves the semantic model itself, and reported roughly a 20 percent average accuracy gain over LLMs alone.

These are vendor numbers so take them with a grain of salt. But the direction matches what I have seen every time an agent writes SQL against a warehouse it does not understand.

The failures are almost never syntax. The model writes beautiful SQL that answers the wrong question.

ELI5: three layers of grounding

Think about onboarding a new analyst. There are three separate things they need, and most AI setups only give the model one of them.

Layer 1: facts. What tables exist. What columns are in them. What the values actually look like. This is INFORMATION_SCHEMA, sample rows, distributions, uniqueness checks. A schema-only prompt gives the model this and nothing else.

Layer 2: meaning. What the business calls things. Which table is canonical. What filters are mandatory. How a metric is calculated, and which of the four plausible calculations is the one your CFO uses.

Layer 3: identity. Which real-world thing the user is actually talking about. When someone types a company name three different ways, all three need to land on the same row.

Layer 1 is free. Layer 2 is work. Layer 3 is where most people never get to, and it is the one that quietly ruins everything.

I wrote about part of this in How I Stopped Drowning in Data Governance back in February. Facts come from Snowflake, meaning comes from your BI layer, synthesis comes from a skill. That framing still holds. What changed is that the meaning layer stopped being a document you hope someone maintains and became an object that lives in the warehouse.

Layer 2, concretely

Here is a real pattern. Someone asks:

"How many active accounts do we have in EMEA?"

Schema-only, the model does something like this:

select count(*)
from accounts
where region = 'EMEA';

Technically valid. Almost certainly wrong. Every single one of these is a landmine:

• accounts might be a snapshot table with one row per account per day.
• There may be a soft-delete column that nobody mentioned.
• "Active" might mean a status flag, or a contract end date in the future, or activity in the last 90 days.
• region might be the billing region, while your reporting standard is the operating region in a different table.
• EMEA might be stored as EMEA, emea, and Europe, Middle East & Africa depending on which pipeline wrote the row.

None of that is a model failure. It is an undocumented-business failure. The model guessed because we gave it nothing to check against.

With a Snowflake semantic view, the metric and the filter live in the object, not in the prompt. The shape below matches Snowflake's current semantic view YAML: metrics and dimensions hang off a logical table, expressions use expr, and mandatory WHERE conditions are boolean dimensions labeled filter. There is no required_filters field.

# Illustrative. Field names match Snowflake's semantic view YAML spec.
# Swap database/schema/table for yours. Do not treat the business logic as production.
name: accounts_sv
description: Account metrics for reporting. Prefer operating_region over billing_region.
tables:
  - name: accounts
    description: Account snapshots. One row per account per snapshot_date.
    base_table:
      database: ANALYTICS
      schema: CORE
      table: ACCOUNTS
    primary_key:
      columns: [account_id, snapshot_date]
    dimensions:
      - name: operating_region
        synonyms: [region, geography, geo]
        description: Reporting region. Use this, not billing_region, for external reporting.
        expr: accounts.operating_region
        data_type: VARCHAR
      - name: is_active
        synonyms: [active, live, current]
        description: True when status is ACTIVE.
        expr: accounts.status = 'ACTIVE'
        data_type: BOOLEAN
        labels:
          - filter
      - name: is_latest_snapshot
        description: True on the latest snapshot_date. Prefer a maintained boolean column or fact over a subquery in the view.
        expr: accounts.is_latest_snapshot
        data_type: BOOLEAN
        labels:
          - filter
    metrics:
      - name: active_accounts
        synonyms: [live accounts, current accounts, active clients]
        description: Distinct ACTIVE accounts on the latest snapshot date.
        expr: COUNT(DISTINCT accounts.account_id)

The important part is still not the syntax. It is the operating_region description: use this, not billing_region. That sentence exists in exactly one place in most companies, which is inside the head of the person who has been there seven years.

A semantic view is a place to put that sentence. In an advisory context, that sentence is where institutional knowledge stops being tacit and becomes an object a machine can read and a test can check.

Layer 3 is the one people skip

Semantic models handle metrics and joins. They do not handle the fact that humans refer to entities however they feel like.

At Sodali this shows up everywhere. Shareholder and issuer entity resolution is genuinely hard. The same holder or issuer arrives as a legal name, a DBA, a ticker, a CIK, a custodian nominee, or an internal ID. A metric definition cannot help you here. You need retrieval.

Variations of the same entity, from the kind of text people actually type. The name here is invented, the pattern is not:

• Northfield Asset Management, Inc.
• Northfield Asset Mgmt Inc
• NORTHFIELD ASSET MANAGEMENT INC.
• Northfield (formerly Cardinal Ridge Partners)
• the ticker
• the CIK
• a custodian nominee that does not resemble the holder name at all
• an internal holder ID nobody outside the team recognizes

If you are already on Snowflake semantic views, the cleanest path is attaching a Cortex Search service to the entity dimension with cortex_search_service in the YAML. That keeps retrieval next to the meaning layer instead of as a separate side system.

Underneath, the idea is still: embed the canonical entity list, embed the user's phrase, take the nearest neighbors, then resolve with a confidence threshold and a clarifying question when it is close.

-- Pattern using current Snowflake functions.
-- Prefer attaching Cortex Search to the semantic view dimension when you can.
-- Model name should match what your account has provisioned.
WITH query_vec AS (
  SELECT AI_EMBED('snowflake-arctic-embed-l-v2.0', :user_phrase) AS embedding
)
SELECT
  e.entity_id,
  e.canonical_name,
  VECTOR_COSINE_SIMILARITY(e.name_embedding, q.embedding) AS score
FROM entity_index AS e
CROSS JOIN query_vec AS q
ORDER BY score DESC
LIMIT 5;

Then a rule, not a vibe: if the top match scores above your threshold, resolve silently. If the top two are within a hair of each other, ask. If nothing clears the floor, say you do not know.

I have a fixed opinion about this one. An agent that guesses which company you meant is worse than an agent that asks. Wrong-entity answers look completely correct. There is no error message. You find out in a meeting.

Why this matters for advisory firms specifically

The professional services to intelligence company transition is not a metaphor. It is a technical shift.

Professional services firms operate on partner expertise and relationship memory. That model scales linearly. You need more senior people to serve more clients. Intelligence products scale differently. They require that your definitions, your entity mappings, and your domain logic are written down in a way that a system can execute them consistently. That is what a semantic layer does.

At Sodali I have already built semantic views across the databases we have, including news and financials. That was the first concrete step of the job: stop treating meaning as something that lives in a partner's head or a stale BI wiki, and put it in the warehouse next to the facts.

The domains still include shareholder identification, proxy solicitation, governance advisory, and ESG reporting. Each of those has entities that need canonical representations, metrics that need precise definitions, and workflows that currently rely on human judgment. Building the views did not finish that work. It made the work visible. Every metric that is still undefined, every filter that is still tribal knowledge, and every entity that still resolves three different ways now has a place to land.

This is not glamorous. It is sitting with the person who owns the metric and asking, one more time, what "beneficial holder" means in this context versus that one.

The part I got wrong in February

In the database-scanner post I treated this as mostly a retrieval problem. Get the facts from the warehouse, get the meaning from the BI tool, stop pasting schemas into prompts.

That was right and incomplete. Retrieval gets you grounded. It does not tell you whether you are getting better.

The thing I keep coming back to is something Shanti Greene said when I was on Not Another AI Podcast: your skills need version control, and your evaluation criteria need to be measurable before and after each change. Not "this looks a little better."

Starting fresh at Sodali, I wanted the harness next to the views, not as a side project. Snowflake even gives you a place for that inside the semantic view YAML itself: verified_queries, which pairs a natural language question with the SQL you agree is correct. That is a good start. It is not the whole harness.

Eval harness, minimum viable version

You need a question set and a grading rule. That is it. It is boring and it is the difference between a demo and a product.

Build a spreadsheet, or a verified_queries block, with three columns: the question a real person asked, the SQL you agree is correct, and the tier.

Tiers I use:

Tier 5 is the one everyone forgets and the one that saves you. A system that confidently answers questions your data cannot answer will burn your credibility faster than a system that is occasionally slow.

Grade on result equivalence, not string match. Two queries can differ completely and return the same correct set. Run the reference SQL, run the generated SQL, compare the result frames.

Then the discipline part. Every time you touch the semantic model, rerun the whole set. Log the score. If a change moves Tier 2 up four points and Tier 4 down nine, you need to know that before your users find it.

Fifty questions is enough to start. Do not wait until you have three hundred.

Where this still breaks

Being honest about the limits, because I do not think this is solved.

Semantic layers only see the warehouse. If the meaning of your metric depends on a system that is not in Snowflake, the semantic view does not know about it. Warehouse-bounded is a real constraint, not a footnote.

There is no automatic sync from your existing comments. Table comments you already wrote do not flow into a semantic view for free. Someone has to do the work. Building views across news and financials made that pain concrete: every database needs its own definitions, and none of them appear by magic.

The definitions rot. A semantic view is a promise to maintain something. If nobody owns it, in eight months it is another stale governance doc, except now an agent is confidently querying against it.

Coverage is a scoping decision. Putting a semantic view on every database is not the same as defining every metric in those databases. The advice I keep giving is still to prioritize the 20 percent of concepts that answer 80 percent of the questions, then validate with the people who own those definitions. Broad database coverage without metric discipline just gives you a larger surface area for confident wrong answers.

Agents are moving underneath you. Snowflake changed how Cortex Agents generate SQL in April 2026, moving from delegating to the Analyst service to generating directly. If you build tightly against today's tool-call shape, expect to revisit it.

The Skeptic's Corner

"This is just a BI semantic layer with a new hat."

Mostly yes, and that is the point. The concept is decades old. What changed is the consumer. A BI semantic layer served a dashboard builder who could eyeball a wrong number. Now it serves an agent that will write a query, get a plausible answer, and hand it to someone who will not check. Same object, much higher stakes.

"Why not just fine-tune a model on our SQL?"

Because your definitions change monthly and your model does not. A semantic layer is editable by a business owner in an afternoon. A fine-tune is a project. Also, if you have not written your definitions down, you have nothing to fine-tune on.

"We do not have clean data. Should we even start?"

Start narrower, not later. Pick one domain where people ask the same five questions every week. Model that. The unglamorous truth is that half the value shows up while you are writing the definitions, before the AI touches anything, because you find out that three teams have been calculating the same metric three ways.

"Is this just token optimization again?"

Kind of, and I keep noticing that everything I write comes back to this. Yes, you stop stuffing schemas into prompts, which is a real cost and quality win. But the bigger unlock is that retrieved meaning can be tested. Pasted meaning cannot.

How I will know this is working

Treating it like a product, so it needs a scorecard:

• Tier accuracy: score per tier, tracked over time, not one blended number.
• Refusal quality: how often does it decline a question it should decline.
• Entity precision: on messy names, how often does it resolve to the right entity or correctly ask.
• Definition coverage: what share of real questions asked this month hit a defined metric.
• Time to trust: how long before someone runs a number from this in front of a client without checking it by hand.

That last one is the only metric that actually matters. Everything else is a proxy for it.

The takeaway

Every time I have seen natural language SQL fail, the postmortem has been the same. The model did fine. We just never told it what we meant.

Semantic models are not an AI feature. They are documentation with a contract, in a place a machine can read, with tests attached.

Which means the work is not glamorous. It is sitting with the person who owns the metric and asking, one more time, what "active" means. For an advisory firm, it is also the work of turning institutional knowledge into an object the next hire can query. That is the difference between a services firm and an intelligence company.

Do that, and the model was always good enough.