Our engine never sees your warehouse password
datahashi

BlogAgents & MCP

Grounding a support copilot in certified customer health metrics

How a support copilot answers an account-health question from certified metrics, with sample schema, model and queries that separate ticket volume from open critical escalations.

The datahashi team9 min read

The problem

Before an executive renewal call, a Customer Success manager asks their CRM copilot: “Is Acme Corp healthy?”

The copilot replies instantly with high confidence: “Acme Corp is healthy. Total support ticket volume is low this week.” But the copilot’s assessment is dangerously wrong. The LLM inspected raw ticket counts without filtering on ticket severity or resolution status. In reality, Acme Corp logged two unresolved critical Sev-1 production outages while daily active user engagement lagged behind expectations.

When customer success managers rely on ungrounded AI summaries, renewal negotiations fall apart unexpectedly. Managers walk into critical customer conversations unaware of serious product dissatisfaction. The account churns weeks later despite the copilot reporting a clean bill of health.

When autonomous agents generate freeform SQL or read raw CRM tables, they confuse low ticket counts with satisfied accounts. They miss filtered operational nuance. You need an architecture where customer-facing copilots ground their answers in certified semantic metrics. Through the Model Context Protocol, the copilot queries certified calculations for open escalations, ticket volumes, and active users.

+--------------------------------------------------------------+
| CS Manager asks: "Is Acme Corp healthy?"                     |
+--------------------------------------------------------------+
                               |
               Ungrounded LLM SQL Generation ✗
                               |
            "Account is healthy! Ticket volume is low!"
                               |
            Reality: 2 Sev-1 outages unresolved ✗
            Critical open escalations ignored ✗
                               |
               Grounding with datahashi MCP ✓
                               |
        "Warning: 2 critical open escalations active."

The data

The database organizes customer accounts, support tickets, and daily product engagement logs. The accounts table stores company accounts and subscription tiers. The support_tickets table captures ticket creation dates, severity ratings, and resolution flags. The product_usage table records daily active user engagement.

+--------------------+              +----------------------+
|      accounts      |              |   support_tickets    |
+--------------------+              +----------------------+
| account_id (PK)    |<---+         | ticket_id (PK)       |
| account_name       |    +---------| account_id (FK)      |
| tier               |    |         | created_at           |
+--------------------+    |         | severity             |
          ^               |         | resolved             |
          |               |         +----------------------+
          |               |
          |               |         +----------------------+
          |               |         |    product_usage     |
          |               |         +----------------------+
          |               +---------| usage_id (PK)        |
          +-------------------------| account_id (FK)      |
                                    | usage_date           |
                                    | active_users         |
                                    +----------------------+

Here is the SQL DDL and sample rows runnable directly in DuckDB:

CREATE TABLE accounts (
    account_id VARCHAR PRIMARY KEY,
    account_name VARCHAR NOT NULL,
    tier VARCHAR NOT NULL
);

CREATE TABLE support_tickets (
    ticket_id VARCHAR PRIMARY KEY,
    account_id VARCHAR NOT NULL REFERENCES accounts(account_id),
    created_at DATE NOT NULL,
    severity VARCHAR NOT NULL,
    resolved BOOLEAN NOT NULL
);

CREATE TABLE product_usage (
    usage_id VARCHAR PRIMARY KEY,
    account_id VARCHAR NOT NULL REFERENCES accounts(account_id),
    usage_date DATE NOT NULL,
    active_users INTEGER NOT NULL
);

INSERT INTO accounts VALUES
    ('acc_acme', 'Acme Corp', 'Enterprise'),
    ('acc_globex', 'Globex Inc', 'Mid-Market'),
    ('acc_stark', 'Stark Industries', 'Enterprise');

INSERT INTO support_tickets VALUES
    ('tkt_01', 'acc_acme', '2026-10-01', 'critical', false),
    ('tkt_02', 'acc_acme', '2026-10-02', 'critical', false),
    ('tkt_03', 'acc_acme', '2026-10-02', 'normal', true),
    ('tkt_04', 'acc_globex', '2026-10-01', 'normal', true),
    ('tkt_05', 'acc_globex', '2026-10-02', 'normal', true),
    ('tkt_06', 'acc_stark', '2026-10-01', 'critical', true),
    ('tkt_07', 'acc_stark', '2026-10-02', 'normal', true);

INSERT INTO product_usage VALUES
    ('usg_01', 'acc_acme', '2026-10-01', 450),
    ('usg_02', 'acc_acme', '2026-10-02', 85),
    ('usg_03', 'acc_globex', '2026-10-01', 120),
    ('usg_04', 'acc_globex', '2026-10-02', 135),
    ('usg_05', 'acc_stark', '2026-10-01', 890),
    ('usg_06', 'acc_stark', '2026-10-02', 910);

The model

The semantic model defines three entities and links both fact tables to the central Account entity.

Crucially, the measure critical_open_tickets embeds business filter logic into the measure definition. It applies filters where severity = "critical" and resolved = false. Copilots and analysts do not need to remember which database flags indicate an active incident.

The metrics expose total_tickets, open_escalations, and active_users:

source: warehouse_main

entities:
  - name: Account
    table: accounts
    primary_key: account_id
    dimensions:
      - name: account_id
        column: account_id
        type: string
      - name: account_name
        column: account_name
        type: string
      - name: tier
        column: tier
        type: string

  - name: SupportTicket
    table: support_tickets
    primary_key: ticket_id
    dimensions:
      - name: ticket_id
        column: ticket_id
        type: string
      - name: account_id
        column: account_id
        type: string
      - name: created_at
        column: created_at
        type: date
        grains: [day, month, quarter, year]
      - name: severity
        column: severity
        type: string
      - name: resolved
        column: resolved
        type: bool
    measures:
      - name: ticket_count
        expr: ticket_id
        agg: count
      - name: critical_open_tickets
        expr: ticket_id
        agg: count
        filters:
          - dimension: severity
            op: eq
            value: "critical"
          - dimension: resolved
            op: eq
            value: false

  - name: ProductUsage
    table: product_usage
    primary_key: usage_id
    dimensions:
      - name: usage_id
        column: usage_id
        type: string
      - name: account_id
        column: account_id
        type: string
      - name: usage_date
        column: usage_date
        type: date
        grains: [day, month, quarter, year]
    measures:
      - name: daily_active_users
        expr: active_users
        agg: sum

relationships:
  - name: ticket_account
    from: SupportTicket
    to: Account
    cardinality: many_to_one
    keys:
      - [account_id, account_id]

  - name: usage_account
    from: ProductUsage
    to: Account
    cardinality: many_to_one
    keys:
      - [account_id, account_id]

metrics:
  - name: total_tickets
    expr: ticket_count
    description: "Total customer support tickets logged"
  - name: open_escalations
    expr: critical_open_tickets
    description: "Unresolved critical severity tickets"
  - name: active_users
    expr: daily_active_users
    description: "Total active product users in period"

Notice how critical_open_tickets encases domain knowledge into the model. An agent querying open_escalations always receives the certified count of unresolved critical bugs.

AI agents interacting with databases often struggle with nuanced filter conditions. When an agent writes raw SQL, it might guess WHERE status = 'open' instead of checking resolved = false. It might forget to filter by severity = 'critical'. Defining critical_open_tickets inside the model embeds business rules directly into the catalog. The copilot merely selects open_escalations without constructing fragile boolean expressions.

The multi-fact design allows the copilot to request support tickets and product telemetry side by side. SupportTicket and ProductUsage both join to Account. The query compiler resolves this by building separate aggregate subqueries for tickets and usage before joining on account keys. This approach prevents fan-out multiplication across unrelated fact tables.

See the model specification for full syntax details on measure filters.

The queries

The copilot connects to datahashi using the Model Context Protocol (MCP). The MCP server exposes standard tools including list_catalog, describe_metric, and run_semantic_query. The agent inspects metric definitions and submits structured query parameters.

When the agent invokes run_semantic_query, the MCP server passes the query payload to the semantic engine. The engine verifies the requested metrics, compiles dialect-specific SQL, and executes it via the Query Runner. The copilot receives clean tabular rows directly in its context window. MCP does not return that SQL. The SQL blocks below are the compiled statement. REST returns it only when the engine is started with ENGINE_ALLOW_SQL=true and the request sets include_sql.

The copilot connects via Model Context Protocol (MCP) tools. The tools include list_catalog, describe_metric, and run_semantic_query. The copilot inspects metric definitions and sends structured requests adhering to the query specification.

Query 1: Hero account health check via MCP run_semantic_query

When the CS manager asks about Acme Corp, the CRM copilot calls run_semantic_query filtering on account_name = "Acme Corp":

{
  "name": "run_semantic_query",
  "arguments": {
    "metrics": ["total_tickets", "open_escalations", "active_users"],
    "dimensions": [
      {
        "name": "account_name",
        "entity": "Account"
      }
    ],
    "filters": [
      {
        "dimension": "account_name",
        "entity": "Account",
        "op": "eq",
        "value": "Acme Corp"
      }
    ],
    "order": [
      { "ref": "account_name", "desc": false }
    ]
  }
}

The gateway returns the filtered metrics:

account_name total_tickets open_escalations active_users
Acme Corp 3 2 535

The engine compiles the query into parameterized SQL:

SELECT
  COALESCE(q0."account_name", q1."account_name") AS "account_name",
  CAST(q0."ticket_count" AS DOUBLE) AS "total_tickets",
  CAST(q0."critical_open_tickets" AS DOUBLE) AS "open_escalations",
  CAST(q1."daily_active_users" AS DOUBLE) AS "active_users"
FROM (
  SELECT
    "Account"."account_name" AS "account_name",
    COUNT("SupportTicket"."ticket_id") AS "ticket_count",
    COUNT(CASE WHEN "SupportTicket"."severity" = ? AND "SupportTicket"."resolved" = ? THEN "SupportTicket"."ticket_id" END) AS "critical_open_tickets"
  FROM "support_tickets" AS "SupportTicket"
  JOIN "accounts" AS "Account"
    ON "SupportTicket"."account_id" = "Account"."account_id"
  WHERE "Account"."account_name" = ?
  GROUP BY "Account"."account_name"
) AS q0
FULL OUTER JOIN (
  SELECT
    "Account"."account_name" AS "account_name",
    CAST(SUM("ProductUsage"."active_users") AS DOUBLE) AS "daily_active_users"
  FROM "product_usage" AS "ProductUsage"
  JOIN "accounts" AS "Account"
    ON "ProductUsage"."account_id" = "Account"."account_id"
  WHERE "Account"."account_name" = ?
  GROUP BY "Account"."account_name"
) AS q1
  ON q0."account_name" = q1."account_name"
ORDER BY "account_name"

The copilot now receives accurate grounding data: Acme Corp logged three total tickets, but two are open critical escalations. The copilot warns the manager immediately before the renewal discussion.

Query 2: Ranking accounts by open escalations

The copilot can also rank all accounts by escalation risk. A limit with no order is sorted for you: the first time dimension ascending, otherwise the first selected column descending. This query sends an explicit order on open_escalations because that is the ranking it wants:

{
  "metrics": ["total_tickets", "open_escalations", "active_users"],
  "dimensions": [
    {
      "name": "account_name",
      "entity": "Account"
    },
    {
      "name": "tier",
      "entity": "Account"
    }
  ],
  "order": [
    { "ref": "open_escalations", "desc": true }
  ],
  "limit": 3
}

The gateway returns the top accounts sorted by escalation count:

account_name tier total_tickets open_escalations active_users
Acme Corp Enterprise 3 2 535
Globex Inc Mid-Market 2 0 255
Stark Industries Enterprise 2 0 1800

The compiled query evaluates each fact table independently and applies sorting and limits:

SELECT
  COALESCE(q0."account_name", q1."account_name") AS "account_name",
  COALESCE(q0."tier", q1."tier") AS "tier",
  CAST(q0."ticket_count" AS DOUBLE) AS "total_tickets",
  CAST(q0."critical_open_tickets" AS DOUBLE) AS "open_escalations",
  CAST(q1."daily_active_users" AS DOUBLE) AS "active_users"
FROM (
  SELECT
    "Account"."account_name" AS "account_name",
    "Account"."tier" AS "tier",
    COUNT("SupportTicket"."ticket_id") AS "ticket_count",
    COUNT(CASE WHEN "SupportTicket"."severity" = ? AND "SupportTicket"."resolved" = ? THEN "SupportTicket"."ticket_id" END) AS "critical_open_tickets"
  FROM "support_tickets" AS "SupportTicket"
  JOIN "accounts" AS "Account"
    ON "SupportTicket"."account_id" = "Account"."account_id"
  GROUP BY "Account"."account_name", "Account"."tier"
) AS q0
FULL OUTER JOIN (
  SELECT
    "Account"."account_name" AS "account_name",
    "Account"."tier" AS "tier",
    CAST(SUM("ProductUsage"."active_users") AS DOUBLE) AS "daily_active_users"
  FROM "product_usage" AS "ProductUsage"
  JOIN "accounts" AS "Account"
    ON "ProductUsage"."account_id" = "Account"."account_id"
  GROUP BY "Account"."account_name", "Account"."tier"
) AS q1
  ON q0."account_name" = q1."account_name"
 AND q0."tier" = q1."tier"
ORDER BY "open_escalations" DESC
LIMIT 3

Acme Corp surfaces at the very top of the risk ranking due to active escalations.

Query 3: Refusal for hallucinated metric

If an agent invents an unsupported calculation like a probabilistic churn score, the semantic layer halts execution:

{
  "metrics": ["churn_risk_probability_score"]
}

The gateway returns HTTP 404:

{
  "error": "model: resolve: metric \"churn_risk_probability_score\" is not exposed by the named view",
  "code": "not_found",
  "reason": "not_in_view",
  "ref": "churn_risk_probability_score"
}

The copilot recognizes that the metric does not exist in the catalog and asks the user for clarification rather than making up numbers.

What to notice

  • Measure-level filter encapsulation. Business definitions like severity = 'critical' AND resolved = false live inside the model. AI agents do not have to generate filter predicates on raw columns.
  • Reliable copilot grounding. The agent accesses verified numbers through MCP tools. Answers reflect real database metrics instead of training data assumptions.
  • Fan-out safe multi-fact joins. Queries combine data from support tickets and product usage logs without inflating metrics through cartesian join multiplication.
  • Agent-safe defaults. Rejections prevent agents from querying unverified fields or running unindexed raw table queries.
  • Deterministic ordering. Explicit sorting guarantees reproducible top-N customer rankings across every agent interaction.

For earlier examples of connecting chat tools and bots, check out the guide on building a Slack bot for weekly results and the overview of self-hosting regulated agents.

Limits

  • Default order. A query with limit and no order is sorted for you: the first time dimension ascending, otherwise the first selected column descending. Send order when you need a different sort. offset still requires an explicit order and a positive limit.
  • Tool discovery grounding. The LLM must call list_catalog or describe_metric to discover available metrics. If an agent does not inspect the catalog first, it may attempt to query unmodeled metric names.
  • Predictive churn modeling. The semantic layer evaluates historical transactional facts and aggregations. Complex machine learning inference, like calculating predictive churn likelihood scores, belongs in predictive model services.
  • Transformation boundary. The semantic layer does not deduplicate tickets or merge duplicate customer account records. Data cleansing remains an upstream data transformation task.

Product notes

An occasional email when we ship something or write something worth reading.