Learn

Metric layer

The costliest mistake is not bad arithmetic but the right arithmetic on the wrong thing.

Why "revenue" is written down as a definition you can read and confirm, and compiled to one SQL query, before any number is computed.

Stage 2 of 10. METRICS_DRAFT writes down what each business number means; AWAIT_CONFIRM lets you check it before anything is measured.

The problem it solves

The most common way an AI analyst goes wrong is not bad arithmetic. It is the right arithmetic on the wrong thing: gross sales instead of net, refunds counted as revenue, the neighbouring column, the wrong month. The answer looks confident and is quietly about something else.

So the engine never computes “revenue” from a question. It first writes a metric definition: which table, which column, which rows are left out, which date. You can read it in one sentence, and every number in the report is computed from it.

What happens on the example

METRICS_DRAFT · AWAIT_CONFIRM · the coffee-roaster run

T1 Orders8 columnsdate:date · order_id:id · customer:category · channel:category · product:category · qty:integer · amount:currency · status:category

METRICS_DRAFT · AWAIT_CONFIRM

1 · DraftA model (or deterministic rules) writes each metric as a small YAML definition.
2 · ValidateEvery column, filter and unit is checked against the profiled table; a bad definition is refused with a reason.
3 · CompileEach definition becomes one parameterised SQL query, run in DuckDB.

Out

revenueassumed
ordersassumed
retail_revenueassumed
wholesale_revenueassumed
refund_rateassumed
revenue, 2026-04$36,200N11one row of the compiled query

From metric_defs and the MEASURE facts of the run.

The run drafted 5 metrics: revenue (assumed), orders (assumed), retail_revenue (assumed), wholesale_revenue (assumed), refund_rate (assumed). “Assumed” means nobody has confirmed them yet; the report says so at the top. Here is the definition of revenue, exactly as it was stored:

id: revenue
label: Revenue
type: sum
table: t_t1
expression: amount
filters:
  - status IS NULL OR status NOT IN ('refunded')
time_column: date
unit: currency
currency: USD
dimensions:
  - customer
  - channel
  - product
status: assumed

In words: revenue is the sum of amount over orders whose status is not “refunded”, by month of date, in USD. The refunded orders are the line that a careless analysis gets wrong.

Validation. Before it is accepted, code checks every part against the profiled table: the column exists and holds numbers, the filters name real columns, the time column is a date. A definition that fails is refused with a reason, so a model that invents a column name gets nowhere.

Compilation. Each definition becomes exactly one SQL query, with its values passed as parameters (never pasted into the text):

WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL)
SELECT period, value FROM m1 ORDER BY period

with parameters ["refunded","2026-01","2026-06"]. Run for one month, it gives one fact: April’s revenue is $36,200N11. All six months:

Month	Revenue
2026-01	$41,200N14
2026-02	$42,900N15
2026-03	$44,100N12
2026-04	$36,200N11
2026-05	$35,800N16
2026-06	$37,000N17

The ideas behind it

A semantic layer

Analysts call this a semantic layer or metric layer: the meaning of each business number is written once, in one place, and every query uses it. It is why the engine can say “revenue fell” and you can check what “revenue” meant.

The model drafts, code decides

When a model is configured, it drafts the definitions from the column names and profile, because naming things is what language models are good at. The output is structured: YAML that must parse and pass validation.⁠Note 1 Validation is code, not a model: every table and column must exist, a summed column must be a number, units must fit the aggregation, and a ratio may not divide a metric by itself. With no model, deterministic rules draft the same kind of definitions (that is what happened in the tutorial). Either way, a person can confirm, edit or drop each one.

Parameterised SQL

Values such as 'refunded' travel as parameters ($1), not as text glued into the query. That rules out SQL injection from a cell value, and it means the verifier can re-run the exact query later.

Try it

Draft the metrics, see their SQL and values, and save them to a file you can edit and reuse:

Recorded output, offline run

$ bun run aa metrics examples/coffee-roaster --yes --currency USD --today 2026-07-01 --save agentic-analytics.metrics.yaml
Drafted 5 metric(s) (deterministic fallback). Note: the model was unavailable (…); used deterministic fallbacks.
  assumption: Amounts in revenue are taken to be in USD (no currency marked in the data).
  assumption: Amounts in retail_revenue are taken to be in USD (no currency marked in the data).
  assumption: Amounts in wholesale_revenue are taken to be in USD (no currency marked in the data).

Revenue = sum of amount where status is empty or status is not refunded, per month, in USD (assumed)
  sql: WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period
  params: ["refunded","2026-01","2026-06"]
  2026-01 41200 · 2026-02 42900 · 2026-03 44100 · 2026-04 36200 · 2026-05 35800 · 2026-06 37000
Orders = number of distinct order_id where status is empty or status is not refunded, per month (assumed)
  sql: WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, count(DISTINCT "order_id") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period
  params: ["refunded","2026-01","2026-06"]
… (15 more lines)

Replaced in the recording because they change on every run: the reason no model answered (it depends on your setup).

Then review them interactively during a job: Confirm your metrics.

In the code

packages/metrics/src/draft.ts: drafting, with the model or the fallback rules.
packages/metrics/src/validate.ts: every check a definition must pass.
packages/metrics/src/compile.ts: definition to parameterised SQL.
packages/metrics/src/describe.ts: the plain-words sentence.
The spec: docs/specs/metric-layer.md; the format: Metric YAML.

Go deeper: Metric layer.

Facts on this page

Fact	Value	What it is	How it was computed	Verified
N11	$36,200	`revenue_2026-04`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-04","$3":"2026-04","input_rows":1020}`	recomputed
N12	$44,100	`revenue_2026-03`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-03","$3":"2026-03","input_rows":1050}`	recomputed
N14	$41,200	`revenue_2026-01`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-01","$3":"2026-01","input_rows":980}`	not in the report
N15	$42,900	`revenue_2026-02`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-02","$3":"2026-02","input_rows":1010}`	not in the report
N16	$35,800	`revenue_2026-05`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-05","$3":"2026-05","input_rows":1005}`	not in the report
N17	$37,000	`revenue_2026-06`	SQL, run in DuckDB WITH m1 AS (SELECT strftime("date", '%Y-%m') AS period, sum("amount") AS value FROM "t_t1" WHERE NOT _is_total AND "date" IS NOT NULL AND (("status" IS NULL) OR ("status" NOT IN ($1))) AND strftime("date", '%Y-%m') BETWEEN $2 AND $3 GROUP BY ALL) SELECT period, value FROM m1 ORDER BY period parameters: `{"$1":"refunded","$2":"2026-06","$3":"2026-06","input_rows":1040}`	recomputed