Support joins with query pushdown in the SQL API

[igorlukanin]

Currently, joins between cubes are not supported in queries to the SQL API if query pushdown is enabled. We would like to support joins and lift that limitations.

It's currently planned for February 2025.

[pauldheinrichs]

Hello!

@igorlukanin Just a thought I had this AM, with the recent release of v36 rollup_joins are unblocked by joining different datasources with different driver Factory syntax. 💚 (thank you all for that)

---

A feature that i have absolutely no idea the feasibility would be genuinely amazing on the roadmap of this current epic. If cube was capable of auto joining different datasources (providing a rollup existed) IE: removing the need for the rollup_join pre-definitions of which rollups should be referenced when joining.

IE the following query

select user_id from mysql.users
LEFT JOIN user_id from redshift.users

and the understanding that redshift.users has a pre-build pre-aggregation with a dimension on user_id

cube is capable of planning that sql to some capacity? 🤷

Anywho - just a thought.

[igorlukanin]

select user_id from mysql.users LEFT JOIN user_id from redshift.users

@pauldheinrichs I think this is outside of the current scope of what we feel Cube should be capable of. This is the territory of query engines and I feel like Athena/Trino/Presto/DuckDB would do a much better job joining data across different data sources than Cube, given how much effort they have invested into this feature. And since Cube supports all of them, then it looks like reimplementing this in Cube might be redundant.

So, I guess, it's fair to not expect anything like cross-data source joins (without pre-aggregations) to appear in Cube.

(Just to clarify, the feature described in this ticket relates to joins in SQL API queries—that can be resolved against a single data source.)