Why You Need a SQL-based Metrics Layer

On January 31, 2025 I spoke at DuckCon #6 about SQL and metrics, specifically why your metrics layer should be defined in SQL.

The ability to aggregate raw data into summarized metrics (summing order prices into revenue, counting distinct user IDs as DAU) and enable slicing of these metrics across dimensions (revenue broken down by country) is at the core of what analytics teams do.  If every business can be distilled down to a mathematical equation -- as Lenny Rachitsky has written -- then the variables in that equation are metrics. The job of every data team is to turn digital exhaust into metrics, to feed their knowledge workers who in turn tune the business equation.

Metrics are a just-right-sized abstraction for analytics. They are neither too global -- e.g. a single, annual revenue figure -- nor are they too granular -- e.g. a database table with millions of sales orders.

Metrics are flexible aggregates which can be evaluated in different contexts. Revenue can filtered ("revenue in the US and Canada"), trended ("revenue by day this month"), or broken down ("revenue for top 10 products").

For metrics to be useful, they need to be explorable. If revenue is trending down, it could be because MAUs are off, or because of uptime issues in a particular cloud region. Investigation into metrics requires fast exploration, and fast exploration requires a fast backend. Pivot tables remain wildly popular because they are a fast, flexible, intuitive way to explore metrics.

DuckDB is an ideal database for storing metrics because of its performance and ergonomics. Metrics can be stored in DuckDB in the form of OLAP cubes, which are often 100x smaller than raw fact tables, while still enabling calculation of metrics across any dimensional context.

At Rill, we use DuckDB to efficiently store and rapidly serve metrics. Once metrics are defined with SQL expressions, we automatically generate an exploratory dashboard -- enabling slicing, dicing, and drill down across any dimension.

While humans love this interactive experience, we also believe that metrics are the right features for conversational AI agents to engage with: no one likes a slow analyst, human or AI.

Inspiration & Further Reading

I'm far from alone in my belief in that metrics are the core primitive for analytics, my thinking has been inspired by writings and talks from Julian Hyde, Abhi Sivasailam, Nick Handel, Ankur Goyal, alana goyal, and Dave Fowler.

Hyde, J., & Fremlin, J. (2024). Measures in SQL. In Companion of the 2024 International Conference on Management of Data (pp. 31-40). Santiago, Chile: ACM. https://dl.acm.org/doi/10.1145/3626246.3653374

Handel, N. (2021, December 15). A Brief History of the Metrics Store. Towards Data Science. Retrieved from https://towardsdatascience.com/a-brief-history-of-the-metrics-store-28208ec8f6f1/

Fowler, D. (2020, December 7). Kimball in the Context of the Modern Data Warehouse: What’s Worth Keeping, and What’s Not. Coalesce Conference 2020. YouTube. https://www.youtube.com/watch?v=3OcS2TMXELU

Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). Wiley.