Hi! Question about aggregate awareness. Our marts are pretty big, so in dbt we build each one at several aggregation levels (

agg_level_0

to

agg_level_3

, same schema, coarser levels drop dimensions). Right now we expose them in Lightdash through a

UNION ALL

view with an

agg_level

column, and users have to pick the right level with a filter. It works, but it's easy to get wrong and it's not a great experience, specially for the end user. I saw somebody already asked for this in github.com/lightdash/lightdash/issues/16168. Is it something you're planning to work on anytime soon? We're considering building it ourselves otherwise, so if it's coming we'd rather wait or help out. Thanks!

👋 Thanks for your message - someone from the Lightdash support team will get back to you asap. Feel free to add any additional context to the thread here in the meantime (screenshots, app version if you're self-hosting etc.).

Hi Winnie! Thanks for your fast response. Unfortunately Pre-aggregates probably won't work for us. Two main reasons: 1. It only supports simple re-aggregatable metrics (sum, count, min, max, avg). A big chunk of our charts use count distinct / HLL metrics, and those would just fall back to the warehouse anyway. 2. It's Enterprise only, so it's not even available on our current setup.

Hey Gerard — yep apologies, I realized that it wasn't available for you guys after sending, apologies for the red-herring. It did at least help me think through and discuss this a bit with our team internally. Couple of options come to mind: • Separate the explores rather than the unioned + select drop-down. This maps more closely to the modeling and moves the selection up to a place that may be easier to reason about and build on. Not any more convenient, but imo not worse either, and could feel more clear, so an overall improvement still. • You could do some fancy footwork with Liquid templating in the YAML, maybe — but I think you'll still end up in a place where you're either pivoting on something somewhere that the user has to select if you want dynamism, or building artifacts explicitly at one level or another. I would opt for having more explicitly separate levels, clearly labeled, even if it means similar constructs built on each tier — because the artifacts here are all code-backed, with agents this is not difficult to maintain the way a pure click legacy UX has been historically when you do this kind of spread (even though it sets off the DRY instincts most of us have because of that). You'll get more control per level, and agents will navigate it better to boot. Lmk what you think, happy to keep diggin!

All that said — the aggregate awareness issue, and this one github.com/lightdash/lightdash/issues/16184 — remain open. We would be very interested in your findings if you guys work on this. We've been working on some potential ideas to allow pre-aggregates to live in users' own warehouses or infrastructure — this is functionally the same idea from a different direction. Both are intelligent routing to the right level of aggregation when multiple materializations at different aggregate levels exist.