Why AI Analysts Pick the Wrong Metric

Semantic sprawl is warehouse technical debt. A normal warehouse accumulates duplicate metrics, old names, unfinished migrations, hidden filters, stale columns, and undocumented flags. Nobody sets out to create ambiguity. It builds up because adding one more object is easy and deleting an old one is risky.

Wrong selection is the metric to watch. An AI analyst can write valid SQL and still answer the wrong business question. If it picks recurring_revenue when the governed definition is mrr, the query runs, the chart looks normal, and the number is wrong.

Static checks belong before agent evals. Golden evals are necessary, but they sample behavior. They do not cover every table, metric, column, alias, and hidden rule in the warehouse. A preflight scan can inspect the definitions first and remove obvious wrong choices before the expensive agent loop starts.

Agents pick from the context you give them#

By AI analyst, I mean an LLM system that chooses warehouse objects, writes SQL, runs it, and returns an answer. That flow has two different failure modes.

Wrong construction is the familiar AI problem: the agent picked the right concept but built the query badly. Wrong join, wrong date window, wrong aggregation, missing filter. That is where better models, SQL validation, refusal rules, and verification guardrails help. I covered that in the guardrails article.

Wrong selection is quieter. The agent chose the wrong metric, table, column, or default before SQL generation began. The SQL may be correct for the thing it selected. The problem is that the thing it selected was not what the business meant.

Agent context is not just prompt text. It is the warehouse interface: metric names, table names, column docs, model structure, semantic definitions, defaults, and deprecations. If that interface shows three revenue metrics, six active-user aliases, and two user tables, the agent has to choose from a noisy context.

That is a warehouse problem. Bigger models can use clear context better. They cannot reliably recover company-specific meaning that is absent or contradictory.

I built a test warehouse around that failure. It is a small subscription product with five dbt-style models, documented columns, and a metrics file shaped like dbt's Semantic Layer example project. Then I gave it the problems I keep seeing in real companies.

This is normal warehouse mess. Not a clean warehouse, not a disaster. It has old names beside new names, migrations that stopped at 80%, metrics that began as experiments and became permanent, and filters that everyone assumes someone else documented.

Selection errors happen in both layers. In the semantic layer, multiple metrics can compete for the same business question: mrr, recurring_revenue, and monthly_recurring_revenue. In the raw warehouse, the agent may still need tables and columns outside semantic-layer coverage: dim_users beside dim_users_v2, or is_internal beside is_test.

Semantic coverage is rarely complete. A semantic layer shrinks the choice surface when the agent is forced through it. Many real agentic setups are not that complete. The semantic layer covers common metrics, and the agent still falls back to raw SQL for questions outside that surface. That raw warehouse is still part of the context.

The quality-control pyramid#

You can find wrong selection with golden evals. Write a set of business questions, run the agent, compare each answer to a trusted SQL key, and inspect the traces when it fails. I do that below. It works.

But evals do not cover a warehouse. They cover a question set. Every extra metric, alias, table version, stale column, and hidden filter gives the agent another choice it could make. Testing all of those choices by running the agent is too slow and too expensive.

Read it from the bottom up:

• Coverage first. Static checks can inspect every model, metric, and column definition.
• Evidence next. Evals prove the agent behaves on the questions the business actually asks.
• Protection last. Runtime guardrails and manual review handle cases that are too important to trust blindly.

Static checks are broad but shallow. Evals are narrow but behavioral. Guardrails are runtime and expensive. You need all three, but you do not want the expensive layers carrying obvious warehouse hygiene.

That is where preflight fits. It complements dbt tests, docs, and contracts. It does not check whether a model built successfully or whether a column is non-null. It checks a different failure mode: confusing definitions and names that can lead an agent to select the wrong valid object.

Static analysis for the warehouse#

Preflight works like a code linter. Instead of running the warehouse, it reads the project files: metric YAML, model YAML, and SQL. Then it uses names, structure, and embeddings to find definitions that are easy to confuse but would produce different answers. There is no agent run and no warehouse connection. The scan happens before runtime, on the definitions you already have.

It decides in three steps:

1. Read every definition into one comparable shape: what is measured, from which table, over which rows, at what grain.
2. Pair up the definitions a person could mix up: names that read alike, or meanings that overlap.
3. Check each pair structurally: would these two return different numbers for the same question? If yes, that pair becomes a finding, with a severity and the file and line where each side is defined.

Pointed at the test warehouse, it printed this:

ONE WAREHOUSE — 11 findings (high 4, medium 3, low 4)

METRICS LAYER

HIGH
  [SCOPE_TRAP]  new_signups ~ new_users
      same measure; 'new_users' is 'new_signups' plus a filter — a bare
      question is silently scoped
  [SCOPE_TRAP]  value_moments ~ real_value_moments ~ total_moments
  [SCOPE_TRAP]  active_users ~ actives ~ dau ~ engaged_users ~ mau ~ monthly_active_users
  [FACT_TWIN]   paying_users ~ subscribers
      the same count over one process at two grains: the current book and
      every subscription ever

MEDIUM
  [FACT_TWIN]       active_habits ~ total_habits

LOW
  [DUPLICATE]   monthly_recurring_revenue ~ mrr        # one meaning, two names
  [DUPLICATE]   total_moments ~ value_moments
  [DUPLICATE]   actives ~ dau ~ engaged_users ~ mau ~ monthly_active_users
  [NAME_COLLISION]  monthly_recurring_revenue ~ recurring_revenue
      names read alike, definitions do not agree — different measure,
      different table

MODELS

MEDIUM
  [VERSIONED_TWIN]  dim_users ~ dim_users_v2
      'dim_users_v2' reads as a versioned or leftover twin — nothing marks
      which one is current
  [VERSIONED_TWIN]  fct_subscriptions ~ fct_subscriptions_2026_03

How to read it:

• SCOPE_TRAP: two metrics share the same measure, but one has a filter the other does not. A plain question can land on the wrong population.
• DUPLICATE: one meaning under two names. This is lower risk because the numbers should agree.
• NAME_COLLISION: two names read alike while the definitions behind them do not agree.
• FACT_TWIN: the same business process counted at two grains, like current subscribers beside every subscription ever.
• VERSIONED_TWIN: a name reads as a version or leftover of another, like dim_users_v2 beside dim_users, with nothing marking which one is current.

Each finding is a prediction: the agent could confuse these definitions. The eleven findings collapse into eight problems in the chart below, because some pairs trip more than one rule. That chart also carries what happened next, so it is worth reading twice: once now, for what a scan can see before anything runs, and once after the benchmarks below, for what each problem cost.

Does the linter help the agent?#

The eval covered two surfaces. I ran gpt-5-mini on thirty-six business questions, three times each: one hundred and eight graded answers per warehouse. Twenty-nine questions targeted sprawl clusters; seven used unambiguous definitions as a baseline. Thirty-one questions stayed inside the metrics layer. Five forced the agent to leave metrics and write raw SQL. A fixed SQL key graded each answer, and the trace showed which metric or table the agent grounded on.

Wrong selection showed up inside governed metrics. On the metric-layer questions, the agent chose the wrong metric in 24% of answers. Counting construction mistakes too, silent errors reached 30%: wrong number, normal-looking answer, no exception.

The scan predicted where failures would land. The six-name active-user cluster was the worst: the agent grounded on a decoy in ten of fifteen answers. Signups, revenue, and the subscriber twin followed. The unambiguous baseline did not show the same pattern.

For a data team, this is the useful read: the checked rows are cleanup work you can catch before an agent ever runs. Duplicate metric names, hidden scope, fact twins, and versioned tables can become CI findings instead of production mistakes.

The right column is the cost of leaving each row alone. Some flagged rows hurt more than their severity label suggests, but the pattern holds: wrong answers concentrate where the warehouse gives the agent competing valid choices. The two bottom rows show the limit. If the meaning lives only in the rows, a linter cannot see it; those problems need modelling, documentation, and data tests.

Clean the context with data modelling#

The fixes were standard analytics engineering work: dimensional modelling, dbt hygiene, clear metric names, documented columns, explicit filters, and deprecated objects removed instead of left beside the current ones.

This is the same repair motion I mapped in The AI-Readiness Repair Matrix. These are old data-modelling practices: Kimball fact and dimension tables, clear grain, governed metrics, explicit filters, documented columns, and deprecated objects removed or marked. Agents just make the cost of ignoring that work easier to measure.

The human work still matters. Deleting recurring_revenue means telling the team whose dashboard uses it. Deciding which revenue definition wins can be political because teams are measured on these numbers. The working motion is deprecate, redirect, then delete. A CI gate needs an owner, usually analytics engineering or the data platform team.

Benchmarks#

Then I reran the same questions against the fixed warehouse.

Wrong selection fell to zero. All ninety-three governed answers used the right metric on the fixed warehouse, for both models.

The choice set changed, not the model. The prompt stayed the same. Once the warehouse had one governed revenue metric, the agent could not choose a decoy revenue metric.

Defaults have to be written down. One construction error remained: when the question said "right now", the small model sometimes used last month's complete snapshot. The fix was simple: no period means the latest snapshot for a stock metric.

I also ran the headline cells on a larger model in the same family, gpt-5.6-terra.

The bigger model made almost no construction errors. It still picked the wrong metric when the warehouse exposed plausible definitions without saying which one was governed. Scale helped SQL construction. It did not recover missing business meaning.

Things I got wrong while proving this#

The ambiguity fooled me too.

The first answer key had mistakes. I applied staff exclusions inconsistently. I left time periods implicit. One sentence I added to help the agent created a new ambiguity. A few late questions also needed repair: one decoy metric could pass the tolerance, one expected answer was zero, and two wordings mixed up customers and contracts.

preflight improved the same way: traces failed in ways that made no sense. It missed mrr beside monthly_recurring_revenue until preflight 0.2.0. It missed _v2 table twins until 0.3.0. It missed subscriber counts at two grains until 0.4.0.

That is the point. Care was not enough. The agent got confused. I got confused. The tool got confused. The useful controls were mechanical: static linting, a fixed answer key, traces, and a CI gate.

Lessons learned#

Give the agent fewer valid choices. A semantic layer helps, but only for the questions it covers. If the agent can fall back to raw SQL, raw tables are still part of its context. Clean both surfaces.

Static checks need written context. A linter can read names, definitions, scope, grain, filters, and obvious leftovers. It cannot read meaning that lives only in rows or in people's heads. That is why it missed the staff flags and stale status. The raw-table questions improved after I modeled and documented those columns, but they did not all become correct. Some questions still asked for a business concept the governed layer did not define.

Govern the concepts people actually ask about. One question asked for active subscription contracts. The semantic layer had customer metrics, not a contract-count metric, so the agent used the closest governed number and answered the wrong question. Cleaning existing definitions cannot fix a missing definition. Coverage gaps need new governed concepts.

Use static checks as the base layer, not the whole system. The scan is not a replacement for golden evals, dbt tests, docs, contracts, or runtime guardrails. It is the cheap layer that catches written ambiguity before the agent sees it. For preflight, or any ambiguity linter, fix HIGH findings first and let LOW findings inform. If a team decides a finding is acceptable, overrule it in the pull request and move on.

Bigger models do not replace governed context. The larger model nearly erased construction mistakes, then still picked the wrong revenue metric when the warehouse exposed plausible choices without saying which one was governed. Scale helps the agent use context. It does not create company-specific meaning that is absent or contradictory.

Old modelling practices are the agent-readiness practices. Clean fact and dimension tables, one governed metric per concept, explicit grain, visible filters, documented columns, deprecation windows, and backups kept out of analytics. None of this is new. Agents just make the cost of ignoring it easier to measure.

OpenAI's internal data-agent write-up points at the same problem from the other end. At their scale, they reconstruct meaning industrially from pipeline code, annotations, ownership, usage, and lineage so the agent can select well at answer time. Most teams do not need that machinery. At a few hundred models, prevention is still available: clean names, governed definitions, documented columns, old objects removed, and a preflight scan in CI.

Most warehouses have a real_something: a metric people know to use only because someone once explained it to them. That is already a weak contract for humans. For an agent, it is not a contract at all. In this experiment, pairs like that produced wrong selections in 24% of the answers where a governed metric existed to be chosen. The fix was simple: remove the ambiguity, then block it from coming back.