Does OLAP Need an ORM

Summary

TL;DR· ORMs have proven to be useful for many developers in the OLTP/transactional stack (Postgres, MySQL, etc).· OLAP/analytical databases like ClickHouse could potentially benefit from ORM abstractions.· Existing transactional ORMs probably shouldn’t be extended to OLAP due to fundamental differences in semantic meaning between OLTP and OLAP.· Moose OLAP (part of MooseStack) is an open source, MIT-licensed implementation of an ORM-like interface for ClickHouse, inspired by transactional ORMs, but adjusted for the OLAP world.The intention of this article is to open a debate with the community, and explore the question: what might an effective ORM for OLAP actually look like? Modern applications are leaning into user-facing analytics and AI functionality—i.e. features that are powered by aggregations across large data sets. That’s pushing dev teams beyond their standard transactional/OLTP database (Postgres, MySQL, etc), and into the world of analytical/OLAP databases like ClickHouse—in other words, beyond the typical scope and capabilities of your go-to Object-Relational Mapper (ORM). While sometimes polarizing, ORMs like Prisma, Drizzle, and SQLAlchemy are popular because they let language specific objects in your application code govern your database schema. You get IDE-native ergonomics (like autocomplete, inline type checks, jump-to-definition, safe refactors) that keep database code clean and close to your business logic. But there are downsides to creating an abstraction layer above SQL, from reduced visibility and control, to potentially leaky abstractions. Interestingly, analytical workloads tend to amplify these ORM weaknesses. OLAP queries tend to be more complex and harder to optimize than simple CRUD operations. And analytical databases often provide specialized functionality—window functions, incremental aggregations, table engines, etc—that existing OLTP ORMs don’t expose. But what if we could bring some of the benefits of ORMs to the world of OLAP? O...