Our engine never sees your warehouse password
datahashi

BlogAgents & MCP

Slack bot weekly results: certified metrics for operations

How operations teams connect Slack bots to certified metrics using Model Context Protocol and query time zones in a thin semantic layer.

The datahashi team7 min read

The problem

Operations managers rely on daily Slack check-ins to monitor order volume and team sales performance. Every Monday morning, an operations lead in New York asks the channel bot: “How did last week go?”

The bot generates ad-hoc SQL using raw UTC timestamps from the central warehouse. Because timestamps are stored in UTC, an order placed at 10:30 PM on Sunday night in New York gets recorded at 2:30 AM on Monday morning in UTC. Standard SQL queries group that late Sunday transaction into the following calendar week. The bot reports 19100 instead of the real 24100 New York business revenue. Regional field managers and finance leads argue over conflicting numbers across channels.

When AI bots write raw SQL, they hallucinate inconsistent date boundary filters. Prompts phrased slightly differently yield conflicting SQL logic each morning. You need an architecture where conversational bots query certified metrics over the Model Context Protocol (MCP) while the semantic layer compiles precise time-zone-aware calendar boundaries.

+--------------------------------------------------------------+
| Ops Manager asks Slack Bot: "How did last week go?"          |
+--------------------------------------------------------------+
                               |
               Unchecked LLM SQL Generation ✗
                               |
       Raw UTC grouping puts late Sunday order into next week!
            Bot reports 19100 instead of 24100 ✗
                               |
               Governed MCP Query with Time Zone ✓
                               |
         Query time zone shifts timestamp to New York
            Bot reports exact 24100 business total ✓

The data

The warehouse tracks operational sales across two physical tables. The teams table identifies internal sales squads and their designated notification channels. The orders table records customer purchases, completion statuses, order amounts in USD, and UTC transaction timestamps.

CREATE TABLE teams (
    team_id VARCHAR PRIMARY KEY,
    team_name VARCHAR NOT NULL,
    channel VARCHAR NOT NULL
);

CREATE TABLE orders (
    order_id VARCHAR PRIMARY KEY,
    team_id VARCHAR NOT NULL REFERENCES teams(team_id),
    ordered_at TIMESTAMP NOT NULL,
    amount DOUBLE NOT NULL,
    status VARCHAR NOT NULL
);

INSERT INTO teams VALUES
    ('team_retention', 'Customer Retention', '#ops-retention'),
    ('team_inbound', 'Inbound Sales', '#ops-inbound'),
    ('team_expansion', 'Account Expansion', '#ops-expansion');

INSERT INTO orders VALUES
    ('ord_101', 'team_inbound', '2026-10-05 14:30:00', 4200.0, 'completed'),
    ('ord_102', 'team_inbound', '2026-10-06 11:15:00', 1800.0, 'completed'),
    ('ord_103', 'team_retention', '2026-10-07 16:45:00', 2500.0, 'completed'),
    ('ord_104', 'team_expansion', '2026-10-09 20:00:00', 7500.0, 'completed'),
    ('ord_105', 'team_inbound', '2026-10-11 22:30:00', 3100.0, 'completed'),
    ('ord_106', 'team_inbound', '2026-10-12 02:30:00', 5000.0, 'completed'),
    ('ord_107', 'team_expansion', '2026-10-13 10:00:00', 6200.0, 'completed'),
    ('ord_108', 'team_retention', '2026-10-14 15:00:00', 2800.0, 'completed');

The relationship joins each order record to its responsible sales team:

+--------------------+             +------------------+
|       orders       |             |      teams       |
+--------------------+             +------------------+
| order_id (PK)      |             | team_id (PK)     |
| team_id (FK) ------+------------>| team_name        |
| ordered_at         |  many_to_1  | channel          |
| amount             |             +------------------+
| status             |
+--------------------+

Notice the order ord_106. In UTC, it occurred on 2026-10-12 02:30:00. But in eastern daylight time (America/New_York), that instant was 2026-10-11 22:30:00 on Sunday night.

The model

The semantic model defines two entities, their relationship, and certified metrics.

The Order entity declares ordered_at as a timestamp dimension supporting multiple time grains. The measures define additive sums and counts filtered to completed transactions. The metrics expose weekly_sales and completed_orders.

source: warehouse_main

entities:
  - name: Team
    table: teams
    primary_key: team_id
    dimensions:
      - name: team_id
        column: team_id
        type: string
      - name: team_name
        column: team_name
        type: string
      - name: channel
        column: channel
        type: string

  - name: Order
    table: orders
    primary_key: order_id
    dimensions:
      - name: order_id
        column: order_id
        type: string
      - name: team_id
        column: team_id
        type: string
      - name: ordered_at
        column: ordered_at
        type: timestamp
        grains: [day, week, month, quarter, year]
      - name: status
        column: status
        type: string
    measures:
      - name: order_revenue
        expr: amount
        agg: sum
        filters:
          - dimension: status
            op: eq
            value: "completed"
      - name: order_count
        expr: order_id
        agg: count
        filters:
          - dimension: status
            op: eq
            value: "completed"

relationships:
  - name: order_team
    from: Order
    to: Team
    cardinality: many_to_one
    keys:
      - [team_id, team_id]

metrics:
  - name: weekly_sales
    expr: order_revenue
    description: "Completed gross sales revenue"
    unit: USD
    format: currency
  - name: completed_orders
    expr: order_count
    description: "Total completed customer orders"

Notice that the model embeds the status filter directly inside the measures. When callers query weekly_sales, the compiler automatically adds WHERE status = 'completed'. The Slack bot cannot accidentally sum cancelled or pending transactions.

See the model specification for syntax details on timestamp dimensions.

The queries

The Slack bot communicates through Model Context Protocol (MCP) tools. The server exposes six: list_catalog, describe_metric, run_semantic_query, drill_down, refine_semantic_query, and list_dimension_values. None accept or return SQL. The bot discovers available metrics and calls run_semantic_query following the query specification. The SQL shown below is what the engine compiles. MCP does not return it. REST returns it only when the engine is started with ENGINE_ALLOW_SQL=true and the request sets include_sql.

Query 1: Hero MCP call with New York time zone

The Slack bot executes run_semantic_query with time_zone: "America/New_York". The query requests weekly totals for sales and orders:

{
  "name": "run_semantic_query",
  "arguments": {
    "metrics": ["weekly_sales", "completed_orders"],
    "dimensions": [
      {
        "name": "ordered_at",
        "entity": "Order",
        "grain": "week"
      }
    ],
    "time_zone": "America/New_York",
    "order": [
      { "ref": "ordered_at", "desc": false }
    ]
  }
}

The server returns business results reflecting eastern daylight time boundaries:

ordered_at weekly_sales completed_orders
2026-10-05T00:00:00Z 24100 6
2026-10-12T00:00:00Z 9000 2

The order placed at 2026-10-12 02:30:00 UTC fell on Sunday evening 2026-10-11 22:30:00 in New York. Because the query specified America/New_York, the compiler placed that order into the week of October 5. The total reflects the 24100 weekly sales total.

Here is the compiled SQL generated by the engine:

SELECT
  q0."ordered_at" AS "ordered_at",
  CAST(q0."order_revenue" AS DOUBLE) AS "weekly_sales",
  CAST(q0."order_count" AS DOUBLE) AS "completed_orders"
FROM (
  SELECT
    date_trunc('week', timezone('America/New_York', timezone('UTC', "ordered_at"))) AS "ordered_at",
    CAST(SUM(amount) AS DOUBLE) AS "order_revenue",
    COUNT(order_id) AS "order_count"
  FROM "orders" AS "Order"
  GROUP BY date_trunc('week', timezone('America/New_York', timezone('UTC', "ordered_at")))
) AS q0
ORDER BY "ordered_at"

The engine generates appropriate SQL timezone conversion functions for the target database dialect, preventing time shift errors.

Query 2: Default UTC comparison query

When a query omits the time_zone property, the engine defaults to standard UTC calendar boundaries:

{
  "metrics": ["weekly_sales", "completed_orders"],
  "dimensions": [
    {
      "name": "ordered_at",
      "entity": "Order",
      "grain": "week"
    }
  ],
  "order": [
    { "ref": "ordered_at", "desc": false }
  ]
}

The resulting table places the late Sunday transaction into the subsequent calendar week:

ordered_at weekly_sales completed_orders
2026-10-05T00:00:00Z 19100 5
2026-10-12T00:00:00Z 14000 3

The compiled SQL truncates the unshifted timestamp column directly:

SELECT
  q0."ordered_at" AS "ordered_at",
  CAST(q0."order_revenue" AS DOUBLE) AS "weekly_sales",
  CAST(q0."order_count" AS DOUBLE) AS "completed_orders"
FROM (
  SELECT
    date_trunc('week', "ordered_at") AS "ordered_at",
    CAST(SUM(amount) AS DOUBLE) AS "order_revenue",
    COUNT(order_id) AS "order_count"
  FROM "orders" AS "Order"
  GROUP BY date_trunc('week', "ordered_at")
) AS q0
ORDER BY "ordered_at"

The difference between 19100 and 24100 highlights why conversational bots need explicit time zone handling when answering operations teams.

Query 3: Refusal on hallucinated metric

If an AI agent attempts to query a fabricated calculation such as custom_margin, the semantic layer halts execution immediately:

{
  "metrics": ["custom_margin"]
}

The server returns HTTP 404 with an informative error envelope:

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

The bot catches this error cleanly and tells the user that the requested metric does not exist in the certified catalog.

What to notice

  • Calendar boundary protection. Converting UTC instants to America/New_York before weekly truncation preserved the actual business week for the operations team. The bot delivers numbers matching local operational reality.
  • Agent SQL restriction. AI agents cannot invent SQL joins or hallucinate column aggregations. The Slack bot emits metric names and dimension references; the semantic compiler generates dialect-specific SQL.
  • Governed metric discovery. The bot inspects the published catalog using list_catalog instead of crawling raw table schemas. LLM token consumption drops while accuracy increases.
  • Refusals prevent hallucinated numbers. Asking for uncertified metrics produces clean error codes that guide the bot to re-prompt or apologize.
  • Sealed Query Runner credentials. The Query Runner handles physical database execution. The engine compiles parameterized SQL without storing warehouse passwords.

For foundational modeling techniques, see the guide on building your first metric in DuckDB and the overview of self-hosting regulated agents.

Limits

  • Saved queries. A model can declare saved_queries. A caller runs one by name with saved_query on the request. Filters and segments in the request are added. Any other list replaces the stored one. Bots can still send a full semantic query when the shape is not saved.
  • Natural language boundary. The semantic layer does not parse natural language questions. An LLM orchestrator translates human conversation into structured MCP tool calls.
  • Timestamp dimension requirement. Timezone conversions apply to timestamp dimensions. Date-only warehouse columns are assumed to represent local calendar dates without time component.
  • Pre-aggregation bypass. Queries requesting non-UTC time zones bypass rollups and run live against the warehouse to guarantee zone accuracy. Each time zone defines distinct calendar bucket cuts.
  • Warehouse transformation scope. The semantic layer does not alter raw warehouse timestamps at rest. Cleaning unformatted order strings and loading transactional event logs remain warehouse pipeline responsibilities.

Product notes

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