Making pre-aggregations transparent to existing database clients

Can SQL queries against the source, row-level data be transparently converted to use pre-aggregations in a cube where available?

I am working on a Django-based data warehouse that has outgrown its Postgresql database. We need to do some pre-aggregation, but have years and years of business logic written using Python packages that are very tightly integrated with the Django ORM.

Is there any way the ORM’s SQL queries could be translated into queries against pre-aggregations, where they are available? (And if none exist, we could just fall back to the database.)

I assume this is pie in the sky, as the SQL API doesn’t do joins between cubes yet and I’d imagine that is the only SQL parser and therefore necessary groundwork. But if it is plausible I’d love to have a go.

Hi @technicaltitch
Not sure I get your question. What Cube configuration have you implemented or just have in mind?
From what you’ve mentioned looks like you want to send queries to the Cube Store directly and don’t want to use Cube.js Backend Server but I would like to confirm.

Cube is designed to be a semantic and caching layer between your DB and frontend app. It means that you can set up your schema there and use REST API to execute queries and get data for analytics with pre-aggregations and use several other features:
https://cube.dev/docs/introduction#architecture

Thanks very much for your reply. I’d like Cube.js taking care of defining and refreshing the pre-aggregations, and I’d like Cube.js to convert SQL against the original row data into SQL (or GraphQL) against the pre-aggregated data.

For example if I’ve a pre-aggregation of monthly sales, and a query is issued “SELECT SUM(ccy) FROM sales GROUP BY MONTH(date);”, I’d like Cube.js to convert that into a query against the pre-aggregated data, I guess by passing the original SQL to the Cube.js SQL API. (I don’t mind whether it works against pre-aggregations in a database or in the Backend Server.)

The reason is we’ve years and years of API business logic in a database ORM that converts REST requests into high level Python code into SQL. This blocks us from using Cube.js as we’d need to build a whole new API working off Cube.js.

If Cube.js could convert the ORM-generated SQL queries into queries against pre-aggregations, we could very easily add Cube.js into our existing project, with Cube.js transparently making use of pre-aggregations where available. Our ORM needn’t know about Cube.js but would still work and invisibly make use of Cube.js pre-aggregations. The transpilation needn’t be complete - if a query is too complex to convert into a query against pre-aggregation it just gets run against the original row level data like any other cache miss.