How do we reconcile conflicting metrics before we feed them into an LLM?

How do we reconcile conflicting metrics before we feed them into an LLM?

We reconcile conflicting metrics by implementing a centralized semantic layer that acts as a single source of truth between the data warehouse and the LLM. Instead of passing raw SQL tables or CSV exports directly to an AI agent, we define business logic in a code-based metric layer (such as dbt Semantic Layer or Cube) and expose these governed definitions via an API.

In our work with mid-market SaaS companies, we have seen that the most common cause of AI hallucination is not the model itself, but the underlying data inconsistency. When an LLM is asked to calculate Gross Margin and has access to both a CRM table and a finance table with slightly different definitions, it will likely provide a confidently wrong answer. According to Gartner, inaccurate data causes 40 percent of Generative AI pilot failures because models cannot adjudicate between two different SQL definitions of the same metric. To fix this, we must move the "brain" of the metric definition out of the prompt and into the data stack.

Why do AI agents hallucinate when using raw warehouse data?

The core problem is that business logic is often fragmented across the Modern Data Stack (MDS). In many organizations, the definition of Churn or Monthly Recurring Revenue (MRR) exists in a BI tool, a series of scheduled SQL scripts, and perhaps a few local spreadsheets. When we build AI agents, we often give them tools to query the warehouse directly. However, without a mediator, the agent is forced to play the role of an analytics engineer.

For example, a Series B SaaS client we worked with had three different ways to calculate Customer Acquisition Cost (CAC). The marketing team included agency fees, while the finance team only looked at direct ad spend. When their internal AI assistant was asked for the CAC of the last quarter, it pulled from both the marketing_spend table and the finance_ledger table, resulting in a number that matched neither department's dashboard. This inconsistency erodes trust in the AI system and leads to expensive strategic errors.

By fixing inconsistent data for AI agents at the transformation layer, we ensure that the model never has to decide which definition is "correct." It simply requests the cac metric from the API, and the semantic layer handles the joins and filters according to the pre-approved business logic.

Implementing a metric layer for LLM context

To provide a robust metric layer for LLM context, we follow a three-step process that validates definitions before they ever reach the model. This framework ensures that every metric served to the LLM is governed and version-controlled.

Step 1: Consolidate logic in dbt

We start by ensuring that all base metrics are defined in dbt (Data Build Tool). This allows us to use version control and automated testing to ensure data quality. A typical model for an orders table might look like this:

-- models/marts/fct_orders.sql
with orders as (
    select * from {{ ref('stg_orders') }}
),
order_items as (
    select * from {{ ref('stg_order_items') }}
)
select
    o.order_id,
    o.customer_id,
    o.order_date,
    sum(oi.revenue) as total_revenue,
    sum(oi.cost) as total_cost
from orders o
join order_items oi on o.order_id = oi.order_id
group by 1, 2, 3

Step 2: Define metrics in the semantic layer

Once the models are stable, we define the metrics in a YAML file. This is where we reconcile conflicts. By defining "Revenue" once, we prevent the LLM from trying to sum the wrong columns or forget to exclude cancelled orders.

# models/metrics/metrics.yml
metrics:
  - name: revenue
    label: Total Revenue
    model: ref('fct_orders')
    description: "Total revenue from completed orders, excluding refunds."
    type: sum
    type_params:
      measure: total_revenue
    filters:
      - field: status
        operator: '='
        value: "'completed'"

Step 3: Expose metrics via API for the LLM

Finally, we expose these metrics to the LLM via a GraphQL or REST API. When the user asks "What was our revenue last month?", the LLM does not write SQL. Instead, it generates a structured request to the semantic layer. This approach is part of what we evaluate in our AI Stack Audit, where we help teams identify where their data logic is too fragile for AI consumption.

Standardizing business logic for RAG pipelines

When building Retrieval-Augmented Generation (RAG) pipelines, the focus is often on vectorizing text documents. However, for most business use cases, "data RAG" is just as important. Standardizing business logic for RAG pipelines involves treating your metrics as part of the context window.

If an AI agent is performing a root-cause analysis on why churn increased, it needs more than just a list of customer tickets. It needs the exact churn rate for that specific cohort. If the RAG pipeline pulls a document that defines churn one way and a SQL table that defines it another way, the agent will produce a logically inconsistent summary.

We solve this by creating a "Metric Store" interface for the RAG system. Before the LLM generates an answer, the system retrieves the relevant metric definitions (metadata) and the current metric values from the semantic layer. This provides the LLM with a "ground truth" context that overrides any conflicting information it might have found in unstructured documents.

Comparing the cost of manual prompting versus a semantic layer

Many teams attempt to reconcile metrics by adding massive amounts of "context" to their LLM prompts. This involves writing long system instructions that explain every edge case: "If the user asks for revenue, use the orders table but only where status is 'shipped' and the region is 'US'..."

This approach leads to significant technical debt and increased operational costs. The Total Cost of Ownership (TCO) of manual prompting is deceptive. While it is cheap to start, it becomes expensive to maintain as the business evolves.

For companies looking to avoid these pitfalls, we cover the architecture of these systems in our Learn AI Bootcamp, specifically focusing on how to build the semantic bridge between data warehouses and AI agents.

Fixing inconsistent data for AI agents in production

In production environments, fixing inconsistent data for AI agents requires more than just better prompts; it requires a structural change in how data is delivered. We often see teams struggle with "Metric Drift," where the logic in the dashboard changes but the logic in the AI agent remains static.

To prevent this, we implement "Logic-as-Code." Every time a data engineer updates a metric in dbt, the semantic layer is automatically updated, and the AI agent immediately inherits the new definition. This creates a closed-loop system where the AI is always in sync with the official company reporting.

Consider the risk of Lifetime Value (LTV) or CAC hallucinations. If the LLM pulls from both CRM and warehouse tables without a mediator, it might count a "Lead" as a "Customer" or fail to deduplicate accounts that exist in both systems. Our team specializes in building this mediation layer. For startups and mid-market teams, we offer an Automation Sprint ($5,000-$8,000) that helps build this semantic bridge in under two weeks, ensuring your AI agents are grounded in reality rather than hallucinated logic.

Frequently Asked Questions About Reconciling Metrics

What is a semantic layer and how does it help LLMs?

A semantic layer is a middleware that sits between your data source and your data consumer. It maps complex data structures to business concepts. For LLMs, it provides a simplified, governed interface. Instead of the LLM needing to know that "Revenue" requires joining three tables and filtering for specific status codes, it simply asks the semantic layer for "Revenue," which returns the correct value based on pre-defined logic.

Why is hardcoding logic in the prompt considered technical debt?

Hardcoding logic in a prompt is fragile. If your data schema changes, or if your business redefines a KPI (e.g., changing from "Gross Revenue" to "Net Revenue"), you have to find and update every single prompt across your entire AI application. This is prone to human error and leads to inconsistent answers across different parts of your product. A semantic layer allows you to change the definition in one place and have it reflect everywhere.

Can we just use LLMs to write SQL queries instead?

You can, but it is risky for metrics. LLMs are excellent at writing SQL for simple questions, but they struggle with complex, multi-join business logic and specific company nuances. Without a semantic layer, the LLM is effectively guessing your business rules. By using a semantic layer, you allow the LLM to handle the natural language intent while the semantic layer handles the deterministic calculation.

How do we handle metrics that exist in different tools like HubSpot and BigQuery?

The best practice is to ingest all data into a central warehouse (like BigQuery or Snowflake) first. Once the data is in one place, you can use dbt to model and reconcile the differences between your CRM (HubSpot) and your production database. The semantic layer then sits on top of this reconciled warehouse data, providing a single source of truth for the LLM regardless of where the data originated.

What is the typical timeline for building a semantic layer for AI?

For most mid-market companies, a foundational semantic layer for core metrics (Revenue, Churn, CAC, LTV) can be built in two to four weeks. This involves auditing existing definitions, codifying them in a tool like dbt or Cube, and connecting them to the AI agent's API. Our Automation Sprint is specifically designed to handle this transition in under two weeks for a fixed price of $5,000-$8,000.

Ready to reconcile your data logic?

If you are tired of your AI agents giving inconsistent answers to simple business questions, our team can help. We specialize in building the data foundation required for production-grade AI.

Whether you need a full architecture review or a targeted sprint to fix your metrics, we have the framework to get you there. You can book a free consultation to discuss your specific data challenges and see how a semantic layer can stabilize your AI roadmap.