GenAI & Claude for Analytics

Why “just don’t let it write anything harmful” isn’t a real control

Telling an agent, via prompt instructions, “only run SELECT queries” is not a security boundary — it’s a request the model might not follow, especially under an unusual phrasing, a bug in the prompt, or a prompt-injection attempt embedded in data the agent reads. The actual control has to live below the model, at the database or warehouse layer, where it can’t be talked around.

What the safe default looks like in practice

Grant the credential the agent’s queries run under a role that is, at the infrastructure level, incapable of writing:

-- Illustrative — exact syntax varies by warehouse
CREATE ROLE analytics_agent_readonly;
GRANT SELECT ON SCHEMA analytics_views TO analytics_agent_readonly;
-- No GRANT INSERT / UPDATE / DELETE / CREATE / DROP, ever, for this role.

If that role literally does not have INSERT, UPDATE, DELETE, CREATE, or DROP privileges, then no generated SQL — no matter how the model was tricked or confused into producing it — can modify anything. This is the same principle as the read-only-role guidance in the MCP server design and auth cards: least privilege isn’t a nice-to-have, it’s the thing that limits the blast radius when (not if) something goes wrong upstream of the database.

Allow-listing on top of read-only

Read-only is necessary but often not sufficient on its own — you generally also want to constrain what can be read, via allow-listed views or schemas rather than every table in the warehouse (see the companion card on exposing parameterized tools instead of raw SQL). A read-only role that can still SELECT * from every table, including ones with sensitive columns nobody meant to expose through a chat interface, is safer than a writable one but still broader than it needs to be.

Why write capability deserves its own separate, reviewed pathway

If there’s a genuine business need for an AI-assisted workflow to fix data (e.g., correcting a known bad record), that’s a fundamentally different risk profile than answering questions, and it deserves its own explicit design: a narrow, purpose-built tool (not a general SQL-writing capability), almost certainly gated behind human approval for every execution (see the companion card on human-in-the-loop approval), and probably its own audit trail separate from the read-only Q&A path. Bolting “oh, and it can also write sometimes” onto a general-purpose analytics agent conflates two very different risk levels into one system, which makes both harder to reason about and secure.

What a semantic layer actually is

A semantic layer sits between raw warehouse tables and anyone (or anything) asking business questions. It defines, once, the things that would otherwise be re-derived ad hoc every time someone writes a query: which tables join on which keys, how to filter out test accounts or canceled orders, and what “active,” “revenue,” or “churn” precisely mean as calculations. Consumers — dashboards, BI tools, and now LLM agents — reference these definitions by name instead of writing the underlying logic themselves.

Why an LLM makes this more important, not less

A human analyst who’s been on the team for a year has internalized a lot of tribal knowledge: “always exclude internal test accounts,” “active means logged in AND took a qualifying action, not just opened the app,” “we changed how we count trials in Q2, don’t compare across that boundary.” An LLM generating SQL from scratch has none of that — it’s working from table and column names, which look self-explanatory but usually aren’t. Two different but reasonable-looking prompts can easily produce two different, both “syntactically correct,” definitions of the same business concept.

Worse, this failure mode is quiet. The generated SQL runs fine, returns a number, and nothing about the output signals “this used a nonstandard definition.” Compare that to a human analyst who at least has a shot at going “wait, that doesn’t look right” based on institutional memory the model doesn’t have.

The fix, in one sentence

Give the model access to pre-defined metrics and dimensions from a semantic layer instead of raw tables, so it’s selecting from a small set of vetted, unambiguous building blocks rather than improvising business logic on every request. See the companion cards on MetricFlow-style metric definitions and on the concrete “two different active-user numbers” failure mode for what this looks like in practice.

Spot-checking versus an eval suite

Spot-checking is what most teams do first, and it’s fine for a demo: try a handful of questions you can think of off the top of your head, eyeball the answers, ship it. The problem is it’s neither repeatable nor representative — it depends on whoever happened to test that day remembering to try the right kinds of questions, it produces no record you can compare against next time, and it silently misses whatever you didn’t happen to think of testing.

An eval is the same idea as spot-checking, formalized: a fixed, versioned set of test cases with known-correct expected results, that you run the same way every time, and whose pass rate you can track over time and compare across changes.

Why this is basically unit/regression testing for a non-deterministic system

Software engineers wouldn’t ship a code change without running the test suite; the same discipline applies to a prompt or model change in an LLM-powered feature — except the “assertions” are fuzzier (a generated SQL query might be phrased differently but still correct, an LLM’s free-text summary might be worded differently but still accurate), which is exactly why evals need more thoughtful grading than a simple equality check. See the companion cards on execution accuracy and on LLM-as-judge grading for how that grading actually works.

A minimal eval record

{
  "id": "eval-042",
  "question": "What was our revenue by region in Q2?",
  "expected_sql": "SELECT region, revenue FROM metric_revenue WHERE quarter = '2026-Q2' GROUP BY region",
  "expected_result_summary": "Revenue broken out by region for Q2 2026",
  "category": "aggregate-metric",
  "difficulty": "easy"
}

Run every record in your suite against the candidate prompt/model, grade each one (see execution accuracy for text-to-SQL specifically), and compare the aggregate pass rate to your last known-good baseline before shipping anything.

The practical difference it makes

Six months from now, when someone tweaks the system prompt to fix one specific complaint, an eval suite tells you immediately whether that fix also broke five other question types that used to work — something spot-checking “a few questions” will essentially never catch, because nobody re-tests the things that already seemed fine.

Metrics over tables, as a default

If the agent can query raw tables directly, every question forces it to reconstruct join logic and business definitions from scratch — which, as covered elsewhere, produces inconsistent answers to the same underlying question asked in different ways. If the agent can only reach pre-defined metrics (revenue, active_users, churn_rate) exposed through the semantic layer, it’s choosing from a small, vetted menu instead of improvising. That’s a large reduction in both error surface and answer variance, at the cost of not being able to answer truly novel questions the semantic layer hasn’t modeled yet — which is usually the right trade for anything customer- or exec-facing.

Headless BI: decoupling the answer from the visualization

“Headless BI” means the metric calculation is a standalone service, independent of any particular front end that renders it. Traditionally, a metric’s logic might live half-buried inside a dashboard’s query definition — which means if someone wants that same number in a different tool, they either can’t get it or have to reimplement the logic and hope it matches.

A semantic layer is the headless-BI approach: the metric definition lives in one place, and any consumer — a BI dashboard, a scheduled email report, a Claude Q&A tool — calls into it and gets the same number back. The “answer” (the metric value) is decoupled from the “visualization” (a chart, a table, a sentence Claude writes).

                      ┌─────────────────────┐
                      │   Semantic Layer     │
                      │  revenue, churn_rate,│
                      │  active_users, ...   │
                      └──────────┬───────────┘
              ┌───────────────────┼───────────────────┐
              ▼                   ▼                   ▼
        BI dashboard        Scheduled report     Claude Q&A agent

What this means concretely for the agent

The agent’s tool surface should look like “give me revenue grouped by region for Q3,” not “here’s a table, go write a query.” That framing is what lets the same revenue number the CFO sees in a dashboard be the exact number Claude cites when someone asks it a question in Slack — same source, same logic, same answer, regardless of the interface.

The three primitives

Primitive	Who invokes it	Analogy	Warehouse example
Tool	The model, autonomously, when it decides a call is needed	A function call	get_revenue_by_region(start_date, end_date, region)
Resource	The client application, usually to load context up front or on request	A file or URL the app can read	A markdown doc describing your core metric definitions and table grain
Prompt	The end user, explicitly selecting it (e.g., a slash command)	A saved query or macro	/monthly-business-review — a template that instructs the model to pull a fixed set of metrics and summarize them

Why the distinction matters for design

Tools are the only primitive where the model decides when to act, which is exactly why tool descriptions matter so much (see the companion card on tool-selection ambiguity). A tool’s name, description, and input schema are effectively the model’s only information about when and how to call it — get them vague or overlapping, and the model will guess wrong.

Resources, by contrast, are typically surfaced by the client rather than autonomously fetched by the model mid-conversation, so they’re a good place to put stable reference material the model should always have available — semantic layer documentation, a glossary of metric names, known data-quality caveats — without needing the model to “decide” to look it up.

Prompts put the human in control of which workflow runs, which is valuable for anything you want to be repeatable and predictable rather than reconstructed fresh by the model each time — a recurring exec report is a good prompt candidate; an open-ended “why did revenue drop” investigation is not.

A minimal tool definition

{
  "name": "get_revenue_by_region",
  "description": "Return total revenue for one or more regions over a date range, sourced from the finance semantic layer's revenue metric. Use this for aggregate regional revenue questions, not per-customer transaction lookups.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "start_date": { "type": "string", "format": "date" },
      "end_date": { "type": "string", "format": "date" },
      "region": { "type": "string", "description": "Region name, e.g. 'EMEA'. Omit for all regions." }
    },
    "required": ["start_date", "end_date"]
  }
}

Notice the description does two jobs at once: it says what the tool does, and it says what it’s for — the “use this for… not…” framing is what actually helps the model choose correctly among several similar-looking tools.

Common mix-up

Don’t confuse a “prompt” in the MCP sense (a server-defined, user-selectable template) with a “prompt” in the general LLM sense (any text sent to the model). MCP prompts are a specific, discoverable capability the server advertises — closer to a saved report or a slash command than to a raw instruction string.

The problem before MCP

Without a standard, every new pairing of “LLM client” and “tool or data source” needs its own bespoke glue code: a Slack bot that queries the warehouse, a desktop app that queries the warehouse, an internal agent that also needs the warehouse — three integrations, three sets of auth handling, three places to fix a bug. Add a second data source (a ticketing system, a CRM) and it’s now six integrations. That’s the M×N problem: M clients times N tools, with no reuse between them.

What MCP standardizes

MCP defines a common protocol for:

• Discovery — a client can ask a server “what can you do?” and get back a structured list of capabilities, instead of needing hardcoded knowledge of the API.
• Invocation — a consistent way to call a capability with typed inputs and get back a typed result.
• Three kinds of capability — tools (actions the model can invoke), resources (content the client can read), and prompts (reusable workflow templates). See the companion card on this distinction.

Once your warehouse team builds one MCP server exposing “query revenue by region,” “look up a customer’s usage,” etc., that server works with Claude Desktop, Claude Code, a custom internal agent, or any other MCP-compatible client — without the warehouse team writing custom code for each one. The M×N problem becomes M+N: N servers, M clients, and any client can talk to any server.

Why this matters more for analytics specifically

Analytics data is high-blast-radius (it’s often production data, sometimes PII-adjacent) and behaviorally sensitive (the “right” way to query it usually isn’t obvious from table names alone). A standard protocol doesn’t fix those problems by itself, but it does mean the safety work — parameterized tools instead of raw SQL, auth scoping, rate limiting — gets built once, reviewed once, and reused everywhere, rather than reimplemented (and re-audited) inconsistently across every bespoke integration your organization ships.

What MCP is not

MCP is not a semantic layer, a query engine, or a security boundary by itself — it’s a transport and interface convention. A poorly designed MCP server (e.g., one exposing raw execute_sql) is just as dangerous as a poorly designed custom API. The protocol standardizes how a model talks to your systems; it’s still your job to decide what you expose and how safely.

Why you can’t just trust the generated query and run it

Even a well-designed, parameterized-tool-based system (see the companion MCP design card) can occasionally produce a query that’s technically valid but wildly expensive — a missing filter that turns a scoped lookup into a full-table scan, or a join that fans out rows unexpectedly. The model has no innate sense of “this will scan 40 billion rows” the way an experienced analyst might glance at a query and wince. A pre-execution gate gives you a cheap, mechanical check before committing to the expensive part.

The gate pattern

Most modern warehouses support some form of dry-run or query-plan estimation that doesn’t actually execute the query but tells you what it would cost — estimated bytes scanned, estimated rows, or a query plan you can inspect for red flags like a missing partition filter.

function execute_with_gate(sql, thresholds):
    plan = warehouse.dry_run(sql)  # or EXPLAIN, depending on the warehouse

    if plan.estimated_bytes_scanned > thresholds.max_bytes:
        return gate_result(
            status="blocked_or_needs_approval",
            reason=f"Estimated {plan.estimated_bytes_scanned} bytes scanned exceeds limit",
        )

    if plan.estimated_rows > thresholds.max_rows:
        return gate_result(
            status="blocked_or_needs_approval",
            reason=f"Estimated {plan.estimated_rows} rows exceeds limit",
        )

    result = warehouse.execute(sql)
    return gate_result(status="executed", result=result)

The exact mechanism is warehouse-specific — some offer an explicit dry-run mode that returns cost/row estimates without running the query, others require reading an EXPLAIN plan and pulling estimated cardinality out of it — but the shape of the gate is the same regardless: estimate first, decide, then execute only if the estimate is within bounds.

What “blocked or needs approval” actually means

A query that fails the gate doesn’t have to be a hard dead end. Depending on how far over the threshold it is, reasonable options include: automatically retrying with a narrower date range or added filter, asking the user to confirm they really want something this broad, or routing to a human-approval step (see the companion card on row-count and cost thresholds for how to decide which). What it should never do is silently execute anyway because the gate was inconvenient.

What this catches in practice

• A missing WHERE clause that turns “revenue for this customer” into “revenue for every customer, ever.”
• A join that fans out due to a one-to-many relationship the model didn’t account for, multiplying row counts unexpectedly.
• A query against the wrong grain of table (row-level events instead of a pre-aggregated daily rollup) for a question that didn’t need that level of detail.

None of these are “wrong” queries in the sense of returning incorrect data — they’d often still return a technically correct answer — but they’re wrong in the sense of being far more expensive or slow than the question warranted, and a pre-execution check is the cheapest place to catch that before it becomes a warehouse cost or performance incident.

How an MCP client actually picks a tool

When Claude has access to multiple tools, it decides which one to call (if any) the same way it decides anything else: by reasoning over the text it can see — the tool’s name, its description, and its input schema — against the user’s question. There’s no hidden metadata about “which system is authoritative for this kind of question.” If two tools are named and described similarly, the model is guessing based on subtle wording differences, and it will sometimes guess wrong.

What causes it in practice

• Overlapping names. get_customer_revenue (CRM, per-account contract value) and get_revenue (warehouse, aggregate transactional revenue) sound like they could answer the same question.
• Descriptions that state what a tool does but not when to use it. “Returns revenue for a customer” doesn’t tell the model whether this is the right tool for “what was our revenue by region last quarter.”
• Too many similar tools in context at once. The more overlapping options, the higher the chance of a wrong pick — this gets worse as an organization plugs in more MCP servers over time without pruning or consolidating.

How to fix it

Be explicit about scope and exclusions in the description, not just the mechanism:

{
  "name": "get_warehouse_revenue_by_dimension",
  "description": "Aggregate revenue from the analytics warehouse's revenue metric, grouped by a dimension like region or plan tier, over a date range. Use this for company-wide or segment-level revenue questions. Do NOT use this for a single named customer's contract value or billing history — that lives in the CRM tool (get_crm_customer_account)."
}

Naming the other tool and explaining the boundary between them gives the model a concrete rule to apply instead of a vibe to infer.

Consolidate or prune overlapping tools where you can. If two tools really do overlap in scope, that’s often a signal you should merge them behind one interface (e.g., one tool with a source parameter) rather than exposing both and hoping the model picks correctly every time.

Test tool selection explicitly in your eval suite. A golden dataset of questions can and should include cases specifically designed to probe ambiguous boundaries between similar tools, checking not just “did it get the right final answer” but “did it call the right tool” — see the companion evals cards for how to structure this.

Keep the total tool count reasonable. There’s no hard limit, but every additional tool is additional room for confusion; before adding a new MCP server or tool, ask whether it’s meaningfully distinct from what’s already exposed.

The failure mode, concretely

Conversation A, generated SQL:

SELECT COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE event_type = 'login'
  AND event_date BETWEEN '2026-06-01' AND '2026-06-30'

Conversation B, generated SQL:

SELECT COUNT(DISTINCT user_id) AS active_users
FROM events
WHERE event_type IN ('created_report', 'shared_dashboard', 'ran_query')
  AND event_date BETWEEN '2026-06-01' AND '2026-06-30'

Both queries are syntactically valid, both run without error, and both return a plausible-looking number. Nothing about the output signals that they measure different things — a login-based count of “active” versus an action-based count. If those two numbers reach two different stakeholders, you now have a credibility problem that has nothing to do with the model “getting the SQL wrong” — the SQL was fine, the definition just wasn’t fixed.

Why this is specifically an LLM-amplified risk

A human analyst reusing a query from last quarter’s report would probably use the same definition again, out of habit or because they copy-pasted it. An LLM regenerating SQL fresh for every question has no such continuity — each generation is an independent roll of the dice over how to interpret an ambiguous term like “active,” influenced by subtle differences in how the question was phrased, prior conversation context, or even non-determinism in generation.

The fix

Define active_users once in the semantic layer, with an explicit, documented calculation — say, “at least one qualifying in-product action in the trailing 30 days” — and expose it to the agent as a named metric rather than raw event tables:

metrics:
  - name: active_users
    type: simple
    type_params:
      measure: qualifying_action_count
    filter: |
      {{ Dimension('event__event_type') }} in ('created_report', 'shared_dashboard', 'ran_query')

Now both conversations, regardless of phrasing, resolve to the same metric definition — because neither one is writing the aggregation logic itself, they’re both just asking the semantic layer for active_users. If the definition of “active” ever needs to change, it changes in exactly one place, and every future question — and every past dashboard built on the same metric — stays consistent with it.

Where good test questions actually come from

The temptation is to write eval questions yourself, which reliably produces a dataset that’s biased toward whatever you already expect the model to handle well. Better sources:

• Real user questions from support tickets, Slack channels, or logged queries to an existing tool — these surface the actual phrasing, ambiguity, and edge cases real people produce, which is very different from the clean, well-specified questions an engineer writes for a test.
• Known hard cases collected from prior failures — a question that once produced a wrong or confusing answer belongs in the suite permanently, so a regression on it is caught automatically going forward.
• Deliberately adversarial or ambiguous phrasings — “how many users do we have” (active? total signups? in what time window?) — to test whether the model asks a clarifying question or guesses.
• Coverage across your semantic layer, not just the metrics that happen to be popular — a metric nobody’s tested is a metric you have no evidence works.

What one record needs

{
  "id": "eval-118",
  "question": "How many customers churned in each region last quarter, compared to the quarter before?",
  "expected_sql": "SELECT region, quarter, churned_customers FROM metric_churn WHERE quarter IN ('2026-Q1', '2026-Q2') GROUP BY region, quarter ORDER BY region, quarter",
  "expected_result_summary": "Churned customer counts by region for Q1 and Q2 2026, for comparison",
  "tables_or_metrics": ["metric_churn"],
  "category": "period-over-period-comparison",
  "difficulty": "medium",
  "source": "support-ticket-4821"
}

The expected_sql field is written and verified by someone who actually knows the schema and business logic — not generated by the model you’re trying to evaluate, which would just be grading the model against itself.

Keep the dataset versioned and maintained

A golden dataset isn’t a one-time artifact. New failure modes discovered in production should be added as new eval cases; metric definitions that change need their expected SQL updated to match. Version the dataset itself (alongside the semantic layer version it was written against — see the semantic layer governance card) so you know exactly which definitions a given eval run assumed, and so a metric redefinition doesn’t silently make half your golden answers “wrong” without anyone noticing why.

Sizing it realistically

There’s no magic number, but a useful eval suite usually needs enough cases to cover your major question categories (aggregate lookups, comparisons, filtered breakdowns, ambiguous/clarification-needed questions) with several examples each — a suite of five questions will tell you almost nothing about whether a prompt change is safe to ship; thirty to a few hundred well-chosen, real-world-sourced cases is a more realistic starting point for anything customer-facing.

Why “it passed the eval suite at launch” isn’t enough

An eval suite is a snapshot: a fixed set of questions, run against a fixed version of the prompt, model, and semantic layer, at one point in time. None of the three stay fixed in production — the underlying data changes shape, the population of users and question types drifts, and small downstream changes (a metric definition update, a new MCP tool, a model version bump) can each independently shift behavior in ways the original eval never anticipated. A launch gate answers “was this safe to ship then”; ongoing monitoring answers “is this still working now.”

The core signals worth tracking

Rate of gated or blocked queries. If your pre-execution gate (see the companion card) or read-only/allow-list guardrails are rejecting an increasing share of queries, that’s worth investigating in either direction — a spike might mean users are asking for things the system wasn’t scoped for and needs new tools or metrics, or it might mean a guardrail is now too aggressive and blocking legitimate requests it used to allow.

User override or correction rate. When users explicitly disagree with, correct, or manually redo an agent’s answer, that’s a direct signal of a wrong or unsatisfying response — arguably a better real-world correctness signal than anything your offline eval suite can produce, because it’s coming from people who actually know whether the answer was useful to them.

Rate of answers flagged wrong after the fact. Whether through periodic human spot-checks, an internal feedback button, or downstream discovery (“wait, this dashboard number doesn’t match what the agent told me”), track how often answers turn out to be incorrect once someone checks, and watch that rate over time rather than only reacting to individual incidents as they surface.

dashboard_metrics = {
    "gated_query_rate": gated_queries / total_queries,
    "user_override_rate": overridden_answers / total_answers,
    "flagged_wrong_rate": flagged_wrong / spot_checked_sample,
    "trend_vs_last_30_days": ...,
}

Closing the loop back into evals

Every one of these signals is a candidate input to your offline eval suite, not just a dashboard number to watch passively — a newly discovered failure mode from a user correction should become a new golden dataset case (see the companion evals cards), so that the next prompt or model change is automatically checked against it before shipping, rather than relying on production monitoring to catch the same failure mode again after it regresses.

Alerting, not just dashboards

A metric nobody looks at until a stakeholder complains isn’t really monitoring. Set concrete alerting thresholds on these rates — a meaningful jump in gated queries or override rate over a short window should page someone, the same way an application error-rate spike would, rather than sitting in a weekly report that gets skimmed after the damage is already done.

Thresholds as a sliding scale, not a binary

Treating every query as either “fine, run it” or “blocked, forbidden” throws away information. A more useful model is a tiered set of thresholds:

Estimated cost / rows	Action
Within normal bounds	Auto-execute
Above normal, below a hard ceiling	Route to human approval before executing
Above the hard ceiling	Block outright, regardless of approval — likely indicates a real bug (accidental cartesian join, missing filter) rather than a legitimately large question

This means a genuinely large but real business question — “give me all transactions across the last five years for an audit” — can still get answered, just with a human confirming it’s intentional, rather than the system either silently running an enormous query or flatly refusing something a stakeholder actually needs.

Read-only Q&A versus side-effecting actions: different bars entirely

Cost and row count are the right gating dimension for read-only queries, because the main risk there is compute cost and performance, not irreversible harm. That’s a completely different risk category from an AI-generated action that does something — sends an email, writes to a table, triggers a downstream workflow. For those:

• Human-in-the-loop approval should be close to the default, not the exception, regardless of estimated cost, because the risk isn’t “this is expensive,” it’s “this cannot be undone once it happens.”
• The threshold isn’t cost-based at all — a cheap, one-row UPDATE and an expensive read-only SELECT are not comparable; the UPDATE needs approval because of what it does, not how much compute it uses.

if action.has_side_effects:
    require_human_approval()  # essentially always, regardless of cost
elif action.estimated_cost > HARD_CEILING:
    block()
elif action.estimated_cost > APPROVAL_THRESHOLD:
    require_human_approval()
else:
    auto_execute()

Why this distinction matters in practice

Teams that only think about cost thresholds often end up either too permissive on side-effecting actions (because a cheap write looks “safe” by cost metrics alone) or too restrictive on read-only Q&A (because they apply write-level caution to every query, making the tool annoying to use for its primary, lower-risk purpose). Separating “how expensive is this” from “can this be undone” gives you two independent, appropriately-calibrated gates instead of one blunt one.

Semantic model: describing the table once

The semantic model tells the layer what a table is — its grain (one row per what?), which columns are dimensions you can slice by, and which are measures you can aggregate. This is illustrative YAML, not a specific tool’s exact current syntax:

semantic_models:
  - name: orders
    model: ref('fct_orders')
    defaults:
      agg_time_dimension: order_date
    entities:
      - name: order_id
        type: primary
      - name: customer_id
        type: foreign
    dimensions:
      - name: order_date
        type: time
        type_params:
          time_granularity: day
      - name: region
        type: categorical
      - name: plan_tier
        type: categorical
    measures:
      - name: order_amount
        agg: sum
        expr: amount_usd
      - name: order_count
        agg: count
        expr: order_id

Metric: a calculation on top of measures

A metric composes one or more measures into a named, reusable number:

metrics:
  - name: revenue
    type: simple
    type_params:
      measure: order_amount

  - name: average_order_value
    type: ratio
    type_params:
      numerator: order_amount
      denominator: order_count

What this buys you once an LLM is querying

The Claude-facing MCP tool doesn’t take a metric parameter and then go build a SUM(amount_usd) FROM fct_orders GROUP BY region query by hand — it passes metric: "revenue", dimension: "region" to the semantic layer’s query interface, and the semantic layer resolves grain, joins, and aggregation the same way it would for a dashboard filter. The model’s job shrinks to “pick the right metric and dimension,” which is a much smaller, much safer surface than “write correct SQL against tables you’ve never seen data from.”

Why this matters for consistency

If someone later changes how revenue is calculated — say, to exclude refunds — that change happens in one place, and every consumer (dashboard, scheduled report, Claude Q&A tool) picks it up automatically and identically. Compare that to a world where the LLM regenerates SQL from scratch each time: a refund-exclusion fix would need to somehow propagate into every future prompt, which it won’t, reliably.

Why “just let it write SQL” is the wrong default

A single execute_sql(query: string) tool looks maximally flexible, but it means:

• No guardrail is possible on intent. You can’t cheaply distinguish “a reasonable aggregate query” from “an accidental cross join that scans the whole fact table” — both are just strings.
• The model reinvents your business logic every time. Which join keys to use, how to define “active,” whether to exclude test accounts — all of that lives in someone’s head or in a semantic layer, and raw table access bypasses it entirely.
• It’s a bigger security surface than it needs to be. Even with a read-only database role (which you should also have — see the companion guardrails card), an unconstrained query can still be enormously expensive, touch tables it shouldn’t, or leak columns nobody meant to expose through natural-language questions.

What to build instead

A fixed set of parameterized tools, each mapped to a specific, reviewed query pattern — ideally calling into your semantic layer’s defined metrics rather than raw tables, so the join logic and metric definitions are guaranteed consistent no matter which tool fires.

{
  "name": "get_metric_by_dimension",
  "description": "Query a single pre-defined metric from the semantic layer, grouped by one dimension, over a date range. Only metrics registered in the semantic layer are valid.",
  "inputSchema": {
    "type": "object",
    "properties": {
      "metric": { "type": "string", "enum": ["revenue", "active_users", "signups", "churn_rate"] },
      "dimension": { "type": "string", "enum": ["region", "plan_tier", "signup_channel"] },
      "start_date": { "type": "string", "format": "date" },
      "end_date": { "type": "string", "format": "date" }
    },
    "required": ["metric", "start_date", "end_date"]
  }
}

Under the hood, this tool’s implementation calls your semantic layer (e.g., a MetricFlow query) rather than string-interpolating SQL — the model never sees or writes SQL at all, it just fills in parameters against an enum you control.

Where the flexibility goes

This approach trades unbounded flexibility for safety and consistency, which is usually the right trade for production analytics — but it does mean genuinely novel questions (a metric nobody’s defined yet, an ad hoc one-off join) won’t be answerable through the tool. Handle that at the product layer: have the model say “I don’t have a tool for that” rather than falling back to raw SQL, and route those requests to a human who can decide whether it’s worth adding a new metric or tool.

if requested_metric not in ALLOWED_METRICS:
    return "This metric isn't available through the assistant yet — ask an analyst, or request it be added to the semantic layer."

That failure mode — a clean “I can’t do that yet” — is far safer than a model that can technically do anything but has no way to know what it shouldn’t.

Why exact-match fails almost immediately

LLMs don’t generate SQL deterministically or in one canonical style. Two syntactically different queries can be semantically identical:

-- Golden SQL
SELECT r.region, SUM(o.amount_usd) AS revenue
FROM orders o
JOIN regions r ON o.region_id = r.id
GROUP BY r.region;

-- Generated SQL — different alias, different join order, same result
SELECT regions.region, SUM(orders.amount_usd) AS revenue
FROM regions
JOIN orders ON regions.id = orders.region_id
GROUP BY regions.region;

An exact-string-match grader marks the second query wrong. It isn’t — it returns identical data. If you grade on string equality, you will see a “high failure rate” that has nothing to do with the model’s actual correctness and everything to do with the grading method being too strict, and you’ll waste time debugging a model that isn’t broken.

Execution accuracy: run it, compare the output

Execute both the golden query and the generated query against the same (ideally sandboxed, read-only) database snapshot, then compare the resulting rows rather than the SQL text:

{
  "id": "eval-118",
  "question": "What was revenue by region last quarter?",
  "expected_sql": "SELECT region, SUM(amount_usd) FROM orders JOIN regions ... GROUP BY region",
  "generated_sql": "SELECT regions.region, SUM(orders.amount_usd) FROM regions JOIN orders ... GROUP BY regions.region",
  "grading_method": "execution_accuracy",
  "expected_result": [["EMEA", 401200], ["NA", 812400], ["APAC", 233900]],
  "actual_result": [["EMEA", 401200], ["NA", 812400], ["APAC", 233900]],
  "result": "pass"
}

This is why execution accuracy is the standard metric for text-to-SQL evaluation in practice: it grades what actually matters to the user (did they get the right answer), not how the model happened to phrase the SQL to get there.

What execution accuracy costs you

It requires a safe environment to actually run candidate SQL — typically a read-only replica or sandboxed warehouse snapshot, not production, since you’re now executing arbitrary model-generated queries as part of your test pipeline (the same guardrail concerns from the MCP and guardrails sections apply to your eval infrastructure too). You also need to decide how strict result comparison should be — row ordering usually shouldn’t matter unless the question specifically asked for an order; floating-point aggregates may need a tolerance rather than exact equality, since minor computation differences (e.g., rounding) shouldn’t fail an otherwise-correct query.

When exact-match is still useful

Exact-match isn’t useless everywhere — it’s reasonable for checking things like “did the query reference the correct table/metric name” as a supplementary signal, or for grading structured outputs where the format genuinely needs to be exact (like calling a specific tool with specific parameter names). The point isn’t that string comparison is always wrong, it’s that it’s the wrong grading method for “is this SQL query correct,” where correctness is about the data returned, not the text.

Why this failure mode is dangerous specifically because it’s confident

A wrong number with a visible caveat (“this may be incomplete, September’s tracking had a known gap”) is a much smaller problem than the same wrong number stated flatly. Stakeholders generally know to sanity-check numbers that come with hedges; they generally don’t know to sanity-check numbers that sound authoritative. The model isn’t lying — it genuinely computed 4.2% correctly from the data it queried — but it has no idea that data is incomplete unless something tells it so, and by default it has no reason to hedge a query that ran successfully and returned a clean number.

The guardrail: surface data quality into the answer path

The fix isn’t “make the model more cautious in general” (that just produces excessive hedging on answers that are actually fine — see below). It’s giving the model access to the specific data-quality signals that matter for the query it just ran, so it can caveat accurately rather than guess whether to caveat at all.

function answer_with_data_quality_context(query, metric):
    result = run_query(query)
    quality_flags = check_data_quality(
        table=metric.source_table,
        date_range=query.date_range,
    )
    # e.g. quality_flags = {"completeness": 0.71, "known_incidents": ["tracking-outage-2026-09-12..09-19"]}

    if quality_flags.has_known_incident or quality_flags.completeness < COMPLETENESS_THRESHOLD:
        return generate_answer(result, required_caveat=quality_flags)
    else:
        return generate_answer(result)

Concretely, this means maintaining some form of machine-readable data quality metadata — known incidents, freshness timestamps, completeness thresholds per table — and actually plumbing it into the context the model sees when it’s about to state a number, not just hoping the model somehow knows to be suspicious.

Evaluating for calibration, not just correctness

A standard eval checks “is the number right.” A calibration-aware eval also checks “did the model hedge when it should have, and did it not over-hedge when the data was actually fine”:

{
  "id": "eval-calibration-07",
  "question": "What was Q3 churn?",
  "data_condition": "known_tracking_gap_september",
  "expected_behavior": "states the number AND explicitly flags the September data gap",
  "graded_dimensions": ["correctness", "appropriate_caveat_present", "not_overly_hedged_elsewhere"]
}

That last dimension matters as much as the caveat itself — a model that hedges every single answer regardless of whether the underlying data is actually fine is nearly as unhelpful as one that never hedges, just in the opposite direction. The goal is accurate calibration, not maximum caution.

The honest limitation

This is a genuinely hard, not-fully-solved problem — you can’t guarantee a model will always know when to be suspicious of data it has no independent way to verify. Treat it as defense in depth: data quality monitoring that actually flags known incidents, guardrail logic that surfaces those flags into the answer path, prompting that instructs the model to caveat when flags are present, and evals that specifically test calibration — none of these alone is sufficient, but together they meaningfully reduce how often a confidently wrong answer reaches someone who’ll act on it.

Why you need LLM-as-judge at all

Execution accuracy works great for text-to-SQL because “correct” has a checkable, mechanical definition — run the query, compare the data. Open-ended output like “summarize what’s driving this month’s revenue change” has no single correct string to compare against, and manually grading hundreds of free-text summaries doesn’t scale. An LLM judge — a separate model call that scores a candidate output against criteria — is the practical way to grade this kind of output at eval-suite scale.

The core risk: the judge isn’t a neutral referee

LLM judges have documented systematic biases: they can favor longer responses regardless of quality, favor a confident tone over an appropriately hedged one, and (when the judge is the same model family as the one being graded) can favor outputs that “sound like” their own style. None of that is the same as favoring correct or useful — a summary can be long, confident, and wrong, and still score well from an under-specified judge.

They’re also inconsistent: asking the same judge to grade the same output twice, especially with a vague rubric, can produce different scores across runs, which undermines the whole point of having a repeatable eval.

Mitigations, concretely

Use a detailed, multi-criteria rubric instead of a single overall score. “Rate this 1-10” invites the judge to average vague impressions. Breaking the rubric into specific, independently gradeable criteria is much more reliable:

{
  "id": "eval-201",
  "question": "Summarize what drove the Q2 revenue miss.",
  "candidate_answer": "Revenue missed target primarily due to a 12% drop in APAC signups, partially offset by stronger EMEA retention.",
  "rubric_scores": {
    "correctness": 5,
    "completeness": 4,
    "cites_caveats_or_data_gaps": 3,
    "actionable_for_stakeholder": 4
  },
  "judge_model": "claude-opus-5",
  "grading_notes": "Correct on APAC driver; doesn't mention that APAC data has a known tracking gap for the last week of the quarter."
}

Notice cites_caveats_or_data_gaps as its own criterion — this directly connects to the “confidently wrong answer” failure mode covered elsewhere: a rubric that only scores correctness will happily reward an answer that’s right on the numbers it had but silently ignores a known data quality issue.

Use multiple judges or multiple passes and check agreement. Running the same grading with two different models, or the same model at a different point, and flagging disagreements for human review catches cases where a single judge’s idiosyncratic preference would otherwise silently set the “ground truth” score.

Spot-check against human ratings, on a cadence. Periodically have a human (ideally the kind of stakeholder who’d actually receive these summaries) rate a sample of the same outputs the judge graded, and compare. If the judge and human ratings diverge meaningfully, that’s a signal the rubric needs tightening or the judge needs a different prompt — not a signal to trust the judge less in general, since some divergence is unavoidable, but a consistent, large divergence means the judge isn’t measuring what you think it’s measuring.

The honest limitation

LLM-as-judge is a useful and now-standard technique for scaling evaluation of open-ended output, but it’s a proxy for human judgment, not a replacement for it. Treat judge scores as a strong signal to prioritize what to review, not as an unquestionable final verdict — especially for anything high-stakes enough that a wrong “pass” would actually matter.

Two separate questions: transport trust and data authorization

Authentication answers “who or what is calling this server?” Authorization answers “what is that caller allowed to see or do?” MCP servers touching production data need explicit answers to both — “the model asked nicely” is not an authorization model.

Local (stdio) vs remote (HTTP): different trust boundaries

	Local (stdio)	Remote (HTTP)
How it runs	Spawned as a subprocess by the client on the same machine	A long-running service reachable over the network
Identity	Inherits the OS user and credentials of whoever launched the client	Needs its own auth mechanism — the network doesn’t know who’s calling
Typical fit	Single-developer tools, local exploration, a data scientist’s own laptop	Shared team tools, anything multiple people or automated systems connect to
Auth burden	Low — the process boundary and OS-level permissions largely are the security boundary	High — you must authenticate every caller (OAuth flow, API keys, mTLS) and often re-authorize per request

A stdio server that only ever runs on an analyst’s own machine, using their own already-scoped warehouse credentials, can reasonably rely on “if you can run this process, you already have access to the data.” The moment the server becomes remote and shared, that assumption breaks — now it’s a multi-tenant system, and you need to know which caller is asking so you can enforce row/column-level access per person, not just per deployment.

Identity model: per-user vs shared service account

• Per-user identity (the agent acts with the credentials of the specific person it’s helping) is more work to set up but means the warehouse’s own row-level security and existing access controls apply automatically — someone who can’t see EMEA financials in a dashboard also can’t get them through the agent.
• Shared service identity (the agent always queries as one broad service account) is simpler to build but means authorization has to be reimplemented and kept in sync at the MCP server layer — easy to under-scope by accident, and it collapses your audit trail down to “the agent did it,” not “this specific person asked for this.”

For anything beyond a single-user prototype, prefer per-user identity or at least per-team scoped service accounts over one shared credential for every caller.

Minimum bar before production

• A database role scoped to the minimum set of views/columns the use case actually needs — not the broadest role that happens to work.
• Authentication on the transport itself for any remote server — don’t rely on “it’s on the internal network” as your only control.
• Logging of who asked what and what was returned, so a bad answer or a leaked figure can be traced back to a specific request.
• A documented answer to “what happens if the agent’s credentials are compromised or the model is prompt-injected into asking for something it shouldn’t” — least privilege is what limits the blast radius when, not if, that happens.

Why governance gets harder once an LLM is a consumer

When only dashboards read from the semantic layer, a metric change is visible — someone notices a chart moved and asks why. When an LLM agent is also answering ad hoc questions from that same layer, a definition change can silently ripple into every future conversation with zero visible signal that anything changed. The agent doesn’t know to flag “by the way, this number reflects an updated definition of revenue as of last Tuesday” unless you build that awareness in explicitly.

Practical governance mechanics

Version control and review, like code. Metric definitions should live in a repository, changed via pull request, with required review from whoever owns that metric (finance for revenue, growth for activation metrics, etc.). A one-line YAML change to a filter clause on active_users is exactly the kind of thing that looks trivial in a diff and is enormously consequential in effect — it deserves the same scrutiny as a schema migration, not less.

Explicit versioning of the semantic layer. Rather than always resolving to “whatever the current definition is,” support pinning — a saved report, a scheduled export, or a previously-generated analysis can reference a specific semantic layer version so its numbers don’t silently drift when someone updates a metric elsewhere.

GET /metrics/revenue?semantic_layer_version=2026-06-01

Log which version served each answer. If the agent’s answer traces back to “semantic layer version X, metric definition Y, last modified by Z on date W,” you can actually answer “why did this number change” with a fact instead of a guess — which matters enormously the first time an executive notices a number moved and wants to know why before they’ve decided whether to trust the agent again.

Deprecation, not silent renaming. If a metric’s meaning needs to fundamentally change (not just a bug fix, but a real redefinition), prefer introducing a new metric name and deprecating the old one over quietly changing what an existing name means. active_users_v2 next to a deprecated active_users is more work up front but avoids retroactively changing the meaning of every historical answer that already cited the old name.

Communicate changes somewhere the agent’s users can find them. A changelog that only engineers read doesn’t help the business stakeholder who’s confused that this month’s number doesn’t match last month’s dashboard screenshot. If your organization is sophisticated enough, consider having the agent itself surface known-recent metric changes when relevant (“note: the definition of active_users was updated on June 1 to include X”) — but only if you’re confident that context is reliably injected, not assumed.

The underlying principle

An LLM agent will confidently use whatever definition is live, with no innate skepticism about whether that definition just changed underneath it. All the governance discipline that used to be “nice to have” for a semantic layer used only by careful, context-aware analysts becomes load-bearing once a model that has no memory of “we changed this recently” is the one giving executives numbers.

Offline evals: the pre-merge gate

Offline evaluation means running your golden dataset (see the companion card) against a candidate prompt, model, or retrieval change before it ships, in a controlled environment, and comparing results to your last known-good baseline. Treat it exactly like a CI gate for code:

1. Engineer proposes system prompt change.
2. Run full eval suite against candidate prompt.
3. Compare: pass rate, execution accuracy, judge scores (for open-ended output).
4. If regression exceeds threshold on any category → block merge, investigate.
5. If clean (or an accepted, deliberate trade-off) → merge.

This catches the obvious case: a prompt tweak that fixes complaint A but silently breaks question categories B and C that used to work. Nobody manually re-tests “the stuff that already seemed fine” — the eval suite does, automatically, every time.

Why offline evals aren’t the whole story

An eval suite is necessarily a fixed, finite snapshot of question types you thought to include. Production traffic isn’t fixed — new user segments ask new kinds of questions, the underlying data changes shape over time, and the question distribution itself drifts as the product evolves. A prompt that scores perfectly on your 150-case golden dataset can still degrade in the real world if real users start asking things the dataset never anticipated.

Online monitoring: what to track once it’s live

• Live failure/error rate — queries that error out, time out, or get blocked by a guardrail, tracked over time rather than as a point-in-time launch check (see the companion monitoring card for the full list of signals).
• Question-type drift — is the shape of incoming questions changing in ways your eval set doesn’t reflect? A spike in a category you have thin eval coverage for is a signal to expand the dataset, not just a curiosity.
• User corrections or overrides — when users push back on, correct, or manually override an answer, that’s a real-world signal of a wrong or unsatisfying response your offline suite may never have caught.
• Newly discovered failures fed back into the offline suite — every real production failure is a candidate new eval case. This is how the offline suite stays relevant instead of becoming a stale snapshot that no longer represents actual usage.

The relationship between the two

Offline evals are the gate that prevents known regressions from shipping; online monitoring is what tells you about regressions and drift you didn’t know to test for. Neither replaces the other — a system with a great offline eval suite but no production monitoring will still degrade silently over time as the world changes around a fixed test set, and a system with great monitoring but no offline gate will ship regressions that monitoring only catches after users have already been affected.