Direct Query to Dataflows: What would the downsides be | Solves any dataset size limits it seem

The data goes into a table in managed SQL store. I'm not sure if any indexes are automatically created.

We've found performance slow, but just in POC testing and that was with a table with high cardinality and lengthily text (URLs).

I imagine you have to build dataflows that precisely fit your use case and probably leverage aggregations in your model too. Perhaps creating lookup tables and importing those in a composite model will improve performance also.