I work for a large healthcare org and from personal experience these things can get large. 6m patients is prob 20m encounters, each encounter may have 10 different kinds of meta data (each one it’s own table) and each kind has 10-100 rows. So really you are doing joins between 10 tables each with ~1-10 billion rows. It actually does slow down even running on expensive hardware.
Ah yes that does sound painful. I'd generally recommend that data be denormalized in the warehouse so the joins don't need to be done at query time. That can make the queries more complex, however.
