Building a Verified-Query Flywheel | Cloud Data Consulting

Text-to-SQL agents can now answer a substantial range of business questions without requiring a user to translate each request into SQL. When the agent operates against a governed semantic model, many of those answers can be tied to reviewed definitions and repeatable query logic. No model covers every question, and eventually a user asks for a metric, dimension, or analytical relationship that was never defined, leaving the agent to construct its own SQL and settle on a plausible reading of the request.

SQL generated by an agent carries more risk, especially when the answer resembles an established KPI but uses a different definition. The same event also reveals a question the current model was not built to answer.

We use these unmatched questions as inputs to a structured improvement process. Real usage identifies the gaps in the semantic model, and a human review process determines which gaps should become governed capabilities. We refer to this process as the verified-query flywheel.

That creates a reinforcing loop rather than a maintenance chore. Analysts and engineers use observed behavior to decide what to add, correct, or test. Each governed definition makes the next answer more likely to be verified, which invites more use, which surfaces the next gap worth closing.

The word flywheel is deliberate. The first turns are the expensive ones: telemetry has to be captured, a replay suite has to exist, and the business has to settle definitions it may never have written down. Momentum accumulates after that. Every verified query added is coverage that does not have to be rebuilt, every settled definition is an argument that does not have to be held again, and every regression test makes the next change safer to make. The process takes considerably less energy to sustain than it took to start, and it moves faster the longer it runs.

Verified queries and generated queries serve different purposes

A verified query is a reviewed SQL definition associated with a known business question or analytical pattern. In Snowflake, verified queries are defined in the semantic view itself rather than in application code, so approved logic travels with the model that serves it. When an agent matches a request to that query, the answer is based on logic that the organization has already approved.

When no verified query matches, the agent may generate SQL dynamically. This allows users to explore questions outside the current boundaries of the semantic model, but with the caveat the resulting definition has not been reviewed.

Generated, however, does not have to mean ungoverned. When Cortex Analyst generates against a semantic view, it now prefers semantic SQL, where metrics, joins, and filters resolve to definitions the view already holds, and falls back to SQL over physical tables when the view cannot express the request. A generated answer may therefore rest largely on approved definitions, or it may sit entirely outside them. The core problem here is that the person reading it cannot see which is which.

Both paths are useful, however the interface must keep them distinct, specifically: A verified answer and a generated answer should not look alike to the person reading them.

In our implementation, answers fall into two categories:

A verified answer cites the verified query used to produce it and can be evaluated against an approved business definition.
A generated answer identifies itself as unverified and includes the metric definition the agent applied.

For KPI-related questions, generated responses include a warning such as:

Generated answer. This result was not matched to a verified query in the semantic model. Confirm the definition before using it for reporting or decision-making.

The response also qualifies the metric itself. In one generic retail example, a bare Transaction Count: 204 silently included seven fee and expense rows. The qualified response returned Transaction Count (excluding returns and fees): 197. The value now carries the definition that produced it.

The distinction matters most in multi-turn conversations, where an ambiguous definition introduced in the first answer propagates into every calculation that follows. Displaying the definition alongside the value gives the user enough context to evaluate the result and gives the development team a record of the interpretation that was used.

Generated queries therefore remain available for exploration, but their presentation should reflect how much of the answer rested on governed definitions and where the agent had to move past them.

How the flywheel turns

The operating process has five stages.

1. Answer the question

A business user submits a question in natural language. The agent first attempts to match it to a verified query.

When a match is found, the response identifies the query that supported the answer. When no match is found, the agent may generate SQL, decline the request, or explain a limitation in the available data.

2. Capture the result

The system records enough information to determine how the request was handled.

We run a curated test suite based on variations of our verified queries and classify unmatched responses by their behavior. For production usage, available telemetry identifies sessions that require investigation. Snowflake’s live API response supplies warnings and verified-query match information. We use these incidents in our next step.

3. Review the signal

A useful generated answer may become a candidate for the verified-query backlog. The candidate includes the original business question, the proposed definition, and an initial SQL implementation.

Warnings are reviewed separately because they often indicate a defect in the semantic model rather than a missing business capability. Responses that appear unsupported or misleading are escalated for human review.

4. Update the model

Approved candidates enter the semantic model at whichever layer makes them reusable: a verified query for a recurring analytical pattern, or a metric, filter, or relationship when the underlying concept will serve more than one question. Model defects are corrected through the appropriate metadata, relationship, or field-definition changes.

Each deployment produces a versioned snapshot so that the released model can be compared with previous versions and restored when necessary.

5. Run the regression suite

A curated set of representative questions is submitted again after the change.

The suite verifies that expected queries still match, warnings have not been introduced, and response performance remains within an acceptable range. The updated model is promoted only after these checks pass.

Each full turn leaves the model with more governed coverage than it had before, and leaves the next turn cheaper to complete: the replay suite is larger, the definitions it depends on are already settled, and the questions that remain unmatched are increasingly the genuinely new ones.

Capturing the necessary signals

Implementing the process requires telemetry that connects execution behavior back to the request that produced it. Two different sources are involved:

Snowflake’s account usage history records that a request happened, capturing request identifiers, timing information, token consumption, and compute statistics. It is the right place to look at cost and performance. What it does not capture is what was asked or what came back.

On the other hand, Cortex Analyst request logging supplies the missing half. Analyst writes its requests to an event table, and the log includes the user, the question asked, the generated SQL, any errors or warnings, and the request and response bodies. A table function exposes the same records for a given semantic model or view. Earlier implementations had to capture this from the live API response at the time of the call or lose it; that is no longer the case (thanks Snowflake!), and a review process built today should treat the request log as the primary record of how the model handled a question.

We therefore combine request-level logging, a curated replay suite, and production usage history.

Request logs

We read the logged payload for the verified-query match and for any warnings raised during evaluation.

This makes it possible to answer questions such as:

Did the request use approved query logic?
Did the semantic model reject or ignore part of a verified query?
Did the agent generate SQL without a verified match?
Did the model identify a limitation in the available fields or grain?

Because these records are retained rather than observed in flight, the same fields serve both targeted model validation and durable production history. Application-side logging still adds what Snowflake cannot see, such as what the user did with the answer.

Curated replay tests

Regression testing operates at two levels.

Cortex Analyst evaluations provide a native baseline for semantic-model correctness, and they use verified queries as their own ground truth. Analyst generates SQL against a temporary copy of the view with the queries under evaluation removed, so it cannot simply match its way to the answer. It then executes both the generated and the verified SQL and compares the results, reporting accuracy, regressions against previously passing queries, and latency.

That measures whether the SQL is right. It does not measure whether the response was appropriate. We supplement those evaluations with a curated replay suite that tests the behaviors correctness scoring does not reach: intentional gaps, graceful declines, qualification language, warning handling, and the organization’s own expectations about ambiguity. A query can be technically correct while the answer around it still violates how the organization wants unsupported questions treated.

The replay suite is a fixed collection of representative business questions. Each question is tagged with its expected behavior.

Some questions should match a particular verified query. Others are intentional gap tests designed to examine how the agent behaves when the model does not support the request.

For unmatched questions, the test harness distinguishes among several outcomes:

Graceful decline: The agent explains why the question cannot be answered and identifies a genuine limitation, such as data that is available weekly but not daily.
Qualified answer: The agent provides an answer but uses language such as “estimated” or “approximate.” These responses require review because the qualification may or may not reflect a legitimate calculation.
Generated query: The agent constructs SQL and returns a result without using a verified query. These results are reviewed as potential additions to the semantic model.

Because the same questions are run repeatedly, the suite also functions as a regression test. It can identify a verified query that no longer matches after a model change, a newly introduced warning, or a material increase in response time.

Production usage history

Curated testing shows how the model behaves on questions we anticipated, but production telemetry is needed to find problems in day-to-day use.

We therefore maintain diagnostic views over the available production telemetry. Because Cortex tags the SQL it issues, an agent turn can be correlated with the statements it executed. One view separates total response time into orchestration time and database execution time and records indicators such as bytes spilled to local or remote storage.

Another view classifies sessions according to observable patterns, including:

Repeated SQL attempts within the same session
Sessions in which every SQL statement failed
Long narrative responses with no successful query execution
Unusually high end-to-end latency

We also maintain a companion view for direct-query traffic that bypasses the conversational agent.

These classifications identify sessions that merit investigation. Joined to the request log, the runtime symptoms can be traced back to the question, the generated SQL, and the warnings that accompanied the response, so an investigation starts from the actual interaction rather than a reconstruction of it.

One gap remains, and it is a matter of topology rather than tooling. When the conversation originates in a chat client connected over MCP, Snowflake sees the request the agent sent to Analyst, not necessarily what the user typed. An agent may rephrase a question, split it into several requests, or carry context from earlier turns that never appears in any single call. The surrounding conversation still lives in an application we do not control.

An example from production usage

In one deployment, a diagnostic view flagged a session as a retry storm: 208 seconds of wall-clock time and four SQL attempts fighting the same column-name mismatch, where a query referenced a physical column name and the model expected the logical one. Two of those attempts each spilled 3.6 GB to disk.

The usage tables showed the execution behavior but not the question behind it, so recovering what the user actually wanted meant going back and asking them directly. Request logging would surface that question today without the interview, which is worth noting as an example of the platform absorbing a step that used to be manual (again, thanks Snowflake!). The question was:

How many new professional customers did we acquire this year by month, and how much incremental revenue did they generate?

This was not a one-off exploration but part of a recurring monthly review, exactly the kind of question that earns a permanent definition.

We added the request to the candidate backlog and worked with the business team to define when a customer should be considered newly professional. That definition was necessary before the SQL could be treated as authoritative.

After the definition was approved, the query was implemented and tested as a verified query. The final version avoided the repeated joins and failed retries produced by the generated path.

The improvement affected both governance and performance. Future users received the approved definition, while the agent executed a more efficient query.

The larger return was the definition itself. Settling the business definition of what makes a customer newly professional was the slow part of that work. Every later question that depends on the concept now starts from an approved answer instead of reopening the discussion.

Converting signals into model changes

Captured signals generally result in one of several kinds of semantic work. A recurring question may justify a verified query, but the more reusable correction is often a metric, filter, relationship, description, or instruction that improves an entire class of questions rather than just one of them.

The review step therefore asks two things: whether the behavior is worth governing, and at what layer the correction belongs. A verified query is the right answer when a recurring analytical pattern deserves an approved reference implementation. A reusable business concept belongs in the semantic layer, where questions nobody has asked yet can benefit from it. A defect in an existing definition or relationship should be repaired at its source rather than papered over with another verified query.

Snowflake has started to make this distinction for you. Its optimization feature reads a verified query such as “How many active users did we have last month?”, extracts how the organization defines active, and proposes an is_active filter carrying that exact definition. The verified query was the occasion; the filter is the reusable asset. Suggestions of this kind require human review before they are added, which keeps the approval boundary appropriately where it belongs.

The goal is not to maximize the number of verified queries. It is to increase the share of real analytical questions that can be answered from reusable, governed semantics.

Adding verified-query coverage

Generated queries enter a review backlog when they appear useful enough to support formally. A backlog entry should contain enough context to evaluate both the business need and the technical implementation.

A typical entry includes:

The user’s question and relevant context
The business decision or workflow supported by the answer
The definition used by the generated query
A proposed SQL implementation
Open questions that require business approval

Promotion should not be automatic. A generated query may be syntactically valid and still rely on an unsettled definition.

Customer lifetime value is a common example in that the agent can construct a reasonable calculation, but the organization may require a specific treatment of customer tenure, margin, acquisition cost, inactive periods, or customer segments. Until those decisions have been made, the query should remain exploratory.

The review process often reveals that the missing component is a formal business definition rather than SQL. Recording the question gives the business and data teams a concrete reason to resolve the ambiguity.

Correcting semantic-model defects

Warnings from the model support a different workflow.

A warning may indicate, for example, that a verified query refers to a physical column name that the semantic layer does not recognize. In some implementations, the invalid query is omitted from the compiled model rather than causing the deployment to fail.

The deployment can therefore appear successful even though verified-query coverage has decreased.

Reviewing warnings on a schedule helps detect this type of regression. Each warning should be connected to a specific remediation, such as correcting a field reference, updating a relationship, or aligning the verified query with the semantic model’s logical field names.

Warning counts alone are not sufficient. The team needs to inspect the warning type, determine its effect on user-facing behavior, and confirm the repair through replay testing.

Versioned deployment and rollback

Changes to a semantic model should be traceable to the source version that produced them.

Our deployment process writes the current production model and an immutable snapshot associated with the exact source-control commit. The production object is the version used by the agent. The snapshot provides a stable record of what was deployed at that point in time.

This supports several operational needs:

Comparing generated behavior before and after a change
Identifying differences between source-controlled and manually edited models
Reviewing a previous model during incident analysis
Restoring a known version when a release causes a regression

The replay suite evaluates a change before release. Versioned snapshots support investigation and recovery after release. Both are required for a controlled production process.

Measuring whether the process is working

Verified-query match rate is useful, but it should not be evaluated in isolation.

A system can achieve a high match rate by testing only a narrow set of established questions. Match rate alone does not show that the model is becoming more useful.

A healthier measurement considers coverage and match rate together. The question set should expand as new analytical needs are identified, while the percentage of supported questions remains stable or improves.

In one production baseline, 94 percent of the representative questions matched a verified query, and median response time was under nine seconds. The remaining question was an intentional gap test. The agent handled it correctly by explaining the boundary of the available data rather than constructing an unsupported answer.

Other useful measures include:

The number of business questions covered by the replay suite
The percentage of generated answers later approved as verified queries
Warning volume by type and model version
SQL failure and retry rates
Median and tail response latency
The age of items in the verified-query candidate backlog

The purpose of these measures is to show whether the model is supporting more real business questions without reducing reliability.

Read across releases, they also show whether the flywheel is gaining speed. Coverage should be growing while the effort behind each turn falls: fewer escalations per new question, less time between a candidate and its verified query, and a backlog that ages down rather than up. A loop that merely runs holds its numbers steady. A flywheel improves them while carrying more.

Conclusion

A governed semantic model gives an analytics agent a reliable foundation, but its initial coverage will always be incomplete. Users will continue to ask questions that fall outside the definitions already modeled.

The semantic model is also only one layer of a production system. An agent can fail because it chose the wrong tool, passed the wrong inputs, or summarized a correct result badly, and none of those failures are semantic. The flywheel described here deliberately addresses one layer: the governed definitions available to the agent. The same principle applies a level higher, where agent traces and tool-selection metrics can drive improvements to routing and response generation. The semantic model remains the authoritative layer for what the business means.

Those questions should be observable. Generated answers should identify the logic they used, production telemetry should support investigation, and recurring business needs should enter a review process before they are added to the governed model.

The resulting operating model combines controlled exploration with deliberate semantic development. Users can investigate new questions, while analysts and engineers retain responsibility for deciding which definitions become authoritative.

The verified-query flywheel connects those activities: capture unsupported questions, review their value and definitions, implement approved queries, and test each change before release. The model gains governed coverage while analysts, engineers, and business owners retain control over the definitions.

What makes it a flywheel rather than a checklist is what happens on the second and third turns. Early on, most of the work is foundational and most answers are generated. As coverage grows, a larger share of questions resolve against approved logic, review shifts from routine gaps to genuinely new analytical needs, and the definitions and tests already in place carry much of the weight of each subsequent change. A team that stops after the first turn pays the full cost of starting and collects none of the return. A team that keeps turning it arrives at the point where governed coverage is the default path rather than the expensive one.