Teach agents your metrics, not your joins

New to Apache Ossie?

This is part two. Part one explains what Ossie is, why metrics need an open standard, and where it fits next to Iceberg and QueryFlux. It's a five-minute read.

Ask an agent for "units in stock by customer region" and you'll get something like this:

SELECT c.region, SUM(p.stock)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products  p ON o.product_id  = p.product_id
GROUP BY c.region

Every table is right. Every join is right. It runs, and it returns this:

region	agent's SQL	correct
EU	350	150
US	60	30

Stock belongs to a product, but the join repeats each product once per order. A product ordered three times gets its stock counted three times. No error, no warning, and the number is more than double the truth.

A better prompt won't fix this, and neither will a bigger model. Nothing in the schema says that total_stock has to be counted once per product before it's grouped through orders, so the join multiplies it.

An agent that can write SQL still doesn't know what your metrics mean.

The model knows what the schema doesn't

An Apache Ossie model is a YAML (or JSON) document that describes your datasets, how they join, and what each metric means. Two small details in it fix the query above:

datasets:
  - name: products
    primary_key: [product_id]    # what "one product" means
relationships:
  - from: orders                 # many orders...
    to: products                 # ...point at one product
metrics:
  - name: total_stock            # SUM(products.stock)

The primary key says what counts as one product. The relationship direction says which joins can multiply rows. The agent had neither, so it couldn't have known its answer was wrong.

But Ossie is a definition, not a query engine. It says what total_stock means, not how to turn "total_stock by region" into SQL that is correct. That gap is the hard part.

ossiec: from metric to correct SQL

So we built ossiec, an open-source (Apache-2.0) compiler for Ossie models, written in Rust. It takes the two kinds of query the spec defines, and handles this one carefully either way.

Asked as a list of metrics and dimensions (the spec's Layer 3), the question is refused:

{"metrics": ["total_stock"], "dimensions": ["customers.region"]}

Stock belongs to products, and there's no many-to-one path from products to customers, so there's no safe answer. ossiec returns E_NO_PATH instead of guessing. Written as SQL with the joins spelled out and the measure wrapped in AGG(...) (the spec's Layer 2), it compiles, and ossiec returns one SQL statement for the target engine with the correctness rules built in:

• No double counting. When a metric reads the "one" side of a join, like stock from products while grouping through orders, ossiec first reduces it to one row per product, then sums. That's how 350 becomes 150.
• Separate facts stay separate. Store sales and web sales are computed on their own and combined, never joined to each other.
• Ambiguity is an error, not a guess. ossiec follows the most direct join path. If two paths are equally direct, say a date reached through "first purchase" or through "first shipment", it refuses and names both, and the caller picks one by role (customer_first_sales.d_year).
• Errors an agent can fix. unknown metric 'revenue'; available: total_revenue, … gets fixed in one retry.

The join repeats each product once per order. ossiec counts each product once: 100 + 50 = 150.

Same request, same answer, on every engine

ossiec emits native SQL for Trino, StarRocks, ClickHouse, Postgres, MySQL, Snowflake, BigQuery and more. Every test runs on six real engines and must return the same rows on each, including official TPC-DS queries.

That testing caught bugs that never raise an error:

• ClickHouse returns 0 instead of NULL for the first row of LAG, so growth for the first period is computed against zero.
• MySQL has no NULLS LAST. Translators quietly drop it, and your "top 10" starts with unknowns.
• ClickHouse 25.8–26.4 can drop a column from a SELECT DISTINCT inside a UNION ALL, so rows that should stay distinct collapse. That's exactly the shape double-count protection produces (fixed in 26.5).

ossiec handles each of these, so the same request means the same thing everywhere.

Why QueryFlux runs it

A compiler produces SQL; it doesn't run it. Someone still has to pick the engine, check what the caller may see, and keep a record. QueryFlux already does that for every query, so that's where semantic queries go. This is the design we're building now:

The planned flow. QueryFlux picks the engine first, so ossiec compiles for the engine that will actually run the query.

• Route, then compile. QueryFlux picks the engine, then ossiec writes SQL in that engine's own dialect, with the fixes above intact.
• No side door. The SQL goes through the same guardrails, row filters and audit as any other query. With access policies configured, a metric can't show rows a user isn't allowed to see.
• Tools built for metrics. Agents get MCP tools to list models, look up real dimension values ('EU', not a guessed 'Europe') and run query_semantic_model.
• Raw SQL is your call. Each deployment chooses whether agents may still write their own SQL: allow, warn or deny.

The agent's job shrinks from "write correct SQL over a schema you've never seen" to "pick metrics from a list". That's a job agents can get right.

Try it

Available now: ossiec, as a Rust library and CLI. Point it at your own Ossie model and compare its SQL with what you'd write by hand. If you find a request where they disagree, open an issue. We want to see it.

Coming to QueryFlux: a model registry, metric tools for agents over MCP, and later a SQL surface so BI tools like Superset can query metrics directly.

New to Ossie? Start with part one. Then read the Apache Ossie spec, try ossiec, and see how QueryFlux already handles agents and row filters.