how-to-agentic-on-your-warehouse

Input - a warehouse with nothing wrong with it

The agent sits at the top of the lineage, on the stage-2 star schema built by how-to-schema-and-warehouse. The orphans are resolved, the categories are normalized, the timezone bug is fixed. Nothing here is dirty. That is the point: this page is not about bad data.

Sample data - the registry is the data that was missing

No new generator this time. The agent reads the same seeded Everrest warehouse (seed 42) that every layer below it produced. What gets built here is the artifact that closes the semantic gap: a metric registry, where each metric carries an owner, a grain, a prose definition, the trap it exists to prevent, and a control_sql that is the trusted answer. The control SQL is never shown to the model - it is the answer key the gate marks against.

- name: platform_net_revenue
  window: >-
    order_ts from 2025-11-01 inclusive to 2025-12-01 exclusive (November 2025).
  owner: Finance
  grain: order_line
  unit: USD
  definition: >-
    SUM(net_amount) over DELIVERED order lines, multiplied by the 11 percent
    take rate. This is what Everrest earns. It is NOT the same as GMV.
  requires_status_filter: true
  trap: >-
    net_amount is merchandise value. Reporting it as revenue overstates the
    P&L line by about 9x, which is the single most expensive mislabel on this
    platform.
  control_sql: |
    SELECT ROUND(SUM(net_amount) * 0.11, 2) AS platform_net_revenue
    FROM fact_orders
    WHERE status = 'delivered'
      AND order_ts >= DATE '2025-11-01'
      AND order_ts < DATE '2025-12-01'

The 11% take rate is an assumption, not a data field - carried over from how-to-metric-to-scorecard, where the board metrics were defined and validated. The scorecard proved the definitions; the agent is held to them. That is the whole reason the registry can be trusted as an answer key.

Objective - a trust question, not a technique question

Five of the six have a signed-off control. The sixth does not, and that is not an oversight - most questions people ask an agent will never have one. If the system presents a controlled answer and an uncontrolled answer the same way, it is lying by omission.

Find-skills - what the agent is actually made of

Almost none of the effort here goes into the model. It goes into the surface the model is allowed to touch and the checks its output has to survive.

Build - thin agent, thick gate

Two phases per question. The model proposes SQL; the harness executes it and runs five checks; if anything fails, the specific failure goes back as the next user turn. Only when a query passes is the model shown the real result rows and asked for one sentence. The model never does arithmetic and never states a number it did not read from a result row - that removes a whole class of hallucination by construction rather than by prompt.

Where the trust comes from: the registry feeds the gate, not the prompt. The model is grounded on the DDL alone - real column names and row counts, no hints about the traps - so the failures below are real failures, not staged ones.

The five checks

res.control = float(con.execute(metric["control_sql"]).fetchone()[0])
res.value = _first_number(res.rows)
...
denom = abs(res.control) if res.control else 1.0
res.delta_pct = (res.value - res.control) / denom * 100
if abs(res.delta_pct) > tolerance_pct:
    res.checks.append(Check("G5 reconciliation", False,
        f"{res.value:,.2f} vs control {res.control:,.2f} ({res.delta_pct:+.1f}%)"))
    res.feedback = (
        f"Rejected on reconciliation. Your query returned {res.value:,.2f}. "
        f"The signed-off definition of `{metric['name']}` returns "
        f"{res.control:,.2f}, a gap of {res.delta_pct:+.1f}%. "
        f"Definition: {metric['definition'].strip()} "
        f"Period: {metric['window'].strip()} "
        "Rewrite the SQL to match that definition and that period exactly.")
    return res

The captured run

Model ollama/qwen2.5-coder:7b, temperature 0, seed 42, run on 2026-08-15 against the seeded warehouse. Six questions, at most three attempts each. Every line below is copied from transcript.json, including the SQL the gate threw away.

SELECT SUM(net_amount) AS total_revenue
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE dim_date.year = 2025 AND dim_date.month = 11

SELECT SUM(net_amount) AS total_revenue
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE dim_date.year = 2025 AND dim_date.month = 11 AND status = 'delivered'

SELECT SUM(net_amount * 0.11) AS platform_net_revenue
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE dim_date.year = 2025 AND dim_date.month = 11 AND status = 'delivered'
  AND order_ts >= '2025-11-01' AND order_ts < '2025-12-01'

This is the failure the whole page exists for. Attempt 2 is flawless SQL. It runs, it filters status, it hits the right month, and it is off by three quarters of a million dollars because "revenue" and net_amount are different things. No syntax check, no schema check and no amount of prompt engineering catches that. Only a reconciliation against a definition somebody signed does.

SELECT COUNT(order_id) AS order_count
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE dim_date.year = 2025 AND dim_date.month = 11

SELECT COUNT(DISTINCT order_id) AS order_count ... WHERE dim_date.year = 2025 AND dim_date.month = 11

SELECT COUNT(DISTINCT order_id) AS order_count
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE dim_date.year = 2025 AND dim_date.month = 11 AND status = 'delivered'

Two different traps, one after the other, each caught by the check built for it. Without G3 this answer ships as 13,547 orders - a 114% overstatement that would land in a demand plan.

SELECT COUNT(DISTINCT customer_id) AS buyer_count
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE order_ts BETWEEN '2026-04-01' AND '2026-06-30' AND status = 'delivered'

SELECT COUNT(DISTINCT customer_id) AS buyer_count
FROM fact_orders JOIN dim_date ON fact_orders.date_key = dim_date.date_key
WHERE order_ts >= '2026-04-01' AND order_ts < '2026-07-01' AND status = 'delivered'

The most dangerous number on this page is 7,707. BETWEEN '2026-04-01' AND '2026-06-30' against a timestamp truncates to midnight and silently drops the last day of the quarter. It is wrong by 0.8% - too small for anyone to notice in a review, big enough to make two reports disagree forever. Nothing but reconciliation finds it.

What the gate did, across the whole run

Five of six first attempts were rejected. Every rejection was on meaning (grain, status, reconciliation) or on SQL that did not run - never on safety, because this model never tried to write anything.

The first number each question produced that a system without reconciliation would have shipped. Q4's bar is almost invisible at this scale. That is the point.

Syntax was almost never the problem. G5, the check that compares against a human definition, did most of the work.

Why Q1 is worth 809%: the distance between the number the schema volunteers and the number the P&L wants, measured over the full 12 months.

The grain trap is not an edge case - it is 2.16x, every month, in the same direction.

What it still gets wrong

Q6 was left out of the registry on purpose, because that is the state most real questions are in. Here is exactly what happened, unedited:

SELECT d.category, SUM(f.net_amount) AS gmv
FROM fact_orders f JOIN dim_merchant d ON f.merchant_id = d.merchant_id
JOIN dim_date dd ON f.date_key = dd.date_key
WHERE dd.year = 2026 AND (dd.month BETWEEN 1 AND 3 OR (dd.month = 4 AND dd.dow < 5))
GROUP BY d.category ORDER BY gmv DESC LIMIT 1

Other limits, stated plainly: G3 and G4 pass vacuously on unregistered questions, so CHECKED is a weaker claim than it looks. The registry binds each question to its metric explicitly here rather than by a classifier, so routing errors are out of scope. A wrong answer that lands inside the 0.5% tolerance is not caught. And a different model fails differently: everything on this page is one captured run of one 7B local model, not a benchmark.

Expert review - five reviewers, five defects, all re-run

An anonymous panel of senior reviewers (10+ years each) read the code and the first captured runs. Every finding below was a real defect in this build, and every fix was applied and the run repeated. The console logs of the two pre-fix runs are committed in materials/how-to-agentic-on-your-warehouse/review/ so the before state is checkable.

# v1 (before): /api/chat silently ignores a top-level "system" key, so the
# schema never reached the model. It answered like a chatbot with no warehouse.
payload = {"model": self.model, "system": system, "messages": messages, ...}

# v2 (after): the system prompt is a message with role "system".
payload = {"model": self.model,
           "messages": [{"role": "system", "content": system}] + messages,
           "options": {"temperature": 0, "seed": SEED, "num_ctx": 8192}, ...}

Re-run confirmation: after all five fixes, the full six-question run was executed again end to end - 5 VERIFIED, 1 CHECKED, every chart on this page regenerated from that final transcript.json. black and ruff clean.