Modify SQL build so that pre-aggregation happens before joining to dimensions

[shangyian]

This is a relatively bigger change to the SQL building paradigm: at the moment we build SQL so that the pre-aggregation happens in the last stage, after joining in all necessary dimensions. However, this is inefficient and we should switch the ordering so that pre-aggregation happens first.

[agorajek]

I don't think you can generalize that the pre-aggregation should always happen before joining to dimensions. So I am wondering what are the specific data model conditions when you know you could apply this or any other order of operations. This seems to me like a potential use case for SQL optimization hints, or perhaps some node attributes that could be used to trigger a particular SQL build. Curious to hear more.

[anhqle]

@agorajek can you give an example where we can't pre-aggregate before joining?

I think it's always possible because we know what dimensions the user wants at the very end. Therefore, we know exactly what to pre-aggregate to, specifically GROUP BY dims_that_users_want_at_the_end, all_the_join_keys_across_all_requested_dimension_joins.

[agorajek]

can you give an example where we can't pre-aggregate before joining?

• We can't pre-agregate before joining if the column used for join could be dropped (not-needed) from the dataset after pre-aggregation.
• We also must be aware of the expressions used during pre-agragation and post-aggregation. If the agg expressions are non-additive then you can't apply then more than once.

All these conditions may be less critical in a specific use-case / pattern. But if we are talking about a generic optimization here, then I would be careful, unless we could implement them as sql-hints/node-attributes/etc.

[shangyian]

We also must be aware of the expressions used during pre-agragation and post-aggregation. If the agg expressions are non-additive then you can't apply then more than once.

Hmm, but to be fair, this is always a problem for any kind of pre-aggregation. If the agg expression is non-additive, then pre-aggregation would never work, regardless of whether we're doing it before the dimension join or after the dimension join.

In the current implementation for requesting SQL, there's a flag to request/enable preaggregation or not, and we additionally check for whether all aggregations are additive before doing any pre-agg, even if it was enabled. So I think there are enough checks to make sure that we're not pre-aggregating into a corner.

[shangyian]

We can't pre-agregate before joining if the column used for join could be dropped (not-needed) from the dataset after pre-aggregation.

Is this a case where the column is required to be dropped? But in that case, as long as all the metrics are aggregatable, isn't it still fine to drop that column (we essentially just aggregate over it to drop it)?

[agorajek]

So I think there are enough checks to make sure that we're not pre-aggregating into a corner.

Perfect. That solves this yes.

Is this a case where the column is required to be dropped?

No. I am thinking about a case when a column needs to stay for some downstream dimensional join, but not for the final projection. If you make sure such columns are retained in the pre-aggregation job then that also is a good solution.

And considering the above this pre-aggregation will probably work for 99% of cases. Still I would not bet my job, that such a translation (even considering the two above) solutions will always produce the same results. Relational algebra is "funny" especially when you consider handling NULLs. I wonder if we could do some comparative unit testing and some generated data inputs here.