It's for procedural effects and simulation, yeah. Stuff like fluid and gas simulation, interactions between them, surface deformation from stuff like footprints or tire treads or whatever. In those areas Houdini can't be beat.

> I’m assuming for ‘regular’ 3D modelling people still prefer other software like blender/max right?

It depends. I'm not sure if Houdini has modeling stuff, it's been a while since I used it. Houdini is only one element of a complete workflow, that's why it has so many importers and exporters.