Should a user profile be a dedicated database table or assembled from normalized tables?

Question 1 is about schema design: Is there value in having a dedicated profiles table that denormalizes some of this data

No, unless you are going for a full on nosql approach the cost of joining tables on indexes is negligible. You would just be exchanging one problem for another. Figure out why your query is slow (if it even is)

Question 2 [fetch all data for tabs or lazy load]

I think this is a UI question. With tabs, I think the user expects instant changes, So I would preload the data. (Although maybe not in a single call)

Caching is important, you want to be making your calls reusable so cached data , for say a profile, can be reused across multiple UI elements.

If your backend is packaging all the data for a single page into one ViewModel before returning it, that works against caching. Less round trips can be faster, but fastest is no round trips at all, because you have it all cached client side already.

Smaller, focused VMs or raw Models that are returned in multiple round trips but once cached on the client get reused are best.

I don't think anyone can answer these questions unless you can tell us what your requirements are. You seem aware that this is a trade-off: a denormalized table is conceptually quicker, but comes with complexities around cache invalidation. Some questions for you to think about:

• How long does it take to load the profile information from normalized tables? What fraction of your SLO for loading the profile page is this?
• What latency is acceptable for "recalculating averages, stats etc"? Do these have to be accurate up to the second, or is it okay if they are 5 minutes (or some other time) out of date?

Similar questions apply to your "Question 2": how long does it take to load all the data, and is this significant?