Metrics SQL: A SQL-based semantic layer for humans and agents

SQL has been the lingua franca of data for over 40 years. We decided to make it the lingua franca of our metrics-based semantic layer too.

When we built Rill, we made a bet that metrics – concepts like revenue, MAU, ROAS — are the core primitive for semantic layers.

We also believed that querying a semantic layer (and its metrics) should not require learning a custom language or API. Instead, the semantic layer should speak SQL, the language every database, BI tool, and AI agent already knows.

This blog post is a deep technical dive into how Rill's Metrics SQL works, why we built it the way we did, and where we're taking it next.

Using SQL and metrics for a determistic, secure, and fast semantic layer

In a modern analytics stack, a business metric like revenue per active user needs to be defined in multiple places: a dbt model, a Looker Explore, a Metabase question, a Python notebook, a Slack bot and in your AI Agent metadata. Over time, each definition drifts from the others. When finance asks "why does your number not match mine?", the answer is usually "we're computing it differently."

The metrics layer concept — define business logic once, query it anywhere — has been around for years. But implementations consistently run into the same trap: they invent a new query language. GraphQL queries, proprietary MDX expressions, or JSON-over-REST APIs, each requires learning a new abstraction. Each breaks existing SQL tooling. Each becomes a barrier between business logic and applications.

Julian Hyde, one of the creators of Apache Calcite and a pioneer of the SQL ecosystem, argued in his Data Council talk that metrics need first-class SQL representation — not because SQL is perfect, but because it is universal: the one language every tool, every engineer, and every AI model already speaks. Julian proposed adding a first-class MEASURE semantics in SQL — where aggregates are declared as semantic objects, not inline expressions, and the database engine handles computing measures in the correct context.

That framing resonated and we built a metrics-first semantic layer queryable using SQL. Here is what that enabled:

A deterministic source of truth. Every consumer — dashboards, notebooks, APIs, AI agents — resolves a metric from the same governed definition. No copies, no drift. An agent querying the revenue metric returns the same answer every time, not an inferred expression on raw table schemas.

A universal interface with access policies. SQL works for human analysts and LLMs alike. No new language to learn, no model fine-tuning required. Row-level security and access rules can be defined once on this interface, and enforced on every query regardless of who or what is asking.

A higher performance architecture. Extending SQL with metrics creates a path for SQL-based optimizations: materialized views can serve metrics queries rather than raw fact tables, improving performance and cost. Database Indexes and Projections can be intelligently tuned based on query patterns.

Rill’s metrics layer is composed of measures and dimensions

What is a metric? In Rill, a metric is defined as an aggregate measure expression — such as total revenue, average session duration, or return on advertising spend (ROAS) — evaluated in a dimensional context. Dimensions are the attributes you slice and group by: geography, product category, channel, date. In SQL terms, they appear in GROUP BY.

Metrics SQL and its transpilation to OLAP SQL

Metrics SQL is a SQL dialect that lets you query a metrics view as if it were a table — selecting dimensions and measures by name, filtering, ordering, and limiting results, without writing any aggregation logic in the query.

A Metrics SQL query passes through three logical layers before reaching the OLAP engine:

1. Parser parses the query and validates its syntax.
2. Query Compiler resolves each name against the metrics view definition.
3. Executor applies security filters and any semantic rewrites before handing a fully-formed SQL string to the OLAP engine.

Example: Top 10 countries by revenue Metrics SQL: SELECT country, revenue FROM revenue_metrics ORDER BY revenue DESC LIMIT 10

Output SQL (ClickHouse): SELECT ("country") AS "country", sum(order_usd) AS "revenue" FROM "revenue_model" GROUP BY 1 ORDER BY "revenue" DESC NULLS LAST LIMIT 10