People do materialize the shape of data and use it directly. That's what most OLAP stores want you to do, and what most data analytics will do as a result.

The performance is, in general, significantly worse than standard OLTP joins for most real-time use-cases.

There's very little reason to avoid an OLTP join. It's faster than anything you're likely to be able to implement yourself, and the compute required with proper indices is negligible.

You're asking the wrong question, because really the question is where do they _not_ make sense, which is almost nowhere.