This is a really good question, actually. I'm curious as to what others have to say.
I'm at Brown, which I would say is definitely research focused. The 6 classes I have taken so far have all been research focused in that they have either taught (a) how to critically evaluate the quality of other's research by reading and analyzing scientific articles or (b) how to do specific research related skills like using statistical programming packages, designing studies, writing systematic reviews, etc. Of course, many of those research skills are transferable to other types of public health work, but academic research is the context in which they are taught. The program also requires a thesis that is essentially a publishable quality, original research paper with another component (a second paper, a lit review, a policy analysis, etc.). Non-research focused programs tends to be more flexible in their graduation requirements and use practical experiences like internships or products made for specific public health settings as capstone projects.
There are some more practical things I think I am not learning because I am at a research focused school, though I am picking up some through an internship at a health department. For the most part, these are things that don't interest me much anyway-- health economics, intricacies of health insurance systems, community organizing, program evaluation, policy development, etc. I feel like I *could* learn all of these things at Brown if I had different interests, but they're covered in elective courses that I probably won't choose to take.
There are research careers outside of academia. Think tanks, research foundations, and some government jobs (CDC, NIH) come to mind, and I'm sure there are more. I'm personally hoping to continue onto a PhD and stay in academia, but I do feel prepared for other research careers. But if you have a career in mind that isn't research focused, I wouldn't choose a school that bills itself that way unless they could explain very clearly how their program could help you meet your goals.