Governed Analytics Agent.

The idea

An LLM is only safe when it is fenced inside a trusted model

Most "chat with your data" demos let a model write raw SQL against raw tables. That is exactly how you get a confident, well-written, wrong answer. This project takes the opposite stance.

A metric is defined once in a governed semantic layer. Dashboards, a scheduled brief, and the AI agent are all composed from those definitions, and nothing reaches the warehouse any other way. The interesting engineering is not the model. It is the governance and validation that make the model trustworthy.

Architecture

One governed path from raw data to every answer

◎ The semantic layer is the only path to data. The agent and the brief consume the exact same governed definitions.

The governed layer

Metrics defined once, with their grain and their SQL

This is the LookML / dbt-metrics idea, kept deliberately small and readable. Change a definition here and it changes everywhere, consistently.

metrics:
  revenue:
    base: items
    sql: SUM(price)
    # Product revenue (GMV). Sum of item price. Excludes freight.
  aov:
    base: orders
    sql: SUM(order_revenue) / NULLIF(COUNT(*), 0)
    # Average order value = order revenue / orders.
  avg_review_score:
    base: orders
    sql: AVG(review_score)
    # Mean review 1-5, at order grain (never fanned across order lines).

The compiler turns a request (metrics + dimensions + structured filters) into parameterized SQL, and refuses anything that violates the model. This is the guardrail that keeps the LLM honest:

# metrics must exist and share a single grain (base)
for m in metrics:
    if m not in self.metrics:
        raise SemanticError(f"Unknown metric '{m}'.")
    bases.add(self.metrics[m]["base"])
if len(bases) > 1:
    raise SemanticError("Metrics span multiple grains "
        "and cannot be combined in one query.")

# every dimension must be valid on this grain
for d in dimensions:
    if base not in self.dimensions[d].get("bases", []):
        raise SemanticError(
            f"Dimension '{d}' is not available at the '{base}' grain.")

The agent

It reasons in language, but touches data only through governed tools

The agent is given two tools and a system prompt that forbids improvising. It cannot reach raw tables:

TOOLS = [
  {"name": "describe_model", ...},   # what metrics/dimensions exist
  {"name": "query_metrics",
   "description": "Query the governed semantic layer ... You may
                  NOT write raw SQL; only compose defined metrics."},
]

SYSTEM = ("Answer ONLY with numbers you obtain from query_metrics"
  " against the governed semantic layer. Never estimate, never"
  " invent a figure, and never claim a metric exists that"
  " describe_model does not list.")

A real session. The agent inspects the model, composes a governed query, and answers with numbers it can point back to a definition:

> What were the top 3 product categories by revenue in 2018?

  [tool] describe_model()
  [tool] query_metrics(metrics=["revenue"], dimensions=["category"],
         filters=[order_date between 2018-01-01 and 2018-12-31],
         order_by="revenue", limit=3)

  Top three categories by revenue in 2018
  (governed metric revenue = SUM of item price):
    1. health_beauty    R$ 772,238
    2. watches_gifts    R$ 708,851
    3. bed_bath_table   R$ 538,069

And when a question falls outside the model, it refuses instead of guessing:

> What was our profit margin by category?

  [tool] query_metrics(metrics=["profit"], dimensions=["category"])
  -> error: Unknown metric 'profit'. Available: revenue, freight,
     gross_total, items_sold, orders, customers, aov, ...

  I can't answer that. There is no profit or margin metric in the
  governed layer (the data has no cost of goods), so I won't
  estimate one. I can show revenue or average order value instead.

That refusal is the whole point.

Automated brief

A hands-off reporting agent that reconciles before it writes

It compares the latest complete month to the prior one and narrates in plain language. Notably, it detects and excludes partial trailing months, so it never reports an incomplete period as a real collapse:

# Peel every partial trailing month, not just the last one. A month
# whose volume is far below the prior month is treated as incomplete.
while len(months) >= 2 and months[-1][1] < 0.5 * months[-2][1]:
    dropped_months.append(months.pop()[0])

The on-time delivery drop is the kind of real signal a stakeholder acts on. The partial-month handling is the guardrail that keeps the brief honest: a naive agent would have narrated September 2018 (16 orders, incomplete) as a 99% revenue collapse.

Trust, underneath

Reconciliation and guardrails carry the whole thing

Revenue booked from items reconciles to what customers actually paid. The check runs after every build and gates the brief:

booked = SELECT SUM(price + freight) FROM fct_order_items
paid   = SELECT SUM(payment_value)  FROM raw_order_payments
gap_pct = abs(booked - paid) / paid * 100

check("revenue reconciles to payments (<= 1.5%)",
      gap_pct <= 1.5,
      f"booked R${booked:,.0f} vs paid R${paid:,.0f} ({gap_pct:.2f}% gap)")

The residual is understood: payment values carry installment and voucher amounts that do not map 1:1 to line items. The tolerance sits just above that known variance, so ordinary noise passes but a real break, a metric silently doubling or a dropped join, trips the check.

And the compiler refuses requests that would produce a misleading number:

These are not errors to work around. They are the model protecting the answer.

What it demonstrates

The craft behind a trustworthy answer

Stack: Python · DuckDB · SQL · YAML semantic layer · Anthropic API. Built on the Olist Brazilian E-Commerce public dataset (~100k real orders). Used purely to demonstrate method on real, public data. No proprietary or employer data is involved.