Rill | Introducing Metrics SQL: A SQL-based semantic layer for humans and agents
Short summary
Rill’s Metrics SQL lets you define business metrics once and query them using plain SQL across dashboards, notebooks, and AI agents. It compiles metric views into optimized OLAP queries, handling grouping, filters, time functions, and security automatically.
- Define a metric once in a YAML-backed metrics view and query it as plain SQL from dashboards, notebooks, and AI agents alike, instead of redefining it in dbt, Looker, Python, etc.
- A parser/compiler/executor pipeline rewrites simple queries like "SELECT country, revenue FROM revenue_metrics" into full OLAP SQL with correct GROUP BY, joins, and filters automatically.
- Security (row-level and column permissions) and SQL-injection-safe parameter binding are enforced consistently at the executor stage for every consumer.
- Works across multiple backends (ClickHouse, Snowflake, DuckDB, Druid) and can leverage existing database optimizations like materialized views and indexes for performance.
Full summary
Rill’s Metrics SQL turns business metrics—like total revenue, MAU or ROAS—into first-class SQL objects. Instead of defining “revenue” across a dbt model, a Looker Explore, a Python script and an AI agent separately, you write it once in a YAML-backed metrics view. Under the hood, each measure (sum(order_usd), count(distinct order_id), etc.) and each dimension (country column or dictGet expression) lives in that view. When you query revenue_metrics, the engine rewrites your simple SELECT country, revenue into full OLAP SQL with GROUP BY, filters and joins, eliminating drift between dashboards, notebooks and APIs.
Because metrics are exposed via SQL, you avoid yet another API or DSL. Analysts keep using their BI tools, data scientists stick with notebooks, and even LLM-powered agents can read and write the same statements. You also inherit database optimizations: materialized views, indexes or projections can speed up metrics queries instead of raw fact-table scans. Row-level security or column permissions apply consistently, too. Every consumer—human or machine—sees the same governed definition.
The Metrics SQL pipeline has three stages. A parser checks syntax. A compiler maps measure and dimension names back to their expressions and adds any missing GROUP BY clauses. An executor injects security rules and semantic rewrites before sending the final SQL to ClickHouse, Snowflake, DuckDB or Druid. In practice, “SELECT country, revenue FROM revenue_metrics ORDER BY revenue DESC LIMIT 10” becomes a query that SUMs order_usd, GROUP BY country, and applies NULLS LAST ordering.
Rill includes real-world transformations in its docs to prove the point: filters on dictGet-based dimensions turn into parameterized WHERE clauses, subqueries with HAVING on metrics get nested correctly, and every argument is safely bound to prevent SQL injection. You get a single source of truth for metrics, a familiar interface, and a performance boost thanks to SQL-compatible optimization paths.