Three Emerging Approaches to Agent Analytics

One of the areas where teams are looking to use AI is in understanding their data. For the longest time, companies spent lots of money building dashboards, data pipelines and reports, only to find that the next business question still needed someone to write a new query or add another filter. Now everyone wants an agent that can answer those questions directly.

Pointing a language model at a database and asking it to write SQL sounds like the obvious shortcut, until you look at how often it fails in practice. Real warehouses have ambiguous column names, multiple tables that look like revenue, implicit join rules and business definitions that live in people’s heads rather than in the schema. A model can write valid SQL that runs without errors and still return a plausible, completely wrong number.

Recent engineering write-ups and projects across the industry show how much scaffolding it takes to make this reliable:

• Anthropic shared that when their internal data team first ran analytics queries through Claude without a governed semantic layer and curated skills, accuracy sat at 21%. Moving above 95% required making a semantic layer the mandatory first path, narrowing retrieval to roughly 30 curated reference files and enforcing documentation freshness in CI.
• dbt Labs reran their Semantic Layer vs. text-to-SQL benchmark and found that while raw text-to-SQL accuracy nearly doubled since 2023 (from 32.7% to 64.5%), it still failed silently on normalized schemas by returning plausible wrong numbers. Routing the same models through the dbt Semantic Layer pushed accuracy on covered questions to 98%–100%, and unsupported questions failed explicitly instead of guessing.
• Snowflake built Cortex Analyst around YAML semantic models and verified query repositories after finding that raw models infer their own formulas for domain metrics.
• Databricks wrote directly about why pure text-to-SQL breaks in production when models have to guess business logic, filter conditions and join paths from raw table names. That is why they built Unity Catalog Metric Views and Genie so agents resolve governed definitions deterministically rather than inferring formulas on the fly, alongside a feedback loop that mines recurring user questions into new semantic definitions.
• NVIDIA open-sourced Auto Ontology, which exposes read-only semantic tools to agents and deliberately omits raw schema browsing so queries are grounded in certified entities, relationships and lineage.
• Apache Ossie (incubating, formerly Open Semantic Interchange), backed by Snowflake, dbt Labs, Salesforce and others, exists because every BI tool, query engine and AI agent otherwise ends up reinventing its own incompatible format for metrics and business logic.

Every one of these efforts is wrestling with the same underlying problem. Shared concepts and relationships help teams agree on business meaning, but an analytics agent also needs executable definitions, a way to interpret requests and controls over how it obtains answers.

A valid analytical plan can still answer the wrong business question.

Across all of this work, three distinct themes keep emerging for how teams approach the problem:

1. Context-guided SQL (brute-force schema + context): Feed the database schema, business rules and examples into the prompt and let the model write SQL directly. This works well for narrow use cases where the consumer already knows what SQL should be produced, for quick proofs of concept where an expert verifies the mistakes, or for technical audiences like data engineers and BI analysts investigating something strange in the data.
2. Predefined metric tools: Restrict the model to calling governed functions for established metrics with supported parameters, so the backend owns the SQL and formulas. This suits recurring operational questions where business users need consistent definitions across teams.
3. Typed intent compilation: Have the model emit a structured analytical plan (an intermediate representation, or IR) that a deterministic compiler validates and turns into SQL. This fits bounded reporting workflows and machine consumers that need an inspectable plan and a strict output contract.

To see how these three approaches behave side by side without vendor marketing in the way, we can walk through a simple case study on a synthetic vetkoek business and look at how each one handles the exact same request.

A simple case study: the vetkoek business#

Imagine a small business running several vetkoek stalls across town. Every day the stalls record orders, item quantities, ingredient costs, spoilage and cancelled transactions. Even in a business this small, the data has traps: cancelled orders sit in the same table as completed ones, ingredient costs are tracked per batch rather than per order, and “profit” could mean gross margin after ingredients or net profit after stall rent and wages.

Now someone asks a deceptively simple question:

“Which stall had the highest gross profit last week?”

Here is how each of the three approaches works through that question.

1. Context-guided SQL gives the model room to construct queries#

In the first approach, you feed the model the vetkoek schema and business context upfront: ontology definitions, metric definitions, table mappings, approved views and calendar assumptions. It writes a SQL query from scratch and submits it to a controlled executor.

To answer the vetkoek question, the model has to resolve what “gross profit” means from the prompt, find the sales and ingredient cost tables, join them at the right grain so batch costs are not multiplied across order lines, exclude cancelled transactions, calculate the date boundaries for “last week” and order the stalls from highest to lowest.

You can give it considerable help. A semantic model can specify the calculation, while curated examples demonstrate approved query patterns. Snowflake’s verified query repository is one example of pairing questions with reviewed SQL to guide generation.

The attraction is flexibility. A BI analyst or data engineer investigating why Tuesday margins suddenly dropped at one stall may need an ad hoc combination of joins and window functions that nobody anticipated when designing a metric API. SQL provides a broad language for expressing that investigation.

That freedom also expands what must be checked. A query can execute without errors while double-counting sales through a join, averaging percentages incorrectly or including cancelled vetkoek orders. Supplying the correct definition in context does not force the generated query to implement it. Read-only execution prevents writes, and access permissions and resource limits can be enforced at the database layer, but none of those establish that the query actually measures what the user asked for.

Even with those risks, brute-forcing the schema and context into the prompt and letting the model generate SQL is very effective in a few specific situations:

• Narrow, well-understood use cases where the consumer already knows what SQL should be produced and wants speed rather than a black-box answer.
• Quick proofs of concept where mistakes are caught and verified by an expert before any number is shared further.
• Technical audiences, such as data engineers or BI analysts, who are digging into an anomaly in the data and can read the generated SQL to verify whether the join and filter logic makes sense.

2. Predefined metric tools restrict the available calculations#

I initially called the second approach “semantic tools.” A clearer name is “predefined metric tools,” because the model can request only known metrics through supported operations.

In the vetkoek business, the backend already owns the formula for gross_profit, the join between sales and ingredient batches, and the rule that excludes cancelled orders. The model only chooses the metric and supplies supported parameters:

query_metric(
    metric = gross_profit,
    group_by = stall,
    period = last_full_week,
    order = descending,
    limit = 1
)

This is a schematic interface, not a particular product API. Its contract must define which combinations are valid.

The model cannot invent its own formula for gross profit. If someone asks the vetkoek agent for net profit after stall rent, and operating expenses are not in the data, the tool rejects that request explicitly instead of quietly returning gross profit under a different label.

Existing semantic engines provide useful building blocks here. For example, MetricFlow constructs SQL from centrally defined metrics and semantic models. An agent-facing tool can place a restricted interface over that kind of backend.

The model can still direct an investigation. It may discover available metrics, query revenue, inspect gross profit and request a breakdown by stall. Any derived calculation promised by the system needs an approved execution path too; asking the model to improvise the arithmetic in prose would weaken the contract.

This works well for recurring business questions covered by a maintained metric catalogue. Several consumers can request the same definition without each generating its implementation.

The restriction does not resolve every error. The model can request revenue when the user meant profit, select the wrong valid period or explain a correlation as a cause. Catalogue coverage is another cost: if an important question requires an unsupported calculation, someone has to define and review it first.

3. Typed intent compilation makes the proposed operation explicit#

In the third approach, the model produces an intermediate representation, or IR. This is a structured description of the analytical request. A backend validates it, resolves identifiers and dates, compiles it into SQL and executes it.

For the same vetkoek question, the model does not write SQL or call an imperative tool loop. Instead, it emits a typed plan:

{
  "operation": "rank",
  "metric": "gross_profit",
  "group_by": ["stall"],
  "period": "last_full_week",
  "order": "descending",
  "limit": 1
}

The model chooses the intent. The backend controls how that accepted intent becomes a calculation. This can be one step inside a larger agent, even if the individual request follows a prescribed pipeline.

A typed plan gives you something concrete to inspect before execution. You can display the chosen metric, resolved dates, filters and definition version. A user who meant revenue can spot the mismatch before accepting the answer.

Prompting the model to “return JSON” is not enough; the schema has to be enforced. JSON Schema provides structural constraints such as required properties and rejection of additional properties; backend checks must also reject unsupported combinations.

Even a perfectly enforced schema accepts many possible valid requests. It cannot establish that gross_profit was the right selection for the original question. The compiler can execute the wrong valid plan consistently.

IR also requires design and maintenance. A schema that handles ranking stalls may not express cohort analysis or a complex period comparison. Making it more expressive expands the compiler and validation work. Keeping it narrow means returning explicit unsupported responses for some useful requests.

4. What happened when I tested all three on the vetkoek holdouts#

While deciding how to structure our own analytics layer, I ran all three approaches against this synthetic vetkoek business. They used the same business rules, model and data snapshots, and the predefined metric tools and IR approaches shared a calculation runtime.

After earlier trials, I tested eight newly authored questions against both baseline data and a stress variant. The questions included six calculations, one ambiguity requiring clarification and one unsupported request requiring refusal. The stress data introduced ties, zero baselines, zero-sales stalls, negative gross profit and uneven margins.

Approach	Baseline automated acceptance	Stress automated acceptance
Context-guided SQL	7/8	7/8
Predefined metric tools	7/8	7/8
Typed intent compilation	8/8	8/8

At first glance, 7/8, 7/8 and 8/8 looked like a clear win for typed intent compilation. Inspecting the actual outputs told a more useful story. The lower-scoring approaches had returned the correct calculations, but with extra rows or columns that violated the requested output shape. The SQL approach returned the correct comparison values with helper columns and a different column alias. The metric-tool approach returned the correct top two stalls plus an extra third row. Separate adjudication confirmed those specific answers against the independent reference calculations, and I preserved the automated failures.

For a machine consumer expecting exactly two rows, the extra row remains a defect. But it is a different defect from choosing the wrong metric or calculating the wrong value. The distinction affects what you fix.

IR used fewer input tokens and finished faster at the median in these holdout runs. That is useful evidence for this implementation. Token counts included cached and repeated input, so they were not a bill. The tool-based approaches also had opportunities to repair errors within a call budget, while IR had one application-level generation.

These were eight language questions repeated against two datasets. They were internal, same-domain holdouts, not proof of transfer to another enterprise. I did not test a live clarification-and-resume conversation or a persistent feedback loop.

---

Closing Thoughts#

Agent analytics is still a difficult, developing area of AI, and no single architecture fits every team or workload yet. These three approaches also overlap in practice: a predefined tool can accept a typed plan, a SQL-generating agent can query governed views, and both metric tools and IR can sit on top of the same semantic engine.

Start with a tight scope, allow “I do not know,” and build a feedback loop.

Workload	Approach I would evaluate first	Main tradeoff
Exploratory analysis or quick POCs with expert review	Context-guided SQL	Broad query freedom requires someone who can inspect the SQL and result
Repeated questions using established metrics	Predefined metric tools	Consistency depends on catalogue coverage and correct tool selection
Bounded reporting with a stable result contract	Typed intent compilation	Inspectability requires an expressive, maintained schema and compiler

Because no single approach solves every problem out of the box, getting this right in production has less to do with chasing a universal agent and more to do with discipline around scope:

• Be specific about what needs answering first. Define the high-value questions and metrics your users actually rely on rather than promising an open-ended analyst for the entire warehouse on day one.
• Accept “good enough to start.” If your system can reliably answer the core twenty questions that drive weekly decisions, ship that boundary rather than waiting until it can answer everything.
• Make “I do not know” a first-class answer. When a user asks a new question outside your supported metrics or schema, it is much better for the agent to refuse cleanly and say what is missing than to improvise a plausible, wrong SQL query.
• Use refusals as your feedback loop. Every unsupported question or clarification is a signal showing what users actually want to ask next. Log those gaps, review them with domain owners, and promote the recurring ones into governed metrics, verified queries or supported IR operations as demand proves out.

I would love to hear your thoughts on how you manage the balance between flexibility and governance in agent analytics. What strategies have you found effective for defining scope, handling refusals, and iteratively improving your system? Thanks in advance for sharing your experiences and insights!