Don’t Let Your AI Invent Your Calculations

Over and over again we see the same issue with our clients: one metric with multiple definitions. There’s the calculation in a dashboard, the database query someone reuses every month, and the document explaining how it should actually be derived.

When metrics disagree, reconciliation has to occur before the business can use any answer safely. A semantic layer gives applications a shared set of definitions for calculating and interpreting business metrics. Those definitions need a home and a way to stay in step with the data.

For an interactive, natural-language analytics assistant we were building for a customer, we started by using the location the team was already building in: their dbt project.

dbt is a tool data teams use to turn source data into tables ready for analysis. Its configuration files use YAML, a human-readable configuration format, and include a meta property for custom information, such as metric formulas, synonyms, user-facing caveats, and instructions for the assistant. We used that property to hold the metric definitions and business context needed to use them.

Each semantic definition is embedded in the model’s YAML metadata, while the YAML file sits next to the model’s SQL file in the dbt project. This gave us a practical foundation for a custom analytics assistant built on centralized, governed logic. The team could maintain the data and its business meaning together through a workflow it already used.

Give your metrics one agreed definition

The client wanted a text-to-analytics application that executives could use to explore their cloud infrastructure costs in plain English. A question about spend could lead to a follow-up about a particular team or billing period, all in the same conversation.

The client had previously given the assistant access to the available tables and allowed it to write its own SQL. Answering a question could become an exploratory loop: identify the relevant tables, inspect their structure, decide how to calculate the metric, run a query, and revise it when the result or assumptions looked wrong. Follow-up questions could trigger more model calls and database queries as the assistant worked through the same decisions again.

That made routine questions slower and more expensive than they needed to be. It also left the assistant responsible for business decisions that the team could make once, such as which costs to include, which date defined the reporting period, and how to handle unallocated expenses. The generated SQL could run successfully while still using the wrong definition.

With a semantic layer, the assistant can select an established metric such as chargeback_rate, supply the requested team and period, and let the application execute the reviewed calculation. The database still has to answer the metric query, but the assistant no longer needs to rediscover how the metric works. That reduces tool-call round trips, repeated queries, response time, and the amount of generated SQL analysts must inspect.

Take chargeback rate: the share of cloud costs charged back to individual teams. Here’s a simplified definition in the YAML for the model that produces those numbers:

models:
  - name: sem__team_period
    config:
      meta:
        semantic:
          default_time_dimension: period_start
          measures:
            - name: provider_cost_amt
              column: provider_cost
              agg: sum
            - name: charged_back_amt
              column: charged_back
              agg: sum
          metrics:
            - name: chargeback_rate
              label: Chargeback rate
              type: ratio
              numerator: charged_back_amt
              denominator: provider_cost_amt
              format: percent
              synonyms: [recovery, chargeback, "share charged back"]

The measures identify the columns to total. The metric divides the amount charged back by the provider cost. Synonyms connect the agreed calculation to the words someone might use in a question.

Everything under semantic is a format we defined for our application. dbt stores this information; our application reads it and applies the rules. Business users can ask in familiar terms while the data team maintains one calculation behind those terms, ensuring alignment across the entire business.

Model the data around your decisions

The YAML definition of chargeback_rate is concise, but it depends on a model whose records, dimensions, and allocation rules have already been prepared for that calculation. The easier we make it to retrieve an agreed metric, the less the assistant has to figure out each time and the faster it can respond.

If teams regularly ask about chargeback rate, we can organize the relevant costs by team and billing period in advance. Getting there requires business decisions: how should shared costs be allocated, and what happens to a cost that has no team assigned? We work through those questions with the people who use the numbers and build the agreed rules into the data models iteratively.

We also make sure the records fit together correctly. An invoice with several chargeback entries should have its cost counted once. Names and categories should match the language people use when asking questions. This preparation gives the assistant a clearer path to the answer and reduces the choices it must make along the way.

That translation of business logic into version-controlled code is a substantial part of the work we do for clients. We build models around recurring business questions and test the results against agreed answers, including cases with missing information or incomplete periods. Those models also support reporting and analysis beyond the assistant.

Clients don’t need to arrive with the foundation finished. We can build or improve the dbt models alongside the semantic layer and the assistant’s tools, so the data and application are designed for the same business needs.

Here is a simplified example of how those pieces can fit into a dbt project:

cloud-cost-analytics/
├── dbt_project.yml
├── packages.yml
├── models/
│   ├── staging/
│   │   └── cloud_billing/
│   │       ├── _cloud_billing__sources.yml
│   │       ├── stg_cloud_billing__line_items.sql
│   │       └── stg_cloud_billing__chargebacks.sql
│   ├── intermediate/
│   │   └── int_costs_by_team_period.sql
│   └── marts/
│       └── cloud_costs/
│           ├── sem__team_period.sql
│           └── sem__team_period.yml  ← config.meta.semantic
├── tests/
│   └── assert_chargeback_not_over_provider_cost.sql
└── target/
    └── manifest.json  ← generated by dbt

In this example, sem__team_period.sql prepares costs at the team-and-period grain, and sem__team_period.yml holds the custom semantic metadata. The target directory is generated by dbt and is normally excluded from version control.

Build on your dbt investment

Once the definitions are in place, they need to reach the application. dbt includes the meta information in manifest.json, a file describing the project and its models. Our application extracts the definitions into its semantic_catalog, the collection it uses to look up metrics. This is separate from dbt’s own catalog.json file.

The path from the dbt project to the assistant looks like this:

dbt SQL models + YAML metadata
              ↓
   dbt parse or normal dbt job
              ↓
      target/manifest.json
              ↓
application extracts semantic definitions
              ↓
       semantic_catalog
              ↓
        MCP metric tools
              ↓
      analytics assistant

Running dbt parse—or a normal dbt job that parses the project—produces target/manifest.json. Our application reads that generated artifact rather than scanning each YAML file.

manifest.json also identifies the database relation for each model, so the team does not have to maintain a separate list of table locations. Both the semantic definitions and model locations therefore come from the same dbt-generated artifact.

This architecture supports a fuller development lifecycle for metric changes. A data team can update the model SQL and semantic YAML in the same pull request, then use automated checks to parse the dbt project, run its tests, validate the custom semantic definitions, and rebuild the semantic_catalog. After review, the team can deploy the updated models and catalog together, keeping the assistant’s definitions in step with the data.

dbt v2, released in September 2026, brings the former Fusion engine under the dbt name. It does not invalidate the approach described here: v2 continues to produce a manifest compatible with dbt v1. v2 also introduces a queryable Information Schema containing project metadata, including custom meta values, in versioned Parquet files. The Information Schema creates a promising path for future versions of this integration to query only the project metadata they need. The implementation described here uses manifest.json, which remains compatible across dbt v1 and v2.

This pattern does not require the hosted dbt platform. Both dbt v2 and dbt OSS are free to install and run, although they use different licenses.

To keep the definitions in sync, regenerate and deploy the semantic_catalog after every successful production dbt deployment. The pipeline runs dbt parse, extracts and validates the semantic definitions from the resulting manifest, and publishes the catalog after the corresponding data models are available. This avoids relying on state:modified, which does not detect changes to meta.

Trace the data behind the answer

dbt records how its models depend on one another and on source data. Attaching a metric definition to a model gives the application a way to trace which sources feed that number.

That matters when a data load stops arriving. A query against old data can still return a plausible answer, and missing records might look like a drop in business activity.

Suppose the billing source feeding provider_cost_amt has not loaded for the latest period. The application can trace chargeback_rate back to that source through dbt’s dependency graph. Before retrieving the metric, the MCP tool can check its freshness status and label the result as incomplete, offer the latest complete period, or withhold the number until the data is ready.

Your business rules, built into the assistant

Knowing the calculation and its source is only part of a useful answer for an analytics agent. The text-to-analytics assistant also needs the business context that an experienced analyst would bring.

The analytics assistant we built accessed the semantic_catalog through a custom MCP server built for the application. The server exposed tools for looking up metric definitions and retrieving results. Because we controlled both the definitions and the tools, we could decide what business context accompanied each number.

For example, we asked the assistant what share of the previous month’s cloud spend had been charged back to teams. It returned roughly three-fifths. The arithmetic was correct, but the month hadn’t closed. Invoices and chargeback runs were still arriving, so the result wasn’t ready for comparison with a complete period.

We added guidance to identify those periods as partial and offer the latest complete period for comparison. Our definitions also separated information for the user from instructions for the assistant and notes for maintainers. Here’s an excerpt:

semantic:
  # on the model: applies to every metric below
  instructions:
    - >-
      Any number for the current or most recent period is partial.
      Say so, and offer the latest complete period for comparison.
  metrics:
    - name: chargeback_rate
      caveats:
        - "Excludes discounts credited at the organization level."
      # on this metric only
      instructions:
        - "Fetch unallocated_cost too, to help explain a change in the rate."
      internal_notes:
        - "Team attribution uses tags; the billing-account hierarchy is unreliable."

Our application gives the assistant caveats to share with the user and instructions to guide the answer. It keeps maintenance notes out of the information sent to the assistant. The partial-period rule applies across the data model, so the application makes it available for every metric on that model.

Designing the semantic catalog and MCP tools together lets us enforce behavior that prompting alone cannot guarantee. A tool can always fetch unallocated_cost with chargeback_rate, reject incomplete periods, attach required caveats, and keep internal maintenance notes out of the user-facing response.

Let’s build your analytics assistant

We can model your data and build a custom analytics assistant around the questions your teams need answered. That includes preparing the source data in dbt, agreeing on metrics with the people who use them, and developing the semantic layer and MCP server that put those definitions to work.

We can build on the data models and analytics tools you already use. Where the foundation needs work, we can help create it as part of the same engagement.

Bring our team the questions that take too long to answer today. Let’s build an analytics assistant that uses your agreed calculations, provides the context behind the numbers, and fits the way your business works.