Blog · 2026-09-30 · Ashutosh Agarwal

Five problems with a plain Snowflake connector, and why finance needs a harness that verifies every answer

An LLM that writes and executes SQL against a fund's warehouse returns plausible wrong aggregates whenever a join fans out or drops rows; the fix is a harness that probes every join's cardinality against the data, learns the warehouse's semantics, and enforces read-only access at the role.

Ask an LLM connected to your warehouse for your exposure to the energy sector, and it will generate the SQL, execute it and return a number. The query succeeds. If the security master carries two listings for one ticker, London and New York, the join to it is one-to-many, the energy aggregate counts that position twice, and nothing in the result says so.

This is not one fund's schema problem: we see the same join hazards across our clients' warehouses. We rebuilt them in a test warehouse with known answers and ran the agent that answers from a firm's Snowflake on ten questions, three times each. With no verification it returned 22 of 30 correct results, and every miss was a plausible number. Prompting it with join rules moved it to 23 of 30. Probing each join's cardinality against the data before using the result moved it to 30 of 30.

Text-to-SQL over a connector is the easy part. On its own it has five failure modes: joins that fan out, inner joins that silently drop rows, no verification of either against the data, no model of what the firm's tables mean, and no enforced guarantee that it cannot write, cannot read beyond its grant, and leaves an audit trail. Finance needs a harness on top of the connector that verifies every result before anyone sees it.

This article sets out each failure mode, the harness we built for it, and how we measured it.

TL;DR

• Text-to-SQL fails silently on finance schemas. In our test warehouse, the naive query for each hazard returned an aggregate from 4% low to 2.97× the right answer, with no error.
• Just prompting does not help; checking the data does. The agent scored 22 of 30 with no verification, 23 of 30 with prompting alone, and 30 of 30 with join cardinality probed against the data, for about half a cent more per question.
• Every join's cardinality can be probed in seconds. One GROUP BY probe per join edge, at most 8 per query and 20 seconds in all; on 32 test queries, the check caught every hazard with no false positives.
• The warehouse already encodes most of its own semantic layer. Declared constraints, view definitions and the team's query history yield join paths and metrics that Matterfact drafts, verifies against the data, and asks an admin to confirm.
• Read-only and auditable has to be enforced, not promised. A SELECT-only role, key-pair auth with a key the firm installs, a parser gate on every statement, a row cap, and a query tag on every statement.

Text-to-SQL fails silently on finance schemas

Finance schemas are full of relations whose grain is finer than the key a model will join on. A ticker maps to more than one listing. A price table's grain is security × date. An identifier bridge table's grain is security × identifier type. A fund can have two portfolio managers, and a trade can be allocated across several accounts. Join to any of them on the obvious key and the join is one-to-many: every aggregate over the left side is inflated by the fan-out (Exhibit 1).

Exhibit 1
One vendor-feed row matches both listings of a dual-listed ticker, so the join emits it twice and SUM(MV) doubles.

Vendor feed

Security master

Joined on TICKER

SUM(MV): 280,000. Twice the 140,000 in the feed.

Note: One row from Matterfact's synthetic test warehouse; the query sums market value (MV) grouped by sector.
Source: Matterfact analysis

The opposite failure is quieter. An inner join is also a filter: legacy rows whose fund code has no match in the fund dimension fall out of the result, and the total is understated with no indication of how many rows were lost.

We rebuilt each of these hazards, as we see them across our client base, in a test warehouse with known right answers (Exhibit 2).¹ The naive query for each hazard returned a wrong aggregate every time, from 4% low to 2.97× the right answer.

Exhibit 2
Each hazard's naive query returned an aggregate from 4% low to 2.97× the right answer, with no error.

Note: Ratio of the naive query's result to the right answer, on Matterfact's synthetic test warehouse.
Source: Matterfact analysis

None of these errors is detectable from the output. A sector total inflated by 27% looks like a sector total. For a team putting an LLM on its own warehouse, the risk is not a query that errors; it is a query that succeeds.

Just prompting does not help; checking the data does

We ran Matterfact's private-data analyst, the agent that answers from a firm's warehouse, on ten questions over the test warehouse, three times each, in three configurations (Exhibit 3). With no verification it returned 22 of 30 correct results. Every miss was one of the silent failures above, not an exception or a refusal.

Exhibit 3
Probing join cardinality took the analyst from 22 of 30 to 30 of 30; prompting alone did not move it.

Share of answers right Ten questions run three times each; the three hardest run five more times. No check: 22 of 30 and 4 of 15. Prompting only: 23 of 30 and 3 of 15. Joins checked: 30 of 30 and 14 of 15.

Note: Claude Sonnet 5 at medium effort, the analyst's production model and setting. A result counts as right within 0.5% of the reference query's. In the five extra runs, one miss with prompting only and the one miss with joins checked were answers given in prose instead of submitted; the eval now re-prompts for those.
Source: Matterfact analysis

The prompting was specific: join on a key that is unique on the far side, include the date in any join to a price table, pin a bridge table to one identifier type, and use a LEFT JOIN when unmatched rows belong in the result. It fixed one answer and left the other misses in place; on the extra runs it did no better. What a prompt cannot supply is the instance-level fact: told that dual listings exist, the model still cannot tell from column names that this security master contains one.

The misses concentrated in three questions (Exhibit 4). Each depends on a property of the data, not of the SQL: that some fund codes have no match, or that a ticker is not unique in the security master.

Exhibit 4
Every miss came from a property only the data reveals: unmatched fund codes and a non-unique ticker.

Source: Matterfact analysis

Verification cost about half a cent and 0.6 extra turns per question, mostly the analyst rewriting queries the check flagged. For any team evaluating an LLM on its warehouse, the lesson is to score it on questions with known answers over data that contains these hazards, not on whether the SQL compiles and runs.

Every join's cardinality can be probed in seconds

The check runs on each query before its result is used. It parses the SQL with sqlglot and walks every SELECT scope, CTEs and subqueries included. In each scope it identifies the relations being aggregated, counted or listed, and every join edge off them. For each edge it asks one question of the data: is the far side unique on the join keys, under this query's own predicates? A bridge table filtered to ID_TYPE = 'CUSIP' passes; the same table unfiltered does not. For inner joins it also measures the match rate and flags any join that keeps less than 95% of the rows being counted (Exhibit 5).

Exhibit 5
The check runs on your warehouse after each query, and a join that fails sends the analyst back to rewrite the query before it answers.

1. Generates one query (Matterfact). From the question, the stored schema and your data guide.
2. Parses it as read-only (Matterfact). A single SELECT; DML, DDL and grants are rejected.
3. Executes as your read-only role (Your warehouse). Result capped at 1,000 rows.
4. Probes each join's cardinality (Your warehouse). One GROUP BY probe per join edge, under the query's predicates: at most 8, in 20 seconds.
5. Answers with the SQL cited (Matterfact). The exact query behind every number.

Back to 1: when a join fans out or drops rows, a diagnostic gives the fix and the analyst rewrites the query.

When an edge fails, the result carries a diagnostic written for the model to act on: the fan-out factor, an example key and the fix. For the vendor feed above:

REF.SECURITIES has up to 2 rows per TICKER (e.g. TICKER = 'SHEL'), so SUM(c.mv)
counts some POS.CUSTODY_HOLDINGS rows up to 2 times. Add up
POS.CUSTODY_HOLDINGS before the join, or join to one row per TICKER of
REF.SECURITIES (filter it, or GROUP BY TICKER first).

Where a better key exists, the diagnostic names it: the shared security ID instead of the ticker, the price date to include, the identifier type to pin, or a join path the firm has already confirmed. The analyst can also run the check on a query before executing it.

The check has to be cheap and never block an answer:

• Each probe is one bounded GROUP BY. At most 8 run per query, each with a 15-second timeout and 20 seconds in all, on the firm's own warehouse.
• Probes are cached per table, key and predicate set. Rerunning or paging a query over the same joins costs nothing more.
• It skips what cannot fan out. Derived tables already grouped on the join key, and aggregates a duplicate cannot change, such as MIN, MAX and ANY_VALUE, need no probe.
• It fails open. A probe that errors or times out is skipped, and tables too large to profile in full are left alone.

We scored the check in isolation against 32 queries written the ways analysts write SQL: CTEs, correlated and uncorrelated subqueries, comma joins, USING, UNIONs and pre-deduplicated derived tables. It caught every hazard and raised no false positives on the correct queries.

The warehouse already encodes most of its own semantic layer

The second gap in a plain connector is semantics. Column names rarely state a table's grain, which predicates always apply, or that SEC_ID in one schema is SECURITY_ID in another. The usual answer is a hand-written semantic layer, which at most firms nobody has time to write.

Most of it is already in the warehouse. When a firm connects, Matterfact drafts the first version from the warehouse's own metadata:

• Declared constraints. Primary and foreign keys, where the firm has declared them.
• View definitions. The firm's own views encode correct join paths, often across differently named columns; Matterfact parses the joins and metric expressions out of their SQL.
• Key-name matches. A SECURITY_ID on two tables is a candidate join.
• Snowflake semantic views, where the firm has built them.
• The team's query history, if the firm opts in. The joins and metrics analysts already run are the strongest evidence there is.

Every draft is verified against the data before anyone sees it. A join must be unique on one side, and at least 90% of the other side's keys must match; a metric expression must execute on its table. On the test warehouse, drafting kept the seven joins that hold and none of the hazards, including a fund mapping with only an 80% match rate. An admin then confirms or corrects the drafts. Our target is that an admin corrects fewer than one draft in five, rather than writing the layer from scratch (Exhibit 6).

Exhibit 6
Every draft starts from the warehouse's own metadata and is verified against the data before the agent relies on it.

1. Reads the warehouse's metadata (Your warehouse). Declared constraints; View definitions; Key-name matches; Semantic views; Query history, if you opt in.
2. Drafts join paths and metrics (Matterfact). Each with the evidence it came from.
3. Verifies each draft against your data (Your warehouse). Unique on one side; At least 90% of keys match; Metric expression executes.
4. Your admin reviews (Your admin). Keeps, corrects or removes the drafts the agent will lean on most.
5. The agent starts from it (Matterfact). Each join records which side is unique.

Back to 3: every week, the drafts are re-verified against the data.

Drafting runs in the background in bounded passes, at most 60 probes of 20 seconds each, and re-verifies its drafts weekly. Verified joins also record which side is unique, so the agent starts from join paths that cannot fan out.

Query history needs particular care, because it reveals what colleagues have been asking. In Snowflake, the privilege that exposes ACCOUNT_USAGE.QUERY_HISTORY covers every database in the account. Instead, the firm's admin creates one view over one database's successful reads for the last 90 days. Matterfact keeps only the join and metric patterns, a pattern must have run at least twice to count, and no query the agent generates can read that view.

Read-only and auditable has to be enforced, not promised

A security review of an LLM on the warehouse asks what it can do, not what it intends to do. We enforced each answer in the connection, as layers a statement must pass in turn (Exhibit 7):

Exhibit 7
A statement passes four controls before it reaches your data, and the two innermost are enforced by your own Snowflake.

Audited throughout: every session sets QUERY_TAG = 'matterfact-agent', so each statement appears in your query history, and every answer cites its SQL.

In detail:

• The role can only read. The setup script creates a role with USAGE and SELECT and nothing else, scoped to the databases, schemas or tables the firm chooses. Masking and row access policies apply as they do to any user.
• Authentication is key-pair, not password. Matterfact generates the key pair and the firm installs only the public key on a service user. The private key goes straight to an encrypted secrets store and never reaches a browser.
• Every generated statement is parsed before it executes. Only a single SELECT passes; any DML, DDL, grant or second statement is rejected wherever it appears in the text.
• Every result is capped. At most 1,000 rows come back from any statement.
• Every statement is attributable. Each answer cites the exact SQL behind it, and every session sets QUERY_TAG = 'matterfact-agent', so the firm can list everything Matterfact ran from its own QUERY_HISTORY.

For the data team, the tag matters as much as the controls. Being able to audit every statement the LLM ran, in the firm's own system, is what lets a firm keep trusting the numbers.

What to ask of any warehouse connector

Before putting an LLM on your warehouse, ask four questions of it:

1. Does it verify that a join cannot fan out or drop rows, or does it trust the generated SQL?
2. Where does it learn your schema's semantics, and who verifies what it learned?
3. Can it write, can it read colleagues' query history, and can you audit every statement it ran?
4. How was it evaluated: against known answers, on data with the hazards your data has?

Join verification and semantic drafting are enabled per firm while we roll them out. If your firm runs on Snowflake, we will enable them with you.