I've been building out embedded reporting for b2b clients (i use next.js), and as our user base grows, the P95 latency is becoming a nightmare. Every time a user changes a date filter, it triggers a fresh compute spin-up on the warehouse.

I’m currently refactoring the stack to put a universal semantic layer (in my case, cube core) between the frontend and the database. and the goal is to move all the logic, like multi-tenant row-level security (RLS) and complex joins, out of the React code itself and into a declarative modeling layer

since I started, the biggest win (so far) is using pre-aggregations. now, instead of hitting raw tables, the API hits a warmed caching tier which is in the 'cubestore'. it feels more like querying a structured API than a database

for those of you here who do high-concurrency analytics in SaaS, a question! are you just throwing more money at warehouse compute, are you moving toward this kind of decoupled architecture, what other best practices do you have? now trying to figure out if I should stick with this or just move everything to a huge ClickHouse instance and hope for the best